您的位置: 首页  >  文章  >  Mybatis之动态SQL

Mybatis之动态SQL

分类: 文章 • 2023-12-01 11:41:16
Mybatis 动态SQL的使用：

简介

  • 动态SQL是MyBatis强大特性之一。极大的简化我们拼装SQL的操作
  • 动态SQL元素和使用 JSTL 或其他类似基于 XML 的文本处理器相似
  • MyBatis 采用功能强大的基于 OGNL 的表达式来简化操作
    • If
    • choose (when, otherwise)
    • trim (where, set)
    • foreach

OGNL（ Object Graph Navigation Language ）对象图导航语言，这是一种强大的表达式语言，通过它可以非常方便的来操作对象属性。 类似于我们的EL，SpEL等

功能 参数
访问对象属性 person.name
调用方法 person.getName()
person.getName() @[email protected] @[email protected]()
调用构造方法 new
com.atguigu.bean.Person(‘admin’).name
运算符 +,-*,/,%
逻辑运算符 in,not in,>,>=,<,<=,==,!=

注意：xml中特殊符号如”,>,<等这些都需要使用转义字符

项目结构

数据准备

Mybatis之动态SQL

Mybatis之动态SQL

项目结构

Mybatis之动态SQL

Department

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
 
package com.hph.mybatis.beans;

import java.util.List;

public class Department {

    private Integer id;
    private String departmentName ;

    private List<Employee> emps ;


    public List<Employee> getEmps() {
        return emps;
    }
    public void setEmps(List<Employee> emps) {
        this.emps = emps;
    }
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getDepartmentName() {
        return departmentName;
    }
    public void setDepartmentName(String departmentName) {
        this.departmentName = departmentName;
    }
    @Override
    public String toString() {
        return "Department [id=" + id + ", departmentName=" + departmentName + "]";
    }
}

Employee

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
 
package com.hph.mybatis.beans;

public class Employee {

    private Integer id;
    private String lastName;
    private String email;
    private Integer gender;

    private Department dept;

    public void setDept(Department dept) {
        this.dept = dept;
    }

    public Department getDept() {
        return dept;
    }

    public Employee() {
    }

    public Employee(Integer id, String lastName, String email, Integer gender, Department dept) {
        this.id = id;
        this.lastName = lastName;
        this.email = email;
        this.gender = gender;
        this.dept = dept;
    }

    public Employee(Integer id, String lastName, String email, Integer gender) {
        super();
        this.id = id;
        this.lastName = lastName;
        this.email = email;
        this.gender = gender;
    }


    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Integer getGender() {
        return gender;
    }

    public void setGender(Integer gender) {
        this.gender = gender;
    }

    @Override
    public String toString() {
        return "Employee [id=" + id + ", lastName=" + lastName + ", email=" + email + ", gender=" + gender + "]";
    }

}

EmployeeMapperDynamicSQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
package com.hph.mybatis.dao;

import com.hph.mybatis.beans.Employee;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface EmployeeMapperDynamicSQL {

    public List<Employee> getEmpsByConditionIfWhere(Employee condition);

    public List<Employee> getEmpsByConditionTrim(Employee condition);

    public void updateEmpByConitionSet(Employee condition);

    public  List<Employee> getEmpsByConditionChoose(Employee condition);

    public List<Employee> getEmpsByIds(@Param("ids") List<Integer> ids);

    //批量操作: 删除 修改 添加
    public void addEmps(@Param("emps") List<Employee> emps);
}

EmployeeMapperDynamicSQL.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
 
<?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.hph.mybatis.dao.EmployeeMapperDynamicSQL">
    <!-- public List<Employee>  getEmpsByConditionIfWhere(Employee Condition); -->
    <select id="getEmpsByConditionIfWhere" resultType="com.hph.mybatis.beans.Employee">
        select id, last_name, email, gender
        from tbl_employee
        <!-- where 1=1 -->
        <where> <!-- 在SQL语句中提供WHERE关键字，  并且要解决第一个出现的and 或者是 or的问题 -->
            <if test="id!=null">
                and id = #{id }
            </if>
            <if test="lastName!=null&amp;&amp;lastName!=&quot;&quot;">
                and last_name = #{lastName}
            </if>
            <if test="email!=null and email.trim()!=''">
                and email = #{email}
            </if>
            <if test="gender==0 or gender==1">
                and gender = #{gender}
            </if>
        </where>
    </select>
    <select id="getEmpsByConditionTrim" resultType="com.hph.mybatis.beans.Employee">
        select id, last_name, email, gender
        from tbl_employee
        <!--
        prefix: 添加一个前缀
        pprefixOverrides:覆盖/去掉一个前缀
        ssuffxi:添加一个后缀
        suffixOverrides:覆盖/去掉一个后缀
         -->

        <trim prefix="where" suffixOverrides="and|or">
            <if test="id!=null">
                id = #{id } and
            </if>
            <if test="lastName!=null&amp;&amp;lastName!=&quot;&quot;">
                last_name = #{lastName} and
            </if>
            <if test="email!=null and email.trim()!=''">
                email = #{email} or
            </if>
            <if test="gender==0 or gender==1">
                gender = #{gender}
            </if>
        </trim>
    </select>
    <!--public void updateEmpByConitionSet(Employee condition);-->

    <update id="updateEmpByConitionSet">
        update tbl_employee
        <set>
            <if test="lastName!=null">
                last_name = #{lastName},
            </if>
            <if test="email!=null">
                email = #{email},
            </if>
            <if test="gender==0 or gender==1">
                gender= #{gender},
            </if>
        </set>
        where id = #{id}
    </update>
    <!--    public void updateEmpByConitionSet(Employee condition);-->
    <select id="getEmpsByConditionChoose" resultType="com.hph.mybatis.beans.Employee">
        select id,last_name,email,gender
        from tbl_employee
        where
        <choose>
            <when test="id!=null">
                id=#{id}
            </when>
            <when test="lastName!=null">
                last_name=#{lastName}
            </when>
            <when test="email!=null">
                email = #{email}
            </when>
            <otherwise>
                gender = 0
            </otherwise>
        </choose>
    </select>

    <!--    public List<Employee> getEmpsByIds(@Param("ids")List<Integer> ids);-->
    <select id="getEmpsByIds" resultType="com.hph.mybatis.beans.Employee">
        <!--
        foreach:
            collection:指定要迭代的几乎额
            item:当前集合中迭代出的元素
            open:指定一个开始字符
            close:指定一个结束字符
            separtor:元素与元素之间的分隔符
        -->
        select id,last_name,email,gender from tbl_employee
        where id in
        <foreach collection="ids" item="currId" open="(" close=")" separator=",">
            #{currId}
        </foreach>
    </select>

    <!--    public void addEmps(@Param("emps") List<Employee> emps)
    添加:insert into tbl_employee(x,x,x) values(?,?,?),(?,?<?),(?,?,?)
    删除:delete from tbl_employee where id in (?,?,?)
    修改:update tbl_employee set last_name = #{lastName }...where id  = #{id}}
         update tbl_employee set last_name = #{lastName }...where id  = #{id}}
         update tbl_employee set last_name = #{lastName }...where id  = #{id}}
         默认情况下,JDBCb允许将多条分号SQL通过;平日你改成一个字符串

    ;-->

    <insert id="addEmps">
        insert into tbl_employee(last_name, email,gender ) values
        <foreach collection="emps" item="emp" separator=",">
            (#{emp.lastName},#{emp.email},#{emp.gender})
        </foreach>
    </insert>

</mapper>

TestMybatisDynamicSQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
 
package com.hph.mybatis.test;

import com.hph.mybatis.beans.Employee;
import com.hph.mybatis.dao.EmployeeMapperDynamicSQL;
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 java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

public class TestMybatisDynamicSQL {

    public SqlSessionFactory getSqlSessionFactory() throws Exception {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        return sqlSessionFactory;
    }

    @Test
    public void testIf() throws Exception {
        SqlSessionFactory ssf = getSqlSessionFactory();
        SqlSession session = ssf.openSession();

        try {
            EmployeeMapperDynamicSQL mapper = session.getMapper(EmployeeMapperDynamicSQL.class);
            Employee condition = new Employee();
         /*   condition.setId(5);
            condition.setLastName("清风_笑丶");*/
            //condition.setEmail("[email protected]");
            //condition.setGender(1);
            List<Employee> emps = mapper.getEmpsByConditionIfWhere(condition);
            System.out.println(emps);

        } finally {
            session.close();
        }
    }

    @Test
    public void testTrim() throws Exception {
        SqlSessionFactory ssf = getSqlSessionFactory();
        SqlSession session = ssf.openSession();

        try {
            EmployeeMapperDynamicSQL mapper = session.getMapper(EmployeeMapperDynamicSQL.class);
            Employee condition = new Employee();
            condition.setId(5);
            condition.setLastName("清风_笑丶");
            condition.setEmail("[email protected]");
            //condition.setGender(1);
            List<Employee> emps = mapper.getEmpsByConditionTrim(condition);
            System.out.println(emps);

        } finally {
            session.close();
        }
    }

    @Test
    public void testSet() throws Exception {
        SqlSessionFactory ssf = getSqlSessionFactory();
        SqlSession session = ssf.openSession(true);

        try {
            EmployeeMapperDynamicSQL mapper = session.getMapper(EmployeeMapperDynamicSQL.class);
            Employee condition = new Employee();
            condition.setId(5);
            condition.setLastName("清风_笑丶testSet");
            condition.setEmail("[email protected]");
//         condition.setGender(1);
            mapper.updateEmpByConitionSet(condition);
        } finally {
            session.close();
        }
    }

    @Test
    public void testChoose() throws Exception {
        SqlSessionFactory ssf = getSqlSessionFactory();
        SqlSession session = ssf.openSession(true);

        try {
            EmployeeMapperDynamicSQL mapper = session.getMapper(EmployeeMapperDynamicSQL.class);
            Employee condition = new Employee();
            condition.setId(1);
            condition.setLastName("清风_笑丶testChoose");
            condition.setEmail("[email protected]");
            //condition.setGender(1);
            List<Employee> emps = mapper.getEmpsByConditionChoose(condition);
            System.out.println(emps);
        } finally {
            session.close();
        }
    }

    @Test
    public void testForeach() throws Exception {
        SqlSessionFactory ssf = getSqlSessionFactory();
        SqlSession session = ssf.openSession(true);
        try {
            EmployeeMapperDynamicSQL mapper = session.getMapper(EmployeeMapperDynamicSQL.class);
            Employee condition = new Employee();
            List<Integer> ids = new ArrayList<Integer>();
            ids.add(5);
            ids.add(6);
            ids.add(7);
            List<Employee> emps = mapper.getEmpsByIds(ids);

            System.out.println(emps);
        } finally {
            session.close();
        }
    }

    @Test
    public void testBatch() throws Exception {
        SqlSessionFactory ssf = getSqlSessionFactory();
        SqlSession session = ssf.openSession(true);
        try {
            EmployeeMapperDynamicSQL mapper = session.getMapper(EmployeeMapperDynamicSQL.class);
            List<Employee> emps = new ArrayList<Employee>();
            emps.add(new Employee(null, "清风笑_testBatch1", "[email protected]", 1));
            emps.add(new Employee(null, "清风笑_testBatch2", "[email protected]", 0));
            emps.add(new Employee(null, "清风笑_testBatch3", "[email protected]", 1));

            mapper.addEmps(emps);

        } finally {
            session.close();
        }
    }
}

if where

If用于完成简单的判断.

Where用于解决SQL语句中where关键字以及条件中第一个and或者or的问题 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
<!-- public List<Employee>  getEmpsByConditionIfWhere(Employee Condition); -->
  <select id="getEmpsByConditionIfWhere" resultType="com.hph.mybatis.beans.Employee">
      select id, last_name, email, gender
      from tbl_employee
      <!-- where 1=1 -->
      <where> <!-- 在SQL语句中提供WHERE关键字，  并且要解决第一个出现的and 或者是 or的问题 -->
          <if test="id!=null">
              and id = #{id }
          </if>
          <if test="lastName!=null&amp;&amp;lastName!=&quot;&quot;">
              and last_name = #{lastName}
          </if>
          <if test="email!=null and email.trim()!=''">
              and email = #{email}
          </if>
          <if test="gender==0 or gender==1">
              and gender = #{gender}
          </if>
      </where>
  </select>
 
1
2
3
4
5
6
7
8
9
10
11
12
13
 
public void testIf() throws Exception {
     SqlSessionFactory ssf = getSqlSessionFactory();
     SqlSession session = ssf.openSession();

     try {
         EmployeeMapperDynamicSQL mapper = session.getMapper(EmployeeMapperDynamicSQL.class);
         Employee condition = new Employee();
         List<Employee> emps = mapper.getEmpsByConditionIfWhere(condition);
         System.out.println(emps);
     } finally {
         session.close();
     }
 }

Mybatis之动态SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
@Test
 public void testIf() throws Exception {
     SqlSessionFactory ssf = getSqlSessionFactory();
     SqlSession session = ssf.openSession();

     try {
         EmployeeMapperDynamicSQL mapper = session.getMapper(EmployeeMapperDynamicSQL.class);
         Employee condition = new Employee();
         condition.setId(1001);
         List<Employee> emps = mapper.getEmpsByConditionIfWhere(condition);
         System.out.println(emps);

     } finally {
         session.close();
     }
 }

Mybatis之动态SQL

trim

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 
<select id="getEmpsByConditionTrim" resultType="com.hph.mybatis.beans.Employee">
    select id, last_name, email, gender
    from tbl_employee
    <!--
    prefix: 添加一个前缀
    pprefixOverrides:覆盖/去掉一个前缀
    ssuffxi:添加一个后缀
    suffixOverrides:覆盖/去掉一个后缀
     -->

    <trim prefix="where" suffixOverrides="and|or">
        <if test="id!=null">
            id = #{id } and
        </if>
        <if test="lastName!=null&amp;&amp;lastName!=&quot;&quot;">
            last_name = #{lastName} and
        </if>
        <if test="email!=null and email.trim()!=''">
            email = #{email} or
        </if>
        <if test="gender==0 or gender==1">
            gender = #{gender}
        </if>
    </trim>
</select>
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
@Test
  public void testTrim() throws Exception {
      SqlSessionFactory ssf = getSqlSessionFactory();
      SqlSession session = ssf.openSession();

      try {
          EmployeeMapperDynamicSQL mapper = session.getMapper(EmployeeMapperDynamicSQL.class);
          Employee condition = new Employee();
          List<Employee> emps = mapper.getEmpsByConditionTrim(condition);
          System.out.println(emps);

      } finally {
          session.close();
      }
  }

Mybatis之动态SQL

set

set 主要是用于解决修改操作中SQL语句中可能多出逗号的问题.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
<update id="updateEmpByConitionSet">
    update tbl_employee
    <set>
        <if test="lastName!=null">
            last_name = #{lastName},
        </if>
        <if test="email!=null">
            email = #{email},
        </if>
        <if test="gender==0 or gender==1">
            gender= #{gender},
        </if>
    </set>
    where id = #{id}
</update>
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
@Test
public void testSet() throws Exception {
    SqlSessionFactory ssf = getSqlSessionFactory();
    SqlSession session = ssf.openSession(true);

    try {
        EmployeeMapperDynamicSQL mapper = session.getMapper(EmployeeMapperDynamicSQL.class);
        Employee condition = new Employee();
        condition.setId(1001);
        condition.setLastName("清风笑丶");
        condition.setEmail("[email protected]");
        mapper.updateEmpByConitionSet(condition);
    } finally {
        session.close();
    }
}

Mybatis之动态SQL

Mybatis之动态SQL

choose(when、otherwise)

choose 主要是用于分支判断，类似于java中的switch case,只会满足所有分支中的一个

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
<!--    public void updateEmpByConitionSet(Employee condition);-->
<select id="getEmpsByConditionChoose" resultType="com.hph.mybatis.beans.Employee">
    select id,last_name,email,gender
    from tbl_employee
    where
    <choose>
        <when test="id!=null">
            id=#{id}
        </when>
        <when test="lastName!=null">
            last_name=#{lastName}
        </when>
        <when test="email!=null">
            email = #{email}
        </when>
        <otherwise>
            gender = 0
        </otherwise>
    </choose>
</select>
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
@Test
   public void testChoose() throws Exception {
       SqlSessionFactory ssf = getSqlSessionFactory();
       SqlSession session = ssf.openSession(true);

       try {
           EmployeeMapperDynamicSQL mapper = session.getMapper(EmployeeMapperDynamicSQL.class);
           Employee condition = new Employee();
           
           List<Employee> emps = mapper.getEmpsByConditionChoose(condition);
           System.out.println(emps);
       } finally {
           session.close();
       }
   }

Mybatis之动态SQL

foreach

foreach 主要用户循环迭代

    collection: 要迭代的集合

    item: 当前从集合中迭代出的元素

    open: 开始字符

    close:结束字符

    separator: 元素与元素之间的分隔符

    index:

      迭代的是List集合: index表示的当前元素的下标

      迭代的Map集合: index表示的当前元素的key

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
<!--    public List<Employee> getEmpsByIds(@Param("ids")List<Integer> ids);-->
<select id="getEmpsByIds" resultType="com.hph.mybatis.beans.Employee">
    <!--
    foreach:
        collection:指定要迭代的几乎额
        item:当前集合中迭代出的元素
        open:指定一个开始字符
        close:指定一个结束字符
        separtor:元素与元素之间的分隔符
    -->
    select id,last_name,email,gender from tbl_employee  where id in
    <foreach collection="ids" item="currId" open="(" close=")" separator=",">
        #{currId}
    </foreach>
</select>
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
@Test
public void testForeach() throws Exception {
    SqlSessionFactory ssf = getSqlSessionFactory();
    SqlSession session = ssf.openSession(true);
    try {
        EmployeeMapperDynamicSQL mapper = session.getMapper(EmployeeMapperDynamicSQL.class);
        Employee condition = new Employee();
        List<Integer> ids = new ArrayList<Integer>();
        ids.add(1001);
        ids.add(1002);
        List<Employee> emps = mapper.getEmpsByIds(ids);

        System.out.println(emps);
    } finally {
        session.close();
    }
}

Mybatis之动态SQL

1
2
3
4
5
6
7
8
9
 
<!--
    //批量操作: 删除 修改 添加
    public void addEmps(@Param("emps") List<Employee> emps);-->
<insert id="addEmps">
    insert into tbl_employee(last_name, email,gender ) values
    <foreach collection="emps" item="emp" separator=",">
        (#{emp.lastName},#{emp.email},#{emp.gender})
    </foreach>
</insert>
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
@Test
  public void testBatch() throws Exception {
      SqlSessionFactory ssf = getSqlSessionFactory();
      SqlSession session = ssf.openSession(true);
      try {
          EmployeeMapperDynamicSQL mapper = session.getMapper(EmployeeMapperDynamicSQL.class);
          List<Employee> emps = new ArrayList<Employee>();
          emps.add(new Employee(null, "清风笑_testBatch1", "[email protected]", 1));
          emps.add(new Employee(null, "清风笑_testBatch2", "[email protected]", 0));
          emps.add(new Employee(null, "清风笑_testBatch3", "[email protected]", 1));

          mapper.addEmps(emps);

      } finally {
          session.close();
      }
  }

Mybatis之动态SQL

sql

sql 标签是用于抽取可重用的sql片段，将相同的，使用频繁的SQL片段抽取出来，单独定义，方便多次引用.

抽取SQL

1
2
3
 
<sql id="selectSQL">
		select id , last_name, email ,gender from tbl_employee
</sql>

引用SQL:

1
 
<include refid="selectSQL"></include>

相关推荐