您现在的位置是:首页 >其他 >Java:MyBatis-Plus内置SQL注入器的使用和自定义SQL注入器网站首页其他
Java:MyBatis-Plus内置SQL注入器的使用和自定义SQL注入器
简介Java:MyBatis-Plus内置SQL注入器的使用和自定义SQL注入器
相关文档
环境准备
1、完整依赖 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 https://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.7</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>demo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>demo</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<excludes>
<exclude>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</exclude>
</excludes>
</configuration>
</plugin>
</plugins>
</build>
</project>
2、数据库配置 application.yml
# DataSource Config
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/data?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai
username: root
password: 123456
mybatis-plus:
configuration:
# 开启SQL语句打印
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
global-config:
db-config:
# 自增主键策略
id-type: AUTO
3、建表语句
CREATE TABLE `tb_user` (
`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`username` varchar(128) NOT NULL COMMENT '用户名',
`phone` varchar(32) NOT NULL DEFAULT '' COMMENT '手机号',
`sex` char(1) NOT NULL DEFAULT '' COMMENT '性别',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`deleted` tinyint NOT NULL DEFAULT '0' COMMENT '1、删除 0、未删除',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户表';
4、实体类
package com.example.demo.entity;
import com.baomidou.mybatisplus.annotation.*;
import lombok.Data;
import lombok.experimental.Accessors;
import java.io.Serializable;
import java.time.LocalDateTime;
@Data
@Accessors(chain = true)
@TableName("tb_user")
public class UserDO implements Serializable {
private static final long serialVersionUID = 1L;
/**
* 主键
*/
@TableId(value = "id", type = IdType.AUTO)
private Integer id;
/**
* 用户名
*/
@TableField("username")
private String username;
/**
* 手机号
*/
@TableField("phone")
private String phone;
/**
* 性别
*/
@TableField("sex")
private String sex;
/**
* 创建时间
*/
@TableField(value = "create_time", fill = FieldFill.INSERT)
private LocalDateTime createTime;
/**
* 更新时间
*/
@TableField(value = "update_time", fill = FieldFill.INSERT_UPDATE)
private LocalDateTime updateTime;
/**
* 1、删除 0、未删除
*/
@TableField(value = "deleted", fill = FieldFill.INSERT)
private Integer deleted;
}
使用内置扩展SQL注入器
Mybatis-Plus默认不支持空值更新,可以通过注入内置扩展SQL注入器实现空值更新
自带扩展SQL注入器
-
AlwaysUpdateSomeColumnById : 根据id更新字段(全量更新不忽略null字段),updateById默认会自动忽略实体中null值字段。
-
InsertBatchSomeColumn : 真实批量插入,saveBatch其实是伪批量插入。
-
LogicDeleteBatchByIds : 逻辑删除增加填充功能,比如删除的时候填充更新时间、更新人。
-
Upsert : 插入一条数据(选择字段插入)。
1、SQL注入器全局配置
package com.example.demo.config;
import com.baomidou.mybatisplus.annotation.FieldFill;
import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.injector.DefaultSqlInjector;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import com.baomidou.mybatisplus.extension.injector.methods.AlwaysUpdateSomeColumnById;
import com.baomidou.mybatisplus.extension.injector.methods.InsertBatchSomeColumn;
import com.example.demo.mybatisplus.methods.FindAll;
import org.springframework.stereotype.Component;
import java.util.List;
/**
* SQL注入器全局配置
*/
@Component
public class MySqlInjector extends DefaultSqlInjector {
@Override
public List<AbstractMethod> getMethodList(Class<?> mapperClass, TableInfo tableInfo) {
List<AbstractMethod> methodList = super.getMethodList(mapperClass, tableInfo);
/**
* 把两个扩展内置扩展SQL注入器注入
*/
methodList.add(new InsertBatchSomeColumn(i -> i.getFieldFill() != FieldFill.UPDATE));
methodList.add(new AlwaysUpdateSomeColumnById(i -> i.getFieldFill() != FieldFill.INSERT));
return methodList;
}
}
2、自定义Mapper
package com.example.demo.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import java.util.List;
/**
* 自定义Mapper
* @param <T>
*/
public interface MyBaseMapper<T> extends BaseMapper<T> {
/**
* 全字段更新,不会忽略null值
*
* @param entity 实体对象
*/
int alwaysUpdateSomeColumnById(@Param("et") T entity);
/**
* 全量插入,等价于insert
*
* @param entityList 实体集合
*/
int insertBatchSomeColumn(List<T> entityList);
}
3、继承MyBaseMapper
package com.example.demo.mapper;
import com.example.demo.entity.UserDO;
import org.apache.ibatis.annotations.Mapper;
/**
* 通用mapper接口,以后创建其他mapper接口时,不再继承BaseMapper,而是继承MyBaseMapper
*/
@Mapper
public interface UserMapper extends MyBaseMapper<UserDO> {
}
4、测试
package com.example.demo;
import com.example.demo.entity.UserDO;
import com.example.demo.mapper.UserMapper;
import org.assertj.core.util.Lists;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import java.util.ArrayList;
import java.util.List;
@SpringBootTest
@RunWith(SpringRunner.class)
public class SqlInjectorTest {
@Autowired
private UserMapper mapper;
@Test
public void updateById() {
UserDO user = new UserDO();
user.setUsername("小小");
user.setPhone(null);
user.setSex("女");
user.setId(1);
mapper.updateById(user);
// UPDATE tb_user SET username=?, sex=?, update_time=? WHERE id=?
}
@Test
public void alwaysUpdateSomeColumnById() {
UserDO user = new UserDO();
user.setUsername("小小");
user.setPhone(null);
user.setSex("女");
user.setId(1);
mapper.alwaysUpdateSomeColumnById(user);
// UPDATE tb_user SET username=?, phone=?, sex=?, update_time=? WHERE id=?
}
@Test
public void insertBatchSomeColumn() {
UserDO user = new UserDO();
user.setUsername("张三");
user.setPhone("13811111111");
user.setSex("女");
UserDO user1 = new UserDO();
user1.setUsername("李四");
user1.setPhone("13822222222");
user1.setSex(null);
ArrayList<UserDO> userDOS = Lists.newArrayList(user, user1);
mapper.insertBatchSomeColumn(userDOS);
// INSERT INTO tb_user (username,phone,sex,create_time,update_time,deleted) VALUES (?,?,?,?,?,?) , (?,?,?,?,?,?)
}
}
自定义SQL注入器
1、编写FindAll SQL注入器
package com.example.demo.mybatisplus.methods;
import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlSource;
/**
* 编写FindAll SQL注入器
*/
public class FindAll extends AbstractMethod {
public FindAll() {
super("findAll");
}
public FindAll(String methodName) {
super(methodName);
}
@Override
public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
/* 执行 SQL ,动态 SQL 参考类 SqlMethod */
String sql = "select * from " + tableInfo.getTableName();
SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, modelClass);
return this.addSelectMappedStatementForTable(mapperClass, sqlSource, tableInfo);
}
}
2、注册到Spring容器
package com.example.demo.config;
import com.baomidou.mybatisplus.annotation.FieldFill;
import com.baomidou.mybatisplus.core.injector.AbstractMethod;
import com.baomidou.mybatisplus.core.injector.DefaultSqlInjector;
import com.baomidou.mybatisplus.core.metadata.TableInfo;
import com.baomidou.mybatisplus.extension.injector.methods.AlwaysUpdateSomeColumnById;
import com.baomidou.mybatisplus.extension.injector.methods.InsertBatchSomeColumn;
import com.example.demo.mybatisplus.methods.FindAll;
import org.springframework.stereotype.Component;
import java.util.List;
/**
* SQL注入器全局配置
*/
@Component
public class MySqlInjector extends DefaultSqlInjector {
@Override
public List<AbstractMethod> getMethodList(Class<?> mapperClass, TableInfo tableInfo) {
List<AbstractMethod> methodList = super.getMethodList(mapperClass, tableInfo);
/**
* 自定义SQL注入器注入
*/
methodList.add(new FindAll());
return methodList;
}
}
3、在MyBaseMapper中添加findAll方法
package com.example.demo.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import java.util.List;
/**
* 自定义Mapper
* @param <T>
*/
public interface MyBaseMapper<T> extends BaseMapper<T> {
/**
* 查询所有用户
*/
List<T> findAll();
}
4、测试
package com.example.demo;
import com.example.demo.entity.UserDO;
import com.example.demo.mapper.UserMapper;
import org.assertj.core.util.Lists;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import java.util.ArrayList;
import java.util.List;
@SpringBootTest
@RunWith(SpringRunner.class)
public class SqlInjectorTest {
@Autowired
private UserMapper mapper;
@Test
public void findAll() {
List<UserDO> userDOS = mapper.findAll();
// select * from tb_user
}
}
参考
风语者!平时喜欢研究各种技术,目前在从事后端开发工作,热爱生活、热爱工作。