信步漫谈之MyBatisPlus——CRUD与分页
1 目的
使用MybatisPlus进行增删改查以及分页操作
2 程序结构
com.alfred.mybatisplus.crudpage
∟ pom.xml
∟ src
∟ main
∟ java
∟ com
∟ alfred
∟ mybatisplus
∟ crudpage
∟ config
∟ MybatisPlusConfig.java
∟ CrudApplication.java
∟ mapper
∟ UserMapper.java
∟ po
∟ User.java
∟ resources
∟ application.yml
∟ db
∟ data-h2.sql
∟ schema-h2.sql
∟ mapper
∟ UserMapper.xml
∟ test
∟ java
∟ com
∟ alfred
∟ mybatisplus
∟ crudpage
∟ mapper
∟ test
∟ CrudTest.java
3 程序代码
- MybatisPlusConfig.java
package com.alfred.mybatisplus.crudpage.config;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.autoconfigure.ConfigurationCustomizer;
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;
/**
* @author alfred
* @since 2021-01-27
*/
@Configuration
public class MybatisPlusConfig {
/**
* 新的分页插件,一缓和二缓遵循mybatis的规则,需要设置 MybatisConfiguration#useDeprecatedExecutor = false 避免缓存出现问题
*/
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.H2));
return interceptor;
}
@Bean
public ConfigurationCustomizer configurationCustomizer() {
return configuration -> configuration.setUseDeprecatedExecutor(false);
}
}
- UserMapper.java
package com.alfred.mybatisplus.crudpage.mapper;
import com.alfred.mybatisplus.crudpage.po.User;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
/**
* @Author: alfred
* @Date: 2021/1/27
* @description:
*/
@Mapper
public interface UserMapper extends BaseMapper<User> {
}
- User.java
package com.alfred.mybatisplus.crudpage.po;
import lombok.Builder;
import lombok.Data;
import lombok.experimental.Accessors;
/**
* 用户实体对应表 user
* @Author alfred
* @Date 2021-01-27
*/
@Builder
@Data
@Accessors(chain = true)
public class User {
private Long id;
private String name;
private Integer age;
private String email;
}
- CrudApplication.java
package com.alfred.mybatisplus.crudpage;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
/**
* @Author: alfred
* @Date: 2021/1/27
* @description:
*/
@SpringBootApplication
public class CrudApplication {
public static void main(String[] args) {
SpringApplication.run(CrudApplication.class);
}
}
- data-h2.sql
DELETE FROM user;
INSERT INTO user (id, name, age, email)
VALUES (1, 'Jone', 18, 'test1@baomidou.com'),
(2, 'Jack', 20, 'test2@baomidou.com'),
(3, 'Jack', 20, 'test2@baomidou.com'),
(4, 'Jack', 20, 'test2@baomidou.com'),
(5, 'Jack', 20, 'test2@baomidou.com'),
(6, 'Jack', 20, 'test2@baomidou.com'),
(7, 'Jack', 20, 'test2@baomidou.com'),
(8, 'Jack', 20, 'test2@baomidou.com'),
(9, 'Jack', 20, 'test2@baomidou.com'),
(10, 'Jack', 20, 'test2@baomidou.com'),
(11, 'Jack', 20, 'test2@baomidou.com'),
(12, 'Jack', 20, 'test2@baomidou.com'),
(13, 'Jack', 20, 'test2@baomidou.com'),
(14, 'Jack', 20, 'test2@baomidou.com'),
(15, 'Tom', 28, 'test3@baomidou.com'),
(16, 'Sandy', 21, 'test4@baomidou.com'),
(17, 'Billie', 24, 'test5@baomidou.com');
- schema-h2.sql
DROP TABLE IF EXISTS user;
CREATE TABLE user
(
id BIGINT(20) NOT NULL COMMENT '主键ID',
name VARCHAR(30) NULL DEFAULT NULL COMMENT '姓名',
age INT(11) NULL DEFAULT NULL COMMENT '年龄',
email VARCHAR(50) NULL DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (id)
);
- UserMapper.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.alfred.mybatisplus.crudpage.mapper.UserMapper">
</mapper>
- application.yml
# DataSource Config
spring:
datasource:
driver-class-name: org.h2.Driver
schema: classpath:db/schema-h2.sql
data: classpath:db/data-h2.sql
url: jdbc:h2:mem:mybatisplus
username: root
password: test
# Logger Config
logging:
level:
com.alfred.mybatisplus.crudpage: debug
# MyBatis-Plus 配置
mybatis-plus:
mapper-locations: classpath:/mapper/*Mapper.xml
4 测试程序
- CrudTest.java
package com.alfred.mybatisplus.crudpage.mapper.test;
import static org.assertj.core.api.Assertions.assertThat;
import com.alfred.mybatisplus.crudpage.mapper.UserMapper;
import com.alfred.mybatisplus.crudpage.po.User;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import javax.annotation.Resource;
/**
* @Author: alfred
* @Date: 2021/1/27
* @Description:
*/
@RunWith(SpringRunner.class)
@SpringBootTest
public class CrudTest {
@Resource
UserMapper userMapper;
/**
* 新增
*/
@Test
public void add() {
User user = User.builder().name("alfred").email("alfred@me.com").age(22).build();
assertThat(userMapper.insert(user)).isGreaterThan(0);
}
/**
* 删除
*/
@Test
public void del() {
assertThat(userMapper.deleteById(3)).isGreaterThan(0);
assertThat(userMapper.delete(Wrappers.<User>query().lambda().eq(User::getName, "Jone"))).isGreaterThan(0);
}
/**
* 修改
*/
@Test
public void upd() {
assertThat(userMapper.update(User.builder().name("Sandy2").build(),
Wrappers.<User>lambdaUpdate().eq(User::getName, "Sandy"))).isGreaterThan(0);
}
/**
* 查询数量
*/
@Test
public void selectCount() {
Assert.assertEquals(userMapper.selectCount(null), Integer.valueOf(16));
}
/**
* 条件查询
*/
@Test
public void selectByCondition() {
userMapper.selectList(Wrappers.<User>lambdaQuery().eq(User::getName, "alfred")).forEach(x -> {
assertThat(x.getName()).isEqualTo("alfred");
});
}
/**
* 分页查询
*/
@Test
public void selectPage() {
Page<User> page = new Page<>(1, 3);
Page<User> result = userMapper.selectPage(page, Wrappers.<User>lambdaQuery().ge(User::getAge, 1).orderByAsc(User::getAge));
assertThat(result.getTotal()).isGreaterThan(3);
assertThat(result.getRecords().size()).isEqualTo(3);
}
}
