零、知识介绍
动态SQL其实就是硬编码，不用xml方式，不用注解方式，可以更加灵活多变的使用java代码进行判断，业务逻辑处理生成一个SQL。
硬编码的执行速度比xml方式快，并且可以进行
debug
盘查错误，较全注解方式更加灵活，并且
可读性高
，缺点可能就是不流行，不能和xml接口一起使用，mybatis 3.4以上支持
多参数
，3.4以下，只能使用map传值。
因为是生成字符串SQL，依然存在SQL注入问题，所以必须使用
” username = #{username}”
，不能使用
” username = ” + username
方式
一、依赖准备
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.0.1.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--SpingBoot集成junit测试的起步依赖-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!--mybatis起步依赖-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.1.1</version>
</dependency>
<!-- MySQL连接驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
</dependencies>
二、配置准备
application.yml
spring:
datasource:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false
username: root
password: 123
mvc:
view:
suffix: .jsp
prefix: /
resources:
static-locations: classpath:templates
mybatis:
type-aliases-package: com.hikktn.domain
mapper-locations: classpath:mapper/*Mapper.xml
configuration:
map-underscore-to-camel-case: true #开启自动驼峰命名规则（camel case）映射
lazy-loading-enabled: true #开启延时加载开关
aggressive-lazy-loading: false #将积极加载改为消极加载（即按需加载）,默认值就是false
lazy-load-trigger-methods: "" #阻挡不相干的操作触发，实现懒加载
cache-enabled: true #打开全局缓存开关（二级环境），默认值就是true
logging:
level:
com.hikktn: debug
server:
port: 8080
servlet:
context-path: /
log4j.properties
#---- global logging configuration
#---- level: FATAL,ERROR,WARN,INFO,DEBUG
#---- appender: console, file, mail
### set log levels ###
log4j.rootLogger=INFO,console
### 输出到控制台 ###
log4j.appender.stdout.encoding=UTF-8
log4j.appender.console=org.apache.log4j.ConsoleAppender
log4j.appender.console.Target=System.out
log4j.appender.console.layout=org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=%d{yyyy-MM-dd HH\:mm\:ss} %5p %c{1}:%L - %m%n
### 输出到日志文件 ###
#log4j.appender.file=org.apache.log4j.DailyRollingFileAppender
#log4j.appender.file.File=${webapp.root}/WEB-INF/logs/platform.log
#log4j.appender.file.DatePattern=_yyyyMMdd'.log'
#log4j.appender.file.Append = true
#log4j.appender.file.Threshold = INFO
#log4j.appender.file.layout=org.apache.log4j.PatternLayout
#log4j.appender.file.layout.ConversionPattern=%-d{yyyy-MM-dd HH\:mm\:ss} [ %t\:%r ] - [ %p ] %m%n
### 打印SQL ###
#log4j.logger.com.ibatis=DEBUG
#log4j.logger.com.ibatis.common.jdbc.SimpleDataSource=DEBUG
#log4j.logger.com.ibatis.common.jdbc.ScriptRunner=DEBUG
#log4j.logger.com.ibatis.sqlmap.engine.impl.SqlMapClientDelegate=DEBUG
log4j.logger.java.sql.Connection=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG
#log4j.logger.java.sql.ResultSet=DEBUG
#配置logger扫描的包路径 这样才会打印sql
log4j.logger.com.hikktn.mapper=DEBUG
三、数据准备
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, 'user', 'user');
INSERT INTO `user` VALUES (2, 'admin', 'admin');
INSERT INTO `user` VALUES (3, 'hikktn', 'hikktn0320');
SET FOREIGN_KEY_CHECKS = 1;
四、pojo准备
package com.hikktn.domain;
import java.io.Serializable;
public class User implements Serializable {
private static final long serialVersionUID = 1L;
private Long id;
private String username;
private String password;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
'}';
}
}
五、mapper接口准备
@Mapper
@Repository
public interface UserMapper {
/**
* 动态查询
*
* @param param
* @return
*/
@SelectProvider(type = UserDynaSqlProviderBuilder.class, method = "buildSelectUserWithParam")
public List<User> selectUserInfoWithParam(Map<String, Object> param);
/**
* 动态增加,并自动获取自增主键
*
* @param user
* @return
*/
@InsertProvider(type = UserDynaSqlProviderBuilder.class, method = "buildInsertUser")
@Options(useGeneratedKeys = true, keyColumn = "id", keyProperty = "id")
public int insertUser(User user);
/**
* 动态根据id查询
*
* @param param
* @return
*/
@Results({
@Result(column = "id",property = "id"),
@Result(column = "username",property = "username"),
@Result(column = "password",property = "password")
})
@SelectProvider(type = UserDynaSqlProviderBuilder.class, method = "buildSelectUserWithParam")
public User selectUserWithId(Map<String, Object> param);
/**
* 动态修改
*
* @param user
*/
@UpdateProvider(type = UserDynaSqlProviderBuilder.class, method = "buildUpdateUser")
public int updateUser(User user);
/**
* 动态删除
*
* @param param
*/
@DeleteProvider(type = UserDynaSqlProviderBuilder.class, method = "buildDeleteUser")
public int deleteUser(Map<String, Object> param);
}
六、动态SQL准备
public class UserDynaSqlProviderBuilder {
/**
* 动态查询
*
* @param param
* @return
*/
public String buildSelectUserWithParam(Map<String, Object> param) {
return new SQL() {
{
SELECT("*");
FROM("user");
if (param.get("id") != null) {
WHERE(" id = #{id} ");
}
if (param.get("username") != null) {
WHERE(" username = #{username} ");
}
if (param.get("password") != null) {
WHERE(" password = #{password} ");
}
}
}.toString();
}
/**
* 动态增加
*
* @param user
* @return
*/
public String buildInsertUser(User user) {
return new SQL() {
{
INSERT_INTO("user");
if (user.getUsername() != null) {
VALUES("username", "#{username}");
}
if (user.getPassword() != null) {
VALUES("password", "#{password}");
}
}
}.toString();
}
/**
* 动态修改
*
* @param user
* @return
*/
public String buildUpdateUser(User user) {
return new SQL() {
{
UPDATE("user");
if (user.getUsername() != null) {
SET("username = #{username}");
}
if (user.getPassword() != null) {
SET("password = #{password}");
}
WHERE(" id = #{id} ");
}
}.toString();
}
/**
* 动态删除
* @param param
* @return
*/
public String buildDeleteUser(Map<String, Object> param) {
return new SQL() {
{
DELETE_FROM("user");
if (param.get("id") != null) {
WHERE(" id = #{id} ");
}
if (param.get("username") != null) {
WHERE(" username = #{username}");
}
}
}.toString();
}
}
七、测试用例准备
@RunWith(SpringRunner.class)
@SpringBootTest(classes = SpringbootMybatisApplication.class)
public class MybatisTest {
@Autowired
private UserMapper userMapper;
/**
* 动态查询
*/
@Test
public void testSelectUser() {
Map<String, Object> map = new HashMap<>();
map.put("id", "1");
map.put("username", "user");
map.put("password", "user");
List<User> users = userMapper.selectUserInfoWithParam(map);
System.out.println(users);
}
/**
* 动态增加
*/
@Test
public void testInsertUser() {
User user = new User();
user.setUsername("hikktn");
user.setPassword("123456");
int result = userMapper.insertUser(user);
System.out.println("插入成功，返回结果：" + result);
}
/**
* 动态修改
*/
@Test
public void testUpdateUser() {
Map<String, Object> map = new HashMap<>();
map.put("id", "3");
User user = userMapper.selectUserWithId(map);
user.setUsername("hikktn");
user.setPassword("hikktn0320");
userMapper.updateUser(user);
}
/**
* 动态删除
*/
@Test
public void testDeleteUser() {
Map<String, Object> param = new HashMap<String, Object>();
param.put("username", "hikktn");
userMapper.deleteUser(param);
}
}
八、测试结果
查询结果
2021-08-13 11:56:46.283 DEBUG 400 --- [ main] c.h.m.U.selectUserInfoWithParam : ==> Preparing: SELECT * FROM user WHERE ( id = ? AND username = ? AND password = ? )
2021-08-13 11:56:46.299 DEBUG 400 --- [ main] c.h.m.U.selectUserInfoWithParam : ==> Parameters: 1(String), user(String), user(String)
2021-08-13 11:56:46.322 DEBUG 400 --- [ main] c.h.m.U.selectUserInfoWithParam : <== Total: 1
[User{id=1, username='user', password='user'}]
增加结果
2021-08-13 12:12:03.155 DEBUG 796 --- [ main] com.hikktn.mapper.UserMapper.insertUser : ==> Preparing: INSERT INTO user (username, password) VALUES (?, ?)
2021-08-13 12:12:03.175 DEBUG 796 --- [ main] com.hikktn.mapper.UserMapper.insertUser : ==> Parameters: hikktn(String), 123456(String)
2021-08-13 12:12:03.181 DEBUG 796 --- [ main] com.hikktn.mapper.UserMapper.insertUser : <== Updates: 1
插入成功，返回结果：1
修改结果
2021-08-13 14:25:46.440 DEBUG 11172 --- [ main] c.h.mapper.UserMapper.selectUserWithId : ==> Preparing: SELECT * FROM user WHERE ( id = ? )
2021-08-13 14:25:46.458 DEBUG 11172 --- [ main] c.h.mapper.UserMapper.selectUserWithId : ==> Parameters: 3(String)
2021-08-13 14:25:46.492 DEBUG 11172 --- [ main] c.h.mapper.UserMapper.selectUserWithId : <== Total: 1
2021-08-13 14:25:46.499 DEBUG 11172 --- [ main] com.hikktn.mapper.UserMapper.updateUser : ==> Preparing: UPDATE user SET username = ?, password = ? WHERE ( id = ? )
2021-08-13 14:25:46.499 DEBUG 11172 --- [ main] com.hikktn.mapper.UserMapper.updateUser : ==> Parameters: hikktn(String), hikktn0320(String), 3(Long)
2021-08-13 14:25:46.516 DEBUG 11172 --- [ main] com.hikktn.mapper.UserMapper.updateUser : <== Updates: 1
删除结果
2021-08-13 14:26:49.140 DEBUG 14636 --- [ main] com.hikktn.mapper.UserMapper.deleteUser : ==> Preparing: DELETE FROM user WHERE ( username = ?)
2021-08-13 14:26:49.160 DEBUG 14636 --- [ main] com.hikktn.mapper.UserMapper.deleteUser : ==> Parameters: hikktn(String)
2021-08-13 14:26:49.170 DEBUG 14636 --- [ main] com.hikktn.mapper.UserMapper.deleteUser : <== Updates: 5
版权声明：本文为qq_41520636原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。