文档
使用示例
(目录)
项目结构
$ 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
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.7.5</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.2</version>
</dependency>
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.24</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>p6spy</groupId>
<artifactId>p6spy</artifactId>
<version>3.9.1</version>
</dependency>
</dependencies>
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
# 打印执行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
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<User> {
}
程序入口
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 {
@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);
}
}
打印日志
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)
报错及解决
报错
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;
}