1.批量检索(oracle)
<select id="selectById" parameterType="java.util.List" resultMap="BaseResultMap"> select p.PROD_ID from bf_prod_basic_info p where p.PROD_ID in <foreach collection="list" item="item" open="(" close=")" separator=","> #{item.id} </foreach> </select>2.批量更新(oracle)
<update id="updateById" parameterType="java.util.List"> <foreach collection="list" item="item" index="index" open="begin" close=";end;" separator=";"> update bf_prod_basic_info p <set> p.UNIT_WGT=#{item.unitWgt,jdbcType=VARCHAR},p.CREATE_DATE=(select sysdate from dual), p.CREATE_TYPE='03' </set> where p.PROD_ID= #{item.prodId,jdbcType=VARCHAR} </foreach> </update>
(mysql写法不一样!)
<foreach>改成这样:3.批量插入(oracle)<foreach collection="list" item="item" index="index" open="" close="" separator=";">
例一:
<insert id="insertWeight" parameterType="java.util.List"> <foreach collection="list" item="item" index="index" open="begin" close=";end;" separator=";"> insert into bf_prod_basic_info (PROD_ID,UNIT_WGT,CREATE_DATE,CREATE_TYPE) values ( #{item.prodId,jdbcType=DECIMAL}, #{item.unitWgt,jdbcType=DECIMAL}, (select sysdate from dual), '03' ) </foreach> </insert>
例二
</insert> <insert id="insertByBatch" parameterType="java.util.List"> insert into SF_ERPSTATUS_TOOS (ID, DOCTYPE, DOCID, OPSTATE, OPUSERCODE, OPUSERNAME, OSORDERCODE, OPDATE,UPDATETIME, WAREHOUSECODE, IS_UPDATE, REMARK, ERROR_TYPE, IDT_ID) ) <foreach collection="list" item="item" index="index" separator="union all"> (select FU_GET_NEXT_IDENTITY('SF_ERPSTATUS_TOOS', 1), #{item.doctype,jdbcType=VARCHAR}, #{item.docid,jdbcType=INTEGER}, #{item.opstate,jdbcType=INTEGER}, #{item.opusercode,jdbcType=VARCHAR}, #{item.opusername,jdbcType=VARCHAR}, #{item.osordercode,jdbcType=VARCHAR}, sysdate,sysdate, #{item.warehousecode,jdbcType=VARCHAR}, 0, #{item.remark,jdbcType=VARCHAR}, #{item.errorType,jdbcType=INTEGER}, #{item.idtId,jdbcType=INTEGER} FROM DUAL) </foreach> </insert>
4.检索数据,有重复的只选一条,选择标准是判断该属性的大小关系。(oracle)
select * from erp_dimension_update a where TRANS_STATE=0 <![CDATA[AND ROWNUM <=50 ]]> and not exists (select 1 from erp_dimension_update where a.itemname = itemname and a.trans_lastdatetime>trans_lastdatetime) Order By a.trans_lastdatetime asc
5.若存在,则更新(oracle)
<update id="updateById" parameterType="java.util.List"> <foreach collection="list" item="item" index="index" open="begin" close=";end;" separator=";"> update bf_prod_basic_info p <set> p.UNIT_WGT=#{item.unitWgt,jdbcType=VARCHAR}, p.CREATE_DATE=(select sysdate from dual), p.CREATE_TYPE='03' </set> where exists (select * from bf_prod_basic_info where p.PROD_ID= #{item.id,jdbcType=DECIMAL}) and p.PROD_ID= #{item.id,jdbcType=DECIMAL} </foreach> </update>6.若不存在,则插入
(oracle)
<insert id="insertWeight" parameterType="java.util.List"> <foreach collection="list" item="item" index="index" open="begin" close=";end;" separator=";"> insert into bf_prod_basic_info (PROD_ID,UNIT_WGT,CREATE_DATE,CREATE_TYPE) select #{item.id,jdbcType=DECIMAL}, #{item.unitWgt,jdbcType=DECIMAL}, (select sysdate from dual), '03' from dual where not exists (select * from bf_prod_basic_info p where p.PROD_ID= #{item.id,jdbcType=DECIMAL}) </foreach> </insert>7.Oracle数据库in里面只能批量检索1000条数据,如果想批量检索超过1000的数据,就使用or!(oracle)
<select id="selectById" parameterType="java.util.List" resultMap="ProdBaseResultMap"> select p.PROD_ID from bf_prod_basic_info p where <foreach collection="list" item="item" open="(" close=")" separator="or"> p.PROD_ID in #{item.id,jdbcType=DECIMAL} </foreach> </select>最大的缺陷就是效率问题!我测试了1000条数据,or花费的事件是in的5倍!
8.批量查询(in和exists的区别)
select * from T1 where exists(select 1 from T2 where T1.a=T2.a) ;
T1数据量小而T2数据量非常大时,T1<<T2 时,1) 的查询效率高。
select * from T1 where T1.a in (select T2.a from T2) ;
T1数据量非常大而T2数据量小时,T1>>T2 时,2) 的查询效率高。
8.传多个参数,其中一个是List参数,一个是普通类型参数(oracle)
<select id="testMoreParams" resultType="com.mb.wzl.entity.User"> select * from USER where USERNAME=#{userName} or NAME IN <foreach collection="list" item="item" open="(" close=")" separator=","> #{item.name} </foreach> </select>dao层:
List testMoreParams(@Param("list") List list, @Param("userName") String userName);
9.批量插入
<insert id="insertByBatch" parameterType="java.util.List"> insert into MA_B2C_STATUS (ID, TRANSACTION_TYPE, TRANSACTION_CODE, TRANSACTION_NUMBER, ORDER_SN, WAREH_CODE, DOC_STATE, CREATE_DATE, IS_UPDATE, REMARK) <foreach collection="list" item="item" index="index" separator="union all"> ( select (SELECT SYS_GUID () from dual), #{item.transactionType,jdbcType=VARCHAR}, #{item.transactionCode,jdbcType=VARCHAR}, #{item.transactionNumber,jdbcType=DECIMAL}, #{item.orderSn,jdbcType=VARCHAR}, #{item.warehCode,jdbcType=VARCHAR}, #{item.docState,jdbcType=VARCHAR}, (select sysdate from dual), #{item.isUpdate,jdbcType=DECIMAL}, #{item.remark,jdbcType=VARCHAR} from dual) </foreach> </insert>
10.批量删除
delete from wms_dgnrpt_hq01w850 d where d.order_sn in <foreach collection="list" index="index" item="item" open="(" separator="," close=")" > #{item.orderSn,jdbcType=VARCHAR} </foreach>