欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2022-04-22 23:44:14
...

        实际项目中,除了使用一些常用的增删改查的方法之外,有些复杂的需求,可能还需要执行一些自定义的动态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 提供给插入、更新、删除的时使用。