在做一个数据抽取的项目，框架采用spring+ibatis。批量插入数据的时候报 maximum open cursors exceeded错误

[q46639776]

数据源由用户自己设定，因此采用编码创建一个BoneCPDataSource,并创建sqlMapclient，将它缓存在一个Map集合里。

public SqlMapClient createSqlMapClient(DataSource dataSource) {
		SqlMapClientFactoryBean sqlMapClientFactoryBean = new SqlMapClientFactoryBean();
		try {
			sqlMapClientFactoryBean.setConfigLocation(new ClassPathResource("com/xyh/datacspt/sqlmap-datacspt.xml"));
			sqlMapClientFactoryBean.setDataSource(dataSource);
			sqlMapClientFactoryBean.afterPropertiesSet();
			sqlMapClientFactoryBean.setTransactionConfigClass(ExternalTransactionConfig.class);
		} catch (Exception e) {
			log.error("SqlMapClient初始化失败!", e);
			e.printStackTrace();
		}
		return sqlMapClientFactoryBean.getObject();
	}

以下是批量执行insert，插入数据操作，从Map里获取对应的SqlMapClient

public int insertList(Integer dataSourceId, final List<String> list_str) throws SQLException {
		SqlMapClientTemplate template = new SqlMapClientTemplate(this.getSqlMapClient(dataSourceId));
		return template.execute(new SqlMapClientCallback() {
			public Object doInSqlMapClient(SqlMapExecutor executor) throws SQLException {
				executor.startBatch();
				int batch = 0;
				for (String sql : list_str) {
					executor.insert("dynamic.executeSql", sql);
					batch++;
					if (batch == 100) {
						executor.executeBatch();
						batch = 0;
					}
				}
				executor.executeBatch();
				return 1;
			}
		});
	}

以下是插入第一个100条记录的日志信息:

{conn-100929} Connection
{conn-100929} Preparing Statement: INSERT INTO T_GW_JBXX_GR...
{conn-100929} Preparing Statement: INSERT INTO T_GW_JBXX_GR...
...

当执行executeBatch()后，才插入100条数据到数据表，以下是日志：

bonecp.StatementHandle.executeBatch(StatementHandle.java:452): INSERT INTO...
bonecp.StatementHandle.executeBatch(StatementHandle.java:452): INSERT INTO...
...

当执行第二个100条insert的时候，批量操作好象失效了，每执行一次insert("dynamic.executeSql", sql)就往数据表里插入一条，并且似乎每次都打开一个新的Statement，以下是日志：

{conn-101339} Preparing Statement: INSERT INTO...
{pstm-101440} Executing Statement: INSERT INTO...
{pstm-101440} Parameters: []
{pstm-101440} Types: []
com.jolbox.bonecp.PreparedStatementHandle.execute: INSERT INTO...
{conn-101339} Preparing Statement: INSERT INTO...
{pstm-101441} Executing Statement: INSERT INTO...
{pstm-101441} Parameters: []
{pstm-101441} Types: []
com.jolbox.bonecp.PreparedStatementHandle.execute: INSERT INTO...
{conn-101339} Preparing Statement: INSERT INTO...
{pstm-101442} Executing Statement: INSERT INTO...
{pstm-101442} Parameters: []
{pstm-101442} Types: []
com.jolbox.bonecp.PreparedStatementHandle.execute: INSERT INTO...
...

插入三百多条数据之后就抛出异常，以下是异常信息：

org.springframework.jdbc.UncategorizedSQLException: SqlMapClient operation; uncategorized SQLException for SQL []; SQL state [72000]; error code [1000];
--- The error occurred in com/xyh/datacspt/dao/dataExtraction/dynamic.xml.
--- The error occurred while applying a parameter map.
--- Check the dynamic.executeSql-InlineParameterMap.
--- Check the statement (update failed).
--- Cause: java.sql.SQLException: ORA-01000: maximum open cursors exceeded
; nested exception is com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in com/xyh/datacspt/dao/dataExtraction/dynamic.xml.
--- The error occurred while applying a parameter map.
--- Check the dynamic.executeSql-InlineParameterMap.
--- Check the statement (update failed).
--- Cause: java.sql.SQLException: ORA-01000: maximum open cursors exceeded

难道是因为没有让Spring来管理数据源么？还是没有配置事务的原因？这个问题困扰我好几天了，请各位帮个忙吧，感激不尽!

[q46639776]

没分了， 不好意思..

[9441]

oracle的cursors默认是300，肯定是你程序没关闭cursors造成游标超过了300 二个解决方法 1.修改ORACLE的配置，把cursors改大 2.修改你的程序，及时关闭游标