MyBatis(四)-动态SQL
今天我们说的是MyBatis的动态SQL技术。说之前先看一张图
复杂的SQL语句
what the fuck～看着令人发毛。
我们在使用JDBC或者其他类似的框架进行数据库开发时，通常都要根据需求去手动拼装SQL，这是一个非常麻烦且痛苦的事情。MyBatis的动态SQL技术可以简化我们的操作，提高工作效率。
<if>
<! DOCTYPE mapper PUBLIC "-//http://mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xxx.mapper.CustomerMapper">
<! -- <if>元素使用 -->
<select id="findCustomerByNameAndJobs"
parameterType="com.xxx.po.Customer"
resultType="com.xxx.po.Customer">
select * from t_customer where 1=1
<if test="username !=null and username !=''">
and username like concat('%', #{username}, '%')
</if>
<if test="jobs !=null and jobs !=''">
and jobs= #{jobs}
</if>
</select>
</mapper>
public void deleteCustomerTest() throws IOException {
SqlSession openSession = null;
openSession = MyBatisUtils.getSqlSession();
if (openSession != null) {
Customer customer = new Customer();
customer.setJobs("teacher");
customer.setUsername("jack");
Customer.findCustomerByNameAndJobs(openSession,customer);
}
}
············································································
public static void findCustomerByNameAndJobs(SqlSession openSession, Customer customer) {
List<Customer> customers = openSession.selectList("com.itheima.mapper.CustomerMapper.findCustomerByNameAndJobs",
customer);
for (Customer user : customers) {
System.out.println(user);
}
openSession.commit();
openSession.close();
}
如果我们将customer的name和jobs设置为null会出现什么效果呢？
@Test
public void deleteCustomerTest() throws IOException {
SqlSession openSession = null;
openSession = MyBatisUtils.getSqlSession();
if (openSession != null) {
Customer customer = new Customer();
// customer.setJobs("teacher");
// customer.setUsername("jack");
Customer.findCustomerByNameAndJobs(openSession,customer);
}
}
image.png
从结果看出，当微传递任何参数时，程序会将所有的数据查出。这就是<if>的使用，在使用<if>元素时，只要test属性中的表达式为true，就会执行元素中的条件语句，但是在实际应用中，有时只需要从多个选项中选择一个去执行。在这种情况下，使用<if> 元素进行处理时非常不合适的。如果使用的是java语言，这种情况更适合使用switch--case--default语句来处理，那么在MyBatis中有没有对应的语法呢？针对这种情况，MyBatis中可以使用<choose>,<when>,<otherwise>元素组合去实现上面的情况。
<choose>,<when>,<otherwise>元素
<select id="findCustomerByNameOrJobs" parameterType="customer"
resultType="customer">
select * from t_customer where 1=1
<choose>
<when test="username!=null and username!=''">
and username like concat('%', #{username}, '%')
</when>
<when test="jobs!=null and jobs!=''">
and jobs=#{jobs}
</when>
<otherwise>
and phone is not null
</otherwise>
</choose>
</select>
public static void findCustomerByNameOrJobs(SqlSession openSession, Customer customer) {
List<Customer> customers = openSession.selectList("com.itheima.mapper.CustomerMapper.findCustomerByNameOrJobs",
customer);
for (Customer user : customers) {
System.out.println(user);
}
openSession.commit();
openSession.close();
}
只输入name
@Test
public void findAllUser() {
SqlSession openSession = null;
openSession=MyBatisUtils.getSqlSession();
if(null!=openSession) {
Customer customer = new Customer();
customer.setUsername("joy");
Customer.findCustomerByNameOrJobs(openSession,customer);
}
}
name=joy
输入jobs=student
@Test
public void findAllUser() {
SqlSession openSession = null;
openSession=MyBatisUtils.getSqlSession();
if(null!=openSession) {
Customer customer = new Customer();
customer.setJobs("student");
Customer.findCustomerByNameOrJobs(openSession,customer);
}
}
jobs=student
<where>、<trim>元素
上文中编写的SQL后面都加入了‘where 1=1’的条件，那么到底为什么要这么写呢？如果将where 后‘1=1’的条件去掉，那么MyBatis所拼接接出来的SQL将会如下所示：
select * from t_customer where and username like concat('%',?,'%')
where 之后直接跟的是and，这在运行时肯定会报SQL语法错误，而加入了条件‘1=1’后，即保证了where后面的条件成立，又避免了where后面第一个词是and或者or之类的关键词。那么在MyBatis中，有没有什么办法不用加入‘1=1’这样的条件，也能使拼接后的SQL成立呢？
针对这种情况，MyBatis提供了<where>元素来处理这样的问题。将映射文件中的‘where 1=1’条件删除，并使用<where>元素替换后的代码如下所示。
<select id="findCustomerByNameOrJobs" parameterType="customer"
resultType="customer">
select * from t_customer
<where>
<if test="username !=null and username!=' ' ">
and username like concat('%',#{username},'%')
</if>
<if test="jobs !=null and jobs!=' ' ">
and jobs=#{jobs}
</if>
</where>
</select>
上述配置代码中，使用<where>元素对“where 1=1”条件进行了替换，<where>元素会自动判断组合条件下拼装的SQL语句，只有<where>元素内的条件成立时，才会在拼接SQL中加入where关键字，否则将不会添加；即使where之后的内容有多余的“AND”或“OR”, <where>元素也会自动将它们去除。
除了使用<where>元素外，还可以通过<trim>元素来定制需要的功能，上述代码还可以修改为如下形式：
<! -- <trim>元素-->
<select id="findCustomerByNameAndJobs" parameterType="com.itheima.po.Customer" resultType="com.itheima.po.Customer">
select * from t_customer
<trim prefix="where" prefixOverrides="and">
<if test="username ! =null and username ! =''">
and username like concat('%', #{username}, '%')
</if>
<if test="jobs ! =null and jobs ! =''">
and jobs= #{jobs}
</if>
</trim>
</select>
上述配置代码中，同样使用<trim>元素对“where 1=1”条件进行了替换，<trim>元素的作用是去除一些特殊的字符串，它的prefix属性代表的是语句的前缀（这里使用where来连接后面的SQL片段），而prefixOverrides属性代表的是需要去除的那些特殊字符串（这里定义了要去除SQL中的and），上面的写法和使用<where>元素基本是等效的。
<foreach>元素
在实际开发中，有时可能会遇到这样的情况：假设在一个客户表中有1000条数据，现在需要将id值小于100的客户信息全部查询出来，这要怎么做呢？有人也许会说，“我可以一条一条查出来”，那如果查询200、300甚至更多也一条一条查吗？这显然是不可取的。有的人会想到，可以在Java方法中使用循环，将查询方法放在循环语句中，然后通过条件循环的方式查询出所需的数据。这种查询方式虽然可行，但每执行一次循环语句，都需要向数据库中发送一条查询SQL，其查询效率是非常低的。那么还有其他更好的方法吗？我们能不能通过SQL语句来执行这种查询呢？
其实，MyBatis中已经提供了一种用于数组和集合循环遍历的方式，那就是使用<foreach>元素，我们完全可以通过<foreach>元素来解决上述类似的问题。
<foreach>元素通常在构建IN条件语句时使用，其使用方式如下。
<! --<foreach>元素使用 -->
<select id="findCustomerByIds" parameterType="List" resultType="com.xxx.po.Customer">
select * from t_customer where id in
<foreach item="id" index="index" collection="list"
open="(" separator=", " close=")">
#{id}
</foreach>
</select>
在上述代码中，使用了<foreach>元素对传入的集合进行遍历并进行了动态SQL组装。关于<foreach>元素中使用的几种属性的描述具体如下。
· item：配置的是循环中当前的元素。
· index：配置的是当前元素在集合的位置下标。
· collection：配置的list是传递过来的参数类型（首字母小写）， 它可以是一个array、list（或collection）、Map集合的键、POJO包装类中数组或集合类型的属性名等。
· open和close：配置的是以什么符号将这些集合元素包装起来。
· separator：配置的是各个元素的间隔符。
<bind>标签
在进行模糊查询编写SQL语句的时候，如果使用“${}”进行字符串拼接，则无法防止SQL注入问题；如果使用concat函数进行拼接，则只针对MySQL数据库有效；如果使用的是Oracle数据库，则要使用连接符号“||”。这样，映射文件中的SQL就要根据不同的情况提供不同形式的实现，这显然是比较麻烦的，且不利于项目的移植。为此，MyBatis提供了<bind>元素来解决这一问题，我们完全不必使用数据库语言，只要使用MyBatis的语言即可与所需参数连接。
MyBatis的<bind>元素可以通过OGNL表达式来创建一个上下文变量，其使用方式如下：
<! --<bind>元素的使用：根据客户名模糊查询客户信息 -->
<select id="findCustomerByName" parameterType="com.itheima.po.Customer"
resultType="com.itheima.po.Customer">
<! --_parameter.getUsername()也可直接写成传入的字段属性名，即username-->
<bind name="pattern_username" value="'%'+_parameter.getUsername()+'%'" />
select * from t_customer
where
username like #{pattern_username}
</select>
上述配置代码中，使用<bind>元素定义了一个name为pattern_username的变量，<bind>元素中value的属性值就是拼接的查询字符串，其中_parameter.getUsername()表示传递进来的参数（也可以直接写成对应的参数变量名，如username）。在SQL语句中，直接引用<bind>元素的name属性值即可进行动态SQL组装。
