1.1 Select参数Map中带有List和普通参数，并且伴随>，<，=，like判断,<where>标签
<select id="queryCouponSortsList" parameterType="HashMap"
resultType="HashMap" resultMap="queryCouponSortsListMap">
SELECT
cpt.*
FROM
a_coupon_cpt cpt
JOIN a_coupon_ctur ctur ON cpt.cptSequence = ctur.cptSequence
<where>
<if test="null != customerList">AND ctur.pdCode in
<foreach collection="customerList" item="item" index="index" open="(" separator="," close=")">#{item}</foreach>
</if>
<if test="null != cptCode">AND cpt.cptCode like '%${cptCode}%'</if>
<if test="null != cptType">AND cpt.cptType = #{cptType}</if>
<if test="'N'.toString()== cptStatus" >
AND cpt.cptStatus = #{cptStatus}
<if test="null != cptEffectiveTime">AND cpt.cptEffectiveTime >= #{cptEffectiveTime}</if>
<if test="null != cptExpiryTime">AND #{cptExpiryTime} >= cpt.cptExpiryTime</if>
</if>
<if test="null == cptStatus">
AND cpt.cptStatus = 'N'
AND (cpt.cpmrType = 'R' OR cpt.cpmrType = 'A')
<if test="null != cptEffectiveTime">AND cpt.cptEffectiveTime >= #{cptEffectiveTime}</if>
<if test="null != cptExpiryTime">AND #{cptExpiryTime} >= cpt.cptExpiryTime</if>
</if>
<if test="null != ueabUserOpenID and null == unionID">AND ue.ueabUserOpenID = #{ueabUserOpenID}</if>
</where>
ORDER BY
cpt.cptSequence DESC
</select>
1.2 Select直接使用where(貌似不能使用'0'把数字包起来)
<select id="selectUserReceivedCouponList" parameterType="Map"
resultType="HashMap">
SELECT
cpmr.*
FROM
a_coupon_cpmr cpmr
INNER JOIN a_coupon_cpo cpo ON cpmr.cpmrSequence = cpo.cpmrSequence
WHERE
<!-- 可以使用优惠券 -->
<if test="cpStatus ==0">
cpo.expireTime >= #{currentTime}
<!-- AND cpo.customerSequence = #{customerSequence} -->
AND cpo.customerSequence = '2'
AND cpo.cpStatus = '0'
</if>
<!-- 已经使用优惠券 -->
<if test="cpStatus ==1">
<!-- AND cpo.customerSequence = #{customerSequence} -->
cpo.customerSequence = '2'
AND cpo.cpStatus = '1'
</if>
<!-- 过期优惠券 -->
<if test="cpStatus ==2">
#{currentTime} >cpo.expireTime
<!-- AND cpo.customerSequence = #{customerSequence} -->
AND cpo.customerSequence = '2'
AND cpo.cpStatus = '0'
</if>
</select>
1.3 Select如果在一个范围内就转化成ture，否则就是false
SELECT DISTINCT
pdCode,
pdName,
if(pdCode in (
<foreach collection="customerSelectedList" item="pdCode" index="index" separator=",">
#{pdCode}
</foreach>),'true','false') isSelected
FROM
a_pd
WHERE pmSequence = 10086
1.4 Select多个判断“或/且”关系
<select id="selectUserReceivedCouponList" parameterType="Map"
resultType="HashMap">
SELECT
cpmr.*
FROM
a_coupon_cpmr cpmr
WHERE
INNER JOIN a_coupon_cpo cpo ON cpmr.cpmrSequence = cpo.cpmrSequence
<if test='((pType == "0" || pType == "1") and (null == consumeType || consumeType == "1")) || ((pType == "0" || pType == "2") and (null == consumeType || consumeType == "2")) || ((pType == "0" || pType == "3") and (null == consumeType || consumeType == "3"))'>
cpt.cptStatus = 'N'
</if>
<if test='null != param1 and null != param2'>ORDER BY w.branchSequence, w.warnTime</if>
1.5 Select重用代码
<!--1. 定义好重用的代码 -->
<sql id="Base_Column_List" >name,age</sql>
<!-- 2.然后通过id引用 -->
<select id="selectAll">
SELECT
<include refid="Base_Column_List" />
FROM student
</select>
1.6 Select进行查找
<resultMap id="queryCouponSortsListMap" type="cn.smartcandy.application.a.commonbean.CouponType" >
<id property="cptSequence" column="cptSequence" />
<result property="cptExpiryTime" column="cptExpiryTime" />
<collection property="customerList" javaType="java.util.List" ofType="String">
<constructor><arg column="customerList"/></constructor>
</collection>
</resultMap>
<!-- 1、查询券种列表:（√） -->
<select id="queryCouponSortsList" parameterType="HashMap"
resultType="HashMap" resultMap="queryCouponSortsListMap">
SELECT
cpt.*
FROM
a_coupon_cpt cpt
JOIN a_coupon_ctur ctur ON cpt.cptSequence = ctur.cptSequence
<where>
<if test="null != customerList">AND ctur.pdCode in
<foreach collection="customerList" item="item" index="index" open="(" separator="," close=")">#{item}</foreach>
</if>
<if test="null != cptCode">AND cpt.cptCode like '%${cptCode}%'</if>
<if test="null != cptName">AND cpt.cptName like '%${cptName}%'</if>
<if test="null != cptType">AND cpt.cptType = #{cptType}</if>
<if test="null != cptAppliableBranch">AND cpt.cptAppliableBranch = #{cptAppliableBranch}</if>
<if test="null != limitPeriod">AND cpt.limitPeriod = #{limitPeriod}</if>
<if test="'N'.toString()== cptStatus" >
AND cpt.cptStatus = #{cptStatus}
<if test="null != cptEffectiveTime">AND cpt.cptEffectiveTime >= #{cptEffectiveTime}</if>
<if test="null != cptExpiryTime">AND #{cptExpiryTime} >= cpt.cptExpiryTime</if>
</if>
<if test="'D'.toString()== cptStatus" >
AND cpt.cptStatus = #{cptStatus}
<if test="null != cptEffectiveTime">AND cpt.cptEffectiveTime >= #{cptEffectiveTime}</if>
<if test="null != cptExpiryTime">AND #{cptExpiryTime} >= cpt.cptExpiryTime</if>
</if>
<if test="'O'.toString()== cptStatus">
AND cpt.cptStatus = 'N'
<if test="null != cptEffectiveTime">AND cpt.cptEffectiveTime >= #{cptEffectiveTime}</if>
<if test="null != currentTime">AND #{currentTime} >= cpt.cptExpiryTime</if>
</if>
<if test="null == cptStatus">
AND cpt.cptStatus = 'N'
<if test="null != cptEffectiveTime">AND cpt.cptEffectiveTime >= #{cptEffectiveTime}</if>
<if test="null != cptExpiryTime">AND #{cptExpiryTime} >= cpt.cptExpiryTime</if>
</if>
</where>
ORDER BY
cpt.cptSequence DESC
</select>
1.7 Select in (List集合)
<select id="findByIdsMap" resultMap="BaseResultMap">
Select
<include refid="Base_Column_List" />
from jria where ID in
<foreach item="item" index="index" collection="list" open="(" separator="," close=")">
#{item}
</foreach>
</select>
1.8 Select in (Array数组)
<select id="findByIdsMap" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from tabs where ID in
<foreach item="item" index="index" collection="array" open="(" separator="," close=")">
#{item}
</foreach>
</select>
2.1 Insert批零数据的导入(SQL)
<insert id="couponTypeBranchAdd" parameterType="java.util.List">
INSERT INTO a_coupon_ctbr(
cptSequence,
branchSequence,
cpmrSequence
)VALUES
<foreach collection="list" item="couponBranch" separator=",">
(
#{couponBranch.cptSequence},
#{couponBranch.branchSequence},
#{couponBranch.cpmrSequence}
)
</foreach>
</insert>
2.2 Insert批零数据的导入(JAVA)
public static void main(String[] args) {
int num = 598;//总数量
int batch = 200;//批量每次操作的个数
int count = num/batch + 1;//一定会操作的次数
int tempNum = 0;//当前操作的索引
System.out.println("请求的次数："+count);
for (int i = 0; i < count; i++) {
for (int j = 0; j < batch; j++) {
tempNum = i*batch+j;
if (tempNum<num) {
System.out.println("-------->"+tempNum);
}
}
System.out.println("已经成功打印100次");
}
}
3.1 Update更新语句使用trim去除“，”
<update id="couponBatchMakeCouponNum" parameterType="HashMap">
UPDATE a_coupon_cpmr
<trim prefix="set" suffixOverrides=",">
<if test="batch_no!=null">batch_no = #{batch_no},</if>
<if test="cpmrType!=null">cpmrType = #{cpmrType},</if>
</trim>
WHERE
cpmrSequence = #{cpmrSequence}
</update>
3.2 Update更新语句直接设置
<update id="couponTypeDelete" parameterType="HashMap">
UPDATE a_coupon_cpt
SET
cptStatus = 'D'
WHERE cptSequence = #{cptSequence}
</update>
4.Delete删除语句
<delete id="couponTypeCustomerDelete">
DELETE FROM
a_coupon_ctur
WHERE
cptSequence = #{cptSequence}
</delete>
