mybatis系列-tkmybatis-07-使用动态SQL方式扩展接口，进行多表关联查询

        实际项目中，除了使用一些常用的增删改查的方法之外，有些复杂的需求，可能还需要执行一些自定义的动态sql。mybatis 除了提供了@Insert、@Delete 这些常用的注解，还提供了多个注解如：@InsertProvider,@UpdateProvider,@DeleteProvider和@SelectProvider，用来建立动态sql 和让 mybatis 执行这些sql 的注解。这些注解在Mybatis Spring和tkMybatis中都能使用。像我们在tkMybatis源码分析章节中介绍的，tkMybatis就是采用这种方式来实现接口方法，它提供的默认接口，每一个接口方法的SQL都是由一个对应的Provider类来实现的。

下面就来实现多表关联查询的功能。

1、首先创建 MydefineProvider类，并定义两个方法selectAllUsersOrdersByProvider和selectOneUsersOrdersByProvider分别用来 查询所有用户订单及某一个用户订单

package com.example.demotkmybatisgeneralsecond.providers;

/**

* CreateDate: 2021-4-30 <br/>

* Description:

* Version: 1.0

**/

public class MydefineProvider {

    /*返回的SQL和XML配置里面返回的SQL语法是一致的*/

    public String selectAllUsersOrdersByProvider(){

        String SQL = "SELECT a.*, b.id as order_id, b.receiver_name, b.total_amount, b.real_pay_amount\n" +

                "    FROM users a LEFT JOIN orders b on a.id = b.user_id\n" +

                "    WHERE b.id is not NULL";

        return SQL;

    }

    public String selectOneUsersOrdersByProvider(String userId){

        String SQL = "SELECT a.*, b.id as order_id, b.receiver_name, b.total_amount, b.real_pay_amount\n" +

                "    FROM users a LEFT JOIN orders b on a.id = b.user_id\n" +

                "    WHERE b.id is not NULL AND a.id = #{userId,jdbcType=VARCHAR}";

        return SQL;

    }

}

 

2、Mapper 中新增接口selectAllUsersOrdersByProvider方法和selectAllUsersOrdersByAnnotation

        通过SelectProvider注解来指定用哪一个类的什么方法创建SQL，Results注解与上一章通过注解方法扩展接口是一致的，用来处理返回结果到POJO类的映射。

@SelectProvider(type = MydefineProvider.class, method = "selectAllUsersOrdersByProvider")

@Results({

        @Result(column="id", jdbcType= JdbcType.VARCHAR, property="id",id=true),

        @Result(column="username",jdbcType=JdbcType.VARCHAR,property="username"),

        @Result(column="password",jdbcType=JdbcType.VARCHAR,property="password"),

        @Result(column="nickname",jdbcType=JdbcType.VARCHAR,property="nickname"),

        @Result(column="realname",jdbcType=JdbcType.VARCHAR,property="realname"),

        @Result(column="face",jdbcType=JdbcType.VARCHAR,property="face"),

        @Result(column="mobile",jdbcType=JdbcType.VARCHAR,property="mobile"),

        @Result(column="email",jdbcType=JdbcType.VARCHAR,property="email"),

        @Result(column="sex",jdbcType=JdbcType.INTEGER,property="sex"),

        @Result(column="birthday",jdbcType=JdbcType.DATE,property="birthday"),

        @Result(column="created_time",jdbcType=JdbcType.TIMESTAMP,property="createdTime"),

        @Result(column="updated_time",jdbcType=JdbcType.TIMESTAMP,property="updatedTime"),

        @Result(column="order_id",jdbcType=JdbcType.VARCHAR,property="orderId"),

        @Result(column="receiver_name",jdbcType=JdbcType.VARCHAR,property="receiverName"),

        @Result(column="total_amount",jdbcType=JdbcType.INTEGER,property="totalAmount"),

        @Result(column="real_pay_amount",jdbcType=JdbcType.INTEGER,property="realPayAmount")

})

List<UserOrders> selectAllUsersOrdersByProvider();


@SelectProvider(type = MydefineProvider.class, method = "selectOneUsersOrdersByProvider")

@Results({

        @Result(column="id", jdbcType= JdbcType.VARCHAR, property="id",id=true),

        @Result(column="username",jdbcType=JdbcType.VARCHAR,property="username"),

        @Result(column="password",jdbcType=JdbcType.VARCHAR,property="password"),

        @Result(column="nickname",jdbcType=JdbcType.VARCHAR,property="nickname"),

        @Result(column="realname",jdbcType=JdbcType.VARCHAR,property="realname"),

        @Result(column="face",jdbcType=JdbcType.VARCHAR,property="face"),

        @Result(column="mobile",jdbcType=JdbcType.VARCHAR,property="mobile"),

        @Result(column="email",jdbcType=JdbcType.VARCHAR,property="email"),

        @Result(column="sex",jdbcType=JdbcType.INTEGER,property="sex"),

        @Result(column="birthday",jdbcType=JdbcType.DATE,property="birthday"),

        @Result(column="created_time",jdbcType=JdbcType.TIMESTAMP,property="createdTime"),

        @Result(column="updated_time",jdbcType=JdbcType.TIMESTAMP,property="updatedTime"),

        @Result(column="order_id",jdbcType=JdbcType.VARCHAR,property="orderId"),

        @Result(column="receiver_name",jdbcType=JdbcType.VARCHAR,property="receiverName"),

        @Result(column="total_amount",jdbcType=JdbcType.INTEGER,property="totalAmount"),

        @Result(column="real_pay_amount",jdbcType=JdbcType.INTEGER,property="realPayAmount")

})

List<UserOrders> selectOneUsersOrdersByProvider(String userId);

说明：

　　type：动态生成 SQL 的类。

　　method：类中具体的方法名。

      以上，就是使用sqlprovider 动态创建sql，除了示例中的 @SelectProvider ，还有 @InsertProvider、UpdateProvider、 @SelectProvider 、@DeleteProvider 提供给插入、更新、删除的时使用。