浮光笔记：Mybatis SQL相关

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