04.动态SQL

一、什么是动态SQL？

动态SQL就是根据不同的条件产生的不同SQL语句。

二、动态SQL使用案例

案例：根据条件查询学生

1、查询年龄在18~20之间的同学：WHERE age >=18 AND age <=20

2、查询2班、年龄在18~20之间的同学：WHERE age >=18 AND age <=20 AND cid = 2

3、查询2班、姓“常”的同学：WHERE cid = 2 AND sname LIKE '常%'

（一）创建数据表

还是利用之前的tb_student表中的数据。

（二）创建项目

1、新建Maven项目MyBatisTest3

2、添加各种依赖（Servlet、MySQL、MyBatis、Lombok） 

<dependency>
    <groupId>javax.servlet</groupId>
    <artifactId>javax.servlet-api</artifactId>
    <version>4.0.1</version>
    <scope>provided</scope>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.49</version>
</dependency>
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.13</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.26</version>
    <scope>provided</scope>
</dependency>

3、添加框架支持

4、数据库配置文件db.properties（resources文件夹下）

mysql.driver=com.mysql.jdbc.Driver
mysql.url=jdbc:mysql://localhost:3306/db_test?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false
mysql.username=root
mysql.password=1234

5、MyBatis核心配置文件mybatis-config.xml（resources文件夹下）

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <properties resource="db.properties"/>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${mysql.driver}"/>
                <property name="url" value="${mysql.url}"/>
                <property name="username" value="${mysql.username}"/>
                <property name="password" value="${mysql.password}"/>
            </dataSource>
        </environment>
    </environments>
</configuration>

6、创建MyBatisUtil工具类

在java目录下新建com.sdbi.util包，新建MyBatisUtil.java类。

package com.sdbi.util;

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 java.io.IOException;
import java.io.Reader;

public class MyBatisUtil {
    private static final SqlSessionFactory factory;
    private static ThreadLocal<SqlSession> local = new ThreadLocal<SqlSession>();

    static {
        String resource = "mybatis-config.xml";
        Reader reader = null;
        try {
            reader = Resources.getResourceAsReader(resource);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        factory = new SqlSessionFactoryBuilder().build(reader);
    }

    public static SqlSessionFactory getFactory() {
        return factory;
    }

    private static SqlSession getSession(boolean isAutoCommit) {
        SqlSession session = local.get();
        if (session == null) {
            session = factory.openSession(isAutoCommit); // true，自动提交；false，手动提交
            local.set(session);
        }
        return session;
    }

    public static SqlSession getSession() { // 获取手动提交事务的session
        return getSession(false);
    }

    public static <T extends Object> T getMapper(Class<T> c) {
        return getSession(true).getMapper(c);
    }
}

（三）创建实体类

在java文件夹下，新建com.sdbi.pojo包，在该包下新建实体类，并且使用Lombok的注解。

Student.java

package com.sdbi.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Student {
    private int sid;
    private String sname;
    private int age;
    private int cid; // 班级id
}

（四）创建StudentMapper接口

在java文件夹下，新建com.sdbi.mapper包。

因为查询的条件是动态变化的，例如，有时查询条件里有班级，有时有年龄，有时有姓名，所以我们查询方法的参数列表不能是固定是某一个或某几个参数。

我们可以用集合HashMap或者实体类来封装查询条件。

StudentMapper.java

package com.sdbi.mapper;

import com.sdbi.pojo.Student;
import com.sdbi.pojo.StudentSearchCondition;

import java.util.HashMap;
import java.util.List;

public interface StudentMapper {
    // 在多条件查询中，如果查询条件不确定，可以直接使用HashMap作为参数
    // 优点：无需单独定义传递查询条件的类
    // 缺点：当向Map中存放参数时，key必须与动态sql保持一致（）
    public List<Student> searchStudent(HashMap<String, Object> params);

    // 也可以定义专门用于存放查询条件的实体类存放参数
    // 优点：设置参数时无需关注属性名
    // 缺点：需要单独定义一个类来封装参数
    public List<Student> searchStudent(StudentSearchCondition params);
}

（五）创建SQL映射文件

在resources目录下，新建一个mapper文件夹，在文件夹内新建StudentMapper.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.sdbi.mapper.StudentMapper">
    
</mapper>

并在mybatis-config.xml文件中引入该xml文件

<mappers>
    <mapper resource="/mapper/StudentMapper.xml"/>
</mappers>

（六）在com.sdbi.pojo包下新建实体类StudentSearchCondition

package com.sdbi.pojo;

import lombok.Data;

@Data
public class StudentSearchCondition {
    private String name;
    private int minAge;
    private int maxAge;
    private int cid;
}

（七）编写业务层Servlet

TestServlet.java

package com.sdbi.servlet;

import com.sdbi.mapper.StudentMapper;
import com.sdbi.pojo.Student;
import com.sdbi.pojo.StudentSearchCondition;
import com.sdbi.util.MyBatisUtil;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;

@WebServlet("/TestServlet")
public class TestServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        System.out.println("TestServlet.doGet()...start");
        searchStudent();
        System.out.println("TestServlet.doGet()...end");
    }

    private void searchStudent() {
        StudentMapper mapper = MyBatisUtil.getMapper(StudentMapper.class);
        HashMap<String, Object> params = new HashMap<String, Object>();
        // 添加查询条件

        List<Student> students = mapper.searchStudent(params);
        for (Student student : students) {
            System.out.println(student.toString());
        }

        System.out.println("--------------------------------");
        StudentSearchCondition condition = new StudentSearchCondition();
        // 添加查询条件

        students = mapper.searchStudent(condition);
        for (Student student : students) {
            System.out.println(student.toString());
        }
    }
}

准备工作基本做好了，下面我们来逐一的学习动态SQL的用法。

三、if

我们先来编写SQL语句，SQL语句是重点内容。

SELECT sid, sname, age, cid
FROM tb_student
WHERE cid = 2
AND age >= 18
AND age <= 22
AND sname LIKE "常%"

将以上SQL语句在数据库中测试，能够获取到正确的结果。

但是，我们希望通过动态SQL，在某些条件有值时，才将这个条件拼接到SQL预计中，这里我们就要注意，什么时候应该加上“AND”。

为了解决“AND”问题，我们可以在WHERE后面加上“1=1”，使条件绝对成立，这样后面不管有什么条件，都要有“AND”连接。

SELECT sid, sname, age, cid
FROM tb_student
WHERE 1 = 1
AND cid = 2
AND age >= 18
AND age <= 22
AND sname LIKE "常%"

我们把它用<if>标签写到StudentMapper.xml文件中。

注意，如果对于字符串进行判空，我们的test属性可以写成【test="name != null and name != ''"】（这里的并列条件用and连接，并且空串用单引号）或者写成【test="name != null &amp;&amp; name != &quot;&quot;"】（&amp;是“&”的转义字符，&quot;是单引号“'”的转义字符）

<?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.sdbi.mapper.StudentMapper">
    <select id="searchStudent" resultType="com.sdbi.pojo.Student">
        SELECT sid, sname, age, cid
        FROM tb_student
        WHERE 1 = 1
        <if test="cid != null">  <!-- cid就是参数对象的属性/参数Map的key -->
            AND cid = #{cid}
        </if>
        <if test="minAge != null">
            AND age &gt;= #{minAge}   <!-- &gt;大于号 -->
        </if>
        <if test="maxAge != null">
            AND age &lt;= #{maxAge}  <!-- &lt;小于号 -->
        </if>
        <if test="name != null and name != ''">
            AND sname LIKE #{name}
        </if>
    </select>
</mapper>

业务层测试，TestServlet中编写

package com.sdbi.servlet;

import com.sdbi.mapper.StudentMapper;
import com.sdbi.pojo.Student;
import com.sdbi.pojo.StudentSearchCondition;
import com.sdbi.util.MyBatisUtil;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;

@WebServlet("/TestServlet")
public class TestServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        System.out.println("TestServlet.doGet()...start");
        searchStudent();
        System.out.println("TestServlet.doGet()...end");
    }

    private void searchStudent() {
        StudentMapper mapper = MyBatisUtil.getMapper(StudentMapper.class);
        HashMap<String, Object> params = new HashMap<String, Object>();
        // 添加查询条件
        params.put("cid", 2);
        params.put("minAge", 18);
        params.put("maxAge", 22);
        params.put("name", "常%");
        List<Student> students = mapper.searchStudent(params);
        for (Student student : students) {
            System.out.println(student.toString());
        }

        System.out.println("--------------------------------");
        StudentSearchCondition condition = new StudentSearchCondition();
        // 添加查询条件
        condition.setCid(1);
        condition.setMinAge(16);
        condition.setMaxAge(22);
        condition.setName("%秀英");
        students = mapper.searchStudent(condition);
        for (Student student : students) {
            System.out.println(student.toString());
        }
    }
}

运行结果

 

注意事项：

① 如果我们在使用查询参数实体类StudentSearchCondition传递查询参数的时候，将condition.setCid(1)去掉，允许程序查询，会发现根本查不出任何结果，程序也不报错。

这是因为，我们在定义查询参数实体类StudentSearchCondition时，将cid设置为int类型，这就造成了，即使不通过condition.setCid(1)设置查询条件的cid，它也会有默认初始值0，<if test="cid != null">标签的判断条件是满足的，而我们的表中没有cid=0的数据，所以查询SQL正确执行了，但是没有查到结果。

我们只需要将查询参数实体类StudentSearchCondition中的属性数据类型都改为引用数据类型（如果是基本数据类型，我们就使用对应的包装类）。

查询参数实体类StudentSearchCondition代码修改如下：

package com.sdbi.pojo;

import lombok.Data;

@Data
public class StudentSearchCondition {
    private String name;
    private Integer minAge;
    private Integer maxAge;
    private Integer cid;
}

② 这种情况如果使用HashMap<String, Object>键值对作为查询参数就不会存在，因为没有往HashMap<String, Object>中放“cid”，那拼SQL语句时cid就等于null，这个查询条件就不会起作用，这是合理的，没问题的。

③ 使用HashMap键值对和实体类StudentSearchCondition作为查询参数的优缺点：使用HashMap键值对可以避免刚才那种不传参数引起的SQL语句问题，但是HashMap键值对中“键”的名称是由程序员自己写，如果不小心会写错，没有语法检查，这样会造成参数传递不成功；使用实体类，因为类的属性都已经定义好，用来设置查询参数，只需要设置“值”，不用管“键”，不好的地方就是，我们一定要用引用类型来定义类的属性。

四、where

我们在SQL映射文件中自己拼WHERE得考虑“AND”的问题，我们可以使用<where>标签来代替。<where>会将第一个AND过滤（不会过滤第二个）。

<?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.sdbi.mapper.StudentMapper">
    <select id="searchStudent" resultType="com.sdbi.pojo.Student">
        SELECT sid, sname, age, cid
        FROM tb_student
        <where>
            <if test="cid != null">  <!-- cid就是参数对象的属性/参数Map的key -->
                AND cid = #{cid}
            </if>
            <if test="minAge != null">
                AND age &gt;= #{minAge}   <!-- &gt;大于号 -->
            </if>
            <if test="maxAge != null">
                AND age &lt;= #{maxAge}  <!-- &lt;小于号 -->
            </if>
            <if test="name != null and name !=''">
                AND sname LIKE #{name}
            </if>
        </where>
        ORDER BY age
    </select>
</mapper>

并且我们在后面增加ORDER BY语句也很方便。

五、trim

trim（点缀物）标签的功能：子句首尾的删除与添加。它就是一个字符串处理工具，类似于replace()，但它只处理首尾。其实，在mybatis中的<set>和<where>都可以用<trim>来实现，但<trim>的功能更强大，使用起来更灵活。

语法：

<trim prefix="" suffix="" prefixOverrides="" suffixOverrides="">子句</trim>

属性：

  • prefix：在trim标签内SQL子句加上前缀。
  • suffix：在trim标签内SQL子句加上后缀。
  • prefixOverrides：去除多余的前缀内容。
  • suffixOverrides：去除多余的后缀内容。

上一节的<where>标签我们可以改写成下面的格式：

<?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.sdbi.mapper.StudentMapper">
    <select id="searchStudent" resultType="com.sdbi.pojo.Student">
        SELECT sid, sname, age, cid
        FROM tb_student
        <trim prefix="WHERE" prefixOverrides="AND | OR" suffix="ORDER BY age">
            <if test="cid != null">  <!-- cid就是参数对象的属性/参数Map的key -->
                AND cid = #{cid}
            </if>
            <if test="minAge != null">
                AND age &gt;= #{minAge}   <!-- &gt;大于号 -->
            </if>
            <if test="maxAge != null">
                AND age &lt;= #{maxAge}  <!-- &lt;小于号 -->
            </if>
            <if test="name != null and name !=''">
                AND sname LIKE #{name}
            </if>
        </trim>
    </select>
</mapper>

六、foreach

案例：如果我们只想查询几个年龄的学生，例如，我们想查询年龄是16、18、21的同学。

我们的SQL语句应该是：

SELECT sid, sname, age, cid
FROM tb_student
WHERE age IN (16, 18, 21)

那这个where条件用的是IN关键字指定范围怎么办呢？

我们就可以使用foreach标签来遍历一个集合。

StudentMapper.xml

<select id="searchStudentByAge" resultType="com.sdbi.pojo.Student">
    SELECT sid, sname, age, cid
    FROM tb_student
    WHERE age IN
    <!-- (16, 18, 21) -->
    <foreach collection="list" item="age" separator="," open="(" close=")">
        #{age}
    </foreach>
</select>

StudentMapper.java

public List<Student> searchStudentByAge(List<Integer> list);

TestServlet.java

private void searchStudentByAge() {
    StudentMapper mapper = MyBatisUtil.getMapper(StudentMapper.class);
    List<Integer> list = new ArrayList<>();
    list.add(16);
    list.add(18);
    list.add(21);
    List<Student> students = mapper.searchStudentByAge(list);
    for (Student student : students) {
        System.out.println(student.toString());
    }
}

 

posted @ 2023-06-09 08:08  熊猫Panda先生  阅读(760)  评论(0)  编辑  收藏  举报