1. 合同付款类:涉及Mybatis Collection的 多多关联配置 (配置resultMap 时选择相关字段配置,没配置的查询后不会有数据)
public class ContractPayBills {
private String contractID;
private Integer payBillsId;
private List<Contract> contractList; //合同
private List<PayBill> payBillList; //合同付款
}
2. 合同付款类的 映射文件:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.deyun.contract.dao.ContractCompMapper">
<resultMap id="BaseResultMap" type="com.deyun.contract.bean.ContractPayBills">
<result column="contract_id" property="contractID" jdbcType="VARCHAR" />
<result column="pay_bills_id" property="payBillsId" jdbcType="INTEGER" />
<collection property="contractList" ofType="com.deyun.contract.bean.Contract"
column="contract_id" select="selectContractList" />
<collection property="payBillList" ofType="com.deyun.contract.bean.PayBill"
column="pay_bills_id" select="selectPayBillList" />
</resultMap>
<!-- 合同 数据结构 -->
<resultMap id="contractMap" type="com.deyun.contract.bean.Contract">
<id column="id" property="id" jdbcType="VARCHAR" />
<result column="name" property="name" jdbcType="VARCHAR" />
<result column="contract_name" property="contractName" jdbcType="VARCHAR" />
<result column="company" property="company" jdbcType="VARCHAR" />
<result column="amount" property="amount" jdbcType="DOUBLE" />
<result column="start_date" property="startDate" jdbcType="TIMESTAMP" />
<result column="end_date" property="endDate" jdbcType="TIMESTAMP" />
<result column="way" property="way" jdbcType="VARCHAR" />
<result column="receivable_amount" property="receivableAmount" jdbcType="DOUBLE" />
<result column="clause" property="clause" jdbcType="VARCHAR" />
</resultMap>
<!-- 合同 查询 -->
<select id="selectContractList" resultMap="contractMap"
parameterType="java.lang.String">
select id,name, contract_name, company,amount,start_date,end_date,way,receivable_amount,pay_bill_status_id,clause,comp_status
from contract
where id = #{id,jdbcType=VARCHAR}
</select>
<!-- 付款 数据结构-->
<resultMap id="payBillMap" type="com.deyun.contract.bean.PayBill">
<id column="id" property="id" jdbcType="INTEGER" />
<result column="amount" property="amount" jdbcType="DOUBLE" />
<result column="date" property="date" jdbcType="TIMESTAMP" />
<result column="note" property="note" jdbcType="VARCHAR" />
</resultMap>
<!-- 付款 查询 -->
<select id="selectPayBillList" resultMap="payBillMap"
parameterType="java.lang.Integer">
select id, amount, date,note
from pay_bill
where id = #{id,jdbcType=INTEGER}
</select>
<!-- 通过ID 查询 某个合同 的 已付款情况 - -->
<select id="findHasPayAmountById" resultMap="BaseResultMap"
parameterType="java.lang.String">
select c.contract_id, c.amount,c.receivable_amount,SUM(d.amount) hasPay
from (
select b.contract_id,a.amount , a.receivable_amount ,b.pay_bills_id
from contract a
inner join
contract_pay_bills b
on a.id = b.contract_id ) c
inner join pay_bill d
on c.pay_bills_id = d.id
WHERE c.contract_id = #{contractId, jdbcType=VARCHAR}
</select>
<!-- 查询合同已付款的总金额 -->
<select id="findHasPay" resultType="java.lang.Double" parameterType="java.lang.String">
select SUM(d.amount) hasPay
from (
select b.contract_id,a.amount , a.receivable_amount ,b.pay_bills_id
from contract a
inner join
contract_pay_bills b
on a.id = b.contract_id ) c
inner join pay_bill d
on c.pay_bills_id = d.id
WHERE c.contract_id = #{contractId, jdbcType=VARCHAR}
</select>
<!-- ContractDTO 映射 Map -->
<resultMap id="ContractDTOMap" type="com.deyun.contract.bean.dto.ContractDTO">
<id column="id" property="id" jdbcType="VARCHAR" />
<result column="name" property="name" jdbcType="VARCHAR" />
<result column="contract_name" property="contractName" jdbcType="VARCHAR" />
<result column="company" property="company" jdbcType="VARCHAR" />
<result column="amount" property="amount" jdbcType="DOUBLE" />
<result column="start_date" property="startDate" jdbcType="TIMESTAMP" />
<result column="end_date" property="endDate" jdbcType="TIMESTAMP" />
<result column="way" property="way" jdbcType="VARCHAR" />
<result column="operate" property="operate" jdbcType="VARCHAR" />
<result column="xs_finish" property="xsFinish" jdbcType="VARCHAR" />
<result column="cg_finish" property="cgFinish" jdbcType="VARCHAR" />
<result column="receivable_amount" property="receivableAmount" jdbcType="DOUBLE" />
<association property="paymentStatus" javaType="PaymentStatus">
<result property="name" column="name"/>
<result property="price" column="price"/>
</association>
</resultMap>
<!-- ContractDTO 数据结构 -->
<select id="findContractDTO" resultMap="ContractDTOMap" >
select id, name,contract_name,company,amount,start_date,end_date,way,receivable_amount
from contract
</select>
</mapper>