MyBatis大杂烩

1. 集成到SpringBoot项目中

核心依赖是org.mybatis.spring.boot:mybatis-spring-boot-starter，当然还需要jdbc和数据库驱动

build.gradle

buildscript {
    repositories {
        mavenCentral()
        jcenter()
    }

    dependencies {
        classpath 'org.springframework.boot:spring-boot-gradle-plugin:2.0.3.RELEASE'
    }
}

plugins {
    id 'io.franzbecker.gradle-lombok' version '1.14'
}

apply plugin: 'java'
apply plugin: 'application'
apply plugin: 'org.springframework.boot'
apply plugin: 'io.spring.dependency-management'
apply plugin: 'io.franzbecker.gradle-lombok'

mainClassName = 'bj.App'

dependencies {
    compile 'org.springframework.boot:spring-boot-starter'
    compile 'org.springframework.boot:spring-boot-starter-jdbc'
    compile 'org.mybatis.spring.boot:mybatis-spring-boot-starter:1.3.2'
    compile 'org.projectlombok:lombok'
    compile 'mysql:mysql-connector-java'

    testCompile 'org.springframework.boot:spring-boot-starter-test'
}

repositories {
    mavenCentral()
}

2. 配置数据库连接

application.yml

spring:
  datasource:
    username: foo
    password: foo
    url: jdbc:mysql://localhost:3306/foo

3. 使用MyBatis

需要先创建好相应的数据表

App.java

package bj;

import lombok.Data;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.context.event.ApplicationReadyEvent;
import org.springframework.context.ApplicationListener;

import javax.annotation.Resource;

/**
 * Created by BaiJiFeiLong@gmail.com at 2018/6/26 下午1:51
 */
@SpringBootApplication
public class App implements ApplicationListener<ApplicationReadyEvent> {
    @Resource
    private AnimalMapper animalMapper;

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

    @Override
    public void onApplicationEvent(ApplicationReadyEvent event) {
        System.out.println("Ready.");
        Animal animal = animalMapper.findAnimalById(1);
        System.out.println(animal);
        System.out.println(animal.getName());
    }
}

@Mapper
interface AnimalMapper {

    @Select("SELECT * FROM animal WHERE id = #{id}")
    Animal findAnimalById(int id);
}

@Data
class Animal {
    private Integer id;
    private String name;
}

不使用Spring配置文件示例

package bj;

import com.zaxxer.hikari.HikariDataSource;
import io.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.context.event.ApplicationReadyEvent;
import org.springframework.context.ApplicationListener;
import org.springframework.context.annotation.Bean;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.List;
import java.util.Map;

/**
 * Created by BaiJiFeiLong@gmail.com at 2018/12/6 下午9:29
 */
@SpringBootApplication(exclude = SpringBootConfiguration.class)
public class MyBatisApp implements ApplicationListener<ApplicationReadyEvent> {

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

    @Resource
    private UserMapper userMapper;

    @Override
    public void onApplicationEvent(ApplicationReadyEvent applicationReadyEvent) {
        System.out.println("Users:");
        userMapper.selectAll().forEach(System.out::println);
    }

    @Bean
    public DataSource dataSource() {
        return new HikariDataSource() {{
            setJdbcUrl("jdbc:mysql://localhost/foo");
            setUsername("root");
            setPassword("root");
        }};
    }

    @Mapper
    interface UserMapper {
        @Select("SELECT * FROM user")
        List<Map<String, Object>> selectAll();
    }
}

4. 使用XML格式的Mapper文件

AnimalMapper(可以放到App.java中)

@Mapper
interface AnimalMapper {
    Animal findAnimalById(int id);
}

animalMapper.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="bj.AnimalMapper">
    <select id="findAnimalById" resultType="bj.Animal">
        select *
        from animal
        where id = #{id}
    </select>
</mapper>

注意：mapper.xml文件默认放在Mapper.java文件同一目录下。如果需要指定其他目录，可以在application.yml中进行配置(注意星号)：

mybatis:
  mapper-locations: classpath*:*Mapper.xml

具体示例：查询ID范围并带分页

<select id="getIdIn" resultType="bj.Person">
    SELECT *
    FROM person
    <where>
        <if test="ids != null and !ids.isEmpty()">
            id IN
            <foreach collection="ids" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
    </where>
    <if test="pageable != null">
        LIMIT #{pageable.pageSize} OFFSET #{pageable.offset}
    </if>
</select>

List<Person> getIdIn(@Param("ids") List<Integer> ids, @Param("pageable") Pageable pageable);

5. SQL Builder 的使用

interface PersonMapper {
    @Data
    class MyProvider {
        private String allSql = new SQL() {{
            SELECT("*");
            FROM("person");
        }}.toString();
    }

    @SelectProvider(type = MyProvider.class, method = "getAllSql")
    List<Person> getAll();
}

6. 分页

6.1 自带RowBounds分页

MyBatis自带一个RowBounds分页，这是个假分页，在SQL查询出全部结果后，才从中进行分页截取，在实际业务上基本是废柴一个(分分钟卡爆数据库)。用法：Mapper中的查询函数添加一个RowBounds参数即可，例如:

List<Person> findAll(RowBounds rowBounds);

6.2 自己写个简单分页

interface PersonMapper {
    @Data
    class MyProvider {
        private String allSql = new SQL() {{
            SELECT("*");
            FROM("person");
        }}.toString() + " LIMIT #{limit} OFFSET #{offset}";
    }

    @SelectProvider(type = MyProvider.class, method = "getAllSql")
    List<Person> getAll(MyLimit limit);
}

@Data
class MyLimit {
    private int limit;
    private int offset;
}

不能用现成的org.apache.ibatis.session.RowBounds类型，不是纯POJO，MyBatis读不了(至少目前是)

使用SpringDataCommons自带的分页类进行分页查询

package bj;

import lombok.Data;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.context.event.ApplicationReadyEvent;
import org.springframework.context.ApplicationListener;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;

import javax.annotation.Resource;
import java.util.Date;
import java.util.List;

/**
 * Created by BaiJiFeiLong@gmail.com at 2018/6/26 下午1:51
 */
@SpringBootApplication
@MapperScan(basePackageClasses = App.class)
public class App implements ApplicationListener<ApplicationReadyEvent> {

    @Resource
    private PersonMapper personMapper;

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

    @Override
    public void onApplicationEvent(ApplicationReadyEvent event) {
        personMapper.findAll(PageRequest.of(0, 2)).forEach(System.out::println);
    }
}

@Data
class Person {
    private Integer id;
    private String username;
    private String firstName;
    private Date createdAt;
    private String remark;
}

interface PersonMapper {
    @Select("SELECT * FROM person LIMIT #{pageable.pageSize} OFFSET #{pageable.offset}")
    List<Person> findAll(@Param("pageable") Pageable pageable);
}

如果需要组装分页结果，可以使用：

// 组装分页结果 没有查询total，先设为-1，以免造成歧义
// Page: org.springframework.data.domain.Page
// PageImpl: org.springframework.data.domain.PageImpl
Page<Person> personPage = new PageImpl<>(people, pageable, -1);

SpringDataCommons是SpringDataJpa的依赖，可以通过添加spring-boot-starter-data-jpa添加此依赖

6.3 MyBatisPlus分页

先引入依赖 compile 'com.baomidou:mybatis-plus-boot-starter:2.2.0'

Mapper类中查询函数加入分页参数

interface PersonMapper {
    @Select("SELECT * FROM person ")
    List<Person> findAll(Pagination pagination);
}

直接调用即可

personMapper.findAll(new Pagination(2, 2)).forEach(System.out::println);

注意：MyBatisPlus的分页页数基数是1。查询完成后，对应的Pagination会更新总数、页数等(不更内容，需要手动更新)，此时查询Pagination可获得记录总数

MyBatisPlus查询的结果是List不是分页(区别于PageHelper)

6.4 PageHelper分页(TkMyBatis)

首先，注入一个MyBatis拦截器。必须设置helperDialect参数，否则会报空指针(NullPointerException)。

@Bean
public PageInterceptor pageInterceptor() {
    PageInterceptor pageInterceptor = new PageInterceptor();
    pageInterceptor.setProperties(new Properties() {{
        this.setProperty("helperDialect", "mysql");
    }});
    return pageInterceptor;
}

用法:

(1). 直接使用RowBounds(PageHelper做了处理)

interface PersonMapper {
    @Select("SELECT * FROM person ")
    List<Person> findAll(RowBounds rowBounds);
}

(2). 添加PageRowBounds参数(可以获取记录总数。RowBounds不支持)

(3). 使用PageHelper.startPage

PageHelper.startPage(2, 2);
Page<Person> people = personMapper.findAll();

注意：

startPage会把分页参数放在当前线程(通过ThreadLocal)，执行完查询后会自动清理。如果不保证MyBatis查询会被执行，可以通过PageHelper.clearPage()清除状态
PageHelper分页查询返回的结果是实现了List接口的Page类型(非分页查询也返回Page类型)，可以在Mapper类中直接将返回类型定义为Page类型，省去类型强转
PageHelper的分页页数基数也是1

interface PersonMapper {
    @Select("SELECT * FROM person ")
    Page<Person> findAll();
}

小技巧与提示

使用@MapperScan注解，开启自动扫描，每个Mapper类都可以省去@Mapper注解，例@MapperScan(basePackageClasses = App.class)
mybatis.type-aliases-package可以省去XML文件中实体类的包前缀
mybatis.configuration.map-underscore-to-camel-case: 可以将数据库的下划线命名法映射为java的驼峰命名法
auto-mapping-unknown-column-behavior设为'failing'，可以保证数据库查询到的每一列都进行了映射