Mybatis中collection的使用（一对多嵌套查询）

1. 方法一: 嵌套结果映射

    <!-- 定义resultMap -->
    <resultMap id="UserResultMap" type="User">
        <result column="id" property="id"/>
        <result column="name" property="name"/>
        <collection ofType="role" property="roles">
            <result column="role_id" property="id"/>
            <result column="role_name" property="name"/>
            <result column="role_type" property="type"/>
        </collection>
    </resultMap>

    <!--查询语句-->
    <select id="selectUserById" resultMap="UserResultMap">
        select u.id ,u.name,r.id AS role_id ,r.name AS role_name ,r.type AS role_type FROM user AS u INNER JOIN role AS r ON u.id = r.user_id where u.id = #{id}
    </select>

2. 方法二: 嵌套select 查询

    <resultMap id="providerMap" type="com.szl.pojo.entity.Provider">
        <id property="id" column="id"/>
        <result property="proContact" column="proContact"/>
    </resultMap>
    <resultMap type="com.szl.pojo.entity.Provider" id="ProviderMap">
        <result property="id" column="id" jdbcType="INTEGER"/>
        <result property="proCode" column="proCode" jdbcType="VARCHAR"/>
        <result property="proName" column="proName" jdbcType="VARCHAR"/>
        <result property="proDesc" column="proDesc" jdbcType="VARCHAR"/>
        <result property="proContact" column="proContact" jdbcType="VARCHAR"/>
        <result property="proPhone" column="proPhone" jdbcType="VARCHAR"/>
        <result property="proAddress" column="proAddress" jdbcType="VARCHAR"/>
        <result property="proFax" column="proFax" jdbcType="VARCHAR"/>
        <result property="createdBy" column="createdBy" jdbcType="INTEGER"/>
        <result property="creationDate" column="creationDate" jdbcType="TIMESTAMP"/>
        <result property="modifyDate" column="modifyDate" jdbcType="TIMESTAMP"/>
        <result property="modifyBy" column="modifyBy" jdbcType="INTEGER"/>
    <!-- 一对多 订单关联属性映射：使用select引用方式 -->
    <!-- collection定义关联集合类型的属性的封装规则 -->
    <!-- property: 对应po类关联的集合字段 orders -->
    <!-- column: 关联字段 与sql查询结果的字段对应 -->
    <!-- ofType: 指定的是映射到list集合属性中的pojo类型 -->
    <!-- fetchType: 加载类型：lazy（延迟加载）eager（立即加载）如果使用，它将取代全局配置参数lazyLoadingEnable -->
        <collection property="listBill" ofType="com.szl.pojo.entity.SmbmsBill" column="id" select="selectByProviderId"></collection>
    </resultMap>
    <resultMap type="com.szl.pojo.entity.SmbmsBill" id="smbmsBill">
        <id property="id" column="id" jdbcType="INTEGER"/>
        <result property="billcode" column="billCode" jdbcType="VARCHAR"/>
        <result property="productname" column="productName" jdbcType="VARCHAR"/>
        <result property="productdesc" column="productDesc" jdbcType="VARCHAR"/>
        <result property="productunit" column="productUnit" jdbcType="VARCHAR"/>
        <result property="productcount" column="productCount" jdbcType="NUMERIC"/>
        <result property="totalprice" column="totalPrice" jdbcType="NUMERIC"/>
        <result property="ispayment" column="isPayment" jdbcType="INTEGER"/>
        <result property="createdby" column="createdBy" jdbcType="INTEGER"/>
        <result property="creationdate" column="creationDate" jdbcType="TIMESTAMP"/>
        <result property="modifyby" column="modifyBy" jdbcType="INTEGER"/>
        <result property="modifydate" column="modifyDate" jdbcType="TIMESTAMP"/>
        <result property="providerid" column="providerId" jdbcType="INTEGER"/>
    </resultMap>
<select id="selectByProAddress" resultMap="ProviderMap">
    select * from smbms_provider where proAddress like concat('%',#{proAddress},'%')
</select>
    <select id="selectByProviderId" resultMap="smbmsBill">
        select * from smbms_bill where providerId=#{providerId}
    </select>

3.错误记录

在查询供应商对应的所有订单信息时，查询结果只有供应商的信息，没有订单信息，后面经过询问，发现是

这里的column我开始填的是providerId,而不是id,所以导致没有查询出订单信息，这里的id对应的是Provider表里面的id ,所以在查询是要查询Provider的id，不然还是没有订单数据