Mybatis 动态sql

最新推荐文章于 2018-10-10 21:41:17 发布

monk use rejoice

最新推荐文章于 2018-10-10 21:41:17 发布

阅读量162

点赞数

CC 4.0 BY-SA版权

分类专栏：框架

本文链接：https://blog.youkuaiyun.com/amazing_Code/article/details/82708835

框架专栏收录该内容

28 篇文章

订阅专栏

本文深入探讨MyBatis框架中动态SQL的实现原理，包括if、where、foreach、choose、when、otherwise、trim等标签的用法，以及如何通过WorkerCondition对象灵活地构建查询条件。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Worker类

public class Worker {

    private int id;
    private String name;
    private int age;
    private String sex;
    private Date birthday;
    private String address;

workerDao

public interface WorkerDao {

    //List<Worker> getWorkersByAge() throws Exception;
    //List<Worker> getWorkersByAge(Map<String,Object> map) throws Exception;

    List<Worker> getWorkersByAge(WorkerCondition workerCondition) throws Exception;

    /**
     * 根据姓名和年龄查询工人，如果没有传递参数则查询所有
     * @param workerCondition
     * @return
     * @throws Exception
     */
    List<Worker> getWorkersByNameAndAge(WorkerCondition workerCondition) throws Exception;

    /**
     * 根据姓名进行模糊查询
     * @param workerCondition
     * @return
     * @throws Exception
     */
    List<Worker> getWorkersLikeName(WorkerCondition workerCondition) throws Exception;


    List<Worker> selectWorkersByIds(WorkerCondition workerCondition) throws Exception;

    List<Worker> selectWorkersByMap(WorkerCondition workerCondition) throws Exception;

    List<Worker> selectWorkersByWhen(WorkerCondition workerCondition) throws Exception;

    List<Worker> getWorkersByTrim(WorkerCondition workerCondition) throws Exception;

}

workerDao.xml

<?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="com.hsj.dao.WorkerDao">

    <!-- 
    1.定义sql片段
     注意：定义sql片段时一定不要在里面写where标签，因为当前定义的sql片段可能会在其它的where标签中进行引用，会
     造成多个where关键字从而出现错误

     -->
    <sql id="sqlwhere">
            <if test="name!=null and name.length()>0">
                and name=#{name} 
            </if>

            <if test="age!=null and age!=0">
                and age=#{age}
            </if>
    </sql>
    <!-- 
    parameterType="map" 
     -->
    <select id="getWorkersByAge" parameterType="workerCondition"  resultType="worker">
        <!-- html实体集的使用：
        &lt;等价于<
        &gt;

        select * from t_worker where  age&gt;=18 and age&lt;=20
        select * from t_worker where  age&gt;=#{min} and age&lt;=#{max}
         -->
        select * from t_worker where  age&gt;=#{min} and age&lt;=#{max}
    </select>

    <sql id="NameAndAge">
        name,age
    </sql>
    <select id="getWorkersByNameAndAge" parameterType="workerCondition" resultType="worker">
        <!-- select * from t_worker where 1=1
        <if test="name!=null and name.length()>0">
            and name=#{name} 
        </if>

        <if test="age!=null and age!=0">
            and age=#{age}
        </if> -->


        <!-- select * from t_worker -->
        select 
            <include refid="NameAndAge"/>
         from t_worker
        <!-- 
        where标签的作用：
            A:当where子标签中有任何一个条件成立则会自动在select 字段列表 from  表名 后加追加where关键字
            B:可以自动删除第一个成立的条件前面的谓词(逻辑运算符)
         -->
        <!-- <where>
            <if test="name!=null and name.length()>0">
                and name=#{name} 
            </if>

            <if test="age!=null and age!=0">
                and age=#{age}
            </if>
        </where> -->

        <where>
            <!-- 2.引用已经定义的sql片段 -->
            <include refid="sqlwhere"/>

        </where>


    </select>


    <select id="getWorkersLikeName" parameterType="workerCondition" resultType="Worker">
        select * from t_worker
        <where>
            <if test="name!=null">
                and name like '%' #{name} '%'
            </if>
        </where>

    </select>

    <select id="selectWorkersByIds" parameterType="workerCondition" resultType="Worker">
        select * from t_worker 
        <where>
            <if test="ids!=null and ids.length>0">
                <!-- 
                collection="ids":指定 workerCondition对象中的集合或者数组属性
                item="id"：将集合中的每一个元素取出来分别赋值给id变量 
                open="id in ("：组拼字符串的开始部分的内容
                close=")":组拼字符串的结束部分的内容
                separator=","：指定每次变量元素后的分隔符

                id in (1,2,3,4,5)

                 -->
                <!-- <foreach collection="ids" item="id" open="id in (" close=")" separator=",">
                    #{id}
                </foreach> -->

                <!-- 
                    方式一：
                     and id=1 or id=2 or id=3 or id=4 
                 -->
                <!--  
                    方式二：
                     and id=1 or id=2 or id=3 or id=4 
                <foreach collection="ids" item="id" open="id=" close="" separator="or id=">
                    #{id}
                 </foreach> -->

                 <!-- 
                   方式三：
                  and id=1 or id=2 or id=3 or id=4 
                  -->
                 <foreach collection="ids" item="id" open="" close="" separator="or">
                    id=#{id}
                 </foreach>
             </if>
        </where>

    </select>

    <select id="selectWorkersByMap" parameterType="workerCondition" resultType="Worker">
        select * from t_worker 
        <where>
            <if test="map!=null">
                <if test="map.condition!=null">
                    and name like '%' #{map.condition} '%'
                </if>

                <if test="map.age!=null">
                    and age = #{map.age}
                </if>

            </if>
        </where>
    </select>

    <select id="selectWorkersByWhen" parameterType="workerCondition" resultType="Worker">
        select * from t_worker
        <where>
            <choose>
                <!-- 在choose ...when 表达式中， 如果第一个when的条件成立则不会再执行第二个when条件的判断了和执行了 -->
                <when test="name!=null">
                    and name like '%'  #{name} '%'
                </when>

                <when test="age!=0">
                    and age=#{age}
                </when>

                <!-- 
                当所有的when的测试都为false时才会执行otherwise中的内容
                otherwise必须放在所有的when的后面并且只能有一个otherwise
                 -->
                <otherwise>
                    and 1=1
                </otherwise>
            </choose>
        </where>
    </select>


    <select id="getWorkersByTrim" parameterType="workerCondition" resultType="Worker">
        select * from t_worker

        <!-- 

        select * from t_worker where name=? or age=?  

        prefix="where":自定义条件的前缀
        prefixOverrides="or":去掉前缀后面和当前属性值第一个相同的内容
        suffix="qq":指定后缀的内容
        suffixOverrides="or"：去掉后缀前面和当前属性值相同的最后一个内容
         -->
        <trim prefix="where" prefixOverrides="or"  suffixOverrides="or">
            or name=#{name}
            or
            age=#{age}
            or 
        </trim>
    </select>

</mapper>