之前有遇到这么一个问题,通过ID查找查找一个复杂对象,由几个表格的数据共同组成。
开发的时候,采用的方法是,先查主表,再根据外键查从表。
这样增加了与数据库的查询次数,数据量大的时候效率不理想。
于是进行了优化,一次性查出所有的表的数据并拼成复杂对象,dao层接收。
代码如下:
<!-- 一次性导出现金申报书所有关联信息 -->
<resultMap type="com.fiberhome.ms.cus.cashform.entity.CashformReturn" id="ResultMap">
<id property="id" column="ID" /> //必须提出来作为区分数据的关键,一个ID一条数据
<association property="cashform" javaType="com.fiberhome.ms.cus.cashform.entity.Cashform" resultMap="CashformResultMap" />
<association property="formHead" javaType="com.fiberhome.ms.cus.cashform.entity.CashformDetail" resultMap="DetailResultMap" />
<collection property="formBody" ofType="com.fiberhome.ms.cus.cashform.entity.CashformList" resultMap="ListResultMap" />
</resultMap>
<resultMap type="com.fiberhome.ms.cus.cashform.entity.Cashform" id="CashformResultMap">
<id property="id" column="ID" />
<result property="formId" column="FORM_ID" />
<result property="formSerialNum" column="FORM_SERIAL_NUM" />
<result property="port" column="PORT" />
<result property="formNumber" column="FORM_NUMBER" />
<result property="state" column="STATE" />
<result property="cashFormCategory" column="CASH_FORM_CATEGORY" />
<result property="flag" column="FLAG" />
<result property="enterDate" column="ENTER_DATE" jdbcType="DATE" />
<result property="scanDate" column="SCAN_DATE" jdbcType="DATE" />
</resultMap>
<resultMap type="com.fiberhome.ms.cus.cashform.entity.CashformDetail" id="DetailResultMap">
<id property="id" column="detailId" />
<result property="cashFormId" column="secondCashFormId" />
<result property="sourPlace" column="SOUR_PLACE" />
<result property="desPlace" column="DES_PLACE" />
<result property="placeDate" column="PLACE_DATE" />
<result property="name" column="NAME" />
<result property="sex" column="SEX" />
<result property="country" column="COUNTRY" />
<result property="birthplace" column="BIRTHPLACE" />
<result property="birthday" column="BIRTHDAY" jdbcType="DATE" />
<result property="certificateCategory" column="CERTIFICATE_CATEGORY" />
<result property="signDate" column="SIGN_DATE" jdbcType="TIMESTAMP" />
<result property="idNumber" column="ID_NUMBER" />
<result property="issuePlace" column="ISSUE_PLACE" />
<result property="customsSignature" column="CUSTOMS_SIGNATURE" />
<result property="placeAndPhone" column="PLACE_AND_PHONE" />
<result property="phone" column="PHONE" />
<result property="flag" column="detailFlag" />
<result property="formId" column="cashFormFormId" />
<result property="cashFormCategory" column="cashCaregory" />
</resultMap>
<resultMap id="ListResultMap" type="com.fiberhome.ms.cus.cashform.entity.CashformList" >
<id column="cashformListId" property="id" jdbcType="BIGINT" />
<result column="thirdCashFormId" property="cashFormId" jdbcType="INTEGER" />
<result column="CASH_TYPE" property="cashType" jdbcType="NVARCHAR" />
<result column="CURRENCY_TYPE" property="currencyType" jdbcType="NVARCHAR" />
<result column="CURRENCY_VALUE" property="currencyValue" jdbcType="NVARCHAR" />
<result column="listFlag" property="flag" jdbcType="NVARCHAR" />
<result column="OTHER" property="other" jdbcType="NVARCHAR" />
<result column="ISORNOT" property="isornot" jdbcType="BIT" />
<collection property="third" ofType="com.fiberhome.ms.cus.cashform.entity.CashformThird" resultMap="thirdResultMap" />
</resultMap>
<resultMap id="thirdResultMap" type="com.fiberhome.ms.cus.cashform.entity.CashformThird">
<result property="id" column="thirdId" jdbcType="BIGINT" />
<result property="cashFormListId" column="CASH_FORM_LIST_ID" jdbcType="BIGINT" />
<result property="ownerType" column="OWNER_TYPE" jdbcType="VARCHAR" />
<result property="ownerNameLaw" column="OWNER_NAME_LAW" jdbcType="VARCHAR" />
<result property="ownerName" column="OWNER_NAME" jdbcType="VARCHAR" />
<result property="ownerSex" column="OWNER_SEX" jdbcType="VARCHAR" />
<result property="ownerAddress" column="OWNER_ADDRESS" jdbcType="VARCHAR" />
<result property="flag" column="thirdFlag" jdbcType="VARCHAR" />
</resultMap>
<select id = "selectCashformAllInfo" resultMap="ResultMap">
SELECT T.* FROM (
SELECT
A.ID,
A.FORM_ID,
A.PORT,
A.FORM_SERIAL_NUM,
A.FORM_NUMBER,
A.STATE,
A.CASH_FORM_CATEGORY,
A.ENTER_DATE,
A.SCAN_DATE,
A.FLAG,
B.ID AS detailId,
B.CASH_FORM_ID AS secondCashFormId,
B.SOUR_PLACE,
B.DES_PLACE,
B.NAME,
B.SEX,
B.COUNTRY,
B.BIRTHDAY,
B.BIRTHPLACE,
B.CERTIFICATE_CATEGORY,
B.ID_NUMBER,
B.ISSUE_PLACE,
B.PLACE_AND_PHONE,
B.PHONE,
B.CUSTOMS_SIGNATURE,
B.FORM_ID AS cashFormFormId,
B.SIGN_DATE,
B.FLAG AS detailFlag,
B.CASH_FORM_CATEGORY AS cashCaregory,
C.ID AS cashformListId ,
C.CASH_TYPE,
C.CURRENCY_VALUE,
C.CURRENCY_TYPE,
C.ISORNOT,
C.OTHER,
C.FLAG AS listFlag,
C.CASH_FORM_ID AS thirdCashFormId,
D.ID AS thirdId,
D.OWNER_NAME_LAW,
D.OWNER_NAME,
D.OWNER_SEX,
D.OWNER_ADDRESS,
D.CASH_FORM_LIST_ID,
D.FLAG AS thirdFlag
FROM
CUS_CASH_FORM A
LEFT JOIN CUS_CASH_FORM_DETAIL B ON B.CASH_FORM_ID = A.ID AND B.FLAG = '1'
LEFT JOIN CUS_CASH_LIST C ON C.CASH_FORM_ID = A.ID AND C.FLAG = '1'
LEFT JOIN CUS_CASH_FORM_THIRD D ON D.CASH_FORM_LIST_ID = C.ID AND D.FLAG = '1'
) T
WHERE T.FLAG = '1'
AND T.STATE != '0'
AND T.ID in
<foreach item="id" collection="ids" open="(" separator="," close=")">
#{id}
</foreach>
</select>