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 提供给插入、更新、删除的时使用。
下一篇: PHP常用函数对象