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先生  阅读(713)  评论(0编辑  收藏  举报