Sample code for Mybatis Dynamic SQL

Basic catalog process IF, Where Set Choose(when,otherwise) SQL fragment Summarize

What is Dynamic SQL: Dynamic SQL is the generation of different SQL statements based on different conditions

Basic process

1. Prepare a table for the database
2. Guide package
3. Write core configuration files
4. Write entity classes
5. Write Mapper and Mapper. xml files corresponding to entity classes
6. Register Mapper. xml in the core configuration file
7. Test

Turn on automatic hump naming convention mapping

    <!-- Turn on hump naming mapping -->
    <setting name="mapUnderscoreToCamelCase" value="true"/>

That is, create_time corresponds to Java entity class attribute createTime in the database

IF, Where

    <select id="queryListIf" parameterType="map" resultType="Blog">
        select * from blog 
        <where>
            <if test="title != null">
             title = #{title}
         </if>
         <if test="author != null">
             and author = #{author}
         </if>
        </where>
      </select>

Function of Where: Add Where when at least one satisfies the condition, and the first statement added later will be judged. If and starts, this and will be deleted automatically
Essentially splicing SQL, querying all in the blog table when the condition is not met, and splicing SQL when the condition is met

Set

 <update id="updateBlog" parameterType="map">
        update blog
        <set>
            <if test="title != null">
                title = #{title},
            </if>
            <if test="author != null">
                author = #{author}
            </if>
        </set>
        where id = #{id}
    </update>

Function of Set: Add Set when at least one condition is met, and the last statement added after it will be judged. If "," ends, this "," will be deleted automatically

Choose(when,otherwise)

    <select id="queryNoLimit" parameterType="map" resultType="Blog">
        select * from blog
        <where>
            <choose>
                <when test="title != null">
                    title = #{title}
                </when>
                <when test="author != null">
                    and author = #{author}
                </when>
                <otherwise>
                    and `view` = #{view}
                </otherwise>
            </choose>
        </where>
    </select>

choose (when, otherwise) is similar to switch (case, default) in Java, choose enters the selection, when judges the condition when the condition is met, and if the condition is met, the contents in the condition are executed, the following when and otherwise will no longer be executed, and otherwise will be executed when all when do not meet the condition

ForEach

    <select id="queryBlogById" parameterType="map" resultType="blog">
        select * from blog
        <where>
            <foreach collection="ids" item="id" open="(" close=")" separator="or">
                id = #{id}
            </foreach>
        </where>
    </select>

As mentioned above, one set ids stores the contents of id. According to this set, the contained id is queried, open is the beginning, close is the end, and separator is the separator
Import the collection as map. put ("ids", list)

Suggestion: Now write the complete sql in Mysql, and then modify the corresponding one

SQL fragment

Extract some parts of some functions to facilitate reuse

Extract common parts using SQL tags

    <sql id="titleAuthor">
        <if test="title != null">
            title = #{title}
        </if>
        <if test="author != null">
            and author = #{author}
        </if>
    </sql>

Use the include tag reference where necessary

    <select id="queryListIf" parameterType="map" resultType="Blog">
        select * from blog
        <where>
            <include refid="titleAuthor"></include>
        </where>
    </select>

Precautions:
1. It is best to define SQL fragments based on a single table
2. Do not have where tags

Summarize

The so-called dynamic SQL is splicing SQL statements. We only need to ensure the correctness of SQL and arrange and combine it according to the format of SQL