Mybatis基于注解形式的sql语句生成实例代码

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 */@Componentpublic 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);  }}