aspose file tools*
The moose likes JDBC and the fly likes Connection Method Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Connection Method" Watch "Connection Method" New topic
Author

Connection Method

Davie Lin
Ranch Hand

Joined: Aug 05, 2007
Posts: 294
Hi everyone, I need some suggestions from you experienced programmers. I want to know if it's ok to make a static get_Connection() method where I put all the access code to open a connection and have another separate static method where I can close the same connection, statements or resultset. Is this doable? or do I have to do all of it in main()? I wanted to implement it this way because this class is my Model layer that Controller calls the method from it. I thought the model layer don't contain main(). If this can not be done? would it mean that my db access code (i.e. DriverManager.getConnection()) would have to be in the Controller layer? or am I wrong about Model layer can't contain main()?

Thanks for all your opinions.
[ March 24, 2008: Message edited by: Davie Lin ]
Roger Chung-Wee
Ranch Hand

Joined: Sep 29, 2002
Posts: 1683
It's fine to have static methods for what you want. What matters more is where these methods are invoked from. My rule is to get JDBC objects such as connections as late as possible and close them as soon as possible. I most certainly never do this in any controller I write.


SCJP 1.4, SCWCD 1.3, SCBCD 1.3
Davie Lin
Ranch Hand

Joined: Aug 05, 2007
Posts: 294
Hi Roger, thanks for you reply. So based upon my original post and your initial reply. I was not sure about how to write the method that close connections. For instance the controller calls
and within taht code I have but within fetch_Conn() method, the connection is not closed and I need a to close the connection. How would close the connection opened in

Is that possible to do?

Thanks for your help
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

Are we talking about static method or static object? Static method for getConnection() is fine, and from time to time its a good way to do JDBC in the sense that in centralizes your JDBC calls. Static objects (storing the connection in as static Connection object) to use between calls is generally a very bad idea. Not only can it lead to threading/transaction issues, but often memory leaks and performance problems in JDBC applications are a result of connection objects that were never closed/destroyed.


My Blog: Down Home Country Coding with Scott Selikoff
Roger Chung-Wee
Ranch Hand

Joined: Sep 29, 2002
Posts: 1683
You need this (exception handling ignored for simplicity):



And this:



So, in your method, you would have this:



And this in the finally clause:

Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

You don't need the static closeConnection nor would it be a good idea to have. You can just do conn.close() with the object you all ready have in the first line. In general, getConnection() should always allocate a new connection, since the process as you point out is going to close it in a finally block.
Davie Lin
Ranch Hand

Joined: Aug 05, 2007
Posts: 294
Originally posted by Scott Selikoff:
You don't need the static closeConnection nor would it be a good idea to have. You can just do conn.close() with the object you all ready have in the first line. In general, getConnection() should always allocate a new connection, since the process as you point out is going to close it in a finally block.


Thanks for your suggestion, perhaps I am still need more practice in Java. I do have a little concern about what you said, how would conn.close work if the Controller (i.e. Servlet) calls the fetch_conn() method and conn.getConnection() is in fetch_conn()? My concern is the scope of this conn.close() because it conn wasn't declare in Controller layer, it wouldn't compile right? Or is there an exception for conn?

Thanks for your thoughts
Davie Lin
Ranch Hand

Joined: Aug 05, 2007
Posts: 294
Thanks Roger, I like your idea, I should of thought of conn_close() with a Connection connection as an argument to pass into the method. This make sense to me, I will try this approach Thanks again.
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

I was commenting more on Roger suggestion, if guaranteed to close the connection after using it (via a finally block), it makes no sense to keep the connection alive in some static object, since other threads could come in and request the connection before/after/during you closed it. In the event you're guaranteed to close the connection, then its better that getConnection() just creates a new connection.

The key here is, once you have a connection, don't throw it away. You can store a connection locally as part of a single process, but storing in as a static object across the entire application is not a great idea especially if there's any chance this is a multi-threaded environment. This is what connection pools are for, to keep a group of available connections. When you request a connection object from a pool, you never get one someone else is using.
[ March 25, 2008: Message edited by: Scott Selikoff ]
Davie Lin
Ranch Hand

Joined: Aug 05, 2007
Posts: 294
Appreciate your comment Scott, even thought the code I write for now is quite primitive and probably not a concern with what you stated. It would be very helpful to keep what you suggest in mind for future JDBC development.

Thanks for Scott and Roger for all your thoughts on this subject matter.
Roger Chung-Wee
Ranch Hand

Joined: Sep 29, 2002
Posts: 1683
I was commenting more on Roger suggestion, if guaranteed to close the connection after using it (via a finally block), it makes no sense to keep the connection alive in some static object, since other threads could come in and request the connection before/after/during you closed it. In the event you're guaranteed to close the connection, then its better that getConnection() just creates a new connection.


Indeed, that is exactly my thinking. The getConnection() method returns a connection, it does not store it.

You can store a connection locally as part of a single process, but storing in as a static object across the entire application is not a great idea especially if there's any chance this is a multi-threaded environment. This is what connection pools are for, to keep a group of available connections. When you request a connection object from a pool, you never get one someone else is using.

This is why I suggested referencing the connection using a method-local variable, and closing the connection in a finally clause will guarantee that the connection is returned to the pool. My observation is that many problems are avoided if all DDBC objects are referenced by local variables and are closed at the appropriate time.
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

I think we both agree on the same concepts, I'm just not sure why you need a static close method. The close is defined on the connection itself.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Connection Method