使用Mybatis框架时,涉及到的标签:
if标签 where标签 choose when otherwise标签 set标签 trim标签 bind标签 sql和include标签 foreach标签
1》if 标签
<if test="person != null and person != ‘‘"> AND rel.person=#{preson} </if>
2》where 标签
功能:自动的给Sql语句添加where关键字,并将第一个and去除。
SELECT name FROM Person
<where> <if test="personId!= null and personId!= ‘‘"> AND rel.personId = #{personId} </if> <if test="meetType == ‘1‘.toString() "> AND rel.state in (‘1‘,‘2‘) </if> <if test="meetType == ‘2‘.toString() "> AND base.proj_stat_cd in (303,307) </if> <if test="meetType == ‘3‘.toString() "> AND base.proj_stat_cd in (309,404) </if> </where>
数据库执行时:SELECT name FROM Person where rel.personId = ‘id‘ and rel.state in (‘1‘,‘2‘)
3> choose when otherwise
<select id="queryPersonInfo" resultType="com.cctv.Person"> SELECT * from LOG <where> <choose> <when test="param1!=null and param1!=‘‘"> and outno=#{param1} </when> <when test="param2!=null and param2!=‘‘"> and inno=#{param2} </when> <otherwise> and 1=1 </otherwise> </choose> </where> </select>
执行时,类似于java编译器解释执行
if(m == n){
}else if(m > n){
}else{
}
4>.set标签
自动去除最后一个逗号。
注意: 在判断条件中最后保持有一个永远成立的条件。避免sql错误。
<update id="updatePersonInfo"> UPDATE Person <set> <if test="name != null and name !=‘‘"> name = #{name}, </if> <if test="age !=null and age !=‘‘"> age =#{age}, </if> <if test="weight !=null and weight !=‘‘"> weight=#{weight}, </if> </set> WHERE licenseId=#{licenseId} </update>
数据库执行时:update person set name = "name", weight = "weight" WHERE licenseId = licenseId;
5>.trim 标签
prefix = "where" :匹配成功的第一个 if 判断里的 sql片段前拼接 where ;
prefixOverrides = "and":匹配成功的第一个if 判断里 sql 片段如果有 and ,将删除 and ;
suffix ,suffixOverrides 则是匹配最后一个;
顺序是 prefixOverrides -》prefix
<select id="selectUsersTrim" resultMap="resultListUsers" parameterType="Users"> select * from users <trim prefix="where" prefixOverrides="and"> <if test="name!=null"> and name=#{name} </if> <if test="address!=null"> and address=#{address} </if> </trim> </select>
链接:https://www.cnblogs.com/westward/p/6706570.html
6>.bing 标签
name : 参数名;
value :表达式,< bind name = "score" value = "20+score" >
或 <bind name = "hostId" value = "‘$‘ + hostId">
<update id="updPerson" parameterType="com.ccav.Person"> <bind name="score" value="score+100"/> update person <trim prefix="set" suffixOverrides=","> <if test="score !=null and score !=‘‘ "> score = #{score}, </if> </trim> where personId =#{personId} </update>
数据库执行时:score = 100, SQL = "update person score = 100 where personId = ‘id‘";
7>.foreach 标签
collection :要遍历的对象,类型:集合或数组;
item :每次遍历的集合元素;
open :在所拼接片段前拼接字符串;
separator :多次遍历后拼接的内容之间的分割符;
close :在所拼接片段最后追加字符串;
<if test=‘list != null and list.size() > 0‘> <foreach item="item" collection="list" separator="or" index="" open="(" close=")"> (meeting.actl_meet_time = #{item.meetingTime} AND mes.corp_cn_fn = #{item.companyName}) </foreach> </if>
执行时SQL片段:
(meeting.actl_meet_time = ‘time1‘ AND mes.corp_cn_fn = ‘name1‘)
or (meeting.actl_meet_time = ‘time2‘ AND mes.corp_cn_fn = ‘name2‘)