1、批量新增:
insert into TB_DUTY select SEQ_TB_DUTY.nextval,A.* from(
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
) A
2、批量修改：
单个条件、单个修改字段：
update tb_code_name_result_new set state=#{state,jdbcType=INTEGER} where id in
#{item}
多个条件、多个修改字段：
update TB_DUTY
MORNING_PEOPLE_IDS=#{item.morningPeopleIds,jdbcType=VARCHAR},
MORNING_PEOPLE_NAMES=#{item.morningPeopleNames,jdbcType=VARCHAR},
AFTERNOON_PEOPLE_IDS=#{item.afternoonPeopleIds,jdbcType=VARCHAR},
AFTERNOON_PEOPLE_NAMES=#{item.afternoonPeopleNames,jdbcType=VARCHAR},
EVENING_PEOPLE_IDS=#{item.eveningPeopleIds,jdbcType=VARCHAR},
EVENING_PEOPLE_NAMES=#{item.eveningPeopleNames,jdbcType=VARCHAR},
LEADER_IDS=#{item.leaderIds,jdbcType=VARCHAR},
LEADER_NAMES=#{item.leaderNames,jdbcType=VARCHAR},
where DUTY_ID=#{item.dutyId,jdbcType=INTEGER}
3、批量删除：
单个条件：
delete from TB_CODE_NAME_RESULT_NEW
where ID in
#{item}
多个条件：
delete from tb_duty A
where exists
(
select 1 from(
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}
)S where A.duty_id=S.duty_id
)
多个条件第2种形式：
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}
4、批量查询：(未验证)
SELECT FROM 表名
WHERE poi_id in
#{poiId}
AND pass_uid = #{passUid}
AND status = #{status,jdbcType=BIGINT}
5、mapper常用写法
aa,
bb ,
cc,
dd,
ee
id,
TableName
INSERT INTO T_DDQ_INSTALLMENT_INFO (
aa,
bb ,
cc,
dd,
ee
)
VALUES (
#{sltAccountId},
#{loanPeriodNo},
#{scheduleAmount},
now(),
now()
)
]]>