之前使用association主要是下面两种情况
- 左连接之后封装对象
- 分步查询封装对象
比如
左连接查询之后封装Order里面的Customer对象
<!--由于数据库字段和实体类字段名称不对应,所以设置resultMap进行映射-->
<resultMap id="OrderMap" type="Order">
<id property="orderId" column="order_id"/>
<result property="orderName" column="order_name"/>
<result property="orderNum" column="order_num"/>
<association property="customer" javaType="Customer">
<id column="cust_id" property="custId"></id>
<result column="cust_name" property="custName"></result>
<result column="cust_phone" property="custPhone"></result>
<result column="email" property="custEmail"></result>
<result column="cust_profession" property="custProfession"></result>
</association>
</resultMap>
<select id="getAllOrder" resultMap="OrderMap">
select * from `order` as o left join `customer` as c on o.cust_id = c.cust_id
</select>
给association传入cust_id再进行下一次查询,查出来的对象封装成Customer对象
<!--分步查询(就是利用第一次查询的结果进行下一次查询)-->
<resultMap id="OrderMap2" type="Order">
<id property="orderId" column="order_id"/>
<result property="orderName" column="order_name"/>
<result property="orderNum" column="order_num"/>
<association property="customer" javaType="Customer"
select="fei.mapper.CustomerMapper.queryCustomerById" column="cust_id">
<id column="cust_id" property="custId"></id>
<result column="cust_name" property="custName"></result>
<result column="cust_phone" property="custPhone"></result>
<result column="email" property="custEmail"></result>
<result column="cust_profession" property="custProfession"></result>
</association>
</resultMap>
<!--根据cust_id查询客户-->
<select id="queryCustomerById" parameterType="Integer" resultMap="CustomerMap">
SELECT * FROM `customer` WHERE cust_id = #{cust_id}
</select>
这两种比较常见的association用法相信大家都很熟悉
下面是我经过探索发现,association可以分步查询为单个基本类型字段(非自定义对象字段)赋值
这样一个需求,Customer对象有一个Integer类型的orderNum字段(订单数),现在可以直接使用association可以分步查询,然后为orderNum字段赋值
代码如下
<!--分步查询(就是利用第一次查询的结果进行下一次查询)-->
<resultMap id="CustomerMap" type="Customer">
<!--主键的映射-->
<id column="cust_id" property="custId"></id>
<result column="cust_name" property="custName"></result>
<result column="cust_phone" property="custPhone"></result>
<result column="email" property="custEmail"></result>
<result column="cust_profession" property="custProfession"></result>
<association property="orderNum" javaType="Integer"
select="fei.mapper.OrderMapper.queryOrderNum" column="cust_id">
</association>
</resultMap>
然后fei.mapper.OrderMapper.queryOrderNum
定义为
<!--根据cust_id查询订单数量-->
<select id="queryCustomerById" resultType="Integer">
SELECT COUNT(order_id) FROM `order` WHERE cust_id = #{cust_id}
</select>
完成!!
association分布查询为单个基本类型字段(非自定义对象字段)赋值