Mybatis基于注解形式的sql语句生成实例代码
对其做了些优化,但此种sql生成方式仅适用于复杂程度不高的sql,所以实用性不是很高,仅仅是写着玩的,知道点mybatis的注解形式的使用方式,可能以后会逐渐完善起来。第一次写博客,写的简单点。
创新互联是一家专注于成都网站建设、网站制作与策划设计,曲麻莱网站建设哪家好?创新互联做网站,专注于网站建设十多年,网设计领域的专业建站公司;建站业务涵盖:曲麻莱等地区。曲麻莱做网站价格咨询:18982081108
package com.bob.config.mvc.mybatis; import java.lang.annotation.Documented; import java.lang.annotation.ElementType; import java.lang.annotation.Retention; import java.lang.annotation.RetentionPolicy; import java.lang.annotation.Target; /** * 实体类对应的列 * * @author jjb * @create 2017-09-08 14:42 */ @Documented @Retention(RetentionPolicy.RUNTIME) @Target({ElementType.FIELD, ElementType.METHOD}) public @interface Column { /** * 当前属性对应的列名 * * @return */ String value() default ""; /** * 当前属性是不是表必须的 * * @return */ boolean required() default true; }
package com.bob.config.mvc.mybatis; import java.lang.annotation.Documented; import java.lang.annotation.ElementType; import java.lang.annotation.Retention; import java.lang.annotation.RetentionPolicy; import java.lang.annotation.Target; /** * 实体类对应的表 * * @author jjb * @create 2017-09-08 14:44 */ @Documented @Retention(RetentionPolicy.RUNTIME) @Target({ElementType.TYPE}) public @interface Table { String value() default ""; /** * 当前表的主键 * * @return */ String key(); }
package com.bob.config.mvc.mybatis; import org.apache.ibatis.annotations.DeleteProvider; import org.apache.ibatis.annotations.InsertProvider; import org.apache.ibatis.annotations.Options; import org.apache.ibatis.annotations.SelectProvider; import org.apache.ibatis.annotations.UpdateProvider; /** * Mybatis基础Mapper * * @author jjb * @create 2017-09-08 14:37 */ public interface BaseMapper{ /** * 插入语句 * * @param bean * @return */ @Options(useGeneratedKeys = true) @InsertProvider(type = SqlProvider.class, method = "insert") public int insert(T bean); /** * 删除语句 * * @param bean * @return */ @DeleteProvider(type = SqlProvider.class, method = "delete") public int delete(T bean); /** * 更新语句 * * @param bean * @return */ @UpdateProvider(type = SqlProvider.class, method = "update") public int update(T bean); /** * 查找语句 * * @param bean * @return */ @SelectProvider(type = SqlProvider.class, method = "select") public T findFirst(T bean); }
package com.bob.config.mvc.mybatis; import java.time.LocalDate; /** * Mybatis实体类 * * @author jjb * @create 2017-09-08 17:16 */ @Table(key = "id") public class MybatisEntity { @Column() private String id; @Column("USER_NAME") private String name; @Column() private Integer age; private LocalDate date; @Column("ADRESS_NUMBER") private Integer userAdressNumber; public String getId() { return id; } public void setId(String id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } @Column("CUR_DATE") public LocalDate getDate() { return date; } public void setDate(LocalDate date) { this.date = date; } public Integer getUserAdressNumber() { return userAdressNumber; } public void setUserAdressNumber(Integer userAdressNumber) { this.userAdressNumber = userAdressNumber; } }
package com.bob.config.mvc.mybatis; import java.lang.reflect.Field; import java.util.Map; /** * 表到实体的格式化器 * * @author jjb * @create 2017-09-08 14:51 */ public interface TableFormatter { /** * 根据属性获取字段名称 * * @param field * @return */ public String getColumnName(Field field); /** * 获取主键属性对应的列名 * * @return */ public String getKeyColumnName(Class<?> clazz); /** * 获取主键的属性名称 * * @param clazz * @return */ public String getKeyFiledName(Class<?> clazz); /** * 根据类获取表名称 * * @param clazz * @return */ public String getTableName(Class<?> clazz); /** * 获取一个类的所有属性的映射信息 * * @param clazz * @return */ public MapgetFieldMappings(Class<?> clazz); }
package com.bob.config.mvc.mybatis; import java.lang.reflect.Field; import java.lang.reflect.Modifier; import java.util.HashMap; import java.util.Map; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.BeanUtils; import org.springframework.stereotype.Component; import org.springframework.util.Assert; import org.springframework.util.ReflectionUtils; import org.springframework.util.StringUtils; /** * 依据驼峰原则来将表的信息格式化为实体类的信息,在驼峰处改小写同时插入下划线 * * @author jjb * @create 2017-09-08 14:55 */ @Component public class HumpToUnderLineFormatter implements TableFormatter { private static final Logger LOGGER = LoggerFactory.getLogger(HumpToUnderLineFormatter.class); private static final Map, Map > FIELD_TO_COLUMN_MAPPINGS = new HashMap , Map >(); private static final Map CLASS_TO_TABLE_MAPPING = new HashMap (); private static final StringBuilder SB = new StringBuilder(); private static final Object LOCK = new Object(); @Override public String getColumnName(Field field) { Assert.notNull(field, "属性不能为空"); Map mappings = FIELD_TO_COLUMN_MAPPINGS.get(field.getDeclaringClass()); if (mappings == null) { synchronized (LOCK) { mappings = FIELD_TO_COLUMN_MAPPINGS.get(field.getDeclaringClass()); if (mappings == null) { mappings = buildMapping(field.getDeclaringClass()); } } } return mappings.get(field); } @Override public String getKeyColumnName(Class<?> clazz) { Table table = checkClass(clazz); return getColumnName(ReflectionUtils.findField(clazz,table.key())); } @Override public String getKeyFiledName(Class<?> clazz) { Table table = checkClass(clazz); Field field = ReflectionUtils.findField(clazz,table.key()); Assert.state(field != null,"@Table的key()指定的属性必须存在"); return field.getName(); } private Table checkClass(Class<?> clazz){ Assert.isTrue(clazz != null , "与Table对应的Class不能为空"); Table table = clazz.getAnnotation(Table.class); Assert.isTrue(table != null && StringUtils.hasText(table.key()),"["+clazz.getName()+"]必须标识@Table注解且key()不能为空"); return table; } @Override public String getTableName(Class<?> clazz) { Assert.notNull(clazz, "类不能为空"); Assert.isTrue(clazz.isAnnotationPresent(Table.class), "[" + clazz.getName() + "]类上必须含有@Table注解"); String name = CLASS_TO_TABLE_MAPPING.get(clazz); if (name == null) { synchronized (LOCK) { name = CLASS_TO_TABLE_MAPPING.get(clazz); if (name == null) { buildMapping(clazz); } } } return CLASS_TO_TABLE_MAPPING.get(clazz); } @Override public Map getFieldMappings(Class<?> clazz) { Assert.isTrue(clazz != null && clazz.isAnnotationPresent(Table.class), "与Table对应的Class不能为空且必须标识@Table注解"); Map mappings = FIELD_TO_COLUMN_MAPPINGS.get(clazz); if (mappings == null) { synchronized (LOCK) { mappings = FIELD_TO_COLUMN_MAPPINGS.get(clazz); if (mappings == null) { mappings = buildMapping(clazz); } } } return FIELD_TO_COLUMN_MAPPINGS.get(clazz); } /** * 创建实体到表映射 * * @param clazz */ private Map buildMapping(Class<?> clazz) { buildClassToTableMapping(clazz); Map mappings = new HashMap (); FIELD_TO_COLUMN_MAPPINGS.put(clazz, mappings); buildFiledToColumnMapping(clazz, mappings); buildFiledToColumnMappingWithGetter(clazz, mappings); return mappings; } /** * 创建类名到表名的名称映射 * * @param clazz */ private void buildClassToTableMapping(Class<?> clazz) { Table table = clazz.getAnnotation(Table.class); Assert.notNull(table, "[" + clazz.getName() + "]类上必须有@Table注解"); CLASS_TO_TABLE_MAPPING.put(clazz, StringUtils.hasText(table.value()) ? table.value() : doFormatWithHunmRule(clazz.getSimpleName())); } /** * 通过Filed建立属性名称到字段名称的映射 * * @param clazz * @param mappings */ private void buildFiledToColumnMapping(Class<?> clazz, Map mappings) { ReflectionUtils.doWithLocalFields(clazz, (field) -> { Column column = field.getAnnotation(Column.class); if (column != null) { if (Modifier.isStatic(field.getModifiers())) { LOGGER.error("[{}]注解不适用于静态方法:[{}]", Column.class.toString(), field); return; } mappings.put(field, StringUtils.hasText(column.value()) ? column.value() : doFormatWithHunmRule(field.getName())); } } ); } /** * 通过getter()建立属性名称到字段名称的映射 * * @param clazz * @param mappings */ private void buildFiledToColumnMappingWithGetter(Class<?> clazz, Map mappings) { ReflectionUtils.doWithLocalMethods(clazz, (method) -> { Column column = method.getAnnotation(Column.class); if (column != null) { if (Modifier.isStatic(method.getModifiers())) { LOGGER.warn("[{}]注解不适用于静态方法: [{}]", Column.class.toString(), method); return; } if (!method.getName().startsWith("get") || method.getParameterTypes().length > 0) { LOGGER.warn("[{}]注解只适用于getter方法,而非: [{}]方法", Column.class.toString(), method); return; } String fieldName = BeanUtils.findPropertyForMethod(method).getName(); mappings.put(ReflectionUtils.findField(clazz, fieldName), StringUtils.hasText(column.value()) ? column.value() : doFormatWithHunmRule(fieldName)); } } ); } /** * 依据驼峰原则格式化属性或者类名称,在驼峰处改小写同时前一位插入下划线,忽略首字母 * * @param name * @return */ private static String doFormatWithHunmRule(String name) { Assert.hasText(name, "属性或者类名称不能为空"); SB.delete(0, SB.length()); SB.append(toUpperCase(name.charAt(0))); for (int i = 1; i < name.length(); i++) { if (isUpperCase(name.charAt(i))) { SB.append("_"); } SB.append(toUpperCase(name.charAt(i))); } return SB.toString(); } /** * 将字符转换为大写 * * @param ch * @return */ private static char toUpperCase(char ch) { return Character.toUpperCase(ch); } /** * 判断是否为大写 * * @param ch * @return */ private static boolean isUpperCase(char ch) { return Character.isUpperCase(ch); } }
package com.bob.config.mvc.mybatis; import java.lang.reflect.Field; import java.util.ArrayList; import java.util.List; import java.util.Map.Entry; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.util.StringUtils; /** * Mybatis的SQL语句供应器 * * @author jjb * @create 2017-09-08 14:37 */ public class SqlProvider { private static final Logger LOGGER = LoggerFactory.getLogger(SqlProvider.class); private TableFormatter tableFormat = new HumpToUnderLineFormatter(); /** * 根据Bean对象生成插入SQL语句 * * @param bean * @return */ public String insert(Object bean) { Class<?> beanClass = bean.getClass(); String tableName = tableFormat.getTableName(beanClass); StringBuilder insertSql = new StringBuilder(); Listcolumns = new ArrayList (); List values = new ArrayList (); insertSql.append("INSERT INTO ").append(tableName).append("("); try { for (Entry entry : tableFormat.getFieldMappings(beanClass).entrySet()) { Field field = entry.getKey(); field.setAccessible(true); if (field.get(bean) != null) { columns.add(entry.getValue()); values.add("#{" + field.getName() + "}"); } } } catch (Exception e) { new RuntimeException("get insert sql has exceptoin:" + e); } int columnSize = columns.size(); for (int i = 0; i < columnSize; i++) { insertSql.append(columns.get(i)); insertSql.append(i != columnSize - 1 ? "," : ") VALUES("); } for (int i = 0; i < columnSize; i++) { insertSql.append(values.get(i)); insertSql.append(i != columnSize - 1 ? "," : ")"); } return insertSql.toString(); } /** * 根据Bean对象生成更新SQL语句 * * @param bean * @return */ public String update(Object bean) { Class<?> beanClass = bean.getClass(); String tableName = tableFormat.getTableName(beanClass); StringBuilder updateSql = new StringBuilder(); updateSql.append(" UPDATE ").append(tableName).append(" SET "); try { for (Entry entry : tableFormat.getFieldMappings(beanClass).entrySet()) { Field field = entry.getKey(); field.setAccessible(true); if (field.get(bean) != null) { updateSql.append(entry.getValue()).append("=#{").append(field.getName()).append("},"); } } updateSql.deleteCharAt(updateSql.length() - 1); } catch (Exception e) { new RuntimeException("get update sql is exceptoin:" + e); } updateSql.append(" WHERE ").append(tableFormat.getKeyColumnName(beanClass) + " =#{" + tableFormat.getKeyFiledName(beanClass) + "}"); return updateSql.toString(); } /** * 根据Bean对象生成删除SQL语句 * * @param bean * @return */ public String delete(Object bean) { Class<?> beanClass = bean.getClass(); String tableName = tableFormat.getTableName(beanClass); StringBuilder deleteSql = new StringBuilder(); deleteSql.append(" DELETE FROM ").append(tableName).append(" WHERE "); try { for (Entry entry : tableFormat.getFieldMappings(beanClass).entrySet()) { Field field = entry.getKey(); field.setAccessible(true); if (field.get(bean) != null) { deleteSql.append(entry.getValue()).append("=#{").append(field.getName()).append("} AND "); } } deleteSql.delete(deleteSql.length() - 5, deleteSql.length() - 1); } catch (Exception e) { new RuntimeException("get delete sql is exceptoin:" + e); } return deleteSql.toString(); } /** * 生成查询SQL语句 * * @param bean * @return */ public String select(Object bean) { Class<?> beanClass = bean.getClass(); String tableName = tableFormat.getTableName(beanClass); StringBuilder selectSql = new StringBuilder(); List columns = new ArrayList (); List values = new ArrayList (); selectSql.append("SELECT "); try { for (Entry entry : tableFormat.getFieldMappings(beanClass).entrySet()) { Field field = entry.getKey(); field.setAccessible(true); selectSql.append(entry.getValue() + ","); if (field.get(bean) != null) { columns.add(entry.getValue()); values.add("#{" + field.getName() + "}"); } } selectSql.deleteCharAt(selectSql.length() - 1); } catch (Exception e) { new RuntimeException("get select sql is exceptoin:" + e); } selectSql.append(" FROM ").append(tableName).append(" WHERE "); int columnSize = columns.size(); for (int i = 0; i < columnSize; i++) { selectSql.append(columns.get(i)).append("=").append(values.get(i)).append(" AND "); } selectSql.delete(selectSql.length() - 5, selectSql.length() - 1); return selectSql.toString(); } }
package com.bob.test.concrete.MySQLGenerate; import java.time.LocalDate; import com.bob.config.mvc.mybatis.MybatisEntity; import com.bob.config.mvc.mybatis.SqlProvider; import org.junit.Before; import org.junit.Test; /** * Mysql基于注解形式的sql语句生成测试 * * @author jjb * @create 2017-09-11 11:10 */ public class MysqlGenerateTest { private SqlProvider sqlProvider; private MybatisEntity mybatisEntity; @Before public void doBefore(){ sqlProvider = new SqlProvider(); mybatisEntity = new MybatisEntity(); mybatisEntity.setId("0015415"); mybatisEntity.setName("lanboal"); mybatisEntity.setAge(28); mybatisEntity.setDate(LocalDate.now()); mybatisEntity.setUserAdressNumber(24); } @Test public void testInsert(){ String sql = sqlProvider.insert(mybatisEntity); System.out.println(sql); } @Test public void testUpdate(){ String sql = sqlProvider.update(mybatisEntity); System.out.println(sql); } @Test public void testDelete(){ String sql = sqlProvider.delete(mybatisEntity); System.out.println(sql); } @Test public void testSelect(){ String sql = sqlProvider.select(mybatisEntity); System.out.println(sql); } }
总结
以上所述是小编给大家介绍的 Mybatis基于注解形式的sql语句生成实例代码,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对创新互联网站的支持!
文章名称:Mybatis基于注解形式的sql语句生成实例代码
浏览地址:http://myzitong.com/article/ieoojj.html