SpringBoot与MybatisPlus3.X整合示例(十六)
2024-09-01 16:12:41
包含 分页、逻辑删除、自定义全局操作 等绝大部分常用功能的使用示例，相当于大整合的完整示例
pom.xml
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.2.0</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.49</version>
<scope>test</scope>
</dependency>
<!-- for testing -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>application.yml
# DataSource Config
spring:
datasource:
driver-class-name: org.h2.Driver
url: jdbc:h2:tcp://192.168.180.115:19200/~/mem/test
username: root
password: test
# Logger Config
logging:
level:
com.mp.deluxe: debug
mybatis-plus:
# 扫描 mapper.xml
mapper-locations: classpath:/mapper/*Mapper.xml
configuration:
jdbc-type-for-null: 'null'配置类
public interface MyBaseMapper<T> extends BaseMapper<T> {
/**
* 自定义通用方法
*/
Integer deleteAll();
int myInsertAll(T entity);
/**
* 如果要自动填充，@{@code Param}(xx) xx参数名必须是 list/collection/array 3个的其中之一
*
* @param batchList
* @return
*/
int mysqlInsertAllBatch(@Param("list") List<T> batchList);
}
@Configuration
@MapperScan("com.mp.deluxe.mapper")
public class MybatisPlusConfig {
/**
* 1.分页插件
* 2.多租户
*/
@Bean
public PaginationInterceptor paginationInterceptor() {
return new PaginationInterceptor();
}
@Bean
public OptimisticLockerInterceptor optimisticLockerInterceptor(){
return new OptimisticLockerInterceptor();
}
/**
* 自定义 SqlInjector
* 里面包含自定义的全局方法
*/
@Bean
public MyLogicSqlInjector myLogicSqlInjector() {
return new MyLogicSqlInjector();
}
}
public class TestTypeHandler extends BaseTypeHandler<String> {
@Override
public void setNonNullParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType) throws SQLException {
ps.setString(i, "TestTypeHandler set {" + parameter + "}");
}
@Override
public String getNullableResult(ResultSet rs, String columnName) throws SQLException {
String string = rs.getString(columnName);
return "TestTypeHandler(rs columnName) get {" + string + "}";
}
@Override
public String getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
String string = rs.getString(columnIndex);
return "TestTypeHandler(rs columnIndex) get {" + string + "}";
}
@Override
public String getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
String string = cs.getString(columnIndex);
return "TestTypeHandler(cs columnIndex) get {" + string + "}";
}
}
@Data
@Accessors(chain = true)
public class User {
private Long id;
private String name;
private Integer age;
@TableField(typeHandler = TestTypeHandler.class)
private String email;
@Version
private Integer version;
@TableLogic(value = "0", delval = "1")
@TableField(select = false)
private Integer deleted;
@TableField(value = "create_time", fill = FieldFill.INSERT)
private Timestamp createTime;
}
Mapper层
public interface UserMapper extends MyBaseMapper<User> {
/**
* 自定义分页查询
*
* @param userPage 单独 user 模块使用的分页
* @return 分页数据
*/
UserPage selectUserPage(UserPage userPage);
List<User> findList(@Param("user") User user);
}方法层
public class DeleteAll extends AbstractMethod {
@Override
public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
/* 执行 SQL ，动态 SQL 参考类 SqlMethod */
String sql = "delete from " + tableInfo.getTableName();
/* mapper 接口方法名一致 */
String method = "deleteAll";
SqlSource sqlSource = languageDriver.createSqlSource(configuration, sql, modelClass);
return this.addDeleteMappedStatement(mapperClass, method, sqlSource);
}
}
public class MyInsertAll extends AbstractMethod {
@Override
public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
String sql = "insert into %s %s values %s";
StringBuilder fieldSql = new StringBuilder();
fieldSql.append(tableInfo.getKeyColumn()).append(",");
StringBuilder valueSql = new StringBuilder();
valueSql.append("#{").append(tableInfo.getKeyProperty()).append("},");
tableInfo.getFieldList().forEach(x->{
fieldSql.append(x.getColumn()).append(",");
valueSql.append("#{").append(x.getProperty()).append("},");
});
fieldSql.delete(fieldSql.length()-1, fieldSql.length());
fieldSql.insert(0, "(");
fieldSql.append(")");
valueSql.insert(0, "(");
valueSql.delete(valueSql.length()-1, valueSql.length());
valueSql.append(")");
SqlSource sqlSource = languageDriver.createSqlSource(configuration, String.format(sql, tableInfo.getTableName(), fieldSql.toString(), valueSql.toString()), modelClass);
return this.addInsertMappedStatement(mapperClass, modelClass, "myInsertAll", sqlSource, new NoKeyGenerator(), null, null);
}
}
public class MysqlInsertAllBatch extends AbstractMethod {
@Override
public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
final String sql = "<script>insert into %s %s values %s</script>";
final String fieldSql = prepareFieldSql(tableInfo);
final String valueSql = prepareValuesSqlForMysqlBatch(tableInfo);
final String sqlResult = String.format(sql, tableInfo.getTableName(), fieldSql, valueSql);
SqlSource sqlSource = languageDriver.createSqlSource(configuration, sqlResult, modelClass);
return this.addInsertMappedStatement(mapperClass, modelClass, "mysqlInsertAllBatch", sqlSource, new NoKeyGenerator(), null, null);
}
private String prepareFieldSql(TableInfo tableInfo) {
StringBuilder fieldSql = new StringBuilder();
fieldSql.append(tableInfo.getKeyColumn()).append(",");
tableInfo.getFieldList().forEach(x -> {
fieldSql.append(x.getColumn()).append(",");
});
fieldSql.delete(fieldSql.length() - 1, fieldSql.length());
fieldSql.insert(0, "(");
fieldSql.append(")");
return fieldSql.toString();
}
private String prepareValuesSqlForMysqlBatch(TableInfo tableInfo) {
final StringBuilder valueSql = new StringBuilder();
valueSql.append("<foreach collection=\"list\" item=\"item\" index=\"index\" open=\"(\" separator=\"),(\" close=\")\">");
valueSql.append("#{item.").append(tableInfo.getKeyProperty()).append("},");
tableInfo.getFieldList().forEach(x -> valueSql.append("#{item.").append(x.getProperty()).append("},"));
valueSql.delete(valueSql.length() - 1, valueSql.length());
valueSql.append("</foreach>");
return valueSql.toString();
}
}
model层
@Data
@Accessors(chain = true)
@EqualsAndHashCode(callSuper = true)
public class UserPage extends Page<User> {
private static final long serialVersionUID = 7246194974980132237L;
private Integer selectInt;
private String selectStr;
public UserPage(long current, long size) {
super(current, size);
}
}
其他类
@Component
public class MyMetaObjectHandler implements MetaObjectHandler {
private static final Logger LOGGER = LoggerFactory.getLogger(MyMetaObjectHandler.class);
@Override
public void insertFill(MetaObject metaObject) {
LOGGER.info("start insert fill ....");
//避免使用metaObject.setValue()
this.setInsertFieldValByName("createTime", new Timestamp(System.currentTimeMillis()), metaObject);
}
@Override
public void updateFill(MetaObject metaObject) {
LOGGER.info("nothing to fill ....");
}
}
public class MyLogicSqlInjector extends DefaultSqlInjector {
/**
* 如果只需增加方法，保留MP自带方法
* 可以super.getMethodList() 再add
* @return
*/
@Override
public List<AbstractMethod> getMethodList(Class<?> mapperClass) {
List<AbstractMethod> methodList = super.getMethodList(mapperClass);
methodList.add(new DeleteAll());
methodList.add(new MyInsertAll());
methodList.add(new MysqlInsertAllBatch());
methodList.add(new SelectById());
return methodList;
}
}mapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.mp.deluxe.mapper.UserMapper">
<select id="selectUserPage" resultType="com.mp.deluxe.model.UserPage">
select *
from user
<trim prefix="where" prefixOverrides="AND">
<if test="selectInt != null">
age = #{selectInt}
</if>
<if test="selectStr != null">
AND name = #{selectStr}
</if>
AND deleted = 0
</trim>
</select>
<!-- verify github #1532-->
<select id="findList" parameterType="com.mp.deluxe.entity.User" resultType="com.mp.deluxe.entity.User">
select * from user where name like concat(concat('%', #{user.name}), '%')
</select>
</mapper>测试类
@SpringBootTest
class DeluxeApplicationTests {
@Resource
private UserMapper mapper;
@Test
public void testPage() {
// System.out.println("------ 自定义 xml 分页 ------");
// UserPage selectPage = new UserPage(1, 5).setSelectInt(20);
// UserPage userPage = mapper.selectUserPage(selectPage);
// Assert.assertSame(userPage, selectPage);
// System.out.println("总条数 ------> " + userPage.getTotal());
// System.out.println("当前页数 ------> " + userPage.getCurrent());
// System.out.println("当前每页显示数 ------> " + userPage.getSize());
// print(userPage.getRecords());
System.out.println("------ baseMapper 自带分页 ------");
Page<User> page = new Page<>(1, 5);
IPage<User> userIPage = mapper.selectPage(page, new QueryWrapper<User>().eq("age", 20));
Assert.assertSame(userIPage, page);
System.out.println("总条数 ------> " + userIPage.getTotal());
System.out.println("当前页数 ------> " + userIPage.getCurrent());
System.out.println("当前每页显示数 ------> " + userIPage.getSize());
print(userIPage.getRecords());
}
@Test
public void testDelAll() {
mapper.deleteAll();
}
@Test
public void testInsert() {
User u = new User().setEmail("122@qq.com").setVersion(1).setDeleted(0);
mapper.insert(u);
u.setAge(18);
mapper.updateById(u);
u = mapper.selectById(u.getId());
Assert.assertEquals("version should be updated",2, u.getVersion().intValue());
}
@Test
public void testSelect() {
System.out.println(mapper.selectById(1L));
}
private <T> void print(List<T> list) {
if (!CollectionUtils.isEmpty(list)) {
list.forEach(System.out::println);
}
}
@Test
public void myInsertAll(){
long id =1008888L;
User u = new User().setEmail("122@qq.com").setVersion(1).setDeleted(0).setId(id);
mapper.myInsertAll(u);
User user = mapper.selectById(id);
Assert.assertNotNull(user);
Assert.assertNotNull(user.getCreateTime());
}
@Test
public void myInsertBatch(){
long id = 1009991;
List<User> batchList = new ArrayList<>(2);
batchList.add(new User().setId(id++).setEmail("111@qq.com").setVersion(1).setDeleted(0));
batchList.add(new User().setId(id).setEmail("112@qq.com").setVersion(1).setDeleted(0));
mapper.mysqlInsertAllBatch(batchList);
User user = mapper.selectById(1009991);
Assert.assertNotNull(user);
Assert.assertNotNull(user.getCreateTime());
}
@Test
public void verifyGithub1532(){
mapper.findList(new User().setName("a")).forEach(System.out::println);
}
}部分方法的测试结果
2019-11-02 16:16:46.170 WARN 6088 --- [ main] c.b.m.core.injector.AbstractMethod : [com.mp.deluxe.mapper.UserMapper.selectById] Has been loaded by XML or SqlProvider or Mybatis's Annotation, so ignoring this injection for [class com.baomidou.mybatisplus.core.injector.methods.SelectById]
_ _ |_ _ _|_. ___ _ | _
| | |\/|_)(_| | |_\ |_)||_|_\
/ |
3.2.0
2019-11-02 16:16:46.366 INFO 6088 --- [ main] com.mp.deluxe.DeluxeApplicationTests : Started DeluxeApplicationTests in 3.38 seconds (JVM running for 5.204)
------ baseMapper 自带分页 ------
2019-11-02 16:16:46.680 INFO 6088 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2019-11-02 16:16:46.799 INFO 6088 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
2019-11-02 16:16:46.873 DEBUG 6088 --- [ main] c.m.deluxe.mapper.UserMapper.selectPage : ==> Preparing: SELECT COUNT(1) FROM user WHERE deleted = 0 AND (age = ?)
2019-11-02 16:16:46.893 DEBUG 6088 --- [ main] c.m.deluxe.mapper.UserMapper.selectPage : ==> Parameters: 20(Integer)
2019-11-02 16:16:46.916 DEBUG 6088 --- [ main] c.m.deluxe.mapper.UserMapper.selectPage : ==> Preparing: SELECT id,create_time,name,version,email,age FROM user WHERE deleted=0 AND (age = ?) limit ?
2019-11-02 16:16:46.928 DEBUG 6088 --- [ main] c.m.deluxe.mapper.UserMapper.selectPage : ==> Parameters: 20(Integer), 5(Long)
2019-11-02 16:16:46.946 DEBUG 6088 --- [ main] c.m.deluxe.mapper.UserMapper.selectPage : <== Total: 5
总条数 ------> 13
当前页数 ------> 1
当前每页显示数 ------> 5
User(id=2, name=Jack, age=20, email=test2@baomidou.com, version=1, deleted=null, createTime=null)
User(id=3, name=Jack, age=20, email=test2@baomidou.com, version=1, deleted=null, createTime=null)
User(id=4, name=Jack, age=20, email=test2@baomidou.com, version=1, deleted=null, createTime=null)
User(id=5, name=Jack, age=20, email=test2@baomidou.com, version=1, deleted=null, createTime=null)
User(id=6, name=Jack, age=20, email=test2@baomidou.com, version=1, deleted=null, createTime=null)
最新文章
- POJ 3624 Charm Bracelet(01背包)
- 可视化工具gephi源码探秘(一)
- iOS自动处理键盘事件的第三方库：IQKeyboardManager
- Linux 下根据进程名kill进程
- 多线程相关Interlocked.Increment问题
- windows下编译支持https的libcurl
- oralce11 过程
- python之循序渐进学习装饰器
- ImportError libcublas.so.9.0
- 第4周小组作业：WordCount优化
- 【转】Selenium - 封装WebDrivers (C#)
- 2018-2019-1 20165205 ch02 课下作业
- 【补充】第一次个人项目出现的bug
- Spark History Server产生背景
- 分析params_s方法
- ActiveReports 报表应用教程 (10)---交互式报表之向下钻取（详细数据按需显示解决方案）
- ios中摄像头和图片调用
- C#四舍五入保留一位小数
- RabbitMQ学习之（三）_Centos6下RabbitMQ PHP扩展的安装
- centOS下JDK1.8的安装