MyBatis 是一款优秀的持久层框架，它支持定制化 SQL、存储过程以及高级映射。MyBatis 避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集。MyBatis 可以使用简单的 XML 或注解来配置和映射原生信息，将接口和 Java 的 POJOs(Plain Ordinary Java Object,普通的 Java对象)映射成数据库中的记录。
在Spring Boot 框架下，MyBatis官方提供了一套自动化的配置方案，让Mybatis可以做到开箱即用。
1、新建y一个Spring Boot项目，pom.xml文件中添加对应的引用
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.8.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>cn.weisoft</groupId>
<artifactId>test-web</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>test-web</name>
<description>Demo project for Spring Boot</description> <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-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.0</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.20</version>
</dependency>
</dependencies> <build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>false</filtering>
</resource>
</resources>
</build> </project>
pom.xml
2、创建数据库表及存储过程
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0; -- ----------------------------
-- Table structure for book
-- ----------------------------
DROP TABLE IF EXISTS `book`;
CREATE TABLE `book` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
`author` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
`publishDate` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0),
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 81 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic; SET FOREIGN_KEY_CHECKS = 1; drop procedure if exists p_get_book_by_id;
DELIMITER //
CREATE PROCEDURE p_get_book_by_id(IN v_id integer)
BEGIN
select *
from book
where id = v_id; END DROP PROCEDURE IF EXISTS p_ins_book; DELIMITER //
CREATE PROCEDURE p_ins_book (
OUT v_id INTEGER,
IN v_name VARCHAR ( 100 ),
v_author VARCHAR (50),
v_publishDate datetime
)
BEGIN
INSERT INTO book ( NAME,author,publishDate)
VALUES
( v_name, v_author,v_publishDate); SET v_id = LAST_INSERT_ID(); END // delimiter ; call p_ins_book(@v_id,'c#','wilson.fu',now());
3、建立对应 实体类
package cn.weisoft.testweb.entity;
import java.util.Date;
public class Book {
private Integer id;
private String name;
private String author;
private Date publishDate;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
public Date getPublishDate() {
return publishDate;
}
public void setPublishDate(Date publishDate) {
this.publishDate = publishDate;
}
}
4、建立Mapper类及配置文件
package cn.weisoft.testweb.mapper; import cn.weisoft.testweb.entity.Book;
import org.apache.ibatis.annotations.Mapper; @Mapper
public interface BookMapper {
Book getBookById(int id); int addBook(Book book); int addBookByProc(Book book); Book selectBookById(int id);
}
BookMapper
<?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="cn.weisoft.testweb.mapper.BookMapper">
<select id="getBookById" parameterType="int" resultType="cn.weisoft.testweb.entity.Book">
select * from book where id=#{id}
</select>
<insert id="addBook" parameterType="cn.weisoft.testweb.entity.Book">
INSERT into book(name,author,publishDate) VALUES (#{name},#{author},#{publishDate})
</insert>
<insert id="addBookByProc" parameterType="cn.weisoft.testweb.entity.Book" statementType="CALLABLE">
{call p_ins_book(
#{id,mode=OUT,jdbcType=INTEGER},
#{name,mode=IN},
#{author,mode=IN},
#{publishDate,mode=IN}
)}
</insert>
<select id="selectBookById" parameterType="int" resultType="cn.weisoft.testweb.entity.Book" statementType="CALLABLE">
{call p_get_book_by_id(
#{id,mode=IN}
)}
</select>
</mapper>
BookMapper.xml
配置文件中包括通过SQL语句及存储过程访问数据库的两种方式
5、建立Service类
package cn.weisoft.testweb.serivce; import cn.weisoft.testweb.entity.Book;
import cn.weisoft.testweb.mapper.BookMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service; @Service
public class BookService2 {
@Autowired
BookMapper bookMapper; public Book getBookById(int id){
return bookMapper.getBookById(id);
} public int addBook(Book book){
return bookMapper.addBook(book);
} public int addBookByProc(Book book){
return bookMapper.addBookByProc(book);
} public Book selectBookById(int id){
return bookMapper.selectBookById(id);
}
}
6、添加Controller测试类
package cn.weisoft.testweb.controller; import cn.weisoft.testweb.entity.Book;
import cn.weisoft.testweb.serivce.BookService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController; import java.util.Date; @RestController
public class BookController {
@Autowired
BookService bookService; @GetMapping("/book/getBookById")
public Book getBookById(int id){
return bookService.getBookById(id);
} @GetMapping("/book/selectBookById")
public Book selectBookById(int id){
return bookService.selectBookById(id);
} @GetMapping("/book/addBook")
public int addBook(){
Book book =new Book();
book.setName("C#入门。");
book.setAuthor("wilson.fu88");
book.setPublishDate(new Date()); return bookService.addBook(book);
} @GetMapping("/book/addBookByProc")
public Book addBookByProc(){
Book book =new Book();
book.setName("Spring Boot 入门。");
book.setAuthor("wilson.fu1982");
book.setPublishDate(new Date()); bookService.addBookByProc(book);
return book;
}
}
BookController
基于XML配置的方式，默认BookMapper.xml不会自动复制到target对应的文件夹，需要在pom.xml中加上如下配置，这个不是推荐的方式，因为spring boot本身讲究的是0配置，后面将会写一遍基于注解方式实现上面的数据访问
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>false</filtering>
</resource>
</resources>
</build>
pom-build
7、设置应用的配置文件，里面主要包含数据库连接，日志配置等信息
spring:
datasource:
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://localhost/testdb?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false&maxReconnects=15000&serverTimezone=Asia/Shanghai
username: root
password: root
logging:
path: E:/temp/20190906/mybatis-study
file: test-web.log
level:
cn:
weisoft:
mybatisstudy:
mapper: debug
application.yml
8、让Mybatis的SQL语句输出到日志中配置，在resources文件夹添加logback.xml文件，配置如下
<?xml version="1.0" encoding="UTF-8"?>
<configuration debug="false">
<!--定义日志文件的存储地址 勿在 LogBack 的配置中使用相对路径-->
<property name="LOG_HOME" value="E:/temp/20190906/test-web" />
<!-- 控制台输出 -->
<appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
<encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">
<!--格式化输出：%d表示日期，%thread表示线程名，%-5level：级别从左显示5个字符宽度%msg：日志消息，%n是换行符-->
<pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{50} - %msg%n</pattern>
</encoder>
</appender>
<!-- 按照每天生成日志文件 -->
<appender name="FILE" class="ch.qos.logback.core.rolling.RollingFileAppender">
<rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
<!--日志文件输出的文件名-->
<FileNamePattern>${LOG_HOME}/TestWeb.log.%d{yyyy-MM-dd}.log</FileNamePattern>
<!--日志文件保留天数-->
<MaxHistory>30</MaxHistory>
</rollingPolicy>
<encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">
<!--格式化输出：%d表示日期，%thread表示线程名，%-5level：级别从左显示5个字符宽度%msg：日志消息，%n是换行符-->
<pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{50} - %msg%n</pattern>
</encoder>
<!--日志文件最大的大小-->
<triggeringPolicy class="ch.qos.logback.core.rolling.SizeBasedTriggeringPolicy">
<MaxFileSize>10MB</MaxFileSize>
</triggeringPolicy>
</appender> <!--myibatis log configure-->
<logger name="com.apache.ibatis" level="DEBUG"/>
<logger name="java.sql.Connection" level="DEBUG"/>
<logger name="java.sql.Statement" level="DEBUG"/>
<logger name="java.sql.PreparedStatement" level="DEBUG"/> <!-- 日志输出级别 -->
<root level="DEBUG">
<appender-ref ref="STDOUT" />
<appender-ref ref="FILE" />
</root>
<!--日志异步到数据库 -->
<!--<appender name="DB" class="ch.qos.logback.classic.db.DBAppender">-->
<!--<!–日志异步到数据库 –>-->
<!--<connectionSource class="ch.qos.logback.core.db.DriverManagerConnectionSource">-->
<!--<!–连接池 –>-->
<!--<dataSource class="com.mchange.v2.c3p0.ComboPooledDataSource">-->
<!--<driverClass>com.mysql.jdbc.Driver</driverClass>-->
<!--<url>jdbc:mysql://127.0.0.1:3306/databaseName</url>-->
<!--<user>root</user>-->
<!--<password>root</password>-->
<!--</dataSource>-->
<!--</connectionSource>-->
<!--</appender>-->
</configuration>
logback.xml