kavin

MySQL动态SQL拼接实例详解

kavin web技术 2023-02-27 3524浏览 1195

目标

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

总结

继续浏览有关 数据库技术文章/教程 的文章
1195条评论
  • 游客 发表于 1周前 回复

    语言表达流畅,没有冗余,读起来很舒服。http://qcn.zststn.cn

  • 游客 发表于 1周前 回复

    好帖子!http://2e2t.mojushijie.com

  • 游客 发表于 1周前 回复

    看了这么多帖子,第一次看到这么有深度了!http://41w6.xinhe-iso.com

  • 游客 发表于 1周前 回复

    看在楼主的面子上,认真回帖!http://q60aad.data1688.cn

  • 游客 发表于 1周前 回复

    今天是个特别的日子,值得纪念!http://sff4g.zststn.cn

  • 游客 发表于 1周前 回复

    网页的加载速度非常快,不会影响用户体验。http://www.gymcj.cn

  • 游客 发表于 1周前 回复

    楼主是一个典型的文艺青年啊!http://auyjo.data1688.cn

  • 游客 发表于 1周前 回复

    突然觉得楼主说的很有道理,赞一个!http://c8ypkw.haosina.com

  • 游客 发表于 1周前 回复

    有内涵!http://prm.tjjyfs.com

  • 游客 发表于 1周前 回复

    网站做得不错http://6enw.menhuzhan.net

  • 游客 发表于 1周前 回复

    我就搞不明白了,看帖回帖能死人么,居然只有我这么认真的在回帖!http://a2wyk6.0519srdz.com

  • 游客 发表于 1周前 回复

    管它三七二十一!http://nbhe9.lirendapei.com/01/3.html

  • 游客 发表于 1周前 回复

    赞一个!http://q9807a.chygjs.com

  • 游客 发表于 1周前 回复

    太邪乎了吧?http://8ku29.chygjs.com

  • 游客 发表于 1周前 回复

    楼主的帖子实在是写得太好了。文笔流畅,修辞得体!http://wyhe.yunzongcn.com

  • 游客 发表于 1周前 回复

    好东西,赞一个!http://sl27.mojushijie.com

  • 游客 发表于 1周前 回复

    楼主英明!http://ey11k.jxbdcdj.org

  • 游客 发表于 1周前 回复

    看在楼主的面子上,认真回帖!http://0xc1a.color023.com

  • 游客 发表于 1周前 回复

    楼主内心很强大!http://2stxi.fjshsywz.com/E/3.html

  • 游客 发表于 1周前 回复

    强,我和我的小伙伴们都惊呆了!http://ruk0.sensenkd.cn

  • 游客 发表于 1周前 回复

    被楼主的逻辑打败了!http://emv53.hm130.com/07/4.html

  • 游客 发表于 1周前 回复

    网站做得不错http://rn3s.hsmgzn.com

  • 游客 发表于 1周前 回复

    好好学习楼主的帖子!http://dpjsek.3rombos.com

  • 游客 发表于 1周前 回复

    看帖不回帖都是耍流氓!http://ztd7o.xmona.com.cn

  • 游客 发表于 1周前 回复

    楼主是我最崇拜的人!http://nrpnf.253fe.com/01/3.html

  • 游客 发表于 1周前 回复

    文章论点明确,论据充分,说服力强。http://www.hntbhz.cn/post/46.html

  • 游客 发表于 1周前 回复

    信楼主,得永生!http://1o801.lnscwhyjh.com

  • 游客 发表于 1周前 回复

    没人理我,好伤心啊!http://prohockeyiq.com/news/84c099465.html

  • 访客 发表于 1周前 回复

    安福相册精准查找 https://z11.cn

  • 游客 发表于 1周前 回复

    楼上的能详细介绍一下么?http://02b9r.lkjmpcb.com/01/4.html

  • 游客 发表于 1周前 回复

    不是惊喜,是惊吓!http://6ls.wms1688.com

  • 访客 发表于 1周前 回复

    安福相册 https://z11.cn

  • 游客 发表于 1周前 回复

    有机会找楼主好好聊聊!http://4c5x.yunzongcn.com

  • 游客 发表于 1周前 回复

    雷锋做好事不留名,都写在帖子里!http://adstone.cn/news/76a099473.html

  • 游客 发表于 1周前 回复

    关注一下!http://yunzongcn.com/news/09d399630.html

  • 游客 发表于 1周前 回复

    最近压力山大啊!http://ova99s.yunzongcn.com

  • 游客 发表于 1周前 回复

    视死如归的架势啊!http://a0em2.changjiaguo.com/20240712/3.html

  • 游客 发表于 1周前 回复

    我默默的回帖,从不声张!http://d8e4h.v51888888.com

  • 游客 发表于 1周前 回复

    顶一下,收藏了!http://decidamoda.com/news/25c099524.html

  • 游客 发表于 1周前 回复

    有内涵!http://99kang.cn/news/34e399605.html

  • 游客 发表于 1周前 回复

    今天的心情很不错啊http://www.kdhlpt.com/yaowen/589666527.html

  • 游客 发表于 1周前 回复

    看帖、回帖、拿分、走人http://tor14.yunzongcn.com

  • 游客 发表于 1周前 回复

    太高深了,理解力不够用了!http://www.kdhlpt.com/yaowen/880180574.html

  • 游客 发表于 1周前 回复

    楼主是我最崇拜的人!http://www.kdhlpt.com/yaowen/750945131.html

  • 游客 发表于 1周前 回复

    写的太好啦,评论一个http://etijian.com/news/89c399550.html

  • 游客 发表于 1周前 回复

    回帖也有有水平的!http://www.kdhlpt.com/yaowen/868539558.html

  • 游客 发表于 1周前 回复

    世界末日我都挺过去了,看到楼主我才知道为什么上帝留我到现在!http://www.kdhlpt.com/yaowen/190191539.html

  • 游客 发表于 1周前 回复

    支持一下!http://www.kdhlpt.com/yaowen/938211006.html

  • 游客 发表于 1周前 回复

    以后要跟楼主好好学习学习!http://pfzk.lvdouya.net/test/708078265.html

  • 游客 发表于 1周前 回复

    我只是来赚积分的!http://nsji.lvdouya.net/test/697107272.html

  • 游客 发表于 1周前 回复

    收藏了,楼主加油!http://ukyt.hangboss.net/test/680455975.html

  • 游客 发表于 1周前 回复

    楼主你想太多了!http://ricg.hangboss.net/test/637305337.html

  • 游客 发表于 1周前 回复

    太高深了,理解力不够用了!http://rzek.hangboss.net/test/355368358.html

  • 游客 发表于 1周前 回复

    读了楼主的帖子,顿时马桶就通了。。。http://wtvk.hangboss.net/test/427228005.html

  • 游客 发表于 1周前 回复

    白富美?高富帅?http://ljjl.hangboss.net/test/342932946.html

  • 游客 发表于 1周前 回复

    听楼主一席话,省我十本书!http://gmch.hangboss.net/test/972114333.html

  • 游客 发表于 1周前 回复

    楼上的真不讲道理!http://ukir.hangboss.net/test/394838746.html

  • 游客 发表于 7天前 回复

    雷锋做好事不留名,都写在帖子里!http://xusf.hangboss.net/test/719863765.html

  • 游客 发表于 7天前 回复

    今天是个特别的日子,值得纪念!http://azym.hangboss.net/test/021309673.html

  • 游客 发表于 7天前 回复

    好东西,学习学习!http://vgyr.lvdouya.net/test/957010065.html

  • 游客 发表于 7天前 回复

    好好学习楼主的帖子!http://gsvk.lvdouya.net/test/783037979.html

  • 游客 发表于 7天前 回复

    楼上的心情不错啊!http://ydjy.hangboss.net/test/377246378.html

  • 游客 发表于 7天前 回复

    楼主最近很消极啊!http://www.hntbhz.cn/post/26.html

  • 游客 发表于 7天前 回复

    收藏了,很不错的内容!http://jjor.hangboss.net/test/937371944.html

  • 游客 发表于 7天前 回复

    求加金币!http://mork.hangboss.net/test/739749087.html

  • 游客 发表于 7天前 回复

    信楼主,得永生!http://uewr.hangboss.net/test/496830181.html

  • 游客 发表于 7天前 回复

    看了这么多帖子,第一次看到这么高质量内容!http://ruve.hangboss.net/test/629420653.html

  • 游客 发表于 7天前 回复

    有品位!http://lzod.lvdouya.net/test/535272757.html

  • 游客 发表于 7天前 回复

    我裤子脱了,纸都准备好了,你就给我看这个?http://lolu.hangboss.net/test/776042902.html

  • 游客 发表于 7天前 回复

    看帖不回帖都是耍流氓!http://iuacc0.jzsocialvip.com

  • 游客 发表于 7天前 回复

    无图无真相!http://koft.hangboss.net/test/737817448.html

  • 游客 发表于 7天前 回复

    顶!顶!顶!http://yabn.lvdouya.net/test/867111958.html

  • 游客 发表于 7天前 回复

    写的太好啦,评论一个http://jlid.hangboss.net/test/698442528.html

  • 游客 发表于 7天前 回复

    这么版块的帖子越来越有深度了!http://upai.hangboss.net/test/442430362.html

  • 游客 发表于 7天前 回复

    楼主的帖子提神醒脑啊!http://xogr.lvdouya.net/test/917951452.html

  • 游客 发表于 7天前 回复

    论坛的人气不行了!http://apym.hangboss.net/test/736236159.html

  • 游客 发表于 7天前 回复

    这么经典的话只有楼主能想到!http://jztg.hangboss.net/test/961225856.html

  • 游客 发表于 7天前 回复

    顶!顶!顶!http://3hw5v.ekor-metal.com/01/3.html

  • 游客 发表于 7天前 回复

    每次看到楼主的帖子都有惊吓!http://jaid.lvdouya.net/test/059003079.html

  • 游客 发表于 7天前 回复

    楼主发几张靓照啊!http://yitd.hangboss.net/test/677168761.html

  • 游客 发表于 7天前 回复

    终于看完了,很不错!http://nmre.hangboss.net/test/178437730.html

  • 游客 发表于 7天前 回复

    楼主发几张靓照啊!http://cujz.lvdouya.net/test/188867981.html

  • 游客 发表于 7天前 回复

    青春不在了,青春痘还在!http://xeto.hangboss.net/test/346171652.html

  • 游客 发表于 7天前 回复

    写的太好啦,评论一个http://xipt.hangboss.net/test/800106112.html

  • 游客 发表于 7天前 回复

    楼上的别说的那么悲观好吧!http://rzgy.hangboss.net/test/301311896.html

  • 游客 发表于 7天前 回复

    楼上的真不讲道理!http://d92oay.gangqinnet.com

  • 游客 发表于 7天前 回复

    视死如归的架势啊!http://fvjx.hangboss.net/test/096567066.html

  • 游客 发表于 7天前 回复

    文章写太挺好了,真的值得推荐http://thpj.hangboss.net/test/914203398.html

  • 游客 发表于 7天前 回复

    坚持回帖!http://tfac.hangboss.net/test/723766198.html

  • 游客 发表于 7天前 回复

    听楼主一席话,省我十本书!http://iwqb.hangboss.net/test/641499224.html

  • 游客 发表于 7天前 回复

    我对楼主的敬仰犹如滔滔江水绵延不绝!http://igwp.lvdouya.net/test/984053700.html

  • 游客 发表于 7天前 回复

    楼主是男的还是女的?http://hwjj.lvdouya.net/test/295621413.html

  • 游客 发表于 7天前 回复

    赞一个!http://oojm.hangboss.net/test/850921082.html

  • 游客 发表于 7天前 回复

    楼主的头像能辟邪啊!http://4eo9h.sh-hangte.com/01/3.html

  • 游客 发表于 7天前 回复

    楼主的头像能辟邪啊!http://mkqx.lvdouya.net/test/742718052.html

  • 游客 发表于 7天前 回复

    观点鲜明,立场坚定,作者态度明确。http://ykac.lvdouya.net/test/476392987.html

  • 游客 发表于 7天前 回复

    顶一下,收藏了!http://kgzz.hangboss.net/test/419234198.html

  • 游客 发表于 7天前 回复

    灌水不是我的目的!http://pk6.sh-hangte.com