java表的分组查询collection用法

温暖.jpg

最近在学习java做项目中遇到了关联查询之后要填充modl，但是model是分组的，里面有多个数组，如果只是用关联查询，对于这种会出现重复查询，所以这里要用到Mybatis的collection来处理

1、首先介绍一下我们的model属性（可以看到有2个指定对象的数组）

    private Long id;
    private String customerNo;
    private Long listId;
    private String name;
    private String identifyType;
    private String identifyNo;   
    private String phoneA;
    private String phoneB;
    private String phoneC;
    private String telephone;
    private String email;
    private String idcardAdress;
    private String householdAddress;
    private String liveAddress;
    private String workAddress;
    private Date gmtCreate;
    private Date gmtModified;
    private Long createBy;
    private Long lastUpdateBy;
    private String customerType;
    private List<CmCustomerContact> contactList;
    private List<CmCustomerBankcard> bankcardList;

2、在mapper里面对接输出model

<resultMap id="BaseResultMap1" type="com.paic.gamma_cm.model.dto.CmCustomerForExcel">
    <id column="id" jdbcType="BIGINT" property="id" />
    <result column="customer_no" jdbcType="VARCHAR" property="customerNo" />
    <result column="list_id" jdbcType="BIGINT" property="listId" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <result column="identify_type" jdbcType="VARCHAR" property="identifyType" />
    <result column="identify_no" jdbcType="VARCHAR" property="identifyNo" />
    <result column="phone_a" jdbcType="VARCHAR" property="phoneA" />
    <result column="phone_b" jdbcType="VARCHAR" property="phoneB" />
    <result column="phone_c" jdbcType="VARCHAR" property="phoneC" />
    <result column="telephone" jdbcType="VARCHAR" property="telephone" />
    <result column="email" jdbcType="VARCHAR" property="email" />
    <result column="idcard_adress" jdbcType="VARCHAR" property="idcardAdress" />
    <result column="household_address" jdbcType="VARCHAR" property="householdAddress" />
    <result column="live_address" jdbcType="VARCHAR" property="liveAddress" />
    <result column="work_address" jdbcType="VARCHAR" property="workAddress" />
    <result column="gmt_create" jdbcType="TIMESTAMP" property="gmtCreate" />
    <result column="gmt_modified" jdbcType="TIMESTAMP" property="gmtModified" />
    <result column="create_by" jdbcType="BIGINT" property="createBy" />
    <result column="last_update_by" jdbcType="BIGINT" property="lastUpdateBy" />
    <result column="customer_type" jdbcType="VARCHAR" property="customerType" />
    <collection property="contactList" resultMap="CMContactResultMap" />
    <collection property="bankcardList" resultMap="CMBankResultMap" />
    </resultMap>
<resultMap id="CMContactResultMap" type="com.paic.gamma_cm.model.dto.CmCustomerContact">
            <id column="contact_id" property="id" jdbcType="BIGINT"/>
            <result column="customer_id" property="customerId" jdbcType="BIGINT"/>
            <result column="relation_type" property="relationType" jdbcType="VARCHAR"/>
            <result column="relation_desc" property="relationDesc" jdbcType="VARCHAR"/>
            <result column="contact_identify_type" property="identifyType" jdbcType="VARCHAR"/>
            <result column="contact_identify_no" property="identifyNo" jdbcType="VARCHAR"/>
            <result column="contact_name" property="contactName" jdbcType="VARCHAR"/>
            <result column="contact_phone" property="contactPhone" jdbcType="VARCHAR"/>
            <result column="gmt_create" property="gmtCreate" jdbcType="TIMESTAMP"/>
            <result column="gmt_modified" property="gmtModified" jdbcType="TIMESTAMP"/>
  </resultMap>
  <resultMap id="CMBankResultMap" type="com.paic.gamma_cm.model.dto.CmCustomerBankcard">
            <id column="bankcard_id" property="id" jdbcType="BIGINT"/>
            <result column="customer_id" property="customerId" jdbcType="BIGINT"/>
            <result column="card_bank" property="cardBank" jdbcType="VARCHAR"/>
            <result column="account_type" property="accountType" jdbcType="VARCHAR"/>
            <result column="bank_card_no" property="bankCardNo" jdbcType="VARCHAR"/>
            <result column="gmt_create" property="gmtCreate" jdbcType="TIMESTAMP"/>
            <result column="gmt_modified" property="gmtModified" jdbcType="TIMESTAMP"/>
  </resultMap>

3、在mapper的xml里写关联查询

<select id="selectCustomerAllList" resultMap="BaseResultMap1">
  SELECT
customer . id,
customer .customer_no ,
customer .list_id ,
customer . name ,
customer .identify_type,
customer .identify_no ,
customer .phone_a ,
customer .phone_b ,
customer .phone_c ,
customer .telephone,
customer .email,
customer .idcard_adress,
customer .household_address,
customer .live_address,
customer .work_address,
customer .customer_type,
contact . id AS contact_id,
contact .relation_type,
contact .identify_type AS contact_identify_type,
contact .identify_no AS contact_identify_no,
contact .contact_name,
contact .contact_phone,
bankcard. id AS bankcard_id,
bankcard.card_bank,
bankcard.account_type,
bankcard.bank_card_no
FROM
cm_customer customer
LEFT JOIN cm_customer_contact contact ON customer.id = contact.customer_id
LEFT JOIN cm_customer_bankcard bankcard ON customer.id = bankcard.customer_id 
  </select>

简单3步就完成了分组查询，其中要注意BaseResultMap1中表属性对应的要与自己创建的匿名表列名对应（区分相同字段在不同表中的情况）
详细的collection用法和Association用法可参考https://blog.csdn.net/qq_38157516/article/details/79712721