Mybatis 相关操作(想起来就记录一下~~)

1. 字符串用逗号隔开转列
   如数据库某一字段存储值为： 01,02,03 查询转列显示

SELECT 
	substring_index(
		substring_index('01,02,03',',',help_topic_id+1),',',-1
	) as Id
FROM mysql.help_topic
WHERE help_topic_id < (length('01,02,03')-length(replace('01,02,03',',',''))+1)

2. MYSQL批量插入操作：存在则更新，不存在插入（以主键为唯一值）

/**
	 * 
	 * @title insertBatch
	 * @Description 批量保存或更新
	 * @author chenlf
	 * @param erStatusList
	 */
	void insertBatch(List<EvalResultStatus> list);

<insert id="insertBatch" parameterType="java.util.List">
		INSERT INTO t_eval_result_status(
			id,
			raters_id,
			eval_manage_id,
			bs_id,
			type,
			status
		) VALUES
		<foreach collection="list" item="item" index="index" open="(" separator="),("  close=")">
			#{item.id},
			#{item.ratersId},
			#{item.evalManageId},
			#{item.bsId},
			#{item.type},
			#{item.status}
		</foreach>
			 ON DUPLICATE KEY UPDATE
             	status = values(status),<!-- 状态 -->
             	bs_id= values(bs_id)
	</insert>

3. ORCLE批量插入操作：

<!-- 批量添加 -->
<insert id="insertGroupCompanyRelation" >
merge into T_BZH_TTLM_CY cy
using
(
    <foreach collection="companyIds" item="item" separator="union all"> <!-- 传入的集合参数 -->
         SELECT
            #{groupId, jdbcType=VARCHAR} AS GROUP_ID,
            #{item, jdbcType=VARCHAR} AS company_id
         FROM DUAL
</foreach>
) t
on (cy.GROUP_ID = t.GROUP_ID and cy.company_id = t.company_id) <!-- 关联主键-->
when matched then <!-- 如果存在就更新 -->
    update set <!-- 注意，这里不要写set id == xx -->
    fail.time = t1.time,
when not matched then <!-- 不存在就插入数据 -->
insert
(cy.GROUP_ID, cy.COMPANY_ID)
values
(t.GROUP_ID, t.COMPANY_ID)
</insert>