MyBatis Plus逻辑删除和分页插件 MyBatis Plus逻辑删除和分页插件使用详解

<?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.6.RELEASE</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>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <scope>runtime</scope>
  </dependency>
  <dependency>
   <groupId>org.projectlombok</groupId>
   <artifactId>lombok</artifactId>
   <optional>true</optional>
  </dependency>
  <dependency>
   <groupId>com.baomidou</groupId>
   <artifactId>mybatisplus-spring-boot-starter</artifactId>
   <version>1.0.5</version>
  </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>
  <dependency>
   <groupId>com.baomidou</groupId>
   <artifactId>mybatis-plus</artifactId>
   <version>3.3.1</version>
  </dependency>
 </dependencies>

 <build>
  <plugins>
   <plugin>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-maven-plugin</artifactId>
   </plugin>
  </plugins>
 </build>

</project>

server:
 port: 8080
 servlet:
 context-path: /
# Logger Config
logging:
 level:
 com.baomidou.mybatisplus.samples.quickstart: debug

spring.datasource.jdbc-url = jdbc:mysql://localhost:3306/demo?serverTimezone=UTC&characterEncoding=utf8
spring.datasource.username = root
spring.datasource.password = 123456
spring.datasource.driverClassName = com.mysql.cj.jdbc.Driver

package com.example.demo.config;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.plugins.pagination.optimize.JsqlParserCountOptimize;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@EnableTransactionManagement
@Configuration
@MapperScan("com.example.demo.mapper")
public class MybatisPlusConfig {

 @Bean("paginationInterceptor")
 public PaginationInterceptor paginationInterceptor() {
  PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
  paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true));
  paginationInterceptor.setDbType(DbType.MYSQL);
  return paginationInterceptor;
 }
}

package com.example.demo.config;

import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;

import org.apache.ibatis.plugin.Interceptor;
import org.mybatis.spring.mapper.MapperScannerConfigurer;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import javax.sql.DataSource;

@Configuration
@PropertySource("classpath:db/db.properties")
public class SqlSessionConfig {

 private Logger logger = LoggerFactory.getLogger(SqlSessionConfig.class);

 @Bean("myDatasource")
 @ConfigurationProperties(prefix="spring.datasource")
 public DataSource masterDataSource() {
  return DataSourceBuilder.create().build();
 }

 @Bean("mySqlSessionFactoryBean")
 public MybatisSqlSessionFactoryBean createSqlSessionFactory(@Qualifier("myDatasource") DataSource dataSource,
                @Qualifier("paginationInterceptor") PaginationInterceptor paginationInterceptor) {
  // MybatisSqlSessionFactory
  MybatisSqlSessionFactoryBean sqlSessionFactoryBean = null;
  try {
   // 实例SessionFactory
   sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
   // 配置数据源
   sqlSessionFactoryBean.setDataSource(dataSource);
   // 设置 MyBatis-Plus 分页插件
   Interceptor [] plugins = {paginationInterceptor};
   sqlSessionFactoryBean.setPlugins(plugins);
   // 加载MyBatis配置文件
   PathMatchingResourcePatternResolver resourcePatternResolver = new PathMatchingResourcePatternResolver();
   sqlSessionFactoryBean.setMapperLocations(resourcePatternResolver.getResources("classpath*:mapper/*.xml"));
  } catch (Exception e) {
   logger.error("Init SqlSessionFactory Error：{}", e.getMessage());
  }
  return sqlSessionFactoryBean;
 }

 @Bean
 public MapperScannerConfigurer myGetMapperScannerConfigurer() {
  MapperScannerConfigurer myMapperScannerConfigurer = new MapperScannerConfigurer();
  myMapperScannerConfigurer.setBasePackage("com.example.demo.mapper");
  myMapperScannerConfigurer.setSqlSessionFactoryBeanName("mySqlSessionFactoryBean");
  return myMapperScannerConfigurer;
 }

}

package com.example.demo.controller;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.demo.bean.UserDTO;
import com.example.demo.entity.User;
import com.example.demo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import java.time.LocalDateTime;
import java.util.List;

@RestController
@RequestMapping(value = "/demo")
public class UserController {

 @Autowired
 private UserService userService;

 @RequestMapping(value = "/create", method = RequestMethod.POST)
 public String create(@RequestBody UserDTO userDTO){
  User user = new User();
  user.setName(userDTO.getName());
  user.setPhone(userDTO.getPhone());
  user.setCreateTime(LocalDateTime.now());
  user.setEnable(true);
  user.setVersion(1L);
  userService.insert(user);
  return "ok";
 }

 @RequestMapping(value = "/delete", method = RequestMethod.POST)
 public String create(Long id){
  userService.deleteById(id);
  return "ok";
 }

 @RequestMapping(value = "/update", method = RequestMethod.POST)
 public String update(@RequestBody UserDTO userDTO){
  User user = new User();
  user.setId(userDTO.getId());
  user.setName(userDTO.getName());
  user.setPhone(userDTO.getPhone());
  user.setCreateTime(LocalDateTime.now());
  userService.updateById(user);
  return "ok";
 }

 @RequestMapping(value = "/queryPage", method = RequestMethod.POST)
 public List<User> update(Integer pageNo, Integer pageSize){
  Page<User> page = new Page<>(pageNo, pageSize);
  return userService.queryList(page, 1);
 }
}

package com.example.demo.service.impl;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.demo.mapper.UserMapper;
import com.example.demo.entity.User;
import com.example.demo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Optional;

@Service
public class UserServiceImpl implements UserService {

 @Autowired
 private UserMapper userMapper;

 @Override
 public int insert(User user) {
  return userMapper.insert(user);
 }

 @Override
 public int deleteById(Long id) {
  return userMapper.deleteById(id);
 }

 @Override
 public int updateById(User user) {
  return userMapper.updateById(user);
 }

 @Override
 public List<User> queryList(Page<User> page, Integer enable) {
  return userMapper.selectPageByEnable(page, enable).getRecords();
 }
}

package com.example.demo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.demo.entity.User;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;

@Mapper
@Repository
public interface UserMapper extends BaseMapper<User> {

 IPage<User> selectPageByEnable(Page<?> page, Integer enable);

}

package com.example.demo.entity;

import com.baomidou.mybatisplus.annotation.*;
import lombok.Data;

import java.time.LocalDateTime;

@Data
@TableName("user")
public class User{

 @TableId(value = "id", type = IdType.AUTO)
 private Long id;

 @TableField("name")
 private String name;

 @TableField("phone")
 private String phone;

 @TableField("create_time")
 private LocalDateTime createTime;

 @TableLogic(delval = "0", value = "1")
 @TableField("enable")
 private boolean enable;

 @Version
 @TableField("version")
 private Long version;

}

<?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.example.demo.mapper.UserMapper">

 <!-- 通用查询映射结果 -->
 <resultMap id="BaseResultMap" type="com.example.demo.entity.User">
  <id column="id" property="id" />
  <result column="name" property="name" />
  <result column="phone" property="phone" />
  <result column="create_time" property="createTime" />
  <result column="enable" property="enable" />
  <result column="version" property="version" />
 </resultMap>

 <select id="selectPageByEnable" resultMap="BaseResultMap">
  SELECT * FROM user WHERE enable=#{enable} order by create_time desc
 </select>
</mapper>

CREATE TABLE `user` (
 `id` bigint NOT NULL AUTO_INCREMENT,
 `name` varchar(20) NOT NULL,
 `phone` char(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
 `enable` tinyint NOT NULL,
 `create_time` datetime NOT NULL,
 `version` bigint NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;