Java：Spring Boot整合mybatis-plus示例

文档

• https://start.spring.io/
• mybatis-plus 文档 | github
• maven 文档
• p6spy 文档 | github

使用示例

项目结构

$ tree -I target
.
├── pom.xml
└── src
    ├── main
    │   ├── java
    │   │   └── com
    │   │       └── example
    │   │           └── demo
    │   │               ├── DemoApplication.java
    │   │               ├── entity
    │   │               │   └── User.java
    │   │               └── mapper
    │   │                   └── UserMapper.java
    │   └── resources
    │       ├── application.yml
    │       ├── db
    │       │   ├── data-h2.sql
    │       │   └── schema-h2.sql
    │       └── spy.properties
    └── test
        └── java
            └── com
                └── example
                    └── demo
                        └── SampleTest.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

下载初始项目 https://start.spring.io/

• Project Maven Project
• Language Java
• Spring Boot 2.7.5
• Packaging Jar
• Java 8

依赖

pom.xml

<parent>
	<groupId>org.springframework.bootgroupId>
	<artifactId>spring-boot-starter-parentartifactId>
	<version>2.7.5version>
	<relativePath/> 
parent>

<properties>
	<java.version>1.8java.version>
properties>

<dependencies>
	<dependency>
		<groupId>org.springframework.bootgroupId>
		<artifactId>spring-boot-starterartifactId>
	dependency>

	<dependency>
		<groupId>org.springframework.bootgroupId>
		<artifactId>spring-boot-starter-testartifactId>
		<scope>testscope>
	dependency>

	<dependency>
		<groupId>com.baomidougroupId>
		<artifactId>mybatis-plus-boot-starterartifactId>
		<version>3.5.2version>
	dependency>

	<dependency>
		<groupId>com.h2databasegroupId>
		<artifactId>h2artifactId>
		<scope>runtimescope>
	dependency>

	<dependency>
		<groupId>org.projectlombokgroupId>
		<artifactId>lombokartifactId>
		<version>1.18.24version>
		<scope>providedscope>
	dependency>

	<dependency>
		<groupId>p6spygroupId>
		<artifactId>p6spyartifactId>
		<version>3.9.1version>
	dependency>
dependencies>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

SQL文件

schema-h2.sql

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user`
(
    id BIGINT NOT NULL COMMENT '主键ID',
    name VARCHAR(30) NULL DEFAULT NULL COMMENT '姓名',
    age INT NULL DEFAULT NULL COMMENT '年龄',
    email VARCHAR(50) NULL DEFAULT NULL COMMENT '邮箱',
    PRIMARY KEY (id)
);
1
2
3
4
5
6
7
8
9
10

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, 'Tom', 28, 'test3@baomidou.com'),
(4, 'Sandy', 21, 'test4@baomidou.com'),
(5, 'Billie', 24, 'test5@baomidou.com');
1
2
3
4
5
6
7
8

配置文件

application.yml

# DataSource Config
spring:
  datasource:
    # driver-class-name: org.h2.Driver
    # 打印执行SQL
    driver-class-name: com.p6spy.engine.spy.P6SpyDriver
    url: jdbc:p6spy:h2:mem:test
    username: root
    password: test
  sql:
    init:
      schema-locations: classpath:db/schema-h2.sql
      data-locations: classpath:db/data-h2.sql
      encoding: UTF-8

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

spy.properties

#3.2.1以上使用
modulelist=com.baomidou.mybatisplus.extension.p6spy.MybatisPlusLogFactory,com.p6spy.engine.outage.P6OutageFactory
#3.2.1以下使用或者不配置
#modulelist=com.p6spy.engine.logging.P6LogFactory,com.p6spy.engine.outage.P6OutageFactory
# 自定义日志打印
logMessageFormat=com.baomidou.mybatisplus.extension.p6spy.P6SpyLogger
#日志输出到控制台
appender=com.baomidou.mybatisplus.extension.p6spy.StdoutLogger
# 使用日志系统记录 sql
#appender=com.p6spy.engine.spy.appender.Slf4JLogger
# 设置 p6spy driver 代理
deregisterdrivers=true
# 取消JDBC URL前缀
useprefix=true
# 配置记录 Log 例外,可去掉的结果集有error,info,batch,debug,statement,commit,rollback,result,resultset.
excludecategories=info,debug,result,commit,resultset
# 日期格式
dateformat=yyyy-MM-dd HH:mm:ss
# 实际驱动可多个
#driverlist=org.h2.Driver
# 是否开启慢SQL记录
outagedetection=true
# 慢SQL记录标准 2 秒
outagedetectioninterval=2
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

实体类

User.java

package com.example.demo.entity;

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


@Data
@TableName("`user`")
public class User {
    private Long id;
    private String name;
    private Integer age;
    private String email;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14

Mapper

UserMapper.java

package com.example.demo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.demo.entity.User;

public interface UserMapper extends BaseMapper<User> {

}

1
2
3
4
5
6
7
8
9

程序入口

DemoApplication.java

package com.example.demo;

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

@SpringBootApplication
@MapperScan("com.example.demo.mapper")
public class DemoApplication {

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

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

查询测试

SampleTest.java

package com.example.demo;


import com.example.demo.entity.User;
import com.example.demo.mapper.UserMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;


import java.util.List;

@SpringBootTest
public class SampleTest {

    @Autowired
    private UserMapper userMapper;

    @Test
    public void testSelect() {
        System.out.println(("----- selectAll method test ------"));
        List<User> userList = userMapper.selectList(null);
        userList.forEach(System.out::println);
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

打印日志

 Consume Time：2 ms 2022-11-22 09:42:16
 Execute SQL：DROP TABLE IF EXISTS `user`

 Consume Time：5 ms 2022-11-22 09:42:16
 Execute SQL：CREATE TABLE `user` ( 
    id BIGINT NOT NULL COMMENT '主键ID', 
    name VARCHAR(30) NULL DEFAULT NULL COMMENT '姓名', 
    age INT NULL DEFAULT NULL COMMENT '年龄', 
    email VARCHAR(50) NULL DEFAULT NULL COMMENT '邮箱', 
    PRIMARY KEY (id) 
)

 Consume Time：3 ms 2022-11-22 09:42:16
 Execute SQL：DELETE FROM `user`

 Consume Time：2 ms 2022-11-22 09:42:16
 Execute SQL：INSERT INTO `user` (id, name, age, email) VALUES 
 (1, 'Jone', 18, 'test1@baomidou.com'), 
 (2, 'Jack', 20, 'test2@baomidou.com'), 
 (3, 'Tom', 28, 'test3@baomidou.com'), 
 (4, 'Sandy', 21, 'test4@baomidou.com'), 
 (5, 'Billie', 24, 'test5@baomidou.com')

----- selectAll method test ------
 Consume Time：3 ms 2022-11-22 09:42:16
 Execute SQL：SELECT id,name,age,email FROM `user`

User(id=1, name=Jone, age=18, email=test1@baomidou.com)
User(id=2, name=Jack, age=20, email=test2@baomidou.com)
User(id=3, name=Tom, age=28, email=test3@baomidou.com)
User(id=4, name=Sandy, age=21, email=test4@baomidou.com)
User(id=5, name=Billie, age=24, email=test5@baomidou.com)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

报错及解决

报错

org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database.  Cause: org.h2.jdbc.JdbcSQLSyntaxErrorException: 
Syntax error in SQL statement "SELECT  id,name,age,email  FROM [*]user";
expected "identifier";SQL statement:
SELECT  id,name,age,email  FROM user [42001-214]
1
2
3
4
5

解决

package com.example.demo.entity;

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

// 增加表名注解`user`
@Data
@TableName("`user`")
public class User {
    private Long id;
    private String name;
    private Integer age;
    private String email;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14

参考
https://github.com/baomidou/mybatis-plus/issues/4788