简介
说明
本文用实例介绍MybatisPlus如何自定义SQL。
使用
自定义的sql使用Wrapper对版本有要求：mybatis-plus版本需要大于或等于3.0.7。
| 值 | 含义 |
| ew.customSqlSegment | 条件构造器（Wrapper） |
| ew.sqlSet | 所设置的列 |
| ew.sqlSelect | 要select的列 |
特殊语句
| 操作 | 实例 | 说明 |
| TRUNCATE | @Update(“TRUNCATE xxx”) | 不要用@Delete |
| ALTER | @Update(“ALTER xxx”) | |
| DELETE | @Delete(“DELETE xxx”) |
源码
包里边定义好的常量。我们可以直接用这些常量。
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;
@Repository
public 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
@Autowired
private 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@qq.com",
"createTime": "2021-01-12T13:12:21",
"updateTime": "2021-01-12T13:12:21",
"deletedFlag": 0
},
{
"id": 4,
"userName": "bb",
"nickName": "昵称2",
"email": "444@qq.com",
"createTime": "2021-02-11T18:12:21",
"updateTime": "2021-02-11T18:12:21",
"deletedFlag": 0
},
{
"id": 5,
"userName": "cc",
"nickName": "昵称3",
"email": "555@qq.com",
"createTime": "2021-03-24T18:12:21",
"updateTime": "2021-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 SqlSession SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@699ca44c] was not registered for synchronization because synchronization is not active JDBC 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@qq.com, 2021-01-12 13:12:21, 2021-01-12 13:12:21, 0 <== Row: 4, bb, 昵称2, 444@qq.com, 2021-02-11 18:12:21, 2021-02-11 18:12:21, 0 <== Row: 5, cc, 昵称3, 555@qq.com, 2021-03-24 18:12:21, 2021-03-24 18:12:21, 0 <== Total: 3 Closing 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;
@Repository
public 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 {
@Autowired
private UserMapper userMapper;
@Autowired
private 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: 3 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@17030d24] Creating a new SqlSession SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@425ed15b] was not registered for synchronization because synchronization is not active JDBC 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: 3 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@425ed15b] Creating a new SqlSession SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@38c16ae] was not registered for synchronization because synchronization is not active JDBC 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: 3 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@38c16ae] Creating a new SqlSession SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@53f471bc] was not registered for synchronization because synchronization is not active JDBC 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: 2 Closing 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;
@Repository
public 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 {
@Autowired
private 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: 2
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@55b1e390]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3589afac] was not registered for synchronization because synchronization is not active
JDBC 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: 2
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3589afac]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@61942f9f] was not registered for synchronization because synchronization is not active
JDBC 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: 2
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@61942f9f]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5cf8e912] was not registered for synchronization because synchronization is not active
JDBC 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: 1
Closing 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;
@Repository
public 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 {
@Autowired
private 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: 0 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7b3d58a6] Creating a new SqlSession SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6e65ce0e] was not registered for synchronization because synchronization is not active JDBC 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: 0 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6e65ce0e] Creating a new SqlSession SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3eadc23b] was not registered for synchronization because synchronization is not active JDBC 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: 2 Closing 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;
@Repository
public 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 {
@Autowired
private 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: 2 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3e93302e] [User(id=2, userName=Tony2, age=22), User(id=3, userName=Tony2, age=22)]
请先 ！