mybatis嵌套结果使用

137 阅读 0 评论 91 点赞

我是靠谱客的博主闪闪小馒头，这篇文章主要介绍mybatis嵌套结果使用，现在分享给大家，希望可以做个参考。

好记性不如烂笔头，好久不写mapper文件，都不会写了，还要现查，所以还是记下来的好。

嵌套结果：2表关联，在sql中，可能是2条或多条记录，然后mybatis自己组装，返回结果集可能只有1条。
嵌套结果优点，应用层组装，只查一次，而非嵌套多次查询。

继承的话，如果用lombok的@Data话，记得用上@ToString(callSuper = true)，要不然在集合类中，toString()可能出问题，默认的toString()方法只有子类的属性，没有父类属性
domain

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
import lombok.Data;
import lombok.ToString;

@Data
@ToString(callSuper = true)
public class InnerTxDo extends InnerTx implements Serializable {
    private static final long serialVersionUID = 1L;
    private Long id;
    private String bizType;
	...
    private List<InnerItem> innerItemList;
}
@Data
public class InnerItem implements Serializable {
    private static final long serialVersionUID = 1L;
    private Long       id;
    private Long       innerTxId;
    ...
    private BigDecimal amount;
}

@Data
public class InnerTxParam implements Serializable {
    private static final long serialVersionUID = 1L;
    private String       startTime;
    private String       endTime;
    private List<String> fromAddressList;
}

mapper.java

复制代码

1
2
3
4
public interface InnerTxMapper extends BaseMapper<InnerTx> {
    List<InnerTxDo> queryInnerTxList(InnerTxParam innerTxParam);
}

mapper.xml

复制代码

<select id="queryInnerTxList" resultMap="BaseResultMap"
            parameterType="com.*.domain.InnerTxParam">
        SELECT t.id,t.biz_type,t.amount,ii.id inner_item_id, ii.pay_type,ii.amount inner_item_amount
        FROM t_inner_tx t Left JOIN t_inner_item ii on t.id=ii.inner_tx_id
        WHERE t.chain = #{chain}
        <if test="startTime !=null and startTime !='' ">
            and t.create_time  <![CDATA[>= ]]>  #{startTime}
        </if>
        <if test="endTime !=null and endTime!='' ">
            and t.create_time <![CDATA[ <= ]]> #{endTime}
        </if>
        <if test="fromAddressList != null and fromAddressList.size()>0">
            AND  t.from_address IN
            <foreach item="item" index="index" collection="fromAddressList" open="("
                     separator="," close=")">
                #{item}
            </foreach>
        </if>
    </select>

</mapper>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
<mapper namespace="com.*.dao.InnerTxMapper">
    <resultMap id="BaseResultMap" type="com.*.domain.InnerTxDo">
        <id column="id" jdbcType="BIGINT" property="id"/>
        <result column="biz_type" jdbcType="VARCHAR" property="bizType"/>
        <result column="amount" jdbcType="NUMERIC" property="amount"/>
        <collection property="innerItemList" ofType="com.*.domain.InnerItem">
            <result column="inner_item_id" jdbcType="VARCHAR" property="id"/>
            <!-- 两张表中都有amount字段，如果不起别名的话，会取值错误 -->
            <result column="inner_item_amount" jdbcType="NUMERIC" property="amount"/>
        </collection>
    </resultMap>

    <select id="queryInnerTxList" resultMap="BaseResultMap"
            parameterType="com.*.domain.InnerTxParam">
        SELECT t.id,t.biz_type,t.amount,ii.id inner_item_id, ii.pay_type,ii.amount inner_item_amount
        FROM t_inner_tx t Left JOIN t_inner_item ii on t.id=ii.inner_tx_id
        WHERE t.chain = #{chain}
        <if test="startTime !=null and startTime !='' ">
            and t.create_time  <![CDATA[>= ]]>  #{startTime}
        </if>
        <if test="endTime !=null and endTime!='' ">
            and t.create_time <![CDATA[ <= ]]> #{endTime}
        </if>
        <if test="fromAddressList != null and fromAddressList.size()>0">
            AND  t.from_address IN
            <foreach item="item" index="index" collection="fromAddressList" open="("
                     separator="," close=")">
                #{item}
            </foreach>
        </if>
    </select>

</mapper>