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

借鉴博客网址: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);
    }

}