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的拼接。

​ 常用的标签有：

• ：用来进行判断，相当于Java里的if判断
• ：通常和if配合，用来代替SQL语句中的where 1=1
• ：用来遍历一个集合，把集合里的内容拼接到SQL语句中。例如拼接：in (value1, value2, ...)
• ：用于定义sql片段，达到重复使用的目的

讲解

1. 准备Mybatis环境

1. 创建java项目，导入jar包；准备JavaBean
2. 创建映射器接口UserDao
3. 创建映射配置文件UserDao.xml
4. 创建全局配置文件SqlMapConfig.xml
5. 创建日志配置文件log4j.properties

2. 标签：

语法介绍

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

使用示例

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

1. 映射器接口UserDao上加方法

package com.demo.dao;import com.demo.domain.User;import java.util.List;public interface UserDao {        List<User> search1(User user);}

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

DOCTYPE mapper        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"        "http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.itheima.dao.UserDao">        <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>

1. 功能测试，在测试类里加测试方法

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;        @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. 标签

语法介绍

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

代码示例

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

1. 映射器UserDao的search1方法：已有，不用修改

List<User> search1(User user);

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

<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>

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

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

4. 标签

语法介绍

​ 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="" open="id in(" item="id" separator="," close=")">    #{id}foreach>

使用示例

1. 有搜索条件类QueryVO如下：

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

1. 在映射器UserDao里加方法

List<User> search2(QueryVO vo);

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

        <select id="search2" resultType="User">                select * from user where        <foreach collection="ids" open="id in(" item="id" separator="," close=")">            #{id}        foreach>    select>

1. 功能测试

    @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片段是重复的，比如：select * from user。Mybatis提供了一个标签，把重复的SQL片段抽取出来，可以重复使用。

语法介绍

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

<sql id="唯一标识">sql语句片段sql>

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

<include refid="sql片段的id">include>

使用示例

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

• 要求：QueryVO里有ids，user对象。根据条件进行搜索

1. 修改QueryVO，增加成员变量user

package com.itheima.domain;public class QueryVO {    private Integer[] ids;    private User user;    //get/set方法……}

1. 在映射器UserDao里加方法

        List<User> search3(QueryVO vo);

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

<select id="search3" resultType="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>                <include refid="userCondition"/>    where>select><sql id="selUser">select * from usersql><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>

1. 在测试类里加测试方法

    @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