JDBC数据库连接池connection关闭后Statement和ResultSet未关闭的问题

转载 2017年04月14日 11:59:40

本文转自:http://k1121.iteye.com/blog/1279063


(1)    主要问题


针对关闭connection是否会自动关闭Statement和ResultSet的问题,以及Statement和ResultSet所占用资源是否会自动释放问题,JDBC处理规范或JDK规范中做了如下描述:

JDBC处理规范

JDBC. 3.0 Specification——13.1.3 Closing Statement Objects

An applicationcalls the method Statement.close to indicate that it has finished processing astatement. All Statement objects will be closed when the connection thatcreated them is closed. However, it is good coding practice for applications toclose statements as soon as they have finished processing them. This allows anyexternal resources that the statement is using to be released immediately.

Closing aStatement object will close and invalidate any instances of ResultSet producedby that Statement object. The resources held by the ResultSet object may not bereleased until garbage collection runs again, so it is a good practice toexplicitly close ResultSet objects when they are no longer needed.

These commentsabout closing Statement objects apply to PreparedStatement and CallableStatementobjects as well.

JDBC. 4.0 Specification——13.1.4 Closing Statement Objects

An applicationcalls the method Statement.close to indicate that it has finished processing astatement. All Statement objects will be closed when the connection that createdthem is closed. However, it is good coding practice for applications to closestatements as soon as they have finished processing them. This allows any externalresources that the statement is using to be released immediately.

Closing aStatement object will close and invalidate any instances of ResultSet producedby that Statement object. The resources held by the ResultSet object may not bereleased until garbage collection runs again, so it is a good practice to explicitlyclose ResultSet objects when they are no longer needed.

Once aStatement has been closed, any attempt to access any of its methods with theexception of the isClosed or close methods will result in a SQLException beingthrown.

These commentsabout closing Statement objects apply to PreparedStatement andCallableStatement objects as well.

规范说明:connection.close 自动关闭 Statement.close 自动导致 ResultSet 对象无效(注意只是 ResultSet 对象无效,ResultSet 所占用的资源可能还没有释放)。所以还是应该显式执行connection、Statement、ResultSet的close方法。特别是在使用connection pool的时候,connection.close 并不会导致物理连接的关闭,不执行ResultSet的close可能会导致更多的资源泄露。

JDK处理规范:

JDK1.4

Note: A ResultSet object is automatically closed by theStatement object that generated it when that Statement object is closed,re-executed, or is used to retrieve the next result from a sequence of multipleresults. A ResultSet object is also automatically closed when it is garbagecollected.

Note: A Statement object is automatically closed when it isgarbage collected. When a Statement object is closed, its current ResultSetobject, if one exists, is also closed.

Note: A Connection object isautomatically closed when it is garbage collected. Certain fatal errors alsoclose a Connection object.

JDK1.5

Releases this ResultSet object’sdatabase and JDBC resources immediately instead of waiting for this to happenwhen it is automatically closed.

Note: A ResultSet object isautomatically closed by the Statement object that generated it when thatStatement object is closed, re-executed, or is used to retrieve the next resultfrom a sequence of multiple results. A ResultSet object is also automaticallyclosed when it is garbage collected.

规范说明:

1.垃圾回收机制可以自动关闭它们;

2.Statement关闭会导致ResultSet关闭;

3.Connection关闭不一定会导致Statement关闭。


V6使用的是数据库连接池,Connection关闭并不是物理关闭,只是归还连接池,所以Statement和ResultSet有可能被持有,并且实际占用相关的数据库的游标资源,在这种情况下,只要长期运行就有可能报“游标超出数据库允许的最大值”的错误,导致程序无法正常访问数据库。

(2)    解决建议

(1)      由于垃圾回收的线程级别是最低的,为了充分利用数据库资源,有必要显式关闭它们,尤其是使用Connection Pool的时候;

(2)      最优经验是按照ResultSet,Statement,Connection的顺序执行close;

(3)      为了避免由于java代码有问题导致内存泄露,需要在rs.close()和stmt.close()后面一定要加上rs = null和stmt = null;

(4)      如果一定要传递ResultSet,应该使用RowSet,RowSet可以不依赖于Connection和Statement。Java传递的是引用,所以如果传递ResultSet,你会不知道Statement和Connection何时关闭,不知道ResultSet何时有效。

JDBC优化之手写数据库连接池技术

-
  • 1970年01月01日 08:00

使用连接池一定要正确关闭PreparedStatement和ResultSet

使用jdbc连接数据库,获取数据库连接的过程在网络上其他文章中多有介绍,在此不多介绍,本文主要分析获取数据库连接并且完成处理过程后如何处理这个连接。 先看一个简单的模拟数据库连接池获取连接的实例...
  • zf_1024
  • zf_1024
  • 2017-09-15 13:08:43
  • 1816

[疯狂Java]JDBC:Statement、ResultSet、连接资源自动关闭、Properties配置文件

1. 建立连接得到Connection对象后如何继续操作数据库?     1) 查询数据库就必须要执行SQL语句,可以通过Connection对象conn获取一个代表要提交给数据库的SQL语句的句柄,...
  • Lirx_Tech
  • Lirx_Tech
  • 2016-04-12 11:23:13
  • 1874

大家来说一说关于jdbc的那些事情,关于Connection,Statement和ResultSet的关闭顺序

以下只是个人观点,毕竟楼主只是大三学生一枚。还有很多需要学习的地方。 最近在做一个jsp+servlet的程序。 用的是Oracle数据库,然后安装的时候很多东西都是默认值。 用servlet,...
  • u012916287
  • u012916287
  • 2014-12-23 21:37:26
  • 5502

JDBC中关于Connection, PreparedStatement, ResultSet是否关闭的一些思考

如果你不使用连接池,那么就没有什么问题,一旦Connection关闭,数据库物理连接就被释放,所有相关Java资源也可以被GC回收了。 但是如果你使用连接池,那么请注意,Connection关闭并不...
  • wc0077
  • wc0077
  • 2016-04-07 10:25:58
  • 7228

JDBC中,如果ResultSet 和 Statement 不关闭的话,会有什么影响

  • 2010年03月08日 10:20
  • 167KB
  • 下载

怎样实现关闭connection时自动关闭Statement和ResultSet

转自:怎样实现关闭connection时自动关闭Statement和ResultSet 关闭数据连接时, 一定也要关闭Statement和ResultSet, 不然在并发量较大的时候可能...
  • diligentcat
  • diligentcat
  • 2017-07-27 10:18:21
  • 481

jdbc 中Connection DriverManager ResultSet Statement 等类的含义及常用方法

类Connection (与特定数据库的连接。在连接上下文中执行 SQL 语句并返回结果) 它的方法:1. createStatement() 创建一个 Statement 对象来将 SQL...
  • WenwenFighting1314
  • WenwenFighting1314
  • 2016-08-12 15:47:33
  • 706

jdbc的con、pstmt、rs的非正常关闭顺序探讨

首先大家都知道,正常的关闭顺序都是ResultSet、PreparedStatement、Connection的顺序,这里就不作讨论了 Connection:与特定数据库的连接(会话)。 Prep...
  • wxwzy738
  • wxwzy738
  • 2012-08-03 10:25:16
  • 6440

JDBC之使用Statement,PreparedStatement,ResultSet

1. 创建一个获取 Connection 对象和关闭资源的工具类 在对数据库进行CRUD操作的时候,每一个操作都需要获取Connection对象,所以我们就可以把获取Connection对象的过...
  • hustwht
  • hustwht
  • 2016-08-12 13:07:10
  • 1292
收藏助手
不良信息举报
您举报文章:JDBC数据库连接池connection关闭后Statement和ResultSet未关闭的问题
举报原因:
原因补充:

(最多只允许输入30个字)