mybatis 级联查询
一对一用 association 标签,一对多用 collection 标签。多对一就是一对一,只是站的角度不同
一对一
完全没必要做级联查询,可以一个对象来接收两个表的字段,这样数据库层面查询次数更少,但还是写下做法
java 实体
@Data
public class SkuWithPrice{
private Long id;
private String code;
private String name;
// 一个 sku 只有一个价格
private Price price;
@Data
public static class Price{
private Long id;
private BigDecimal price;
private String currency;
}
}
映射文件
<resultMap id="one2oneMap" type="com.study.mybatis.dto.SkuWithPrice">
<id column="id" property="id" jdbcType="BIGINT"/>
<result column="code" property="code" jdbcType="VARCHAR"/>
<result column="name" property="name" jdbcType="VARCHAR"/>
<association property="price"
column="id"
javaType="com.study.mybatis.dto.SkuWithPrice$Price"
select="findPriceBySkuId">
</association>
</resultMap>
<!-- 主表,sku -->
<select id="findSkuAndPrice" resultMap="one2oneMap">
select a.id, a.code, a.name from t_sku a
</select>
<!-- 子表,price -->
<select id="findPriceBySkuId" resultType="com.study.mybatis.dto.SkuWithPrice$Price">
<!-- #{id} 是主表传过来的 -->
select b.id, b.price, b.currency from t_price b on b.sku_id = #{id}
</select>
一对多
java 实体
@Data
public class ItemWithSku{
private Long id;
private String code;
private String name;
// 一个商品多个 sku
private List<Sku> skus;
@Data
public static class Sku{
private Long id;
private String code;
private String name;
}
}
映射文件
<resultMap id="one2manyMap" type="com.study.mybatis.dto.ItemWithSku">
<id column="id" property="id" jdbcType="BIGINT"/>
<result column="code" property="code" jdbcType="VARCHAR"/>
<result column="name" property="name" jdbcType="VARCHAR"/>
<collection property="skus"
column="id"
javaType="ArrayList"
ofType="com.study.mybatis.dto.ItemWithSku$Sku"
select="getSkusByItemId"
/>
</resultMap>
<!-- 先查主表,以主表分页就不会有分页问题 -->
<select id="findItemAndSku" resultMap="one2manyMap">
select a.id, a.code, a.name from t_item a
</select>
<!-- 再根据主表查询子表 -->
<select id="getSkusByItemId" resultType="com.study.mybatis.dto.ItemWithSku$Sku">
select a.id, a.code, a.name from t_sku a where a.item_id = #{id}
</select>