一起聊聊MySQL动态SQL拼接

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了关于动态SQL拼接的相关内容,实际业务开发中,我们的SQL语句通常是动态拼接而成的,比如条件搜索功能的SQL语句等等,下面一起来看一下,希望对大家有帮助。

一起聊聊MySQL动态SQL拼接

程序员必备接口测试调试工具立即使用
Apipost = Postman + Swagger + Mock + Jmeter
Api设计、调试、文档、自动化测试工具
后端、前端、测试,同时在线协作,内容实时同步

推荐学习:mysql视频教程

一、动态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> search1(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="search1" 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.search1(user);         userList.forEach(System.out::println);     }       @Before     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的search1方法:已有,不用修改

/**  * 根据username和sex搜索用户  * @param user 封装了搜索条件的User对象  * @return 搜索的结果  */List<User> search1(User user);
登录后复制

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

<!--     where标签:让Mybatis帮我们生成一个where关键字         Mybatis会智能判断:             如果一个条件都没有,就不生成where关键字             如果有条件,会判断是否有多余的and关键字,把多余的and去掉         注意:建议把所有的where条件都放到where标签里边     --><select id="search1" 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.search1(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> search2(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="search2" 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 testSearch2(){         QueryVO vo = new QueryVO();         vo.setIds(new Integer[]{41,42,43,44,45});         List<User> userList = userDao.search2(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> search3(QueryVO vo);
登录后复制

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

<select id="search3" 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 testSearch3(){         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.search3(vo);         userList.forEach(System.out::println);     }
登录后复制

推荐学习:mysql视频教程

© 版权声明
THE END
喜欢就支持一下吧
点赞9 分享
评论 抢沙发