Mybatis框架的拓展用法--持续更新

自定义sql（扩展用法）

方案一

注册sql到Mybatis Configuration中

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.yzh.cases.entity.BotIntegrationCategory;
import org.apache.ibatis.mapping.*;
import org.apache.ibatis.scripting.LanguageDriver;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.junit.Test;
import org.junit.platform.commons.util.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;
    
    @Autowired
    SqlSessionFactory sqlSessionFactory;

    @Test
    public void doSearchDataSource() {
        String sql = "SELECT * FROM table_name WHERE column_a = #{paramA} AND column_b = #{paramB} AND column_c = #{paramC}";
        Configuration configuration = sqlSessionFactory.getConfiguration();
        LanguageDriver driver = configuration.getDefaultScriptingLanguageInstance();
        String statementId = sql.hashCode() + SqlCommandType.SELECT.toString();

        if (!configuration.hasStatement(statementId)) {
            SqlSource sqlSource = driver.createSqlSource(configuration, sql, Map.class);
            MappedStatement.Builder builder = new MappedStatement.Builder(configuration, statementId, sqlSource, SqlCommandType.SELECT);
            MappedStatement statement = builder.resultMaps(new ArrayList<ResultMap>() {
                {
                    List<ResultMapping> resultMappings = new ArrayList<>();
                    resultMappings.add(new ResultMapping.Builder(configuration, "id", "id", Long.class).build());
                    resultMappings.add(new ResultMapping.Builder(configuration, "parentId", "parent_id", Long.class).build());
                    resultMappings.add(new ResultMapping.Builder(configuration, "orderNum", "order_num", Integer.class).build());
                    resultMappings.add(new ResultMapping.Builder(configuration, "level", "level", Integer.class).build());
                    resultMappings.add(new ResultMapping.Builder(configuration, "type", "type", Short.class).build());
                    resultMappings.add(new ResultMapping.Builder(configuration, "createTime", "create_time", Date.class).build());
                    resultMappings.add(new ResultMapping.Builder(configuration, "updateTime", "update_time", Date.class).build());
                    this.add(new ResultMap.Builder(configuration, "baseResultMap", BotIntegrationCategory.class, resultMappings).build());
                }
            }).build();
            configuration.addMappedStatement(statement);
            configuration.setCallSettersOnNulls(true);
        }
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        Map<String, Object> params = new HashMap<>();
        params.put("paramA", "valueA");
        params.put("paramB", "valueB");
        params.put("paramC", (short) 1);
        try {
            List<BotIntegrationCategory> list = sqlSession.selectList(statementId, params);
            System.out.println(JSON.toJSONString(list));
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sqlSession.close();
        }


    }

方案二

    public DataSource datasource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://ip:port/datasource?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false");
        dataSource.setUsername("用户名");
        dataSource.setPassword("密码");
        return dataSource;
    }


    public NamedParameterJdbcTemplate namedParameterJdbcTemplate(DataSource dataSource) {
        return new NamedParameterJdbcTemplate(dataSource);
    }

    public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        PathMatchingResourcePatternResolver resResolver = new PathMatchingResourcePatternResolver();
        bean.setMapperLocations(new Resource[]{resResolver.getResource(ResourcePatternResolver.CLASSPATH_ALL_URL_PREFIX + "/*.xml")});
        return bean.getObject();
    }


    @Test
    public void doSearch() {
        DataSource datasource = datasource();
        NamedParameterJdbcTemplate namedParameterJdbcTemplate = namedParameterJdbcTemplate(datasource);
        String sql = "SELECT * FROM table_name WHERE column_a = :paramA AND column_b = :paramA limit :start,:nums";
        Map<String, Object> params = new HashMap<>();
        params.put("paramA", "value_1");
        params.put("paramB", "value_2");
        params.put("start", 0);
        params.put("nums", 2);
        List<Map<String, Object>> list = namedParameterJdbcTemplate.queryForList(sql, params);
        System.out.println(JSON.toJSONString(list));

        RowMapper<BotIntegrationCategory> rowMapper = new RowMapper<BotIntegrationCategory>() {
            @Override
            public BotIntegrationCategory mapRow(ResultSet rs, int rowNum) throws SQLException {
                System.out.println("-----------------------" + rowNum);
                BotIntegrationCategory po = new BotIntegrationCategory();
                po.setId(rs.getLong("id"));
                po.setParentId(rs.getLong("parent_id"));
                po.setOrderNum(StringUtils.isNotBlank(rs.getString("order_num"))?Integer.parseInt(rs.getString("order_num")):null);
                po.setLevel(StringUtils.isNotBlank(rs.getString("level"))?Integer.parseInt(rs.getString("level")):null);
                po.setType(rs.getShort("type"));             po.setClassificationName(rs.getString("classification_name"));
                po.setCreateTime(rs.getDate("create_time"));
                po.setUpdateTime(rs.getDate("update_time"));
                po.setIsDeleted(rs.getBoolean("is_deleted"));
                return po;
            }
        };
        List<BotIntegrationCategory> query = namedParameterJdbcTemplate.query(sql, params, rowMapper);
        System.out.println(JSON.toJSONString(query));


    }