search

Home  >  Article  >  Database  >  Let's talk about MySQL dynamic SQL splicing

Let's talk about MySQL dynamic SQL splicing

WBOY
WBOYforward
2022-12-01 17:20:351965browse

This article brings you relevant knowledge about mysql, which mainly introduces the relevant content about dynamic SQL splicing. In actual business development, our SQL statements are usually dynamically spliced. , such as the SQL statement of the conditional search function, etc. Let’s take a look at it together. I hope it will be helpful to everyone.

Let's talk about MySQL dynamic SQL splicing

Recommended learning: mysql video tutorial

1. Dynamic sql splicing

Goal

  • Be able to use mybatis tags to implement dynamic SQL splicing

Analysis

In the previous learning process, the SQL statements we used were very simple. In actual business development, our SQL statements are usually spliced ​​dynamically, such as SQL statements for conditional search functions. 

# 提供了一个功能：用户可以在页面上根据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 '%北京%';

In Mybatis, SQL statements are written in the XML file of the mapping configuration. Mybatis provides some XML tags to implement dynamic SQL splicing.

Commonly used tags are:

  • ##: used for judgment, equivalent to if judgment in Java
  • <where></where>: Usually used in conjunction with if to replace where 1=1
    • ## in the SQL statement #
  • : Used to traverse a collection and splice the contents of the collection into SQL statements. For example, splicing: in (value1, value2, ...)
    • <sql>
  • : used to define sql fragments for reuse Purpose

Explanation

1. Prepare the Mybatis environment

    Create java project, import jar package; prepare JavaBean

    • Create mapper interface UserDao

    • Create mapping Configuration file UserDao.xml

    • Create global configuration file SqlMapConfig.xml

    • Create log configuration file log4j.properties

2. Tags:

Grammar introduction
<if test="判断条件，使用OGNL表达式进行判断">
	SQL语句内容， 如果判断为true，这里的SQL语句就会进行拼接</if>
Usage Example
    Search user information based on the user's name and gender. Put the search conditions into the User object and pass it to the SQL statement

    • Add methods to the mapper interface 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);}
    Configure statement in the mapping file UserDao.xml

  • <?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>
    Functional test, add test method in the test class

  • 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. Tags

Grammar introductionIn the SQL statement we just practiced, we wrote
where 1=1

. If you do not write it, the SQL statement will have syntax errors. Mybatis provides a technology that replaces where 1=1: <where></where> tag.

Code exampleOptimize the implementation code in the previous chapter and use
<where></where>

tag insteadwhere 1=1

    The search1 method of mapper UserDao: existing, no need to modify

  • /**
 * 根据username和sex搜索用户
 * @param user 封装了搜索条件的User对象
 * @return 搜索的结果
 */List<User> search1(User user);
    Modify the SQL statement in the mapping file UserDao.xml

  • <!--
    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>
    Perform functional testing in the test class: the test method does not need to be modified

  • @Testpublic void testSearch(){
    User user = new User();
    // user.setUsername("王");
    // user.setSex("男");

    List<User> userList = userDao.search1(user);
    userList.forEach(System.out::println);}

4. tag

Syntax introductionforeach tag, usually Used to loop through a collection and splice the contents of the collection into SQL statements. For example, we need to query user information based on multiple IDs. The SQL statement is: 
select * from user where id = 1 or id = 2 or id = 3;select * from user where id in (1, 2, 3);

If we pass a collection of IDs, how do we traverse the collection and splice the SQL statement in the mapping file? This can be achieved using the

foreach

tag. <pre class="brush:php;toolbar:false">&lt;!-- foreach标签： 属性： collection：被循环遍历的对象，使用OGNL表达式获取，注意不要加#{} open：循环之前，拼接的SQL语句的开始部分 item：定义变量名，代表被循环遍历中每个元素，生成的变量名 separator：分隔符 close：循环之后，拼接SQL语句的结束部分 标签体： 使用#{OGNL}表达式，获取到被循环遍历对象中的每个元素 --&gt;&lt;foreach collection=&quot;&quot; open=&quot;id in(&quot; item=&quot;id&quot; separator=&quot;,&quot; close=&quot;)&quot;&gt;     #{id}&lt;/foreach&gt;</pre>

Usage example
    The search condition class QueryVO is as follows:

  • package com.itheima.domain;public class QueryVO {
    private Integer[] ids;

    public Integer[] getIds() {
        return ids;
    }

    public void setIds(Integer[] ids) {
        this.ids = ids;
    }}
    Add method to mapper UserDao

  • /**
     * QueryVO里有一个Integer[] ids
     * 要求：根据ids查询对应的用户列表
     */List<User> search2(QueryVO vo);
    Configure statement in mapping file UserDao.xml

  •     <!--
    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>
    Functional Test

  •     @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>TagIn the mapping file , we found that there are many SQL fragments that are repeated, such as:

select * from user

. Mybatis provides a <sql> tag to extract repeated SQL fragments and can be reused. <h5> <a id="_326"></a>语法介绍</h5> <p>在映射文件中定义SQL片段：</p> <pre class="brush:php;toolbar:false">&lt;sql id=&quot;唯一标识&quot;&gt;sql语句片段&lt;/sql&gt;</pre> <p>在映射文件中引用SQL片段：</p> <pre class="brush:php;toolbar:false">&lt;include refid=&quot;sql片段的id&quot;&gt;&lt;/include&gt;</pre> <h5> <a id="_340"></a>使用示例</h5> <p>在查询用户的SQL中，需要重复编写：<code>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 above is the detailed content of Let's talk about MySQL dynamic SQL splicing. For more information, please follow other related articles on the PHP Chinese website!

Java sql mysql log4j mybatis if foreach 成员变量 select xml 循环 接口 对象
Statement：
This article is reproduced at:csdn.net. If there is any infringement, please contact admin@php.cn delete
Previous article：13 keywords you must know to learn MySQL (summary sharing)Next article：13 keywords you must know to learn MySQL (summary sharing)

Related articles

See more