Back end technology: introduction to MyBatis dynamic SQL writing method

One of MyBatis's favorite features is dynamic SQL. In the process of using JDBC, splicing SQL according to conditions is very troublesome and error prone. The emergence of MyBatis dynamic SQL solves this problem.

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

1. Data preparation

For the following demonstration, a Maven project mybatis dynamic is created, and the corresponding database and tables are created

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `student_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'number',
  `name` varchar(20) DEFAULT NULL COMMENT 'full name',
  `phone` varchar(20) DEFAULT NULL COMMENT 'Telephone',
  `email` varchar(50) DEFAULT NULL COMMENT 'mailbox',
  `sex` tinyint(4) DEFAULT NULL COMMENT 'Gender',
  `locked` tinyint(4) DEFAULT NULL COMMENT 'state(0:normal,1:locking)',
  `gmt_created` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Time stored in database',
  `gmt_modified` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Time of modification',
  `delete` int(11) DEFAULT NULL,
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Student list';

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 and updating. Must be used in conjunction with the test attribute.

2.1 use if tag in WHERE condition

This is a common phenomenon. There may be many situations when we query by criteria.

2.1.1 query criteria

Carry out conditional retrieval 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, gender is used for exact matching
  • When both user name and gender exist, these two criteria are used for query matching
2.1.2 dynamic SQL

application program interface

    /**
     * Carry out conditional retrieval according to the input student information
     * 1. When only the user name is entered, the user name is used for fuzzy retrieval;
     * 2. When only mailbox is entered, gender is used for exact matching
     * 3. When both user name and gender exist, these two criteria are used for query matching
     * @param student
     * @return
     */
        List<Student> selectByStudentSelective(Student student);

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>

In this SQL statement, where 1=1 is a trick in multi condition splicing. You can use and for subsequent conditional queries.

At the same time, we added the 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>

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

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

        Student search = new Student();
        search.setName("bright");

        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("Query with name and gender");
        search.setName("bright");
        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();
    }

Query with only name, statement and result sent

The query criteria are only sent

where 1=1 and name like concat('%', ?, '%') 

Queries with only gender, statements and results sent

The query criteria are only sent

where 1=1 and sex=? 

Queries with both name and gender, statements and results sent

query criteria

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

2.2 use if tag in UPDATE column

Sometimes we don't want to update all the fields, only the changed fields.

2.2.1 update conditions

Only the changed fields are updated, and null values are not updated.

2.2.1 dynamic SQL

Interface method

    /**
     * Update non empty properties
     */
    int updateByPrimaryKeySelective(Student record);

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}
2.2.3 testing
    @Test
    public void updateByStudentSelective() {
        SqlSession sqlSession = null;
        sqlSession = sqlSessionFactory.openSession();
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);

        Student student = new Student();
        student.setStudentId(1);
        student.setName("Clearly");
        student.setPhone("13838438888");
        System.out.println(studentMapper.updateByPrimaryKeySelective(student));

        sqlSession.commit();
        sqlSession.close();
    }

give the result as follows

2.3 using if tag in INSERT dynamic insertion

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

2.3.1 insertion conditions

Only non empty attributes are inserted.

2.3.2 dynamic SQL

Interface method

    /**
     * Only non empty fields can be inserted
     */
    int insertSelective(Student record);

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>

You should be familiar with this SQL. After all, it is automatically generated.

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

        Student student = new Student();
        student.setName("Small plane");
        student.setPhone("13838438899");
        student.setEmail("xiaofeiji@qq.com");
        student.setLocked((byte) 0);

        System.out.println(studentMapper.insertSelective(student));

        sqlSession.commit();
        sqlSession.close();
    }

Corresponding results

In SQL, only non empty fields are inserted.

3. choose tag

The choose when otherwise tag can help us implement the logic of if else. A choose tag has at least one when and at most one otherwise.

The following is an example of a query.

3.1 query criteria

Assuming that name is unique, query a student

  • When studen_ When ID has a value, use student_ ID to query;
  • When studen_ When ID has no value, use name to query;
  • Otherwise, it returns null

3.2 dynamic SQL

Interface method

    /**
     * - When studen_ When ID has a value, use student_ ID to query;
     * - When studen_ When ID has no value, use name to query;
     * - Otherwise, it returns null
     */
    Student selectByIdOrName(Student record);

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>

3.3 testing

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

        Student student = new Student();
        student.setName("Small plane");
        student.setStudentId(1);

        Student studentById = studentMapper.selectByIdOrName(student);
        System.out.println("have ID Then according to ID obtain");
        System.out.println(ToStringBuilder.reflectionToString(studentById, ToStringStyle.MULTI_LINE_STYLE));

        student.setStudentId(null);
        Student studentByName = studentMapper.selectByIdOrName(student);
        System.out.println("No, ID Then according to name obtain");
        System.out.println(ToStringBuilder.reflectionToString(studentByName, ToStringStyle.MULTI_LINE_STYLE));

        student.setName(null);
        Student studentNull = studentMapper.selectByIdOrName(student);
        System.out.println("No, ID and name, return null");
        Assert.assertNull(studentNull);

        sqlSession.commit();
        sqlSession.close();
    }

If there is an ID, get the result according to the ID

If there is no ID, it is obtained by name

If there is no ID and name, null is returned

4, trim(set,where)

These three actually solve similar problems. For example, when we write the previous [use if tag in WHERE condition] SQL, we don't want the condition where 1=1 to exist.

4.1 where

4.1.1 query criteria

Carry out conditional retrieval 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, gender is used for exact matching
  • When both user name and gender exist, these two criteria are used for query matching

where 1=1 is not used.

4.1.2 dynamic SQL

Obviously, we have to solve these problems

When the conditions are not met: there should be no where in the SQL at this time, otherwise an error will occur When the if condition is satisfied: where is required in SQL, and the and | or under the first established if tag should be removed At this time, we can use the where tag.

Interface method

    /**
     * Carry out conditional retrieval according to the input student information
     * 1. When only the user name is entered, the user name is used for fuzzy retrieval;
     * 2. When only mailbox is entered, gender is used for exact matching
     * 3. When both user name and gender exist, these two criteria are used for query matching
     */
    List<Student> selectByStudentSelectiveWhereTag(Student student);

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>
4.1.3 testing
    @Test
    public void selectByStudentWhereTag() {
        SqlSession sqlSession = null;
        sqlSession = sqlSessionFactory.openSession();
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);

        Student search = new Student();
        search.setName("bright");

        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("Query with name and gender");
        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 when 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();
    }

Query with name only, where

where is the name and gender query

Query when the name and gender do not exist. At this time, where will not appear again.

4.2 set

The set tag is similar. In [2.2 use if tag in UPDATE column], if our method updateByPrimaryKeySelective is not used

4.3 trim

Both set and where are actually types of trim tags. Both functions can be implemented using trim tags.

4.3.1 trim to indicate where

For example, we can also write the where tag above

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

Indicates that where is added when the trim contains content, and the first is and or, it will be removed. If there is no content, where is not added.

4.3.2 trim to represent set

Accordingly, the set tag can be represented as follows

<trim prefix="SET" suffixOverrides=",">
</trim>

Indicates that when the trim contains content, set is added, and the last content is, it will be removed. Without content, set is not added

4.3.3 several attributes of trim

  • Prefix: when the trim element contains content, increase the prefix specified by prefix
  • prefixOverrides: when the trim element contains content, remove the prefix specified by prefixOverrides
  • Suffix: when the trim element contains content, add the suffix specified by suffix
  • suffixOverrides: when the trim element contains content, remove the suffix specified by suffixOverrides

5. foreach tag

foreach tags can implement Iterable interfaces for arrays, maps, or.

The foreach has the following properties:

  • Collection: required, name of collection / array / Map
  • item: variable name. That is, each value taken from the iterated object
  • Index: attribute name of the index. When the iterated object is Map, the value is the Key in the Map
  • open: the string at the beginning of the loop
  • close: the string at the end of the loop
  • Separator: separator for each loop

Others are easy to understand. How should the value in collection be set?

It is related to the parameters in the interface method.

1. There is only one array parameter or set parameter

Default: collection=list, and the array is collection=array

Recommendation: use @ Param to specify the name of the parameter. If @ Param("ids") precedes the parameter, fill in collection=ids

2. Multi parameter

Use @ Param to specify multiple parameters, otherwise it will be inconvenient in SQL

3. The parameter is Map

Specify as the corresponding Key in the Map. In fact, the above @ Param is finally converted into a Map.

4. Parameters are objects

You can use the attribute.

5.1 using foreach in where

It is used in the where condition, such as querying by id set, deleting by id set, etc.

5.1.1 query criteria

We want to query all user information in the user id set.

5.1.2 dynamic SQL

Function interface

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

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

result

5.2 foreach implements batch insertion

Batch insertion can be realized through foreach.

5.2.1 dynamic SQL

Interface method

    /**
     * Batch insert students
     */
    int insertList(List<Student> students);

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>
5.2.2 testing
    @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.setName("Batch 01");
        stu1.setPhone("13888888881");
        stu1.setLocked((byte) 0);
        stu1.setEmail("13888888881@138.com");
        stu1.setSex((byte) 1);
        students.add(stu1);

        Student stu2 = new Student();
        stu2.setName("Batch 02");
        stu2.setPhone("13888888882");
        stu2.setLocked((byte) 0);
        stu2.setEmail("13888888882@138.com");
        stu2.setSex((byte) 0);
        students.add(stu2);

        System.out.println(studentMapper.insertList(students));
        sqlSession.commit();
        sqlSession.close();
    }

result

6. Bind tag

The bind tag defines a context variable through an OGNL expression, which is convenient for us to use.

For example, in the selectByStudentSelective method, there are the following

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

In MySQL, the function supports multiple parameters, but in Oracle, only two parameters are supported. Then we can use bind to make the SQL support two databases

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

The changed query results are as follows

7 code

Use example:

Author: ah Jin's desk

cnblogs.com/homejim/p/9909657.html

Posted on Wed, 01 Dec 2021 12:09:40 -0500 by slawrence10