About the shutdown of Oracle connections

Do you have a question about "resultset the value after closing the connection" on the Internet? See several times this view:

Release Date: 2005-01-26 Robbin

If you do not use the connection pool, then there is no problem, once the connection is closed, the database physical connection is released, all related Java resources can also be collected by GC.

But if you use connection pooling, then note that connection shutdown is not physical shutdown, just returning the connection pool, so preparedstatement and ResultSet are both held and actually occupy the relevant database cursor resources, in which case, long-running , the error "cursor exceeds the maximum allowed for the database" is reported, causing the program to fail to access the database properly.

Without a connection pool, if you close the connection directly, the corresponding Statement,resultset object should be driver vendor to help you close it. That is the release of resources by him. This is referred to in the JDBC3.0 specification. Because some database resources may fall within the scope that GC cannot release.
For connection pooling implementations, there is time for me to look at the implementation of Oracle and Postgres. But I think the situation above should be understood as an issue that is not implemented by specification as a connection pool vendor. If the connection pool is implemented using the database itself, this should not happen.

Release Date: 2005-01-26 Seantan

Without a connection pool, if you close the connection directly, the corresponding Statement,resultset object should be driver vendor to help you close it. That is the release of resources by him. This is referred to in the JDBC3.0 specification. Because some database resources may fall within the scope that GC cannot release.

For connection pooling implementations, there is time for me to look at the implementation of Oracle and Postgres. But I think the situation above should be understood as an issue that is not implemented by specification as a connection pool vendor. If the connection pool is implemented using the database itself, this should not happen.

Release Date: 2005-03-28 qilong2000

This is not related to the use of Conn pool does not have a relationship, the general operation is that, the thread gets a conn from the outside world, then creates its own stmt,rs, then performs the logical operation and then returns the Conn to the pool. If the programmer forgets to manually shut down the conversation. When this thread is finished, the stmt,rs are garbage, and when they are collected by garbage, the GC will turn them off to the ground. That's a lot of code that doesn't shut down and still works.
But there is a potential problem with this. Is that the GC cannot determine when to run. If there is a lot of free memory, it is likely that some GC will not be started, so that the stmt has not been shut down, execution for some time will be error.
So robust code should manually turn the rs,stmt off,

The original source of the search is: http://www.iteye.com/topic/10118

See a discussion about JDBC Resource recycling: http://www.blogjava.net/fantasy/archive/2006/09/01/67012.html

The original source of the search is: http://www.jdon.com/13373

We use the connection pool to access the database, is not the connection after shutting down the statement and result will automatically shut down? So you just need to close connection?
So this is not as long as the try{...} Catch{...} Finnally{conn.close ();} In such a framework, it is certain that there will be no connection resource usage.
After I have tested on the DB2, I do not know if the different JDK or as environment will differ? I have been unable to fully affirm this problem, please enlighten us! I just had to develop the habit of closing the other object one by one before closing the connection, but I'm not going to have this problem, but I've wrapped up a simple query and update operation into a common function of a base class for the sake of laziness, and it's easy to call it in code as follows:
public static ResultSet sysselect (Connection conn,string sql) throws sqlexception{
Statement st = null;
st = Conn.createstatement ();
return st.executequery (SQL);
}
Since such features are used anytime and anywhere, this has to make me consider this function to produce intermediate objects statement ST's lifetime Problem! *_* -------------------------------------------------------------------------------------
Yes, at the very least, database-side resources are released.
But this is not absolute, because statement,result corresponds to Curior, and some
The curior of the DB is share,
In short, close the connection will be able to-------------------------------------------------------------------------------------
You mean, at some point, it's a release of connection, and maybe some of the statement and ResultSet still occupy database resources?
If this is the case, then at this time the connection between Java and the database is broken, but because there is no pre-release of these statement and Resultsret resources at the time of connection (the database should be understood as cursors or other resources), it will affect the performance of the database? This does not seem to be confirmed from the program! -------------------------------------------------------------------------------------
> You mean, at some point, it's a connection, maybe a part of it.
> Tatement and ResultSet also occupy database resources?
No, the local statement and ResultSet will be recycled, but the database-side resources may not be released, but you don't have to worry about it, it's not a bad thing, just like the database connection pool you use, the database can also cache the Curior. After all, frequent creation of Curior is relatively expansive for db.
Of course, if the database Share_curior parameter is not set to 0--------------------------------------------------------------------------------- ----
If the local object is not used, it can be released, but when the TCP connection is released is a problem, and the TCP connection and connection corresponds to the ResultSet, which seems to be unknown, but it seems that Java appears to correspond with connection, Otherwise, it is only necessary to maintain a long-connected connection object globally, and only consider the problem of the build (availability) without considering the shutdown problem (unless for performance purposes, you can consider the implementation of multi-connection locking, and no need for the user to shut down), ADO seems to be able to do so. If the TCP connection is too large, the database will be dragged down. As for Conn, the dependency between RS and St seems unclear, so I'm going to write an implementation of the ResultSet interface at this point, then overload the Close method, go to one by one to close its dependent objects, and even you can close the final function, but the latter doesn't seem to be much use. -------------------------------------------------------------------------------------
Statement also to Close, I found in the use, if the only call Connection.close, in fact connection will not immediately close, may have to wait until the garbage collection statement connection will close, The database I am using is SQL Server. -------------------------------------------------------------------------------------
I have tried to put the connection object as a static property in a class DbConnection, then, after establishing a connection, all other places to use the connection are called
Dbconnection.connection,
At last, I thought it was a brilliant idea, but it turned out to be a no-no. Connection is used once, the call dbconnection.connection is not.
But I think the idea should be right, I do not know whether there are comrades have tried as for statement, it is not difficult to turn off, you can be in a class to even a database will be declared as static connection, so that in any place after use, it can be very convenient to turn off the------- ------------------------------------------------------------------------------
All to close, close connection after statement and result can still be used, you can close the connection, and then try to traverse the result or can get the data, result does not close will cause the cursor out of scope and other errors-------- -----------------------------------------------------------------------------
Navyzhu and RTM: What database did you test to get this result?
The results I tested on DB2 and ORACLE8.1.2 were clearly prompted: SQLException: Closed connection .... (all conn,st,rs are used in the default way)-------------------------------------------------------------------------------------
So it seems that Odbc/jdbc's design is not very suitable now, why there is Conn, St's existence is because the hope can be reused, reduce overhead, but increase the burden of the programmer, may not be a few lines of code, the object and close the object is half. Even reuse is difficult to achieve, because many pages may only access one or two of databases, if you do not have their own design or the underlying connection pool, page switching is consumed or consumed. Even if this is not the case, according to the general standard of design, we need to refine the granularity, so that the result is the same as above, if you want to reuse, you must attach the redundant coupling variables, more bad.
In turn, there is a connection pool, also basically do not need Conn, or St, the latter, according to the experience of the user, it seems can be expressed as the RS can be closed or directly reused. Conn is also reusable (otherwise it cannot connect to the pool), but probably needs to be closed after the previous return resultset, which is what I said and the TCP connection concept, this is different from ADO. -------------------------------------------------------------------------------------
Once connection is turned off, all resultset opened by it are not available. This is described in the JDK documentation. Otherwise, it is not necessary to define javax.sql.RowSet. -------------------------------------------------------------------------------------
I use the database is SQLServer2000, the program is a data-month program, beginning I found that the knot sometimes success, sometimes failed to see the backstage only to find that because statement did not close, so connection too much to open hundreds of connection, Result processing failed. -------------------------------------------------------------------------------------
Statement,resultset must have been shut down, but the resources haven't been released yet. If you consider the performance aspects, a certain obvious closure of the Statement,resultset, there are two points to note:
1, do not implicitly produce statement,resultset objects
2, close object must be closed in finally-------------------------------------------------------------------------------------
A new specification is proposed in the JDBC3.0 specification: statement is also cached (typically implemented by Application server vendors) so that statement can be shared between different connection. So I personally think that before releasing connection, it is best to close Statement first, which is beneficial to the reuse of Statement (and this is the better programming habit recommended by the specification). -------------------------------------------------------------------------------------
Statement and PreparedStatement have already encapsulated the database operations well, so I never do a class like this again. In addition, connection resources must be used off. Do not return ResultSet objects, use rowset instead. -------------------------------------------------------------------------------------
In Oracle, if statement is not turned off, Oracle will end up with an error
Ora-1000:max cursor exceeded. You can query the cursor that is not closed in v$open_cursor. It's better to shut up when you're done. -------------------------------------------------------------------------------------
One thing should be unified, right? That is: When the direct connection.close () of the Java side of the St object resources must have been released, and the corresponding cursor resources on the DB side may not be released immediately, different DB vendors for this situation has a different resource recovery strategy.
But the question is: Is it as Oldma said, we can also take the db end of the resource recycling as a black box (or a self-adjusting container) to look at, and do not have to care about its overflow or blocking??? As we are now often using the same connection pool!!
1) If this is the case, then we can only deal with the connection shutdown, because the connection shutdown will automatically cause the Java side of the RS, St and PST Shutdown, and on the DB side, there is the cache can be reused these are not explicitly released cursor resources, So that these resources are not suspended;
2) If this is not the case, then we must manually close the RS, St and PST objects corresponding to the database resources to free the db-side resources, and then close Conn at the end, because the DB does not intelligently dispatch these suspended resources, do not actively close them will cause the DB resource is exhausted. By answering this question, would you please tell me that the function I wrote when I posted it was very frequently called? Because this function does not handle any close () operation, it simply returns RS to the caller based on the caller's connection argument and string argument, and at the end of the caller's code there will be Rs.close () and Connection.close () operations, But there is certainly no st.close (). So the St that is not explicitly close () is something that I have not been aware of, and is repeated so frequently (in fact such a call is as much as out.print (), what is the db end? -------------------------------------------------------------------------------------
For frequently called functions Connecton and statement absolutely want to close, as other netizens say, different databases may have different implementations, but considering the reuse and database porting and other issues, or close bar. -------------------------------------------------------------------------------------
That method must have a problem, do not say that the server side of the problem, the optical client may have problems, such as a loop, you open many times of resultset and statement, this object will not be released immediately.
And as I said before, there are a few things:
1, resultset do not rely on connection and statement, so you only need a connection and a statement, so you can use these two objects each time, Then you just have to connection the same as the statement, and the middle user has to shut it down resultset
2, ResultSet relies on connection and statement, so you have to carefully design the intermediate process, in the same namespace can not simultaneously establish two ResultSet objects.
3, ResultSet depends on the statement object, but does not depend on the connection object, so that you do not close statement may be the same as the first case does not close resultset. -------------------------------------------------------------------------------------
In advance, I do not often write database code.
But I have a doubt that according to Java doc,connection, Statement, resultset should be automatically released when garbage collection. So theoretically they should not have the problem of resource leakage. It is possible to explicitly call connection's close to release database resources faster, but I think using connection pool should be a more efficient practice. But for the case of using Connection pool, is it necessary to explicitly close Connection? Statement and ResultSet should have no need to explicitly release it, at least never heard of the statement pool and resultset pool, does that mean they don't use DB resources?
All is just my speculation, I hope that someone can come up with a credible test plan to overturn or verify it. -------------------------------------------------------------------------------------
Specification: Connection.close auto-shutdown statement.close automatically causes the ResultSet object to be invalid, note that only the ResultSet object is invalid, and the resources that are used by ResultSet may not be released. Therefore, the Close method of connection, Statement, resultset should be executed explicitly. In particular, when using connection pool, Connection.close does not cause the physical connection to close, and closing of resultset does not result in more resource leaks. Excerpt from JDBC. 3.0 specification,13.1.3 Closing Statement Objects An application calls the method Statement.close to indicate the IT have Finished processing a statement. All Statement objects'll be closed when the connection, created them is closed. However, it is good coding practice for applications to close statements as soon as they has finished processing them. This allows any external resources, the statement is a using to being released immediately. Closing a Statement object would close and invalidate any instances of the ResultSet produced by that Statement object. The resources held by the ResultSet object could be released until garbage collection runs again, so it's a good practi Ce to explicitly close ResultSet objects when they is nO longer needed. These comments about closing Statement objects apply to PreparedStatement and CallableStatement objects as well. -------------------------------------------------------------------------------------
I think it's reasonable for you to write this way: public static ResultSet sysselect (Connection conn,string sql) throws sqlexception{
Statement st = null;
try{
st = Conn.createstatement ();
ResultSet rs = st.executequery (SQL);
Return RS;
}catch (SQLException ex) {
Throw ex;
}finally{
if (null!=st) st.close ();
}
} -------------------------------------------------------------------------------------
To Glassprogrammer, did you say the method has done the test, I think from the principle that does not make sense.
The St.close () in finally causes the ResultSet instance it produces to be invalid, that is, the RS that the method returns the parameter is invalid when the method executes. I don't know what I think, right? -------------------------------------------------------------------------------------
Depending on the actual experience in the project, statement should be explicitly closed, perhaps with the corresponding database vendor for the database resources and the JDBC driver implementation is different, in order to avoid the resource is not released in time, before connection shutdown, explicitly shut down statement is necessary. My practice is to make a wrapper for the connection object, which is actually like a proxy, to record its reference when getting statement, and then to check if statement in the record's reference has been closed when calling connection's Close method. If you call its Close method off without closing, the effect is good, depending on the feedback you actually applied. -------------------------------------------------------------------------------------
Thank you very much for your advice here! While this may seem a bit of a dead-over from the program, it helps me understand the huge difference in the efficiency of code execution in high-frequency situations!
I personally think: in a big swallow data volume and access rate of the information system, its robustness in addition to using some Advanced API library or architecture, the key lies in the code writing habits, or we often use, seemingly dispensable some of the code to write the way! To Glassprogrammer: After closing the connection can also use the discussion of RS, I have seen similar discussions elsewhere, but I used to DB2, ORACLE812 test results are not feasible! Don't know where you are setting this feature? But for the last argument using conn.createstatement (int resultsettype, int resultsetconcurrency, int resultsetholdability), you can do this! But this is the jdbc3.0 function, I am now in the IBM environment, IBM's own JDK is only equivalent to the Sun jre1.3.1 version, there is no such function can not be tested. -------------------------------------------------------------------------------------
According to the test, off statement, ResultSet does not matter, as long as the connection off, automatically shut down the former two, if said statement, ResultSet did not shut down, will make the DB end resources can not be released, But in the Connection.close () event, Statement.close ()->resultset.close () is called, and you call Statement.close (), Resultset.close () What did you do once? The cursor, I tested the oracle,sql2000, as long as the connection.close,cursor is automatically closed.
To Navyzhu: You're sure there are problems elsewhere. I did the experiment, try{.
Class.forName ("Com.microsoft.jdbc.sqlserver.SQLServerDriver");
for (int i=0; i<100; i++) {
Connection conn = Drivermanager.getconnection (
"Jdbc:microsoft:sqlserver://akun:1433;databasename=bookstore", "sa",
"");
Statement stm = Conn.createstatement ();
ResultSet rs = stm.executequery ("SELECT * from book");
Stm.close ();
Conn.close ();
}
After execution, in SQL Query Analyzer sp_who, do not account for the connection ah.
-------------------------------------------------------------------------------------
Sorry, actually connection.close just disconnected the connection with the DB, it actually did not go off statement, ResultSet. After Connection.close, resultset is invalid because it checks whether the connection is valid until the resultset operation. Therefore, such as Next,first and other operations will be wrong. There is no problem with other data-independent operations.
Be sure to close the resultset, otherwise the resource will leak, because ResultSet has a stream object, close will call Stream.Close. As for the Mysapphire problem, one simple way is to call Resultset.getstatement () again. Close is fine. I do not know what other experts have other ideas. -------------------------------------------------------------------------------------
Two days ago two Bea company to do technical support, said our code has a problem caused by the memory leak, let us after Rs.close () and stmt.close () must be added rs = null and stmt = null. Blast the cold!
-------------------------------------------------------------------------------------
Note:a ResultSet object is a automatically closed by the Statement object that generated it when this Statement object is C losed, re-executed, or is used to retrieve the next result from a sequence of multiple results. A ResultSet object is also automatically closed if it is garbage collected. Note:a Statement object is automatically closed if it is garbage collected. When a Statement object was closed, its current ResultSet object, if one exists, is also closed. Note:a Connection object is automatically closed if it is garbage collected. Certain fatal errors also close a Connection object. Excerpt from JDK1.4 visible
1. Garbage collection mechanism can automatically close them
2.Statement shutdown causes ResultSet to close
3.Connection off does not (? ) causes statement to close
4. Because the thread level of garbage collection is the lowest, it is necessary to explicitly close them in order to make full use of the database resources, especially when using connection pool.
5. The best experience is to execute close in the order of Resultset,statement,connection
6. If you must pass resultset, you should use Rowset,rowset to be independent of connection and statement. Java passes a reference, so if you pass resultset, you don't know when statement and connection are closed and don't know when resultset will work. ------------------------------------------------------------------------------------- That's what I'm dealing with: Put this method in DAO Factory. Baseimpl This method is called in the base class of the implementation of all DAO interface objects. In addition, there are getconnection. Thus, in each of my Impl classes, after using these resources, the last of the try, I use Close (RS, PST, Conn) to close the related database resources (because it is a reference, so I think I can reduce the code). Getconnection () is directly connected.

Would you please help me to judge, so that there is a problem?

 PublicStaticvoidClose (Java.sql.ResultSet RS,
Java.sql.PreparedStatement PST,
Java.sql.Connection Conn
) {
if(rs! =NULL) {
Try{
Rs.close ();
rs =NULL;
}Catch(SQLException se) {
Se.printstacktrace ();
}finally{
if(rs! =NULL) {
Try{
Rs.close ();
}Catch(SQLException se) {
Se.printstacktrace ();
}
rs =NULL;
}
}
}

if(PST! =NULL) {
Try{
Pst.close ();
PST =NULL;
}Catch(SQLException ex) {
Ex.printstacktrace ();
}finally{
if(PST! =NULL) {
Try{
Pst.close ();
}Catch(SQLException se) {
Se.printstacktrace ();
}

PST =NULL;
}
}
}

if(Conn! =NULL) {
Try{
Conn.close ();
conn =NULL;
}Catch(SQLException ex) {
Ex.printstacktrace ();
}finally{
if(Conn! =NULL) {
Try{
Conn.close ();
}Catch(SQLException se) {
Se.printstacktrace ();
}
conn =NULL;
}
}
}
}
</code>

It was said that the Close method must be placed infinally, I don't know what to think.

The database resource is not the same as the normal Object.　It should be said that the proposal should be closed immediately and completely, and also conducive to pooling to work. I think so, it should be said that your close () is implemented by pooling to?br>

-------------------------------------------------------------------------------------
In fact, it seems that this is a very common misconception. Many veteran, master, all want to rs=null of course.
For some implementations of the JVM, local block out of scope when the local variable refers to the object does not become a GC ' able.
However, there is no problem when the function exits. The downside of not needing rs=null rs=null is, one, trouble. Second, it is impossible to use final, and final to improve the quality of the program is very meaningful. On this resource problem, I was doing vb4 the other day (ashamed, all the old!) ), also encountered. Surprisingly consistent, I also wanted to do a common function to encapsulate common stored proc calls. It's been a long time since I checked out MSDN. The test is no problem. Fortunately, the program is the client, no server-side so frequently called. But in VB, there are problems, in Java can be easily solved.
Purely from the problem itself, I think it is not stipulated by the standard. (statement unavailable after connection is disabled does not mean that resources are necessarily recycled). Without the standard, the implementation of each driver may be different. Therefore, no matter what the results of the experiment, are not dependent.
The solution should be as the previous man said, he encapsulated resultset.
Thanks to the JDBC Designer, resultset is an interface. We can implement resultset ourselves, and close the statement in close. In order to avoid writing a lot of commission function trouble (ResultSet way much ah!) Different JDBC versions are not the same), you can use the dynamic proxy. (Concrete method I originally wrote a bit, but just submitted, the session timeout, re-landed, I write things are gone! Too lazy to write again).
So in your primitive function, you can use this: Statement stmt = ...;
BooleanOK = false;
Try{
ResultSet rs = ...;
OK = true;
returnMyresultwrapper.instance (RS,STMT);
}
finally{
if(!ok) stmt.close ();
}

However, cross the question itself. I think returning to resultset may not always be a good choice. Why not take a callback? This way, you don't have to ask the client code to remember to call Close (), and you're done with it. I also mentioned this approach in one of my previous posts:

Interface resultlistener{
 Public void Acceptresult (Resultrow rs);
}
void populate (Resultlistener l) {
Final Statement stmt = ...;
Try {
Final ResultSet rs = ...;
Try {
 while (...) {L.acceptresult (new Resultrow (RS));}
    }
finally {
      Rs.close ();
    }
  }
finally {
    Stmt.close ();
  }
}

Personally, this method is sufficient to require no update and no need to move the cursor randomly on result set. and resource management is more robust.

-------------------------------------------------------------------------------------
As a best practice, when executing the Connection class method close () and closing the statement, consider using delegate mode to sacrifice some of the performance in exchange for stability;--------------------------------- ----------------------------------------------------
I think connection such an object itself is a package, different vendors implement different, if you use a vendor connection pool, such as WebLogic, then Connection.close () did not release the Conn, but put it back into the pool. Because the conn removed from DataSource is actually a subclass of connection, it overrides the Close () method. You can view the documentation provided by the relevant vendor. I think this is why some people have to show off statement, and some people also found not to show off statement can also cause it.