mybatis系列-tkmybatis-05-使用xml方式扩展接口，进行多表关联查询

    tkMybatis是对Mybatis功能的扩展，在Mybatis中，可以通过xml方式扩展接口（先在*mapper接口类中定义新的接口方法，然后在*mapper.xml文件写实现SQL）。在tkMybatis中，并没有改变Mybatis的作用机制，在常规Mybatis扫描xml文件和mapper文件创建sqlSessionFactory和各种Mapper代理类之后，才进行的扩展，所以以前Mybatis的那一套机制依旧有效。

    这里举例说明下，如何基于xml扩展，实现多表关联查询。现在有个Users（用户表）和Orders表（订单表），Orders表中user_id表示下订单的用户id，与Users表的id字段相对应。

1、增加新的Pojo类

    该类继承自Users类，然后补充了几个Orders表中的字段

package com.example.demotkmybatisgeneralsecond.pojo;


import javax.persistence.Column;

import javax.persistence.Id;


/**

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

* Description: 自定义类，用于Users表关联查询Orders表

* Version: 1.0

**/

public class UserOrders extends Users {

    /**

     * 订单主键;同时也是订单编号

     */

    private String orderId;


    /**

     * 收货人快照

     */

    private String receiverName;



    /**

     * 订单总价格

     */

    private Integer totalAmount;



    /**

     * 实际支付总价格

     */

    private Integer realPayAmount;



    public String getOrderId() {

        return orderId;

    }



    public void setOrderId(String orderId) {

        this.orderId = orderId;

    }



    public String getReceiverName() {

        return receiverName;

    }



    public void setReceiverName(String receiverName) {

        this.receiverName = receiverName;

    }



    public Integer getTotalAmount() {

        return totalAmount;

    }



    public void setTotalAmount(Integer totalAmount) {

        this.totalAmount = totalAmount;

    }



    public Integer getRealPayAmount() {

        return realPayAmount;

    }



    public void setRealPayAmount(Integer realPayAmount) {

        this.realPayAmount = realPayAmount;

    }

}

2、为接口Mapper类添加新的接口方法

这里加了两个，selectAllUsersOrders 和 selectUsersOrders(String userId)

package com.example.demotkmybatisgeneralsecond.mapper;


import com.example.demotkmybatisgeneralsecond.pojo.UserOrders;

import com.example.demotkmybatisgeneralsecond.pojo.Users;

import tk.mybatis.mapper.common.Mapper;

import java.util.List;


public interface UsersMapper extends Mapper<Users> {

    List<UserOrders> selectAllUsersOrders();

    List<UserOrders> selectUsersOrders(String userId);

}

 

3、在对应的*Mapper.xml文件中，添加接口方法对应的Sql实现

        a、新建了id为UserOrdersResultMap的ResultMap，继承自BaseResultMap，对应UserOrders类，增加了几个Orders的字段

        b、第2步中的每一个新增接口方法，在这里都增加了一个同名id的SQL

<?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="com.example.demotkmybatisgeneralsecond.mapper.UsersMapper">

    <resultMap id="BaseResultMap" type="com.example.demotkmybatisgeneralsecond.pojo.Users">

        <!--

          WARNING - @mbg.generated

        -->

        <id column="id" jdbcType="VARCHAR" property="id"/>

        <result column="username" jdbcType="VARCHAR" property="username"/>

        <result column="password" jdbcType="VARCHAR" property="password"/>

        <result column="nickname" jdbcType="VARCHAR" property="nickname"/>

        <result column="realname" jdbcType="VARCHAR" property="realname"/>

        <result column="face" jdbcType="VARCHAR" property="face"/>

        <result column="mobile" jdbcType="VARCHAR" property="mobile"/>

        <result column="email" jdbcType="VARCHAR" property="email"/>

        <result column="sex" jdbcType="INTEGER" property="sex"/>

        <result column="birthday" jdbcType="DATE" property="birthday"/>

        <result column="created_time" jdbcType="TIMESTAMP" property="createdTime"/>

        <result column="updated_time" jdbcType="TIMESTAMP" property="updatedTime"/>

    </resultMap>


    <!--

          自定义ResultMap

        -->

    <resultMap id="UserOrdersResultMap" extends="BaseResultMap" type="com.example.demotkmybatisgeneralsecond.pojo.UserOrders">

        <result column="order_id" jdbcType="VARCHAR" property="orderId"/>

        <result column="receiver_name" jdbcType="VARCHAR" property="receiverName"/>

        <result column="total_amount" jdbcType="INTEGER" property="totalAmount"/>

        <result column="real_pay_amount" jdbcType="INTEGER" property="realPayAmount"/>

    </resultMap>

    <!--

          自定义SQL

        -->

    <select id="selectAllUsersOrders" resultMap="UserOrdersResultMap">

    SELECT a.*, b.id as order_id, b.receiver_name , b.total_amount, b.real_pay_amount

    FROM users a LEFT JOIN orders b on a.id = b.user_id

    WHERE b.id is not NULL

  </select>


    <select id="selectUsersOrders" parameterType="java.lang.String" resultMap="UserOrdersResultMap">

    SELECT a.*, b.id as order_id, b.receiver_name , b.total_amount, b.real_pay_amount

    FROM users a LEFT JOIN orders b on a.id = b.user_id

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

  </select>

</mapper>

 

4、扩展结束，按需修改对应的Controller层和Service层代码，编译运行