[TIP]关于Java开发中使用Oracle数据库的一点注意事项（原创）

lastwinner

最初由 cc59 发布
[B]

确实是要经常重用.

但是也要看你如何来处理了.不可能在一处循环里面无限次的来使用.那样肯定会出现连接数不够用的情况了. [/B]

那这种情况下，connection就应该在循环外定义
而不是在循环中反复get然后又colse

ASongjason

我正巧碰上一个类似的游标溢出问题，不过是使用hibernate招致的，和大家一起分析
软件环境：
Oracle 8i
JDK 1.4.2
Hibernate 3.0

Java代码如下：
java代码:  


net.sf.hibernate.cfg.Configuration  config = ...
SessionFactory sessions = config.buildSessionFactory();
Session dataSession = sessions.openSession();
Transaction tx = dataSession.beginTransaction();
for(int i=0; i<A_BIG_NUM; i++) {
  ...
  dataSession.saveOrUpdate(...)
}
tx.commit();
dataSession.close();



<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD//EN" "http://hibernate.sourceforge.net/hibernate-configuration-2.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="connection.url">jdbc:mysql://localhost/ASong</property>
<property name="connection.username">ASong</property>
<property name="connection.password">123456</property>
<property name="dialect">net.sf.hibernate.dialect.MySQLDialect</property>
<!-- jakarta commond DBCP Connection Pool -->
<property name="hibernate.connection.provider_class">net.sf.hibernate.connection.DBCPConnectionProvider</property>
<property name="hibernate.dbcp.maxActive">300</property>
<property name="hibernate.dbcp.whenExhaustedAction">1</property>
<property name="hibernate.dbcp.maxWait">3000</property>
<property name="hibernate.dbcp.maxIdle">10</property>
<property name="hibernate.cache.provider_class"> net.sf.hibernate.cache.HashtableCacheProvider</property>
<!-- prepared statement cache -->
<property name="hibernate.dbcp.ps.maxActive">300</property>
<property name="hibernate.dbcp.ps.whenExhaustedAction">1</property>
<property name="hibernate.dbcp.ps.maxWait">3000</property>
<property name="hibernate.dbcp.ps.maxIdle">100</property>
<!-- optional query to validate pooled connections: -->
<property name="hibernate.dbcp.validationQuery select">1</property>
<property name="hibernate.dbcp.testOnBorrow">true</property>
<property name="hibernate.dbcp.testOnReturn">false</property>
<!-- jakarta commond DBCP Connection Pool -->
<property name="show_sql">false</property>
<property name="transaction.factory_class">net.sf.hibernate.transaction.JDBCTransactionFactory</property>
<property name="hibernate.jdbc.fetch_size">50</property>
<property name="hibernate.jdbc.batch_size">30</property>
<mapping resource="ASong.hbm.xml"/>
</session-factory>
</hibernate-configuration>
错误提示信息：
prepareStatement from pool failed, cause: ORA-01000: maximum open cursors exceeded
at org.apache.commons.dbcp.PoolingConnection.prepareStatement(PoolingConnection.java:136)
at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:216)
at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:323)
at net.sf.hibernate.impl.BatcherImpl.getPreparedStatement(BatcherImpl.java:249)
at net.sf.hibernate.impl.BatcherImpl.prepareStatement(BatcherImpl.java:61)
at net.sf.hibernate.impl.BatcherImpl.prepareStatement(BatcherImpl.java:56)
at net.sf.hibernate.impl.BatcherImpl.prepareBatchStatement(BatcherImpl.java:109)
at net.sf.hibernate.persister.EntityPersister.update(EntityPersister.java:660)
... 9 more
Caused by: java.sql.SQLException: ORA-01000: maximum open cursors exceeded
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oopen.receive(Oopen.java:120)
at oracle.jdbc.ttc7.TTC7Protocol.open(TTC7Protocol.java:586)
at oracle.jdbc.driver.OracleStatement.<init>(OracleStatement.java:385)
at oracle.jdbc.driver.OracleStatement.<init>(OracleStatement.java:413)
at oracle.jdbc.driver.OraclePreparedStatement.<init>(OraclePreparedStatement.java:119)
at oracle.jdbc.driver.OraclePreparedStatement.<init>(OraclePreparedStatement.java:92)
at oracle.jdbc.driver.OracleConnection.privatePrepareStatement(OracleConnection.java:950)
at oracle.jdbc.driver.OracleConnection.prepareStatement(OracleConnection.java:802)
at org.apache.commons.dbcp.PoolingConnection.makeObject(PoolingConnection.java:223)
at org.apache.commons.pool.impl.GenericKeyedObjectPool.borrowObject(GenericKeyedObjectPool.java:792)
at org.apache.commons.dbcp.PoolingConnection.prepareStatement(PoolingConnection.java:130)
... 16 more

我的分析：
估计是需要适当地 close statement，问题是这些工作在hibernate下是怎么实现的？

spiritfrog

原来是这样，真的很感谢

suixing2k66

上次遇到这个问题还不知道怎么解决呢

zhpsam

刚开始做java项目，学习！

jsujs

虽然我现在还看不太懂,但是也一样支持斑竹,
相信有一天我也会明白的 !呵呵

CharlesLi

谢谢，受益多多啊

maker.zhang

又學習了一次..,.3ks.

hphubei

写程序需要注意代码的严谨，这些错误可能在现在你的测试里面不会出现，但是如果随着用户数目的增加会不会呢？

assion

　可在finally { }
把打开的资源关闭掉