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