spring boot 整合 mybatis Dynamic SQL

目录

前言

环境搭建

语法实战

---

前言

作为java开发者，一般我们在使用orm框架的时候绝大部分会使用mybatis作为我们的第一方案。mybatis是一款半自动化的orm框架，它的灵活性、易上手是它能够快速占据java开发者市场的一个重要原因。在早期使用mybatis的过程中，遇到比较困扰的问题就是，每个功能需求都需要去编写sql语句，虽然这个对于sql的优化提供了便利，但是即使是简单的增删改查都需要单独编写的话，就会让程序员觉得有些繁琐。后来有许多的开发者提供了一些mybatis的插件,可以通过逆向工程生成许多的工具代码，为开发提供了便利。那么今天这里想给大家介绍一款mybatis 官网的Dynamic SQL。这个款基本去除了原来的xml文件，使用mapper做了替代，在代码量上骤减（虽然之前代码也是），同时也吸取了一部分jpa的思想，具体的我们在实战代码中领略吧。

环境搭建

首先创建一个spring boot的工程，然后在我们的pom.xml文件中加入依赖。需要的依赖如下

        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.3</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis.dynamic-sql</groupId>
            <artifactId>mybatis-dynamic-sql</artifactId>
            <version>1.1.4</version>
        </dependency>
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.3.0</version>
        </dependency>

这三个依赖前面两个是引入mybatis、mybatis dynamic sql的，最后一个相信大家都很熟悉了是提供分页功能的。

这里我们同样需要使用插件以逆向工程的方式创建实体的映射。在pom中进行配置

                <plugin>
                <groupId>org.mybatis.generator</groupId>
                <artifactId>mybatis-generator-maven-plugin</artifactId>
                <version>1.4.0</version>
                <dependencies>
                    <dependency>
                        <groupId>mysql</groupId>
                        <artifactId>mysql-connector-java</artifactId>
                        <version>8.0.20</version>
                    </dependency>
                </dependencies>
                <configuration>
                    <configurationFile>
src/main/resources/mybatis-config/generatorConfig.xml</configurationFile>
                    <verbose>true</verbose>
                    <overwrite>true</overwrite>
                </configuration>
            </plugin>

这里指定配置文件的位置，里面配置实体的映射。

这里是mybatis genrator的官网的地址。在开始中可以看到这张表格，我们选择的是第一个，点击sample。

<!DOCTYPE generatorConfiguration PUBLIC
 "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
 "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
  <context id="dsql" targetRuntime="MyBatis3DynamicSql">
    <jdbcConnection driverClass="org.hsqldb.jdbcDriver"
        connectionURL="jdbc:hsqldb:mem:aname" />

    <javaModelGenerator targetPackage="example.model" targetProject="src/main/java"/>

    <javaClientGenerator targetPackage="example.mapper" targetProject="src/main/java"/>

    <table tableName="FooTable" />
  </context>
</generatorConfiguration>

上面这个是最简单的配置表，首先指定了targetRuntime,然后指定了我们的数据库驱动（我们选择的是mysql),后面两个是指定了我们实体类和mapper接口的位置。

首先我们创建一个名为generatorConfig.xml。然后按照下面进行创建

<!DOCTYPE generatorConfiguration PUBLIC
        "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
    <context id="selfDb" targetRuntime="MyBatis3DynamicSql">
        <jdbcConnection driverClass="com.mysql.cj.jdbc.Driver"
                        connectionURL="jdbc:mysql://127.0.0.1:3306/ebook?useUnicode=true&amp;characterEncoding=utf8&amp;serverTimezone=GMT%2B8"
                        userId="root"
                        password="123456">
            <property name="nullCatalogMeansCurrent" value="true" />
        </jdbcConnection>
        <javaModelGenerator targetPackage="com.zjl.spring_boot_mybatis.model" targetProject="src/main/java"/>
        <javaClientGenerator targetPackage="com.zjl.spring_boot_mybatis.mapper" targetProject="src/main/java"/>
        <table tableName="tb_book" domainObjectName="BookPO">
            <generatedKey column="book_id" sqlStatement="MySql" identity="true"/>
            <columnOverride column="publication_date" jdbcType="DATE" javaType="java.time.LocalDate"/>
            <columnOverride column="create_time" jdbcType="TIMESTAMP" javaType="java.time.LocalDateTime"/>
            <columnOverride column="update_time" jdbcType="TIMESTAMP" javaType="java.time.LocalDateTime"/>
        </table>
        
        <table tableName="tb_customer" domainObjectName="CustomerPO">
            <generatedKey column="customer_id" sqlStatement="MySql" identity="true"/>
            <columnOverride column="create_time" jdbcType="TIMESTAMP" javaType="java.time.LocalDateTime"/>
            <columnOverride column="update_time" jdbcType="TIMESTAMP" javaType="java.time.LocalDateTime"/>
        </table>

        <table tableName="tb_order" domainObjectName="OrderPO">
            <generatedKey column="order_id" sqlStatement="MySql" identity="true"/>
            <columnOverride column="create_time" jdbcType="TIMESTAMP" javaType="java.time.LocalDateTime"/>
            <columnOverride column="update_time" jdbcType="TIMESTAMP" javaType="java.time.LocalDateTime"/>
        </table>

        <table tableName="tb_order_detail" domainObjectName="OrderDetailPO">
            <generatedKey column="order_detail_id" sqlStatement="MySql" identity="true"/>
            <columnOverride column="create_time" jdbcType="TIMESTAMP" javaType="java.time.LocalDateTime"/>
            <columnOverride column="update_time" jdbcType="TIMESTAMP" javaType="java.time.LocalDateTime"/>
        </table>
    </context>
</generatorConfiguration>

nullCatalogMeansCurrent是防止生成器把其他数据库的同名表生成下来。

generatedKey指定的是主键以及生成策略。

columnOverride用于自定义字段的映射规则。

然后我们可以在idea的maven视图中找到mybatis genrator插件点击运行，成功之后就可以看到生成了我们所需要的实体类了。

语法实战

逆向工程生成之后，我们的项目中就多了model和mapper两个模块。model模块比较简单就是数据表所映射的实体。而mapper中主要有两个类型的文件，一是接口文件mapper,这种有点类似于之前mybatis的sql注解写法，就是不在xml中编写sql,而是在mapper接口文件中在方法上添加增删改查的注解，然后把sql写在注解上。虽然这种写法是不用生成xml了但是在视觉上代码会显得比较凌乱。在mybatis dynamic sql中的mapper文件，主要提供了我们通用的单表操作，包括主键查询，计数查询，

我们来看下简单的单表操作语法，例如插入操作

 @Generated(value="org.mybatis.generator.api.MyBatisGenerator", date="2021-02-12T11:49:30.818+08:00", comments="Source Table: tb_book")
    default int insert(BookPO record) {
        return MyBatis3Utils.insert(this::insert, record, bookPO, c ->
            c.map(bookName).toProperty("bookName")
            .map(publicationDate).toProperty("publicationDate")
            .map(price).toProperty("price")
            .map(createTime).toProperty("createTime")
            .map(updateTime).toProperty("updateTime")
        );
    }

    @Generated(value="org.mybatis.generator.api.MyBatisGenerator", date="2021-02-12T11:49:30.818+08:00", comments="Source Table: tb_book")
    default int insertSelective(BookPO record) {
        return MyBatis3Utils.insert(this::insert, record, bookPO, c ->
            c.map(bookName).toPropertyWhenPresent("bookName", record::getBookName)
            .map(publicationDate).toPropertyWhenPresent("publicationDate", record::getPublicationDate)
            .map(price).toPropertyWhenPresent("price", record::getPrice)
            .map(createTime).toPropertyWhenPresent("createTime", record::getCreateTime)
            .map(updateTime).toPropertyWhenPresent("updateTime", record::getUpdateTime)
        );
    }

一般会提供两个方法，一个是全字段的插入，另一个是动态的，也就是判断这个字段是否为空，非空的才会。

删除方法如下

  @Generated(value="org.mybatis.generator.api.MyBatisGenerator", date="2021-02-12T11:49:30.818+08:00", comments="Source Table: tb_book")
    default int delete(DeleteDSLCompleter completer) {
        return MyBatis3Utils.deleteFrom(this::delete, bookPO, completer);
    }

    @Generated(value="org.mybatis.generator.api.MyBatisGenerator", date="2021-02-12T11:49:30.818+08:00", comments="Source Table: tb_book")
    default int deleteByPrimaryKey(Integer bookId_) {
        return delete(c -> 
            c.where(bookId, isEqualTo(bookId_))
        );
    }

默认提供一个是根据主键删除，另一个是传入条件参数根据条件删除，

default int update(UpdateDSLCompleter completer);

static UpdateDSL<UpdateModel> updateAllColumns(T t,UpdateDSL<UpdateModel> dsl);

static UpdateDSL<UpdateModel> updateSelectiveColumns(T t,UpdateDSL<UpdateModel> dsl);

default int updateByPrimaryKey(T t);

default int updateByPrimaryKeySelective(T t);

默认提供了五种修改的方法。

1、通过传入一个包含条件的函数表达式来更新

2、更新符合条件记录的所有的字段

3、更新符合条件记录的所有不为空的字段

4、通过主键更新所有字段

5、通过主键更新所有不为空的字段

最后是查询

default Optional<T> selectOne(SelectDSLCompleter completer);

default List<T> selectMany(SelectDSLCompleter completer);

default long count(CountDSLCompleter completer); 

default List<BookPO> selectDistinct(SelectDSLCompleter completer);

default Optional<BookPO> selectByPrimaryKey(Integer bookId_);

查询主要是给了五个方法

1. 只返回一个符合条件的记录，多条结果会报错，也可能会返回null值
2. 返回多个符合条件的记录
3. 计数查询返回符合条件的记录总数
4. 去重查询，返回符合条件的记录
5. 通过主键查询记录

以上是mybatis dynamic sql提供的主要单表操作方法。

在上面比较常出现的一个是SelectDSLCompleter ，这是一个函数式接口，里面可以接受一个QueryExpressionDSL查询表达式的参数。里面提供了几个通用的查询方法比如sql中的and、or、!=，以及group by,join等。

例如逻辑组合

//and
public List<BookPO> queryList(BookPO bookPO) {
        return bookPOMapper.select(selectModelQueryExpressionDSL ->
                selectModelQueryExpressionDSL.where(BookPODynamicSqlSupport.bookName, isEqualTo(bookPO.getBookName()))
                        .and(BookPODynamicSqlSupport.price, isEqualTo(bookPO.getPrice())));
}

//or
public List<BookPO> queryList(BookPO bookPO) {
        return bookPOMapper.select(selectModelQueryExpressionDSL ->
                selectModelQueryExpressionDSL.where(BookPODynamicSqlSupport.bookName, isEqualTo(bookPO.getBookName()))
                        .or(BookPODynamicSqlSupport.price, isEqualTo(bookPO.getPrice())));
}

//!=
public List<BookPO> queryList(BookPO bookPO) {
        return bookPOMapper.select(selectModelQueryExpressionDSL ->
                selectModelQueryExpressionDSL.where(BookPODynamicSqlSupport.bookName, isEqualTo(bookPO.getBookName()))
                        .and(BookPODynamicSqlSupport.price, isNotEqualTo(bookPO.getPrice())));
}

例如范围查询

public List<BookPO> queryList(BookPO bookPO) {
        return bookPOMapper.select(selectModelQueryExpressionDSL ->
                selectModelQueryExpressionDSL.where(BookPODynamicSqlSupport.publicationDate, isGreaterThanOrEqualTo(bookPO.getPublicationDate()))
        );
    }

例如模糊查询

public List<BookPO> queryList(BookPO bookPO) {
        return bookPOMapper.select(selectModelQueryExpressionDSL ->
                selectModelQueryExpressionDSL.where(BookPODynamicSqlSupport.bookName, isLike(bookPO.getBookName()))
        );
    }

例如排序，可以指定正序倒序，支持多个条件

public List<BookPO> orderByTime() {
        return bookPOMapper.select(selectModelQueryExpressionDSL ->
                selectModelQueryExpressionDSL.orderBy(BookPODynamicSqlSupport.publicationDate.descending(),
                        BookPODynamicSqlSupport.createTime.descending()));
    }

例如分组查询

public List<BookPO> groupByPublication(String name) {
        return bookPOMapper.select(selectModelQueryExpressionDSL ->
                selectModelQueryExpressionDSL.where(BookPODynamicSqlSupport.bookName, isEqualTo(name))
                        .groupBy(BookPODynamicSqlSupport.publicationDate));
    }

如果是分页查询的话可以使用pageHelper

public PageVO<BookPO> getPage(Integer pageNum, Integer pageSize) {
        Page<BookPO> page = PageHelper.startPage(pageNum, pageSize).doSelectPage(() ->
                bookPOMapper.select(SelectDSLCompleter.allRows()));
        List<BookPO> result = page.getResult();
        if (CollectionUtils.isNotEmpty(result)) {
            return PageVO.pageOf(result, pageNum, pageSize, page.getTotal());
        } else {
            return PageVO.pageOf(Collections.emptyList(), pageNum, pageSize, 0);
        }
    }

一般来说jpa写法的优势在单表，多表jion的时候语法比较晦涩难懂。举例订单表和订单详情表存在一个一对多的关系，某个业务场景下需要我们进行关联查询

那么在mybatis dynamic sql中可以这样操作

List<OrderDetailPO> select = orderDetailPOMapper.select(selectModelQueryExpressionDSL ->
                selectModelQueryExpressionDSL.join(SqlTable.of("tb_order"))
                        .on(OrderDetailPODynamicSqlSupport.orderId, new EqualTo(OrderPODynamicSqlSupport.orderId))
                        .where(OrderPODynamicSqlSupport.customerId, isEqualTo(1)));

首先通过join方法指定可要关联的表名，支持left join、right join。后面的on方法指定关联的字段，简单的多表查询还是非常方便的

当然如果是那些非常复杂的sql，建议还是用原生的sql语句去编写这样，一来比较清晰，程序员不至于苦恼于语法而不知道怎么操作，二复杂的sql往往会伴随着性能问题需要调优，如果是sql 语句的话对于调优方面也会比较方便。

总结一下，mybatis dynamic sql结合了一部分jpa的思想，把sql和java代码做了更加紧密的整合，提高了代码的简洁性和完整度，在单表操作上给程序员带来了很大的便利。而复杂sql,是使用java语法编写还是直接使用原生sql，这个还需要具体问题具体分析。