一、整合 MyBatisPlus

1. 导入依赖

<dependency>
   <groupId>com.baomidou</groupId>
   <artifactId>mybatis-plus-boot-starter</artifactId>
   <version>3.5.1</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.26</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
    <version>2.6.3</version>
</dependency>

2. 配置文件

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/demo?useUnicode=true&characterEncoding=UTF-8&useSSL=true
    username: root
    password: root

mybatis-plus:
  # 指定 Mapper XML 文件的位置，使用 classpath 通配符指定路径
  mapper-locations: classpath:/mapper/**/*.xml
  # 指定实体类的包路径，用于自动扫描并注册类型别名
  type-aliases-package: com.cnbai.bean
  global-config:
    db-config:
      id-type: input
      # 驼峰下划线转换（将数据库字段的下划线命名规则转换为 Java 实体类属性的驼峰命名规则）
      db-column-underline: true
      # 刷新 mapper
      refresh-mapper: true
  configuration:
    # 将 Java 实体类属性的驼峰命名规则转换为数据库字段的下划线命名规则
    map-underscore-to-camel-case: true
    # 查询结果中包含空值的列，在映射的时候，不会映射这个字段
    call-setters-on-nulls: true
    # 开启 sql 日志
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    # 关闭 sql 日志
    # log-impl: org.apache.ibatis.logging.nologging.NoLoggingImpl

3. 配置类

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.autoconfigure.MybatisPlusProperties;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

@Configuration
public class MybatisPlusConfig {

    /**
     * 加载分页插件
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        // 添加分页插件(如果配置多个插件,切记分页最后添加)
        PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor(DbType.MYSQL);
        // 分页合理化，超出总页数默认获取第一页数据
        paginationInnerInterceptor.setOverflow(true);
        interceptor.addInnerInterceptor(paginationInnerInterceptor);
        return interceptor;
    }

    @Primary
    @Bean
    @ConfigurationProperties("mybatis-plus")
    public MybatisPlusProperties mybatisPlusProperties() {
        return new MybatisPlusProperties();
    }
}

4. 业务类

public interface UserService extends IService<User> {}

@Service
public class UserServiceImpl extends ServiceImpl<UserDao, User> implements UserService {}

@Mapper
public interface UserDao extends BaseMapper<User> {}

5. 实体类

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableField;

import java.io.Serializable;

@TableName(value = "t_user")
public class User implements Serializable {

    @TableId(value = "id", type = IdType.INPUT)
    private Integer id;
    
    @TableField(value = "name")
    private String name;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

6. 启动类

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("com.cnbai.dao")
public class Application {

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
}

二、查询

1. 普通查询

public List<User> queryUserList(String name) {
    LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
    wrapper.eq(User::getName, name);
    return list(wrapper);
}

public User queryUserById(String id) {
    return getBaseMapper().selectById(id);
}

2. 拼接查询

 LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
 queryWrapper
        .eq(User::getName, "张三")
        .eq(User::getAge, 26);
 List<User> list = list(queryWrapper);

3. 分页查询

// offset 默认从 1 开始，展示第 1 页， 读 limit 条数据
public Page<User> queryPage(Integer offset, Integer limit) {
    return userDao.selectPage(PageDTO.of(offset, limit), new QueryWrapper<>());
}

4. 查询部分字段

public List<User> queryUserList() {
    LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
    wrapper.select(User::getName, User::getAge);
    return list(wrapper);
}

5. 多表联查

// UserService
LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
queryWrapper.eq(StringUtils.isNotBlank(user.getName()), "u.name", user.getName());
List<UserVO> list = userDao.queryList(queryWrapper);

// UserDao
List<UserVO> queryList(@Param(Constants.WRAPPER) Wrapper<User> queryWrapper);

<select id="queryList" resultType="com.cnbai.entity.vo.UserVO">
	SELECT
		u.user_id, u.name, d.name as deptName
	FROM
		t_user u
		LEFT JOIN t_dept d ON u.dept_id = d.dept_id
    ${ew.getCustomSqlSegment}
</select>

6. 格式化日期查询

 LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
 queryWrapper
 	.apply("DATE_FORMAT (birth,'%Y-%m-%d')={0}", DateUtil.format(param.getBirth(),"yyyy-MM-dd"))
 	.apply("DATE_FORMAT (create_time,'%Y')={0}", "2024")
 List<User> list = list(queryWrapper);

三、常用条件构造器

1. 单条件

// name = 'bai'
eq("name", "bai")

2. 拼接 AND

// name = ? AND age = ?
.eq("name", "张三").eq("age", 26);

3. 拼接 OR

// id = 1 or name = 'bai'
.eq("id",1).or().eq("name","bai")

4. Apply

String years = "2022,2023,2024";

// birth IN (2022,2023,2024)
.apply("YEAR(birth) IN (" + years + ")")

5. 嵌套

// or ( name = '张三' and age != 12 )
.or ( x -> x.eq("name", "张三").ne("age", 12) )

// and (  name like '%bai%' or address like '%xian%' )
.and(  x -> x.like("name", "bai").or().like("address", "xian") )

// and (  name = 'bai' or ( name = '张三' and age = 12 )  )
.and(  x -> x.eq("name", "bai").or( y -> y.eq("name", "张三").eq("age", 12) )  )

// ( name = '张三' and age != 12 )
.nested ( x -> x.eq("name", "张三").ne("age", 12) )

四、注解查询

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.ResultType;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface UserDao extends BaseMapper<User> {

    @Select("select * from t_user where name = #{name}")
    @ResultType(User.class)
    List<User> getUserListByName(@Param("name") String name);

    @Select("select * from t_user where id = #{id}")
    @ResultType(User.class)
    User getUserById(@Param("id") String id);
    
    @Select("select name from t_user where id = #{id}")
    String getUserNameById(@Param("id") String id);
}

五、xml 查询

1. 直接返回查询结果

List<Map<String,Object>> queryUser();

<?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.bai.dao.UserDao">

  <select id="queryUser" resultType="java.util.Map">
    select * from user
  </select>
  
</mapper>

2. 返回对象集合

List<User> queryUser();

<?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.bai.dao.UserDao">

  <select id="queryUser" resultType="com.cnbai.entity.User">
    select * from user
  </select>
  
</mapper>

3. 返回单个对象

User queryUserById();

<?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.bai.dao.UserDao">
 
  <!-- 已配置实体包路径可不用配置全类名 -->
  <select id="queryUserById" resultType="User">
    select * from user where id = '1'
  </select>
  
</mapper>

4. 返回整型

int queryUserCount();

<?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.bai.dao.UserDao">
  
  <select id="queryUserCount" resultType="java.lang.Integer">
    select count(*) from user
  </select>
  
</mapper>

5. 返回字符串集合

List<String> queryUserName();

<?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.bai.dao.UserDao">

  <resultMap id="userNameMap" type="java.lang.String">
    <result column="name" typeHandler="org.apache.ibatis.type.StringTypeHandler" />
  </resultMap>
  
  <select id="queryUserName" resultMap="userNameMap">
    select name from user
  </select>
  
</mapper>

6. 字符串比较

MyBatis 单引号内有一个字符的情况下，会将其以 java 中的 char 类型进行解析，那么此时 char 类型与参数 String 类型用等号进行比较的时候结果都是 false

<?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.bai.dao.UserDao">

  <select id="queryUser" resultType="com.cnbai.entity.User">
    select * from user where 1=1
    <choose>
      <when test="status == '5'.toString()">
          and status = #{status}
      </when>
      <when test="name == 'cnbai'">
          and name = #{name}
      </when>
      <otherwise>
          and id = #{id}
      </otherwise>
    </choose>
  </select>
  
</mapper>

注：xml 中使用某些符号（ 如：< ）会报错

1. 使用 <![CDATA[]]

SELECT * FROM t_user WHERE
<![CDATA[ age < 12 ]]>
AND name = 'zhangsan';

2. 转义

符号	转义字符	描述
<	&lt;	小于，需转义
<=	&lt;=	小于等于，需转义
>	&gt;	大于，无需转义
>=	&gt;=	大于等于，无需转义

六、处理 Json 数据

1. 存储

实体类中某个字段属性是 List，Map 之类的可以转为 Json 格式，其在 MySQL 中存储字段类型可以设置为 Json 类型，添加注解将此类型映射为 Json 存入数据库中

注：插入时可以不定义 autoResultMap = true，查询时必须定义

@TableName(value = "t_user")
public class User {
    @TableId
    private int id;

    @TableField(value = "user_info", typeHandler = JacksonTypeHandler.class)
    private JSONArray userInfo;

    @TableField(value = "info", typeHandler = JacksonTypeHandler.class)
    private JSONObject info;
}

2. 取出

当没有使用到 xml 时

@TableName(value = "t_user", autoResultMap = true)
public class User {
    @TableId
    private int id;

    @TableField(value = "user_info", typeHandler = JacksonTypeHandler.class)
    private JSONArray userInfo;

    @TableField(value = "info", typeHandler = JacksonTypeHandler.class)
    private JSONObject info;
}

当使用了 xml 时

<result property="userInfo" column="user_info" typeHandler="com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler"/>
<result property="info" column="info" typeHandler="com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler"/>

七、循环遍历集合数组

/**
 * List : User(id=1, name=zhangsan, age=21)
 * List : User(id=2, name=lisi, age=22)
 *
 * Map : User(id=2, name=lisi, age=22)
 * Map : User(id=3, name=wangwu, age=23)
 * 
 * Array: String[] -> ("zhangsan", "lisi", "wangwu")
 */
public void queryTest() {
    userService.queryByList();
    userService.queryByMap();
    userService.updateByMap();
    userService.updateByList();
    userService.queryByName();
}

1. UserService

public class UserService {

    @Resource
    UserDao userDao;

    public void queryByList() {
        List<Integer> list = new ArrayList<>();
        list.add(1);
        list.add(2);
        userDao.queryByList(list);
    }

    public void queryByMap() {
        Map<String, Object> map = new HashMap<>();
        map.put("name", username);
        userDao.queryByMap(map);
    }

    public void updateByMap() {
        Map<Integer, Integer> map = new HashMap<>();
        map.put(2, 21);
        map.put(3, 31);
        userDao.updateByMap(map);
    }

    public void updateByList() {
        List<User> list = new ArrayList<>();
        list.add(user1);
        list.add(user2);
        userDao.updateByList(list);
    }
    
	public void queryByName() {
		String[] split = "zhangsan, lisi, wangwu".split(",");
        userDao.queryByName(split);
    }
}

2. UserDao

public interface UserDao extends BaseMapper<User> {

    // UserMapper.xml 中 collection 的值对应 @Param 里的值
    List<User> queryByList(@Param("list") List<Integer> userList);

    // 此处不使用 @Param , 或者不用 Map 直接传参 -> queryByMap(@Param("name") String username);
    List<Map<String, Object>> queryByMap(Map<String, Object> map);

    void updateByMap(@Param("map") Map<Integer, Integer> map);
    
    void updateByList(@Param("list") List<User> userList);
    
	List<User> queryByName(@Param("array") String[] array);
}

3. UserMapper

<?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.bai.dao.UserDao">

  <select id="queryByList" resultType="com.cnbai.entity.User">
    select * from user where
    <choose>
      <when test="list != null and list.size() > 0">
        id in
        <foreach collection="list" item="id" open="(" separator="," close=")">
      		#{id}
    	</foreach>
      </when>
      <otherwise>
        1 = 2
      </otherwise>
    </choose>
  </select>

  <select id="queryByMap" resultType="com.cnbai.entity.User">
    select id, name as username, age from user where
    <if test="name != null and name != ''">
      name = #{name}
    </if>
  </select>

  <update id="updateByMap">
    <foreach collection="map" index="id" item="age" separator=";">
      update user set age = #{age} where id = #{id}
    </foreach>
  </update>
  
  <update id="updateByList">
    <foreach collection="list" index="index" item="user">
      update user set age = #{user.age} where id = #{user.id}
    </foreach>
  </update>

  <!-- SELECT * FROM user WHERE (name LIKE '%zhangsan%' OR name LIKE '%lisi%' OR name LIKE '%wangwu%') -->
  <select id="queryByName" resultType="com.cnbai.entity.User">
    select * from user where
    <foreach collection="array" item="name" open="(" separator="OR" close=")">
      	name LIKE CONCAT('%', #{name}, '%')
    </foreach>
  </select>
</mapper>

4. 增加配置参数

Mybatis 批量更新时需要在 url 后加上 &allowMultiQueries=true
application.yml

spring:
  datasource:
    username: root
    password: root
    url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&useSSL=true&allowMultiQueries=true
    driver-class-name: com.mysql.jdbc.Driver

否则会报错

org.springframework.jdbc.BadSqlGrammarException: 
### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update user set age = 1 where id = 3' at line 3
### The error may involve com.bai.faker.mapper.UserMapper.updateByMapThree-Inline
### The error occurred while setting parameters
### SQL: update user set age = ? where id = ?      ;        update user set age = ? where id = ?
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update user set age = 1 where id = 3' at line 3
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update user set age = 1 where id = 3' at line 3
... ...

八、执行 Sql 文件

1. UserService

import com.baomidou.mybatisplus.extension.service.IService;

import javax.sql.DataSource;
import java.io.File;

public interface UserService extends IService<User> {
    void executeSqlFile(DataSource dataSource, File sqlFile);
}

2. UserServiceImpl

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

@Service
public class UserServiceImpl extends ServiceImpl<UserDao, User> implements UserService {

    @Override
    public void executeSqlFile(DataSource dataSource, File sqlFile) {
        Connection connection = null;
        FileReader reader = null;
        Statement statement = null;
        try {
            connection = dataSource.getConnection();
            connection.setAutoCommit(false);
            statement = connection.createStatement();

            reader = new FileReader(sqlFile);
            String line;
            int size = 0;
            BufferedReader bufferedReader = new BufferedReader(reader);
            while ((line = bufferedReader.readLine()) != null) {
                if (size % 1000 == 0) {
                    statement.executeBatch();
                    connection.commit();
                    statement.clearBatch();
                }
                statement.addBatch(line);
                size++;
            }
            statement.executeBatch();
            connection.commit();
            bufferedReader.close();
        } catch (Exception e) {
            if (connection != null) {
                try {
                    connection.rollback();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }
                if (reader != null) {
                    reader.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

九、手动实现分页查询

1. pom.xml

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.5.1</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.26</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
    <version>2.6.3</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
    <version>2.6.3</version>
</dependency>
<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.8.27</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.34</version>
</dependency>

2. 业务类

TestController

import com.cnbai.entity.PageQuery;
import com.cnbai.entity.TableDataInfo;
import com.cnbai.entity.User;
import lombok.RequiredArgsConstructor;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RequiredArgsConstructor
@RestController
@RequestMapping("/test")
public class TestController {

    private final TestService testService;

    @PostMapping("/list")
    public TableDataInfo<User> getList(User user, PageQuery pageQuery) {
        return testService.getList(user, pageQuery);
    }
}

TestService

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.cnbai.entity.PageQuery;
import com.cnbai.entity.TableDataInfo;
import com.cnbai.entity.User;
import com.cnbai.mapper.TestMapper;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
@RequiredArgsConstructor
public class TestService {

    private final TestMapper testMapper;

    public TableDataInfo<User> getList(User user, PageQuery pageQuery) {
        pageQuery.buildDefaultParam(pageQuery);
        LambdaQueryWrapper<User> wrapper =  Wrappers.lambdaQuery();
        wrapper.eq(User::getUsername, user.getUsername());
        List<User> userList = testMapper.getList(pageQuery.getPageNum(), pageQuery.getPageSize(), wrapper);
        return TableDataInfo.build(userList, testMapper.queryListCount(wrapper));
    }
}

3. 实体类

User

import lombok.Data;

@Data
public class User {

    private int id;
    private String username;
    private String address;
}

PageQuery

import cn.hutool.core.util.ObjectUtil;
import lombok.Data;

import java.io.Serial;
import java.io.Serializable;

/**
 * 分页查询实体类
 */
@Data
public class PageQuery implements Serializable {

    @Serial
    private static final long serialVersionUID = 1L;

    /**
     * 分页大小
     */
    private Integer pageSize;

    /**
     * 当前页数
     */
    private Integer pageNum;

    /**
     * 当前记录起始索引 默认值
     */
    public static final int DEFAULT_PAGE_NUM = 1;

    /**
     * 每页显示记录数 默认值 默认查全部
     */
    public static final int DEFAULT_PAGE_SIZE = Integer.MAX_VALUE;

    /**
     * 自定义分页，构建分页参数
     */
    public void buildDefaultParam(PageQuery pageQuery) {
        Integer num = ObjectUtil.defaultIfNull(getPageNum(), DEFAULT_PAGE_NUM);
        Integer pageSize = ObjectUtil.defaultIfNull(getPageSize(), DEFAULT_PAGE_SIZE);
        if (num <= 0) {
            num = DEFAULT_PAGE_NUM;
        }
        pageQuery.setPageNum(pageSize);
        pageQuery.setPageSize((num - 1) * pageSize);
    }
}

TableDataInfo

import cn.hutool.http.HttpStatus;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serial;
import java.io.Serializable;
import java.util.List;

/**
 * 表格分页数据对象
 */
@Data
@NoArgsConstructor
public class TableDataInfo<T> implements Serializable {

    @Serial
    private static final long serialVersionUID = 1L;

    /**
     * 总记录数
     */
    private long total;

    /**
     * 列表数据
     */
    private List<T> rows;

    /**
     * 消息状态码
     */
    private int code;

    /**
     * 消息内容
     */
    private String msg;

    public static <T> TableDataInfo<T> build(List<T> list, int total) {
        TableDataInfo<T> rspData = new TableDataInfo<>();
        rspData.setCode(HttpStatus.HTTP_OK);
        rspData.setMsg("查询成功");
        rspData.setRows(list);
        rspData.setTotal(total);
        return rspData;
    }
}

4. Mapper

TestMapper.java

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.cnbai.entity.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

@Mapper
public interface TestMapper extends BaseMapper<User> {

    List<User> getList(@Param("limit") Integer limit,
                       @Param("offset") Integer offset,
                       @Param(Constants.WRAPPER) Wrapper<User> queryWrapper);

    int queryListCount(@Param(Constants.WRAPPER) Wrapper<User> queryWrapper);
}

TestMapper.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.cnbai.mapper.TestMapper">

    <select id="getList" resultType="com.cnbai.entity.User">
        SELECT
          *
        FROM
            t_user
        ${ew.getCustomSqlSegment}
        LIMIT #{limit} OFFSET #{offset}
    </select>

    <select id="queryListCount" resultType="java.lang.Integer">
        SELECT
            count(1)
        FROM
            t_user
        ${ew.getCustomSqlSegment}
    </select>
</mapper>