MyBatis Study Notes (1) — Usage

MyBatis learning Notes (2) — Mapping relations

One-to-one mapping for advanced mapping

Mybatis Learning Notes (4) – Advanced mapping one of multiple mappings

Advanced mapping many-to-many mapping

One of the most powerful features of Mybatis is dynamic SQL. You can use dynamic SQL to add some logic to SQL statements, and avoid the pain of compiling SQL statements using JDBC. Here are some of the most common applications.

If (judge element)

Mybatis = Mybatis = Mybatis = Mybatis = Mybatis = Mybatis = Mybatis

<select id="findProductList" parameterType="com.shuqing28.pojo.Products"
        resultType="com.shuqing28.pojo.Products">
    SELECT * FROM products
    WHERE 1=1
    <if test="prodName! =null and prodName! = "">
            AND prod_name like '%${prodName}%'
    </if>
</select>
Copy the code

Here we use an if statement to detect if prodName is null, add a fuzzy match to prod_NAME if it is not null, and unconstruct the condition if the parameter is null. So what we see here is that we have WHERE 1=1 in case this condition is true, so without adding WHERE 1=1, SELECT * FROM products AND prod_name like ‘%${prodName}%’.

There are several ways to get rid of WHERE 1=1

where, trim, set

We can use the WHERE element instead of WHERE 1=1:

<select id="findProductList" parameterType="com.shuqing28.pojo.Products"
        resultType="com.shuqing28.pojo.Products">
    SELECT * FROM products
    <where>
    <if test="prodName! =null and prodName! = "">
            AND prod_name like '%${prodName}%'
    </if>
    </where>
</select>
Copy the code

The where clause is added only when the if inside the WHERE element is true, AND the existence of AND is adjusted automatically.

You can also use trim to adjust formatting:

<select id="findProductList" parameterType="com.shuqing28.pojo.Products"
        resultType="com.shuqing28.pojo.Products">
    SELECT * FROM products
    <trim prefix="WHERE" prefixOverrides="AND">
    <if test="prodName! =null and prodName! = "">
            AND prod_name like '%${prodName}%'
    </if>
    </trim>
</select>
Copy the code

Here prefix stands for prefix, AND PrefixOverride removes the first AND, so the result is the same as the WHERE element.

The set element applies to the update, adding that we want to update a field:

<update id="updateProduct" parameterType="com.shuqing28.pojo.Products">
    UPDATE products
    <set>
    <if test="prodName! =null and prodName! = "">
            AND prod_name like '%${prodName}%'
    </if>
    </set>
    WHERE prod_id=#{prodId}
</update>
Copy the code

This is wrapped in set, which means that if the if element is judged to be true, the set clause is automatically added

UPDATE products SET prod_name like '%${prodName}% WHERE prod_id=#{prodId}
Copy the code

You can also use the trim element:

<trim prefix="SET" suffixOverride=",">... </trim>Copy the code

SuffixOverride automatically strips out the last, unwanted item by changing the prefix to SET. Of course, suffixOverride removes the last comma.

foreach

Foreach is obviously a loop that iterates through collections, but if you pass in a List or a Set of interfaces, it can be useful. Suppose you have the following query:

SELECT * FROM products WHERE prod_id IN ("ANV01"."ANV02"."ANV03");
Copy the code

So we can wrap three arguments in a List and iterate through it with a foreach statement:

<select id="findProductListByProdId" parameterType="java.util.List"
        resultType="com.shuqing28.pojo.Products">
    SELECT * FROM products
    WHERE prod_id IN
    <foreach item="prod_id" index="index" collection="list" open="(" separator="," close=")">
            #{prod_id}
    </foreach>
</select>
Copy the code

Let’s see how it works:

@Test
public void findProductListByProdId(){
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
        OrdersDao ordersDao = sqlSession.getMapper(OrdersDao.class);
        List<String> prodList = new ArrayList<String>();
        prodList.add("ANV01");
        prodList.add("ANV02");
        prodList.add("ANV03"); List<Products> productList = ordersDao.findProductListByProdId(prodList); System.out.println(productList); } finally { sqlSession.close(); }}Copy the code

Construct the query parameters and pass the list directly.

bind

Other dynamic SQL parameters include the previously included test, which tests true and false in the conditional judgment element, and a bind element, which defines a context variable, such as the %${prodName}% in our first example, which we can also define in advance with bind:

<select id="findProductList" parameterType="com.shuqing28.pojo.Products"
        resultType="com.shuqing28.pojo.Products">
        <bind name="pattern" value="'%' + _parameter + '%'"/>
    SELECT * FROM products
    WHERE 1=1
    <if test="prodName! =null and prodName! = "">
            AND prod_name like #{pattern}'
    </if>
</select>
Copy the code

In the above example, we defined pattern in advance, which can be used directly. For variables that are used in multiple places, this operation is suitable. _parameter represents the parameter passed in, which is assigned to pattern after combining with the wildcard character.

These are the only common elements for dynamic SQL.