漂泊雪狼的博客

思考，讨论，分享C#,JavaScript,.NET,Oracle,SQL Server……技术

导航

Spring Boot 框架下使用MyBatis访问数据库之基于XML配置的方式

Posted on 2019-09-08 13:42  漂泊雪狼  阅读(922)  评论(0)  编辑  收藏  举报

    MyBatis 是一款优秀的持久层框架，它支持定制化 SQL、存储过程以及高级映射。MyBatis 避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集。MyBatis 可以使用简单的 XML 或注解来配置和映射原生信息，将接口和 Java 的 POJOs(Plain Ordinary Java Object,普通的 Java对象)映射成数据库中的记录。

   在Spring Boot 框架下，MyBatis官方提供了一套自动化的配置方案，让Mybatis可以做到开箱即用。

　　1、新建y一个Spring Boot项目，pom.xml文件中添加对应的引用

 1 <?xml version="1.0" encoding="UTF-8"?>
 2 <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 3          xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
 4     <modelVersion>4.0.0</modelVersion>
 5     <parent>
 6         <groupId>org.springframework.boot</groupId>
 7         <artifactId>spring-boot-starter-parent</artifactId>
 8         <version>2.1.8.RELEASE</version>
 9         <relativePath/> <!-- lookup parent from repository -->
10     </parent>
11     <groupId>cn.weisoft</groupId>
12     <artifactId>test-web</artifactId>
13     <version>0.0.1-SNAPSHOT</version>
14     <name>test-web</name>
15     <description>Demo project for Spring Boot</description>
16 
17     <properties>
18         <java.version>1.8</java.version>
19     </properties>
20 
21     <dependencies>
22         <dependency>
23             <groupId>org.springframework.boot</groupId>
24             <artifactId>spring-boot-starter</artifactId>
25         </dependency>
26         <dependency>
27             <groupId>org.springframework.boot</groupId>
28             <artifactId>spring-boot-starter-web</artifactId>
29         </dependency>
30         <dependency>
31             <groupId>org.springframework.boot</groupId>
32             <artifactId>spring-boot-starter-test</artifactId>
33             <scope>test</scope>
34         </dependency>
35         <dependency>
36             <groupId>org.springframework.boot</groupId>
37             <artifactId>spring-boot-starter-jdbc</artifactId>
38         </dependency>
39         <dependency>
40             <groupId>mysql</groupId>
41             <artifactId>mysql-connector-java</artifactId>
42             <scope>runtime</scope>
43         </dependency>
44         <dependency>
45             <groupId>org.mybatis.spring.boot</groupId>
46             <artifactId>mybatis-spring-boot-starter</artifactId>
47             <version>2.1.0</version>
48         </dependency>
49         <dependency>
50             <groupId>com.alibaba</groupId>
51             <artifactId>druid</artifactId>
52             <version>1.1.20</version>
53         </dependency>
54     </dependencies>
55 
56     <build>
57         <plugins>
58             <plugin>
59                 <groupId>org.springframework.boot</groupId>
60                 <artifactId>spring-boot-maven-plugin</artifactId>
61             </plugin>
62         </plugins>
63           <resources>  
64             <resource>  
65                 <directory>src/main/java</directory>  
66                 <includes>  
67                         <include>**/*.properties</include>  
68                         <include>**/*.xml</include>  
69                     </includes>  
70                 <filtering>false</filtering>  
71             </resource>  
72            </resources>  
73     </build>
74 
75 </project>
pom.xml

 

　　2、创建数据库表及存储过程

 1 SET NAMES utf8mb4;
 2 SET FOREIGN_KEY_CHECKS = 0;
 3 
 4 -- ----------------------------
 5 -- Table structure for book
 6 -- ----------------------------
 7 DROP TABLE IF EXISTS `book`;
 8 CREATE TABLE `book`  (
 9   `id` int(11) NOT NULL AUTO_INCREMENT,
10   `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
11   `author` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
12   `publishDate` datetime(0) NULL DEFAULT CURRENT_TIMESTAMP(0),
13   PRIMARY KEY (`id`) USING BTREE
14 ) ENGINE = InnoDB AUTO_INCREMENT = 81 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
15 
16 SET FOREIGN_KEY_CHECKS = 1;
17 
18 
19 
20 drop procedure  if exists p_get_book_by_id;
21 DELIMITER //
22 CREATE PROCEDURE p_get_book_by_id(IN v_id integer)
23 BEGIN
24     select *
25     from book
26     where id = v_id;
27 
28 END
29 
30 DROP PROCEDURE IF EXISTS p_ins_book;
31 
32 DELIMITER //
33 CREATE PROCEDURE p_ins_book (
34     OUT v_id INTEGER,
35     IN v_name VARCHAR ( 100 ),
36     v_author VARCHAR (50),
37     v_publishDate datetime    
38     )
39     BEGIN
40     INSERT INTO book ( NAME,author,publishDate)
41     VALUES
42         ( v_name, v_author,v_publishDate);
43     
44     SET v_id = LAST_INSERT_ID();
45     
46 END //
47 
48 delimiter ;
49 
50 call p_ins_book(@v_id,'c#','wilson.fu',now());
View Code

3、建立对应 实体类

 1 package cn.weisoft.testweb.entity;
 2 
 3 
 4 import java.util.Date;
 5 
 6 public class Book {
 7     private Integer id;
 8     private String name;
 9 
10     private String author;
11 
12     private Date publishDate;
13 
14     public Integer getId() {
15         return id;
16     }
17 
18     public void setId(Integer id) {
19         this.id = id;
20     }
21 
22     public String getName() {
23         return name;
24     }
25 
26     public void setName(String name) {
27         this.name = name;
28     }
29 
30     public String getAuthor() {
31         return author;
32     }
33 
34     public void setAuthor(String author) {
35         this.author = author;
36     }
37 
38     public Date getPublishDate() {
39         return publishDate;
40     }
41 
42     public void setPublishDate(Date publishDate) {
43         this.publishDate = publishDate;
44     }
45 }
View Code

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
 1 <?xml version="1.0" encoding="UTF-8"?>
 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 3 <mapper namespace="cn.weisoft.testweb.mapper.BookMapper">
 4     <select id="getBookById" parameterType="int" resultType="cn.weisoft.testweb.entity.Book">
 5         select * from book where id=#{id}
 6     </select>
 7     <insert id="addBook" parameterType="cn.weisoft.testweb.entity.Book">
 8         INSERT into book(name,author,publishDate) VALUES (#{name},#{author},#{publishDate})
 9     </insert>
10     <insert id="addBookByProc" parameterType="cn.weisoft.testweb.entity.Book" statementType="CALLABLE">
11         {call p_ins_book(
12         #{id,mode=OUT,jdbcType=INTEGER},
13         #{name,mode=IN},
14         #{author,mode=IN},
15         #{publishDate,mode=IN}
16         )}
17     </insert>
18     <select id="selectBookById" parameterType="int" resultType="cn.weisoft.testweb.entity.Book" statementType="CALLABLE">
19       {call p_get_book_by_id(
20       #{id,mode=IN}
21       )}
22     </select>
23 </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);
    }
}
View Code

 

6、添加Controller测试类

 1 package cn.weisoft.testweb.controller;
 2 
 3 import cn.weisoft.testweb.entity.Book;
 4 import cn.weisoft.testweb.serivce.BookService;
 5 import org.springframework.beans.factory.annotation.Autowired;
 6 import org.springframework.web.bind.annotation.GetMapping;
 7 import org.springframework.web.bind.annotation.RestController;
 8 
 9 import java.util.Date;
10 
11 @RestController
12 public class BookController {
13     @Autowired
14     BookService bookService;
15 
16     @GetMapping("/book/getBookById")
17     public Book getBookById(int id){
18         return bookService.getBookById(id);
19     }
20 
21 
22     @GetMapping("/book/selectBookById")
23     public Book selectBookById(int id){
24         return bookService.selectBookById(id);
25     }
26 
27 
28     @GetMapping("/book/addBook")
29     public  int addBook(){
30         Book book =new Book();
31         book.setName("C#入门。");
32         book.setAuthor("wilson.fu88");
33         book.setPublishDate(new Date());
34 
35         return bookService.addBook(book);
36     }
37 
38     @GetMapping("/book/addBookByProc")
39     public Book addBookByProc(){
40         Book book =new Book();
41         book.setName("Spring Boot 入门。");
42         book.setAuthor("wilson.fu1982");
43         book.setPublishDate(new Date());
44 
45          bookService.addBookByProc(book);
46          return book;
47     }
48 }
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、设置应用的配置文件，里面主要包含数据库连接，日志配置等信息

 1 spring:
 2   datasource:
 3     type: com.alibaba.druid.pool.DruidDataSource
 4     url: jdbc:mysql://localhost/testdb?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false&maxReconnects=15000&serverTimezone=Asia/Shanghai
 5     username: root
 6     password: root
 7 logging:
 8   path: E:/temp/20190906/mybatis-study
 9   file: test-web.log
10   level:
11     cn:
12       weisoft:
13         mybatisstudy:
14           mapper: debug
application.yml

 

8、让Mybatis的SQL语句输出到日志中配置，在resources文件夹添加logback.xml文件，配置如下

 1 <?xml version="1.0" encoding="UTF-8"?>
 2 <configuration debug="false">
 3     <!--定义日志文件的存储地址 勿在 LogBack 的配置中使用相对路径-->
 4     <property name="LOG_HOME" value="E:/temp/20190906/test-web" />
 5     <!-- 控制台输出 -->
 6     <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
 7         <encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">
 8             <!--格式化输出：%d表示日期，%thread表示线程名，%-5level：级别从左显示5个字符宽度%msg：日志消息，%n是换行符-->
 9             <pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{50} - %msg%n</pattern>
10         </encoder>
11     </appender>
12     <!-- 按照每天生成日志文件 -->
13     <appender name="FILE"  class="ch.qos.logback.core.rolling.RollingFileAppender">
14         <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
15             <!--日志文件输出的文件名-->
16             <FileNamePattern>${LOG_HOME}/TestWeb.log.%d{yyyy-MM-dd}.log</FileNamePattern>
17             <!--日志文件保留天数-->
18             <MaxHistory>30</MaxHistory>
19         </rollingPolicy>
20         <encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">
21             <!--格式化输出：%d表示日期，%thread表示线程名，%-5level：级别从左显示5个字符宽度%msg：日志消息，%n是换行符-->
22             <pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{50} - %msg%n</pattern>
23         </encoder>
24         <!--日志文件最大的大小-->
25         <triggeringPolicy class="ch.qos.logback.core.rolling.SizeBasedTriggeringPolicy">
26             <MaxFileSize>10MB</MaxFileSize>
27         </triggeringPolicy>
28     </appender>
29 
30 
31     <!--myibatis log configure-->
32     <logger name="com.apache.ibatis" level="DEBUG"/>
33     <logger name="java.sql.Connection" level="DEBUG"/>
34     <logger name="java.sql.Statement" level="DEBUG"/>
35     <logger name="java.sql.PreparedStatement" level="DEBUG"/>
36 
37     <!-- 日志输出级别 -->
38     <root level="DEBUG">
39         <appender-ref ref="STDOUT" />
40         <appender-ref ref="FILE" />
41     </root>
42     <!--日志异步到数据库 -->
43     <!--<appender name="DB" class="ch.qos.logback.classic.db.DBAppender">-->
44     <!--<!–日志异步到数据库 –>-->
45     <!--<connectionSource class="ch.qos.logback.core.db.DriverManagerConnectionSource">-->
46     <!--<!–连接池 –>-->
47     <!--<dataSource class="com.mchange.v2.c3p0.ComboPooledDataSource">-->
48     <!--<driverClass>com.mysql.jdbc.Driver</driverClass>-->
49     <!--<url>jdbc:mysql://127.0.0.1:3306/databaseName</url>-->
50     <!--<user>root</user>-->
51     <!--<password>root</password>-->
52     <!--</dataSource>-->
53     <!--</connectionSource>-->
54     <!--</appender>-->
55 </configuration>
logback.xml