MyBatis-动态SQL | 乐乐哥

数据库

Publish Date: 2020-08-02

Update Date: 2020-08-02

Word Count: 3.1k

Read Times: 15 Min

Read Count:

一、简介&环境搭建

1.1简介

MyBatis最强大的特性之一就是它的动态SQL功能。 如果您有使用JDBC或任何类似框架的经验，您就会理解有条件地将SQL字符串连接在一起是多么痛苦，请确保不要忘记空格或忽略列列表末尾的逗号。处理动态SQL可能非常痛苦。虽然使用动态SQL永远不会成为问题，但是MyBatis使用了一种强大的动态SQL语言，可以在任何映射的SQL语句中使用。
在MyBatis的早期版本中，有很多元素需要了解和理解。MyBatis 3大大改进了这一点，现在可以使用的元素还不到一半。MyBatis使用强大的基于OGNL的表达式来消除大部分其他元素:
• if
• choose (when, otherwise)
• trim (where, set)
• foreach

2.环境搭建

编写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.atorg.mybatis.dao.EmployeeMapperDynamicSQl">
    <!--namespace的值为接口的全类名-->
    <!-- •    if
         •    choose (when, otherwise):分支选择;带了break的switch-case
                 如果带了id就用id查，带了lastname就用lastName查
         •    trim (where(封装查询条件), set(封装修改条件))
         •    foreach
     -->
  </mapper>

二、if判断

条件：查询员工，携带了哪个字段查询条件就带上这个字段的值
接口编写

package com.atorg.mybatis.dao;

import java.util.List;

import org.apache.ibatis.annotations.Param;

import com.atorg.mybatis.bean.Employee;

public interface EmployeeMapperDynamicSQl {
    //携带哪个字段查询条件就带上这个字段
    public List<Employee> getEmpsTestInnerParameter(Employee employee);
    }

接口实现编写

<?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.atorg.mybatis.dao.EmployeeMapperDynamicSQl">
      <!-- public List<Employee> getEmpsByConditionIf(Employee employee); -->
      <select id="getEmpsByConditionIf" resultType="emp">
          select * from tab1_employee
          where
          <!--test:判断表达式-->
          <!--test:判断表达式（OGNL）
              从参数中取值进行判断
              遇见特殊符号应该去写准转义字符
                -->
              <if test="id!=null"><!--判断id不为空是拼接到sql语句中-->
                  id=#{id}
              </if>
              <if test="lastName!=null &amp;&amp; lastName!=&quot;&quot;"><!--如上和id判断一致-->
                  and last_name like #{lastName}
              </if>
              <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>

提示：遇见特殊符号应该去写转义字符
测试程序

package com.atorg.mybatis.test;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import com.atorg.mybatis.bean.Department;
import com.atorg.mybatis.bean.Employee;
import com.atorg.mybatis.dao.DepartmentMapper;
import com.atorg.mybatis.dao.EmployeeMapper;
import com.atorg.mybatis.dao.EmployeeMapperAnnotation;
import com.atorg.mybatis.dao.EmployeeMapperDynamicSQl;
import com.atorg.mybatis.dao.EmployeeMapperPlus;

public class MyBatisTest {
    public SqlSessionFactory getSqlSessionFactory() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        return new SqlSessionFactoryBuilder().build(inputStream);
    }
    @Test
    public void testDynamicSQl() throws IOException {
        // 获取sqlSessionFactory对象
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        // 获取sqlSession对象不会自动提交数据
        SqlSession openSession = sqlSessionFactory.openSession();
        // 获取接口的实现类对象
        try {
            EmployeeMapperDynamicSQl mapper = openSession.getMapper(EmployeeMapperDynamicSQl.class);
            // 测试if/where
            Employee employee = new Employee(1,"Admin",null,null);
            List<Employee> employees=mapper.getEmpsByConditionIf(employee);
            for (Employee emp : employees) {
            System.out.println(emp);
            }
        } finally {
            openSession.close();
        }
    }

测试结果
测试类中给出的参数为 id和 name
执行结果
结论： 与测试程序给出的参数一致
讨论
==当实现接口的程序动态sql中的第一个参数不合法时，sql语句为
select * from tab1_employee where and last_name=#{LastName}== 这时就出现了问题，where条件后直接出现一个and，此时执行出现语法错误

解决办法
查询的时候如果某些条件没带可能sql拼装会有问题
1.给where后面加上1=1，以后的条件都and xxx

<?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.atorg.mybatis.dao.EmployeeMapperDynamicSQl">
      <!-- public List<Employee> getEmpsByConditionIf(Employee employee); -->
      <select id="getEmpsByConditionIf" resultType="emp">
          select * from tab1_employee
          where 1=1
          <!--test:判断表达式-->
          <!--test:判断表达式（OGNL）
              从参数中取值进行判断
              遇见特殊符号应该去写准转义字符
                -->
              <if test="id!=null"><!--判断id不为空是拼接到sql语句中-->
                  and id=#{id}
              </if>
              <if test="lastName!=null &amp;&amp; lastName!=&quot;&quot;"><!--如上和id判断一致-->
                  and last_name like #{lastName}
              </if>
              <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>

此时执行
执行成功
2.mybatis推荐使用where标签来将所有的查询条件包括在内，mybatis就会where标签中拼装sql，多出来的and或者or去掉

<?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.atorg.mybatis.dao.EmployeeMapperDynamicSQl">
      <!-- public List<Employee> getEmpsByConditionIf(Employee employee); -->
      <select id="getEmpsByConditionIf" resultType="emp">
          select * from tab1_employee
          <where>
          <!--test:判断表达式-->
          <!--test:判断表达式（OGNL）
              从参数中取值进行判断
              遇见特殊符号应该去写准转义字符
                -->
              <if test="id!=null"><!--判断id不为空是拼接到sql语句中-->
                  id=#{id}
              </if>
              <if test="lastName!=null &amp;&amp; lastName!=&quot;&quot;"><!--如上和id判断一致-->
                  and last_name like #{lastName}
              </if>
              <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>
              </where>
      </select>

此时执行
执行成功
讨论
==当我们的sql语句中的and出现在条件后边的时候，当倒数第一个参数为null时select * from tab1_employee WHERE last_name like ? and== 此时最后多出一个and，出现语法错误
测试
sql语句出现语法错误
结论：==where标签只会去掉第一个多出的and或者or== 此时就引出了另一个标签trim

三、trim标签

编写接口

package com.atorg.mybatis.dao;

import java.util.List;
import org.apache.ibatis.annotations.Param;
import com.atorg.mybatis.bean.Employee;

public interface EmployeeMapperDynamicSQl {
    public List<Employee> getEmpsByConditionTrim(Employee employee);
    }

编写接口实现

<?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.atorg.mybatis.dao.EmployeeMapperDynamicSQl">
      <!-- public List<Employee> getEmpsByConditionTrim(Employee employee); -->
      <select id="getEmpsByConditionTrim" resultType="emp">
          select * from tab1_employee
          <!-- 后面多出的and或者or  where标签不能解决
           prefix="":前缀，trim标签体是整个字符串拼串后的结果
                       prefix给拼串后的整个字符串加一个前缀
           prefixOverrides="" :前缀覆盖，去掉整个字符串前面多余的字符
           suffix="" 后缀
                       suffix给拼串后的整个字符串加一个后缀
           suffixOverrides=""
                       后缀覆盖：去掉整个字符串后面多余的字符-->
           <!-- trim自定义字符串截取规则:只要满足条件都拼串 -->
          <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>
</mapper>

测试程序

package com.atorg.mybatis.test;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import com.atorg.mybatis.bean.Department;
import com.atorg.mybatis.bean.Employee;
import com.atorg.mybatis.dao.DepartmentMapper;
import com.atorg.mybatis.dao.EmployeeMapper;
import com.atorg.mybatis.dao.EmployeeMapperAnnotation;
import com.atorg.mybatis.dao.EmployeeMapperDynamicSQl;
import com.atorg.mybatis.dao.EmployeeMapperPlus;

public class MyBatisTest {
    public SqlSessionFactory getSqlSessionFactory() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        return new SqlSessionFactoryBuilder().build(inputStream);
    }
    @Test
    public void testDynamicSQl() throws IOException {
        // 获取sqlSessionFactory对象
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        // 获取sqlSession对象不会自动提交数据
        SqlSession openSession = sqlSessionFactory.openSession();
        // 获取接口的实现类对象
        try {
            EmployeeMapperDynamicSQl mapper = openSession.getMapper(EmployeeMapperDynamicSQl.class);
            // 测试trim
             Employee employee = new Employee(5,"%e%",null,null);
             List<Employee> employees=mapper.getEmpsByConditionTrim(employee);
             for (Employee employee2 : employees) {
             System.out.println(employee2); }
        } finally {
            openSession.close();
        }
    }

测试结果
原先where标签如果最后一个参数为空时，会多出and或者or出现sql语句语法错误，此时使用trim标签，会将多余的and删除，以下测试结果证实了这一说法
测试成功
prefix=””:前缀，trim标签体是整个字符串拼串后的结果
prefix给拼串后的整个字符串加一个前缀
prefixOverrides=”” :前缀覆盖，去掉整个字符串前面多余的字符
suffix=”” 后缀 suffix给拼串后的整个字符串加一个后缀
suffixOverrides=”” 后缀覆盖：去掉整个字符串后面多余的字符

四、choose标签(只选择满足条件的其中一个)

编写接口

package com.atorg.mybatis.dao;

import java.util.List;
import org.apache.ibatis.annotations.Param;
import com.atorg.mybatis.bean.Employee;
public interface EmployeeMapperDynamicSQl {
    public List<Employee> getEmpByConditionChoose(Employee employee);
    }

编写接口实现

<?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.atorg.mybatis.dao.EmployeeMapperDynamicSQl">
      <!--choose：只选择满足条件的一个  -->
      <!-- public List<Employee> getEmpByConditionChoose(Employee employee); -->
      <select id="getEmpByConditionChoose" resultType="emp">
          select * from tab1_employee
          <where>
              <choose>
                  <when test="id!=null">
                      id=#{id}
                  </when>
                  <when test="lastName!=null">
                      last_name like #{lastName}
                  </when>
                  <when test="email!=null">
                      email=#{email}
                  </when>
                  <otherwise><!--其他条件-->
                      gender=0
                  </otherwise>
              </choose>
          </where>
      </select>
</mapper>

测试程序

package com.atorg.mybatis.test;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import com.atorg.mybatis.bean.Department;
import com.atorg.mybatis.bean.Employee;
import com.atorg.mybatis.dao.DepartmentMapper;
import com.atorg.mybatis.dao.EmployeeMapper;
import com.atorg.mybatis.dao.EmployeeMapperAnnotation;
import com.atorg.mybatis.dao.EmployeeMapperDynamicSQl;
import com.atorg.mybatis.dao.EmployeeMapperPlus;
public class MyBatisTest {

    public SqlSessionFactory getSqlSessionFactory() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        return new SqlSessionFactoryBuilder().build(inputStream);
    }
    @Test
    public void testDynamicSQl() throws IOException {
        // 获取sqlSessionFactory对象
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        // 获取sqlSession对象不会自动提交数据
        SqlSession openSession = sqlSessionFactory.openSession();
        // 获取接口的实现类对象
        try {
            EmployeeMapperDynamicSQl mapper = openSession.getMapper(EmployeeMapperDynamicSQl.class);
            Employee employee = new Employee(null,"%e%",null,null);
            // 测试choose
              List<Employee> emps= mapper.getEmpByConditionChoose(employee);
              for (Employee employee2 : emps) { System.out.println(employee2);
              }
        } finally {
            openSession.close();
        }
    }

测试结果
此时只有name属性不为空，所以此时choose会选择姓名作为查询参数，以下测试结果证实这一说法 如果一次带了多个参数（多个参数都不为null）则默认会选择第一个作为查询参数，如果所有参数都为null则默认执行其他条件即otherwise标签中的条件作为查询条件测试成功

五、set标签和if标签结合做更新操作(封装修改条件)

接口编写

package com.atorg.mybatis.dao;
import java.util.List;
import org.apache.ibatis.annotations.Param;
import com.atorg.mybatis.bean.Employee;
public interface EmployeeMapperDynamicSQl {
    public void updateEmp(Employee employee);
    }

接口实现编写

<?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.atorg.mybatis.dao.EmployeeMapperDynamicSQl">
      <update id="updateEmp">
          update tab1_employee 
          <!-- set标签去除，号 -->
        <set>
            <if test="lastName!=null">
                last_name=#{lastName},
            </if>
            <if test="email!=null">
                email=#{email},
            </if>
            <if test="gender!=null">
                gender=#{gender}
            </if>
        </set>
        <!--trim做更新操作
         <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>

测试程序

package com.atorg.mybatis.test;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import com.atorg.mybatis.bean.Department;
import com.atorg.mybatis.bean.Employee;
import com.atorg.mybatis.dao.DepartmentMapper;
import com.atorg.mybatis.dao.EmployeeMapper;
import com.atorg.mybatis.dao.EmployeeMapperAnnotation;
import com.atorg.mybatis.dao.EmployeeMapperDynamicSQl;
import com.atorg.mybatis.dao.EmployeeMapperPlus;
public class MyBatisTest {
    public SqlSessionFactory getSqlSessionFactory() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        return new SqlSessionFactoryBuilder().build(inputStream);
    }
@Test
    public void testDynamicSQl() throws IOException {
        // 获取sqlSessionFactory对象
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        // 获取sqlSession对象不会自动提交数据
        SqlSession openSession = sqlSessionFactory.openSession();
        // 获取接口的实现类对象
        try {
            EmployeeMapperDynamicSQl mapper = openSession.getMapper(EmployeeMapperDynamicSQl.class);
             Employee employee = new Employee(1,"Admin",null,null);
            // 测试更新
            /*
             * mapper.updateEmp(employee); openSession.commit();
             */
            mapper.updateEmp(employee);
            openSession.commit();
        } finally {
            openSession.close();
        }
    }
}

测试
测试成功

Lelege

https://github.com/Lelege0/2020/08/02/MyBatis-%E5%8A%A8%E6%80%81SQL/

All articles in this blog are used except for special statements CC BY 4.0 reprint polocy. If reproduced, please indicate source Lelege !

Java 数据库 MyBatis

Cookie和Session

一、Cookie1.什么是Cookie？1.为了辨别用户身份，进行Session跟踪而储存在用户本地终端上的数据（通常经过加密），由用户客户端计算机暂时或永久保存的信息。2.Cookie是服务器通知客户端保存键值对的一种技术。3.客户端有了

2020-08-02 JavaEE

JavaEE Servlet Cookie

MyBatis-映射文件

增删改见上篇博客一、自增主键值的获取 MySQL支持自增主键，自增主键值的获取mybatis也是利用statement.getGenratedKeys()获取 * <insert id="addEmp" parameterType

2020-08-02 数据库

Java 数据库 MyBatis