MyBatis_动态sql

最新推荐文章于 2023-08-13 15:26:19 发布

qq_36901488

最新推荐文章于 2023-08-13 15:26:19 发布

阅读量175

点赞数

分类专栏： MyBatis学习增删查改 SQL语句

本文链接：https://blog.csdn.net/qq_36901488/article/details/80625688

版权

MyBatis学习同时被 3 个专栏收录

32 篇文章 1 订阅

订阅专栏

增删查改

7 篇文章 0 订阅

订阅专栏

SQL语句

3 篇文章 0 订阅

订阅专栏

使用过JSTL或任何类似的基于XML的文本处理器的人都应该熟悉动态SQL元素。在MyBatis之前的版本中，有很多元素需要了解和理解。MyBatis 3在这方面有了很大的改进，现在只有不到一半的元素需要处理。MyBatis使用强大的OGNL表达式来消除大部分其他元素:MyBatis使用强大的OGNL表达式来消除大部分其他元素:

OGNL（ Object Graph Navigation Language ）对象图导航语言，这是一种强大的表达式语言，通过它可以非常方便的来操作对象属性。类似于我们的EL，SpEL等
访问对象属性:person.name
调用方法:person.getName()
调用静态属性/方法:@java.lang.Math@PI,@java.util.UUID@randomUUID()
调用构造方法:new com.atguigu.bean.Person(‘admin’).name
运算符:+,-*,/,%
逻辑运算符:in,not in,>,>=,<,<=,==,!=
注意：xml中特殊符号如”,>,<等这些都需要使用转义字符

if:判断

假设我们现在查询员工，要求，携带了哪个字段查询条件就带上这个字段的值
新建一个新的接口EmployeeMapperDynamicSQL.java写方法:

public List<Employee> getEmpsByConditionIf(Employee employee);

然后新建一个与之对应的EmployeeMapperDynamicSQL.xml映射文件

<select id="getEmpsByConditionIf" resultType="com.atguigu.mybatis.bean.Employee">
        select * from tbl_employee
            <!-- test：判断表达式（OGNL）
                 c:if  test
            从参数中取值进行判断
            遇见特殊符号应该去写转义字符：
            -->
            <if test="id!=null">
                id=#{id}
            </if>
            <!-- &amp;&amp; 代表&&,是转义字符 -->
            <if test="lastName!=null &amp;&amp; lastName!=&quot;&quot;">
                and last_name like #{lastName}
            </if>
            <!-- &quot;&quot; 代表"",是转义字符 -->
            <if test="email!=null and email.trim()!=&quot;&quot;">
                and email=#{email}
            </if> 
            <!-- ognl会进行字符串与数字的转换判断  像这样的"0"==0是可以的 -->
            <if test="gender==0 or gender==1">
                and gender=#{gender}
            </if>
     </select>

测试一下:

    @Test
    public void testDynamicSql() throws IOException{
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession();
        try{
            EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
            //可以看到查询语句是这样的:select * from tbl_employee where id=? and last_name like ?
            //因为我们下面写sql语句时只带了 id 和 lastName ,所以查询语句中只有 id 和 last_name 
            //测试if\where
            Employee employee = new Employee(1, "%e%", null, null);
            List<Employee> emps = mapper.getEmpsByConditionIf(employee );
            for (Employee emp : emps) {
                System.out.println(emp);
            }
        }finally{
            openSession.close();
        }
    }

where:封装查询条件

注意:查询的时候如果某些条件没带可能sql拼装会有问题
比如下面查询语句没有带id:
Employee employee = new Employee(null, "Admin", null, null);
这样就会报错了,这时候发送的SQL语句是这样的:select * from tbl_employee where and last_name like ?可以看到它硬生生把and条件拼装上去了…..
解决办法:使用where标签来将所有的查询条件包括在内。mybatis就会将where标签中拼装的sql多出来的and或者or去掉
像这样:

<where>
    <if test="id!=null">
        id=#{id}
    </if>
    <!-- 后面如果还有if也要写进来 -->
</where>

加上之后查询时发送的SQL语句就不会出错了:select * from tbl_employee WHERE last_name like ?
但是:where只会去掉第一个多出来的and或者or:
这里写图片描述
还是这个查询语句:
Employee employee = new Employee(null, "Admin", null, null);
如果你把and写在后面了,还是会报错的,这时的SQL语句是这样的select * from tbl_employee WHERE last_name like ? and
解决办法:
and写在前面,哈哈哈!

trim:自定义字符串截取

上面的后面多出的and或者or用where标签不能解决,这时可以用trim标签的 suffixOverrides解决
在EmployeeMapperDynamicSQL.java中写方法:

public List<Employee> getEmpsByConditionTrim(Employee employee);

然后在EmployeeMapperDynamicSQL.xml中实现:

trim
- prefix=”“:前缀：trim标签体中是整个字符串拼串后的结果。prefix的作用就是给拼串后的整个字符串加一个前缀
- prefixOverrides=”“:前缀覆盖：作用就是去掉整个字符串前面多余的字符
- suffix=”“:后缀.suffix给拼串后的整个字符串加一个后缀
- suffixOverrides=”“:后缀覆盖：去掉整个字符串后面多余的字符

<select id="getEmpsByConditionTrim" resultType="com.atguigu.mybatis.bean.Employee">
        select * from tbl_employee
        <!-- 自定义字符串的截取规则 -->
        <trim prefix="where" suffixOverrides="and">
            <if test="id!=null">
                id=#{id} and
            </if>
            <if test="lastName!=null &amp;&amp; lastName!=&quot;&quot;">
                last_name like #{lastName} and
            </if>
            <if test="email!=null and email.trim()!=&quot;&quot;">
                email=#{email} and
            </if> 
            <!-- ognl会进行字符串与数字的转换判断  "0"==0 -->
            <if test="gender==0 or gender==1">
                gender=#{gender}
            </if>
         </trim>
     </select>

测试:

    @Test
    public void testDynamicSql() throws IOException{
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession();
        try{
            EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
            Employee employee = new Employee(null, "%e%", null, null);
            List<Employee> emps2 = mapper.getEmpsByConditionTrim(employee);
            for (Employee emp : emps2) {
                System.out.println(emp);
            }
        }finally{
            openSession.close();
        }
    }

这样就可以在id没有带的情况下正确的去掉后面的and了

choose(when, otherwise):分支选择

跟swtich-case差不多
假设我们现在要查询员工,如果带了id就用id查,如果带了lastName就用lastName查,而且只用一个查
在EmployeeMapperDynamicSQL.java中写方法:

public List<Employee> getEmpsByConditionChoose(Employee employee);

然后在EmployeeMapperDynamicSQL.xml中实现:

<select id="getEmpsByConditionChoose" resultType="com.atguigu.mybatis.bean.Employee">
        select * from tbl_employee 
        <where>
            <!-- 如果带了id就用id查，如果带了lastName就用lastName查;只会进入其中一个 -->
            <choose>
                <when test="id!=null">
                    id=#{id}
                </when>
                <when test="lastName!=null">
                    last_name like #{lastName}
                </when>
                <when test="email!=null">
                    email = #{email}
                </when>
                <!-- 当前面的when都不满足时进入otherwise -->
                <otherwise>
                    gender = 0
                </otherwise>
            </choose>
        </where>
     </select>

测试:

    @Test
    public void testDynamicSql() throws IOException{
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession();
        try{
            EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
            Employee employee = new Employee(null, "%e%", null, null);
            List<Employee> list = mapper.getEmpsByConditionChoose(employee);
            for (Employee emp : list) {
                System.out.println(emp);
            }
        }finally{
            openSession.close();
        }
    }

结果:
当传入参数是Employee employee = new Employee(null, "%e%", null, null);时
发出的SQL语句是:select * from tbl_employee WHERE last_name like ?

当传入参数是Employee employee = new Employee(1, "%e%", null, null);时
发出的SQL语句是:select * from tbl_employee WHERE id = ?

当传入参数是Employee employee = new Employee(null, null, null, null);时
发出的SQL语句是:select * from tbl_employee WHERE gender = 0

set:封装修改条件

现在我们想实现的是我们SQL语句带了哪个条件就修改员工的哪个属性
在EmployeeMapperDynamicSQL.java中写方法:

public void updateEmp(Employee employee);

然后在EmployeeMapperDynamicSQL.xml中实现:

<update id="updateEmp">
        <!-- Set标签的使用 -->
        update tbl_employee 
        set
            <if test="lastName!=null">
                last_name=#{lastName},
            </if>
            <if test="email!=null">
                email=#{email},
            </if>
            <if test="gender!=null">
                gender=#{gender}
            </if>
        where id=#{id} 
     </update>

测试:

    @Test
    public void testDynamicSql() throws IOException{
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession();
        try{
            EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
            Employee employee = new Employee(1, "Admin", null, null);
            mapper.updateEmp(employee);
            openSession.commit();
        }finally{
            openSession.close();
        }
    }

这时测试是不可以正常修改的因为SQL语句报错了:update tbl_employee set last_name=?, where id=?
可以看到这时的SQL语句last_name=?后面多了个,
这时我们把<set>标签加进去,就可以正常去掉了

    <update id="updateEmp">
        <!-- Set标签的使用 -->
        update tbl_employee 
        <set>
            <if test="lastName!=null">
                last_name=#{lastName},
            </if>
            <if test="email!=null">
                email=#{email},
            </if>
            <if test="gender!=null">
                gender=#{gender}
            </if>
        </set>
        where id=#{id} 
     </update>

可以想到这里用前面学过的<trim>标签也是可以解决的

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

foreach:遍历集合

在EmployeeMapperDynamicSQL.java中写方法:

    //查询员工id'在给定集合中的
    public List<Employee> getEmpsByConditionForeach(@Param("ids")List<Integer> ids);

然后在EmployeeMapperDynamicSQL.xml中实现方法:

 <select id="getEmpsByConditionForeach" resultType="com.atguigu.mybatis.bean.Employee">
        select * from tbl_employee
        <!--
            collection：指定要遍历的集合：
                list类型的参数会特殊处理封装在map中，map的key就叫list
            item：将当前遍历出的元素赋值给指定的变量
            separator:每个元素之间的分隔符
            open：遍历出所有结果拼接一个开始的字符
            close:遍历出所有结果拼接一个结束的字符
            index:索引。遍历list的时候是index就是索引，item就是当前值
                          遍历map的时候index表示的就是map的key，item就是map的值

            #{变量名}就能取出变量的值也就是当前遍历出的元素
          -->
        <foreach collection="ids" item="item_id" separator=","
            open="where id in(" close=")">
            #{item_id}
        </foreach>
     </select>

测试:

    @Test
    public void testDynamicSql() throws IOException{
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession();
        try{
            EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
            List<Employee> list = mapper.getEmpsByConditionForeach(Arrays.asList(1,2));
            for (Employee emp : list) {
                System.out.println(emp);
            }
        }finally{
            openSession.close();
        }
    }

可以看到结果所发的SQL语句:select * from tbl_employee where id in ( ? , ? )是正确的

<foreach>标签也经常用于批量操作(比如批量增删查改等)
在EmployeeMapperDynamicSQL.java中写方法:

public void addEmps(@Param("emps")List<Employee> emps);

然后在EmployeeMapperDynamicSQL.xml中实现方法:

    <!--MySQL下批量保存：可以foreach遍历   mysql支持values(),(),()语法-->
    <insert id="addEmps">
        insert into tbl_employee(last_name,email,gender,d_id)
        values
        <foreach collection="emps" item="emp" separator=",">
            (#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})
        </foreach>
     </insert>

测试:

    @Test
    public void testBatchSave() throws IOException{
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        SqlSession openSession = sqlSessionFactory.openSession();
        try{
            EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
            List<Employee> emps = new ArrayList<>();
            emps.add(new Employee(null, "smith", "smith@atguigu.com", "1",new Department(1)));
            emps.add(new Employee(null, "allen", "allen@atguigu.com", "0",new Department(1)));
            mapper.addEmps(emps);
            openSession.commit();
        }finally{
            openSession.close();
        }
    }

以上这种values(),(),()语法方式在oracle中是不支持的….

Oracle支持的批量方式

多个insert放在begin - end里面

    <!-- 
    begin
        insert into employees(employee_id,last_name,email) 
        values(employees_seq.nextval,'test_001','test_001@atguigu.com');
        insert into employees(employee_id,last_name,email) 
        values(employees_seq.nextval,'test_002','test_002@atguigu.com');
    end; 
    -->
<insert id="addEmps" databaseId="oracle">
    <!-- oracle第一种批量方式 -->
    <foreach collection="emps" item="emp" open="begin" close="end;">
        insert into employees(employee_id,last_name,email) 
            values(employees_seq.nextval,#{emp.lastName},#{emp.email});
    </foreach>
 </insert>

利用中间表

    <!-- 
    insert into employees(employee_id,last_name,email)
           select employees_seq.nextval,lastName,email from(
                  select 'test_a_01' lastName,'test_a_e01' email from dual
                  union
                  select 'test_a_02' lastName,'test_a_e02' email from dual
                  union
                  select 'test_a_03' lastName,'test_a_e03' email from dual
           )
    -->
    <insert id="addEmps" databaseId="oracle">
        <!-- oracle第二种批量方式  -->
        insert into employees(
            <!-- 引用外部定义的sql -->
            <include refid="insertColumn">
                <property name="testColomn" value="abc"/>
            </include>
        )
                <foreach collection="emps" item="emp" separator="union"
                    open="select employees_seq.nextval,lastName,email from("
                    close=")">
                    select #{emp.lastName} lastName,#{emp.email} email from dual
                </foreach>
     </insert>