mybatis的mapper.xml编写

63 阅读 0 评论 42 点赞

我是靠谱客的博主魁梧唇彩，最近开发中收集的这篇文章主要介绍mybatis的mapper.xml编写，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

最近在使用mybatis进行项目开发，关于mapper.xml进行如下总结，希望对跟我一样的初学者有一些帮助：

一、resultMap的编写，该类型最大的用处就是用于定义查询返回的类型，代码如下所示：

resultMap标签中的type属性为整个实体对应文件，若该实体包含对象类型属性则使用association标签；若包含list属性则使用collection标签。collection中属性过多，可以使用例子中的查询方式。

<resultMap
		type="org.test.settle.national.pension.PensionTransBill"
		id="PensionTransBillResultMap">
		<id column="BAZ463" property="id" />
		<result column="BAZ002" property="baz002" />
		<result column="AAB299" property="tgtAgencyState" />
		<result column="AAE270" property="contactNoteNumber" />
		<result column="AAE405" property="transBillNumber" />
		<result column="BAC999" property="formerPersonNumber" />
		<result column="AAE035" property="transferDate" />
		<result column="AIC113" property="pensionInsTransDirection" />
		<result column="AAE173" property="transferType" />
		<result column="AAB004" property="srcUnitName" />
		<result column="AAB305" property="firstJoinlandPersonalPaytime" />
		<result column="AAC049" property="firstPersonalPaytime" />
		<result column="AAC032" property="personActCreateTime" />
		<result column="AAE030" property="paymentBeginTime" />
		<result column="AAE031" property="paymentEndTime" />
		<result column="AAE201" property="actualPaymentMonths" />
		<result column="AIC093" property="oldPersonalActSum" />
		<result column="AIC089" property="theNewPersonalActSum" />
		<result column="AIC082" property="transYearInPersonalAct" />
		<result column="AIC083" property="personalActTransSum" />
		<result column="AIC084" property="regionFundTransferSum" />
		<result column="AIC102" property="transferFundSum" />
		<result column="BAE787" property="repeatFees" />
		<result column="BAE829" property="repeatFeeFlag" />
		<result column="BAE828" property="giveupAddFee" />
		<result column="AAD019" property="fundTransferBankNumber" />
		<result column="AAB300" property="transferInAgencyName" />
		<result column="AAA146" property="transferOutAgencyName" />
		<result column="AAZ198" property="nationAgencyId" />
		<result column="BAE062" property="transEventId" />
		<result column="BAE037" property="onlineDownload" />
		<result column="BAE038" property="isConfirm" />
		<result column="BAE022" property="uploadStatus" />
		<result column="BZE111" property="uploadOperator" />
		<result column="BZE136" property="uploadOperateTime" />
		<result column="AAE013" property="remark" />
		<result column="BAE681" property="transBeforeEventId" />
		<result column="AAC067" property="contactPhone" />
		<result column="BAE039" property="financeStatus" />
		<!-- 六经字段 -->
		<result column="AAE011" property="aae011" />
		<result column="AAE036" property="aae036" />
		<result column="AAB034" property="aab034" />
		<result column="AAA027" property="aaa027" />
		<result column="BZE011" property="bze011" />
		<result column="BZE036" property="bze036" />
		<result column="BZE034" property="bze034" />
		<association property="pensionTransBillPK"
			javaType="org.test.settle.national.pension.PensionTransBillPK">
			<result column="AAZ341" property="sysTraceId" />
			<result column="AAB301" property="srcAgencyState" />
		</association>
		<association property="transferInfo"
			javaType="org.test.settle.common.settleinfo.TransferInfo">
			<result column="AAC001" property="personId" />
			<result column="AAC999" property="personNumber" />
			<result column="AAC002" property="socialEnsureNumber" />
			<result column="AAC003" property="name" />
			<result column="AAC004" property="sex" />
			<result column="AAC006" property="birthday" />
			<result column="AAC028" property="migrantWorkers" />
			<result column="AAC010" property="householdAddress" />
			<result column="AAE473" property="transferAccountType" />
			<result column="AAC007" property="workDate" />
		</association>
		<!-- 用于一对多关系配置，在保存时没有对应字段保存 -->
		<collection property="pensionTransBillItems"
			column="BAZ463"
			ofType="org.test.settle.national.pension.PensionTransBillItem"
			select="org.test.settle.national.pension.PensionTransBillItemDAO.findTransBillItemByTransBillId">
		</collection>

	</resultMap>

二、编写查询方法，动态拼接查询条件，入参类型为map，查询结果以上面定义的resultMap形式展现：

<select id="selectEntityByMap"
		resultMap="PensionTransBillResultMap" parameterType="map">
		select * from test
		<where>
			<include refid="where_criteria"></include>
		</where>
		order by aae036 desc
	</select>
	<!-- 查询条件 -->
	<sql id="where_criteria">
		<if test="id!=null">and BAZ463=#{id}</if>
		<if test="baz002!=null">and BAZ002=#{baz002}</if>
		<if test="tgtAgencyState!=null">and AAB299=#{tgtAgencyState}</if>
		<if test="contactNoteNumber!=null">and AAE270=#{contactNoteNumber}</if>
		<if test="transBillNumber!=null">and AAE405=#{transBillNumber}</if>
		<if test="formerPersonNumber!=null">and BAC999=#{formerPersonNumber}</if>
		<if test="transferDate!=null">and AAE035=#{transferDate}</if>
		<if test="pensionInsTransDirection!=null">and AIC113=#{pensionInsTransDirection}</if>
		<if test="transferType!=null">and AAE173=#{transferType}</if>
		<if test="srcUnitName!=null">and AAB004=#{srcUnitName}</if>
		<if test="firstJoinlandPersonalPaytime!=null">and AAB305=#{firstJoinlandPersonalPaytime}</if>
		<if test="firstPersonalPaytime!=null">and AAC049=#{firstPersonalPaytime}</if>
		<if test="personActCreateTime!=null">and AAC032=#{personActCreateTime}</if>
		<if test="paymentBeginTime!=null">and AAE030=#{paymentBeginTime}</if>
		<if test="paymentEndTime!=null">and AAE031=#{paymentEndTime}</if>
		<if test="actualPaymentMonths!=null">and AAE201=#{actualPaymentMonths}</if>
		<if test="oldPersonalActSum!=null">and AIC093=#{oldPersonalActSum}</if>
		<if test="theNewPersonalActSum!=null">and AIC089=#{theNewPersonalActSum}</if>
		<if test="transYearInPersonalAct!=null">and AIC082=#{transYearInPersonalAct}</if>
		<if test="personalActTransSum!=null">and AIC083=#{personalActTransSum}</if>
		<if test="regionFundTransferSum!=null">and AIC084=#{regionFundTransferSum}</if>
		<if test="transferFundSum!=null">and AIC102=#{transferFundSum}</if>
		<if test="repeatFees!=null">and BAE787=#{repeatFees}</if>
		<if test="repeatFeeFlag!=null">and BAE829=#{repeatFeeFlag}</if>
		<if test="giveupAddFee!=null">and BAE828=#{giveupAddFee}</if>
		<if test="fundTransferBankNumber!=null">and AAD019=#{fundTransferBankNumber}</if>
		<if test="transferInAgencyName!=null">and AAB300=#{transferInAgencyName}</if>
		<if test="transferOutAgencyName!=null">and AAA146=#{transferOutAgencyName}</if>
		<if test="nationAgencyId!=null">and AAZ198=#{nationAgencyId}</if>
		<if test="transEventId!=null">and BAE062=#{transEventId}</if>
		<if test="onlineDownload!=null">and BAE037=#{onlineDownload}</if>
		<if test="isConfirm!=null">and BAE038=#{isConfirm}</if>
		<if test="uploadStatus!=null">and BAE022=#{uploadStatus}</if>
		<if test="uploadOperator!=null">and BZE111=#{uploadOperator}</if>
		<if test="uploadOperateTime!=null">and BZE136=#{uploadOperateTime}</if>
		<if test="remark!=null">and AAE013=#{remark}</if>
		<if test="transBeforeEventId!=null">and BAE681=#{transBeforeEventId}</if>
		<if test="contactPhone!=null">and AAC067=#{contactPhone}</if>
		<if test="financeStatus!=null">and BAE039=#{financeStatus}</if>
		<if test="aae011!=null">and AAE011=#{aae011}</if>
		<if test="aae036!=null">and AAE036=#{aae036}</if>
		<if test="aab034!=null">and AAB034=#{aab034}</if>
		<if test="aaa027!=null">and AAA027=#{aaa027}</if>
		<if test="bze011!=null">and BZE011=#{bze011}</if>
		<if test="bze036!=null">and BZE036=#{bze036}</if>
		<if test="bze034!=null">and BZE034=#{bze034}</if>
		<if test="sysTraceId!=null">and AAZ341=#{sysTraceId}</if>
		<if test="srcAgencyState!=null">and AAB301=#{srcAgencyState}</if>
		<if test="personId!=null">and AAC001=#{personId}</if>
		<if test="personNumber!=null">and AAC999=#{personNumber}</if>
		<if test="socialEnsureNumber!=null">and AAC002=#{socialEnsureNumber}</if>
		<if test="name!=null">and AAC003=#{name}</if>
		<if test="sex!=null">and AAC004=#{sex}</if>
		<if test="birthday!=null">and AAC006=#{birthday}</if>
		<if test="migrantWorkers!=null">and AAC028=#{migrantWorkers}</if>
		<if test="householdAddress!=null">and AAC010=#{householdAddress}</if>
		<if test="transferAccountType!=null">and AAE473=#{transferAccountType}</if>
		<if test="workDate!=null">and AAC007=#{workDate}</if>

	</sql>

三、编写更新方法，动态进行value补充，入参类型为实体型：

<update id="updateEntity"
		parameterType="org.test.settle.national.pension.PensionTransBill">
		UPDATE test
		<trim prefix="set" suffixOverrides=",">
			<if test="baz002!=null"> BAZ002=#{baz002},</if>
			<if test="tgtAgencyState!=null"> AAB299=#{tgtAgencyState},</if>
			<if test="contactNoteNumber!=null"> AAE270=#{contactNoteNumber},</if>
			<if test="transBillNumber!=null"> AAE405=#{transBillNumber},</if>
			<if test="formerPersonNumber!=null"> BAC999=#{formerPersonNumber},</if>
			<if test="transferDate!=null"> AAE035=#{transferDate},</if>
			<if test="pensionInsTransDirection!=null"> AIC113=#{pensionInsTransDirection},</if>
			<if test="transferType!=null"> AAE173=#{transferType},</if>
			<if test="srcUnitName!=null"> AAB004=#{srcUnitName},</if>
			<if test="firstJoinlandPersonalPaytime!=null"> AAB305=#{firstJoinlandPersonalPaytime},</if>
			<if test="firstPersonalPaytime!=null"> AAC049=#{firstPersonalPaytime},</if>
			<if test="personActCreateTime!=null"> AAC032=#{personActCreateTime},</if>
			<if test="paymentBeginTime!=null"> AAE030=#{paymentBeginTime},</if>
			<if test="paymentEndTime!=null"> AAE031=#{paymentEndTime},</if>
			<if test="actualPaymentMonths!=null"> AAE201=#{actualPaymentMonths},</if>
			<if test="oldPersonalActSum!=null"> AIC093=#{oldPersonalActSum},</if>
			<if test="theNewPersonalActSum!=null"> AIC089=#{theNewPersonalActSum},</if>
			<if test="transYearInPersonalAct!=null"> AIC082=#{transYearInPersonalAct},</if>
			<if test="personalActTransSum!=null"> AIC083=#{personalActTransSum},</if>
			<if test="regionFundTransferSum!=null"> AIC084=#{regionFundTransferSum},</if>
			<if test="transferFundSum!=null"> AIC102=#{transferFundSum},</if>
			<if test="repeatFees!=null"> BAE787=#{repeatFees},</if>
			<if test="repeatFeeFlag!=null"> BAE829=#{repeatFeeFlag},</if>
			<if test="giveupAddFee!=null"> BAE828=#{giveupAddFee},</if>
			<if test="fundTransferBankNumber!=null"> AAD019=#{fundTransferBankNumber},</if>
			<if test="transferInAgencyName!=null"> AAB300=#{transferInAgencyName},</if>
			<if test="transferOutAgencyName!=null"> AAA146=#{transferOutAgencyName},</if>
			<if test="nationAgencyId!=null"> AAZ198=#{nationAgencyId},</if>
			<if test="transEventId!=null"> BAE062=#{transEventId},</if>
			<if test="onlineDownload!=null"> BAE037=#{onlineDownload},</if>
			<if test="isConfirm!=null"> BAE038=#{isConfirm},</if>
			<if test="uploadStatus!=null"> BAE022=#{uploadStatus},</if>
			<if test="uploadOperator!=null"> BZE111=#{uploadOperator},</if>
			<if test="uploadOperateTime!=null"> BZE136=#{uploadOperateTime},</if>
			<if test="remark!=null"> AAE013=#{remark},</if>
			<if test="transBeforeEventId!=null"> BAE681=#{transBeforeEventId},</if>
			<if test="contactPhone!=null"> AAC067=#{contactPhone},</if>
			<if test="financeStatus!=null"> BAE039=#{financeStatus},</if>
			<if test="aae011!=null"> AAE011=#{aae011},</if>
			<if test="aae036!=null"> AAE036=#{aae036},</if>
			<if test="aab034!=null"> AAB034=#{aab034},</if>
			<if test="aaa027!=null"> AAA027=#{aaa027},</if>
			<if test="bze011!=null"> BZE011=#{bze011},</if>
			<if test="bze036!=null"> BZE036=#{bze036},</if>
			<if test="bze034!=null"> BZE034=#{bze034},</if>
			<if test="pensionTransBillPK!=null">
				<if test="sysTraceId!=null"> AAZ341=#{sysTraceId},</if>
				<if test="srcAgencyState!=null"> AAB301=#{srcAgencyState},</if>
			</if>
			<if test="transferInfo!=null">
				<if test="personId!=null"> AAC001=#{personId},</if>
				<if test="personNumber!=null"> AAC999=#{personNumber},</if>
				<if test="socialEnsureNumber!=null"> AAC002=#{socialEnsureNumber},</if>
				<if test="name!=null"> AAC003=#{name},</if>
				<if test="sex!=null"> AAC004=#{sex},</if>
				<if test="birthday!=null"> AAC006=#{birthday},</if>
				<if test="migrantWorkers!=null"> AAC028=#{migrantWorkers},</if>
				<if test="householdAddress!=null"> AAC010=#{householdAddress},</if>
				<if test="transferAccountType!=null"> AAE473=#{transferAccountType},</if>
				<if test="workDate!=null"> AAC007=#{workDate},</if>
			</if>
		</trim>
		WHERE BAZ463=#{id}
	</update>

四、编写保存方法，入参类型为实体型：

<insert id="saveEntity"
		parameterType="org.test.settle.national.pension.PensionTransBill">
		insert into test
		(BAZ463,
		BAZ002,
		AAB299,
		AAE270,
		AAE405,
		BAC999,
		AAE035,
		AIC113,
		AAE173,
		AAB004,
		AAB305,
		AAC049,
		AAC032,
		AAE030,
		AAE031,
		AAE201,
		AIC093,
		AIC089,
		AIC082,
		AIC083,
		AIC084,
		AIC102,
		BAE787,
		BAE829,
		BAE828,
		AAD019,
		AAB300,
		AAA146,
		AAZ198,
		BAE062,
		BAE037,
		BAE038,
		BAE022,
		BZE111,
		BZE136,
		AAE013,
		BAE681,
		AAC067,
		BAE039,
		AAE011,
		AAE036,
		AAB034,
		AAA027,
		BZE011,
		BZE036,
		BZE034,
		AAZ341,
		AAB301,
		AAC001,
		AAC999,
		AAC002,
		AAC003,
		AAC004,
		AAC006,
		AAC028,
		AAC010,
		AAE473,
		AAC007)
		values
		(#{id,jdbcType=BIGINT},
		#{baz002,jdbcType=BIGINT},
		#{tgtAgencyState,jdbcType=VARCHAR},
		#{contactNoteNumber,jdbcType=VARCHAR},
		#{transBillNumber,jdbcType=VARCHAR},
		#{formerPersonNumber,jdbcType=VARCHAR},
		#{transferDate,jdbcType=BIGINT},
		#{pensionInsTransDirection,jdbcType=VARCHAR},
		#{transferType,jdbcType=VARCHAR},
		#{srcUnitName,jdbcType=VARCHAR},
		#{firstJoinlandPersonalPaytime,jdbcType=BIGINT},
		#{firstPersonalPaytime,jdbcType=BIGINT},
		#{personActCreateTime,jdbcType=BIGINT},
		#{paymentBeginTime,jdbcType=BIGINT},
		#{paymentEndTime,jdbcType=BIGINT},
		#{actualPaymentMonths,jdbcType=BIGINT},
		#{oldPersonalActSum,jdbcType=DOUBLE},
		#{theNewPersonalActSum,jdbcType=DOUBLE},
		#{transYearInPersonalAct,jdbcType=DOUBLE},
		#{personalActTransSum,jdbcType=DOUBLE},
		#{regionFundTransferSum,jdbcType=DOUBLE},
		#{transferFundSum,jdbcType=DOUBLE},
		#{repeatFees,jdbcType=DOUBLE},
		#{repeatFeeFlag,jdbcType=VARCHAR},
		#{giveupAddFee,jdbcType=VARCHAR},
		#{fundTransferBankNumber,jdbcType=VARCHAR},
		#{transferInAgencyName,jdbcType=VARCHAR},
		#{transferOutAgencyName,jdbcType=VARCHAR},
		#{nationAgencyId,jdbcType=BIGINT},
		#{transEventId,jdbcType=BIGINT},
		#{onlineDownload,jdbcType=VARCHAR},
		#{isConfirm,jdbcType=VARCHAR},
		#{uploadStatus,jdbcType=VARCHAR},
		#{uploadOperator,jdbcType=VARCHAR},
		#{uploadOperateTime,jdbcType=BIGINT},
		#{remark,jdbcType=VARCHAR},
		#{transBeforeEventId,jdbcType=BIGINT},
		#{contactPhone,jdbcType=VARCHAR},
		#{financeStatus,jdbcType=VARCHAR},
		#{aae011,jdbcType=VARCHAR},
		#{aae036,jdbcType=BIGINT},
		#{aab034,jdbcType=VARCHAR},
		#{aaa027,jdbcType=VARCHAR},
		#{bze011,jdbcType=VARCHAR},
		#{bze036,jdbcType=BIGINT},
		#{bze034,jdbcType=VARCHAR},
		#{pensionTransBillPK.sysTraceId,jdbcType=VARCHAR},
		#{pensionTransBillPK.srcAgencyState,jdbcType=VARCHAR},
		#{transferInfo.personId,jdbcType=BIGINT},
		#{transferInfo.personNumber,jdbcType=VARCHAR},
		#{transferInfo.socialEnsureNumber,jdbcType=VARCHAR},
		#{transferInfo.name,jdbcType=VARCHAR},
		#{transferInfo.sex,jdbcType=VARCHAR},
		#{transferInfo.birthday,jdbcType=BIGINT},
		#{transferInfo.migrantWorkers,jdbcType=VARCHAR},
		#{transferInfo.householdAddress,jdbcType=VARCHAR},
		#{transferInfo.transferAccountType,jdbcType=VARCHAR},
		#{transferInfo.workDate,jdbcType=BIGINT}
		)

	</insert>

喜欢我的文章希望和我一起成长的宝宝们，可以搜索并添加公众号TryTestwonderful ，或者扫描下方二维码添加公众号