11-3阶内容-1.3-2-mybatisplus查询构造器

# 本节要点 - map.xml查询编写 - 熟练运用两种查询器 - 多数据源配置 - 认识Druid连接池 # 一、Mybatis plus的查询写mapper.xml查询(范例)，通过名字去查询 ## 1、编写 map.xml \`\`\` id,username,password,real_name,dept_id,status, create_time,update_time select from sys_user where 1=1 and username = #{username} \`\`\` ## 2、编写mapper对象 \`\`\` package com.example.demo.mapper; import com.example.demo.entity.SysUser; import com.baomidou.mybatisplus.core.mapper.BaseMapper; import java.util.List; /\*\* \* @author daiwei \* @description 针对表【sys_user(系统用户表)】的数据库操作Mapper \* @createDate 2025-11-24 11:27:09 \* @Entity com.example.demo.entity.SysUser \*/ public interface SysUserMapper extends BaseMapper { List getByCondition(SysUser sysUser); } \`\`\` ## 3、编写service接口 \`\`\` package com.example.demo.service; import com.example.demo.entity.SysUser; import com.baomidou.mybatisplus.extension.service.IService; import java.util.List; /\*\* \* @author daiwei \* @description 针对表【sys_user(系统用户表)】的数据库操作Service \* @createDate 2025-11-24 11:27:09 \*/ public interface SysUserService extends IService { //指定条件查询 List getByCondition(SysUser sysUser); } \`\`\` ## 4、编写serviceImpl接口实现注入当前业务的mapper \`\`\` @Autowired private SysUserMapper sysUserMapper; \`\`\` 完整代码如下: \`\`\` package com.example.demo.service.impl; import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl; import com.example.demo.entity.SysUser; import com.example.demo.service.SysUserService; import com.example.demo.mapper.SysUserMapper; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import java.util.List; /\*\* \* @author daiwei \* @description 针对表【sys_user(系统用户表)】的数据库操作Service实现 \* @createDate 2025-11-24 11:27:09 \*/ @Service public class SysUserServiceImpl extends ServiceImpl implements SysUserService{ @Autowired private SysUserMapper sysUserMapper; @Override public List getByCondition(SysUser sysUser) { return sysUserMapper.getByCondition(sysUser); } } \`\`\` ## 5、controller编写测试调用 \`\`\` package com.example.demo.controller; import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper; import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; import com.example.demo.aspect.PermessionAuth; import com.example.demo.entity.SysUser; import com.example.demo.service.SysUserService; import com.example.demo.util.ResultContent; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.RequestBody; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.bind.annotation.RestController; import java.util.List; @RestController @RequestMapping("/sysUser") public class SysUserController { @Autowired private SysUserService sysUserService; @RequestMapping("/saveUser") //@PermessionAuth("sys:user:save") public ResultContent saveUser(@RequestBody SysUser sysUser) { boolean result = sysUserService.save(sysUser); return ResultContent.success(result); } @RequestMapping("/getById") public ResultContent getById(@RequestParam Integer id) { SysUser sysUser = sysUserService.getById(id); return ResultContent.success(sysUser); } @RequestMapping("/getAll") public ResultContent getAll() { List alist = sysUserService.list(); return ResultContent.success(alist); } @RequestMapping("/getByCondition") public ResultContent getByCondition(@RequestBody SysUser sysUser) { List alist = sysUserService.getByCondition(sysUser); return ResultContent.success(alist); } } \`\`\` # 二、条件构造器(wrapper)常见的Wrapper类型： ## 1、查询构造器 https://baomidou.com/guides/wrapper/ 用来写复杂的SQL，Wapper 是一个接口，底下有很多的实现类，查询用QueryWrapper类 - \*\*QueryWrapper\*\* \`\`\` QueryWrapper wrapper = new QueryWrapper\<\>(); 两种一样的效果 QueryWrapper wrapper = Wrappers.query(); \`\`\` 编写查询方法 \`\`\` @RequestMapping("/searchUser2") public ResultContent searchUser2(@RequestBody SysUser sysUser) { QueryWrapper queryWrapper = Wrappers.query(); queryWrapper.eq("username",sysUser.getUsername()).or() .eq("real_name",sysUser.getRealName()); List alist =sysUserService.list(queryWrapper); return ResultContent.success(alist); } \`\`\` - \*\*LambdaQueryWrapper\*\*(可选) \`\`\` LambdaQueryWrapper wrapper2 = new LambdaQueryWrapper\<\>(); or LambdaQueryWrapper wrapper2 = Wrappers.lambdaQuery(); \`\`\` \`\`\` @RequestMapping("/searchUser") public ResultContent searchUser(@RequestBody SysUser sysUser) { LambdaQueryWrapper lambdaQueryWrapper = Wrappers.lambdaQuery(); lambdaQueryWrapper.like(SysUser::getUsername, sysUser.getUsername()) .or() .like(SysUser::getRealName, sysUser.getRealName()); List alist =sysUserService.list(lambdaQueryWrapper); return ResultContent.success(alist); }urn ipage; } \`\`\` ## 2、用法简要说明 \`\`\` @GetMapping("/query") public List queryUsers(@RequestParam("name") String name) { LambdaQueryWrapper wrapper = new LambdaQueryWrapper\<\>(); wrapper.like(Product::getProductName, name); return productService.list(wrapper); } \`\`\` \`\`\`\` // 等于 wrapper.eq(Product::getStatus, 1); // 不等于 wrapper.ne(Product::getPrice, 0); // 大于 wrapper.gt(Product::getPrice, 100); // 大于等于 wrapper.ge(Product::getStock, 10); // 小于 wrapper.lt(Product::getPrice, 1000); // 小于等于 wrapper.le(Product::getSales, 500); \`\`\` \`\`\`\` 组合查询 \*\*默认是and\*\* \`\`\` LambdaQueryWrapper wrapper = new LambdaQueryWrapper\<\>(); wrapper.eq(Product::getStatus, 1) .ge(Product::getPrice, 100) .le(Product::getPrice, 1000) .like(Product::getProductName, "华为") .in(Product::getCategoryId, Arrays.asList(1, 2, 3)) .isNotNull(Product::getImageUrl) .orderByDesc(Product::getSales) .orderByAsc(Product::getPrice) .last("LIMIT 10"); \`\`\` or \`\`\` // OR 连接多个条件 wrapper.like(Product::getProductName, "华为") .or() .like(Product::getProductName, "小米") .or() .like(Product::getProductName, "苹果"); // 相当于：product_name LIKE '%华为%' OR product_name LIKE '%小米%' OR product_name LIKE '%苹果%' \`\`\` 查询范围 \`\`\` // BETWEEN 范围查询 wrapper.between(Product::getPrice, 50, 200); // NOT BETWEEN wrapper.notBetween(Product::getCreateTime, LocalDateTime.of(2023,1,1,0,0), LocalDateTime.of(2023,12,31,23,59)); // IN 查询 wrapper.in(Product::getCategoryId, Arrays.asList(1, 2, 3)); // NOT IN 查询 wrapper.notIn(Product::getId, Arrays.asList(10, 20, 30)); \`\`\` 模糊查询 \`\`\` // LIKE %value% wrapper.like(Product::getProductName, "手机"); // NOT LIKE wrapper.notLike(Product::getDescription, "二手"); // LEFT LIKE value% wrapper.likeLeft(Product::getProductCode, "PROD2023"); // RIGHT LIKE %value wrapper.likeRight(Product::getTags, "热销"); \`\`\` 空值判断 \`\`\` // IS NULL wrapper.isNull(Product::getDeleteTime); // IS NOT NULL wrapper.isNotNull(Product::getUpdateTime); \`\`\` 分组 \`\`\` // 排序 wrapper.orderByAsc(Product::getPrice); // 升序 wrapper.orderByDesc(Product::getSales); // 降序 // 分组 wrapper.groupBy(Product::getCategoryId); \`\`\` 逻辑 \`\`\` // AND 连接（默认） wrapper.eq(Product::getStatus, 1) .gt(Product::getPrice, 100); // OR 连接 wrapper.eq(Product::getCategoryId, 1) .or() .eq(Product::getCategoryId, 2); // 嵌套条件 wrapper.nested(i -\> i.eq(Product::getStatus, 1).gt(Product::getPrice, 100)) .or(j -\> j.eq(Product::getStatus, 2).lt(Product::getPrice, 50)); \`\`\` 复杂查询 \`\`\` // EXISTS 子查询 wrapper.exists("SELECT 1 FROM category c WHERE c.id = category_id AND c.status = 1"); // 自定义SQL片段 wrapper.apply("DATE(create_time) = '2023-10-01'"); // HAVING 条件 wrapper.groupBy(Product::getCategoryId) .having("COUNT(\*) \> 10"); \`\`\` # 三、分页查询 ## 1、无需加依赖已经集成只需要定义一个page对象和返回IPage就可以 \`\`\` Page page = new Page\<\>(1, 5); //第一个参数是第几页，第二个参数是一页有多少数据 \`\`\` \`\`\` IPage alist_page = userInfoService.page(page); //用 service自带的page方法查询你，返回一个IPage集合对象 \`\`\` ## 2、增加分页插件配置官网 https://baomidou.com/plugins/pagination/ \`\`\` package com.example.demo.config; import com.baomidou.mybatisplus.annotation.DbType; import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor; import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; @Configuration public class MybatisConfig { @Bean public MybatisPlusInterceptor mybatisPlusInterceptor() { MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor(); //创建分页拦截器 interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL)); //专门处理分页逻辑的拦截器，以及指定数据库类型为 MySQL return interceptor; } } \`\`\` ## 3、分页代码 \`\`\` //分页查询 @RequestMapping("/page") public ResultContent page(@RequestBody SysUser sysUser) { Page page = new Page\<\>(sysUser.getPageNum(), sysUser.getPageSize()); LambdaQueryWrapper lambdaQueryWrapper = Wrappers.lambdaQuery(); lambdaQueryWrapper.like(SysUser::getUsername, sysUser.getUsername()); IPage pageResult = sysUserService.page(page, lambdaQueryWrapper); return ResultContent.success(pageResult); } \`\`\` # 四、\*\*常用wrapper方法\*\* ### \*\*1 比较条件方法：\*\* !\[img\](https://woniumd.oss-cn-hangzhou.aliyuncs.com/daiwei/java/20250930165113356.jpg) ### \*\*2\*\* \*\*空值判断方法\*\* !\[img\](https://woniumd.oss-cn-hangzhou.aliyuncs.com/daiwei/java/20250930165113378.jpg) ### \*\*3 IN相关方法\*\* !\[img\](https://woniumd.oss-cn-hangzhou.aliyuncs.com/daiwei/java/20250930165113372.jpg) ### \*\*4 逻辑操作方法\*\* !\[img\](https://woniumd.oss-cn-hangzhou.aliyuncs.com/daiwei/java/20250930165113394.jpg) ### \*\*5 排序方法\*\* !\[img\](https://woniumd.oss-cn-hangzhou.aliyuncs.com/daiwei/java/20250930165113399.jpg) ### \*\*6 其他常用方法\*\* !\[img\](https://woniumd.oss-cn-hangzhou.aliyuncs.com/daiwei/java/20250930165113404.jpg) 修改：UpdateWrapper 或 LambdaUpdateWrapper Wrappers.update() 或new UpdateWrapper\<\>() Wrappers.lambdaUpdate() 或 new LambdaUpdateWrapper\<\>(); # 五、多数据源有时候我们会让系统同时连接多个数据库的场景。比如：系统需要从一个三方库里抽取数据；抽取数据后再写入自己创建的数据库里 !\[image-20251125141242949\](https://woniumd.oss-cn-hangzhou.aliyuncs.com/daiwei/java/20251125141243017.png) ## 1、加依赖 https://baomidou.com/guides/dynamic-datasource/#dynamic-datasource \`\`\` com.baomidou dynamic-datasource-spring-boot3-starter 4.2.0 \`\`\` ## 2、加配置 \`\`\` server: port: 9090 spring: profiles: active: dev application: name: deme datasource: //注意一下：和application是同一级，都在spring下 dynamic: primary: master datasource: master: url: jdbc:mysql://localhost:3306/springboot?useUnicode=true\&characterEncoding=utf8\&serverTimezone=Asia/Shanghai username: root password: daiwei driver-class-name: com.mysql.cj.jdbc.Driver slave: url: jdbc:mysql://localhost:3306/springboot_ext?useUnicode=true\&characterEncoding=utf8\&serverTimezone=Asia/Shanghai username: root password: daiwei driver-class-name: com.mysql.cj.jdbc.Driver mybatis-plus: mapper-locations: classpath\*:mapper/\*.xml type-aliases-package: com.example.demo.entity configuration: map-underscore-to-camel-case: true logging: level: root: info com.example.demo.mapper: debug \`\`\` ## 3、加注解可以加在\*\*方法上\*\*也可以在\*\*类\*\*上 @DS("master") \`\`\` @Service //@DS("slave") public class ProductServiceImpl extends ServiceImpl implements ProductService { @Override @DS("master") public int reduceProduct(Long id) { return baseMapper.reduceProduct(id); } } \`\`\` # 六、Druid连接池 ## 1、spring boot 3.2.4自带\*\*hikari\*\*数据源，也是默认连接池 \`\`\` spring: profiles: active: dev application: name: demo datasource: username: root password: daiwei url: jdbc:mysql://localhost:3306/springboot?useUnicode=true\&characterEncoding=utf8\&serverTimezone=Asia/Shanghai driver-class-name: com.mysql.cj.jdbc.Driver # 8.x驱动类 type: com.zaxxer.hikari.HikariDataSource #不用配置默认就是hikari，也可以手动加入配置 hikari: pool-name: MyHikariPool minimum-idle: 5 maximum-pool-size: 20 idle-timeout: 300000 max-lifetime: 1200000 connection-timeout: 30000 \`\`\` ## 2、为什么要用Druid连接池？ https://github.com/alibaba/druid/wiki/%E5%B8%B8%E8%A7%81%E9%97%AE%E9%A2%98 Druid 是阿里巴巴开源的一款强大的\*\*数据库连接池\*\*和\*\*监控工具\*\*。它提供了高性能、高可靠性的数据库连接管理能力。 \*\*监控统计\*\*： - SQL 监控 - Web 应用监控 - Session 监控 - Spring 监控 \*\*防御功能\*\*： - SQL 注入防御 - WallFilter 防火墙 \*\*性能优化\*\*： - 连接池优化 - PSCache 优化 ## 3、如何使用 ### 1、加依赖 \`\`\` com.alibaba druid 1.2.20 \`\`\` ### 2、加配置在数据库连接配置下面加上druid的配置即可 \`\`\` ...#在数据连接的后面加，druid的配置 type: com.alibaba.druid.pool.DruidDataSource druid: initial-size: 5 //初始连接数 min-idle: 5 //最小空闲连接数 max-active: 20 //最大活跃连接数 max-wait: 60000 //获取连接最大等待时间 time-between-eviction-runs-millis: 60000 //空闲连接检查间隔 min-evictable-idle-time-millis: 300000 //连接最小空闲时间 validation-query: SELECT 1 FROM DUAL test-while-idle: true test-on-borrow: false test-on-return: false filters: stat,wall,slf4j \`\`\` 完整配置如下： - 单数据源 \`\`\` spring: profiles: active: dev application: name: demo datasource: username: root password: daiwei url: jdbc:mysql://localhost:3306/springboot?useUnicode=true\&characterEncoding=utf8\&serverTimezone=Asia/Shanghai driver-class-name: com.mysql.cj.jdbc.Driver # 8.x驱动类 type: com.alibaba.druid.pool.DruidDataSource druid: initial-size: 5 min-idle: 5 max-active: 20 max-wait: 60000 time-between-eviction-runs-millis: 60000 min-evictable-idle-time-millis: 300000 validation-query: SELECT 1 FROM DUAL test-while-idle: true test-on-borrow: false test-on-return: false filters: stat,wall,slf4j \`\`\` - 多数据源 \`\`\` server: port: 9090 spring: profiles: active: dev application: name: demo datasource: dynamic: primary: master datasource: master: url: jdbc:mysql://localhost:3306/springboot?useUnicode=true\&characterEncoding=utf8\&serverTimezone=Asia/Shanghai username: root password: daiwei driver-class-name: com.mysql.cj.jdbc.Driver type: com.alibaba.druid.pool.DruidDataSource druid: initial-size: 5 min-idle: 5 max-active: 20 max-wait: 60000 time-between-eviction-runs-millis: 60000 min-evictable-idle-time-millis: 300000 validation-query: SELECT 1 FROM DUAL test-while-idle: true test-on-borrow: false test-on-return: false filters: stat,wall,slf4j slave: url: jdbc:mysql://localhost:3306/springboot_ext?useUnicode=true\&characterEncoding=utf8\&serverTimezone=Asia/Shanghai username: root password: daiwei driver-class-name: com.mysql.cj.jdbc.Driver mybatis-plus: mapper-locations: classpath\*:mapper/\*.xml type-aliases-package: com.example.demo.entity configuration: map-underscore-to-camel-case: true logging: level: root: info com.example.demo.mapper: debug \`\`\`

如果觉得文章对你有用，请随意赞赏

知识分享

11-3阶内容-1.3-2-mybatisplus查询构造器

https://xiaochenblog.icu/archives/11-3jie-nei-rong-1.3-2-mybatispluscha-xun-gou-zao-qi

作者

xiaochen

发布于

2026-06-27 16:57

更新于

2026-06-27 16:56

许可协议

CC BY 4.0

作者

发布于

更新于

许可协议

评论