mybatis针对Oracle数据库进行(单个或多个条件)批量操作(新增、修改、删除)的sql写法--mysql...

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/big1989wmf/article/details/84738186
[b]1、批量新增:[/b]
<insert id="addMonthDutyIntoDB" parameterType="java.util.List">
insert into TB_DUTY select SEQ_TB_DUTY.nextval,A.* from(
<foreach collection="list" item="item" index="index" separator="union">
SELECT #{item.dscd}, #{item.unitId},#{item.year},#{item.month},#{item.day},#{item.weekDay},
#{item.morningPeopleIds}, #{item.morningPeopleNames},#{item.afternoonPeopleIds},#{item.afternoonPeopleNames},#{item.eveningPeopleIds},
#{item.eveningPeopleNames},#{item.leaderIds},#{item.leaderNames},#{item.flag},#{item.remark},#{item.day0} FROM DUAL
</foreach>
) A
</insert>


[b]2、批量修改:[/b]
[color=red]单个条件、单个修改字段:[/color]
 <update id="auditMultiByIds" parameterType="java.util.List">  
update tb_code_name_result_new set state=#{state,jdbcType=INTEGER} where id in
<foreach collection="list" item="item" index="index" open="(" separator="," close=")" >
#{item}
</foreach>
</update>

[color=red]多个条件、多个修改字段:[/color]
<update id="updateByMultiConditions" parameterType="java.util.List">   
<foreach collection="list" item="item" index="index" open="begin" close="; end;" separator=";">
update TB_DUTY
<set>
<if test="item.morningPeopleIds != null and item.morningPeopleIds != '' " >
MORNING_PEOPLE_IDS=#{item.morningPeopleIds,jdbcType=VARCHAR},
</if>
<if test="item.morningPeopleNames != null and item.morningPeopleNames != '' " >
MORNING_PEOPLE_NAMES=#{item.morningPeopleNames,jdbcType=VARCHAR},
</if>
<if test="item.afternoonPeopleIds != null and item.afternoonPeopleIds != '' " >
AFTERNOON_PEOPLE_IDS=#{item.afternoonPeopleIds,jdbcType=VARCHAR},
</if>
<if test="item.afternoonPeopleNames != null and item.afternoonPeopleNames != '' " >
AFTERNOON_PEOPLE_NAMES=#{item.afternoonPeopleNames,jdbcType=VARCHAR},
</if>
<if test="item.eveningPeopleIds != null and item.eveningPeopleIds != '' " >
EVENING_PEOPLE_IDS=#{item.eveningPeopleIds,jdbcType=VARCHAR},
</if>
<if test="item.eveningPeopleNames != null and item.eveningPeopleNames != '' " >
EVENING_PEOPLE_NAMES=#{item.eveningPeopleNames,jdbcType=VARCHAR},
</if>
<if test="item.leaderIds != null and item.leaderIds != '' " >
LEADER_IDS=#{item.leaderIds,jdbcType=VARCHAR},
</if>
<if test="item.leaderNames != null and item.leaderNames != '' " >
LEADER_NAMES=#{item.leaderNames,jdbcType=VARCHAR},
</if>
</set>
where DUTY_ID=#{item.dutyId,jdbcType=INTEGER}
</foreach>
</update>


[b]3、批量删除:[/b]
[color=red]单个条件:[/color]
<delete id="delMultiByIds" parameterType="java.util.List">  
delete from TB_CODE_NAME_RESULT_NEW
where ID in
<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</delete>

[color=red]多个条件:[/color]
<delete id="delMultiByIds2" parameterType="java.util.List"> 
delete from tb_duty A
where exists
(
select 1 from(
<foreach collection="list" item="item" index="index" separator="union all">
select B.* from tb_duty B where 1=1 and B.dscd=${item.dscd} and B.unit_id=${item.unitId} and
B.year=${item.year} and B.month=${item.month} and B.flag=${item.flag}
</foreach>
)S where A.duty_id=S.duty_id
)
</delete>

[color=red]多个条件第2种形式:[/color]
<!-- 成功删除返回的是-1而不是删除的记录数 -->
<delete id="delMultiByIds" >
<foreach collection="list" item="item" index="index" open="begin" close="; end;" separator=";">
delete from tb_duty_statistics a
where a.person_id
in(
select b.person_id from tb_duty_person_info b where b.dscd=#{dscd,jdbcType=CHAR} and b.unit_id=#{unitId,jdbcType=INTEGER}
)
and substr(a.duty_id,1,7)=#{item.dutyId,jdbcType=CHAR}
</foreach>
</delete>

[b]4、批量查询:(未验证)[/b]
<select id="selectBySomePoiIds" resultType="list" parameterType="java.util.Map">      
SELECT <include refid="Base_Column_List" /> FROM 表名
WHERE poi_id in

<foreach collection="poiIds" item="poiId" index="index" open="(" close=")" separator=",">
#{poiId}
</foreach>

AND pass_uid = #{passUid}
<if test="status != null">
AND status = #{status,jdbcType=BIGINT}
</if>
</select>


PS:带in的查询:
[b]4.1mybatis sql in 查询总结[/b]
http://blog.csdn.net/u013628152/article/details/51184641


String[] array={CommonEnum.INITIATIVE_PAYMENT_AWARD_FUN.getCode(),CommonEnum.INITIATIVE_PAYMENT_AWARD_NUM.getCode()};
List<ModuleConfigEntity> list=moduleConfigRepository.selectMultiRecords(array);
if(CollectionUtils.isNotEmpty(list)){
for (ModuleConfigEntity moduleConfigEntity : list) {
if(CommonEnum.INITIATIVE_PAYMENT_AWARD_FUN.getCode().equals(moduleConfigEntity.getConfigName())){
unionPaySltEntities.get(0).setInitiativePaymentAwardFun(moduleConfigEntity.getConfigValue());//开关
}else if(CommonEnum.INITIATIVE_PAYMENT_AWARD_NUM.getCode().equals(moduleConfigEntity.getConfigName())){
unionPaySltEntities.get(0).setInitiativePaymentAwardNum(moduleConfigEntity.getConfigValue());//数量
}
}
}
//
<!--根据config_name查询多个开关配置-->
<select id="selectMultiRecords" resultType="ModuleConfigEntity">
SELECT
CONFIG_NAME configName,
CONFIG_VALUE configValue
FROM t_ddq_module_config
WHERE DELETE_FLAG=0
and CONFIG_NAME in
<foreach item="item" index="index" collection="array" open="(" separator="," close=")">
#{item}
</foreach>
</select>


[b]5、mapper常用写法[/b]

<resultMap id="BaseResultMap" type="com.xyxc.CustomerInfoEntity">
<id column="id" property="id" />
<result column="aa" property="aa" />
<result column="bb" property="bb" />
</resultMap>

<sql id="table_column_no_id">
aa,
bb ,
cc,
dd,
ee
</sql>

<sql id="columns" >
id,<include refid="table_column_no_id"/>
</sql>

<sql id="table_name" >
TableName
</sql>

<insert id="insert" parameterType="com.xyxc.CustomerInfoEntity" useGeneratedKeys="true" keyProperty="installmentId">
<![CDATA[
INSERT INTO T_DDQ_INSTALLMENT_INFO (
aa,
bb ,
cc,
dd,
ee
)
VALUES (
#{sltAccountId},
#{loanPeriodNo},
#{scheduleAmount},
now(),
now()
)
]]>

<selectKey resultType="Long" keyProperty="installmentId" order="AFTER">
<![CDATA[ SELECT LAST_INSERT_ID() AS installmentId ]]>
</selectKey>
</insert>
展开阅读全文

没有更多推荐了,返回首页