Mybatis动态SQL
1.1动态SQL中的元素
动态SQL是MyBatis的强大特性之一,MyBatis 3采用了功能强大的基于OGNL的表达式来完成动态SQL,
它消除了之前版本中需要了解的大多数元素,使用不到原来一半的元素就能完成所需工作。
MyBatis动态SQL中的主要元素,如表所示。
1.2<if>元素
在MyBatis中,<if>元素是最常用的判断语句,它类似于Java中的if语句,主要用于实现某些简单的条件选择。
在实际应用中,我们可能会通过多个条件来精确地查询某个数据。例如,要查找某个客户的信息,可以通过姓名和职业来查找客户,
也可以不填写职业直接通过姓名来查找客户,还可以都不填写而查询出所有客户,此时姓名和职业就是非必须条件。
类似于这种情况,在MyBatis中就可以通过<if>元素来实现。下面就通过一个具体的案例,来演示这种情况下<if>元素的使用,具体实现步骤如下。
(2)修改映射文件CustomerMapper.xml,在映射文件中使用<if>元素编写根据客户姓名和职业组合条件查询客户信息列表的动态SQL
<?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"> <!-- namespace表示命名空间 --> <mapper namespace="com.itheima.mapper.CustomerMapper"> <!-- <if>元素使用 --> <!-- parameterType属性用于指定传入参数的类型 --> <!-- resultType属性用于指定返回结果的类型 --> <select id="finCustomerByNameAndJob" parameterType="com.itheima.po.Customer" resultType="com.itheima.po.Customer"> select *from t_customer where 1=1 <if test="username!=null and username!=''"> and username like concat('%',#{username},'%') </if> <if test="job!=null and job!=''"> and job=#{job} </if> </select> </mapper>
使用<if>元素的test属性分别对username和jobs进行了非空判断(test属性多用于条件判断语句中,
用于判断真假,大部分的场景中都是进行非空判断,有时候也需要判断字符串、数字和枚举等),
如果传入的查询条件非空就进行动态SQL组装。
注意工具类代码和mybatis-config.xml文件
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <!-- 1.配置环境,默认的环境id为mysql --> <environments default="mysql"> <!-- 1.2配置id为mysql的数据库环境 --> <environment id="mysql"> <!-- 使用JDBC的事务管理 --> <transactionManager type="JDBC"/> <!-- 数据库连接池 --> <dataSource type="POOLED"> <!-- 数据库驱动 --> <property name="driver" value="com.mysql.jdbc.Driver"></property> <!-- 连接数据库 --> <property name="url" value="jdbc:mysql://localhost:3306/test"></property> <!-- 连接数据库的用户名 --> <property name="username" value="root"></property> <!-- 连接数据库的密码 --> <property name="password" value="root"></property> </dataSource> </environment> </environments> <!-- 2.配置Mapper的位置 --> <mappers> <mapper resource="com/itheima/mapper/CustomerMapper.xml"></mapper> </mappers> </configuration>
工具类
import java.io.Reader; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; public class MybatisUtils { private static SqlSessionFactory sqlSessionFactory = null; // 初始化SqlSessionFactory对象 static { try { // 使用MyBatis提供的Resources类加载MyBatis的配置文件 Reader reader = Resources.getResourceAsReader("mybatis-config.xml"); // 构建SqlSessionFactory工厂 sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader); } catch (Exception e) { e.printStackTrace(); } } // 获取SqlSession对象的静态方法 public static SqlSession getSession() { return sqlSessionFactory.openSession(); } }
(3)在测试类MybatisTest中,编写测试方法findCustomerByNameAndJobsTest(),如文件
/* * 根据客户姓名和职业组合条件查询客户信息列表 */ public void findCustomerByNameAndJobTest() { //通过工具类生成SqlSession对象 SqlSession sqlSession=MybatisUtils.getSession(); //创建Customer对象,封装需要组合查询的条件 Customer customer=new Customer(); //customer.setUsername("jack"); //customer.setJobs("teacher"); //执行SqlSession的查询方法,返回结果集 List<Customer> customers=sqlSession.selectList("com.itheima.mapper" +".CustomerMapper2.finCustomerByNameAndJob",customer); //输出结果 for(Customer customer2:customers) { System.out.println(customer2.toString()); } sqlSession.close(); }
注意://customer.setUsername("jack");//customer.setJobs("teacher");
这两行加与不加产生的结果大不相同
1.3<choose>、<when>、<otherwise>元素
在使用<if>元素时,只要test属性中的表达式为true,就会执行元素中的条件语句,但是在实际应用中,有时只需要从多个选项中选择一个去执行。
例如下面的场景:“当客户名称不为空,则只根据客户名称进行客户筛选;当客户名称为空,而客户职业不为空,则只根据客户职业进行客户筛选。
当客户名称和客户职业都为空,则要求查询出所有电话不为空的客户信息。”此种情况下,使用<if>元素进行处理是非常不合适的。
如果使用的是Java语言,这种情况显然更适合使用switch…case…default语句来处理。那么在MyBatis中有没有类似的语句呢?答案是肯定的。
针对上面情况,MyBatis中可以使用<choose>、<when>、<otherwise>元素进行处理。下面让我们看一下如何使用<choose>、<when>、<otherwise>元素组合去实现上面的情况。
(1)在映射文件CustomerMapper.xml中,使用<choose>、<when>、<otherwise>元素执行上述情况的动态SQL代码如下所示。
<!-- <choose>.<when>.<otherwise>元素 --> <select id="findCustomerByNameOrJob" parameterType="com.itheima.po.Customer" resultType="com.itheima.po.Customer"> <choose> <when test="username!=null and username!=''"> and username like concat('%',#{username},'%') </when> <when test="job!=null and job!=''"> and job=#{job} </when> <otherwise> and phone is not null </otherwise> </choose> </select>
在上述代码中,使用了<choose>元素进行SQL拼接,当第一个<when>元素中的条件为真,
则只动态组装第一个<when>元素内的SQL片段,
否则就继续向下判断第二个<when>元素中的条件是否为真,以此类推。当前面所有when元素中的条件都不为真时,则只组装<otherwise>元素内的SQL片段。
(2)在测试类MybatisTest中,编写测试方法findCustomerByNameOrJobsTest(),其代码如下所示。
public void findCustomerByNameOrJobTest() { //通过工具类生成SqlSession对象 SqlSession sqlSession=MybatisUtils.getSession(); //创建Customer对象,封装需要组合查询的条件 Customer customer=new Customer(); //customer.setUsername("jack"); //customer.setJobs("teacher"); //执行SqlSession的查询方法,返回结果集 List<Customer> customers=sqlSession.selectList("com.itheima.mapper" +".CustomerMapper2.finCustomerByNameOrJob",customer); //输出结果 for(Customer customer2:customers) { System.out.println(customer2.toString()); } }
1.4<where>、<trim>元素
在前两个小节的案例中,映射文件中编写的SQL后面都加入了“where 1=1”的条件,那么到底为什么要这么写呢?如果将where后“1=1”的条件去掉,那么MyBatis所拼接出来的SQL将会如下所示。
select *from t_customer where and username like concat('%',?,'%')
上面SQL中,where后直接跟的是and,这在运行时肯定会报SQL语法错误,而加入了条件“1=1”后,既保证了where后面的条件成立,
又避免了where后面第一个词是and或者or之类的关键词。那么在MyBatis中,有没有什么办法不用加入“1=1”这样的条件,也能使拼接后的SQL成立呢?
针对这种情况,MyBatis提供了<where>元素来处理这样的问题。
<!-- <where>元素 --> <select id="findCustomerByNameAndJob" parameterType="com.itheima.po.Customer" resultType="com.itheima.po.Customer"> select *from t_customer <where> <if test="username!=null and username!=''"> and username like concat('%',#{username},'%') </if> <if test="job!=null and job!=''"> and job=#{job} </if> </where> </select>
上述配置代码中,使用<where>元素对“where 1=1”条件进行了替换,
<where>元素会自动判断组合条件下拼装的SQL语句,只有<where>元素内的条件成立时,才会在拼接SQL中加入where关键字,
否则将不会添加;即使where之后的内容有多余的“AND”或“OR”,<where>元素也会自动将它们去除。
可以和<if>的做一个比较
<!-- <if>元素使用 --> <!-- parameterType属性用于指定传入参数的类型 --> <!-- resultType属性用于指定返回结果的类型 --> <select id="finCustomerByNameAndJob" parameterType="com.itheima.po.Customer" resultType="com.itheima.po.Customer"> select *from t_customer where 1=1 <if test="username!=null and username!=''"> and username like concat('%',#{username},'%') </if> <if test="job!=null and job!=''"> and job=#{job} </if> </select>
除了使用<where>元素外,还可以通过<trim>元素来定制需要的功能,上述代码还可以修改为如下形式:
<!-- <trim>元素 --> <select id="findCustomerByNameAndJob" 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="job!=null and job!=''"> and job=#{job} </if> </trim> </select>
上述配置代码中,同样使用<trim>元素对“where 1=1”条件进行了替换,<trim>元素的作用是去除一些特殊的字符串,
它的prefix属性代表的是语句的前缀(这里使用where来连接后面的SQL片段),而prefixOverrides属性代表的是需要去除的那些特殊字符串
(这里定义了要去除SQL中的and),上面的写法和使用<where>元素基本是等效的。
1.5<set>元素:
<set>元素主要用于更新操作,其主要作用是在动态包含的SQL语句前输出一个SET关键字,并将SQL语句中最后一个多余的逗号去除。
以入门案例中的更新操作为例,使用<set>元素对映射文件中更新客户信息的SQL语句进行修改的代码如下所示。
<!-- <set>元素 --> <update id="updateCustomer" parameterType="com.itheima.po.Customer"> update t_customer <set> <if test="username!=null and username!=''"> username=#{username}, </if> <if test="job!=null and job!=''"> job=#{job}, </if> <if test="phone!=null and phone!=''"> phone=#{phone}, </if> </set> where id=#{id} </update>
为了验证上述配置,可以在测试类中编写测试方法updateCustomerTest(),其代码如下所示。
/* * 更新客户 */ public void updateCustomerTest() { //通过工具类生成SqlSession对象 SqlSession sqlSession=MybatisUtils.getSession(); //创建Customer对象,封装需要组合查询的条件 Customer customer=new Customer(); customer.setId(3); customer.setPhone("1234564332213"); //4.SqlSession执行更新操作 //4.2执行SqlSession的更新方法,返回的是SQL语句影响的行数 int row=sqlSession.update("com.itheima.mapper" +".CustomerMapper.updateCustomer",customer); //4.3通过返回值判断是否更新成功 if(row>0) { System.out.println("您成功修改了"+row+"条数据"); }else { System.out.println("执行修改操作失败!!!!"); } //4.4提交事务 sqlSession.commit(); //5关闭sqlSession sqlSession.close(); }
1.6<foreach>元素
在实际开发中,有时可能会遇到这样的情况:假设在一个客户表中有1000条数据,现在需要将id值小于100的客户信息全部查询出来,这要怎么做呢?
有人也许会说,“我可以一条一条查出来”,那如果查询200、300甚至更多也一条一条查吗?这显然是不可取的。有的人会想到,可以在Java方法中使用循环,
将查询方法放在循环语句中,然后通过条件循环的方式查询出所需的数据。这种查询方式虽然可行,但每执行一次循环语句,都需要向数据库中发送一条查询SQL
,其查询效率是非常低的。那么还有其他更好的方法吗?我们能不能通过SQL语句来执行这种查询呢?
其实,MyBatis中已经提供了一种用于数组和集合循环遍历的方式,那就是使用<foreach>元素,我们完全可以通过<foreach>元素来解决上述类似的问题。
<foreach>元素通常在构建IN条件语句时使用,其使用方式如下。
<!-- <foreach>元素使用 --> <select id="finCustomerByIds" parameterType="List" resultType="com.itheima.po.Customer"> select *from t_customer where id in <foreach item="id" index="index" collection="list" open="(" separator=", " close=")"> #{id} </foreach>
在上述代码中,使用了<foreach>元素对传入的集合进行遍历并进行了动态SQL组装。关于<foreach>元素中使用的几种属性的描述具体如下。
· item:配置的是循环中当前的元素。
· index:配置的是当前元素在集合的位置下标。
· collection:配置的list是传递过来的参数类型(首字母小写),它可以是一个array、list(或collection)、Map集合的键、POJO包装类中数组或集合类型的属性名等。
· open和close:配置的是以什么符号将这些集合元素包装起来。
· separator:配置的是各个元素的间隔符。
为了验证上述配置,可以在测试类MybatisTest中,编写测试方法findCustomerByIdsTest(),其代码如下所示。
/* * 根据客户编号批量查询客户信息 */ public void findCustomerByIdsTest() { //通过工具类生成SqlSession对象 SqlSession sqlSession=MybatisUtils.getSession(); //创建List集合,封装查询id List<Integer> ids=new ArrayList<Integer>(); ids.add(1); ids.add(2); ids.add(3); //执行SqlSession的查询方法,返回结果集 List<Customer> customers=sqlSession.selectList("com.itheima.mapper" + ".CustomerMapper.finCustomerByIds",ids); //输出查询结果 for(Customer customer:customers) { System.out.println(customer.toString()); } sqlSession.close(); }
1.7<bind>元素
<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组装。
/* * <bind>元素的使用:根据客户名模糊查询客户信息 */ public void findCustomerByNameTest() { //通过工具类生成SqlSession对象 SqlSession sqlSession=MybatisUtils.getSession(); //创建Customer对象,封装需要组合查询的条件 Customer customer=new Customer(); customer.setUsername("j"); //执行SqlSession的查询方法,返回结果集 List<Customer> customers=sqlSession.selectList("com.itheima.mapper" + ".CustomerMapper.findCustomerByName",customer); //输出查询结果 for(Customer customer2:customers) { System.out.println(customer2.toString()); } sqlSession.close(); }