Spring Boot整合MyBatis

本文最后更新于：2 年前

引言

随着微服务和容器化技术的普及，Spring Boot成为Java开发者首选的微服务框架之一，而MyBatis作为一个灵活且功能强大的持久层框架，其与Spring Boot的集成可以极大地提高数据库操作的效率和灵活性。本文将详细介绍如何在Spring Boot项目中集成MyBatis，从依赖管理到配置细节，每一步都旨在帮助开发者构建一个高效、可维护的应用。

引入依赖

在Spring Boot项目中引入MySQL连接、MyBatis、pagehelper的Maven依赖：

1
2
3
4
5
6
7
8
9
10
11
12
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
</dependency>
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
</dependency>

数据源配置

这里我们采用“配置文件+Java配置类”的形式来配置数据源

在项目配置文件中指定数据库连接信息

1
2
3
4
5
6
7
8
# 数据源连接url
spring.datasource.url=jdbc:mysql://localhost:3306/mybatis_db?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
# 数据库连接用户名
spring.datasource.username=root
# 数据库连接密码
spring.datasource.password=root
# 数据库连接驱动
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

这里我们采用连接池方案，配置连接池的相关参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 连接池最小空闲连接数
spring.datasource.minimum-idle=5
# 连接池最大连接数
spring.datasource.maximum-pool-size=20
# 连接池空闲连接存活时间
spring.datasource.idle-timeout=30000
# 连接池连接最大存活时间
spring.datasource.max-lifetime=2000000
# 连接池等待连接超时时间
spring.datasource.connection-timeout=30000
# 连接池名称
spring.datasource.pool-name=MyHikariCP
# 连接池是否自动提交
spring.datasource.auto-commit=true
# 连接池验证SQL
spring.datasource.connection-test-query=SELECT 1
# 连接池验证SQl超时时间
spring.datasource.validation-timeout=3000

对应的Java Properties类为

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
@Data
@NoArgsConstructor
@AllArgsConstructor
@Accessors(chain = true)
@Component
@ConfigurationProperties(prefix = "spring.datasource")
public class DataSourceProperties {

    private String url;

    private String username;

    private String password;

    private String driverClassName;

    private int minimumIdle;

    private int maximumPoolSize;

    private long idleTimeout;

    private long maxLifetime;

    private long connectionTimeout;

    private String poolName;

    private boolean autoCommit;

    private String connectionTestQuery;

    private long validationTimeout;

}

对应的数据源实现配置为

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
@Configuration
@RequiredArgsConstructor
public class DataSourceConfig {

    private final DataSourceProperties dataSourceProperties;

    @Bean
    public DataSource dataSource() {
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setJdbcUrl(dataSourceProperties.getUrl());
        dataSource.setUsername(dataSourceProperties.getUsername());
        dataSource.setPassword(dataSourceProperties.getPassword());
        dataSource.setDriverClassName(dataSourceProperties.getDriverClassName());
        dataSource.setMinimumIdle(dataSourceProperties.getMinimumIdle());
        dataSource.setMaximumPoolSize(dataSourceProperties.getMaximumPoolSize());
        dataSource.setIdleTimeout(dataSourceProperties.getIdleTimeout());
        dataSource.setMaxLifetime(dataSourceProperties.getMaxLifetime());
        dataSource.setConnectionTimeout(dataSourceProperties.getConnectionTimeout());
        dataSource.setPoolName(dataSourceProperties.getPoolName());
        dataSource.setAutoCommit(dataSourceProperties.isAutoCommit());
        dataSource.setConnectionTestQuery(dataSourceProperties.getConnectionTestQuery());
        dataSource.setValidationTimeout(dataSource.getValidationTimeout());
        return dataSource;
    }

}

MyBatis自身配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
# mapper文件位置
mybatis.mapper-locations=classpath:mapper/*.xml
# 数据库字段下划线转驼峰
mybatis.configuration.map-underscore-to-camel-case=true
# 启用懒加载
mybatis.configuration.lazy-loading-enabled=true
# 允许单个语句返回多结果集
mybatis.configuration.multiple-result-sets-enabled=true
# 使用列标签代替列名
mybatis.configuration.use-column-label=true
# 驱动程序提示每次批量返回的结果行数
mybatis.configuration.default-fetch-size=100
# SQL语句的默认超时时间
mybatis.configuration.default-statement-timeout=30
# 允许JDBC支持自动生成主键
mybatis.configuration.use-generated-keys=true
# 执行器类型
mybatis.configuration.default-executor-type=batch
# 开启二级缓存
mybatis.configuration.cache-enabled=true
# 二级缓存的缓存区域
mybatis.configuration.local-cache-scope=SESSION
# 自动映射所有结果集
mybatis.configuration.auto-mapping-behavior=FULL
# 参数为null时的JDBC类型
mybatis.configuration.jdbc-type-for-null=null

分页插件配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 分页插件数据库方言
pagehelper.helper-dialect=mysql
# 自动判断数据库方言
# pagehelper.auto-dialect=true
# 分页参数合理化
pagehelper.reasonable=true
# 支持通过Mapper接口参数来传递分页参数
pagehelper.support-methods-arguments=true
# 分页参数别名
pagehelper.params=pageNum=page;pageSize=size
# 通过offest方式进行分页
# pagehelper.offset-as-page-num=true
# 查询后关闭数据库连接（一般交给web容器管理）
# pagehelper.close-conn=false

日志配置

在系统日志配置文件中指定相关类的日志级别

1
2
3
4
<logger name="com.apache.ibatis" level="TRACE"/>
<logger name="java.sql.Connection" level="DEBUG"/>
<logger name="java.sql.Statement" level="DEBUG"/>
<logger name="java.sql.PreparedStatement" level="DEBUG"/>

测试

  1. 以Student表为例，编写数据库Mapper文件

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    <?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="space.yangtao.mapper.StudentMapper">

        <select id="list" resultType="space.yangtao.domain.po.StudentPO">
            SELECT * FROM tt_student
        </select>

    </mapper>

  2. 对应的Mapper接口为

    1
    2
    3
    4
    5
    6
    @Mapper
    public interface StudentMapper {

        List<StudentPO> list();

    }

  3. 定义服务层接口及对应的实现类

    1
    2
    3
    4
    5
    public interface StudentService {

        PageInfo<StudentPO> page();

    }
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    @Service
    public class StudentServiceImpl implements StudentService {

        @Resource
        private StudentMapper studentMapper;

        @Override
        @Transactional(rollbackFor = Exception.class, readOnly = true)
        public PageInfo<StudentPO> page() {
            PageHelper.startPage(2, 2);
            return new PageInfo<>(studentMapper.list());
        }

    }

  4. 编写视图层接口，用于测试

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    @RestController
    @RequestMapping("/student")
    public class StudentController {

        @Resource
        private StudentService studentService;

        @GetMapping("/page")
        public PageInfo<StudentPO> page() {
            return studentService.page();
        }

    }

  5. 启动项目，通过HTTP请求进行测试

    1
    curl -XGET http://localhost:8080/student/page

    请求结果如下

    1
    {"total":3,"list":[{"id":3,"classId":2,"name":"zhangsan2","height":180.0,"gender":"男","birthday":"1989-12-31T16:00:00.000+00:00","createTime":"2022-07-01T14:10:22.000+00:00"}],"pageNum":2,"pageSize":2,"size":1,"startRow":3,"endRow":3,"pages":2,"prePage":1,"nextPage":0,"isFirstPage":false,"isLastPage":true,"hasPreviousPage":true,"hasNextPage":false,"navigatePages":8,"navigatepageNums":[1,2],"navigateFirstPage":1,"navigateLastPage":2}

  6. 控制台输出如下

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    18:00:56.756 [http-nio-8080-exec-3] DEBUG org.springframework.web.servlet.DispatcherServlet - GET "/student/page", parameters={}
    18:00:56.756 [http-nio-8080-exec-3] DEBUG o.s.w.s.m.m.a.RequestMappingHandlerMapping - Mapped to space.yangtao.domain.controller.StudentController#page()
    18:00:56.757 [http-nio-8080-exec-3] DEBUG o.s.jdbc.support.JdbcTransactionManager - Creating new transaction with name [space.yangtao.service.StudentServiceImpl.page]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT,-java.lang.Exception
    18:00:56.757 [http-nio-8080-exec-3] DEBUG o.s.jdbc.support.JdbcTransactionManager - Acquired Connection [HikariProxyConnection@493797028 wrapping com.mysql.cj.jdbc.ConnectionImpl@2ac4f26f] for JDBC transaction
    18:00:56.757 [http-nio-8080-exec-3] DEBUG o.s.jdbc.support.JdbcTransactionManager - Switching JDBC Connection [HikariProxyConnection@493797028 wrapping com.mysql.cj.jdbc.ConnectionImpl@2ac4f26f] to manual commit
    18:00:56.757 [http-nio-8080-exec-3] DEBUG org.mybatis.spring.SqlSessionUtils - Creating a new SqlSession
    18:00:56.758 [http-nio-8080-exec-3] DEBUG org.mybatis.spring.SqlSessionUtils - Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@195129d1]
    18:00:56.758 [http-nio-8080-exec-3] DEBUG SQL_CACHE - Cache Hit Ratio [SQL_CACHE]: 0.6666666666666666
    18:00:56.758 [http-nio-8080-exec-3] DEBUG o.m.spring.transaction.SpringManagedTransaction - JDBC Connection [HikariProxyConnection@493797028 wrapping com.mysql.cj.jdbc.ConnectionImpl@2ac4f26f] will be managed by Spring
    18:00:56.758 [http-nio-8080-exec-3] DEBUG space.yangtao.mapper.StudentMapper.list_COUNT - ==>  Preparing: SELECT count(0) FROM tt_student
    18:00:56.758 [http-nio-8080-exec-3] DEBUG space.yangtao.mapper.StudentMapper.list_COUNT - ==> Parameters: 
    18:00:56.759 [http-nio-8080-exec-3] DEBUG space.yangtao.mapper.StudentMapper.list_COUNT - <==      Total: 1
    18:00:56.760 [http-nio-8080-exec-3] DEBUG space.yangtao.mapper.StudentMapper.list - ==>  Preparing: SELECT * FROM tt_student LIMIT ?, ?
    18:00:56.760 [http-nio-8080-exec-3] DEBUG space.yangtao.mapper.StudentMapper.list - ==> Parameters: 2(Long), 2(Integer)
    18:00:56.760 [http-nio-8080-exec-3] DEBUG space.yangtao.mapper.StudentMapper.list - <==      Total: 1
    18:00:56.761 [http-nio-8080-exec-3] DEBUG org.mybatis.spring.SqlSessionUtils - Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@195129d1]
    18:00:56.761 [http-nio-8080-exec-3] DEBUG org.mybatis.spring.SqlSessionUtils - Transaction synchronization committing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@195129d1]
    18:00:56.761 [http-nio-8080-exec-3] DEBUG org.mybatis.spring.SqlSessionUtils - Transaction synchronization deregistering SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@195129d1]
    18:00:56.761 [http-nio-8080-exec-3] DEBUG org.mybatis.spring.SqlSessionUtils - Transaction synchronization closing SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@195129d1]
    18:00:56.761 [http-nio-8080-exec-3] DEBUG o.s.jdbc.support.JdbcTransactionManager - Initiating transaction commit
    18:00:56.761 [http-nio-8080-exec-3] DEBUG o.s.jdbc.support.JdbcTransactionManager - Committing JDBC transaction on Connection [HikariProxyConnection@493797028 wrapping com.mysql.cj.jdbc.ConnectionImpl@2ac4f26f]
    18:00:56.761 [http-nio-8080-exec-3] DEBUG o.s.jdbc.support.JdbcTransactionManager - Releasing JDBC Connection [HikariProxyConnection@493797028 wrapping com.mysql.cj.jdbc.ConnectionImpl@2ac4f26f] after transaction
    18:00:56.761 [http-nio-8080-exec-3] DEBUG o.s.w.s.m.m.a.RequestResponseBodyMethodProcessor - Using 'application/json', given [*/*] and supported [application/json, application/*+json, application/json, application/*+json]
    18:00:56.761 [http-nio-8080-exec-3] DEBUG o.s.w.s.m.m.a.RequestResponseBodyMethodProcessor - Writing [PageInfo{pageNum=2, pageSize=2, size=1, startRow=3, endRow=3, total=3, pages=2, list=Page{count=true (truncated)...]
    18:00:56.762 [http-nio-8080-exec-3] DEBUG org.springframework.web.servlet.DispatcherServlet - Completed 200 OK

    由请求结果和控制台日志可知，Web环境下MyBatis的各项基本配置已经配置成功。

总结

通过本文的介绍，开发者可以详细了解如何在Spring Boot环境下配置和使用MyBatis，包括如何设置数据源、如何配置MyBatis以优化执行和管理SQL会话，以及如何利用MyBatis的高级功能如分页插件来增强应用的性能。此外，文章还探讨了如何通过适当的日志配置来监控和调试数据库交互，确保应用的稳定运行。整合MyBatis后，开发者将能够更有效地管理数据库操作，同时保持代码的清晰和组织性。

MyBatis
MyBatis

本博客所有文章除特别声明外，均采用 CC BY-SA 4.0 协议 ，转载请注明出处！