Mybatis:自定义SQL语句涉及到的标签

使用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‘)

  

Mybatis:自定义SQL语句涉及到的标签

上一篇:net stop mysql 服务名无效


下一篇:PGSQL invalid primary checkpoint record 的修复