Spring Boot 入门 - 基础篇（8）- 数据库操作

[b]（1）导入mybatis-spring-boot-starter[/b]
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);
          }
        };
    }