MyBatis实现一对多查询两个数据库

MyBatis实现一对多查询两个数据库

我们现在有两张表，第一张时ppuser表，第二张是pporder表，我们想通过MyBatis实现在查询ppuser的id的时候，把pporder的id相同的也查出来。业务逻辑就是查询用户的时候，把pporder的用户消费商品也都查出来，ppuser的id就是用户id，正常的业务应该在pporder表添加一个userid字段进行查询，我这里给的demo就先用id了。

PpOrderMapper.xml代码：

<?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.ruoyi.project.mapper.PpOrderMapper">
    
    <resultMap type="PpOrder" id="PpOrderResult">
        <result property="id"    column="id"    />
        <result property="shangname"    column="shangname"    />
        <result property="userId"    column="userId"    />
    </resultMap>

    <sql id="selectPpOrderVo">
        select id, shangname, userId from pp_order
    </sql>

    <select id="selectPpOrderList" parameterType="PpOrder" resultMap="PpOrderResult">
        <include refid="selectPpOrderVo"/>
        <where>  
            <if test="shangname != null  and shangname != ''"> and shangname like concat('%', #{shangname}, '%')</if>
            <if test="userId != null "> and userId = #{userId}</if>
        </where>
    </select>
    
    <select id="selectPpOrderById" parameterType="Integer" resultMap="PpOrderResult">
        <include refid="selectPpOrderVo"/>
        where id = #{id}
    </select>
  
</mapper>

PpUserMapper.xml代码：

<?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.ruoyi.project.mapper.PpUserMapper">
    
    <resultMap type="PpUser" id="PpUserResult">
        <result property="id"    column="id"    />
        <result property="username"    column="username"    />
        <result property="gender"    column="gender"    />
        <collection property="OrderList" ofType="pporder"
                    select="com.ruoyi.project.mapper.PpOrderMapper.selectPpOrderById"
                    column="id"></collection>
    </resultMap>

    <sql id="selectPpUserVo">
        select id, username, gender from pp_user
    </sql>

    <select id="selectPpUserList" parameterType="PpUser" resultMap="PpUserResult">
        <include refid="selectPpUserVo"/>
        <where>  
            <if test="username != null  and username != ''"> and username like concat('%', #{username}, '%')</if>
            <if test="gender != null  and gender != ''"> and gender = #{gender}</if>
        </where>
    </select>
    
    <select id="selectPpUserById" parameterType="Integer" resultMap="PpUserResult">
        <include refid="selectPpUserVo"/>
        where id = #{id}
    </select>

</mapper>

关键代码就是：

 <collection property="OrderList" ofType="pporder"
                    select="com.ruoyi.project.mapper.PpOrderMapper.selectPpOrderById"
                    column="id"></collection>

执行selectPpOrderById将结果返回给OrderList

PpOrder.java，要定义private List OrderList;接收结果

package com.ruoyi.project.domain;

import org.apache.commons.lang3.builder.ToStringBuilder;
import org.apache.commons.lang3.builder.ToStringStyle;
import com.ruoyi.common.core.annotation.Excel;
import com.ruoyi.common.core.web.domain.BaseEntity;

import java.util.List;

/**
 * w对象 pp_user
 * 
 * @author ruoyi
 * @date 2023-06-15
 */
public class PpUser extends BaseEntity
{
    private static final long serialVersionUID = 1L;

    /** $column.columnComment */
    private Integer id;

    /** $column.columnComment */
    @Excel(name = "${comment}", readConverterExp = "$column.readConverterExp()")
    private String username;

    /** $column.columnComment */
    @Excel(name = "${comment}", readConverterExp = "$column.readConverterExp()")
    private String gender;
private List<PpOrder> OrderList;

    public List<PpOrder> getOrderList() {
        return OrderList;
    }

    public void setOrderList(List<PpOrder> orderList) {
        OrderList = orderList;
    }

    public void setId(Integer id)
    {
        this.id = id;
    }

    public Integer getId() 
    {
        return id;
    }
    public void setUsername(String username) 
    {
        this.username = username;
    }

    public String getUsername() 
    {
        return username;
    }
    public void setGender(String gender) 
    {
        this.gender = gender;
    }

    public String getGender() 
    {
        return gender;
    }

    @Override
    public String toString() {
        return new ToStringBuilder(this,ToStringStyle.MULTI_LINE_STYLE)
            .append("id", getId())
            .append("username", getUsername())
            .append("gender", getGender())
            .toString();
    }
}

在controller里面执行：

PpUser user=  ppUserService.selectPpUserById(1);
//然后user.getOrderList()就可以得到结果
System.out.println(user.getOrderList()+"getPporderList");