原文网址：MyBatis-Plus--自定义SQL_IT利刃出鞘的博客-CSDN博客
简介
说明
本文用实例介绍MybatisPlus如何自定义SQL。
使用
自定义的sql使用Wrapper对版本有要求：mybatis-plus版本需要大于或等于3.0.7。
特殊语句
源码
包里边定义好的常量。我们可以直接用这些常量。
mybatis-plus-core-3.3.2.jar\com\baomidou\mybatisplus\core\toolkit\Constants.class
package com.baomidou.mybatisplus.core.toolkit;public interface Constants extends StringPool {String MYBATIS_PLUS = "mybatis-plus";String MD5 = "MD5";String AES = "AES";String AES_CBC_CIPHER = "AES/CBC/PKCS5Padding";String ENTITY = "et";String ENTITY_DOT = "et.";String WRAPPER = "ew";String WRAPPER_DOT = "ew.";String WRAPPER_ENTITY = "ew.entity";String WRAPPER_SQLSEGMENT = "ew.sqlSegment";String WRAPPER_EMPTYOFNORMAL = "ew.emptyOfNormal";String WRAPPER_NONEMPTYOFNORMAL = "ew.nonEmptyOfNormal";String WRAPPER_NONEMPTYOFENTITY = "ew.nonEmptyOfEntity";String WRAPPER_EMPTYOFWHERE = "ew.emptyOfWhere";String WRAPPER_NONEMPTYOFWHERE = "ew.nonEmptyOfWhere";String WRAPPER_ENTITY_DOT = "ew.entity.";String U_WRAPPER_SQL_SET = "ew.sqlSet";String Q_WRAPPER_SQL_SELECT = "ew.sqlSelect";String Q_WRAPPER_SQL_COMMENT = "ew.sqlComment";String Q_WRAPPER_SQL_FIRST = "ew.sqlFirst";String COLUMN_MAP = "cm";String COLUMN_MAP_IS_EMPTY = "cm.isEmpty";String COLLECTION = "coll";String WHERE = "WHERE";String MP_OPTLOCK_INTERCEPTOR = "oli";String MP_OPTLOCK_VERSION_ORIGINAL = "MP_OPTLOCK_VERSION_ORIGINAL";String MP_OPTLOCK_VERSION_COLUMN = "MP_OPTLOCK_VERSION_COLUMN";String MP_OPTLOCK_ET_ORIGINAL = "MP_OPTLOCK_ET_ORIGINAL";String WRAPPER_PARAM = "MPGENVAL";String WRAPPER_PARAM_FORMAT = "#{%s.paramNameValuePairs.%s}";}
分页
代码
Mapper
package com.example.demo.user.mapper;import com.baomidou.mybatisplus.core.conditions.Wrapper;import com.baomidou.mybatisplus.core.mapper.BaseMapper;import com.baomidou.mybatisplus.core.metadata.IPage;import com.baomidou.mybatisplus.core.toolkit.Constants;import com.baomidou.mybatisplus.extension.plugins.pagination.Page;import com.example.demo.user.entity.User;import org.apache.ibatis.annotations.Param;import org.apache.ibatis.annotations.Select;import org.springframework.stereotype.Repository;@Repositorypublic interface UserMapper extends BaseMapper<User> {// 自定义SQL的分页@Select("SELECT * FROM t_user ${ew.customSqlSegment}")IPage<User> findUser(IPage<User> page, @Param(Constants.WRAPPER) Wrapper wrapper);}
Controller
package com.example.demo.user.controller;import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;import com.baomidou.mybatisplus.core.metadata.IPage;import com.baomidou.mybatisplus.core.toolkit.Wrappers;import com.baomidou.mybatisplus.extension.conditions.query.LambdaQueryChainWrapper;import com.baomidou.mybatisplus.extension.plugins.pagination.Page;import com.example.demo.user.entity.User;import com.example.demo.user.mapper.UserMapper;import com.example.demo.user.service.UserService;import io.swagger.annotations.Api;import io.swagger.annotations.ApiOperation;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.GetMapping;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RestController;import java.util.List;@Api(tags = "分页")@RestController@RequestMapping("page")public class PageController {// 我为了简单直接注入mapper，项目中controller要通过service调mapper@Autowiredprivate UserMapper userMapper;@ApiOperation("自定义SQL")@GetMapping("customSQL")public IPage<User> customSQLPage(Page<User> page) {LambdaQueryWrapper<User> wrapper = Wrappers.lambdaQuery();wrapper.like(User::getNickName, "昵称");// 这样写会报错：MybatisPlusException: can not use this method for "getCustomSqlSegment"// LambdaQueryChainWrapper<User> wrapper = userService.lambdaQuery()// .eq(User::getUserName, "sky");return userMapper.findUser(page, wrapper);}}
测试
访问：http://localhost:8080/page/customSQL
结果：
前端结果
{"records": [{"id": 3,"userName": "aa","nickName": "昵称1","email": "333@","createTime": "-01-12T13:12:21","updateTime": "-01-12T13:12:21","deletedFlag": 0},{"id": 4,"userName": "bb","nickName": "昵称2","email": "444@","createTime": "-02-11T18:12:21","updateTime": "-02-11T18:12:21","deletedFlag": 0},{"id": 5,"userName": "cc","nickName": "昵称3","email": "555@","createTime": "-03-24T18:12:21","updateTime": "-03-24T18:12:21","deletedFlag": 0}],"total": 3,"size": 10,"current": 1,"orders": [],"optimizeCountSql": true,"searchCount": true,"countId": null,"maxLimit": null,"pages": 1}
后端结果
Creating a new SqlSessionSqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@699ca44c] was not registered for synchronization because synchronization is not activeJDBC Connection [HikariProxyConnection@1734039311 wrapping com.mysql.cj.jdbc.ConnectionImpl@5280daae] will not be managed by Spring==> Preparing: SELECT COUNT(*) AS total FROM t_user WHERE (nick_name LIKE ?)==> Parameters: %昵称%(String)<== Columns: total<== Row: 3<==Total: 1==> Preparing: SELECT * FROM t_user WHERE (nick_name LIKE ?) LIMIT ?==> Parameters: %昵称%(String), 10(Long)<== Columns: id, user_name, nick_name, email, create_time, update_time, deleted_flag<== Row: 3, aa, 昵称1, 333@, -01-12 13:12:21, -01-12 13:12:21, 0<== Row: 4, bb, 昵称2, 444@, -02-11 18:12:21, -02-11 18:12:21, 0<== Row: 5, cc, 昵称3, 555@, -03-24 18:12:21, -03-24 18:12:21, 0<==Total: 3Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@699ca44c]
用法大全
代码
Mapper
package com.example.business.customsql.mapper;import com.baomidou.mybatisplus.core.conditions.Wrapper;import com.baomidou.mybatisplus.core.mapper.BaseMapper;import com.example.business.customsql.entity.User;import org.apache.ibatis.annotations.Param;import org.apache.ibatis.annotations.Select;import org.springframework.stereotype.Repository;import java.util.List;@Repositorypublic interface UserMapper extends BaseMapper<User> {// 条件放到wrapper@Select("SELECT * FROM t_user ${ew.customSqlSegment}")List<User> testSelect1(@Param("ew") Wrapper wrapper);// 也可以这么写// List<User> testSelect1(@Param(Constants.WRAPPER) Wrapper wrapper);// 条件放到wrapper，指定表名@Select("SELECT * FROM ${table_name} ${ew.customSqlSegment}")List<User> testSelect2(@Param("table_name") String tableName, @Param("ew") Wrapper wrapper);// 条件放到wrapper，指定表名、所需字段@Select("SELECT ${ew.SqlSelect} FROM ${table_name} ${ew.customSqlSegment}")List<User> testSelect3(@Param("table_name") String tableName, @Param("ew") Wrapper wrapper);// 条件放到wrapper，指定某个字段，指定表名、所需字段@Select("SELECT ${ew.SqlSelect} FROM ${table_name} ${ew.customSqlSegment} AND age = #{age} ")List<User> testSelect4(@Param("table_name") String tableName,@Param("age") Integer age, @Param("ew") Wrapper wrapper);}
Controller
package com.example.business.customsql;import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;import com.baomidou.mybatisplus.extension.conditions.query.LambdaQueryChainWrapper;import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;import com.baomidou.mybatisplus.core.toolkit.Wrappers;import com.example.business.customsql.entity.User;import com.example.business.customsql.mapper.UserMapper;import com.example.business.customsql.service.UserService;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.GetMapping;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RestController;import java.util.Arrays;import java.util.List;import java.util.Map;@RestController@RequestMapping("customSQL")public class CustomSQLController {@Autowiredprivate UserMapper userMapper;@Autowiredprivate UserService userService;@GetMapping("test1234")public void test1234() {// 写法1：Wrappers获取LambdaQueryWrapper（推荐）LambdaQueryWrapper<User> queryWrapper = Wrappers.lambdaQuery();queryWrapper.eq(User::getUserName, "Tony2").select(User::getId, User::getAge);// 写法2：通过service获得lambdaQuery。// 这样写有的版本会报错：MybatisPlusException: can not use this method for "getCustomSqlSegment"// LambdaQueryChainWrapper<User> queryWrapper = userService// .lambdaQuery// .eq(User::getUserName, "Tony2")// .select(User::getId, User::getAge)// 写法3：QueryWrapper// QueryWrapper<User> queryWrapper = new QueryWrapper<>();// queryWrapper.eq("name", "Tony2").select("id", "age");List<User> users1 = userMapper.testSelect1(queryWrapper);List<User> users2 = userMapper.testSelect2("t_user", queryWrapper);List<User> users3 = userMapper.testSelect3("t_user", queryWrapper);List<User> users4 = userMapper.testSelect4("t_user", 22, queryWrapper);System.out.println("----------------------------------------------------------");System.out.println(users1);System.out.println(users2);System.out.println(users3);System.out.println(users4);}}
测试
访问：http://localhost:8080/customSQL/test1234
结果
JDBC Connection [HikariProxyConnection@1693577632 wrapping com.mysql.cj.jdbc.ConnectionImpl@55539d0] will not be managed by Spring==> Preparing: SELECT * FROM t_user WHERE (user_name = ?)==> Parameters: Tony2(String)<== Columns: id, user_name, age<== Row: 2, Tony2, 22<== Row: 3, Tony2, 22<== Row: 4, Tony2, 23<==Total: 3Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@17030d24]Creating a new SqlSessionSqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@425ed15b] was not registered for synchronization because synchronization is not activeJDBC Connection [HikariProxyConnection@243999369 wrapping com.mysql.cj.jdbc.ConnectionImpl@55539d0] will not be managed by Spring==> Preparing: SELECT * FROM t_user WHERE (user_name = ?)==> Parameters: Tony2(String)<== Columns: id, user_name, age<== Row: 2, Tony2, 22<== Row: 3, Tony2, 22<== Row: 4, Tony2, 23<==Total: 3Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@425ed15b]Creating a new SqlSessionSqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@38c16ae] was not registered for synchronization because synchronization is not activeJDBC Connection [HikariProxyConnection@938269581 wrapping com.mysql.cj.jdbc.ConnectionImpl@55539d0] will not be managed by Spring==> Preparing: SELECT id,age FROM t_user WHERE (user_name = ?)==> Parameters: Tony2(String)<== Columns: id, age<== Row: 2, 22<== Row: 3, 22<== Row: 4, 23<==Total: 3Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@38c16ae]Creating a new SqlSessionSqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@53f471bc] was not registered for synchronization because synchronization is not activeJDBC Connection [HikariProxyConnection@808334346 wrapping com.mysql.cj.jdbc.ConnectionImpl@55539d0] will not be managed by Spring==> Preparing: SELECT id,age FROM t_user WHERE (user_name = ?) AND age = ?==> Parameters: Tony2(String), 22(Integer)<== Columns: id, age<== Row: 2, 22<== Row: 3, 22<==Total: 2Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@53f471bc]----------------------------------------------------------[User(id=2, userName=Tony2, age=22), User(id=3, userName=Tony2, age=22), User(id=4, userName=Tony2, age=23)][User(id=2, userName=Tony2, age=22), User(id=3, userName=Tony2, age=22), User(id=4, userName=Tony2, age=23)][User(id=2, userName=null, age=22), User(id=3, userName=null, age=22), User(id=4, userName=null, age=23)][User(id=2, userName=null, age=22), User(id=3, userName=null, age=22)]
不常用操作
代码
Mapper
package com.example.business.customsql.mapper;import com.baomidou.mybatisplus.core.conditions.Wrapper;import com.baomidou.mybatisplus.core.mapper.BaseMapper;import com.example.business.customsql.entity.User;import org.apache.ibatis.annotations.Param;import org.apache.ibatis.annotations.Select;import org.apache.ibatis.annotations.Update;import org.springframework.stereotype.Repository;import java.util.List;import java.util.Map;@Repositorypublic interface UserMapper extends BaseMapper<User> {// 指定某个条件，指定表名@Select("SELECT * FROM ${table_name} WHERE age = #{age}")List<User> testSelect5(@Param("table_name") String tableName, @Param("age") Integer age);// 条件放到wrapper，指定表名、所需字段，返回map@Select("SELECT ${ew.SqlSelect} FROM ${table_name} ${ew.customSqlSegment}")List<Map<String, Object>> testSelect6(@Param("table_name") String tableName, @Param("ew") Wrapper wrapper);// 只取name这一列@Select("SELECT t_user.user_name FROM t_user ${ew.customSqlSegment}")List<String> testSelect7(@Param("ew") Wrapper wrapper);// 条件放到wrapper，指定表名，只获取一个。若有多个则报错@Select("SELECT * FROM ${table_name} ${ew.customSqlSegment}")User testSelect8(@Param("table_name") String tableName, @Param("ew") Wrapper wrapper);// 测试更新@Update("UPDATE ${table_name} SET ${ew.sqlSet} ${ew.customSqlSegment}")int testUpdate1(@Param("table_name") String tableName, @Param("ew") Wrapper wrapper);}
Controller
package com.example.business.customsql;import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;import com.baomidou.mybatisplus.extension.conditions.query.LambdaQueryChainWrapper;import com.baomidou.mybatisplus.core.toolkit.Wrappers;import com.example.business.customsql.entity.User;import com.example.business.customsql.mapper.UserMapper;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.GetMapping;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RestController;import java.util.Arrays;import java.util.List;import java.util.Map;@RestController@RequestMapping("customSQL")public class CustomSQLController {@Autowiredprivate UserMapper userMapper;@GetMapping("test5678")public void test5678() {List<User> users1 = userMapper.testSelect5("t_user", 22);LambdaQueryWrapper<User> queryWrapper = Wrappers.lambdaQuery();queryWrapper.eq(User::getUserName, "Tony2").eq(User::getAge, 22).select(User::getUserName, User::getAge);// 写法2 有service时可以这么写，此写法推荐使用。// LambdaQueryChainWrapper<User> queryWrapper = userService// .lambdaQuery// .eq(User::getUserName, "Tony2")//.eq(User::getAge, 22)//.select(User::getId, User::getAge)// 写法3// QueryWrapper<User> queryWrapper = new QueryWrapper<>();// queryWrapper.eq("user_name", "Tony2").eq("age", 22);List<Map<String, Object>> users2 = userMapper.testSelect6("t_user", queryWrapper);List<String> userNames = userMapper.testSelect7(queryWrapper);LambdaQueryWrapper<User> queryWrapper1 = Wrappers.lambdaQuery();queryWrapper1.eq(User::getUserName, "Tony1").eq(User::getAge, 21).select(User::getUserName, User::getAge);User user = userMapper.testSelect8("t_user", queryWrapper1);System.out.println("----------------------------------------------------------");System.out.println(users1);System.out.println(users2);System.out.println(userNames);System.out.println(user);}}
测试
访问：http://localhost:8080/customSQL/test5678
结果
JDBC Connection [HikariProxyConnection@570520501 wrapping com.mysql.cj.jdbc.ConnectionImpl@54a994a6] will not be managed by Spring==> Preparing: SELECT * FROM t_user WHERE age = ?==> Parameters: 22(Integer)<== Columns: id, user_name, age<== Row: 2, Tony2, 22<== Row: 3, Tony2, 22<==Total: 2Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@55b1e390]Creating a new SqlSessionSqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3589afac] was not registered for synchronization because synchronization is not activeJDBC Connection [HikariProxyConnection@672917088 wrapping com.mysql.cj.jdbc.ConnectionImpl@54a994a6] will not be managed by Spring==> Preparing: SELECT user_name,age FROM t_user WHERE (user_name = ? AND age = ?)==> Parameters: Tony2(String), 22(Integer)<== Columns: user_name, age<== Row: Tony2, 22<== Row: Tony2, 22<==Total: 2Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3589afac]Creating a new SqlSessionSqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@61942f9f] was not registered for synchronization because synchronization is not activeJDBC Connection [HikariProxyConnection@1111642269 wrapping com.mysql.cj.jdbc.ConnectionImpl@54a994a6] will not be managed by Spring==> Preparing: SELECT t_user.user_name FROM t_user WHERE (user_name = ? AND age = ?)==> Parameters: Tony2(String), 22(Integer)<== Columns: user_name<== Row: Tony2<== Row: Tony2<==Total: 2Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@61942f9f]Creating a new SqlSessionSqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5cf8e912] was not registered for synchronization because synchronization is not activeJDBC Connection [HikariProxyConnection@622095606 wrapping com.mysql.cj.jdbc.ConnectionImpl@54a994a6] will not be managed by Spring==> Preparing: SELECT * FROM t_user WHERE (user_name = ? AND age = ?)==> Parameters: Tony1(String), 21(Integer)<== Columns: id, user_name, age<== Row: 1, Tony1, 21<==Total: 1Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5cf8e912]----------------------------------------------------------[User(id=2, userName=Tony2, age=22), User(id=3, userName=Tony2, age=22)][{user_name=Tony2, age=22}, {user_name=Tony2, age=22}][Tony2, Tony2]User(id=1, userName=Tony1, age=21)
IN的坑
简介
Mybatis-Plus无法在自定义SQL中直接使用IN查询，无论传参类型是List、ArrayList、数组、可变参数。
解决方法：将IN查询放到Wrapper中，然后传入自定义SQL。
代码
Mapper
package com.example.business.customsql.mapper;import com.baomidou.mybatisplus.core.conditions.Wrapper;import com.baomidou.mybatisplus.core.mapper.BaseMapper;import com.example.business.customsql.entity.User;import org.apache.ibatis.annotations.Param;import org.apache.ibatis.annotations.Select;import org.springframework.stereotype.Repository;import java.util.List;@Repositorypublic interface UserMapper extends BaseMapper<User> {// 错误做法。Mybatis-Plus不支持自定义SQL中使用IN，参数类型是List、ArrayList、数组、可变参数都不行@Select("SELECT t_user.user_name FROM t_user WHERE id IN (#{ids}) AND user_name = #{userName}")List<User> testIn1(@Param("ids") List<Long> ids, @Param("userName")String userName);// 错误做法。Mybatis-Plus不支持自定义SQL中使用IN，参数类型是List、ArrayList、数组、可变参数都不行@Select("SELECT t_user.user_name FROM t_user WHERE id IN (#{ids}) AND user_name = #{userName}")List<User> testIn2(@Param("userName") String userName, @Param("ids") Long... ids);@Select("SELECT t_user.user_name FROM t_user ${ew.customSqlSegment} AND user_name = #{userName}")List<User> testIn3(@Param("ew") Wrapper wrapper, @Param("userName")String userName);}
Controller
package com.example.business.customsql;import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;import com.baomidou.mybatisplus.core.toolkit.Wrappers;import com.example.business.customsql.entity.User;import com.example.business.customsql.mapper.UserMapper;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.GetMapping;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RestController;import java.util.Arrays;import java.util.List;@RestController@RequestMapping("customSQL")public class CustomSQLController {@Autowiredprivate UserMapper userMapper;@GetMapping("testIn")public void testIn() {Long[] ids = new Long[]{1L, 2L, 3L};List<Long> idList = Arrays.asList(ids);List<User> users1 = userMapper.testIn1(idList, "Tony2");List<User> users2 = userMapper.testIn2("Tony2", ids);LambdaQueryWrapper<User> queryWrapper = Wrappers.lambdaQuery();queryWrapper.in(User::getId, idList);List<User> users3 = userMapper.testIn3(queryWrapper, "Tony2");System.out.println("----------------------------------------------------------");System.out.println(users1);System.out.println(users2);System.out.println(users3);}}
测试
访问：http://localhost:8080/customSQL/testIn
结果
JDBC Connection [HikariProxyConnection@1841994020 wrapping com.mysql.cj.jdbc.ConnectionImpl@73e5ef65] will not be managed by Spring==> Preparing: SELECT t_user.user_name FROM t_user WHERE id IN (?) AND user_name = ?==> Parameters: [1, 2, 3](ArrayList), Tony2(String)<==Total: 0Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7b3d58a6]Creating a new SqlSessionSqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6e65ce0e] was not registered for synchronization because synchronization is not activeJDBC Connection [HikariProxyConnection@676282173 wrapping com.mysql.cj.jdbc.ConnectionImpl@73e5ef65] will not be managed by Spring==> Preparing: SELECT t_user.user_name FROM t_user WHERE id IN (?) AND user_name = ?==> Parameters: [Ljava.lang.Long;@e4762e9(Long[]), Tony2(String)<==Total: 0Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6e65ce0e]Creating a new SqlSessionSqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3eadc23b] was not registered for synchronization because synchronization is not activeJDBC Connection [HikariProxyConnection@1413127542 wrapping com.mysql.cj.jdbc.ConnectionImpl@73e5ef65] will not be managed by Spring==> Preparing: SELECT t_user.user_name FROM t_user WHERE (id IN (?,?,?)) AND user_name = ?==> Parameters: 1(Long), 2(Long), 3(Long), Tony2(String)<== Columns: user_name<== Row: Tony2<== Row: Tony2<==Total: 2Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3eadc23b]----------------------------------------------------------[][][User(id=null, userName=Tony2, age=null), User(id=null, userName=Tony2, age=null)]
动态查询
简介
代码里经常遇到动态查询（多条件查询）的情况，mybatis-plus在查单表的时候直接用代码拼接动态条件即可，但多表的时候就只能手写sql了。
本处示例如何手写sql来进行动态查询。本处示例单表自定义SQL动态查询，多表是一样的用法。
代码
mapper
package com.example.business.customsql.mapper;import com.baomidou.mybatisplus.core.conditions.Wrapper;import com.baomidou.mybatisplus.core.mapper.BaseMapper;import com.example.business.customsql.entity.User;import org.apache.ibatis.annotations.Param;import org.apache.ibatis.annotations.Select;import org.springframework.stereotype.Repository;import java.util.List;@Repositorypublic interface UserMapper extends BaseMapper<User> {// ----------------- 测试动态查询 ---------------------------------------------------------------------@Select("SELECT * FROM t_user WHERE " +" IF(#{userName} IS NOT NULL, t_user.user_name = #{userName}, 1=1)" +" AND IF(#{age} IS NOT NULL, t_user.age = #{age}, 1=1)")List<User> testDynamic1(@Param("userName") String userName, @Param("age") Integer age);}
controller
package com.example.business.customsql;import com.baomidou.mybatisplus.core.toolkit.Wrappers;import com.example.business.customsql.entity.User;import com.example.business.customsql.mapper.UserMapper;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.GetMapping;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RestController;import java.util.List;@RestController@RequestMapping("customSQL")public class CustomSQLController {@Autowiredprivate UserMapper userMapper;@GetMapping("testDynamic")public void testDynamic() {List<User> users = userMapper.testDynamic1(null, 22);System.out.println(users);}}
测试
访问：http:localhost:8080/customSQL/testDynamic
后端结果
JDBC Connection [HikariProxyConnection@245966168 wrapping com.mysql.cj.jdbc.ConnectionImpl@7e2d6fd9] will not be managed by Spring==> Preparing: SELECT * FROM t_user WHERE IF(? IS NOT NULL, t_user.user_name = ?, 1=1) AND IF(? IS NOT NULL, t_user.age = ?, 1=1)==> Parameters: null, null, 22(Integer), 22(Integer)<== Columns: id, user_name, age<== Row: 2, Tony2, 22<== Row: 3, Tony2, 22<==Total: 2Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3e93302e][User(id=2, userName=Tony2, age=22), User(id=3, userName=Tony2, age=22)]
其他网址
mybatis-plus之自定义sql、分页、Wrapper_CDN-CSDN博客_mybatisplus wrapper分页
Mybatisplus 自定义sql 使用条件构造器 - 静水165 - 博客园
