[b](1)导入mybatis-spring-boot-starter[/b]
pom.xml
src/main/resources/schema.sql
src/main/resources/application.properties
src/main/java/com/rensanning/springboot/controller/TodoController.java
src/main/java/com/rensanning/springboot/domain/Todo.java
src/main/java/com/rensanning/springboot/service/TodoService.java
src/main/java/com/rensanning/springboot/mapper/TodoMapper.java
可以不使用@Mapper注解自动扫描Mapper
[url=https://github.com/mybatis/mybatipse]Mybatipse[/url]:MyBatis 的一个Eclipse插件。提供Java和XML的Hyperlinks,XML中的自动补全、错误检查等。
src/main/java/com/rensanning/springboot/mapper/TodoMapper.xml
启动应用是src/main/resources/schema.sql会被自动执行:
[quote]2017-02-10 15:13:24.614 INFO 7520 --- [ main] o.s.jdbc.datasource.init.ScriptUtils : Executing SQL script from URL [file:/D:/springbootsample/MybatisDemo/target/classes/schema.sql]
2017-02-10 15:13:24.657 INFO 7520 --- [ main] o.s.jdbc.datasource.init.ScriptUtils : Executed SQL script from URL [file:/D:/springbootsample/MybatisDemo/target/classes/schema.sql] in 43 ms.[/quote]
可以通过设置关闭自动执行
src/main/resources/application.properties
[quote]spring.datasource.initialize=false[/quote]
POST一条数据
http://localhost:8080/set.json?details=this+is+details&title=test
[quote]{"id":1,"title":"test","details":"this is details","finished":1}[/quote]
GET一条数据
http://localhost:8080/get.json?id=1
[quote]{"id":1,"title":"test","details":"this is details","finished":1}[/quote]
[b](2)设置日志级别[/b]
src/main/resources/application.properties
[quote]spring.datasource.initialize=false
logging.level.com.rensanning.springboot.mapper=TRACE[/quote]
访问 http://localhost:8080/get.json?id=1
[quote]2017-02-10 15:28:57.706 DEBUG 1976 --- [nio-8080-exec-1] c.r.springboot.mapper.TodoMapper.select : ==> Preparing: SELECT id, title, details, finished FROM todo WHERE id = ?
2017-02-10 15:28:57.720 DEBUG 1976 --- [nio-8080-exec-1] c.r.springboot.mapper.TodoMapper.select : ==> Parameters: 1(Integer)
2017-02-10 15:28:57.738 TRACE 1976 --- [nio-8080-exec-1] c.r.springboot.mapper.TodoMapper.select : <== Columns: id, title, details, finished
2017-02-10 15:28:57.738 TRACE 1976 --- [nio-8080-exec-1] c.r.springboot.mapper.TodoMapper.select : <== Row: 1, test, this is details, 1
2017-02-10 15:28:57.743 DEBUG 1976 --- [nio-8080-exec-1] c.r.springboot.mapper.TodoMapper.select : <== Total: 1[/quote]
[b]Log4jdbc输出操作日志[/b]
MyBatis 可以配置 SQL 语句的输出,但输出带问号的语句,并不是一个完整可以运行的 SQL。Log4jdbc 使用代理模式对JDBC Driver操作的拦截,记录真实 SQL。Log4jdbc-log4j2增加了对 ResultSet 以表格方式的显示。
pom.xml
src/main/resources/application.properties
src/main/resources/log4jdbc.log4j2.properties
src/main/resources/logback-spring.xml
访问 http://localhost:8080/get.json?id=1
[quote]2017-02-10 15:44:24.206 DEBUG 2560 --- [nio-8080-exec-1] jdbc.sqlonly : org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:63)
10. SELECT id, title, details, finished FROM todo WHERE id = 1
2017-02-10 15:44:24.216 INFO 2560 --- [nio-8080-exec-1] jdbc.sqltiming : SELECT id, title, details, finished FROM todo WHERE id = 1
{executed in 10 msec}
2017-02-10 15:44:24.229 INFO 2560 --- [nio-8080-exec-1] jdbc.resultsettable :
|---|------|----------------|---------|
|id |title |details |finished |
|---|------|----------------|---------|
|1 |test |this is details |1 |
|---|------|----------------|---------|[/quote]
[b](3)HikariCP连接池[/b]
默认采用的是[url=http://tomcat.apache.org/tomcat-8.0-doc/jdbc-pool.html]Tomcat JDBC Connection Pool[/url]。
pom.xml
src/main/resources/application.properties
[quote]2017-02-10 16:05:12.749 INFO 2328 --- [ main] o.s.j.e.a.AnnotationMBeanExporter : Located MBean 'dataSource': registering with JMX server as MBean [com.zaxxer.hikari:name=dataSource,type=HikariDataSource][/quote]
[color=red]打包成jar时的两个问题[/color]
如果mybatis的mapper文件放在和java同一目录下时,打jar包时xml文件不能被包含进jar文件,需要使用以下插件。
打包成jar时,setTypeAliasesPackage("xxx")找不到类的问题。MyBatis通过VFS来扫描,在Spring Boot中由于是嵌套Jar,导致Mybatis默认的VFS实现DefaultVFS无法扫描嵌套Jar中的类,需要改成SpringBootVFS扫描。
pom.xml
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.2.0</version>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
</dependency>
src/main/resources/schema.sql
CREATE TABLE todo
(
id serial NOT NULL,
title character varying(50),
details text,
finished integer,
CONSTRAINT todo_pkey PRIMARY KEY (id)
);
src/main/resources/application.properties
spring.datasource.url=jdbc:postgresql://localhost:5432/test
spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.username=user
spring.datasource.password=123456
src/main/java/com/rensanning/springboot/controller/TodoController.java
@RestController
public class TodoController {
@Autowired
private TodoService todoService;
@RequestMapping(value = "/get.json", method = RequestMethod.GET)
public Todo get(@RequestParam(value = "id") String id) {
return todoService.select(Integer.parseInt(id));
}
@RequestMapping(value = "/set.json", method = RequestMethod.POST)
public Todo set(@RequestParam(value = "title") String title,
@RequestParam(value = "details") String details) {
int id = todoService.insert(title, details);
return todoService.select(id);
}
}
src/main/java/com/rensanning/springboot/domain/Todo.java
public class Todo {
private int id;
private String title;
private String details;
private int finished;
// setter/getter
}
src/main/java/com/rensanning/springboot/service/TodoService.java
@Service
public class TodoService {
@Autowired
private TodoMapper todoMapper;
public Todo select(int id) {
return todoMapper.select(id);
}
public int insert(String title, String details) {
Todo todo = new Todo();
todo.setTitle(title);
todo.setDetails(details);
todo.setFinished(1);
todoMapper.insert(todo);
return todo.getId();
}
}
src/main/java/com/rensanning/springboot/mapper/TodoMapper.java
@Mapper
public interface TodoMapper {
void insert(Todo todo);
Todo select(int id);
}
可以不使用@Mapper注解自动扫描Mapper
@MapperScan("com.rensanning.springboot.mapper")
@SpringBootApplication
public class MybatisDemoApplication {
// ...
}
[url=https://github.com/mybatis/mybatipse]Mybatipse[/url]:MyBatis 的一个Eclipse插件。提供Java和XML的Hyperlinks,XML中的自动补全、错误检查等。
src/main/java/com/rensanning/springboot/mapper/TodoMapper.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="com.rensanning.springboot.mapper.TodoMapper">
<insert id="insert" useGeneratedKeys="true" keyProperty="id">
INSERT INTO todo (title, details, finished) VALUES (#{title}, #{details}, #{finished})
</insert>
<select id="select" resultType="com.rensanning.springboot.domain.Todo">
SELECT id, title, details, finished FROM todo WHERE id = #{id}
</select>
</mapper>
启动应用是src/main/resources/schema.sql会被自动执行:
[quote]2017-02-10 15:13:24.614 INFO 7520 --- [ main] o.s.jdbc.datasource.init.ScriptUtils : Executing SQL script from URL [file:/D:/springbootsample/MybatisDemo/target/classes/schema.sql]
2017-02-10 15:13:24.657 INFO 7520 --- [ main] o.s.jdbc.datasource.init.ScriptUtils : Executed SQL script from URL [file:/D:/springbootsample/MybatisDemo/target/classes/schema.sql] in 43 ms.[/quote]
可以通过设置关闭自动执行
src/main/resources/application.properties
[quote]spring.datasource.initialize=false[/quote]
POST一条数据
http://localhost:8080/set.json?details=this+is+details&title=test
[quote]{"id":1,"title":"test","details":"this is details","finished":1}[/quote]
GET一条数据
http://localhost:8080/get.json?id=1
[quote]{"id":1,"title":"test","details":"this is details","finished":1}[/quote]
[b](2)设置日志级别[/b]
src/main/resources/application.properties
[quote]spring.datasource.initialize=false
logging.level.com.rensanning.springboot.mapper=TRACE[/quote]
访问 http://localhost:8080/get.json?id=1
[quote]2017-02-10 15:28:57.706 DEBUG 1976 --- [nio-8080-exec-1] c.r.springboot.mapper.TodoMapper.select : ==> Preparing: SELECT id, title, details, finished FROM todo WHERE id = ?
2017-02-10 15:28:57.720 DEBUG 1976 --- [nio-8080-exec-1] c.r.springboot.mapper.TodoMapper.select : ==> Parameters: 1(Integer)
2017-02-10 15:28:57.738 TRACE 1976 --- [nio-8080-exec-1] c.r.springboot.mapper.TodoMapper.select : <== Columns: id, title, details, finished
2017-02-10 15:28:57.738 TRACE 1976 --- [nio-8080-exec-1] c.r.springboot.mapper.TodoMapper.select : <== Row: 1, test, this is details, 1
2017-02-10 15:28:57.743 DEBUG 1976 --- [nio-8080-exec-1] c.r.springboot.mapper.TodoMapper.select : <== Total: 1[/quote]
[b]Log4jdbc输出操作日志[/b]
MyBatis 可以配置 SQL 语句的输出,但输出带问号的语句,并不是一个完整可以运行的 SQL。Log4jdbc 使用代理模式对JDBC Driver操作的拦截,记录真实 SQL。Log4jdbc-log4j2增加了对 ResultSet 以表格方式的显示。
pom.xml
<dependency>
<groupId>org.bgee.log4jdbc-log4j2</groupId>
<artifactId>log4jdbc-log4j2-jdbc4.1</artifactId>
<version>1.16</version>
</dependency>
src/main/resources/application.properties
spring.datasource.url = jdbc:postgresql://localhost:5432/mydb
spring.datasource.driverClassName = org.postgresql.Driver
->
spring.datasource.url = jdbc:log4jdbc:postgresql://localhost:5432/mydb
spring.datasource.driverClassName = net.sf.log4jdbc.sql.jdbcapi.DriverSpy
src/main/resources/log4jdbc.log4j2.properties
log4jdbc.spylogdelegator.name=net.sf.log4jdbc.log.slf4j.Slf4jSpyLogDelegator
src/main/resources/logback-spring.xml
<?xml version="1.0" encoding="UTF-8"?>
<configuration>
<include resource="org/springframework/boot/logging/logback/base.xml"/>
<!-- log4jdbc-log4j2 -->
<logger name="jdbc.sqlonly" level="DEBUG"/>
<logger name="jdbc.sqltiming" level="INFO"/>
<logger name="jdbc.audit" level="OFF"/>
<logger name="jdbc.resultset" level="ERROR"/>
<logger name="jdbc.resultsettable" level="DEBUG"/>
<logger name="jdbc.connection" level="OFF"/>
</configuration>
访问 http://localhost:8080/get.json?id=1
[quote]2017-02-10 15:44:24.206 DEBUG 2560 --- [nio-8080-exec-1] jdbc.sqlonly : org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:63)
10. SELECT id, title, details, finished FROM todo WHERE id = 1
2017-02-10 15:44:24.216 INFO 2560 --- [nio-8080-exec-1] jdbc.sqltiming : SELECT id, title, details, finished FROM todo WHERE id = 1
{executed in 10 msec}
2017-02-10 15:44:24.229 INFO 2560 --- [nio-8080-exec-1] jdbc.resultsettable :
|---|------|----------------|---------|
|id |title |details |finished |
|---|------|----------------|---------|
|1 |test |this is details |1 |
|---|------|----------------|---------|[/quote]
[b](3)HikariCP连接池[/b]
默认采用的是[url=http://tomcat.apache.org/tomcat-8.0-doc/jdbc-pool.html]Tomcat JDBC Connection Pool[/url]。
pom.xml
<dependency>
<groupId>com.zaxxer</groupId>
<artifactId>HikariCP</artifactId>
</dependency>
src/main/resources/application.properties
spring.datasource.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.hikari.maximum-pool-size=5
spring.datasource.hikari.connection-timeout=5000
[quote]2017-02-10 16:05:12.749 INFO 2328 --- [ main] o.s.j.e.a.AnnotationMBeanExporter : Located MBean 'dataSource': registering with JMX server as MBean [com.zaxxer.hikari:name=dataSource,type=HikariDataSource][/quote]
[color=red]打包成jar时的两个问题[/color]
如果mybatis的mapper文件放在和java同一目录下时,打jar包时xml文件不能被包含进jar文件,需要使用以下插件。
<plugin>
<groupId>org.codehaus.mojo</groupId>
<artifactId>build-helper-maven-plugin</artifactId>
<executions>
<execution>
<id>add-resource</id>
<phase>generate-resources</phase>
<goals>
<goal>add-resource</goal>
</goals>
<configuration>
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
</resource>
</resources>
</configuration>
</execution>
</executions>
</plugin>
打包成jar时,setTypeAliasesPackage("xxx")找不到类的问题。MyBatis通过VFS来扫描,在Spring Boot中由于是嵌套Jar,导致Mybatis默认的VFS实现DefaultVFS无法扫描嵌套Jar中的类,需要改成SpringBootVFS扫描。
@Bean
public ConfigurationCustomizer mybatisConfigurationCustomizer() {
return new ConfigurationCustomizer() {
@Override
public void customize(org.apache.ibatis.session.Configuration config) {
config.setVfsImpl(SpringBootVFS.class);
config.getTypeAliasRegistry().registerAliases(Constants.BASE_PACKAGE_ALIAS);
}
};
}