越努力越幸运

0%

MyBatis在SpringBoot上的集成运用

Posted on Edited on In

前言: 最近做公司的项目时,用到的访问数据库CRUD框架为mybatis,实际使用起来经常踩坑，于是总结一下基本使用方法和踩过的坑

基本介绍：mybatis是一款基于JAVA JDBC API [^1] 的持久层框架,它支持将Java POJOs 映射成数据库中对应的记录

[^1]:Java Database connection(java数据库连接)

一. 添加依赖

1
2
3
4
5
<dependency>
  <groupId>org.mybatis</groupId>
  <artifactId>mybatis</artifactId>
  <version>x.x.x</version>
</dependency>

二. 配置SqlSessionFactory的两种方法

基本定义

  • sqlSession Factory:

    创建sqlSession的工厂,SqlSessionFactory是MyBatis的关键对象,它是个单个数据库映射关系经过编译后的内存镜像.SqlSessionFactory对象的实例可以通过SqlSessionFactoryBuilder对象类获得,而SqlSessionFactoryBuilder则可以从XML配置文件或一个预先定制的Configuration的实例构建出SqlSessionFactory的实例.每一个MyBatis的应用程序都以一个SqlSessionFactory对象的实例为核心.同时SqlSessionFactory也是线程安全的,SqlSessionFactory一旦被创建,应该在应用执行期间都存在.在应用运行期间不要重复创建多次,建议使用单例模式

  • sql session

    sqlSession: 应用程序和数据库之间的一个单线程对象,类似于JDBC的connection,线程不安全,是具体执行sql语句的实例

xml

基于xml配置定义数据库源和mapper路径

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
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
  PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 引入外部资源文件 -->
<properties resource="jdbc.properties"></properties>
<!-- 设置驼峰匹配 -->
<settings>
  <setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<!-- 设置包扫描(别名) -->
<typeAliases>
  <package name="cn.itcast.pojo"/>
</typeAliases>
<!-- 配置环境：可以配置多个环境，default：配置某一个环境的唯一标识，表示默认使用哪个环境 -->
  <environments default="development">
    <environment id="development">
      <transactionManager type="JDBC"/>
      <dataSource type="POOLED">
      	<!-- 配置连接信息 -->
        <property name="driver" value="${jdbc.driverClass}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
      </dataSource>
    </environment>
  </environments>
  <!-- 配置映射文件：用来配置sql语句和结果集类型等 -->
  <mappers>
    <mapper resource="UserMapper.xml"/>
  </mappers>
</configuration>

Configuration

1
2
3
4
5
6
DataSource dataSource = BlogDataSourceFactory.getBlogDataSource();
TransactionFactory transactionFactory = new JdbcTransactionFactory();
Environment environment = new Environment("development", transactionFactory, dataSource);
Configuration configuration = new Configuration(environment);
configuration.addMapper(BlogMapper.class);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(configuration);

sqlSession

1
2
3
4
try (SqlSession session = sqlSessionFactory.openSession()) {
  BlogMapper mapper = session.getMapper(BlogMapper.class);
  Blog blog = mapper.selectBlog(101);
}

定义SQL语句的两种方法

  • mapper.xml
1
2
3
4
5
6
7
8
9
<?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="org.mybatis.example.BlogMapper">
  <select id="selectBlog" resultType="Blog">
    select * from Blog where id = #{id}
  </select>
</mapper>
  • 注解
1
2
3
4
5
package org.mybatis.example;
public interface BlogMapper {
  @Select("SELECT * FROM blog WHERE id = #{id}")
  Blog selectBlog(int id);
}

三. 与SpringBoot的集成

1.配置application.yml文件/application.properties

  • 这一步操作的工作与mybatis.xml的作用类似，定义数据库源和需要扫描的相关mapper的路径

application.properties:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# mybatis
mybatis.mapperLocations=classpath:mapper/*/*.xml
mybatis.typeAliasesPackage=com.xxx.xxx.xxx.webapi.dao
mybatis.configuration.map-underscore-to-camel-case=true
mybatis.mapper3.identity=MYSQL
mybatis.mapper3.notEmpty=true
#表明POJO中的驼峰自动转换成数据库中的下划线
mybatis.mapper3.style=camelhump
mybatis.mapper3.mapperLocations=classpath:mapper/*.xml
#自动扫描自定义POJO的路径
mybatis.mapper3.typeAliasesPackage=com.xxx.xxx.xxx.webapi.domain
mybatis.mapper3.pageUsable=true
mybatis.mapper3.pageDialect=mysql
mybatis.mapper3.pageReasonable=true
mybatis.mapper3.pageSupportMethodsArguments=true
mybatis.mapper3.pageReturnPageInfo=check
mybatis.mapper3.pageParams=count=countSql

# ds
spring.secure.ds.default.database=bee
spring.secure.ds.default.url=jdbc:mysql://001.bi.mysql.test.xxx.info:3308/bee
spring.secure.ds.default.username=username
spring.secure.ds.default.password=pwd

application.yml

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
server:
  port: 8080

spring:
    datasource:
        name: test
        url: jdbc:mysql://127.0.0.1:3306/depot
        username: root
        password: root
        # 使用druid数据源
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        filters: stat
        maxActive: 20
        initialSize: 1
        maxWait: 60000
        minIdle: 1
        timeBetweenEvictionRunsMillis: 60000
        minEvictableIdleTimeMillis: 300000
        validationQuery: select 'x'
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        poolPreparedStatements: true
        maxOpenPreparedStatements: 20
mybatis:
  mapper-locations: classpath:mapping/*.xml
  type-aliases-package: com.winter.model

#pagehelper分页插件
pagehelper:
    helperDialect: mysql
    reasonable: true
    supportMethodsArguments: true
    params: count=countSql

2.mapper.xml(这一步可省略)
3.定义Mapper接口

1
2
3
4
5
6
7
8
9
10
11
12
package com.wacai.stanlee.bullseye.webapi.dao;

import com.wacai.stanlee.bullseye.webapi.domain.BseAppSidePO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import tk.mybatis.mapper.common.Mapper;
import tk.mybatis.mapper.common.MySqlMapper;

public interface BseAppSideMapper extends MySqlMapper<BseAppSidePO>, Mapper<BseAppSidePO> {
    @Select("select * from bee_app_side where is_deleted=1 and bee_app_main_id=#{beeAppMainId} limit 1")
    BseAppSidePO selectByAppId(@Param("beeAppMainId") Integer beeAppMainId);
}

4.在服务启动的主方法类上添加org.mybatis.spring.annotation.MapperScan配置，作用：在服务启动的时候
全局配置系统对mapper接口扫描。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
package com.winter;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("com.winter.mapper")//将项目中对应的mapper类的路径加进来就可以了
public class SpringbootMybatisDemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(SpringbootMybatisDemoApplication.class, args);
    }
}

四. 实际使用时的注意事项

1.mybatis对于 in 数组 的支持非常不好，建议用find_in_set(array,num)代替
2.mybatis @Select(“select * from …”)若其中某个字段为字符串 “1,2,3”,而我想将其转换成list,默认的映射无法做到，这时可以自定义一个typeHandler继承BaseTypeHandler,并在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
27
28
29
30
31
32
33
34
35
36
37
public class IntArrayTypeHandler extends BaseTypeHandler<int[]> {
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, int[] parameter, JdbcType jdbcType) throws SQLException {
        List<String> list = new ArrayList<>();
        for (int item : parameter) {
            list.add(String.valueOf(item));
        }
        ps.setString(i, String.join(",", list));
    }

    @Override
    public int[] getNullableResult(ResultSet rs, String columnName) throws SQLException {
        String str = rs.getString(columnName);
        if (rs.wasNull())
            return null;

        return Arrays.stream(str.split(",")).mapToInt(Integer::valueOf).toArray();
    }

    @Override
    public int[] getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        String str = rs.getString(columnIndex);
        if (rs.wasNull())
            return null;

        return Arrays.stream(str.split(",")).mapToInt(Integer::valueOf).toArray();
    }

    @Override
    public int[] getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        String str = cs.getString(columnIndex);
        if (cs.wasNull())
            return null;

        return Arrays.stream(str.split(",")).mapToInt(Integer::valueOf).toArray();
    }
}
1
2
3
<typeHandlers>
        <typeHandler handler="com.jarhf.mybatis.handler.IntArrayTypeHandler"/>
</typeHandlers>

3.@Result搭配Ibatis的@Select注解使用

  • 最近在使用mybatis遇到的一个坑,javax.persistence的@Column[^2]在原生ibatis的@Select注解上不起作用,@Column注解只有搭配mybatis预定义方法和才起作用

  • @Select必须搭配@Result注解来自定义类与表字段的映射
    [^2]:Specifies the mapped column for a persistent property or field. If no Column annotation is specified, the default values apply.

    1
    2
    3
    4
    5
    6
    7
    8
    @Select("select * from bee_deliverys where find_in_set(#{beeAppId},bee_app_id) and channel_id=#{channelId} and is_deleted=0")
        @Results({
                @Result(column = "account_name", property = "loginName"),
                @Result(property = "creator", column = "authors"),
                @Result(property = "blineId", column = "bee_bline_id"),
                @Result(property = "appId", column = "bee_app_id")
        })
        List<PutinAccount> selectByAppIdAndChannel(@Param("channelId") Integer channelId, @Param("beeAppId") Integer beeAppId);

  • 或者也可以使用xml配置文件中的resultMap解决这一问题

1
2
3
4
5
6
7
8
9
10
11
<resultMap type="com.test" id="testResultMap">
    <!-- property对应实体类的属性名称，column为数据库结果集的列的名称 -->
   <id property="id" column="id" />
   <result property="parentId" column="parentId"/>
   <result property="name" column="name"/>
   <result property="enName" column="enName"/>
</resultMap>

<select id="selectList"  resultMap="testResultMap">
        select * from test1
 </select>

五. 小结

对于框架背后原理的理解可以提升使用效率，本篇只是一个开端，主要总结了一下mybatis基础使用方法，没有涉及到的内容还有很多，例如mybatis的事物管理机制,mybatis连接数据库背后的原理分析,之后再深入学习一下，再做更新:)