Mybatis基于注解形式的sql语句生成

忘是亡心i 2022-06-06 09:18 274阅读 0赞

借鉴博客网址:[http://blog.csdn.net/hj7jay/article/details/51907652][http_blog.csdn.net_hj7jay_article_details_51907652].  
对其做了些优化，但此种sql生成方式仅适用于复杂程度不高的sql，所以实用性不是很高，仅仅是写着玩的，知道点mybatis的注解形式的使用方式，可能以后会逐渐完善起来。第一次写博客，写的简单点。

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<T> { 
    
        /** * 插入语句 * * @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 Map<Field, String> getFieldMappings(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<Class<?>, Map<Field, String>> FIELD_TO_COLUMN_MAPPINGS = new HashMap<Class<?>, Map<Field, String>>();
    
        private static final Map<Class, String> CLASS_TO_TABLE_MAPPING = new HashMap<Class, String>();
    
        private static final StringBuilder SB = new StringBuilder();
    
        private static final Object LOCK = new Object();
    
        @Override
        public String getColumnName(Field field) {
            Assert.notNull(field, "属性不能为空");
            Map<Field, String> 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<Field, String> getFieldMappings(Class<?> clazz) {
            Assert.isTrue(clazz != null && clazz.isAnnotationPresent(Table.class), "与Table对应的Class不能为空且必须标识@Table注解");
            Map<Field, String> 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<Field, String> buildMapping(Class<?> clazz) {
            buildClassToTableMapping(clazz);
            Map<Field, String> mappings = new HashMap<Field, String>();
            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<Field, String> 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<Field, String> 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();
            List<String> columns = new ArrayList<String>();
            List<String> values = new ArrayList<String>();
            insertSql.append("INSERT INTO ").append(tableName).append("(");
            try {
                for (Entry<Field, String> 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<Field, String> 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<Field, String> 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<String> columns = new ArrayList<String>();
            List<String> values = new ArrayList<String>();
            selectSql.append("SELECT ");
            try {
                for (Entry<Field, String> 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);
        }
    
    }

[http_blog.csdn.net_hj7jay_article_details_51907652]: http://blog.csdn.net/hj7jay/article/details/51907652