当前位置： 首页 > news >正文

Spring Boot中MyBatis Provider注解实现动态SQL

news 2025/8/30 5:24:56

项目配置与依赖

1. Maven依赖配置 (pom.xml)

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>2.7.0</version><relativePath/></parent><groupId>com.example</groupId><artifactId>mybatis-dynamic-sql-demo</artifactId><version>1.0.0</version><name>mybatis-dynamic-sql-demo</name><description>Demo project for MyBatis dynamic SQL with Spring Boot</description><properties><java.version>11</java.version><mybatis-spring-boot.version>2.2.2</mybatis-spring-boot.version><mysql-connector.version>8.0.29</mysql-connector.version><lombok.version>1.18.24</lombok.version></properties><dependencies><!-- Spring Boot Starter --><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><!-- MyBatis Spring Boot Starter --><dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>${mybatis-spring-boot.version}</version></dependency><!-- MySQL Connector --><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>${mysql-connector.version}</version><scope>runtime</scope></dependency><!-- Lombok --><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>${lombok.version}</version><scope>provided</scope></dependency><!-- Test Dependencies --><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency><!-- HikariCP (连接池，Spring Boot 2.x默认使用) --><dependency><groupId>com.zaxxer</groupId><artifactId>HikariCP</artifactId></dependency></dependencies><build><plugins><plugin><groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId></plugin></plugins></build>
</project>

2. YAML配置文件 (application.yml)

# 应用配置
spring:application:name: mybatis-dynamic-sql-demo# 数据源配置datasource:url: jdbc:mysql://localhost:3306/testdb?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghaiusername: rootpassword: passworddriver-class-name: com.mysql.cj.jdbc.Driverhikari:# 连接池配置maximum-pool-size: 20minimum-idle: 5connection-timeout: 30000idle-timeout: 600000max-lifetime: 1800000pool-name: MyBatisHikariCP# 开发环境配置profiles:active: dev# MyBatis配置
mybatis:# mapper.xml文件位置mapper-locations: classpath:mapper/*.xml# 实体类别名包路径type-aliases-package: com.example.demo.entity# 开启驼峰命名转换configuration:map-underscore-to-camel-case: true# 其他配置log-impl: org.apache.ibatis.logging.stdout.StdOutImpl# 日志配置
logging:level:com.example.demo.mapper: DEBUGorg.springframework.web: INFOpattern:console: "%d{yyyy-MM-dd HH:mm:ss} [%thread] %-5level %logger{36} - %msg%n"# 服务器配置
server:port: 8080servlet:context-path: /api

3. 实体类

package com.example.demo.entity;import lombok.Data;
import java.util.Date;@Data
public class User {private Long id;private String name;private Integer age;private String email;private Date createTime;private Date updateTime;// 构造方法public User() {}public User(String name, Integer age, String email) {this.name = name;this.age = age;this.email = email;}
}

4. Mapper接口

package com.example.demo.mapper;import com.example.demo.entity.User;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;import java.util.List;@Mapper
@Repository
public interface UserMapper {@SelectProvider(type = UserSqlProvider.class, method = "buildGetUsersByCondition")List<User> selectByCondition(User user);@UpdateProvider(type = UserSqlProvider.class, method = "buildUpdateUser")int update(User user);@InsertProvider(type = UserSqlProvider.class, method = "buildInsertUser")@Options(useGeneratedKeys = true, keyProperty = "id")int insert(User user);@DeleteProvider(type = UserSqlProvider.class, method = "buildDeleteUser")int delete(Long id);// 复杂查询示例@SelectProvider(type = UserSqlProvider.class, method = "buildGetUsersWithPage")List<User> selectWithPage(@Param("user") User user, @Param("offset") Integer offset, @Param("limit") Integer limit);
}

5. SQL提供者类

package com.example.demo.mapper;import com.example.demo.entity.User;
import org.apache.ibatis.jdbc.SQL;public class UserSqlProvider {public String buildGetUsersByCondition(User user) {return new SQL() {{SELECT("*");FROM("user");if (user.getName() != null && !user.getName().trim().isEmpty()) {WHERE("name like CONCAT('%', #{name}, '%')");}if (user.getAge() != null) {WHERE("age = #{age}");}if (user.getEmail() != null && !user.getEmail().trim().isEmpty()) {WHERE("email like CONCAT('%', #{email}, '%')");}ORDER_BY("create_time DESC");}}.toString();}public String buildUpdateUser(User user) {return new SQL() {{UPDATE("user");if (user.getName() != null) {SET("name = #{name}");}if (user.getAge() != null) {SET("age = #{age}");}if (user.getEmail() != null) {SET("email = #{email}");}SET("update_time = NOW()");WHERE("id = #{id}");}}.toString();}public String buildInsertUser(User user) {return new SQL() {{INSERT_INTO("user");if (user.getName() != null) {VALUES("name", "#{name}");}if (user.getAge() != null) {VALUES("age", "#{age}");}if (user.getEmail() != null) {VALUES("email", "#{email}");}VALUES("create_time", "NOW()");VALUES("update_time", "NOW()");}}.toString();}public String buildDeleteUser(Long id) {return new SQL() {{DELETE_FROM("user");WHERE("id = #{id}");}}.toString();}public String buildGetUsersWithPage(User user, Integer offset, Integer limit) {String sql = new SQL() {{SELECT("*");FROM("user");if (user.getName() != null && !user.getName().trim().isEmpty()) {WHERE("name like CONCAT('%', #{user.name}, '%')");}if (user.getAge() != null) {WHERE("age = #{user.age}");}if (user.getEmail() != null && !user.getEmail().trim().isEmpty()) {WHERE("email like CONCAT('%', #{user.email}, '%')");}ORDER_BY("create_time DESC");}}.toString();// 添加分页if (offset != null && limit != null) {sql += " LIMIT #{offset}, #{limit}";}return sql;}
}

6. Service层

package com.example.demo.service;import com.example.demo.entity.User;
import com.example.demo.mapper.UserMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;import java.util.List;@Service
public class UserService {@Autowiredprivate UserMapper userMapper;public List<User> getUsersByCondition(User user) {return userMapper.selectByCondition(user);}public int updateUser(User user) {return userMapper.update(user);}public int addUser(User user) {return userMapper.insert(user);}public int deleteUser(Long id) {return userMapper.delete(id);}public List<User> getUsersWithPage(User user, Integer page, Integer size) {Integer offset = (page - 1) * size;return userMapper.selectWithPage(user, offset, size);}
}

7. Controller层

package com.example.demo.controller;import com.example.demo.entity.User;
import com.example.demo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;import java.util.HashMap;
import java.util.List;
import java.util.Map;@RestController
@RequestMapping("/users")
public class UserController {@Autowiredprivate UserService userService;@GetMappingpublic Map<String, Object> getUsersByCondition(@RequestParam(required = false) String name,@RequestParam(required = false) Integer age,@RequestParam(required = false) String email,@RequestParam(defaultValue = "1") Integer page,@RequestParam(defaultValue = "10") Integer size) {User user = new User();user.setName(name);user.setAge(age);user.setEmail(email);List<User> users;long total = 0;if (page != null && size != null) {users = userService.getUsersWithPage(user, page, size);// 实际项目中应查询总数total = users.size();} else {users = userService.getUsersByCondition(user);total = users.size();}Map<String, Object> result = new HashMap<>();result.put("code", 200);result.put("message", "success");result.put("data", users);result.put("total", total);return result;}@PutMapping("/{id}")public Map<String, Object> updateUser(@PathVariable Long id, @RequestBody User user) {user.setId(id);int result = userService.updateUser(user);Map<String, Object> response = new HashMap<>();response.put("code", result > 0 ? 200 : 500);response.put("message", result > 0 ? "更新成功" : "更新失败");response.put("data", result);return response;}@PostMappingpublic Map<String, Object> addUser(@RequestBody User user) {int result = userService.addUser(user);Map<String, Object> response = new HashMap<>();response.put("code", result > 0 ? 200 : 500);response.put("message", result > 0 ? "添加成功" : "添加失败");response.put("data", user);return response;}@DeleteMapping("/{id}")public Map<String, Object> deleteUser(@PathVariable Long id) {int result = userService.deleteUser(id);Map<String, Object> response = new HashMap<>();response.put("code", result > 0 ? 200 : 500);response.put("message", result > 0 ? "删除成功" : "删除失败");response.put("data", result);return response;}
}

8. 主应用类

package com.example.demo;import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;@SpringBootApplication
@MapperScan("com.example.demo.mapper")
public class MybatisDynamicSqlApplication {public static void main(String[] args) {SpringApplication.run(MybatisDynamicSqlApplication.class, args);}
}

总结

本教程详细介绍了如何在Spring Boot项目中使用YAML配置方式整合MyBatis，并通过@SelectProvider、@UpdateProvider等注解实现动态SQL拼接。相比XML配置方式，这种方法更加灵活，可以利用Java的全部功能来构建复杂的SQL语句，同时保持代码的类型安全和可维护性。

关键点：

  1. 使用YAML配置数据源和MyBatis属性

  2. 通过Provider类实现动态SQL拼接

  3. 使用MyBatis的SQL类构建类型安全的SQL语句

  4. 实现分页查询和条件查询

http://www.xdnf.cn/news/1384345.html

相关文章：

  • 云手机中的多开功能具体是指什么？
  • DVWA靶场通关笔记-暴力破解(Impossible级别)
  • Android 14 PMS源码分析
  • 临床研究三千问——如何将临床问题转换成科学问题（7）
  • 【网络安全领域】边界安全是什么？目前的发展及应用场景
  • Nessus 是一款免费功能强大的漏洞扫描工具，广泛用于网络安全评估。
  • eslasticsearch+ik分词器+kibana
  • 【MySQL】练习12-2：配置复制
  • 国产数据库转型指南：DBA技能重构与职业发展
  • Unity RectTransform容易混淆的基础问题
  • 3471. 找出最大的几近缺失整数
  • MyBatis延迟加载
  • LaunchScreen是啥？AppDelegate是啥？SceneDelegate是啥？ContentView又是啥？Main.storyboard是啥？
  • DoIP路由激活报文
  • 玄机靶场 | 第九章-blueteam 的小心思3
  • day083-Filebeat数据采集案例与Kibana可视化应用
  • 创建uniApp小程序项目vue3+ts+uniapp
  • Docker 核心技术：Union File System
  • ros2与gazebo harmonic机械臂仿真项目Moveit2YoloObb的优化
  • 有限与无限的游戏 之感
  • Rust 登堂 之 类型转换（三）
  • CnSTD+CnOCR的联合使用
  • 虚拟机逃逸攻防演练技术文章大纲
  • 编写TreeMap自定义排序的插曲
  • InstructGPT：使用人类反馈训练语言模型以遵循指令
  • 文本处理与模型对比：BERT, Prompt, Regex, TF-IDF
  • 在angular20中使用monaco-editor
  • 任天堂NDS中文游戏ROM精选毕业整合包整理分享！ +NDS模拟器
  • Maven 编译打包一个比较有趣的问题
  • 机器视觉学习-day08-图像缩放