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

时间：2023-11-26 11:36:14

习惯使用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);

    }

相关文章