Mybatis中常见的SQL DML
1、sql select 查询
<select id="query" resultType="CfCurrbusilogEntity" >
select
cb.bizid bizid,
cb.phone phone,
cb.ispno ispno,
cb.ipstype ipstype,
cb.province province,
cb.facevalue facevalue,
cb.orderid orderid,
cb.cporderno cporderno,
cb.systransno systransno,
cb.responsecode responsecode,
cb.spprice spprice,
cb.spno spno,
cb.apprice apprice,
cb.apno apno,
cb.createtime createtime,
cb.updatetime updatetime,
cb.status status,
ac.acname acname,
c.cname cname,
p.pname pname
from
CF_CURRBUSILOG CB
LEFT JOIN CF_ACCESSCLIENT AC
ON CB.APNO = AC.ID
LEFT JOIN CF_CUSTOMER C
ON AC.CID = C.ID
LEFT JOIN CF_PROVINCE P
ON CB.PROVINCE = P.PNO
WHERE
(
1 =1
and cb.createtime between REPLACE(#{param1.begintime},'-','') AND CONCAT(REPLACE(#{param1.endtime},'-',''),'99')
<!--
<if test="param1.begintime!='' and param1.begintime!=null and param1.endtime!='' and param1.endtime!=null">
and cb.createtime between #{param1.begintime} and #{param1.endtime}
</if> -->
<if test="param1.cid!='' and param1.cid!=null">
and c.id = #{param1.cid}
</if>
<if test="param1.acid!='' and param1.acid!=null">
and ac.id = #{param1.acid}
</if>
<if test="param1.ispno!='' and param1.ispno!=null">
and cb.ispno = #{param1.ispno}
</if>
<if test="param1.province!='' and param1.province!=null">
and cb.province = #{param1.province}
</if>
<if test="param1.ipstype!='' and param1.ipstype!=null">
and cb.ipstype = #{param1.ipstype}
</if>
<if test="param1.status!='' and param1.status!=null">
<if test='param1.status == "4" '>
and cb.status in( '3', '4')
</if>
<if test='param1.status != "4" '>
and cb.status = #{param1.status}
</if>
</if>
<if test="param1.facevalue!='' and param1.facevalue!=null">
and cb.facevalue like CONCAT('%',#{param1.facevalue},'%' )
</if>
<if test="param1.systransno!='' and param1.systransno!=null">
and cb.systransno = #{param1.systransno}
</if>
<if test="param1.orderid!='' and param1.orderid!=null">
and cb.orderid = #{param1.orderid}
</if>
<if test="param1.phone!='' and param1.phone!=null">
and cb.phone like CONCAT('%',#{param1.phone},'%' )
</if>
)
order by cb.updatetime desc
limit #{start},#{length}
</select>
2、sql insert
1 <!-- 插入数据 -->
2 <insert id="insertBlack" parameterType="BlacklistEntity">
3 INSERT INTO BLACKLIST
4 (
5 BLACKID,
6 BLACKTYPE,
7 BLACKVALUE,
8 ACTIONID,
9 REMARK,
10 OVERTIME,
11 STATUS
12 )
13 VALUES
14 (
15 #{blackid},
16 #{blacktype},
17 #{blackvalue},
18 #{actionid},
19 #{remark},
20 #{overtime},
21 #{status}
22 )
23 </insert>
3、sql update
1 <!-- 修改数据 -->
2 <update id="updateblack" parameterType="BlacklistEntity">
3 UPDATE
4 BLACKLIST
5 <set>
6 <if test="overtime!='' and overtime!=null">
7 OVERTIME = #{overtime},
8 </if>
9 <if test="blacktype!='' and blacktype!=null">
10 BLACKTYPE = #{blacktype},
11 </if>
12 <if test="blackvalue!='' and blackvalue!=null">
13 BLACKVALUE = #{blackvalue},
14 </if>
15 <if test="actionid!='' and actionid!=null">
16 ACTIONID = #{actionid},
17 </if>
18 <if test="status!='' and status!=null">
19 STATUS = #{status},
20 </if>
21 <if test="remark!='' and remark!=null">
22 REMARK = #{remark}
23 </if>
24 </set>
25 WHERE
26 BLACKID = #{blackid}
27 </update>
4、sql delete
1 <!-- 删除数据 -->
2 <delete id="deleteblack" parameterType="String">
3 DELETE FROM
4 BLACKLIST
5 WHERE
6 BLACKID=#{blackid}
7 </delete>
