aspose file tools*
The moose likes JDBC and the fly likes Connection  scope Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Connection  scope" Watch "Connection  scope" New topic
Author

Connection scope

nikki lorenzo
Greenhorn

Joined: Jan 12, 2004
Posts: 28
Hi! Would there be any difference if I put the Connection reference in the class scope but new connection is retrieved for every method versus having it inside the method scope? Assuming, we are using Connection Pooling? Thanks.

1) Connection in class scope
public class MyDAO{
private Connection conn = null;

public void openDAO() {
if (this.conn == null) {
this.conn = WebConnectionFactory.getConnection();
}
}

public void myMethod(){
openDAO();
conn.prepareStatment("Select * from table");
//get resultSet
closeDAO();
}

public void mySecondMethod(){
openDAO();
conn.prepareStatment("Select * from table");
//get resultSet
closeDAO();
}
public void closeDAO(){
conn.close();
}
}

2) Connection in method scope
public class MyOtherDAO{
public myOtherMethod(){
Connection conn = WebConnectionFactory.getConnection();
conn.prepareStatment("Select * from table");
//get resultSet
conn.close()
}

public mySecondMethod(){
Connection conn = WebConnectionFactory.getConnection();
conn.prepareStatment("Select * from table");
//get resultSet
conn.close()
}
}
Nitesh Kant
Bartender

Joined: Feb 25, 2007
Posts: 1638

What is the advantage you get out of this exercise?


apigee, a better way to API!
Stan James
(instanceof Sidekick)
Ranch Hand

Joined: Jan 29, 2003
Posts: 8791
If conn were used in a number of methods, having a member variable might feel cleaner than passing a parameter all over the place. But it is not thread safe, if that matters. Oh, and closeDAO() needs to set conn back to null, I think.


A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2476
    
    7

You're supposed to return the connection to the pool as soon as possible (and reasonable). You should certainly not keep a reference to the connection you retrieved from the pool.
Even though you call close(), the connection is not closed, but returned to the pool. But you still have your attribute conn pointing to it.

  • You can get into trouble when the pool decides to shrink, or to regenerate it's connections.
  • When the pool hands over your returned connection to another piece of code, and you do a commit (or a rollback!) in your class with the dangling reference, you're interfering with the operation of that other code.

  • Best practice: Ask a connection, use it, return it.

    Regard, Jan


    OCUP UML fundamental and ITIL foundation
    youtube channel
    Henrique Ordine
    Ranch Hand

    Joined: Sep 03, 2004
    Posts: 127
    Additionally, your code will execute the test (this.conn == null) everytime a method of your DAO is called.
    I would definitely go for the method local variables.



    J2EE Architect/Developer
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: Connection scope
     
    Similar Threads
    need some help
    DB2 Pool Data
    JDBC VRE 2.0 BatchUpdate
    How must i "synchronize" my servlets?
    ejbLoad is not getting called