MyBatis dynamic SQL

Dynamic SQL is the dynamic concatenation of SQL statements according to different conditions during business processing

Use of if statements A. if is usually used in WHERE statements. Test is used for parameter checking. If if meets the condition, and is automatically deleted

ParameterType ="map" resultType="user"> select * from user <where> <if test="name ! = null and name.length()>0" >and name=#{name}</if>Copy the code

B. Update cannot Update an existing value to a null or null set

<update id="updateByPrimaryKeySelective" parameterType="RecruitmentConfBanner"> UPDATE conf_banner t <set> <if test="bannerName ! = null"> t.banner_name = #{bannerName}, </if> </set> where t.banner_id = #{bannerId} </update>Copy the code

C. Use the if scenario in Insert: if the value passed is not null, it is passed; If it is null, the database defaults are used. Use if on both sides of values

<insert id="insert"> insert into sys_user( <if test="userEmail ! =null and userEmail ! = ''"> user_email, </if> ) VALUES ( <if test="userEmail ! = null and userEmail ! =''"> user_email = #{userEmail}, </if> </insert>Copy the code

Choose: implement if… Else where 1=1 does not return any results

<select id="findUserInfoByOneParam" parameterType="Map" resultMap="UserInfoResult">
        select * from userinfo
               where 1=1
        <choose>
            <when test="searchBy=='department'">
                and department=#{department}
            </when>
            <when test="searchBy=='position'">
                and position=#{position}
            </when>
            <otherwise>
                and 1=2
            </otherwise>
        </choose>
    </select>  
Copy the code

Where, set, trim usage

  • Where if a result is returned from where, the following string will start with AND OR, AND the AND/OR will be deleted automatically
< where > < if test = "name! = null and name.length()>0" >and name=#{name}</if>Copy the code
  • The last comma is removed if the set returns a result
<update> update user <set> <if test="name! = null and name.length()>0">name = #{name},</if> <if test="gender! = null and gender.length()>0">gender = #{gender},</if> </set> WHERE id = #{id} </update>Copy the code
  • The function of both the WHERE and set tags can be done with the trim tag
The realization of the function of the where < trim prefix = "where" prefixoverride = "AND | OR" > < if test = "name! = null and name.length()>0"> and name=#{name}</if> </trim> Where id = #{id} "> <if test="name! = null and name. The length () > 0 "> name = # {name}, < / if > < / trim >Copy the code

Foreach implements in collection scenario: select objects within a certain range of IDS. Here, the interface parameter is set Long[] idArray

<select id="dynamicForeachTest" parameterType="java.util.List" resultType="Blog">
 select * from t_blog where id in 
 <foreach collection="list" index="index" item="item" open="(" separator="," close=")"> 
 #{item} 
 </foreach> 
 </select>
Copy the code

Bind usage scenario: Create a variable and bind it to the context to solve the syntax problem caused by switching databases

<! -- List<Employee> getEmpsTestInnerParameter(Employee employee); --> <select id="getEmpsTestInnerParameter" resultType="com.hand.mybatis.bean.Employee"> <! -- bind: You can bind the value of an OGNL expression to a variable, Bind name="bindeName" value="'%'+eName+'%'" SELECT * FROM emp <if test="_parameter! =null"> where ename like #{bindeName} </if> </select>Copy the code