【框架集成】spring boot + mybatis plus

环境及版本
JDK：1.8
Spring Boot：2.1.6
Mybatis Plus：3.1.2
Pagehelper：5.1.10

1. 添加 maven 依赖

除了数据库驱动，其他都用当前最新版本

Spring Boot

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter</artifactId>
    <version>2.1.6.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <version>2.1.6.RELEASE</version>
    <scope>test</scope>
</dependency>

Mybatis Plus

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.1.2</version>
</dependency>

Pagehelper 依赖 5.1.10 版本，需要去掉 mybatis 依赖，否则会和 mybatis plus 冲突

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>1.2.12</version>
    <exclusions>
        <exclusion>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
        </exclusion>
        <exclusion>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-spring</artifactId>
        </exclusion>
    </exclusions>
</dependency>

其他

 <dependency>
     <groupId>mysql</groupId>
     <artifactId>mysql-connector-java</artifactId>
     <version>5.1.47</version>
 </dependency>
 <dependency>
     <groupId>com.alibaba</groupId>
     <artifactId>druid</artifactId>
     <version>1.1.19</version>
 </dependency>

2. 配置文件

最简配置，木有多余的

application.yml

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driverClassName: com.mysql.jdbc.Driver
    url: jdbc:mysql://192.168.0.200:3306/mysql?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
    username: root
    password: root123

# mybatis-plus
mybatis-plus:
  mapper-locations: classpath:mapper/**/*Mapper.xml

# pageHelper
pagehelper:
  helperDialect: mysql

3. Dao 示例

3.1 mybatis plus 的 CRUD

添加表

CREATE TABLE `t_sys_user`  (
  `id` bigint(0) NOT NULL,
  `username` varchar(32) NULL,
  `password` varchar(32) NULL,
  `name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL,
  PRIMARY KEY (`id`)
);

修改启动类

@SpringBootApplication
@MapperScan("ricky.learn.dao")
public class App {
    public static void main(String[] args) {
        SpringApplication.run(App.class, args);
    }
}

添加 User 对象

@TableName("t_sys_user")
public class User {

    @TableId(type = IdType.ID_WORKER)
    private Long id;
    private String name;
    private String username;
    private String password;

    // 省略 getter setter 和构造方法
}

添加 mapper 继承 BaseMapper

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

测试方法

@RunWith(SpringRunner.class)
@SpringBootTest
public class UserDaoTest {

    @Autowired
    private UserDao userDao;

    @Test
    public void add() {
        User user = new User("王山支", "wsz", "wsz123");
        userDao.insert(user);
    }
}

结果

id	username	password	name
1156954967293677569	wsz	wsz123	王山支

解释

• 启动类的@MapperScan("ricky.learn.dao")会扫描ricky.learn.dao下带有@Mapper的类
• User 的@TableName("t_sys_user")指定表名为t_sys_user
• id 字段的@TableId(type = IdType.ID_WORKER)在添加时会自动生成 id（雪花算法）
• UserDao 继承了 BaseMapper 之后可以使用 mybatis plus 自带的 CRUD 方法

更多方法参考 Mybatis-Plus官方文档

3.2 分页与自定义SQL

多加几条数据

INSERT INTO `t_sys_user`(`id`, `username`, `password`, `name`) VALUES (1156958453683032065, 'wsz', 'wsz123', '王山支2');
INSERT INTO `t_sys_user`(`id`, `username`, `password`, `name`) VALUES (1156958475006885889, 'wsz', 'wsz123', '王山支3');
INSERT INTO `t_sys_user`(`id`, `username`, `password`, `name`) VALUES (1156958505272868866, 'wsz', 'wsz123', '王山支4');
INSERT INTO `t_sys_user`(`id`, `username`, `password`, `name`) VALUES (1156958534389747714, 'wsz', 'wsz123', '王山支5');

Mybatis Plus 分页

创建 MybatisPlusConfig 使用分页插件

@EnableTransactionManagement
@Configuration
@MapperScan("ricky.learn.**.dao*")
public class MybatisPlusConfig {
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
        // paginationInterceptor.setLimit(你的最大单页限制数量，默认 500 条，小于 0 如 -1 不受限制);
        return paginationInterceptor;
    }
}

使用自带的分页查询

加一个测试方法

@Test
public void page() {
    List<User> userList = userDao.selectPage(new Page<>(2, 2), null).getRecords();
    userList.forEach(System.out::println);
}

输出：

User{id=1156958475006885889, name='王山支3', username='wsz', password='wsz123'}
User{id=1156958505272868866, name='王山支4', username='wsz', password='wsz123'}

自定义SQL和mybatis plus的分页

UserDao 添加方法

@Mapper
public interface UserDao extends BaseMapper<User> {
    IPage<User> pageList(Page<User> page);
}

新建 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="ricky.learn.dao.UserDao">

    <resultMap id="UserMap" type="ricky.learn.dao.domain.User">
        <id column="id" property="id" />
        <result column="username" property="username" />
        <result column="password" property="password" />
        <result column="name" property="name" />
    </resultMap>
    
    <select id="pageList" resultMap="UserMap">
        select * from t_sys_user
    </select>

</mapper>

添加测试方法试试

@Test
public void pageList() {
    List<User> userList = userDao.pageList(new Page<>(2, 2)).getRecords();
    userList.forEach(System.out::println);
}

当 page.size < 0 则不分页，查询时不会有分页条件

@Test
public void pageList() {
    List<User> userList = userDao.pageList(new Page<>(0, -1)).getRecords();
    userList.forEach(System.out::println);
}

User{id=1156954967293677569, name='王山支', username='wsz', password='wsz123'}
User{id=1156958453683032065, name='王山支2', username='wsz', password='wsz123'}
User{id=1156958475006885889, name='王山支3', username='wsz', password='wsz123'}
User{id=1156958505272868866, name='王山支4', username='wsz', password='wsz123'}
User{id=1156958534389747714, name='王山支5', username='wsz', password='wsz123'}

Pagehelper 分页

UserDao 添加

List<User> pageList2();

UserMapper.xml 添加

<select id="pageList2" resultMap="UserMap">
    select * from t_sys_user
</select>

添加测试方法

@Test
public void pageList2() {
    PageHelper.startPage(2, 2);
    List<User> userList = userDao.pageList2();
    PageInfo<User> pageInfo = new PageInfo<>(userList);
    pageInfo.getList().forEach(System.out::println);
    System.out.println("页大小" + pageInfo.getPageSize());
    System.out.println("当前页" + pageInfo.getPageNum());
    System.out.println("总页数" + pageInfo.getPages());
    System.out.println("总条数" + pageInfo.getTotal());
    System.out.println("当前条数" + pageInfo.getSize());
}

User{id=1156958475006885889, name='王山支3', username='wsz', password='wsz123'}
User{id=1156958505272868866, name='王山支4', username='wsz', password='wsz123'}
页大小2
当前页2
总页数3
总条数5
当前条数2

4. 在 service 中使用

创建 UserService

public interface UserService extends IService<User> {
    UserDto findById(String id);
}

创建 UserServiceImpl，使用自带的 CRUD

@Service
public class UserServiceImpl extends ServiceImpl<UserDao, User> implements UserService {
    @Override
    public UserDto findById(String id) {
        User user = getById(id);
        return ConvertUtil.convert(user, UserDto.class);
    }
}

如果自己的方法名和 mybatis plus 自带的方法一样的话，使用 super

@Override
public UserDto getById(String id) {
    User user = super.getById(id);
    return ConvertUtil.convert(user, UserDto.class);
}

使用自定义方法
添加方法（省略其他方法）

public interface UserService extends IService<User> {
    IPage<User> pageList(Page<User> page);
}

注入 UserDao ，调用 UserDao 的自定义方法

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

    private UserDao userDao;

    @Autowired
    public UserServiceImpl(UserDao userDao) {
        this.userDao = userDao;
    }

    @Override
    public IPage<User> pageList(Page<User> page) {
        return userDao.pageList(page);
    }
}

测试一下

@RunWith(SpringRunner.class)
@SpringBootTest
public class UserServiceImplTest {

    @Autowired
    @Qualifier("userServiceImpl")
    private UserService userService;

    @Test
    public void pageList() {
        List<User> userList = userService.pageList(new Page(2, 2)).getRecords();
        userList.forEach(System.out::println);
    }
}