Of Stack

Dynamic SQL query summary based on mybatis

  • 2021-10-25 06:47:22
  • OfStack

Background

Xx project needs to provide the third-party calling interface of some system functions, design the function interface based on security and avoiding exposing database table information, and provide a custom set of a table according to the authority of the third-party calling identity.

This project is based on the persistence layer framework of mybatis, and supports customized SQL, which can avoid the pain of splicing sql statements.

For example, make sure you can't add spaces when splicing, and take care to remove commas from the last column name of the list.

The mybatis framework based on OGNL expressions can completely solve this pain.

Returns dynamically the name, type, and comment of a specified column in an mysql table 


<select id="queryColumns" resultType="map" parameterType="java.util.HashMap">
    select column_name columnName, data_type dataType, column_comment columnComment
    from information_schema.columns
    where table_name = #{tablename}
    and
    column_name  in
    <foreach collection="columnsArray" item="column_name" index="index" open="(" close=")" separator=",">
         #{column_name}
     </foreach>
</select>

Dynamically query the specified column data of a table 


<select id="query1" resultType="map"  parameterType="java.util.HashMap">
    select
    <foreach collection="columnsArray" item="item" index="index" open=" " separator=", " close=" " >
          ${item}
     </foreach>
     from   #{tableName}   tn
</select>

Dynamic splicing of where conditions using if and foreach elements 


<select id="query2" resultType="map" parameterType="java.util.HashMap">
        select
        <foreach collection="columnsArray" item="item" index="index" open="" separator="," close="" >
            ${item}
        </foreach>
        from  #{tableName}   db
        where
        <if test ="name !=null">
                 db.name=#{name} and
        </if>
        db.LastModifyTime between #{datestart,jdbcType=TIMESTAMP} and #{dateend,jdbcType=TIMESTAMP}
 </select>
The difference between passing parameters ${} and # {}

In Mapper. xml statement of Mybatis, there are two ways to pass parameters from parameterType to SQL statement: # {} and ${}

We often use # {}, which is explained in general because it can prevent SQL injection. Simply put, the SQL statement is precompiled, which escapes the middle parameter of # {} into a string, for example: 


select * from student where studentName = #{name}

After precompilation, it will be dynamically parsed into 1 parameter marker? : 


select * from student where studentName = ?

When using ${} for dynamic parsing, the parameter string will be passed in 


select * from student where studentName = 'lyrics'

-After reading the above examples, we can see that if, foreach and other elements are mainly used. Before mybatis, there are many elements to know, while mybatis greatly simplifies the types of elements. Now we only need to learn the following elements:

-if

-choose(when,otherwise)

-trim(where,set)

-foreach

--Citation [http://www.mybatis.org/mybatis-3/zh/dynamic-sql. html]

if

What dynamic SQL usually does is to include part 1 of the where clause according to the condition. For example: 


<select id="findActiveBlogWithTitleLike"  resultType="Blog">
    SELECT * FROM BLOG
    WHERE state =  ' ACTIVE'
    <if test="title != null">
        AND title like #{title}
    </if>
</select>

This statement provides an optional function of finding text. If "title" is not passed in, all BLOG in the "ACTIVE" state will return; On the contrary, if "title" is passed in, the "title" 1 column will be fuzzy searched and the BLOG result will be returned (careful readers may find that the "title" parameter value can contain 1 mask or wildcard characters).

What if you want to do an optional search with the "title" and "author" parameters? First, change the name of the statement to make it more practical; Then just add another condition. 


<select id="findActiveBlogLike"  resultType="Blog">
    SELECT * FROM BLOG WHERE state =  ' ACTIVE'
    <if test="title != null">
        AND title like #{title}
    </if>
    <if test="author != null and author.name != null">
        AND author_name like #{author.name}
    </if>
</select>

choose , when , otherwise

Sometimes we don't want to apply it to all conditional statements, but only want to choose one of them. In this case, MyBatis provides the choose element, which is a bit like the switch statement in Java.

It is the same example as above, but this time it is changed to search according to "title" if "title" is provided, and search according to "author" if "author" is provided. If neither is provided, all eligible BLOG will be returned (the actual situation may be that the administrator selects the list of BLOG according to a certain policy, instead of returning a large number of meaningless random results). 


<select id="findActiveBlogLike"  resultType="Blog">
    SELECT * FROM BLOG WHERE state =  ' ACTIVE'
    <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>
</select>

trim , where , set

The previous examples have appropriately solved a notorious dynamic SQL problem. Now back to the "if" example, this time we set "ACTIVE = 1" as a dynamic condition to see what happens. 


<select id="findActiveBlogLike" resultType="Blog">
    SELECT * FROM BLOG
    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>
</select>

What happens if none of these conditions match? Eventually this SQL will become like this: 


<select id="query1" resultType="map"  parameterType="java.util.HashMap">
    select
    <foreach collection="columnsArray" item="item" index="index" open=" " separator=", " close=" " >
          ${item}
     </foreach>
     from   #{tableName}   tn
</select>
0

This causes the query to fail. What if only the second condition matches? This SQL will eventually look like this: 


<select id="query1" resultType="map"  parameterType="java.util.HashMap">
    select
    <foreach collection="columnsArray" item="item" index="index" open=" " separator=", " close=" " >
          ${item}
     </foreach>
     from   #{tableName}   tn
</select>
1

This query will also fail. This problem can't be solved simply with conditional sentences. If you have been forced to write this sentence, you will probably never write this sentence again.

MyBatis has a simple process, which is useful in 90% of cases. Where it can't be used, you can customize the processing method to make it work normally. One simple modification can achieve the goal: 


<select id="query1" resultType="map"  parameterType="java.util.HashMap">
    select
    <foreach collection="columnsArray" item="item" index="index" open=" " separator=", " close=" " >
          ${item}
     </foreach>
     from   #{tableName}   tn
</select>
2

The where element inserts the "WHERE" clause only if the condition of at least one child element returns the SQL clause. Moreover, if the statement begins with "AND" or "OR", the where element also removes them.

If the where element does not play according to the normal routine, we can customize the function of the where element by customizing the trim element. For example, the custom trim element equivalent to the where element is: 


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

The prefixOverrides attribute ignores a sequence of text separated by a pipe (note that spaces are also necessary in this example). It removes everything specified in the prefixOverrides property and inserts the content specified in the prefix property.

A similar solution for dynamically updating statements is called set. The set element can be used to dynamically include columns that need to be updated and discard others. For example: 


<select id="query1" resultType="map"  parameterType="java.util.HashMap">
    select
    <foreach collection="columnsArray" item="item" index="index" open=" " separator=", " close=" " >
          ${item}
     </foreach>
     from   #{tableName}   tn
</select>
4

Here, the set element is dynamically preceded by the SET keyword, and irrelevant commas are also deleted, because the conditional statement is likely to be followed by the generated SQL statement. Translator's Note: Because the "if" element is used, if the last "if" does not match but the previous match, there will be a comma left at the end of the SQL statement.)

If you are interested in the code for the custom trim element equivalent of the set element, this is what it really looks like: 


<select id="query1" resultType="map"  parameterType="java.util.HashMap">
    select
    <foreach collection="columnsArray" item="item" index="index" open=" " separator=", " close=" " >
          ${item}
     </foreach>
     from   #{tableName}   tn
</select>
5

Note that here we delete the suffix value and add the prefix value.

foreach

Another common operational requirement of dynamic SQL is to traverse a collection, usually when building IN conditional statements. For example: 


<select id="query1" resultType="map"  parameterType="java.util.HashMap">
    select
    <foreach collection="columnsArray" item="item" index="index" open=" " separator=", " close=" " >
          ${item}
     </foreach>
     from   #{tableName}   tn
</select>
6

The foreach element is very powerful. It allows you to specify a collection and declare the collection items (item) and index (index) variables that can be used in the element body. It also allows you to specify beginning and ending strings and place separators between iteration results. This element is very intelligent, so it does not accidentally attach redundant separators.

-Definition:

- collection There are three values of collection attribute: list, array and map. The corresponding parameter types are List, array and map. The parameters passed above are arrays, so the value is array

- item An alias representing every 1 element during the iteration

- choose0 Indicates the position (subscript) of each iteration during the iteration

- open : Prefix

- close Suffix

- separator A separator that indicates what separates each element during iteration

We can usually use it in batch deletion, addition and other operations.

Attention

You can pass any iterable object (such as List, Set, etc.), Map object, or array object to foreach as a collection parameter.

When using iterative objects or arrays, index is the number of current iterations, and the value of item is the element obtained in this iteration.

When using an Map object (or a collection of Map. Entry objects), index is the key and item is the value.

Related articles: