常用的Mybatis使用

• 动态sql
• 注解
• 对象关系映射
• 二级缓存机制

动态sql

if

值得注意

<if test="burialDepotNum.depotId != null and burialDepotNum.depotId != ''">条件中，''对于数字来说指代的是0，对于字符串来说是''。

<select id="pageList" resultType="a">
    SELECT
    *
    FROM a
    WHERE 1=1
    <if test="obj.id != null and obj.id != ''">
        AND a.id = #{obj.id}
    </if>
    <if test="obj.type != null and obj.type == 'single'">
        AND a.type_value = #{obj.typeValue}
    </if>
</select>

choose（类if else）

<choose>
    <when test="params != null">
        RIGHT JOIN
    </when>
    <when test="params != null">
        RIGHT JOIN
    </when>
    <otherwise>
        LEFT JOIN
    </otherwise>
</choose>

foreach

参数

• collection：集合
• item：元素
• index：索引下标
• open：前缀字符串
• separator：元素之间的分隔符
• close：后缀字符串

批量插入（作为插入数据）

<insert id="batchInsert" parameterType="java.util.List">
	INSERT INTO user (username, email, create_time) VALUES
	<foreach collection="list" item="item" separator=",">
		(#{item.username}, #{item.email}, #{item.createTime})
    </foreach>
</insert>

批量删除（作为IN条件）

<delete id="batchDelete" parameterType="java.util.List">
	DELETE FROM user WHERE id IN
	<foreach collection="list" item="id" open="(" separator="," close=")">
		#{id}
	</foreach>
</delete>

where

where标签会自动去掉最开始的AND或OR

最好是where 1=1起手，数据库优化器会优化掉1=1的，写起来方便，又不影响性能

<select id="findQuery" resultType="Student">
    <include refid="selectvp"/>
    <where>
        <if test="sacc != null">
            AND sacc like concat('%' #{sacc} '%')
        </if>
        <if test="sname != null">
            AND sname like concat('%' #{sname} '%')
        </if>
    </where>
</select>

trim

参数

• prefix：前缀
• prefixOverrides：需要去除的前缀
• suffix：后缀
• suffixOverrides：需要去除的后缀

<select id="selectUserByUsernameAndSex" resultType="user" parameterType="User">
    select * from user
    <trim prefix="where" prefixOverrides="and | or">
        <if test="username != null">
           and username=#{username}
        </if>
        <if test="sex != null">
           and sex=#{sex}
        </if>
    </trim>
</select>

sql、include

用于复用sql的片段，sql定义片段，include使用片段

<sql id="stusql">
    sid, sname,score,clazz
</sql>

<select id="que" parameterType="StudentDto" resultType="Student">
    select <include refid="stusql"/> from student
</select>

如何引用其他mapper中的sql片段

在Amapper中定义sql片段：

<sql id="Base_Column_List"> ID,MAJOR,BIRTHDAY,AGE,NAME,HOBBY</sql>

在Bmapper中引用片段

<include refid="com.xxx.dao.AMapper.Base_Column_List"></include>

注解

备注

为统一风格，尽量还是使用xml。

public interface UserMapper {
    @Select("SELECT * FROM user WHERE id = #{id}")
    User getUserById(Long id);

    @Insert("INSERT INTO user (username, email, create_time) VALUES (#{username}, #{email}, #{createTime})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insertUser(User user);

    @Update("UPDATE user SET username = #{username}, email = #{email} WHERE id = #{id}")
    int updateUser(User user);

    @Delete("DELETE FROM user WHERE id = #{id}")
    int deleteUser(Long id);
}

对象关系映射

对于查询出的数据作对象映射

<resultMap id="userWithOrdersMap" type="User">
    <id property="id" column="user_id"/>
    <result property="username" column="username"/>
    <collection property="orders" ofType="Order">
        <id property="id" column="order_id"/>
        <result property="orderNumber" column="order_number"/>
        <result property="createTime" column="order_create_time"/>
    </collection>
</resultMap>

<select id="getUserWithOrders" resultMap="userWithOrdersMap">
    SELECT
        u.id as user_id,
        u.username,
        o.id as order_id,
        o.order_number,
        o.create_time as order_create_time
    FROM
        user u
        LEFT JOIN orders o ON u.id = o.user_id
    WHERE
        u.id = #{userId}
</select>

二级缓存机制

Mybatis 之 二级缓存-CSDN博客

这个配置启用了LRU淘汰策略的二级缓存,缓存容量为512个对象,每60秒刷新一次。

<cache
  eviction="LRU"
  flushInterval="60000"
  size="512"
  readOnly="true"/>