ibatis 框架 批量添加的写法

insert_viann 2013-06-05 07:01:44
我在ibatis中写一个批量添加的sql,结构报 ORA-00911: invalid character 错误，请指点


我在sqlMap中的写法如下

	<!--批量增加对象-->

	<insert id="batchInsert" parameterClass="java.util.Map">

	  

	 <iterate property="templateId" conjunction=";">  

		insert into SYSTEM_TEMPLATE_RELATE (ID,TEMPLATE_ID, TEMPLATE_GROUP_ID, CREATE_DATE, CREATE_USER)

	 values (SEQ_SYSTEM_TEMPLATE_RELATE.nextval,#templateId[]#, #templateGroupId#,#createDate#, #createUser#)

	 </iterate>



	</insert>



java测试类中调用的方法如下：
	@Test

	public void batchInsertTemplateRelate() {

		Map<String, Object>  map=new HashMap<String, Object>();

		List<Integer> list=new ArrayList<Integer>();

		list.add(111);

		list.add(222);

		list.add(333);

		list.add(444);

		map.put("templateId", list);

		map.put("createDate", new Date(new Date().getTime()));

		map.put("templateGroupId", 555);

		map.put("createUser", "ooo");

		

		

		

		System.out.println(templateGroupDAO.batchInsertTemplateRelate(map));

	}



ibatis 执行的sql代码如下：
2013-06-05 19:02:44,814 DEBUG java.sql.Connection - {conn-100000} Connection

2013-06-05 19:02:44,823 DEBUG java.sql.Connection - {conn-100000} Preparing Statement:             insert into SYSTEM_TEMPLATE_RELATE (ID,TEMPLATE_ID, TEMPLATE_GROUP_ID, CREATE_DATE, CREATE_USER)   values (SEQ_SYSTEM_TEMPLATE_RELATE.nextval,?, ?,?, ?)   ;     insert into SYSTEM_TEMPLATE_RELATE (ID,TEMPLATE_ID, TEMPLATE_GROUP_ID, CREATE_DATE, CREATE_USER)   values (SEQ_SYSTEM_TEMPLATE_RELATE.nextval,?, ?,?, ?)   ;     insert into SYSTEM_TEMPLATE_RELATE (ID,TEMPLATE_ID, TEMPLATE_GROUP_ID, CREATE_DATE, CREATE_USER)   values (SEQ_SYSTEM_TEMPLATE_RELATE.nextval,?, ?,?, ?)   ;     insert into SYSTEM_TEMPLATE_RELATE (ID,TEMPLATE_ID, TEMPLATE_GROUP_ID, CREATE_DATE, CREATE_USER)   values (SEQ_SYSTEM_TEMPLATE_RELATE.nextval,?, ?,?, ?)      

2013-06-05 19:02:44,915 DEBUG java.sql.PreparedStatement - {pstm-100001} Executing Statement:             insert into SYSTEM_TEMPLATE_RELATE (ID,TEMPLATE_ID, TEMPLATE_GROUP_ID, CREATE_DATE, CREATE_USER)   values (SEQ_SYSTEM_TEMPLATE_RELATE.nextval,?, ?,?, ?)   ;     insert into SYSTEM_TEMPLATE_RELATE (ID,TEMPLATE_ID, TEMPLATE_GROUP_ID, CREATE_DATE, CREATE_USER)   values (SEQ_SYSTEM_TEMPLATE_RELATE.nextval,?, ?,?, ?)   ;     insert into SYSTEM_TEMPLATE_RELATE (ID,TEMPLATE_ID, TEMPLATE_GROUP_ID, CREATE_DATE, CREATE_USER)   values (SEQ_SYSTEM_TEMPLATE_RELATE.nextval,?, ?,?, ?)   ;     insert into SYSTEM_TEMPLATE_RELATE (ID,TEMPLATE_ID, TEMPLATE_GROUP_ID, CREATE_DATE, CREATE_USER)   values (SEQ_SYSTEM_TEMPLATE_RELATE.nextval,?, ?,?, ?)      

2013-06-05 19:02:44,915 DEBUG java.sql.PreparedStatement - {pstm-100001} Parameters: [111, 555, 2013-06-05 19:02:44.805, ooo, 222, 555, 2013-06-05 19:02:44.805, ooo, 333, 555, 2013-06-05 19:02:44.805, ooo, 444, 555, 2013-06-05 19:02:44.805, ooo]

2013-06-05 19:02:44,915 DEBUG java.sql.PreparedStatement - {pstm-100001} Types: [java.lang.Integer, java.lang.Integer, java.sql.Timestamp, java.lang.String, java.lang.Integer, java.lang.Integer, java.sql.Timestamp, java.lang.String, java.lang.Integer, java.lang.Integer, java.sql.Timestamp, java.lang.String, java.lang.Integer, java.lang.Integer, java.sql.Timestamp, java.lang.String]



下面是报错的信息：


org.springframework.dao.DataIntegrityViolationException: SqlMapClient operation; SQL [];   

--- The error occurred while applying a parameter map.  

--- Check the SystemTemplateRelateDAO.batchInsert-InlineParameterMap.  

--- Check the statement (update failed).  

--- Cause: java.sql.SQLSyntaxErrorException: ORA-00911: invalid character

; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:   

--- The error occurred while applying a parameter map.  

--- Check the SystemTemplateRelateDAO.batchInsert-InlineParameterMap.  

--- Check the statement (update failed).  

--- Cause: java.sql.SQLSyntaxErrorException: ORA-00911: invalid character



	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:100)

	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)

	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)

	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)

	at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:212)

	at com.alibaba.cobar.client.CobarSqlMapClientTemplate.execute(CobarSqlMapClientTemplate.java:195)

	at org.springframework.orm.ibatis.SqlMapClientTemplate.delete(SqlMapClientTemplate.java:436)

	at com.alibaba.cobar.client.CobarSqlMapClientTemplate.delete(CobarSqlMapClientTemplate.java:265)

	at com.uuzz.lop.sp.dal.impl.SystemTemplateGroupDAOImpl.batchInsertTemplateRelate(SystemTemplateGroupDAOImpl.java:142)

	at test.sp.dal.SystemTemplateDAOTest.batchInsertTemplateRelate(SystemTemplateDAOTest.java:121)

	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

	at java.lang.reflect.Method.invoke(Method.java:601)

	at org.springframework.test.context.junit4.SpringTestMethod.invoke(SpringTestMethod.java:160)

	at org.springframework.test.context.junit4.SpringMethodRoadie.runTestMethod(SpringMethodRoadie.java:233)

	at org.springframework.test.context.junit4.SpringMethodRoadie$RunBeforesThenTestThenAfters.run(SpringMethodRoadie.java:333)

	at org.springframework.test.context.junit4.SpringMethodRoadie.runWithRepetitions(SpringMethodRoadie.java:217)

	at org.springframework.test.context.junit4.SpringMethodRoadie.runTest(SpringMethodRoadie.java:197)

	at org.springframework.test.context.junit4.SpringMethodRoadie.run(SpringMethodRoadie.java:143)

	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.invokeTestMethod(SpringJUnit4ClassRunner.java:160)

	at org.junit.internal.runners.JUnit4ClassRunner.runMethods(JUnit4ClassRunner.java:51)

	at org.junit.internal.runners.JUnit4ClassRunner$1.run(JUnit4ClassRunner.java:44)

	at org.junit.internal.runners.ClassRoadie.runUnprotected(ClassRoadie.java:27)

	at org.junit.internal.runners.ClassRoadie.runProtected(ClassRoadie.java:37)

	at org.junit.internal.runners.JUnit4ClassRunner.run(JUnit4ClassRunner.java:42)

	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:97)

	at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:49)

	at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)

	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)

	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)

	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)

	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)

Caused by: com.ibatis.common.jdbc.exception.NestedSQLException:   

--- The error occurred while applying a parameter map.  

--- Check the SystemTemplateRelateDAO.batchInsert-InlineParameterMap.  

--- Check the statement (update failed).  

--- Cause: java.sql.SQLSyntaxErrorException: ORA-00911: invalid character



	at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeUpdate(MappedStatement.java:107)

	at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update(SqlMapExecutorDelegate.java:457)

	at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.delete(SqlMapExecutorDelegate.java:480)

	at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.delete(SqlMapSessionImpl.java:98)

	at org.springframework.orm.ibatis.SqlMapClientTemplate$11.doInSqlMapClient(SqlMapClientTemplate.java:438)

	at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:209)

	... 28 more

Caused by: java.sql.SQLSyntaxErrorException: ORA-00911: invalid character



	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)

	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)

	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)

	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)

	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)

	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)

	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)

	at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1010)

	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1315)

	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3576)

	at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3677)

	at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1374)

	at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:437)

	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)

	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

	at java.lang.reflect.Method.invoke(Method.java:601)

	at com.ibatis.common.jdbc.logging.PreparedStatementLogProxy.invoke(PreparedStatementLogProxy.java:62)

	at com.sun.proxy.$Proxy11.execute(Unknown Source)

	at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.java:80)

	at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteUpdate(MappedStatement.java:216)

	at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeUpdate(MappedStatement.java:94)

	... 33 more

false
...全文
526 2 打赏 收藏 转发到动态 举报
写回复
用AI写文章
2 条回复
切换为时间正序
请发表友善的回复…
发表回复
zhangjian_xue 2013-06-06
  • 打赏
  • 举报
 回复
<insert id="insertDetectionValue" > insert into um.detection_value ( id, itemid, resid, resname, msg, detectiontime, ip, mark, propertyid, propertyname ) <foreach collection="list" item="item" index="index" separator="union all"> select #{item.id,jdbcType=INTEGER} ,#{item.itemid, jdbcType=INTEGER}, #{item.resid,jdbcType=INTEGER},#{item.resName,jdbcType=VARCHAR}, #{item.msg,jdbcType=VARCHAR}, sysdate,#{item.ip,jdbcType=VARCHAR},#{item.mark,jdbcType=VARCHAR}, #{item.propertyid,jdbcType=INTEGER},#{item.propertyName,jdbcType=VARCHAR} from dual </foreach> </insert> 我用的mybatis,和你那个ibatIS差距不大, 这是批量插入的例子.数据库是oracle
tony4geek 2013-06-06
  • 打赏
  • 举报
 回复
报的是sql字符集问题，是不是配置中的“；”
SpringSpringMVCMybatis框架
Spring框架是由于软件开发的复杂性而创建的。Spring使用的是基本的JavaBean来完成以前只可能由EJB完成的事情。然而，Spring的用途不仅仅限于...iBATIS提供的持久层框架包括SQL Maps和Data Access Objects（DAOs）
Java ibatis批量insert,ibatis批量insert是什么写法？要怎么编写？
与数据库的交互操作永远是一个框架的评判标准，什么项目都离不开数据库，这次我们来看看ibatis该怎么批量实现insert吧。在ibatis框架中使用回调接口就可以实现数据的批量操作，减少对数据库的访问次数，提高性能。例：/***ibatis批量插入数据*@paramrecordList*/publicvoidinsertRecordByList(finalListrecordLis...
关于oracle+ibatis批量insert的写法
现在编写代码，总时刻要求自己，不只是实现功能，而且要质量更高。 最近使用ibatis框架，其灵活的sql编写，能够颗粒度的优化，蛮不错的。 另外使用批量insert时，找了好久，终于被我找到一种合适的批量添加操作。强调一下，本人使用的是oracle数据库。 传到DAO层的是一个List集合类 insert all i
ibatis批量插入、批量修改
1、批量修改，比如mysql中执行批量修改的脚本语句： update zwt_workbench_app  set rank = case app_id when 1 then 20 when 2 then 21 when 3 then 22 end, set mod_id = case app_id when 1 then 3 when 2 then 4 when 3 the...
ibatis批量操作的实现
对iterate标签的属性的了解，即一些适用的场景，也对ibatis的批量进行了实现。
Java EE

67,516

社区成员

225,878

社区内容

发帖
与我相关
我的任务
社区描述
J2EE只是Java企业应用。我们需要一个跨J2SE/WEB/EJB的微容器，保护我们的业务核心组件（中间件），以延续它的生命力，而不是依赖J2SE/J2EE版本。
社区管理员
  • Java EE
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章