SpringBoot+MyBatis中自动根据@Table注解和@Column注解生成增删改查逻辑

习惯使用jpa操作对象的方式，现在用mybatis有点不习惯。

其实是懒得写SQL，增删改查那么简单的事情你帮我做了呗，mybatis：NO。

没办法，自己搞喽！

这里主要是实现了通过代码自动生成mybatis的增删改查语句，并注册到SqlSessionFactory中，并没有生成xml文件，不生成mapper文件。只是在项目启动的时候自动生成，配置到SqlSessionFactory中，下一次启动后自动根据model自动生成相关逻辑。所以不必担心表结构修改需要改一大堆文件。使用了此方法只需要改model文件就可以了。

注意：model必须添加@Table注解，对应的列也必须添加@Column注解（javax）。

思路：

在项目启动时mybatis默认配置运行结束后添加自定义配置

@Configuration
@AutoConfigureAfter(MybatisAutoConfiguration.class)
public class MyBatisTypeMapScannerConfig {

    private Logger log = Logger.getLogger(MyBatisTypeMapScannerConfig.class);

    public MyBatisTypeMapScannerConfig(ApplicationContext applicationContext, SqlSessionFactory sqlSessionFactory) {
        

在配置中可以获取SqlSessionFactory，看到这里，已经结束了。剩下的都是不重要细节。

1.读取项目下的model（包含@table注解的类）

List<Class<?>> list = ClassUtil.getClassesWithAnnotation(Table.class);

2.读取model下的字段（根据@Column注解）

Map<String, Map<String, Object>> cols = ClassUtil.getColumnRelation(clas);

3.根据table和column信息配置resultmap，mapper

End；

代码： MyBatisTypeMapScannerConfig 

 

import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.persistence.Table;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.ibatis.builder.xml.XMLMapperBuilder;
import org.apache.ibatis.mapping.ResultMap;
import org.apache.ibatis.mapping.ResultMapping;
import org.apache.ibatis.session.SqlSessionFactory;

import org.apache.tomcat.util.buf.StringUtils;
import org.mybatis.spring.boot.autoconfigure.MybatisAutoConfiguration;
import org.springframework.boot.autoconfigure.AutoConfigureAfter;
import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.Configuration;

import com.esri.rest.util.ClassUtil;

/**
 * 自动根据@Table注解和@Column注解添加mybatis中的resultmap配置，
 * 此配置生效后不需要在mapper.xml中手动添加resultmap，自动添加的resultmap的ID为类的全路径名
 * <p>
 * Title: MyBatisTypeMapScannerConfig.java
 * </p>
 * <p>
 * Description:
 * </p>
 * 
 * @author lichao1
 * @date 2018年12月4日
 * @version 1.0
 */
@Configuration
@AutoConfigureAfter(MybatisAutoConfiguration.class)
public class MyBatisTypeMapScannerConfig {

    protected final Log log = LogFactory.getLog(getClass());

    public MyBatisTypeMapScannerConfig(ApplicationContext applicationContext, SqlSessionFactory sqlSessionFactory) {
        log.debug("自动添加resultMap");
        org.apache.ibatis.session.Configuration configuration = sqlSessionFactory.getConfiguration();

        // ResultMap rm = new ResultMap.Builder(configuration, id, type,
        // null).build();
        // configuration.addResultMap(rm);
        // 获取默认包下的所有包含@Table注解的类
        List<Class<?>> list = ClassUtil.getClassesWithAnnotation(Table.class);

        for (Class<?> clas : list) {
            System.out.println(clas);
            Map<String, Map<String, Object>> cols = ClassUtil.getColumnRelation(clas);
            ResultMap rm = new ResultMap.Builder(configuration, clas.getName(), clas,
                    getResultMapping(configuration, cols)).build();
            configuration.addResultMap(rm);
            List<ResultMap> resultMaps = new ArrayList<ResultMap>();
            resultMaps.add(rm);

            Table table = clas.getAnnotation(Table.class);
            String tableName = table.name();
            String allColum = getColumListString(cols);

            // select
            // String sql = " select " + allColum + " from " + tableName;
            // SqlSource sqlSource = new RawSqlSource(configuration, sql, clas);
            // Builder builder = new MappedStatement.Builder(configuration,
            // clas.getName() + ".select", sqlSource,
            // SqlCommandType.SELECT);
            // builder.resultMaps(resultMaps);
            // MappedStatement ms = builder.build();
            // configuration.addMappedStatement(ms);

            InputStream inputStream = createXml(clas, cols);
            XMLMapperBuilder mapperParser = new XMLMapperBuilder(inputStream, configuration, clas.getName() + ".auto",
                    configuration.getSqlFragments());
            mapperParser.parse();

        }

        log.debug("自动添加resultMap");
    }

    private InputStream createXml(Class<?> clas, Map<String, Map<String, Object>> cols) {
        StringBuilder builder = new StringBuilder();
        String name = clas.getName();
        Table table = clas.getAnnotation(Table.class);
        String tableName = table.name();
        String allColum = getColumListString(cols);

        builder.append("<?xml version="1.0" encoding="UTF-8" ?>");
        builder.append(
                "<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >");
        builder.append("<mapper namespace="" + name + "" >");

        Set<String> keys = cols.keySet();
        String[] keyArr = new String[keys.size()];
        keys.toArray(keyArr);

        /****************** 查询 start ***************/
        builder.append("<select id="" + name + ".select" resultMap="" + name + ""  >");
        builder.append("SELECT " + allColum + " FROM " + tableName + " WHERE 1=1 ");
        // builder.append(" <if test="id != null"> and id like #{id} </if>");
        // 查询条件
        builder.append(createLikelySql(keyArr, clas, cols));

        // 排序
        builder.append(" <if test="ORDERBY != null"> order by ${ORDERBY} </if>");

        // 分页
        builder.append(" <if test="pagestart != null">  limit  #{pagesize} OFFSET #{pagestart} </if>");

        builder.append("</select>");
        /****************** 查询 end ***************/

        /****************** 计数 start ***************/
        builder.append("<select id="" + name + ".count" resultType="long"  >");
        builder.append("SELECT count(*) count FROM " + tableName + " WHERE 1=1 ");
        builder.append(createLikelySql(keyArr, clas, cols));
        builder.append("</select>");

        /****************** 计数 end ***************/
        
        /****************** 精确查询 start ***************/
        builder.append("<select id="" + name + ".selectexactly" resultMap="" + name + ""  >");
        builder.append("SELECT " + allColum + " FROM " + tableName + " WHERE 1=1 ");
        // builder.append(" <if test="id != null"> and id like #{id} </if>");
        // 查询条件
        builder.append(createExactlySql(keyArr, clas, cols));

        // 排序
        builder.append(" <if test="ORDERBY != null"> order by ${ORDERBY} </if>");

        // 分页
        builder.append(" <if test="pagestart != null">  limit  #{pagesize} OFFSET #{pagestart} </if>");

        builder.append("</select>");
        /****************** 精确查询 end ***************/

        /****************** 精确计数 start ***************/
        builder.append("<select id="" + name + ".countexactly" resultType="long"  >");
        builder.append("SELECT count(*) count FROM " + tableName + " WHERE 1=1 ");
        builder.append(createExactlySql(keyArr, clas, cols));
        builder.append("</select>"); 
        /****************** 精确计数 end ***************/
        
        
        /****************** 自定义条件语句查询 start ***************/
        builder.append("<select id="" + name + ".selectwhere" resultMap="" + name + ""  >");
        builder.append("SELECT " + allColum + " FROM " + tableName + "  "); 
        // 查询条件
        builder.append(" <if test="WHERESTR != null"> WHERE ${WHERESTR} </if>");

        // 排序
        builder.append(" <if test="ORDERBY != null"> order by ${ORDERBY} </if>");

        // 分页
        builder.append(" <if test="pagestart != null">  limit  #{pagesize} OFFSET #{pagestart} </if>");

        builder.append("</select>");
        /****************** 自定义条件语句查询 end ***************/

        /****************** 自定义条件语句计数 start ***************/
        builder.append("<select id="" + name + ".countwhere" resultType="long"  >");
        builder.append("SELECT count(*) count FROM " + tableName + "  ");
        // 查询条件
        builder.append(" <if test="WHERESTR != null"> WHERE ${WHERESTR} </if>");
        builder.append("</select>"); 
        /****************** 自定义条件语句计数 end ***************/
        
        
        

        /****************** 删除 start ***************/
        builder.append("<delete id="" + name + ".delete" parameterType="java.lang.String" >");
        builder.append("  DELETE FROM " + tableName + " WHERE  id =#{id} ");
        builder.append("</delete>");
        /****************** 删除 end ***************/

        /****************** 批量删除 start ***************/
        builder.append("<delete id="" + name + ".deletebatch" >");
        builder.append("  DELETE FROM " + tableName + "    WHERE 1=1  ");
        builder.append(createExactlySql(keyArr, clas, cols));
        builder.append("</delete>");
        /****************** 批量删除 end ***************/

        /****************** 更新 start ***************/
        builder.append("<update id="" + name + ".update"  parameterType="" + name + ""   >");
        builder.append("UPDATE " + tableName + "  SET ");
        for (int i = 0; i < keyArr.length; i++) {
            String key = keyArr[i];
            Map<String, Object> obj = cols.get(key);
            builder.append(" " + (String) obj.get("dbname") + " = #{" + key + "}");
            if (i < (keyArr.length - 1)) {
                builder.append(",");
            }
        }
        builder.append(" WHERE  id =#{id} ");
        builder.append("</update>");
        /****************** 更新 end ***************/

        /****************** 按需更新 start ***************/
        builder.append("<update id="" + name + ".updatesection"  parameterType="" + name + ""   >");
        builder.append("UPDATE " + tableName + "  SET ");
        for (int i = 0; i < keyArr.length; i++) {
            String key = keyArr[i];
            Map<String, Object> obj = cols.get(key);
            // builder.append(" " + (String) obj.get("dbname") + " = #{" + key +
            // "}");
            if (!"id".equals(key)) {
                 builder.append(" <if test="_parameter.containsKey('" + key + "')">" + (String) obj.get("dbname") + " = #{" + key
                         + "} , </if>");
                // builder.append(" " + (String) obj.get("dbname") + " = #{" + key + "} , ");
            }
        }
        builder.append(" id = #{id} ");
        builder.append(" WHERE  id =#{id} ");
        builder.append("</update>");
        /****************** 按需更新 end ***************/

        /****************** 插入 start ***************/
        builder.append("<insert id="" + name + ".insert" parameterType="" + name + ""  >");
        builder.append("insert  INTO " + tableName + "  (" + allColum + ") VALUES");
        builder.append("(" + getColumListString2(cols, "#{", "}") + ") ");
        builder.append("</insert>");
        /****************** 插入 end ***************/

        builder.append("</mapper>");
        InputStream is = new ByteArrayInputStream(builder.toString().getBytes());
        return is;
    }

    private String createListXml() {
        StringBuilder builder = new StringBuilder();

        return builder.toString();
    }

    /**
     * 生成查询条件语句
     * 
     * @param keyArr
     * @param clas
     * @param cols
     * @return
     */
    private String createLikelySql(String[] keyArr, Class<?> clas, Map<String, Map<String, Object>> cols) {
        StringBuilder builder = new StringBuilder();
        for (int i = 0; i < keyArr.length; i++) {
            String key = keyArr[i];
            Map<String, Object> obj = cols.get(key);
            try {
                Class t = (Class) obj.get("type");
                if (t == String.class) {
                    // String 类型自动支持like '%' || #name# || '%'
                    builder.append(" <if test="" + key + " != null"> and " + (String) obj.get("dbname")
                            + " like '%'||#{" + key + "}|| '%' </if>");
                } else {
                    builder.append(" <if test="" + key + " != null"> and " + (String) obj.get("dbname") + " = #{"
                            + key + "} </if>");
                }

            } catch (Exception e) {
                log.info(obj);
                log.info(key);
                log.info(clas.getName());
            }
        }
        return builder.toString();
    }

    private String createExactlySql(String[] keyArr, Class<?> clas, Map<String, Map<String, Object>> cols) {
        StringBuilder builder = new StringBuilder();
        for (int i = 0; i < keyArr.length; i++) {
            String key = keyArr[i];
            Map<String, Object> obj = cols.get(key);
            builder.append(
                    " <if test="" + key + " != null"> and " + (String) obj.get("dbname") + " = #{" + key + "} </if>");
        }
        return builder.toString();
    }

    /**
     * 获取表内字段
     * 
     * @param cols
     * @return
     */
    private String getColumListString(Map<String, Map<String, Object>> cols) {
        return getColumListString(cols, "", "");
    }

    private String getColumListString(Map<String, Map<String, Object>> cols, String pre, String end) {
        pre = pre == null ? "" : pre;
        end = end == null ? "" : end;
        Set<String> keys = cols.keySet();
        String[] keyArr = new String[keys.size()];
        String[] nameArr = new String[keys.size()];
        keys.toArray(keyArr);
        for (int i = 0; i < keyArr.length; i++) {
            String key = keyArr[i];
            Map<String, Object> obj = cols.get(key);
            if (((String) obj.get("dbname")).equals("update_date")) {
                log.info("key");
            }
            nameArr[i] = pre + (String) obj.get("dbname") + end;
        }
        return StringUtils.join(nameArr);
    }

    private String getColumListString2(Map<String, Map<String, Object>> cols) {
        return getColumListString2(cols, "", "");
    }

    private String getColumListString2(Map<String, Map<String, Object>> cols, String pre, String end) {
        pre = pre == null ? "" : pre;
        end = end == null ? "" : end;
        Set<String> keys = cols.keySet();
        String[] keyArr = new String[keys.size()];
        String[] nameArr = new String[keys.size()];
        keys.toArray(keyArr);
        for (int i = 0; i < keyArr.length; i++) {
            String key = keyArr[i];
            nameArr[i] = pre + key + end;
        }
        return StringUtils.join(nameArr);
    }

    /**
     * 根据@Column注解生成字段映射关系
     * 
     * @param configuration
     * @param Map<String,
     *            Map<String, Object>> cols
     * @return
     */
    private List<ResultMapping> getResultMapping(org.apache.ibatis.session.Configuration configuration,
            Map<String, Map<String, Object>> cols) {
        List<ResultMapping> resultMappings = new ArrayList<ResultMapping>();

        System.out.println(cols);
        Set<String> keys = cols.keySet();
        String[] keyArr = new String[keys.size()];
        keys.toArray(keyArr);

        for (String key : keyArr) {
            String property;
            String column;
            Object javaType;
            Map<String, Object> map = cols.get(key);
            property = key;
            column = (String) map.get("dbname");
            javaType = map.get("type");
            ResultMapping mapping = new ResultMapping.Builder(configuration, property, column, (Class<?>) javaType)
                    .build();
            resultMappings.add(mapping);
        }

        return resultMappings;
    }

}

应用 

CommonDaoimpl

/**
 * CommonDaoimpl
 * <p>
 * Title: CommonDaoimpl.java
 * </p>
 * <p>
 * Description:
 * </p>
 * 
 * @author lichao1
 * @date 2018年11月19日
 * @version 1.0
 * @param <T>
 * @param <ID>
 */
@Repository
public class CommonDaoimpl<T, ID extends Serializable> implements ICommonDao<T, ID> {

    @PersistenceContext
    private EntityManager entityManager;

    @Autowired
    public SqlSessionFactory sqlSessionFactory;

    protected <T> String getStatement(Class<T> clazz, String prefix) {
        String entityName = clazz.getSimpleName();
        if (entityName.endsWith("Model")) {
            entityName = entityName.substring(0, entityName.length() - 5);
        }
        if (entityName.endsWith("Entity")) {
            entityName = entityName.substring(0, entityName.length() - 6);
        }
        entityName = prefix + entityName;
        return entityName;
    }

    // Mybatis 查询方法， 只需要输入mapper的命名空间名称和方法名就可以实现数据库操作

    
    /**
     * 执行删除语句
     * @param statement
     * @param parameter
     * @return
     */
    public int deleteByMyBatis(String statement, Object parameter) {
        return this.sqlSessionFactory.openSession().delete(statement, parameter);
    }

    /**
     * 执行删除语句
     * @param t
     * @param funName
     * @param parameter
     * @return
     */
    public int deleteByMyBatis(Class<T> t, String funName, Object parameter) {
        String statement = t.getName() + "." + funName;
        return deleteByMyBatis(statement, parameter);
    }
    public int deleteByMyBatis(Class<T> t, Object parameter) {
        String statement = t.getName() + "." + "delete";
        return deleteByMyBatis(statement, parameter);
    }

    /**
     * 执行查询列表
     * @param statement
     * @param parameter
     * @return
     */
    public List<T> listByMyBatis(String statement, Object parameter) {
        return this.sqlSessionFactory.openSession().selectList(statement, parameter);
    }

    /**
     * 执行查询列表语句
     * @param t
     * @param funName
     * @param parameter
     * @return
     */
    public List<T> listByMyBatis(Class<T> t, String funName, Object parameter) {
        String statement = t.getName() + "." + funName;
        return listByMyBatis(statement, parameter);
    }
    
    public List<T> ListByMyBatis(Class<T> t, Object parameter){
        String statement = t.getName() + "." + "select";
        return listByMyBatis(statement, parameter);
    }

    /**
     * 执行插入语句
     * @param statement
     * @param parameter
     * @return
     */
    public int insertByMyBatis(String statement, Object parameter) {
        return this.sqlSessionFactory.openSession().insert(statement, parameter);
    }

    /**
     * 执行插入语句
     * @param t
     * @param funName
     * @param parameter
     * @return
     */
    public int insertByMyBatis(Class<T> t, String funName, Object parameter) {
        String statement = t.getName() + "." + funName;
        return insertByMyBatis(statement, parameter);
    }

    /**
     * 执行选择一条记录语句
     * @param statement
     * @param parameter
     * @return
     */
    public T selectOneByMyBatis(String statement, Object parameter) {
        return this.sqlSessionFactory.openSession().selectOne(statement, parameter);
    }

    /**
     * 执行选择一条记录语句
     * @param t
     * @param funName
     * @param parameter
     * @return
     */
    public T selectOneByMyBatis(Class<T> t, String funName, Object parameter) {
        String statement = t.getName() + "." + funName;
        return selectOneByMyBatis(statement, parameter);
    }
    
    /**
     * 计数
     * @param t
     * @param parameter
     * @return
     */
    
    public long countByMyBatis(String statement, Object parameter) {
        return (long)this.sqlSessionFactory.openSession().selectOne(statement, parameter);
    }
    
    public long countByMyBatis(Class<?> t, Object parameter){
        String statement = t.getName() + ".count";
        return countByMyBatis(statement, parameter);
    }

    /**
     * 更新数据
     * @param statement
     * @param parameter
     * @return
     */
    public int updateByMyBatis(String statement, Object parameter) {
        return this.sqlSessionFactory.openSession().update(statement, parameter);
    }

    /**
     * 更新数据
     * @param t
     * @param funName
     * @param parameter
     * @return
     */
    public int updateByMyBatis(Class<T> t, String funName, Object parameter) {
        String statement = t.getName() + "." + funName;
        return updateByMyBatis(statement, parameter);
    }
    
    public int updateByMyBatis(Class<T> t,  Object parameter) {
        String statement = t.getName() + ".update";
        return updateByMyBatis(statement, parameter);
    }
    
    /**
     * 插入数据
     * @param statement
     * @param parameter
     * @return
     */
    public int insertByMayBatis(String statement, Object parameter) {
        return this.sqlSessionFactory.openSession().insert(statement, parameter);
    }
    
    public int insertByMayBatis(Class<T> t, String funName, Object parameter) {
        String statement = t.getName() + "." + funName;
        return this.sqlSessionFactory.openSession().insert(statement, parameter);
    }
    
    public int insertByMayBatis(Class<T> t, Object parameter) { 
        return insertByMayBatis(t,"insert", parameter);
    }