Dynamic SQL(动态SQL)

来源：互联网发布：python from 上级目录编辑：程序博客网时间：2024/05/17 04:05

应用场景

有时候需要根据实际传入的参数来动态的拼接SQL语句

应用

在EmployeeMapper接口中添加一个方法：

public List<Employee> getEmployeeByConditionIf(Employee employee);

按需查询：

<select id="getEmployeeByConditionIf" resultType="com.neuedu.entity.Employee">            select *from tbl_employee             where              <!--                test:判断表达式(OGNL)                OGNL参照PPT或者官方文档。                    c:if test                从参数中取值进行判断                遇见特殊符号，应该去写转义字符(&)：参考W3CSchool>>HTML>>ISO8859              -->              <if test="id != null">                id = #{id}              </if>              <if test="userName != null &amp;&amp; userName !=''">                and user_name = #{userName}               </if>              <if test="email != null and email.trim() != &quot;&quot;">                and email = #{email}              </if>              <!-- ognl会进行字符串和数字的转换判断；"0"==0,"1"==1 -->              <if test="gender == 0 or gender == 1">                and gender = #{gender}               </if>        </select>

mybatis可以使用where标签来将所有的查询条件包括在内。mybatis就会将where标签中拼装的sql，多出来的and或者or去掉，但是where标签只会去掉第一个多出来的and或者or

使用trim标签

<select id="getEmployeeByConditionIf" resultType="com.neuedu.entity.Employee">        select *from tbl_employee        <!--             后面多出的and或者or where标签不能够解决           prefix="":前缀：trim标签体重是整个字符串拼串后的结果。                     prefix给拼串后的整个字符串加一个前缀           prefixOverrides="":                    前缀覆盖：去掉整个字符串前面多余的字符           suffix="":后缀             suffix给拼串后的整个字符串加一个后缀           suffixOverrides="":                 后缀覆盖：去掉整个字符串后面多余的字符         -->         <trim prefix="where" suffixOverrides="and">          <if test="id != null">            id = #{id} and          </if>          <if test="userName != null &amp;&amp; userName !=''">            user_name = #{userName} and            </if>          <if test="email != null and email.trim() != &quot;&quot;">            email = #{email} and           </if>          <!-- ognl会进行字符串和数字的转换判断；"0"==0,"1"==1 -->          <if test="gender==0 or gender==1">           gender = #{gender}          </if>             </trim>    </select>

choose标签
只会加载一个when标签

<select id="getEmployeeByConditionChoose" resultType="com.neuedu.entity.Employee">        select *from tbl_employee        <where>            <choose>                <when test="id != null">                    id = #{id}                </when>                <when test="userName != null">                    user_name like #{userName}                </when>                <when test="email != null">                   email = #{email}                </when>                <otherwise>                <!-- 查询全部-->                    1=1                </otherwise>            </choose>        </where>    </select>

update中的set标签【set标签可以将字段后面的逗号去掉】

 <update id="updateEmp">    update tbl_employee     <set>        <if test="userName != null">            user_name = #{userName},        </if>        <if test="email != null">            email = #{email},        </if>        <if test="gender != null">            gender = #{gender},        </if>    </set>    where id = #{id}</update>

trim标签代替set标签

 <update id="updateEmp">        update tbl_employee         <trim prefix="set" suffixOverrides=",">            <if test="userName != null">                user_name = #{userName},            </if>            <if test="email != null">                email = #{email},            </if>            <if test="gender != null">                gender = #{gender},            </if>        </trim>        where id = #{id}    </update>

foreach:遍历元素
批量查询
public List getEmpsByConditionForeach(@Param(“ids”) List ids);

 <select id="getEmpsByConditionForeach" resultType="com.neuedu.entity.Employee">        select * from  tbl_employee where id in        <!--             collection:指定要遍历的集合            item:将当前遍历出的元素赋值给指定的变量            separator:每个元素之间的分隔符            open:遍历出所有记过拼接一个开始的字符            close:遍历出所有结果拼接一个结束的字符         -->        <foreach collection="ids" open="(" close=")" separator="," item="id">            #{id}        </foreach>    </select>

批量插入数据

<insert id="addEmps">        INSERT INTO tbl_employee(user_name,gender,email,d_id) VALUES        <foreach collection="emps" item="emp" separator=",">            (#{emp.userName},#{emp.gender},#{emp.email},#{emp.depart.id})        </foreach>    </insert>

阅读全文

0 0