mybatis中动态SQL

客官°小女子只卖身不卖艺 2023-01-21 02:09 26阅读 0赞

## MyBatis的映射文件中支持在基础SQL上添加一些逻辑操作，并动态拼接成完整的SQL之后再执行，以达到SQL复用、简化编程的效果。 ##

### 1.<sql> ###

<mapper namespace="com.cos.qf.Dao.EmpMapper">
        
        <sql id="base_sql">
            eno,ename,esex,salary,pwd,dno,borndate
        </sql>
        <select id="getEmp" resultType="com.cos.qf.entity.Emp">
            select
            
            <include refid="base_sql"/>  
            from emp
        </select>
    </mapper>

### 2.<where> ###

<select id="findEmpByKey" resultType="com.cos.qf.entity.Emp">
            select
            <include refid="base_sql"></include>
            from emp
            <where>
                <if test="eno!=null and eno!=0">
                    and eno=#{eno}
                </if>
                <if test="ename!=null and ename!=''">
                    and ename=#{ename}
                </if>
                <if test="esex!=null and esex!=''">
                    and esex=#{esex}
                </if>
            </where>
        </select>

<select id="findEmpByKey2" resultType="com.cos.qf.entity.Emp">
            select
            <include refid="base_sql"></include>
            from emp
            <where>
                <if test="ssalary!=null and ssalary!=0">
                    and salary>=#{ssalary}
                </if>
                <if test="esalary!=null and esalary!=0">
                    <![CDATA[
                    and salary<=#{esalary}
                    ]]>
                </if>
            </where>
        </select>

### 3.<set> ###

<update id="updateEmpByKey">
            update emp
            <set>
                <if test="ename!=null">
                    ename=#{ename},
                </if>
                <if test="esex!=null">
                    esex=#{esex},
                </if>
            </set>
            where eno=#{eno}
        </update>

### 4.<trim> ###

< trim **prefix**="添加前缀" **suffix**="添加后缀"   **prefixOverrides**="自动忽略前缀" **suffixOverrides**="自动忽略后缀" >代替< where > 、< set >

<select id="findEmpByKey3" resultType="com.cos.qf.entity.Emp">
            select
            <include refid="base_sql"/>
            from emp
            <trim prefix="Where" prefixOverrides="And | OR" suffixOverrides="," suffix="order by salary desc">
                <if test="ssalary!=null and ssalary!=0">
                    and salary>=#{ssalary}
                </if>
                <if test="esalary!=null and esalary!=0">
                    <![CDATA[
                    and salary<=#{esalary}
                    ]]>
                </if>
            </trim>
        </select>

<update id="updateBookByCondition">
    		UPDATE t_books
    		<trim prefix="SET" suffixOverrides=","> 
    				<if test="name != null">
    						name = #{name} ,
    				</if>
    
    				<if test="author != null">
    						author = #{author} ,
    				</if>
    
    				<if test="publish != null">
    						publish = #{publish} ,
    				</if>
    
    				<if test="sort != null">
    						sort = #{sort}
    				</if>
        </trim>
    		WHERE id = #{id}
    </update>

### 5.<foreach> ###

<delete id="deleteBookByIds">
    		DELETE FROM t_books
    		WHERE id IN
    		<foreach collection="list" open="(" separator="," close=")"  item="id" index="i">
    				#{id}
    		</foreach>
    </delete>

<insert id="addEmps" >
            insert into emp values
            <foreach collection="list" separator="," item="e">
                (default,#{e.ename},#{e.esex},#{e.salary},#{e.pwd},#{e.dno},#{e.bornDate})
            </foreach>
        </insert>

<table> 
 <thead> 
  <tr> 
   <th>参数</th> 
   <th>描述</th> 
   <th>取值</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>collection</td> 
   <td>容器类型</td> 
   <td>list、array、map</td> 
  </tr> 
  <tr> 
   <td>open</td> 
   <td>起始符</td> 
   <td>(</td> 
  </tr> 
  <tr> 
   <td>close</td> 
   <td>结束符</td> 
   <td>)</td> 
  </tr> 
  <tr> 
   <td>separator</td> 
   <td>分隔符</td> 
   <td>,</td> 
  </tr> 
  <tr> 
   <td>index</td> 
   <td>下标号</td> 
   <td>从0开始，依次递增</td> 
  </tr> 
  <tr> 
   <td>item</td> 
   <td>当前项</td> 
   <td>任意名称（循环中通过 #{任意名称} 表达式访问）</td> 
  </tr> 
 </tbody> 
</table>