【数据库】mybatis使用总结

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 指的旧值