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

（1）导入mybatis-spring-boot-starter
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 {
    // ...
}

Mybatipse: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会被自动执行：

引用

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.

可以通过设置关闭自动执行
src/main/resources/application.properties

引用

spring.datasource.initialize=false

POST一条数据
http://localhost:8080/set.json?details=this+is+details&title=test

引用

{"id":1,"title":"test","details":"this is details","finished":1}

GET一条数据
http://localhost:8080/get.json?id=1

引用

{"id":1,"title":"test","details":"this is details","finished":1}

（2）设置日志级别

src/main/resources/application.properties

引用

spring.datasource.initialize=false
logging.level.com.rensanning.springboot.mapper=TRACE

访问 http://localhost:8080/get.json?id=1

引用

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

Log4jdbc输出操作日志

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

引用

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

（3）HikariCP连接池

默认采用的是Tomcat JDBC Connection Pool。

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

引用

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]

打包成jar时的两个问题

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

评论

1 楼 hanlin123 2017-05-24  

老是报错  何解？  
org.apache.ibatis.binding.BindingException: Invalid bound statement (not found): com.hhl.springbootdemo.class8.mapper.TodoMapper.select
at org.apache.ibatis.binding.MapperMethod$SqlCommand.<init>(MapperMethod.java:230)
at org.apache.ibatis.binding.MapperMethod.<init>(MapperMethod.java:48)
at org.apache.ibatis.binding.MapperProxy.cachedMapperMethod(MapperProxy.java:65)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:58)
at com.sun.proxy.$Proxy59.select(Unknown Source)
at com.hhl.springbootdemo.class8.TodoService.select(TodoService.java:17)
at com.hhl.springbootdemo.class8.TodoController.get(TodoController.java:20)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
at
org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)