示例代码
1 建立sql查询与 实体之间的映射
<resultMap id="ResultMap" type="cn.com.sinosoft.club.model.db.club.model.PiccPolicyInfo" >
<id column="INSURANCE_SLIP_NO" property="insuranceSlipNo" jdbcType="VARCHAR" />
<result column="POLICY_NO" property="policyNo" jdbcType="VARCHAR" />
<result column="MEM_ID" property="memId" jdbcType="VARCHAR" />
<result column="MEM_NAME" property="memName" jdbcType="VARCHAR" />
<result column="MEM_ID_TYPE" property="memIdType" jdbcType="VARCHAR" />
<result column="MEM_ID_NO" property="memIdNo" jdbcType="VARCHAR" />
<result column="TYPE_CODE_ID" property="typeCodeId" jdbcType="VARCHAR" />
<result column="MEM_PHONE" property="memPhone" jdbcType="VARCHAR" />
<result column="PREMIUM" property="premium" jdbcType="DECIMAL" />
<result column="CLASS_CODE" property="classCode" jdbcType="VARCHAR" />
<result column="RISK_CODE" property="riskCode" jdbcType="VARCHAR" />
<result column="KIND_CODE" property="kindCode" jdbcType="VARCHAR" />
<result column="AMOUNT" property="amount" jdbcType="DECIMAL" />
<result column="AGENT_CODE" property="agentCode" jdbcType="VARCHAR" />
<result column="LASTDAMAGEDBI" property="lastdamagedbi" jdbcType="VARCHAR" />
<result column="USE_YEARS" property="useYears" jdbcType="VARCHAR" />
<result column="VEHICLE_USAGE" property="vehicleUsage" jdbcType="VARCHAR" />
<result column="COMCODE" property="comcode" jdbcType="VARCHAR" />
<result column="giftpackage_id" property="giftpackageId" jdbcType="VARCHAR" />
<result column="giftpackage_name" property="giftpackageName" jdbcType="VARCHAR" />
<result column="INSURED_VEHICLE_BRAND" property="insuredVehicleBrand" jdbcType="VARCHAR" />
<result column="RECOMMEND_CODE" property="recommendCode" jdbcType="VARCHAR" />
<result column="CHANNEL" property="channel" jdbcType="VARCHAR" />
<result column="EFFECTIVE_DATE" property="effectiveDate" jdbcType="TIMESTAMP" />
<result column="CREATED" property="created" jdbcType="TIMESTAMP" />
<result column="CREATED_BY" property="createdBy" jdbcType="VARCHAR" />
<result column="UPDATED" property="updated" jdbcType="TIMESTAMP" />
<result column="UPDATED_BY" property="updatedBy" jdbcType="VARCHAR" />
<result column="INSURE_ID" property="insureId" jdbcType="VARCHAR" />
<result column="POLICY_EXPIRATION_DATE" property="policyExpirationDate" jdbcType="TIMESTAMP" />
<result column="NET_PREMIUM" property="netPremium" jdbcType="DECIMAL" />
<result column="return_date" property="returnDate" jdbcType="TIMESTAMP" />
<association property="piccGiftPackage" column="dutyid" javaType="cn.com.sinosoft.club.model.db.club.model.PiccGiftPackage">
<id column="pg_GIFTPACKAGE_ID" property="giftpackageId" jdbcType="VARCHAR" />
<result column="pg_GIFTPACKAGE_NAME" property="giftpackageName" jdbcType="VARCHAR" />
<result column="pg_GIFTPACKAGE_REMARK" property="giftpackageRemark" jdbcType="VARCHAR" />
<result column="pg_TYPE_CODE_ID" property="typeCodeId" jdbcType="VARCHAR" />
<result column="pg_GIFTPACKAGE_ORG" property="giftpackageOrg" jdbcType="VARCHAR" />
<result column="pg_IS_EFFECTIVE" property="isEffective" jdbcType="VARCHAR" />
<result column="pg_PRIORITY" property="priority" jdbcType="VARCHAR" />
<result column="pg_CREATED" property="created" jdbcType="TIMESTAMP" />
<result column="pg_CREATED_BY" property="createdBy" jdbcType="VARCHAR" />
<result column="pg_UPDATED" property="updated" jdbcType="TIMESTAMP" />
<result column="pg_UPDATED_BY" property="updatedBy" jdbcType="VARCHAR" />
<result column="pg_STATUS" property="status" jdbcType="VARCHAR" />
<result column="pg_REPLACEABLE_NUM" property="replaceableNum" jdbcType="VARCHAR" />
<result column="pg_EXPIRATION_DATE" property="expirationDate" jdbcType="TIMESTAMP" />
</association>
<association property="piccInsureGiftPackage" column="dutyid" javaType="cn.com.sinosoft.club.model.db.club.model.PiccInsureGiftPackage">
<id column="pi_INSURE_ID" property="insureId" jdbcType="VARCHAR" />
<result column="pi_GIFTPACKAGE_ID" property="giftpackageId" jdbcType="VARCHAR" />
<result column="pi_TYPE_CODE_ID" property="typeCodeId" jdbcType="VARCHAR" />
<result column="pi_CREATED" property="created" jdbcType="TIMESTAMP" />
<result column="pi_CREATED_BY" property="createdBy" jdbcType="VARCHAR" />
<result column="pi_UPDATED" property="updated" jdbcType="TIMESTAMP" />
<result column="pi_UPDATED_BY" property="updatedBy" jdbcType="VARCHAR" />
<result column="pi_GIFTPACKAGE_NAME" property="giftpackageName" jdbcType="VARCHAR" />
<result column="pi_STATUS" property="status" jdbcType="VARCHAR" />
</association>
<collection property="piccInsureGiftPackageDetails" javaType="java.util.List" ofType="cn.com.sinosoft.club.model.db.club.model.PiccInsureGiftPackageDetail">
<id column="pig_INSURE_ITEM_ID" property="insureItemId" jdbcType="VARCHAR" />
<result column="pig_INSURE_ID" property="insureId" jdbcType="VARCHAR" />
<result column="pig_GIFTPACKAGE_ITEM_ID" property="giftpackageItemId" jdbcType="VARCHAR" />
<result column="pig_IS_SELECTED" property="isSelected" jdbcType="VARCHAR" />
<result column="pig_GIFT_NO" property="giftNo" jdbcType="VARCHAR" />
<result column="pig_GIFT_NAME" property="giftName" jdbcType="VARCHAR" />
<result column="pig_GIFT_NUM" property="giftNum" jdbcType="VARCHAR" />
<result column="pig_STATUS" property="status" jdbcType="VARCHAR" />
<result column="pig_ORDER_NO" property="orderNo" jdbcType="VARCHAR" />
<result column="pig_CREATED" property="created" jdbcType="TIMESTAMP" />
<result column="pig_CREATED_BY" property="createdBy" jdbcType="VARCHAR" />
<result column="pig_UPDATED" property="updated" jdbcType="TIMESTAMP" />
<result column="pig_UPDATED_BY" property="updatedBy" jdbcType="VARCHAR" />
<result column="pig_END_DATE" property="endDate" jdbcType="TIMESTAMP" />
</collection>
</resultMap>
<!-- by bs -->
<select id="selectByMap" resultMap="ResultMap" parameterType="Map" >
select
pp.*,
pg.GIFTPACKAGE_ID pg_GIFTPACKAGE_ID,
pg.GIFTPACKAGE_NAME pg_GIFTPACKAGE_NAME,
pg.GIFTPACKAGE_REMARK pg_GIFTPACKAGE_REMARK,
pg.TYPE_CODE_ID pg_TYPE_CODE_ID,
pg.GIFTPACKAGE_ORG pg_GIFTPACKAGE_ORG,
pg.IS_EFFECTIVE pg_IS_EFFECTIVE,
pg.PRIORITY pg_PRIORITY,
pg.CREATED pg_CREATED,
pg.CREATED_BY pg_CREATED_BY,
pg.UPDATED pg_UPDATED,
pg.UPDATED_BY pg_UPDATED_BY,
pg.STATUS pg_STATUS,
pg.REPLACEABLE_NUM pg_REPLACEABLE_NUM,
nvl(pp.return_date,nvl(pg.expiration_date,pp.policy_expiration_date)) pg_EXPIRATION_DATE,
pi.INSURE_ID pi_INSURE_ID,
pi.GIFTPACKAGE_ID pi_GIFTPACKAGE_ID,
pi.TYPE_CODE_ID pi_TYPE_CODE_ID,
pi.CREATED pi_CREATED,
pi.CREATED_BY pi_CREATED_BY,
pi.UPDATED pi_UPDATED,
pi.UPDATED_BY pi_UPDATED_BY,
pi.GIFTPACKAGE_NAME pi_GIFTPACKAGE_NAME,
pi.STATUS pi_STATUS,
pig.INSURE_ITEM_ID pig_INSURE_ITEM_ID,
pig.INSURE_ID pig_INSURE_ID,
pig.GIFTPACKAGE_ITEM_ID pig_GIFTPACKAGE_ITEM_ID,
pig.IS_SELECTED pig_IS_SELECTED,
pig.GIFT_NO pig_GIFT_NO,
pig.GIFT_NAME pig_GIFT_NAME,
pig.GIFT_NUM pig_GIFT_NUM,
pig.STATUS pig_STATUS,
pig.ORDER_NO pig_ORDER_NO,
pig.CREATED pig_CREATED,
pig.CREATED_BY pig_CREATED_BY,
pig.UPDATED pig_UPDATED,
pig.UPDATED_BY pig_UPDATED_BY,
pig.END_DATE pig_END_DATE
from PICC_POLICY_INFO pp join PICC_INSURE_GIFTPACKAGE pi on pp.INSURE_ID = pi.INSURE_ID
join PICC_GIFTPACKAGE pg on pi.GIFTPACKAGE_ID = pg.GIFTPACKAGE_ID
join PICC_INSURE_GIFTPACKAGE_DETAIL pig on pi.INSURE_ID = pig.INSURE_ID
where 1=1
<if test="memId != null">
and pp.MEM_ID = #{memId,jdbcType=VARCHAR}
</if>
<if test="insureId != null">
and pp.INSURE_ID = #{insureId,jdbcType=VARCHAR}
</if>
<if test="time != null">
<![CDATA[ and pp.EFFECTIVE_DATE <= #{time,jdbcType=TIMESTAMP}
and LEAST(nvl( pp.return_date,nvl(pg.expiration_date,pp.policy_expiration_date)),
nvl( nvl(pg.expiration_date,pp.policy_expiration_date),pp.return_date))
>= #{time,jdbcType=TIMESTAMP}
]]>
</if>
</select>
>= 问题还可以用
在 XML 中有 5 个预定义的实体引用:< < 小于
> > 大于
& & 和号
' ' 省略号
" " 引号
```在 XML 中有 5 个预定义的实体引用:< < 小于
> > 大于
& & 和号
' ' 省略号
" " 引号
3 批处理
<div class="se-preview-section-delimiter"></div>
这里写代码片
“`
<update id="updateByMapContainStrAndList" parameterType="map" >
update PICC_INSURE_GIFTPACKAGE_DETAIL p
set p.IS_SELECTED = 1,
p.UPDATED = sysdate,
p.UPDATED_BY = #{memId,jdbcType=VARCHAR},
p.STATUS = #{status,jdbcType=VARCHAR}
where INSURE_ID= #{insureId,jdbcType=VARCHAR} AND
INSURE_ITEM_ID in
<foreach collection="list" item="item" index="index" open="(" close=")" separator=",">
${item.insureItemId}
</foreach>
</update>