GWT RPC: Use same DB connection across multiple requests

Question

I'm working on a web front-end to a database. The purpose is to create a tool that students can use to learn SQL, issue queries, and see the results. Prior to now I've been using the CLI. Its primary disadvantages are a) students these days are more used to GUIs; and b) when the query returns a very wide table, it's hard to read because it wraps around. My web GUI is intended to address these deficiencies.

I'm using GWT as the client-side frontend and a PostgreSQL database backend. GWT communicates with the database through a Java servlet container hosted by either Jetty or Tomcat.

Using the GWT RPC mechanism to issue simple queries is easy enough. I'm stuck on how to handle long-lived transactions, though. In order for the students to better understand how transactions behave, I need them to be able to issue a BEGIN statement, then issue one or more queries, then COMMIT or ROLLBACK. I'd like them to be issue the BEGIN/COMMIT/ROLLBACK statements by hand, which means the transaction could be active for as much as several minutes.

(This is not intended to be a high-performance database server. It's a teaching tool, so I value the user experience over speed.)

In order to implement this, I need to be assured that through the entire transaction, the client will be attached to the same database connection. Using traditional (stateless) techniques, the database connections are either short-lived or pooled. Consequently, one can never be sure that the same database connection will be used across multiple queries.

I'm afraid I'm a bit new to Java servlets, so I have several questions.

First and foremost, is there an existing mechanism for opening a database connection and using it across an entire user session?

Second, I was thinking of creating a lightweight server process that the servlet communicates with. The server process will match session IDs with an active database connection and attach the client to the appropriate one. Thus, the lightweight server maintains the database connection itself and persists until the user signs off -- just like the CLI would. Does something like this already exist?

Answer 1

Java servlets keep an HttpSession object for each user session. Each session object has a string-keyed map you can use to attach arbitrary java objects (for example, an SQL connection) to the session. In your case I'd retrieve the connection object for each request like this:

// getThreadLocalRequest is a member of GWT's RemoteServiceServlet
HttpSession session = getThreadLocalRequest().getSession();
Connection connection = (Connection)session.getAttribute("connection");

if (connection == null) {
     // I'll leave it to you to implement createConnection
     final Connection c = createConnection();
     connection = c;

     session.setAttribute("connection", connection);
     session.setAttribute("expiryListener", new HttpSessionBindingListener() {
         public void valueBound(HttpSessionBindingEvent e) {}

         // This method will be called when the user's session expires
         public void valueUnbound(HttpSessionBindingEvent e) {
             c.close();
         }
     });
}

// connection is ready to use!
Statement statement = connection.createStatement();