java - How to properly keep a DB connection from a Connection Pool opened in JBoss

Question

I'm using JBoss AS 7.1 as a server and I have my DataSource configured with pooling. I'm quite new to this so please excuse any rookie mistakes... after all I'm here to learn.

When a client logs-in it gets a connection to the database and I need to keep that connection(from the pool) open until the user logs-out or the HttpSession expires. This is an absolute requirement coming from our DB Admin. who says that he needs the DB session variables. I am using a servlet for all this.

Playing with the possibilities I have encountered 2 major problems:

1. As far as I see JBoss automatically closes unused connections => my opened connection returns to the pool. So this might not be the right path.

2. If I try to store/recall the Connection object like this:

   private Hashtable<String, Connection> connections = new Hashtable<String, Connection>();
   
   try {
       String strDSName1 = "java:/OracleDSJNDI";
       ctx = new InitialContext();
       ds1 = (javax.sql.DataSource) ctx.lookup(strDSName1);
   
       System.out.println("Got 1'st ds.");
   
   } catch (Exception e) {
       System.out.println("ERROR getting 1'st DS : " + e);
   }
   
   connection = ds1.getConnection();
   connections.put(session.getId(), connection);
   
   conn = (Connection) connections.get(sessionID);
   

   it throws this exception:

   java.sql.SQLException: Connection is not associated with a managed connection.org.jboss.jca.adapters.jdbc.jdk6.WrappedConnectionJDK6@dee1f37

My question is: How do I properly keep my connection opened?

Thanks

Answer 1

How do I properly keep my connection opened?

You must not do that, let the connection pool handle this.

---

Behind the scenes, the connection pool will keep a bunch of database connections to the database engine (MySQL, Oracle, SQL Server... depends how you configure it) in SLEEPING state. When you execute this code:

//avoiding all the particular exceptions just for code simplicity purposes...
//in real world applications, you must handle each of these exceptions
public Connection getConnection() throws Exception {
    ctx = new InitialContext();
    ds1 = (javax.sql.DataSource) ctx.lookup(strDSName1);
    return ds1.getConnection();
}

You're asking to the connection pool to retrieve one of these connections available. The connection pool will give you a database connection (if available) and let you use it as long as you want. Then you use it wherever you want/need and close it:

public void foo() throws Exception {
    Connection connection = getConnection();
    //do what you want/need...

    //in the end, you close the connection
    //this is A MUST!
    connection.close();
}

When executing connection.close() from a connection retrieved by the connection pool, you're not closing the physical database connection but notifying the connection pool this specific database connection must return to the SLEEPING state.

---

Some advices from the explanation:

• You must not try to keep the connection alive, that's connection pool's job.
• You must not try to store the connections in any cache-like structure, that's connection pool's job.
• You must retrieve a java.sql.Connection in the shortest scope you will need it. Once you have used it, close it.