MySQL动态SQL拼接实例详解

作者:一切如你i 时间:2024-01-20 15:13:55 

目标

能够使用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>

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

@Test
public 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);
   }

来源:https://blog.csdn.net/yiqieruni/article/details/127617481

标签:mysql,动态拼接,sql语句
0
投稿

猜你喜欢

  • Go语言题解LeetCode705设计哈希集合

    2024-03-19 22:30:38
  • python实现无边框进度条的实例代码

    2023-07-23 05:22:59
  • python组合无重复三位数的实例

    2021-11-18 03:59:13
  • Go语言题解LeetCode463岛屿的周长示例详解

    2024-04-25 15:09:59
  • python3实现表白神器

    2023-07-27 03:54:01
  • 如何用python 实现老板键功能

    2022-09-30 10:13:25
  • 游戏开发进阶Unity网格(Mesh\\动态合批\\骨骼动画\\蒙皮)

    2022-03-18 11:20:26
  • Laravel 微信小程序后端实现用户登录的示例代码

    2024-06-05 15:40:52
  • 利用Django-environ如何区分不同环境

    2022-01-10 10:52:24
  • SQL查询入门(中篇)

    2011-09-30 11:15:09
  • Python 京东云无线宝消息推送功能

    2021-08-19 09:45:57
  • PHP对象克隆clone用法示例

    2024-05-02 17:33:51
  • 详解OpenCV中直方图,掩膜和直方图均衡化的实现

    2022-10-30 12:03:33
  • 详解Oracle数据库各类控制语句的使用

    2024-01-13 16:49:00
  • pytest中配置文件pytest.ini使用

    2021-01-22 17:04:02
  • iis7 ASP+Access数据库连接错误

    2011-03-08 10:41:00
  • matplotlib quiver箭图绘制案例

    2023-07-13 10:04:11
  • ThinkPHP中自定义错误页面和提示页面实例

    2024-05-02 17:16:21
  • 十分钟利用Python制作属于你自己的个性logo

    2021-08-25 05:12:41
  • 使用FORFILES命令来删除SQLServer备份的批处理

    2012-05-08 06:47:06
  • asp之家 网络编程 m.aspxhome.com