This is the 13th day of my participation in the August More Text Challenge. For details, see:August is more challenging

Mybatis caching and lazy loading are described above. Perhaps cache lazy loading in peacetime can not be used, then dynamic SQL in peacetime work is used too frequently, like that what see the same day. Without further ado, let’s begin today’s introduction.

To prepare

public class QueryCondition {

    /** * ID */
    private Integer id;
    /** * Name */
    private String name;
    /** * Price */
    private Integer price;
    /** * category */
    private Integer category;
    /** * Category set */
    private List<Integer> categoryList;

    Get/set / / ignore
}
Copy the code

SQL splicing: if, Choose, foreach

If label, single condition selection

ifThere’s only one tagtestProperty, the property value is usedOGNLTo expresstrue/false, when the attribute value istrue, it will beifThe SQL fragment inside the tag is spliced into the final SQL, otherwise not spliced

<select id="findByCondition" parameterType="org.apache.ibatis.z_run.pojo.QueryCondition" resultType="org.apache.ibatis.z_run.pojo.Purchase">
    select * from purchase
    <where>
        <if test="id ! = null">
            and id = #{id,jdbcType=INTEGER}
        </if>
        <if test="category ! = null">
            and category = #{category,jdbcType=INTEGER}
        </if>
    </where>
</select>
Copy the code

If id is null and category is null, the output SQL is as follows:

@Test
public void dynamicSQL(a) {
    PurchaseMapper mapper = sqlSession.getMapper(PurchaseMapper.class);
    QueryCondition condition = new QueryCondition();
    condition.setId(2);
    System.out.println(mapper.findByCondition(condition));

}
Copy the code

DEBUG [main] - ==> Preparing: select * from purchase WHERE id = ? [Purchase{id=2, name=' Purchase ', price=18, category=2}]Copy the code

Choose tag, multiple conditional selection

chooseLabels need to matchwhen,otherwiseTag to use, usechooseTo wrap up multiplewhenLabel and aotherwiseThe tag can be used for multiple conditional selection. The following is an example:

<select id="findByCondition" parameterType="org.apache.ibatis.z_run.pojo.QueryCondition" resultType="org.apache.ibatis.z_run.pojo.Purchase">
    select * from purchase
    <where>        
        <choose>
            <when test="id == -1">
                and id = -10
            </when>
            <when test="id == 0">
                and id = -5
            </when>
            <otherwise>
                and id = #{id,jdbcType=INTEGER}
            </otherwise>
        </choose>
    </where>
</select>
Copy the code

When the ID is -1, enter the first onewhenTag, when it matches a result, it doesn’t match down anymore, it just jumps outchooseTag, when allwhenTags are not met if there isotherwiseLabel, then enterotherwiseThe label.

@Test
public void dynamicSQL(a) {
    PurchaseMapper mapper = sqlSession.getMapper(PurchaseMapper.class);
    QueryCondition condition = new QueryCondition();
    // The parameter is -1
    System.out.println("=========== parameter is -1============");
    condition.setId(-1);
    System.out.println(mapper.findByCondition(condition));
    // The parameter is 0
    System.out.println("=========== parameter is 0============");
    condition.setId(0);
    System.out.println(mapper.findByCondition(condition));
    // The parameter is 1
    System.out.println("=========== parameter is 1============");
    condition.setId(1);
    System.out.println(mapper.findByCondition(condition));
}
Copy the code

= = = = = = = = = = = parameter to 1 = = = = = = = = = = = = the DEBUG [main] - Cache Hit thewire [org. Apache. Ibatis. Z_run. Mapper. PurchaseMapper] : 0.0 DEBUG [main] - ==> Preparing: SELECT * from purchase WHERE id = -10 DEBUG [main] - ==> Parameters: DEBUG [main] - <== Total: [0] = = = = = = = = = = = parameter to 0 = = = = = = = = = = = = the DEBUG [main] - Cache Hit thewire [org. Apache. Ibatis. Z_run. Mapper. PurchaseMapper] : 0.0 DEBUG [main] - ==> Preparing: SELECT * from purchase WHERE id = -5 DEBUG [main] - ==> Parameters: DEBUG [main] - <== Total: [0] = = = = = = = = = = = parameter to 1 = = = = = = = = = = = = the DEBUG [main] - Cache Hit thewire [org. Apache. Ibatis. Z_run. Mapper. PurchaseMapper] : 0.0 DEBUG [main] - ==> Preparing: SELECT * from purchase WHERE ID =? DEBUG [main] - ==> Parameters: 1(Integer) DEBUG [main] - <== = Total: 1 [Purchase{id=1, name=' coq ', category=1}]Copy the code

The foreach tag iterates over the collection

foreachTag is used to traverse the collection, there iscollection,item,index,open,close,separatorSeveral properties
- collectionIs mandatory, which indicates the name of the collection to be traversed. All other properties are optional.
- itemRepresents the elements of a set;
- indexRepresents the index of the elements in the collection;
- openRepresentation in splicingforeachThe SQL fragment that needs to be spliced before the tag internal SQL fragment;
- closeIt means that all of them have been splicedforeachThe SQL fragment that needs to be spliced after the SQL fragment in the tag;
- separatorEach saidforeachThe delimiter of the SQL fragment inside the tag.

<select id="findByCondition" parameterType="org.apache.ibatis.z_run.pojo.QueryCondition" resultType="org.apache.ibatis.z_run.pojo.Purchase">
    select * from purchase
    <where>
        <if test="categoryList ! = null and categoryList.size > 0">
            <foreach collection="categoryList" open="and category in (" close=")" item="category" separator=",">
                ${category}
            </foreach>
        </if>
    </where>
</select>
Copy the code

The above SQL indicates that the categoryList collection is traversed when it is not null and its size is greater than 0
In the foreach tag,collection="categoryList"The set to iterate over is categoryList;
open="and category in ("Represents inside the concatenated foreach tag${category}Before the SQL fragment, you need to concatenate the value of the open attribute"and category in (";
We then start traversing the collection, each time with the names of the elements in the collectionitem="category", each loop concatenates the category value and appends itseparatorAttribute values.;
close=")"After the loop is complete, concatenate one at the SQL end);
The resulting SQL looks like this. You can see that Mybatis does not concatenate at the end as it traverses the last elementseparatorProperty.

@Test
public void dynamicSQL(a) {
    PurchaseMapper mapper = sqlSession.getMapper(PurchaseMapper.class);
    QueryCondition condition = new QueryCondition();
    List<Integer> categoryList = new ArrayList<>();
    categoryList.add(1);
    categoryList.add(2);
    categoryList.add(3);
    condition.setCategoryList(categoryList);
    System.out.println(mapper.findByCondition(condition));
}
Copy the code

DEBUG [main] - ==> Preparing: select * from purchase WHERE category in ( 1 , 2 , 3 ) DEBUG [main] - ==> Parameters: DEBUG [main] - <== Total: 6 [Purchase {id = 1, name = 'coke' price = 3, the category = 1}, Purchase {id = 2, name = 'popcorn, price = 18, the category = 2}, Purchase {id = 8, Name = 'ham, price = 3, the category = 1}, Purchase {id = 9, name =' ham, price = 3, the category = 1}, Purchase {id = 10, name = 'ham, price = 3, Purchase{id=11, category=1}, Purchase{id=11, category=1}, Purchase{id=11, category=1}]Copy the code

Format output: WHERE, set, trim

Where the label

It is mainly used for packing query conditions and automatically adding search conditionswhereKeyword, and automatically remove the first of the conditionsandoror.

<select id="findByCondition" parameterType="org.apache.ibatis.z_run.pojo.QueryCondition" resultType="org.apache.ibatis.z_run.pojo.Purchase">
    select * from purchase
    <where>
        <if test="id ! = null">
            and id = #{id,jdbcType=INTEGER}
        </if>
        <if test="category ! = null">
            and category = #{category,jdbcType=INTEGER}
        </if>
    </where>
</select>
Copy the code

@Test
public void dynamicSQL(a) {
    PurchaseMapper mapper = sqlSession.getMapper(PurchaseMapper.class);
    QueryCondition condition = new QueryCondition();
    condition.setId(1);
    condition.setCategory(1);
    System.out.println(mapper.findByCondition(condition));
}
Copy the code

DEBUG [main] - ==> Preparing: select * from purchase WHERE id = ? and category = ? DEBUG [main] - ==> Parameters: 1(Integer), 1(Integer) DEBUG [main] - <== Total: 1 [Purchase{id=1, name=' Purchase ', category=1, price=3}]Copy the code

The set tag

It is mainly used forupdateStatement, which is used to concatenate database attributes to be updated, automatically generate the set keyword for THE SQL statement, and remove the set keyword at the end of the statement..

<update id="updateXmlPojoByID" parameterType="org.apache.ibatis.z_run.pojo.Purchase">
    update purchase
    <set>
        <if test="name ! = null">
            `name` = #{name,jdbcType=VARCHAR},
        </if>
        <if test="price ! = null">
            `price` = #{price,jdbcType=INTEGER},
        </if>
        <if test="category ! = null">
            category = #{category,jdbcType=INTEGER},
        </if>
    </set>
    where id = #{id,jdbcType=INTEGER}
</update>
Copy the code

@Test
public void dynamicSQL(a) {
    PurchaseMapper mapper = sqlSession.getMapper(PurchaseMapper.class);
    Purchase purchase = new Purchase();
    purchase.setId(1);
    purchase.setPrice(6);
    mapper.updateXmlPojoByID(purchase);
}

Copy the code

DEBUG [main] - ==>  Preparing: update purchase SET `price` = ? where id = ? 
DEBUG [main] - ==> Parameters: 6(Integer), 1(Integer)
DEBUG [main] - <==    Updates: 1
Copy the code

Trim tabs

Can be used to prefix SQL (prefix), suffix (suffix), can also be used to remove SQL before AND/OR, (prefixOverrides), or remove commas from the end of SQL (suffixOverrides)
All four properties are optional.

<insert id="insertXmlPojo" parameterType="org.apache.ibatis.z_run.pojo.Purchase" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
    insert into purchase
    <trim prefix="(" suffix=")" suffixOverrides="," prefixOverrides="AND | OR">
        <if test="id ! = null">
            id,
        </if>
        <if test="name ! = null">
            `name`,
        </if>
        <if test="price ! = null">
            `price`,
        </if>
        <if test="category ! = null">
            category,
        </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," prefixOverrides="AND | OR">
        <if test="id ! = null">
            #{id,jdbcType=INTEGER},
        </if>
        <if test="name ! = null">
            #{name,jdbcType=VARCHAR},
        </if>
        <if test="price ! = null">
            #{price,jdbcType=INTEGER},
        </if>
        <if test="category ! = null">
            #{category,jdbcType=INTEGER},
        </if>
    </trim>
</insert>
Copy the code

@Test
public void dynamicSQL(a) {
    PurchaseMapper mapper = sqlSession.getMapper(PurchaseMapper.class);
    Purchase purchase = new Purchase();
    purchase.setName("Durian");
    purchase.setPrice(141);
    purchase.setCategory(3);
    mapper.insertXmlPojo(purchase);
}
Copy the code

DEBUG [main] - ==> Preparing: insert into purchase ( `name`, `price`, category ) values ( ? ,? ,?) DEBUG [main] - ==> Parameters: durian (String), 141(Integer), 3(Integer) DEBUG [main] - <== Updates: 1Copy the code

Define constants and references: SQL, include

SQL TAB

Used to define the extracted common SQL fragment
Contains aidProperty to give the SQL fragment a unique ID for easy invocation

The include tag

Used to usesqlTag defines a common SQL fragment
Contains arefidProperty that is used to reference the specifiedidSQL fragment.

<sql id="Base_Column_List">
    id, `name`, price, category
</sql>

<select id="findByCondition" parameterType="org.apache.ibatis.z_run.pojo.QueryCondition" resultType="org.apache.ibatis.z_run.pojo.Purchase">
    select
    <include refid="Base_Column_List" />
    from purchase
    <where>
        <if test="id ! = null">
            And id = #{id,jdbcType=INTEGER}
        </if>
        <if test="category ! = null">
            And category = #{category,jdbcType=INTEGER}
        </if>
    </where>
</select>
Copy the code

@Test
public void dynamicSQL(a) {
    PurchaseMapper mapper = sqlSession.getMapper(PurchaseMapper.class);
    QueryCondition condition = new QueryCondition();
    condition.setId(1);
    condition.setCategory(1);
    System.out.println(mapper.findByCondition(condition));
}
Copy the code

DEBUG [main] - ==> Preparing: select id, `name`, price, category from purchase WHERE id = ? And category = ? DEBUG [main] - ==> Parameters: 1(Integer), 1(Integer) DEBUG [main] - <== Total: 1 [Purchase{id=1, Purchase =1, category=1, price=6}]Copy the code

This is the introduction of Mybatis dynamic tag, quickly use it!

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

Dynamic SQL for Mybatis

To prepare

SQL splicing: if, Choose, foreach

If label, single condition selection

Choose tag, multiple conditional selection

The foreach tag iterates over the collection

Format output: WHERE, set, trim

Where the label

The set tag

Trim tabs

Define constants and references: SQL, include

SQL TAB

The include tag

Dynamic SQL for Mybatis

To prepare

SQL splicing: if, Choose, foreach

If label, single condition selection

Choose tag, multiple conditional selection

The foreach tag iterates over the collection

Format output: WHERE, set, trim

Where the label

The set tag

Trim tabs

Define constants and references: SQL, include

SQL TAB

The include tag

Related Posts

Comparison of 6 common distributed unique ID generation strategies and their advantages and disadvantages

LeetCode-021- Merges two ordered lists

The past and present life of station B log system