Advanced usage of mybatis

1, Dynamic SQL

  one of the powerful features of MyBatis is its dynamic SQL. If you have experience using JDBC or other similar frameworks, you can realize how painful it is to splice SQL statements according to different conditions. When splicing, make sure you don't forget the necessary spaces, and pay attention to omitting the comma at the end of the column name list. Using the feature of dynamic SQL can completely get rid of this pain.

  usually, using dynamic SQL cannot be an independent part. Of course, MyBatis uses a powerful dynamic SQL language to improve this situation. This language can be used in any SQL mapping statement.

  dynamic SQL elements are similar to using JSTL or other similar XML based text processors. In previous versions of MyBatis, there were many elements to understand. MyBatis3 has greatly improved them, and now it can use less than half of the original elements. MyBatis uses powerful OGNL based expressions to eliminate other elements.

The dynamic SQL statement of   mybatis is based on OGNL expression. It is convenient to implement some logic in SQL statements. Generally speaking, mybatis dynamic SQL statements mainly fall into the following categories:
   1.if statement (simple conditional judgment)
  2.choose (when, otherwise), which is equivalent to switch in java language, is very similar to choose in jstl.
   3.trim (prefix, suffix, etc. are added to the contents)
   4.where (it is mainly used to simplify the judgment of where conditions in sql statements, and can intelligently process and or without worrying about redundant syntax errors)
   5.set (mainly used for updating)
   6.foreach (especially useful when implementing in statement query)
  in actual development, we often encapsulate query condition classes that contain all query conditions. Sometimes, the condition classes can also be replaced by map, but the readability of the code using map is poor.

public class ConditionPet {
    private String name;
    private int minWeight;
    private int maxWeight;
}

if usage
   used to dynamically splice SQL according to parameter conditions. The test in if is the corresponding condition. When the condition is met, the SQL in if will be spliced normally.

Case:

<select id="findByCondition" resultType="cn.hxzy.entity.Pet">
    select * from pet
    <if test="name != null and name != ''">
        where name like #{name}
    </if>
</select>

Case 2:

<select id="findByCondition" resultType="cn.hxzy.entity.Pet">
        select * from pet where true
        <if test="name != null">
          and  name = #{name}
        </if>
        <if test="minWeight != null">
           and weight >= #{minWeight}
        </if>
    </select>

2, Choose (when, otherwise)

  it is equivalent to if else / switch in java language, which is very similar to choose in jstl. However, the difference between it and if is that in the following cases, the program can only take one of the three branch statements.

  case:

<select id="findByCondition" resultType="cn.hxzy.entity.Pet">
    select * from pet where
    <choose>
        <when test="name != null">
            name = #{name}
        </when>
        <when test="minWeight != null">
            weight >= #{minWeight}
        </when>
        <otherwise>
            id = 1
        </otherwise>
    </choose>
</select>

3, trim

   the trim tag of mybatis is generally used to remove redundant and keywords and commas in sql statements, or to splice suffixes such as "where", "set" and "values" (or add ")" in front of sql statements. It can be used for selective insertion, update, deletion or conditional query. If multiple prefixOverrides are to be removed at the same time, use pipe characters to connect prefixOverrides= “AND|OR”.

  case 1: remove redundant and or where

select * from pet
<trim prefix="WHERE" prefixOverrides="AND">
    <if test="name != null">
        name = #{name}
    </if> 
    <if test="minWeight != null">
        AND weight >= #{minWeight}
    </if>
    <if test="maxWeight != null">
        AND weight <![CDATA[ <=]]> #{maxWeight}
    </if>
</trim>

Prefix: prefix

prefixOverrides: remove keywords or characters in front of SQL statements.

suffixOverrides: remove keywords or characters at the end of the SQL statement.

Case 2: using trim tag to remove extra comma at the end

insert into pet
<trim prefix="(" suffix=")" suffixOverrides=",">
    <if test="name != null">name,</if>
    <if test="weight != null">weight,</if>
</trim>
values
<trim prefix="(" suffix=")" suffixOverrides=",">
    <if test="name!=null">#{name},</if>
    <if test="weight!=null">#{weight},</if>
</trim>

4, where

   where will automatically judge the following conditions. If there are no conditions, the where keyword will not be spliced in SQL. At the same time, it can automatically remove the and or keyword at the front of where.

  case:

select * from pet
<where>
    <if test="name != null">and name like #{name}</if>
    <if test="weight != null">and weight = #{weight}</if>
</where>

practice:

   1. Add the following query criteria in the resource table and complete it with a query statement. map is used for all parameters.

    1. Paging query when the user passes in the page and size parameters.

   2. The user passes in beginTime to query the resources created after the creation time of beginTime.

    3. After the url is passed in, you can fuzzy query according to the url.

    4. After passing in name, you can fuzzy query by name.

  reference code:

<resultMap id="BaseResultMap" type="cn.hxzy.entity.Resource">
    <id column="id" jdbcType="INTEGER" property="id"/>
    <result column="name" jdbcType="VARCHAR" property="name"/>
    <result column="url" jdbcType="VARCHAR" property="url"/>
    <result column="pid" jdbcType="INTEGER" property="pid"/>
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
    <result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
    <result column="order_number" jdbcType="INTEGER" property="orderNumber"/>
</resultMap>
<sql id="Base_Column_List">
id, `name`, url, pid, create_time, update_time, order_number
</sql>
<select id="select" parameterType="map" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List"/>
    from resource
    <where>
        <if test="name != null and name !=''">
            and `name` like #{name}
        </if>
        <if test="url != null and url != ''">
            and url like #{url}
        </if>
        <if test="pid != null">
            and pid = #{pid}
        </if>
        <if test="beginTime != null">
            and create_time >= #{beginTime }
        </if>
    </where>
    <if test="page!=null and size!=null">
        limit #{page},#{size}
    </if>
</select>

5, Usage of set

   similar to trim, the set element will dynamically insert the set keyword at the beginning of the line and delete additional commas (these commas are introduced when using conditional statements to assign values to columns).

  case:

update pet
<set>
    <if test="name != null">name = #{name},</if>
    <if test="weight != null">weight = #{weight},</if>
</set>
where id =#{id}

practice:

   1. Add the following modification method in the resource table to modify only non empty attributes, and the parameters use map uniformly.

  reference code:

<update id="updateByPrimaryKeySelective" parameterType="cn.hxzy.entity.Resource">
    update resource
    <set>
        <if test="name != null">
            `name` = #{name},
        </if>
        <if test="url != null">
            url = #{url},
        </if>
        <if test="pid != null">
            pid = #{pid},
        </if>       
        <if test="orderNumber != null">
            order_number = #{orderNumber},
        </if>
        update_time = now()
    </set>
    where id = #{id}
</update>

6, bind

The   bind element allows you to create a variable outside the OGNL expression and bind it to the current context. For example:

<select id="selectBlogsLike" resultType="Blog">
  <bind name="pattern" value="'%' + parameter.titie + '%'" />
  SELECT * FROM BLOG
  WHERE title LIKE #{pattern}
</select>

7, foreach

   foreach is mainly used for traversing arrays and collections. Foreach provides a large number of attributes to meet development needs, such as collection to specify the name of the traversed collection, the object that item is traversing, the string spliced before open traversal, the string spliced at the end of close, the separator interval character and the index to specify the traversal index.

  case:

where id in
<foreach collection="ids" item="id" open="(" close=")" separator=",">
    #{id}
</foreach>

Equivalent to

where id in (
<foreach collection="ids" item="id" separator=",">
    #{id}
</foreach>
)

practice:

   1. Batch modification is often used during modification. Create the following method to complete batch modification of resource.

  int updateAll(int[]ids, Resource resource);

  ids is the modified data id set

   resource contains modified attributes. Only attributes that are not empty can be modified.

  reference code:

<update id="updateAll">
    update resource
    <set>
        <if test="param1.name != null and param1.name != ''">
            name = #{param1.name},
        </if>
        <if test="param1.url != null and param1.url != ''">
            url = #{param1.url},
        </if>
        <if test="param1.pid != null">
            pid = #{param1.pid},
        </if>

        <if test="param1.orderNumber != null">
            order_number = #{param1.orderNumber},
        </if>
        update_time = now()
    </set>
    where id
    <foreach collection="param2" open="in (" close=")" separator="," item="i">#{i}</foreach>
</update>

Interface method

int update(Resource resource,List<Integer> ids);

8, collection in-depth learning

   the collection in foreach can be used in the actual development as follows:

   1. If a single parameter is passed in and the parameter type is an array, the attribute value of collection is array
   2. If a single parameter is passed in and the parameter type is a list, the value of the collection property is list
   3. If there are multiple parameters passed in, we need to encapsulate them into a Map. Of course, a single parameter can also be used. If there are multiple parameters passed in, we can also put them in the entity class (this kind of actual use is also very much)

1. Normal array

<select id="select" parameterType="int">
    select * from person 
    where id in
    <foreach collection="array" item="id" open="(" close=")" separator=",">
        #{id}
    </foreach>
</select>

When used

int[] ids = {4,6};		
mapper.select(int [] ids);

2.List set

<select id="select" parameterType="int">
    select * from person
    where id in
    <foreach collection="list" item="id" open="(" close=")" separator=",">
        #{id}
    </foreach>
</select>

When used

List<Integer> list = new ArrayList<Integer>();
list.add(4);
list.add(6);
mapper.select(list);

3.map value transfer

<select id="select" parameterType="map">
    delete from person
    where id in
    <foreach collection="ids" item="id" open="(" close=")" separator=",">
        #{id}
    </foreach>		
</select>

When used

Map<String,Object> map = new HashMap<String,Object>();
int[] ids = {4,6};
map.put("ids", ids);		
mapper.select(map);

9, script (from)

  to use dynamic SQL in the annotated mapper interface class, you can use the script element. For example:

@Update({"<script>",
      "update Author",
      "  <set>",
      "    <if test='username != null'>username=#{username},</if>",
      "    <if test='password != null'>password=#{password},</if>",
      "    <if test='email != null'>email=#{email},</if>",
      "    <if test='bio != null'>bio=#{bio}</if>",
      "  </set>",
      "where id=#{id}",
      "</script>"})
void updateAuthorValues(Author author);

Chapter exercise: (dictation for 30 minutes)

   1. Use the dynamic tag library to write the following methods, and the written code shall be as general as possible. Selective indicates the common database default value when the entity attribute is empty.

int deleteByPrimaryKey(Integer id);
int insert(User user);
int insertSelective(User user);
User selectByPrimaryKey(Integer id);
List<User> selectByPrimaryKeys(Collection<Integer> ids);
List<User> selectAll();
List<User> selectAllByPage(@Param("start") int start, @Param("size") int size);
int updateByPrimaryKeySelective(User user);
int updateByPrimaryKey(User user);

  reference code:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.hxzy.mapper.UserMapper">
    <resultMap id="BaseResultMap" type="cn.hxzy.entity.User">
        <id column="id" jdbcType="INTEGER" property="id"/>
        <result column="name" jdbcType="VARCHAR" property="name"/>
        <result column="login_name" jdbcType="VARCHAR" property="loginName"/>
        <result column="login_password" jdbcType="VARCHAR" property="loginPassword"/>
        <result column="role_id" jdbcType="INTEGER" property="roleId"/>
        <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
        <result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
    </resultMap>
    <sql id="Base_Column_List">
        id, `name`, login_name, login_password, role_id, create_time, update_time
    </sql>
    <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from user
        where id = #{id}
    </select>
    <select id="selectByPrimaryKeys" parameterType="java.lang.Integer" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from user
        where id in
        <foreach collection="ids" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>
    </select>
    <select id="selectAll" resultType="cn.hxzy.entity.User" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from user
    </select>
    <select id="selectAllByPage" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from user
        limit #{start},#{size}
    </select>
    <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
        delete from user
        where id = #{id}
    </delete>
    <insert id="insert" keyColumn="id" keyProperty="id" parameterType="cn.hxzy.entity.User" useGeneratedKeys="true">
        insert into user (`name`, login_name, login_password,
            role_id, create_time
        )
        values (#{name}, #{loginName}, #{loginPassword}, 
            #{roleId}, now()
        )
    </insert>
    <insert id="insertSelective" keyProperty="id" parameterType="cn.hxzy.entity.User"
            useGeneratedKeys="true">
        insert into user
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="name != null">
                `name`,
            </if>
            <if test="loginName != null">
                login_name,
            </if>
            <if test="loginPassword != null">
                login_password,
            </if>
            <if test="roleId != null">
                role_id,
            </if>
            create_time
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="name != null">
                #{name},
            </if>
            <if test="loginName != null">
                #{loginName},
            </if>
            <if test="loginPassword != null">
                #{loginPassword},
            </if>
            <if test="roleId != null">
                #{roleId},
            </if>
            now()
        </trim>
    </insert>
    <update id="updateByPrimaryKeySelective" parameterType="cn.hxzy.entity.User">
        update user
        <set>
            <if test="name != null">
                `name` = #{name},
            </if>
            <if test="loginName != null">
                login_name = #{loginName},
            </if>
            <if test="loginPassword != null">
                login_password = #{loginPassword},
            </if>
            <if test="roleId != null">
                role_id = #{roleId},
            </if>
            update_time = now()
        </set>
        where id = #{id}
    </update>
    <update id="updateByPrimaryKey" parameterType="cn.hxzy.entity.User">
        update user
        set `name` = #{name},
          login_name = #{loginName},
          login_password = #{loginPassword},
          role_id = #{roleId},
          update_time = now()
        where id = #{id}
  </update>
</mapper>

Tags: Java Mybatis SQL

Posted on Mon, 06 Dec 2021 21:38:42 -0500 by DrDre