04.动态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 && name != """】(&是“&”的转义字符,"是单引号“'”的转义字符)
<?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 >= #{minAge} <!-- >大于号 --> </if> <if test="maxAge != null"> AND age <= #{maxAge} <!-- <小于号 --> </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 >= #{minAge} <!-- >大于号 --> </if> <if test="maxAge != null"> AND age <= #{maxAge} <!-- <小于号 --> </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 >= #{minAge} <!-- >大于号 --> </if> <if test="maxAge != null"> AND age <= #{maxAge} <!-- <小于号 --> </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()); } }