MyBatis dynamic SQL, write SQL more cool

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. Compiled a 272 page MybatisPDF document

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

Add a description

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 ';Copy the code

Corresponding project structure

2. If tag

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.
Use gender for full matching when only gender is entered
If both the user name and gender exist, the two conditions are used to match the 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 the if tag to handle dynamic SQL and put together a 272-page MybatisPDF document

<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('%', ? , '%')Copy the code

Gender only queries, sent statements and results

The query condition is only sent

where 1=1 and sex=? 
Copy the code

Queries with both names and genders, sent statements, and results

Query conditions

where 1=1 and name like concat('%', ? , '%') and sex=?Copy the code

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

@Test public void updateByStudentSelective() { SqlSession sqlSession = null; sqlSession = sqlSessionFactory.openSession(); StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class); Student student = new Student(); student.setStudentId(1); Student. Elegantly-named setName (" Ming Ming "); student.setPhone("13838438888"); System.out.println(studentMapper.updateByPrimaryKeySelective(student)); sqlSession.commit(); sqlSession.close(); }Copy the code

The results are as follows

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

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

3.2 dynamic SQL

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

Without ID, a 272-page MybatisPDF document was 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.
Use gender for full matching when only gender is entered
If both the user name and gender exist, the two conditions are used to match the query

Do 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

<trim prefix="where" prefixOverrides="AND |OR">
</trim>
Copy the code

If trim contains content, add WHERE. If the first trim is AND or OR, the trim is removed. If there is no content, where is not added.

4.3.2 Trim represents set

Accordingly, the set tag can be represented as follows

<trim prefix="SET" suffixOverrides=",">
</trim>
Copy the code

If the trim field contains content, add set, and the final trim field is, the trim field is removed. Without content, no set is added

4.3.3 Several properties of trim

Prefix: Adds the prefix specified by prefix when the trim element contains content
PrefixOverrides: Removes the prefix specified in the prefixOverrides when the trim element contains content
Suffix: Add the suffix specified when the trim element contains content
SuffixOverrides: When the trim element contains content, the suffixOverrides suffix is removed

5. Foreach tags

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

Foreach has the following attributes:

Collection: Mandatory, the name of collection/array /Map.
Item: variable name. That is, each value extracted from the iterated object
Index: indicates the attribute name of the index. When the iterated object is a Map, the value is the Key in the Map.
Open: string at the beginning of a loop
Close: the string at the end of the loop
Separator: Separates each loop

Others are easier to understand. How should values in a collection be set?

Is related to parameters in interface methods.

1. Only one array argument or collection argument

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

2. Many parameters

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

3. The parameter is Map

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

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

Add a description

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>
Copy the code

5.2.2 test

@Test public void insertList() { SqlSession 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(); }Copy the code

The results of

6. Bind tag

The bind tag is a variable that defines a context through an OGNL expression that makes it easier to use.

For example, the selectByStudentSelective method is as follows

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

In MySQL, this function supports multiple arguments, but only two arguments are supported in Oracle. Bind can then be used to enable the SQL to support both databases

<if test="name ! = null and name ! =''"> <bind name="nameLike" value="'%'+name+'%'"/> and name like #{nameLike} </if>Copy the code

The changed query results are as follows: a 272-page MybatisPDF document is compiled

7, code,

Example:

Github.com/homejim/myb…

MyBatis dynamic SQL, write SQL more cool

1. Data preparation

2. If tag

2.1 Use if tags in WHERE conditions

2.1.1 Query Conditions

2.1.2 dynamic SQL

2.1.3 test

2.2 Use the if tag in the UPDATE column

2.2.1 Update conditions

2.2.2 dynamic SQL

2.2.3 test

2.3 Use the if tag in INSERT dynamic inserts

2.3.1 Insertion conditions

2.3.2 dynamic SQL

2.3.3 test

3. Choose the label

3.1 Query Conditions

3.2 dynamic SQL

3.3 test

4, Trim (set, where)

4.1 the where

4.1.1 Query Conditions

4.1.2 dynamic SQL

4.1.3 test

4.2 the set

4.3 the trim

4.3.1 TRIM represents where

4.3.2 Trim represents set

4.3.3 Several properties of trim

5. Foreach tags

1. Only one array argument or collection argument

2. Many parameters

3. The parameter is Map

4. Parameters are objects

5.1 Using foreach in WHERE

5.1.1 Query Conditions

5.1.2 dynamic SQL

5.1.3 test

5.2 Foreach Implements batch Insert

5.2.1 dynamic SQL

5.2.2 test

6. Bind tag

7, code,

Example:

Related Posts

That’s what a programmer’s circle of friends is all about, doing nine-grid photos in Python

TS + Nodejs: connects/manipulates the database

Gnawing concurrency (II) : The life cycle of Java threads