Mybatis association分布查询为单个基本类型字段（非自定义对象字段）赋值

落日映苍穹つ 2022-02-17 06:23 268阅读 0赞

之前使用association主要是下面两种情况

1.  左连接之后封装对象
2.  分步查询封装对象

比如

左连接查询之后封装Order里面的Customer对象

<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>

<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`定义为

<select id="queryCustomerById" resultType="Integer">
    	  SELECT COUNT(order_id) FROM `order` WHERE cust_id  = #{cust_id}
    	</select>

完成！！

association分布查询为单个基本类型字段（非自定义对象字段）赋值