Mybatisplus增删改查(baomidou)
public class PageInfoPO {
private Long id;
private Long bookId;
}
//链式
List<PageInfoPO> list = lambdaQuery().select(PageInfoPO::getId)
.eq(PageInfoPO::getBookId, bookId).list();
PageInfoPO page = lambdaQuery().eq(PageInfoPO::getId, id)
.last(" LIMIT 1").one();
lambdaUpdate().eq(PageInfoPO::getId, id)
.set(PageInfoPO::getBookId, bookId)
.update();
lambdaUpdate().eq(PageInfoPO::getId, id).remove();
一对多
public class OralEvalTrajectoryVO {
private Long trajectoryId;
private Long duration;
private List<OralEvalVO> oralEvals;
private List<Long> bookIds;
public static class OralEvalVO {
private String sessionId;
private Float score;
}
}
//resultMap
<resultMap id="oralEvalTrajectoryMap" type="xxx.OralEvalTrajectoryVO">
<id column="id" property="trajectoryId"/> //id不行??用搜索列的别名吧
<result column="duration" property="duration"/>
//一对一
<association property="album" javaType="Album">
<id property="albumId" column="album_id"/>
<result property="albumName" column="album_name" />
</association>
//一对多
<collection property="oralEvals" ofType="xxx.OralEvalTrajectoryVO.OralEvalVO">
<id column="b.id"/>
<result column="b.sessionId" property="sessionId"/>
</collection>
<collection property="bookIds" ofType="long">
<constructor>
<arg column="c.id"/>
</constructor>
</collection>
</resultMap>
动态SQL
<if test="labels != null and labels.size() > 0">
<choose>
<when test="isBaseBookLabel">
AND a.id=e.book_id
AND e.book_type = 0
</when>
<otherwise>
AND b.id=e.book_id
AND e.book_type = 1
</otherwise>
</choose>
<if test="labels != null and labels.size() != 0">
AND e.label_id in
<foreach collection="labels" item="label" index="index" open="(" close=")" separator=",">#{labe}</foreach>
</if>
//mybatis insertOrUpdate 非主键
<insert id="insertOrUpdate" parameterType="com.wantong.config.domain.po.hd.HDUpgradePO">
<selectKey keyProperty="count" resultType="int" order="BEFORE">
SELECT count(*) FROM tis_hd_upgrade WHERE machine_id = #{machineId}
</selectKey>
<if test="count > 0">
update tis_hd_upgrade
<trim prefix="SET" suffixOverrides=",">
<if test="firmwareId != null ">firmware_id = #{firmwareId},</if>
<if test="updateType != null ">update_type = #{updateType},</if>
<if test="updateParam != null ">update_param = #{updateParam},</if>
</trim>
WHERE machine_id = #{machineId}
</if>
<if test="count==0">
insert into tt_token
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="machineId != null ">machine_id,</if>
<if test="firmwareId != null" >firmware_id,</if>
<if test="updateType != null ">update_type,</if>
<if test="updateParam != null ">update_param,</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="machineId != null ">#{machineId},</if>
<if test="firmwareId != null ">#{firmwareId},</if>
<if test="updateType != null ">#{updateType},</if>
<if test="updateParam != null ">#{updateParam},</if>
</trim>
</if>
</insert>
// mybatis insertOrUpdate 主键
<insert id="insertOrUpdate">
insert into base_person
(pname, idcard, gender, nation, source_flag, create_time)
values
<foreach collection="list" item="p" index="index" separator=",">
(#{p.pname},
#{p.idcard},
#{p.gender},
#{p.nation},
#{p.sourceFlag},
#{p.createTime})
</foreach>
ON DUPLICATE KEY UPDATE
pname=values(pname),
idcard=values(idcard),
gender=values(gender),
nation=values(nation)
</insert>
拼接分组后字段
SET SESSION group_concat_max_len = 102400; -- 默认最大长度1024,默认分隔符英文逗号
SELECT a.book_id, a.id as pageId, GROUP_CONCAT(b.eval_text separator '*') as text
FROM brs_page_info a
LEFT JOIN brs_page_finger_info b on a.id = b.page_id
WHERE a.book_id in (21398)
GROUP BY a.id
###区分大小写匹配
SELECT * FROM dictionary_word WHERE text = 'pencil' COLLATE utf8mb4_bin LIMIT 1