Let's talk about MySQL dynamic SQL splicing

This article brings you relevant knowledge aboutmysql, 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.

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
• : Usually used in conjunction with if to replacewhere 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, ...)
• : 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:

 SQL语句内容， 如果判断为true，这里的SQL语句就会进行拼接

Copy after login

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 search1(User user);}

  Copy after login

Configure statement in the mapping file UserDao.xml

•    select * from user where 1=1  and username like "%"#{username}"%"   and sex = #{sex}  

  Copy after login

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 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(); }}

  Copy after login

3. Tags

Grammar introductionIn the SQL statement we just practiced, we wrote

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

Code exampleOptimize the implementation code in the previous chapter and use

tag insteadwhere 1=1

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

• /** * 根据username和sex搜索用户 * @param user 封装了搜索条件的User对象 * @return 搜索的结果 */List search1(User user);

  Copy after login

Modify the SQL statement in the mapping file UserDao.xml

•  select * from user   and username like "%"#{username}"%"   and sex = #{sex}  

  Copy after login

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 userList = userDao.search1(user); userList.forEach(System.out::println);}

  Copy after login

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

tag.

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

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

  Copy after login

Add method to mapper UserDao

• /** * QueryVO里有一个Integer[] ids * 要求：根据ids查询对应的用户列表 */List search2(QueryVO vo);

  Copy after login

Configure statement in mapping file UserDao.xml

•    select * from user where  #{id}  

  Copy after login

Functional Test

• @Test public void testSearch2(){ QueryVO vo = new QueryVO(); vo.setIds(new Integer[]{41,42,43,44,45}); List userList = userDao.search2(vo); userList.forEach(System.out::println); }

  Copy after login

5.TagIn the mapping file , we found that there are many SQL fragments that are repeated, such as:

. Mybatis provides atag to extract repeated SQL fragments and can be reused.

语法介绍

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

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

package com.itheima.domain;/** * @author liuyp * @date 2021/09/07 */public class QueryVO { private Integer[] ids; private User user; //get/set方法……}

/** * 动态SQL拼接的综合应用：if、where、foreach * 要求：QueryVo里有ids、username、sex值，根据这些值进行搜索 */ List search3(QueryVO vo);

      #{id}     select * from user   and username like "%"#{user.username}"%"   and sex = #{user.sex}  

@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 userList = userDao.search3(vo); userList.forEach(System.out::println); }