MySQL动态SQL拼接怎么实现

54次阅读
没有评论

共计 6986 个字符,预计需要花费 18 分钟才能阅读完成。

这篇“MySQL 动态 SQL 拼接怎么实现”文章的知识点大部分人都不太理解,所以丸趣 TV 小编给大家总结了以下内容,内容详细,步骤清晰,具有一定的借鉴价值,希望大家阅读完这篇文章能有所收获,下面我们一起来看看这篇“MySQL 动态 SQL 拼接怎么实现”文章吧。

一、动态 sql 拼接目标

能够使用 mybatis 的标签实现动态 SQL 拼接

分析

我们在前边的学习过程中,使用的 SQL 语句都非常简单。而在实际业务开发中,我们的 SQL 语句通常是动态拼接而成的,比如:条件搜索功能的 SQL 语句。

#  提供了一个功能:用户可以在页面上根据 username、sex、address 进行搜索
#  用户输入的搜索条件:可以是一个条件,也可能是两个、三个
#  只输入一个条件:姓名是 王 
SELECT * FROM USER WHERE username LIKE  % 王 % 
#  只输入一个条件:性别是“男”SELECT * FROM USER WHERE sex =  男 
#  输入两个条件:姓名“王”,性别“男”SELECT * FROM USER WHERE username LIKE  % 王 %  AND sex =  男 
#  输入三个条件:姓名“王”,性别“男”,地址“北京”SELECT * FROM USER WHERE username LIKE  % 王 %  AND sex =  男  AND address LIKE  % 北京 %

在 Mybatis 中,SQL 语句是写在映射配置的 XML 文件中的。Mybatis 提供了一些 XML 的标签,用来实现动态 SQL 的拼接。

常用的标签有:

if /if:用来进行判断,相当于 Java 里的 if 判断

where /where:通常和 if 配合,用来代替 SQL 语句中的 where 1=1

foreach /foreach:用来遍历一个集合,把集合里的内容拼接到 SQL 语句中。例如拼接:in (value1, value2, …)

sql /sql:用于定义 sql 片段,达到重复使用的目的

讲解 1. 准备 Mybatis 环境

创建 java 项目,导入 jar 包;准备 JavaBean

创建映射器接口 UserDao

创建映射配置文件 UserDao.xml

创建全局配置文件 SqlMapConfig.xml

创建日志配置文件 log4j.properties

2. if 标签:语法介绍

if test= 判断条件,使用 OGNL 表达式进行判断 
 SQL 语句内容,  如果判断为 true,这里的 SQL 语句就会进行拼接 /if

使用示例

根据用户的名称和性别搜索用户信息。把搜索条件放到 User 对象里,传递给 SQL 语句

映射器接口 UserDao 上加方法

package com.demo.dao;import com.demo.domain.User;import java.util.List;public interface UserDao {
 /**
 *  根据 username 和 sex 搜索用户
 * @param user  封装了搜索条件的 User 对象
 * @return  搜索的结果
 */
 List User  search2(User user);}

映射文件 UserDao.xml 里配置 statement

?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.itheima.dao.UserDao 
  !--
 if 标签:用于条件判断
  语法:if test= 用 OGNL 表达式判断   如果判断为 true,这里的内容会拼接上去   /if 
  注意:标签里写 OGNL 表达式,不要再加 #{}、${}
  常用的 OGNL 表达式:  比较:,  ,  =,  =, ==, !=  或者  gt, lt, gte, lte, eq, neq
  逻辑:,||,!  或者  and, or, not
  调用方法:username.length(), list.size()
 -- 
  select id= search2  resultType= User 
 select * from user where 1=1  if test= username != null and username.length() 0 
 and username like  % #{username} %   /if 
  if test= sex != null and sex.length() 0 
 and sex = #{sex}  /if 
  /select /mapper

功能测试,在测试类里加测试方法

package com.demo;import com.demo.dao.UserDao;import com.demo.domain.User;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 org.junit.After;import org.junit.Before;import org.junit.Test;import java.io.IOException;import java.io.InputStream;import java.util.List;public class SqlTest {
 private UserDao userDao;
 private SqlSession session;
 private InputStream is;
 /**
 *  要求:根据 username 和 sex 搜索用户
 *  搜索条件放到 user 对象里
 */
 @Test
 public void testSearch(){ User user = new User();
 // user.setUsername( 王 
 // user.setSex( 男 
 List User  userList = userDao.search2(user);
 userList.forEach(System.out::println);
 }

 public void init() throws IOException {  //1.  读取全局配置文件  is = Resources.getResourceAsStream( SqlMapConfig.xml  //2.  得到一个 SqlSession 对象  SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);  session = factory.openSession();  userDao = session.getMapper(UserDao.class);  }  @After  public void destroy() throws IOException { session.close();  is.close();  }}

3. where 标签语法介绍

在刚刚的练习的 SQL 语句中,我们写了 where 1=1。如果不写的话,SQL 语句会出现语法错误。Mybatis 提供了一种代替 where 1= 1 的技术:where /where 标签。

代码示例

把上一章节的实现代码进行优化,使用 where /where 标签代替 where 1=1

映射器 UserDao 的 search2 方法:已有,不用修改

/**
 *  根据 username 和 sex 搜索用户
 * @param user  封装了搜索条件的 User 对象
 * @return  搜索的结果
 */List User  search2(User user);

在映射文件 UserDao.xml 里修改 SQL 语句

!--
 where 标签:让 Mybatis 帮我们生成一个 where 关键字
 Mybatis 会智能判断:  如果一个条件都没有,就不生成 where 关键字
  如果有条件,会判断是否有多余的 and 关键字,把多余的 and 去掉
  注意:建议把所有的 where 条件都放到 where 标签里边
 -- select id= search2  resultType= User 
 select * from user  where 
  if test= username != null and username.length() 0 
 and username like  % #{username} %   /if 
  if test= sex != null and sex.length() 0 
 and sex = #{sex}  /if 
  /where /select

在测试类里进行功能测试:测试方法不需要修改

@Testpublic void testSearch(){ User user = new User();
 // user.setUsername( 王 
 // user.setSex( 男 
 List User  userList = userDao.search2(user);
 userList.forEach(System.out::println);}

4. foreach 标签语法介绍

foreach 标签,通常用于循环遍历一个集合,把集合的内容拼接到 SQL 语句中。例如,我们要根据多个 id 查询用户信息,SQL 语句:

select * from user where id = 1 or id = 2 or id = 3;select * from user where id in (1, 2, 3);

假如我们传参了 id 的集合,那么在映射文件中,如何遍历集合拼接 SQL 语句呢?可以使用 foreach 标签实现。

!--
foreach 标签:collection:被循环遍历的对象,使用 OGNL 表达式获取,注意不要加 #{}
 open:循环之前,拼接的 SQL 语句的开始部分
 item:定义变量名,代表被循环遍历中每个元素,生成的变量名
 separator:分隔符
 close:循环之后,拼接 SQL 语句的结束部分
 标签体:使用 #{OGNL} 表达式,获取到被循环遍历对象中的每个元素
-- foreach collection=  open= id in( item= id  separator= ,  close=) 
 #{id} /foreach

使用示例

有搜索条件类 QueryVO 如下:

package com.itheima.domain;public class QueryVO { private Integer[] ids;
 public Integer[] getIds() {
 return ids;
 }
 public void setIds(Integer[] ids) {
 this.ids = ids;
 }}

在映射器 UserDao 里加方法

/**
 * QueryVO 里有一个 Integer[] ids
 *  要求:根据 ids 查询对应的用户列表
 */List User  search3(QueryVO vo);

在映射文件 UserDao.xml 里配置 statement

  !--
 foreach 标签:用于循环遍历
 collection:被循环的集合 / 数组
 item:定义一个变量
 separator:定义拼接时的分隔符
 open:拼接字符串时的开始部分
 close:拼接字符串时的结束部分
  相当于  for(Integer id: ids){}
 select * from user where id in(41, 42, 45)
 -- 
  select id= search3  resultType= User 
  !--select * from user where id in(41, 42, 45)-- 
 select * from user where  foreach collection= ids  open= id in( item= id  separator= ,  close=) 
 #{id}  /foreach 
  /select

功能测试

 @Test
 public void testSearch3(){ QueryVO vo = new QueryVO();
 vo.setIds(new Integer[]{41,42,43,44,45});
 List User  userList = userDao.search3(vo);
 userList.forEach(System.out::println);
 }

5. sql 标签

在映射文件中,我们发现有很多 SQL 片段是重复的,比如:select * from user。Mybatis 提供了一个 sql 标签,把重复的 SQL 片段抽取出来,可以重复使用。

语法介绍

在映射文件中定义 SQL 片段:

sql id= 唯一标识 sql 语句片段 /sql

在映射文件中引用 SQL 片段:

include refid= sql 片段的 id /include

使用示例

在查询用户的 SQL 中,需要重复编写:select * from user。把这部分 SQL 提取成 SQL 片段以重复使用

要求:QueryVO 里有 ids,user 对象。根据条件进行搜索

修改 QueryVO,增加成员变量 user

package com.itheima.domain;/**
 * @author liuyp
 * @date 2021/09/07
 */public class QueryVO { private Integer[] ids;
 private User user;
 //get/set 方法……}

在映射器 UserDao 里加方法

 /**
 *  动态 SQL 拼接的综合应用:if、where、foreach
 *  要求:QueryVo 里有 ids、username、sex 值,根据这些值进行搜索
 */
 List User  search4(QueryVO vo);

在映射文件 UserDao.xml 里配置 statement

select id= search4  resultType= User 
  !--select * from user-- 
  include refid= selUser / 
  where 
  if test= ids != null and ids.length   0 
  foreach collection= ids  open= and id in( item= id  separator= ,  close=) 
 #{id}  /foreach 
  /if 
  !-- if test= user != null 
  if test= user.username != null and user.username.length()   0 
 and username like  % #{user.username} % 
  /if 
  if test= user.sex != null and user.sex.length()   0 
 and sex = #{user.sex}
  /if 
  /if -- 
  include refid= userCondition / 
  /where /select !--
 sql 标签:用于定义一个 sql 片段
 include 标签:什么时候要引用某个 SQL 片段,就使用 include 标签
  注意:引入 SQL 片段之后,最终的 SQL 语句必须要完全符合语法
 -- sql id= selUser select * from user /sql sql id= userCondition 
  if test= user != null 
  if test= user.username != null and user.username.length()   0 
 and username like  % #{user.username} %   /if 
  if test= user.sex != null and user.sex.length()   0 
 and sex = #{user.sex}  /if 
  /if /sql

在测试类里加测试方法

 @Test
 public void testSearch4(){ QueryVO vo = new QueryVO();
 vo.setIds(new Integer[]{41,42,43,44,45});
 // User user = new User();
 // user.setUsername( 王 
 // user.setSex( 男 
 // vo.setUser(user);
 List User  userList = userDao.search4(vo);
 userList.forEach(System.out::println);
 }

以上就是关于“MySQL 动态 SQL 拼接怎么实现”这篇文章的内容,相信大家都有了一定的了解,希望丸趣 TV 小编分享的内容对大家有帮助,若想了解更多相关的知识内容,请关注丸趣 TV 行业资讯频道。

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-07-13发表,共计6986字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)