MybatisPlus执行原生Sql的三种方式

@Select注解

这也是网上流传最广的方法，但是我个人认为这个方法并不优雅，且采用${}的方式代码审计可能会无法通过，会被作为代码漏洞

public interface BaseMapper<T> extends com.baomidou.mybatisplus.core.mapper.BaseMapper<T> {
 
    @Select("${nativeSql}")
    Object nativeSql(@Param("nativeSql") String nativeSql);
}

使用Mybatis-plus的SqlRunner

使用SqlRunner的方式执行原生SQL。这个方法我较为提倡，也是MybatisPlus源码的测试类中用的最多的方法。

yml文件中添加MybatisPlus配置:

mybatis-plus.global-config.enable-sql-runner=true

public void queryBySql(){
    List<Map<String, Object>> lstData = SqlRunner.db().selectList("select * from abc");
}

使用Mybatis-plus的Mapper

public interface CustomMapper extends BaseMapper<Entity> {
    @Select("SELECT * FROM your_table WHERE condition = #{value}")
    List<Entity> selectByCustomSql(@Param("value") String value);
    
    @Select("<script>" +
            "SELECT item_id,is_assign,shop_id FROM t_order_detail_items " +
            "where shop_id = #{shopId} and is_deleted = #{status}  " +
            "</script>")
    @Results({
            @Result(property = "itemId", column = "item_id"),
            @Result(property = "isAssign", column = "is_assign"),
            @Result(property = "shopId", column = "shop_id"),
    })
    List<ShopItemVO> selectBySql(@Param("shopId") Integer shopId, @Param("status") Short status);

}

使用Mybatis最底层的方式

String sql = "select * from test_example limit 1,10";
 
Class<ExampleEntity> entityClass = ExampleEntity.class;
// INFO: DCTANT: 2022/9/29 使用MybatisPlus自己的SqlHelper获取SqlSessionFactory
SqlSessionFactory sqlSessionFactory = SqlHelper.sqlSessionFactory(ExampleEntity.class);
// INFO: DCTANT: 2022/9/29 通过SqlSessionFactory创建一个新的SqlSession，并获取全局配置
SqlSession sqlSession = sqlSessionFactory.openSession();
Configuration configuration = sqlSessionFactory.getConfiguration();

// INFO: DCTANT: 2022/9/29 生成一个uuid，用于将这个SQL创建的MappedStatement注册到MybatisPlus中
String sqlUuid = UUID.fastUUID().toString(true);
RawSqlSource rawSqlSource = new RawSqlSource(configuration, sql, Object.class);
MappedStatement.Builder builder = new MappedStatement.Builder(configuration, sqlUuid, rawSqlSource, SqlCommandType.SELECT);
ArrayList<ResultMap> resultMaps = new ArrayList<>();
// INFO: DCTANT: 2022/9/29 创建返回映射
ResultMap.Builder resultMapBuilder = new ResultMap.Builder(configuration, UUID.fastUUID().toString(true), entityClass, new ArrayList<>());
ResultMap resultMap = resultMapBuilder.build();
resultMaps.add(resultMap);
builder.resultMaps(resultMaps);

MappedStatement mappedStatement = builder.build();
// INFO: DCTANT: 2022/9/29 将创建的MappedStatement注册到配置中
configuration.addMappedStatement(mappedStatement);
// INFO: DCTANT: 2022/9/29 使用SqlSession查询原生SQL
List<ExampleEntity> list = sqlSession.selectList(sqlUuid);
// INFO: DCTANT: 2022/9/29 关闭session
sqlSession.close();