Mybatis 一对多关系表及多条件联合查询xml

1.   类对象

• （1）合同类：一个合同有多个产品

public class Contract {      //合同参数较多，只选择一两个属性

    private Long id;  //ID

    private List<Product> product;  //一个合同下有多个产品

   private String contractNum;  //合同编号

  ...

  //get,set方法忽略

 

}

• （2）产品类：一个产品对应一个合同

public class Product {   //产品参数较多，只选择一两个属性

 

private Long id;  //产品ID

private Contract contract;  //合同类

private Long contractId;   //合同ID

private String productModel;  //产品型号

...

//get,set方法忽略

}

 

 

2. 需求

需求：查询产品合同列表，显示产品及其相关的合同信息

因合同以及产品查询字段较多，将查询条件构建为ProductQueryParam 类

public class ProductQueryParam {

    private String productModel;

private String contractNum;

...

//get,set方法忽略

 

}

3. Xml查询

如下

    <resultMap type="Product" id="ProductResult">

        <result property="id" column="id"/>

        <result property="contractId" column="contract_id"/>

        <result property="productModel" column="product_model"/>

        <association property="contract" select="getContract" column="contract_id" javaType="Contract"/>

    </resultMap>

 

    <resultMap type="Contract" id="ContractResult">

        <result property="id" column="id"/>

        <result property="contractNum" column="contract_num"/>

    </resultMap>

 

 

    <select id="getContract" parameterType="int" resultMap="ContractResult">

        select *

        from contract

        where id = #{contract_id}

    </select>

 

    <select id="selectProductList" parameterType="ProductQueryParam" resultMap="ProductResult">

        select p.id,

        p.contract_id,

        p.product_num,

        p.product_model,

        from product p left join contract c on p.contract_id=c.id

        where 1=1

            <if test="contractNum != null ">and c.contract_num  like concat('%',#{contractNum},'%')</if>

            <if test="productModel != null  and productModel != ''">and p.product_model like concat('%',#{productModel},'%')</if>

    </select>

4. 调用方法

public List<Product> selectProductList(ProductQueryParam ProductQueryParam);