Introduction

Dynamic SQL: dynamic SQL.

In this section, we will learn from the official documents of MyBatis.

Description

The official description is as follows:

One of the most powerful features of MyBatis has always been its Dynamic SQL capabilities. If you have any experience with JDBC or any similar framework, you understand how painful it is to conditionally concatenate strings of SQL together, making sure not to forget spaces or to omit a comma at the end of a list of columns. Dynamic SQL can be downright painful to deal with.

While working with Dynamic SQL will never be a party, MyBatis certainly improves the situation with a powerful Dynamic SQL language that can be used within any mapped SQL statement.

The Dynamic SQL elements should be familiar to anyone who has used JSTL or any similar XML based text processors. In previous versions of MyBatis, there were a lot of elements to know and understand. MyBatis 3 greatly improves upon this, and now there are less than half of those elements to work with. MyBatis employs powerful OGNL based expressions to eliminate most of the other elements:

● if ● choose (when, otherwise) ● trim (where, set) ● foreach

My View

In conclusion, there are three points:

• MyBatis is powerful in dynamic SQL.

• You need to know OGNL expression

• if/choose/trim/foreach

OGNL

See here for usage: OGNL Guide

In addition, escape characters are supported, so take a look at escape:

if/where/trim/choose/foreach

• if stands for judgment

  <if test="title != null">
    AND title like #{title}
  </if>

• where

We always write AND in the if tag, so when the first one doesn't exist, there will be a problem: sql: ...WHERE AND ...

So where is the solution

  <where>
    <if test="state != null">
         state = #{state}
    </if>
    <if test="title != null">
        AND title like #{title}
    </if>
    <if test="author != null and author.name != null">
        AND author_name like #{author.name}
    </if>
  </where>

• trim

Someone will say, since there are problems in writing at the front, can we write at the back? Certainly.

Let's look at the usage of trim:

<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ...
</trim>

-----

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

To summarize: prefix: what's in front prefixOverrides: what was previously ignored Suffix: suffix suffixOverrides: what is ignored after

• choose

  <choose>
    <when test="title != null">
      AND title like #{title}
    </when>
    <when test="author != null and author.name != null">
      AND author_name like #{author.name}
    </when>
    <otherwise>
      AND featured = 1
    </otherwise>
  </choose>

• foreach

Traverses a collection.

  WHERE ID in
  <foreach item="item" index="index" collection="list"
      open="(" separator="," close=")">
        #{item}
  </foreach>

Epilogue

Today, I'll come here first, then I'll add.

Test code: DynamicSQL-Demo

It should be noted that in order to maintain the test style, some return value types are not correct, resulting in test errors, but this does not affect the learning of the section, because you can see sql and results.