MyBatis笔记-常用操作

一、if-else写法

<foreach collection="list" index="index" item="item" separator=",">
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <choose>
            <when test="item.id != null and item.id !=''">#{item.id,jdbcType=CHAR},</when>
            <otherwise>'',</otherwise>
        </choose>
        <choose>
            <when test="item.userType != null and item.userType !=''">#{item.userType,jdbcType=VARCHAR},</when>
            <otherwise>'',</otherwise>
        </choose>
    </trim>
</foreach>

二、where标签用法

<where>
    <if test="query.username != null and query.username != ''">
        and `user`.username LIKE CONCAT('%',#{query.username},'%')
    </if>
</where>

    where标签可以自动去除多余的and或or

三、like写法

    LIKE CONCAT('%',#{query.username},'%')

四、转义字符写法

    方式一：<![CDATA[  sql语句  ]]>

    方式二：直接转义

<	<=	>	>=	&	'	"
&lt;	&lt;=	&gt;	&gt;=	&amp;	&apos;	&quot;

五、IN用法

where xxx IN
<foreach collection="list" item="item" index="index" open="(" close=")" separator=",">
  #{item}
</foreach>
// 默认List类型参数collection为list,Array类型为array。也可用@Param("list")指定

六、mybatisplus 常用查询

// 去重查询
xxxService.count(Wrappers.<Xxx>query().select("DISTINCT province")));
// 自定义分组查询
List<Xxx> orderList = xxxService.list(Wrappers.<Xxx>query()
                .select("DATE_FORMAT(create_time, '%Y-%m-%d') AS ym,DATE_FORMAT(create_time, '%d') AS type, COUNT(id) as price")
                .groupBy("ym")
                .lambda().between(Xxx::getCreateTime, LocalDateTime.of(startDate, LocalTime.MIN), LocalDateTime.of(endDate, LocalTime.MAX))
        );
// 求和查询
Xxx all = xxxService.getOne(Wrappers.<Xxx>query().select("sum(price) as price"));