工程展示
第1步:创建maven项目
<?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.2.1.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.lian</groupId>
<artifactId>mbdemo01</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>mbdemo01</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</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
<!--mybatis-plus-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.0.5</version>
</dependency>
<!--mysql-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!--lombok用来简化实体类-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</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>
</plugin>
</plugins>
</build>
</project>
第2步:创建实体类User
package com.lian.mbdemo01.entity;
import com.baomidou.mybatisplus.annotation.*;
import lombok.Data;
import java.io.Serializable;
import java.util.Date;
@Data
public class User implements Serializable {
@TableId(type = IdType.ID_WORKER) //设置主键自增策略
private Long id;
private String name;
private Integer age;
private String email;
//记录的创建时间,更新时间 自动填充
@TableField(fill = FieldFill.INSERT)
private Date createTime;
@TableField(fill = FieldFill.INSERT_UPDATE)
private Date updateTime;
/**
* 乐观锁
* 1、数据库中添加version字段
* 2、实体类添加version字段,并添加@Version 注解
* 3、元对象处理器接口添加version的insert默认值
* 4、配置类 在 MybatisPlusConfig 中注册 Bean
* 5、测试
*/
@Version
@TableField(fill = FieldFill.INSERT)
private Integer version;
/**
* 物理删除:真实删除,将对应数据从数据库中删除,之后查询不到此条被删除数据
* 逻辑删除:假删除,将对应数据中代表是否被删除字段状态修改为“被删除状态”,之后在数据库中仍旧能看到此条数据记录
*
* 1、数据库添加字段 ALTER TABLE `user` ADD COLUMN `deleted` boolean
* 2、实体类添加deleted 字段
* 3、元对象处理器接口添加deleted的insert默认值
* 4、application.properties 加入配置
* 5、在 MybatisPlusConfig 中注册 Bean
* 6、测试逻辑删除
* 测试后发现,数据并没有被删除,deleted字段的值由0变成了1
* 测试后分析打印的sql语句,是一条update
* 注意:被删除数据的deleted 字段的值必须是 0,才能被选取出来执行逻辑删除的操作
* UPDATE user SET deleted=1 WHERE id=? AND deleted=0
* 7、测试逻辑删除后的查询
* 测试后分析打印的sql语句,包含 WHERE deleted=0
* SELECT id,name,age,email,create_time,update_time,deleted FROM user WHERE deleted=0
*/
@TableLogic
@TableField(fill = FieldFill.INSERT)
private Integer deleted;
}
第3步:配置类MybatisPlusConfig
package com.lian.mbdemo01.config;
import com.baomidou.mybatisplus.core.injector.ISqlInjector;
import com.baomidou.mybatisplus.extension.injector.LogicSqlInjector;
import com.baomidou.mybatisplus.extension.plugins.OptimisticLockerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.plugins.PerformanceInterceptor;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Profile;
import org.springframework.transaction.annotation.EnableTransactionManagement;
@Configuration
@EnableTransactionManagement
@MapperScan("com.lian.mbdemo01.mapper")
public class MybatisPlusConfig {
/**
* 乐观锁插件
*/
@Bean
public OptimisticLockerInterceptor optimisticLockerInterceptor(){
return new OptimisticLockerInterceptor();
}
/**
* 分页插件
*/
@Bean
public PaginationInterceptor paginationInterceptor() {
return new PaginationInterceptor();
}
/**
* 逻辑删除插件
*/
@Bean
public ISqlInjector sqlInjector() {
return new LogicSqlInjector();
}
/**
* SQL 执行性能分析插件
* 参数:maxTime: SQL 执行最大时长,超过自动停止运行,有助于发现问题。
* 参数:format: SQL是否格式化,默认false。
* 开发环境使用,线上不推荐。 maxTime 指的是 sql 最大执行时长
*/
@Bean
@Profile({"dev","test"})// 设置 dev test 环境开启
public PerformanceInterceptor performanceInterceptor() {
PerformanceInterceptor performanceInterceptor = new PerformanceInterceptor();
performanceInterceptor.setMaxTime(100);//ms,超过此处设置的ms则sql不执行
// performanceInterceptor.setMaxTime(5);//ms,超过此处设置的ms不执行
performanceInterceptor.setFormat(true);
return performanceInterceptor;
}
}
第4步:实现元对象处理器接口
package com.lian.mbdemo01.handler;
import com.baomidou.mybatisplus.core.handlers.MetaObjectHandler;
import org.apache.ibatis.reflection.MetaObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
import java.util.Date;
/**
* 实现元对象处理器接口
* 1、数据库在User表中添加datetime类型的新的字段 create_time、update_time,自动填充字段
* 2、user字段加注解,@TableField(fill = FieldFill.INSERT)
* 3、MyMetaObjectHandler类配置
*/
@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 ....");
this.setFieldValByName("createTime",new Date(),metaObject);
this.setFieldValByName("updateTime",new Date(),metaObject);
this.setFieldValByName("version",1,metaObject);
this.setFieldValByName("deleted",0,metaObject);
}
@Override
public void updateFill(MetaObject metaObject) {
LOGGER.info("start update fill ....");
this.setFieldValByName("updateTime",new Date(),metaObject);
}
}
第5步:mapper层
package com.lian.mbdemo01.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.lian.mbdemo01.entity.User;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;
@Mapper
@Repository
public interface UserMapper extends BaseMapper<User> {
}
第6步:主启动类
package com.lian.mbdemo01;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@MapperScan("com.lian.mbdemo01.mapper")
@SpringBootApplication
public class Mbdemo01Application {
public static void main(String[] args) {
SpringApplication.run(Mbdemo01Application.class, args);
}
}
第7步:配置application.properties
# 配置数据源
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/mp?serverTimezone=GMT%2B8
spring.datasource.username=root
spring.datasource.password=root
#mybatis日志
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
#全局设置主键生成策略,影响所有实体的配置,可以设置全局主键配置,@TableId只适合单个实体的主键字段
mybatis-plus.global-config.db-config.id-type=auto
# 逻辑删除
mybatis-plus.global-config.db-config.logic-delete-value=1
mybatis-plus.global-config.db-config.logic-not-delete-value=0
#环境设置:dev、test、prod
spring.profiles.active=dev
第8步:测试类之mybatisPlus基础crud
package com.lian.mbdemo01;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.lian.mbdemo01.entity.User;
import com.lian.mbdemo01.mapper.UserMapper;
import org.junit.jupiter.api.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.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* mybatisPlus基础的增删改查操作
*/
@RunWith(SpringRunner.class)
@SpringBootTest
public class Mbdemo01ApplicationTests {
@Autowired
UserMapper userMapper;
@Test
public void testSelectList() {
System.out.println(("----- selectAll method test ------"));
//UserMapper 中的 selectList() 方法的参数为 MP 内置的条件封装器 Wrapper
//所以不填写就是无任何条件
List<User> users = userMapper.selectList(null);
users.forEach(System.out::println);
}
@Test
public void testInsert(){
User user = new User();
//user.setId("");
user.setAge(10);
user.setName("aipi");
user.setEmail("376536577@qq.com");
int result = userMapper.insert(user);
System.out.println(result); //影响的行数
System.out.println(user); //id自动回填
}
@Test
public void testUpdate(){
User user = new User();
user.setId(1471716233916932097L);
user.setAge(10);
int result = userMapper.updateById(user);
System.out.println(result);
}
/**
* 测试乐观锁成功
*/
@Test
public void testVersion(){
User user = userMapper.selectById(1471736174497984514L);
user.setAge(12);
user.setName("danpier");
userMapper.updateById(user);
}
/**
* 测试乐观锁成功
*/
@Test
public void testVersionFail(){
User user = userMapper.selectById(1471728156284805122L);
user.setAge(12);
user.setName("dan");
user.setVersion(user.getVersion()-1);
userMapper.updateById(user);
}
/**
* 测试查询
*/
@Test
public void testSelectById(){
User user = userMapper.selectById(1471728156284805122L);
System.out.println(user);
}
/**
* 测试批量查询
*/
@Test
public void testBatchSelectById(){
List<User> users = userMapper.selectBatchIds(Arrays.asList(1, 2, 3, 4));
System.out.println(users);
}
/**
* 测试用map封装
*/
@Test
public void testSelectByMap(){
//User user = userMapper.selectById(1471728156284805122L);
Map map = new HashMap();
map.put("name","pi");
map.put("age",10);
List list = userMapper.selectByMap(map);
System.out.println(list);
}
/**
* 测试selectPage分页
* 测试:最终通过page对象获取相关数据
*/
@Test
public void testPage(){
Page<User> page = new Page<>(1,5);
userMapper.selectPage(page,null);
page.getRecords().forEach(System.out::println);
System.out.println(page.getCurrent());
System.out.println(page.getPages());
System.out.println(page.getSize());
System.out.println(page.getTotal());
System.out.println(page.hasNext());
System.out.println(page.hasPrevious());
}
//用map的形式查询分页
@Test
public void testSelectMapsPage() {
Page<User> page = new Page<>(1, 5);
IPage<Map<String, Object>> mapIPage = userMapper.selectMapsPage(page, null);
//注意:此行必须使用 mapIPage 获取记录列表,否则会有数据类型转换错误
mapIPage.getRecords().forEach(System.out::println);
System.out.println(page.getCurrent());
System.out.println(page.getPages());
System.out.println(page.getSize());
System.out.println(page.getTotal());
System.out.println(page.hasNext());
System.out.println(page.hasPrevious());
}
//删除单列
@Test
public void delete(){
User user = userMapper.selectById(1471728156284805122L);
userMapper.deleteById(user);
}
//批量删除用批量id
@Test
public void deleteBatch(){
List<Long> list = Arrays.asList(1471716233916932097L, 1471724535568650241L);
userMapper.deleteBatchIds(list);
}
//批量删除用map
@Test
public void deleteBatchMap(){
//List<Long> list = Arrays.asList(1471716233916932097L, 1471724535568650241L);
HashMap<String, Object> map = new HashMap<>();
map.put("id",1471716233916932097L);
map.put("id",1471724535568650241L);
userMapper.deleteByMap(map);
}
/**
* 测试逻辑删除
* 测试后发现,数据并没有被删除,deleted字段的值由0变成了1
* 测试后分析打印的sql语句,是一条update
* 注意:被删除数据的deleted 字段的值必须是 0,才能被选取出来执行逻辑删除的操作
*/
@Test
public void testLogicDelete(){
int result = userMapper.deleteById(1471736174497984514L);
System.out.println(result);
}
/**
* 测试 逻辑删除后的查询:
* 不包括被逻辑删除的记录
*/
@Test
public void testLogicDeleteSelect() {
User user = new User();
List<User> users = userMapper.selectList(null);
users.forEach(System.out::println);
}
/**
* 测试 性能分析插件
* Time:16 ms - ID:com.lian.mbdemo01.mapper.UserMapper.insert
* Execute SQL:
* INSERT
* INTO
* user
* ( id, name, age, email, create_time, update_time, version, deleted )
* VALUES
* ( 1471787336014188545, 'jack', 20, 'test2@baomidou.com', '2021-12-17 18:20:20.051', '2021-12-17 18:20:20.051', 1, 0 )
*/
@Test
public void testPerformance() {
User user = new User();
user.setName("jack");
user.setEmail("test2@baomidou.com");
user.setAge(20);
userMapper.insert(user);
}
}
第9步:测试类之条件构造器demo
package com.lian.mbdemo01;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.lian.mbdemo01.entity.User;
import com.lian.mbdemo01.mapper.UserMapper;
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.HashMap;
import java.util.List;
import java.util.Map;
/**
* mybatisPlus 条件构造器 wrapper 的复杂查询
*/
@RunWith(SpringRunner.class)
@SpringBootTest
public class WrapperTests {
@Autowired
UserMapper userMapper;
/**
* 1、ge、gt、le、lt、isNull、isNotNull
*/
//UPDATE user SET deleted=1 WHERE deleted=0 AND name IS NULL AND age >= ? AND email IS NOT NULL
@Test
public void testDelete(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper
.isNull("name")
.ge("age", 12)
.isNotNull("email");
int result = userMapper.delete(queryWrapper);
System.out.println("delete return count = " + result);
}
/**
* 2、eq、ne
* seletOne返回的是一条实体记录,当出现多条时会报错
* SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 AND name = ?
*/
@Test
public void testSelectOne() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("name", "Tom");
User user = userMapper.selectOne(queryWrapper);
System.out.println(user);
}
/**
* 3、between、notBetween
* SELECT COUNT(1) FROM user WHERE deleted=0 AND age BETWEEN ? AND ?
*/
@Test
public void testSelectCount() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.between("age", 20, 30);
Integer count = userMapper.selectCount(queryWrapper);
System.out.println(count);
}
/**
* 4、allEq
* SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 AND name = ? AND id = ? AND age = ?
*/
@Test
public void testSelectList() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
Map<String, Object> map = new HashMap<>();
map.put("id", 2);
map.put("name", "Jack");
map.put("age", 20);
queryWrapper.allEq(map);
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
/**
* 5、like、notLike、likeLeft、likeRight
* selectMaps返回Map集合列表
* SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 AND name NOT LIKE '%e%' AND email LIKE 't%'
*/
@Test
public void testSelectMaps() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper
.notLike("name", "e")
.likeRight("email", "t");
List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);//返回值是Map列表
maps.forEach(System.out::println);
}
/**
* 6、in、notIn、inSql、notinSql、exists、notExists
* in、notIn:
* notIn("age",{1,2,3})--->age not in (1,2,3)
* notIn("age", 1, 2, 3)--->age not in (1,2,3)
* inSql、notinSql:可以实现子查询
* 例: inSql("age", "1,2,3,4,5,6")--->age in (1,2,3,4,5,6)
* 例: inSql("id", "select id from table where id < 3")--->id in (select id from table where id < 3)
* SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 AND id IN (select id from user where id < 3)
*/
@Test
public void testSelectObjs() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//queryWrapper.in("id", 1, 2, 3);
queryWrapper.inSql("id", "select id from user where id < 3");
List<Object> objects = userMapper.selectObjs(queryWrapper);//返回值是Object列表
objects.forEach(System.out::println);
}
/**
* 7、or、and
* 注意:这里使用的是 UpdateWrapper
* 不调用or则默认为使用 and 连
* UPDATE user SET name=Andy, age=99, update_time=? WHERE deleted=0 AND name LIKE %h% OR age BETWEEN 20 AND 30
*/
@Test
public void testUpdate1() {
//修改值
User user = new User();
user.setAge(99);
user.setName("Andy");
//修改条件
UpdateWrapper<User> userUpdateWrapper = new UpdateWrapper<>();
userUpdateWrapper
.like("name", "h")
.or()
.between("age", 20, 30);
int result = userMapper.update(user, userUpdateWrapper);
System.out.println(result);
}
/**
* 8、嵌套or、嵌套and
* 这里使用了lambda表达式,or中的表达式最后翻译成sql时会被加上圆括号
* UPDATE user SET name=Andy, age=99, update_time=? WHERE deleted=0 AND name LIKE %h% OR ( name = 李白 AND age <> 20 )
*
* EQ 就是 EQUAL等于
* NE 就是 NOT EQUAL不等于
* GT 就是 GREATER THAN大于
* LT 就是 LESS THAN小于
* GE 就是 GREATER THAN OR EQUAL 大于等于
* LE 就是 LESS THAN OR EQUAL 小于等于
*/
@Test
public void testUpdate2() {
//修改值
User user = new User();
user.setAge(99);
user.setName("Andy");
//修改条件
UpdateWrapper<User> userUpdateWrapper = new UpdateWrapper<>();
userUpdateWrapper
.like("name", "h")
.or(i -> i.eq("name", "李白").ne("age", 20));
int result = userMapper.update(user, userUpdateWrapper);
System.out.println(result);
}
/**
* 9、orderBy、orderByDesc、orderByAsc
* SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 ORDER BY id DESC
*/
@Test
public void testSelectListOrderBy() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.orderByDesc("id");
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
/**
* 10、last
* 直接拼接到 sql 的最后
* 注意:只能调用一次,多次调用以最后一次为准 有sql注入的风险,请谨慎使用
* select ... from .. where ... group by ... having ... order by ... limit ...
* SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 limit 1
*/
@Test
public void testSelectListLast() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.last("limit 1");
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
/**
* 11、指定要查询的列
* SELECT id,name,age FROM user WHERE deleted=0
*/
@Test
public void testSelectListColumn() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.select("id", "name", "age");
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
/**
* 12、set、setSql
* 最终的sql会合并 user.setAge(),以及 userUpdateWrapper.set() 和 setSql() 中 的字段
* UPDATE user SET age=99, update_time=?, name=老李头, email = '123@qq.com' WHERE deleted=0 AND name LIKE %h%
*/
@Test
public void testUpdateSet() {
//修改值
User user = new User();
user.setAge(99);
//修改条件
UpdateWrapper<User> userUpdateWrapper = new UpdateWrapper<>();
userUpdateWrapper
.like("name", "h")
.set("name", "老李头")//除了可以查询还可以使用set设置修改的字段
.setSql(" email = '123@qq.com'");//可以有子查询
int result = userMapper.update(user, userUpdateWrapper);
System.out.println(result);
}
}