文档
使用示例
目录
项目结构
$ 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; }
参考
