文档

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

使用示例

目录

• • 使用示例
  • • 依赖
    • SQL文件
    • 配置文件
    • 实体类
    • Mapper
    • 程序入口
    • 查询测试
  • 报错及解决

项目结构

$ 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

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

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

依赖

pom.xml

org.springframework.bootspring-boot-starter-parent2.7.5 
1.8
org.springframework.bootspring-boot-starterorg.springframework.bootspring-boot-starter-testtestcom.baomidoumybatis-plus-boot-starter3.5.2com.h2databaseh2runtimeorg.projectlomboklombok1.18.24providedp6spyp6spy3.9.1

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)
);

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');

配置文件

application.yml

# DataSource Config
spring:datasource:# driver-class-name: org.h2.Driver# 打印执行SQLdriver-class-name: com.p6spy.engine.spy.P6SpyDriverurl: jdbc:p6spy:h2:mem:testusername: rootpassword: testsql:init:schema-locations: classpath:db/schema-h2.sqldata-locations: classpath:db/data-h2.sqlencoding: UTF-8

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

实体类

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;
}

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 {}

程序入口

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);}}

查询测试

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 {@Autowiredprivate UserMapper userMapper;@Testpublic void testSelect() {System.out.println(("----- selectAll method test ------"));List userList = userMapper.selectList(null);userList.forEach(System.out::println);}
}

打印日志

 Consume Time：2 ms 2022-11-22 09:42:16Execute SQL：DROP TABLE IF EXISTS `user`Consume Time：5 ms 2022-11-22 09:42:16Execute 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:16Execute SQL：DELETE FROM `user`Consume Time：2 ms 2022-11-22 09:42:16Execute 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:16Execute 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)

报错及解决

报错

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]

解决

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;
}

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