[以解决]请教iBatis批量插入数据问题

我使用的Spring +Ibatis

在我做批量从插入的时候遇到了数据插不进去的问题，请大家帮忙看看

 

数据访问接口的代码是：

	public void saveMessages(String oknos, String userId, String title,String msg){
		Map<String,Object> params = new HashMap<String,Object>();
		params.put("userId", userId);
		params.put("title", title);
		params.put("msg", msg);
		params.put("oknos", oknos);
		this.getSqlMapClientTemplate().insert("insertMessageByOKNOS", params);
	}

 

定义的IBATIS映射文件是：

	<insert id="insertMessageByOKNOS" >
		INSERT INTO scrip (scrip_id,loginer_id,myfriend_id,isread,msg,scrip_title,stime)
		SELECT scrip_id.NEXTVAL ,$userId$, member_register_id ,
		0,#msg#,#title#,SYSDATE FROM login_information  WHERE member_register_okno IN (#oknos#) 
	</insert>

 

 

调用保存方法就是插入不进去数据，也不出错，但是如果把定义的sql语句换成

	<insert id="insertMessageByOKNOS" >
 INSERT INTO scrip (scrip_id,loginer_id,myfriend_id,isread,msg,scrip_title,stime)  
  SELECT scrip_id.NEXTVAL ,123, member_register_id ,  
  0,'222222','222222',SYSDATE FROM login_information  WHERE member_register_okno IN ('45628846','45628847','45628849') 
	</insert>

 就可以插入，通过参数传递就不可以，下面是Spring的日志

2008-06-23 11:51:58,398 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] Creating new transaction with name [com.inc.i941ok.server.BaseManager.saveMessages]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT
2008-06-23 11:51:58,398 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] Acquired Connection [org.apache.commons.dbcp.PoolableConnection@2f6aea] for JDBC transaction
2008-06-23 11:51:58,398 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] Switching JDBC Connection [org.apache.commons.dbcp.PoolableConnection@2f6aea] to manual commit
2008-06-23 11:51:58,398 DEBUG [org.springframework.transaction.support.TransactionSynchronizationManager] Bound value [org.springframework.jdbc.datasource.ConnectionHolder@158bc22] for key [org.apache.commons.dbcp.BasicDataSource@1dea651] to thread [http-8080-2]
2008-06-23 11:51:58,398 DEBUG [org.springframework.transaction.support.TransactionSynchronizationManager] Initializing transaction synchronization
2008-06-23 11:51:58,398 DEBUG [org.springframework.transaction.interceptor.TransactionInterceptor] Getting transaction for [com.inc.i941ok.server.BaseManager.saveMessages]
2008-06-23 11:51:58,398 DEBUG [org.springframework.orm.ibatis.SqlMapClientTemplate] Opened SqlMapSession [com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl@a76306] for iBATIS operation
2008-06-23 11:51:58,398 DEBUG [org.springframework.transaction.support.TransactionSynchronizationManager] Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@158bc22] for key [org.apache.commons.dbcp.BasicDataSource@1dea651] bound to thread [http-8080-2]
2008-06-23 11:51:58,398 DEBUG [java.sql.Connection] {conn-100008} Connection
2008-06-23 11:51:58,398 DEBUG [org.springframework.orm.ibatis.SqlMapClientTemplate] Obtained JDBC Connection [Transaction-aware proxy for target Connection [org.apache.commons.dbcp.PoolableConnection@2f6aea]] for iBATIS operation
2008-06-23 11:51:58,398 DEBUG [java.sql.Connection] {conn-100008} Preparing Statement:    INSERT INTO scrip (scrip_id,loginer_id,myfriend_id,isread,msg,scrip_title,stime)   SELECT scrip_id.NEXTVAL ,123, member_register_id ,   0,?,?,SYSDATE FROM login_information  WHERE member_register_okno IN (?)  
2008-06-23 11:51:58,398 DEBUG [org.springframework.transaction.support.TransactionSynchronizationManager] Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@158bc22] for key [org.apache.commons.dbcp.BasicDataSource@1dea651] bound to thread [http-8080-2]
2008-06-23 11:51:58,398 DEBUG [java.sql.PreparedStatement] {pstm-100009} Executing Statement:    INSERT INTO scrip (scrip_id,loginer_id,myfriend_id,isread,msg,scrip_title,stime)   SELECT scrip_id.NEXTVAL ,123, member_register_id ,   0,?,?,SYSDATE FROM login_information  WHERE member_register_okno IN (?)  
2008-06-23 11:51:58,398 DEBUG [java.sql.PreparedStatement] {pstm-100009} Parameters: [qqqqqqqqqqqqqqqq, qqqqqqqq, 45628846,45628847,45628849]
2008-06-23 11:51:58,398 DEBUG [java.sql.PreparedStatement] {pstm-100009} Types: [java.lang.String, java.lang.String, java.lang.String]
2008-06-23 11:51:58,414 DEBUG [org.springframework.transaction.support.TransactionSynchronizationManager] Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@158bc22] for key [org.apache.commons.dbcp.BasicDataSource@1dea651] bound to thread [http-8080-2]
2008-06-23 11:51:58,414 DEBUG [org.springframework.transaction.interceptor.TransactionInterceptor] Completing transaction for [com.inc.i941ok.server.BaseManager.saveMessages]
2008-06-23 11:51:58,414 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] Triggering beforeCommit synchronization
2008-06-23 11:51:58,414 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] Triggering beforeCompletion synchronization
2008-06-23 11:51:58,414 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] Initiating transaction commit
2008-06-23 11:51:58,414 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] Committing JDBC transaction on Connection [org.apache.commons.dbcp.PoolableConnection@2f6aea]
2008-06-23 11:51:58,414 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] Triggering afterCommit synchronization
2008-06-23 11:51:58,414 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] Triggering afterCompletion synchronization
2008-06-23 11:51:58,414 DEBUG [org.springframework.transaction.support.TransactionSynchronizationManager] Clearing transaction synchronization
2008-06-23 11:51:58,414 DEBUG [org.springframework.transaction.support.TransactionSynchronizationManager] Removed value [org.springframework.jdbc.datasource.ConnectionHolder@158bc22] for key [org.apache.commons.dbcp.BasicDataSource@1dea651] from thread [http-8080-2]
2008-06-23 11:51:58,461 DEBUG [org.springframework.jdbc.datasource.DataSourceTransactionManager] Releasing JDBC Connection [org.apache.commons.dbcp.PoolableConnection@2f6aea] after transaction
2008-06-23 11:51:58,461 DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] Returning JDBC Connection to DataSource
2008-06-23 11:51:58,461 DEBUG [org.springframework.orm.ibatis.SqlMapClientTemplate] Opened SqlMapSession [com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl@1563e06] for iBATIS operation
2008-06-23 11:51:58,461 DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] Fetching JDBC Connection from DataSource

 

 

评论

3 楼 baiyangshu20081104 2009-03-04  

利用log4j输出SQL语句，就能很快找到问题所在。

2 楼 hf200012 2008-06-23  

问题以解决，是因为#oknos#这个地方的#换成$就可以，因为Ibatis认为这是一个字符串in语句里面就变成了in ('232323,433434,5656')，实事上要的是in (232323,433434,5656)

1 楼 firefox_1983 2008-06-23  

oknos,是什么格式的?是'45628846','45628847','45628849'这样的吗？
表面上看不出什么问题，唯一觉得可能的就是oknos的格式问题