mybatis(三)

拼搏现实的明天。 2022-03-08 09:48 229阅读 0赞

# 动态SQL #

基于OGNL（对象图导航语言）表达式

完成多条件查询等逻辑实现

# 用于实现动态SQL的元素主要有 #

### if ###

<?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper
      PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
      "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="dao.EmpDaoMapper">
    
    	<select id="selectByNoMgr" resultType="bean.Emp">
    		select * from emp where 1=1
    		
    		<if test="empno != null">
    			and empno = #{empno}
    		</if>
    		<if test="mgr != null">
    			and mgr = #{mgr}
    		</if>
    	</select>
    </mapper>

### trim ###

<select id="selectByNoMgr" resultType="bean.Emp">
        select * from emp
        
        
        <trim prefix="where" prefixOverrides="and">
            <if test="empno != null">
                and empno = #{empno}
            </if>
            <if test="mgr != null">
                and mgr = #{mgr}
            </if>
        </trim>
    </select>

<update id="update">
        update emp set 
            
            
            <trim suffixOverrides=",">
                <if test="ename != null and ename != '' ">
                    ename = #{ename},
                </if>
                    <if test="job != null and job != '' ">
                    job = #{job},
                </if>
            </trim>
        where empno = #{empno}
    </update>

### where ###

<select id="selectByNoMgr" resultType="bean.Emp">
        select * from emp
        
        
        <where>
            <if test="empno != null">
                empno = #{empno}
            </if>
            <if test="mgr != null">
                mgr = #{mgr}
            </if>
        </where>
    </select>

### set ###

<update id="update">
        update emp
        
        
            <set>
                <if test="ename != null and ename != '' ">
                    ename = #{ename},
                </if>
                <if test="job != null and job != '' ">
                    job = #{job},
                </if>
            </set>
        where empno = #{empno}
    </update>

### choose(when、otherwise) ###

<select id="selectByNoMgr" resultType="bean.Emp">
        select * from emp where 1=1
        
        <choose>
            <when test="empno != null">
                and empno = #{empno}
            </when>
            <when test="mgr != null">
                and mgr = #{mgr}
            </when>
            <otherwise>
                and empno = 1
            </otherwise>
        </choose>
    </select>

### foreach ###

<select id="selectIn" resultType="bean.Emp">
      	select * from emp where
       
       
       
       
       
      	<foreach collection="list" item="no" open="empno in (" close=")" separator=",">
            #{no}
      	</foreach>
       
       
       
    </selet>