MyBatis

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

ORM框架对比

以下针对Spring JDBC、Spring Data Jpa、Mybatis三款框架做了个粗略的对比。一般应用的性能瓶颈并不是在于ORM，所以这三个框架技术选型应该考虑项目的场景、团队的技能掌握情况、开发周期(开发效率)…

ORM框架	Spring JDBC	Spring Data Jpa	Mybatis
性能	性能最好	性能较差	居中
代码量	多	少	多
学习成本	低	高	居中
推荐指数	❤❤❤	❤❤❤❤❤	❤❤❤❤❤

添加依赖

在 pom.xml 中添加 Mybatis提供的用于整合Spring Boot的 starter 依赖包 mybatis-spring-boot-starter

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>1.3.2</version>
</dependency>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-devtools</artifactId>
    <optional>true</optional>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
</dependency>

数据库及MyBatis相关配置

application.yml

spring:
  application:
    name: spring-boot-mybatis
  datasource:
    url: jdbc:mysql://localhost:3306/test_db?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&useSSL=false
    username: root
    password: mysql123
    driver-class-name: com.mysql.jdbc.Driver
mybatis:
  # config-location与configuration不可同时指定
  # 否则会报java.lang.IllegalStateException: Property 'configuration' and 'configLocation' can not specified with together
  # config-location: classpath:mybatis/mybatis-config.xml # mybatis配置文件位置
  mapper-locations: classpath:mybatis/mappers/*.xml # mapper映射文件位置
  type-aliases-package: com.example.springbootmybatis.entity # 别名包
  configuration:
    map-underscore-to-camel-case: true # 驼峰命名eg.表字段user_sex -- 实体属性userSex

# mybatis sql日志
logging:
  level:
    com:
      example:
        springbootmybatis:
          mapper: debug

MyBatis配置文件mybatis-config.xml

<?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>
    <!--<typeAliases>
        <package name="com.example.springbootmybatis.entity"/>
    </typeAliases>-->
</configuration>

注意：如果将mybatis.mapper-locations配置为classpath:com/example/springbootmybatis/mapper/*.xml即mapper接口所在的包路径下，而Spring Boot默认只打入java package -> *.java，所以我们需要给pom.xml文件添加如下内容

<build>
    <resources>
        <resource>
            <directory>src/main/resources</directory>
        </resource>
        <resource>
            <directory>src/main/java</directory>
            <includes>
                <include>**/*.xml</include>
            </includes>
            <filtering>true</filtering>
        </resource>
    </resources>
    <plugins>
        <plugin>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-maven-plugin</artifactId>
        </plugin>
    </plugins>
</build>

MyBatis sql日志
对于MyBatis sql日志可以像上面那样在application.yml文件中配置，但在项目中往往使用logback日志框架，因此可以在logback-spring.xml配置文件中添加如下logger

<logger name="com.example.springbootmybatis.mapper" level="DEBUG" additivity="false"></logger>

编码实战

表结构

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for tb_user
-- ----------------------------
DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `username` varchar(64) NOT NULL COMMENT '用户名',
  `password` varchar(64) NOT NULL COMMENT '用户密码',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

SET FOREIGN_KEY_CHECKS = 1;

实体类

com.example.springbootmybatis.entity.User

package com.example.springbootmybatis.entity;

import java.util.Date;

public class User {

    /** 用户id */
    private Integer id;

    /** 用户名 */
    private String username;

    /** 用户密码 */
    private String password;

    /** 创建时间 */
    private Date createTime;

    /** 更新时间 */
    private Date updateTime;

    public User() {
        super();
    }

    public User(String username, String password) {
        this.username = username;
        this.password = password;
    }

   // setters and getters ...
}

配置mapper扫描

在启动类中添加@MapperScan注解开启对mapper包的扫描

package com.example.springbootmybatis;

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

@SpringBootApplication
@MapperScan("com.example.springbootmybatis.mapper")
public class SpringBootMybatisApplication {

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

不嫌麻烦的话，可以直接在每个Mapper接口上面添加注解@Mapper

持久层

MyBatis 3提供了基于注解的开发方式，但相比于传统xml映射方式并没有那么灵活，具体可参考官方文档Java API以及SQL语句构建器类两部分

注解开发

com.example.springbootmybatis.mapper.IUserMapper

此示例展示了单表增、删、改、查常见操作

package com.example.springbootmybatis.mapper;

import com.example.springbootmybatis.entity.User;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.jdbc.SQL;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface IUserMapper {

    @Insert("INSERT INTO tb_user(username, password, create_time, update_time) VALUES(#{username}, #{password}, now(), now())")
    @SelectKey(statement = "SELECT LAST_INSERT_ID()", keyProperty = "id", before = false, resultType = Integer.class)
    Integer insert(User user);

    @Delete("DELETE FROM tb_user WHERE id = #{id}")
    Integer delete(Integer id);

    @Update("UPDATE tb_user SET username = #{username}, password = #{password}, update_time = now() WHERE id = #{id}")
    Integer update(User user);

    @Select("SELECT * FROM tb_user")
    @Results(id = "userResult", value = {
            @Result(property = "id", column = "id"),
            @Result(property = "username", column = "username"),
            @Result(property = "password", column = "password"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "updateTime", column = "update_time"),
    })
    List<User> selectAll();

    @Select("SELECT * FROM tb_user LIMIT #{offset}, #{rows}")
    List<User> selectList(@Param("offset") Integer offset, @Param("rows") Integer rows);

    @Select("SELECT * FROM tb_user WHERE id = #{id}")
    @ResultMap("userResult")
    User selectById(Integer id);

    @Select("SELECT * FROM tb_user WHERE username = #{username}")
    @ResultMap("userResult")
    User selectByUsername(String username);

    @SelectProvider(type = UserSqlBuilder.class, method = "selectLike")
    List<User> selectLike(String username);

    class UserSqlBuilder {

        public static String selectLike(String username) {
            return new SQL(){{
                SELECT("id", "username", "password", "create_time", "update_time");
                FROM("tb_user");
                if (username != null) {
                    WHERE("username LIKE CONCAT(CONCAT('%', #{username}), '%')");
                }
                ORDER_BY("id");
            }}.toString();
        }

    }

}

注解解释
@Insert、@Delete、@Update、@Select这四个注解分别代表将会被执行的 SQL 语句。它们用字符串数组（或单个字符串）作为参数。如果传递的是字符串数组，字符串之间先会被填充一个空格再连接成单个完整的字符串。这有效避免了以 Java 代码构建 SQL 语句时的“丢失空格”的问题。然而，你也可以提前手动连接好字符串。属性有：value，填入的值是用来组成单个 SQL 语句的字符串数组。
@Results对应<resultMap>，结果映射的列表，包含了一个特别结果列如何被映射到属性或字段的详情。属性有：value, id。value 属性是 @Result 注解的数组。这个 id的属性是结果映射的名称。
@Result在列和属性或字段之间的单独结果映射。
@ResultMap用于引用@Results或<resultMap>的id，无需重复定义
@InsertProvider、@UpdateProvider、@DeleteProvider、@SelectProvider结合SQL语句构建器类使用用于创建动态SQL

xml映射开发

src/main/resources/mybatis/UserMapper.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.example.springbootmybatis.mapper.IUserMapper">

    <resultMap id="BaseResultMap" type="com.example.springbootmybatis.entity.User" >
        <id column="id" property="id" jdbcType="INTEGER" javaType="java.lang.Integer" />
        <result column="username" property="username" jdbcType="VARCHAR" javaType="java.lang.String"/>
        <result column="password" property="password" jdbcType="VARCHAR" javaType="java.lang.String"/>
        <result column="create_time" property="createTime" jdbcType="DATE" javaType="java.util.Date"/>
        <result column="update_time" property="updateTime" jdbcType="DATE" javaType="java.util.Date"/>
    </resultMap>

    <sql id="Base_Column_List" >
      id, username, password, create_time, update_time
    </sql>

    <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
        select
        <include refid="Base_Column_List" />
        from tb_user
        where id = #{id,jdbcType=INTEGER}
    </select>

</mapper>

com.example.springbootmybatis.mapper.IUserMapper

package com.example.springbootmybatis.mapper;

import com.example.springbootmybatis.entity.User;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.jdbc.SQL;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface IUserMapper {

    User selectByPrimaryKey(Integer id);
    
    // 省略以上注解开发代码

}

测试

com.example.springbootmybatis.mapper.IUserMapperTest.java

package com.example.springbootmybatis.mapper;

import com.example.springbootmybatis.entity.User;
import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.List;

import static org.junit.Assert.*;

@SpringBootTest
@RunWith(SpringRunner.class)
public class IUserMapperTest {

    @Autowired
    private IUserMapper userMapper;

    private static final Logger logger = LoggerFactory.getLogger(IUserMapperTest.class);

    @Test
    public void insert() {
        User user1 = new User("aaa", "123456");
        userMapper.insert(user1);
        logger.info("插入记录id: {}", user1.getId());
        User user2 = new User("bbb", "123456");
        userMapper.insert(user2);
        logger.info("插入记录id: {}", user2.getId());
        User user3 = new User("ccc", "123456");
        userMapper.insert(user3);
        logger.info("插入记录id: {}", user3.getId());
        User user4 = new User("abc123", "123456");
        userMapper.insert(user4);
        logger.info("插入记录id: {}", user4.getId());
        Assert.assertEquals(4, userMapper.selectAll().size());
    }

    @Test
    public void delete() {
        Integer count = userMapper.delete(10);
        Assert.assertEquals(Integer.valueOf(1), count);
    }

    @Test
    public void update() {
        User updateUser = new User();
        updateUser.setId(15);
        updateUser.setUsername("jerry");
        updateUser.setPassword("jerry123456");
        Integer count = userMapper.update(updateUser);
        Assert.assertEquals(Integer.valueOf(1), count);
    }

    @Test
    public void selectAll() {
        List<User> userList = userMapper.selectAll();
        Assert.assertEquals(4, userList.size());
    }

    @Test
    public void selectList() {
        Integer pageNum = 2;
        Integer pageSize = 2;
        List<User> userList = userMapper.selectList((pageNum - 1) * pageSize, pageSize);
        Assert.assertEquals(2, userList.size());
    }

    @Test
    public void selectById() {
        User user = userMapper.selectById(15);
        Assert.assertEquals("jerry", user.getUsername());
    }

    @Test
    public void selectByUsername() {
        User user = userMapper.selectByUsername("jerry");
        Assert.assertEquals("jerry123456", user.getPassword());
    }

    @Test
    public void selectLike() {
        List<User> userList = userMapper.selectLike("a");
        Assert.assertEquals(2, userList.size());
    }

    @Test
    public void selectByPrimaryKey() {
        User user = userMapper.selectById(15);
        Assert.assertEquals("jerry", user.getUsername());
    }
}