MyBatis dynamic SQL

Author: into the desk cnblogs.com/homejim/p/9909657.html o

One of MyBatis’s favorite features is dynamic SQL. When using JDBC, SQL concatenation based on conditions can be cumbersome and error-prone. MyBatis dynamic SQL, to solve this trouble.

MyBatis through OGNL to use dynamic SQL. Currently, dynamic SQL supports the following tags:

1 Data Preparation

For later demonstrations, a Maven project, Mybatis – Dynamic, was created to create the corresponding database and tables

DROP TABLE IF EXISTS `student`; CREATE TABLE 'student' (' student_id 'int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT' id ', 'name' varchar(20) DEFAULT NULL COMMENT 'iD ',' phone 'varchar(20) DEFAULT NULL COMMENT' iD ', 'email' varchar(50) DEFAULT NULL COMMENT 'email' varchar(50) DEFAULT NULL COMMENT 'email ',' sex 'tinyint(4) DEFAULT NULL COMMENT' gender ', 'locked' tinyint(4) DEFAULT NULL COMMENT 'status (0: normal,1: locked)', 'gmT_created' datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Sysdate ', 'gmt_modified' datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ' `delete` int(11) DEFAULT NULL, PRIMARY KEY (`student_id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT=' student '; Corresponding project structureCopy the code

2 if the label

The if tag is the one we use most often. It is likely to be used when querying, deleting, or updating. Must be used in conjunction with the test attribute.

2.1 Use if tags in WHERE conditions

This is a common phenomenon, and there may be a variety of situations when we conduct conditional queries.

2.1.1 Query Conditions

Conditional retrieval is performed according to the input student information

When only the user name is entered, the user name is used for fuzzy retrieval. When only gender is entered, use gender for full match When both the user name and gender exist, use these two conditions to query matching query 2.1.2 Dynamic SQL

The interface function

/** * Conditional search based on input student information * 1. Fuzzy search based on input user name; * 2. When only the email address is entered, use gender for full match * 3. * @param student * @return */ List< student > selectByStudentSelective(student student);Copy the code

Corresponding dynamic SQL

<select id="selectByStudentSelective" resultMap="BaseResultMap" parameterType="com.homejim.mybatis.entity.Student"> select <include refid="Base_Column_List" /> from student where 1=1 <if test="name ! = null and name ! =''"> and name like concat('%', #{name}, '%') </if> <if test="sex ! = null"> and sex=#{sex} </if> </select>Copy the code

SQL > select * from ‘where 1=1’ where 1=1

At the same time, we added an if tag to handle dynamic SQL

<if test="name ! = null and name ! =''"> and name like concat('%', #{name}, '%') </if> <if test="sex ! = null"> and sex=#{sex} </if>Copy the code

The test attribute value of this if tag is an OGNL compliant expression, which can be true or false. If the expression returns a value, 0 is false and non-0 is true;

2.1.3 test

@Test public void selectByStudent() { SqlSession sqlSession = null; sqlSession = sqlSessionFactory.openSession(); StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class); Student search = new Student(); Search. Elegantly-named setName (" Ming "); System.out.println(" query with name only "); List<Student> studentsByName = studentMapper.selectByStudentSelective(search); for (int i = 0; i < studentsByName.size(); i++) { System.out.println(ToStringBuilder.reflectionToString(studentsByName.get(i), ToStringStyle.MULTI_LINE_STYLE)); } search.setName(null); search.setSex((byte) 1); System.out.println(" query with gender only "); List<Student> studentsBySex = studentMapper.selectByStudentSelective(search); for (int i = 0; i < studentsBySex.size(); i++) { System.out.println(ToStringBuilder.reflectionToString(studentsBySex.get(i), ToStringStyle.MULTI_LINE_STYLE)); } system.out.println (" name and gender query "); Search. Elegantly-named setName (" Ming "); List<Student> studentsByNameAndSex = studentMapper.selectByStudentSelective(search); for (int i = 0; i < studentsByNameAndSex.size(); i++) { System.out.println(ToStringBuilder.reflectionToString(studentsByNameAndSex.get(i), ToStringStyle.MULTI_LINE_STYLE)); } sqlSession.commit(); sqlSession.close(); }Copy the code

Only the name of the query, the statement sent and the result

The query condition is only sent

where 1=1 and name like concat(‘%’, ? , ‘%’) only when the query is gender, the statement sent and the result

The query condition is only sent

where 1=1 and sex=? Queries with both names and genders, sent statements, and results

Query conditions

where 1=1 and name like concat(‘%’, ? , ‘%’) and sex=? 2.2 Use the if tag in the UPDATE column

Sometimes we don’t want to update all the fields, just the fields that have changed.

2.2.1 Update conditions

Only fields with changes are updated, null values are not updated.

2.2.1 dynamic SQL

Interface methods

/ * * * update is not empty properties * / int updateByPrimaryKeySelective (Student record);Copy the code

The corresponding SQL

<update id="updateByPrimaryKeySelective" parameterType="com.homejim.mybatis.entity.Student"> update student <set> <if test="name ! = null"> `name` = #{name,jdbcType=VARCHAR}, </if> <if test="phone ! = null"> phone = #{phone,jdbcType=VARCHAR}, </if> <if test="email ! = null"> email = #{email,jdbcType=VARCHAR}, </if> <if test="sex ! = null"> sex = #{sex,jdbcType=TINYINT}, </if> <if test="locked ! = null"> locked = #{locked,jdbcType=TINYINT}, </if> <if test="gmtCreated ! = null"> gmt_created = #{gmtCreated,jdbcType=TIMESTAMP}, </if> <if test="gmtModified ! = null"> gmt_modified = #{gmtModified,jdbcType=TIMESTAMP}, </if> </set> where student_id = #{studentId,jdbcType=INTEGER}Copy the code

2.2.3 test

2.3 Use the if tag in INSERT dynamic inserts

When we insert a record into the database, not every field has a value, but it changes dynamically. Using the if tag at this point helps us solve this problem.

2.3.1 Insertion conditions

Only non-empty attributes are inserted.

2.3.2 dynamic SQL

Interface methods

/** * int insertSelective(Student record);Copy the code

The corresponding SQL

<insert id="insertSelective" parameterType="com.homejim.mybatis.entity.Student"> insert into student <trim prefix="(" suffix=")" suffixOverrides=","> <if test="studentId ! = null"> student_id, </if> <if test="name ! = null"> `name`, </if> <if test="phone ! = null"> phone, </if> <if test="email ! = null"> email, </if> <if test="sex ! = null"> sex, </if> <if test="locked ! = null"> locked, </if> <if test="gmtCreated ! = null"> gmt_created, </if> <if test="gmtModified ! = null"> gmt_modified, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="studentId ! = null"> #{studentId,jdbcType=INTEGER}, </if> <if test="name ! = null"> #{name,jdbcType=VARCHAR}, </if> <if test="phone ! = null"> #{phone,jdbcType=VARCHAR}, </if> <if test="email ! = null"> #{email,jdbcType=VARCHAR}, </if> <if test="sex ! = null"> #{sex,jdbcType=TINYINT}, </if> <if test="locked ! = null"> #{locked,jdbcType=TINYINT}, </if> <if test="gmtCreated ! = null"> #{gmtCreated,jdbcType=TIMESTAMP}, </if> <if test="gmtModified ! = null"> #{gmtModified,jdbcType=TIMESTAMP}, </if> </trim> </insert>Copy the code

You should be familiar with this SQL, which is automatically generated.

2.3.3 test

@Test public void insertByStudentSelective() { SqlSession sqlSession = null; sqlSession = sqlSessionFactory.openSession(); StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class); Student student = new Student(); Student.setname (" little plane "); student.setPhone("13838438899"); student.setEmail("[email protected]"); student.setLocked((byte) 0); System.out.println(studentMapper.insertSelective(student)); sqlSession.commit(); sqlSession.close(); }Copy the code

Corresponding result

In SQL, only non-empty fields are inserted.

3 choose tag

The Choose When Otherwise tag helps us implement the if else logic. A choose tag has at least one WHEN and at most one otherwise.

Here is an example query.

3.1 Query Conditions

Query a student, assuming name is unique

If studen_id has a value, run the studen_id command. If studen_id does not have a value, query by name. Otherwise empty 3.2 dynamic SQL is returned

Interface methods

/** * - If studen_id has a value, use studen_id to query; * - If studen_id does not have a value, use name; * - Otherwise return null */ Student selectByIdOrName(Student Record);Copy the code

The corresponding SQL

<select id="selectByIdOrName" resultMap="BaseResultMap" parameterType="com.homejim.mybatis.entity.Student"> select <include refid="Base_Column_List" /> from student where 1=1 <choose> <when test="studentId ! = null"> and student_id=#{studentId} </when> <when test="name ! = null and name ! = ''"> and name=#{name} </when> <otherwise> and 1=2 </otherwise> </choose> </select>Copy the code

3.3 test

 @Test
    public void selectByIdOrName() {
        SqlSession sqlSession = null;
        sqlSession = sqlSessionFactory.openSession();
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);Copy the code

Student student = new Student(); Student.setname (" little plane "); student.setStudentId(1); Student studentById = studentMapper.selectByIdOrName(student); System.out.println(" if there is an ID, get by ID "); System.out.println(ToStringBuilder.reflectionToString(studentById, ToStringStyle.MULTI_LINE_STYLE)); student.setStudentId(null); Student studentByName = studentMapper.selectByIdOrName(student); System.out.println(" if no ID is available, fetch by name "); System.out.println(ToStringBuilder.reflectionToString(studentByName, ToStringStyle.MULTI_LINE_STYLE)); student.setName(null); Student studentNull = studentMapper.selectByIdOrName(student); System.out.println(" No ID or name, return null"); Assert.assertNull(studentNull); sqlSession.commit(); sqlSession.close(); }Copy the code

If there is an ID, obtain the result based on the ID

If there is no ID, the value is obtained by name

No ID or name, return NULL

4 trim (set, where)

These three actually solve similar problems. When we write SQL WHERE 1=1, we don’t want it to exist.

4.1 the where

4.1.1 Query Conditions

Conditional retrieval is performed according to the input student information.

When only the user name is entered, the user name is used for fuzzy retrieval. When only gender is entered, use gender for full match When both the user name and gender exist, use the two conditions for query. Matching The search does not use where 1=1.

4.1.2 dynamic SQL

Obviously, we need to address these issues

When conditions are not satisfied: at this time should not have the where in SQL, otherwise go wrong When the if condition is met: SQL requires the where, and established the first if label and | or to remove such as under At that time, we can use the where TAB.

Interface methods

/** * Conditional search based on input student information * 1. Fuzzy search based on input user name; * 2. When only the email address is entered, use gender for full match * 3. With these two conditions query matching with * / List < Student > selectByStudentSelectiveWhereTag (Student Student);Copy the code

The corresponding SQL

<select id="selectByStudentSelectiveWhereTag" resultMap="BaseResultMap" parameterType="com.homejim.mybatis.entity.Student"> select <include refid="Base_Column_List" /> from student <where> <if  test="name ! = null and name ! =''"> and name like concat('%', #{name}, '%') </if> <if test="sex ! = null"> and sex=#{sex} </if> </where> </select>Copy the code

4.1.3 test

@Test public void selectByStudentWhereTag() { SqlSession sqlSession = null; sqlSession = sqlSessionFactory.openSession(); StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class); Student search = new Student(); Search. Elegantly-named setName (" Ming "); System.out.println(" query with name only "); List<Student> studentsByName = studentMapper.selectByStudentSelectiveWhereTag(search); for (int i = 0; i < studentsByName.size(); i++) { System.out.println(ToStringBuilder.reflectionToString(studentsByName.get(i), ToStringStyle.MULTI_LINE_STYLE)); } search.setSex((byte) 1); System.out.println(" name and gender query "); List<Student> studentsBySex = studentMapper.selectByStudentSelectiveWhereTag(search); for (int i = 0; i < studentsBySex.size(); i++) { System.out.println(ToStringBuilder.reflectionToString(studentsBySex.get(i), ToStringStyle.MULTI_LINE_STYLE)); } system.out.println (" query if name and gender do not exist "); search.setName(null); search.setSex(null); List<Student> studentsByNameAndSex = studentMapper.selectByStudentSelectiveWhereTag(search); for (int i = 0; i < studentsByNameAndSex.size(); i++) { System.out.println(ToStringBuilder.reflectionToString(studentsByNameAndSex.get(i), ToStringStyle.MULTI_LINE_STYLE)); } sqlSession.commit(); sqlSession.close(); }Copy the code

Name only query, have where

Select * from where where name and gender exist

Select * from where where name and gender do not exist.

4.2 the set

Set label is similar, in [2.2 in the UPDATE to UPDATE the column using an if tags], if we approach updateByPrimaryKeySelective didn’t use

4.3 the trim

Set and WHERE are both types of trim tags, and both functions can be implemented using trim tags.

4.3.1 TRIM represents where

As with the where tag above, we can also write it as

4.3.2 Trim represents set

Accordingly, the set tag can be represented as follows

4.3.3 Several properties of trim

Prefix: prefix prefix prefix is added when the trim element contains content. Prefix prefix prefix prefix is removed when the trim element contains content. SuffixOverrides with suffix is added when the trim element contains content: When the trim element contains content, the suffix 5 foreach tag specified by suffixOverrides is removed

The foreach tag can be applied to arrays, maps, or implements the Iterable interface.

Foreach has the following attributes:

Collection: mandatory, name of collection/array /Map Item: name of variable That is, each value fetched from the iterated object index: the attribute name of the index. Separator: the string at the end of each loop. Separator: The string at the beginning of each loop. Separator: the string at the end of each loop.

Is related to parameters in interface methods.

There is only one array parameter or collection parameter

Default: Collection =list, array = collection=array

Recommendation: Use @param to specify the name of the parameter, such as @param (“ids”) before the parameter, then enter collection= IDS

multi-parameter

Please use @param to specify multiple parameters, otherwise it will be inconvenient in SQL

Parameter is the Map

Specify the corresponding Key in the Map. In fact, @param was converted to Map.

Parameters are objects

Use properties. Properties.

5.1 Using foreach in WHERE

Used in WHERE conditions, such as query by id set, delete by ID set, etc.

5.1.1 Query Conditions

We want to query all user information in the user ID collection.

5.1.2 dynamic SQL

The function interface

/** * get user information in id set * @param ids * @return */ List<Student> selectByStudentIdList(List<Integer> ids);Copy the code

The corresponding SQL

<select id="selectByStudentIdList" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    from student
    where student_id in
    <foreach collection="list" item="id" open="(" close=")" separator="," index="i">
      #{id}
    </foreach>
  </select>Copy the code

5.1.3 test

@Test
public void selectByStudentIdList() {
    SqlSession sqlSession = null;
    sqlSession = sqlSessionFactory.openSession();
    StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);

    List<Integer> ids = new LinkedList<>();
    ids.add(1);
    ids.add(3);

    List<Student> students = studentMapper.selectByStudentIdList(ids);
    for (int i = 0; i < students.size(); i++) {
        System.out.println(ToStringBuilder.reflectionToString(students.get(i), ToStringStyle.MULTI_LINE_STYLE));
    }

    sqlSession.commit();
    sqlSession.close();
}Copy the code

The results of

5.2 Foreach Implements batch Insert

Batch inserts can be implemented through foreach.

5.2.1 dynamic SQL

Interface methods

/** * int insertList(List<Student> students);Copy the code

The corresponding SQL

<insert id="insertList"> insert into student(name, phone, email, sex, locked) values <foreach collection="list" item="student" separator=","> ( #{student.name}, #{student.phone},#{student.email}, #{student.sex},#{student.locked}) </foreach> </insert sqlSession = null; sqlSession = sqlSessionFactory.openSession(); StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class); List<Student> students = new LinkedList<>(); Student stu1 = new Student(); Stu1. Elegantly-named setName (" batch 01 "); stu1.setPhone("13888888881"); stu1.setLocked((byte) 0); stu1.setEmail("[email protected]"); stu1.setSex((byte) 1); students.add(stu1); Student stu2 = new Student(); Stu2. Elegantly-named setName (" batch 02 "); stu2.setPhone("13888888882"); stu2.setLocked((byte) 0); stu2.setEmail("[email protected]"); stu2.setSex((byte) 0); students.add(stu2); System.out.println(studentMapper.insertList(students)); sqlSession.commit(); sqlSession.close(); The bind tag is used to define a context variable using OGNL expressions. For example, the selectByStudentSelective method is as followsCopy the code

7 code

Example:

Github.com/homejim/myb…

Concern public number: Java baodian

Related Posts

Simple HTTP call, why the delay?

This JDK source code analysis, recommended repeatedly watch, write really with detailed!

Operating system Principle Logical structure of an operating system