oracle使用mybatis操作批量插入、更新数据

宁漂打工仔

已于 2022-07-14 14:08:07 修改

阅读量1.2k

点赞数 1

分类专栏： # SQL Java 文章标签： mybatis oracle 数据库

于 2020-03-19 20:37:16 首次发布

本文链接：https://blog.csdn.net/SpringHASh/article/details/104975836

版权

Java 同时被 2 个专栏收录

69 篇文章 0 订阅

订阅专栏

SQL

13 篇文章 0 订阅

订阅专栏

异常堆栈

org.springframework.jdbc.BadSqlGrammarException: 
### Error updating database.  Cause: java.sql.SQLException: ORA-06550: 第 22 行, 第 13 列: 
PL/SQL: ORA-00933: SQL 命令未正确结束
ORA-06550: 第 3 行, 第 13 列: 
PL/SQL: SQL Statement ignored
ORA-06550: 第 193 行, 第 12 列: 
PLS-00103: 出现符号 "end-of-file"在需要下列之一时：
 ( begin case
   declare end exception exit for goto if loop mod null pragma
   raise return select update while with <an identifier>
   <a double-quoted delimited-identifier> <a bind 

### The error may involve com.zz.common.dao.T_FROZENTOPO_FINALMapper.batchInsertData-Inline
### The error occurred while setting parameters
### Cause: java.sql.SQLException: ORA-06550: 第 22 行, 第 13 列: 
PL/SQL: ORA-00933: SQL 命令未正确结束
ORA-06550: 第 3 行, 第 13 列: 
PL/SQL: SQL Statement ignored
ORA-06550: 第 193 行, 第 12 列: 
PLS-00103: 出现符号 "end-of-file"在需要下列之一时：
 ( begin case
   declare end exception exit for goto if loop mod null pragma
   raise return select update while with <an identifier>
   <a double-quoted delimited-identifier> <a bind 

; bad SQL grammar []; nested exception is java.sql.SQLException: ORA-06550: 第 22 行, 第 13 列: 
PL/SQL: ORA-00933: SQL 命令未正确结束
ORA-06550: 第 3 行, 第 13 列: 
PL/SQL: SQL Statement ignored
ORA-06550: 第 193 行, 第 12 列: 
PLS-00103: 出现符号 "end-of-file"在需要下列之一时：
 ( begin case
   declare end exception exit for goto if loop mod null pragma
   raise return select update while with <an identifier>
   <a double-quoted delimited-identifier> <a bind 

	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:71)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:365)
	at com.sun.proxy.$Proxy26.insert(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:237)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:79)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:40)
	at com.sun.proxy.$Proxy68.batchInsertData(Unknown Source)
	at com.zz.common.impl.UploadServiceImpl.importRecord(UploadServiceImpl.java:719)
	at com.zz.common.impl.UploadServiceImpl$$FastClassBySpringCGLIB$$49180d48.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:720)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:655)
	at com.zz.common.impl.UploadServiceImpl$$EnhancerBySpringCGLIB$$f95298ee.importRecord(<generated>)
	at com.zz.common.controller.UploadController.importRecord(UploadController.java:61)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221)
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136)
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:817)
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:731)
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:959)
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893)
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:968)
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:870)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:661)

批处理语句

<insert id="batchInsertData" parameterType="java.util.Collection">
		begin
		<foreach collection="list" item="item" index="index">
		insert into t_meter (meterId, meterBoxId, meter_name,
		meter_no, house_id, phase_remark,
		C_DistrictBCDId, C_AddressId, C_ChannelId,
		CREATE_TIME, CREATE_ID, DEL_FLAG, leak_channel_id
		)
		values (#{item.meterId,jdbcType=VARCHAR}, #{item.meterBoxId,jdbcType=VARCHAR},
		#{item.meter_name,jdbcType=VARCHAR},
		#{item.meter_no,jdbcType=VARCHAR}, #{item.house_id,jdbcType=VARCHAR}, #{item.phase_remark,jdbcType=VARCHAR},
		#{item.c_DistrictBCDId,jdbcType=VARCHAR}, #{item.c_AddressId,jdbcType=VARCHAR},
		#{item.c_ChannelId,jdbcType=INTEGER},
		#{item.CREATE_TIME,jdbcType=TIMESTAMP}, #{item.CREATE_ID,jdbcType=VARCHAR},
		  #{item.DEL_FLAG,jdbcType=VARCHAR},
		#{item.leak_channel_id,jdbcType=INTEGER}
		);
		</foreach>
		end;
	</insert>

需要在每一条sql语句后面加 ;

begin end ;

主要是避免减少数据库多次连接

学了一个新的shell命令，查看当前目录文件大小

du -sh *

更新操作

<update id="batchUpdateData" parameterType="java.util.List" >
		begin
		<foreach collection="list" item="item" index="index" separator=";" >
			update t_meterbox
			<set>
				<if test="item.branchBoxId != null">
					branchBoxId = #{item.branchBoxId,jdbcType=VARCHAR},
				</if>
				<if test="item.c_DistrictBCDId != null">
					C_DistrictBCDId = #{item.c_DistrictBCDId,jdbcType=VARCHAR},
				</if>
				<if test="item.c_AddressId != null">
					C_AddressId = #{item.c_AddressId,jdbcType=VARCHAR},
				</if>
				<if test="item.c_ChannelNum != null">
					C_ChannelNum = #{item.c_ChannelNum,jdbcType=INTEGER},
				</if>
				<if test="item.epu_name != null">
					epu_name = #{item.epu_name,jdbcType=VARCHAR},
				</if>
				<if test="item.epu_local != null">
					epu_local = #{item.epu_local,jdbcType=VARCHAR},
				</if>
				<if test="item.CREATE_TIME != null">
					CREATE_TIME = #{item.CREATE_TIME,jdbcType=TIMESTAMP},
				</if>
				<if test="item.CREATE_ID != null">
					CREATE_ID = #{item.CREATE_ID,jdbcType=VARCHAR},
				</if>
				<if test="item.UPDATE_TIME != null">
					UPDATE_TIME = #{item.UPDATE_TIME,jdbcType=TIMESTAMP},
				</if>
				<if test="item.UPDATE_ID != null">
					UPDATE_ID = #{item.UPDATE_ID,jdbcType=VARCHAR},
				</if>
				<if test="item.DEL_FLAG != null">
					DEL_FLAG = #{item.DEL_FLAG,jdbcType=VARCHAR},
				</if>

				<if test="item.isVirtual != null">
					is_virtual = #{item.isVirtual,jdbcType=INTEGER},
				</if>
				<if test="item.display != null">
					display = #{item.display,jdbcType=INTEGER},
				</if>
				<if test="item.active != null">
					active = #{item.active,jdbcType=INTEGER},
				</if>
			</set>
			where meterBoxId = #{item.meterBoxId,jdbcType=VARCHAR}
		</foreach>
		;end;
	</update>