当前位置：首页 > news >正文

【数据库】mybatis使用总结

news 2025/8/26 5:36:48

文章目录

1. 批量插入、检索`<foreach>`
2. `<if>` 判断等于情况
3. 模糊查询(pgsql为例)
4. 分页
5. `<resultMap >` 中`collection ` 和`association `
6. no conflict do update

1. 批量插入、检索`<foreach>`

<insert id="insertSystemService" >insert into SYSTEMINFO_SERVICE (system_code, service_id, add_user, add_time)values<foreach collection="serviceList" item="serviceId" separator="," >( #{systemCode}, #{serviceId}, #{addUser},now()::timestamp(0))</foreach>
</insert>List<InfoBillOwn> selectByStates(List<String> states);
<select id="selectByStates" resultMap="BaseResultMap" parameterType="list">select STOP_OPEN_ID, ACC_NBR, SRV_CODE, STOP_BUSINESS_TYPE, STATE, STATE_DATE, FD_STATUSfrom INF_BILL_OWE where STATE in<foreach collection="list" open="(" close=")" separator="," item="state">#{state}</foreach>order by STOP_OPEN_ID asc
</select>

2. `<if>` 判断等于情况

方式一


<if test="serviceType=='0'.toString() ">
</if>

方式二

<if test='el.TYPE =="1" or el.TYPE =="2"'>
,#{el.PARENT_ID}
</if>

3. 模糊查询(pgsql为例)

方式一

like '%${keyword}%' //只能用$,不能防止sql注入,且 keyword=a’ 会产生报错

方式二

like concat('%',#{keyword,jdbcType=VARCHAR},'%') // 预编译,且 keyword=a’ 不会会产生报错

方式三

like '%'||#{keyword,jdbcType=VARCHAR} || '%' // 预编译,且 keyword=a’ 不会会产生报错

方式四

if (!StringUtils.isEmpty(keyword)) {keyword= "%" + keyword+ "%";
}//外部直接将keyword改为%%

like #{keyword,jdbcType=VARCHAR} // 预编译,且 keyword=a’ 不会会产生报错

方式五

<bind name="pattern2" value="'%' + keyword + '%'" />
xxx like #{pattern2,jdbcType=VARCHAR} // 预编译,且 keyword=a’ 不会会产生报错

4. 分页

字段pageSize,start 如果为integer

limit #{pageSize,jdbcType=INTEGER} offset #{start,jdbcType=INTEGER}

字段pageSize,start 如果为string

limit #{pageSize,jdbcType=INTEGER}::int offset #{start,jdbcType=INTEGER}::int

5. `<resultMap >` 中`collection` 和`association`

<resultMap id="aaaa" type="map"><result column="title_name" property="title" javaType="string"/><result column="type" property="subtype" javaType="string"/><result column="count" property="count" javaType="string"/><result column="solve" property="solve" javaType="string"/><collection property="items" column="title_name" javaType="collection" ofType="map" notNullColumn="item_id"><result column="item_id" property="item_id"/><result column="item_name" property="item_name"/><result column="rn" property="rn"/></collection>
</resultMap><resultMap id="qryAskDetailsNoDimenMap" type="map"><result column="category2_id" property="category2_id"/><result column="category2_name" property="category2_name"/><result column="num" property="num" javaType="string"/><result column="rn" property="rn" javaType="string"/><association property="knowledge" column="category2_id" javaType="map"><result property="knowledge_id" column="knowledge_id"/><result property="knowledge_solve" column="knowledge_solve"/><result property="recommand_num" column="recommand_num" javaType="string"/><result property="release_time" column="release_time"/><result property="solve_num" column="solve_num" javaType="string"/><result property="view_num" column="view_num" javaType="string"/></association></resultMap>

6. no conflict do update

insert into bs_memo_config (id, xxx, xxx, xxx, xxx, xxxx) VALUES(nextval('seq_xxxx_id'),#{editor,jdbcType=VARCHAR},#{memoId,jdbcType=VARCHAR},now(),now()) on conflict (username,age)do updateset id=bs_memo_staff_config.id,staff_id=excluded.staff_id,xxx=xxxexcluded 指的新值,
bs_memo_config.id 指的旧值