Sample Database Query using Database Connection Pooling

This example demonstrates the use of the Resin servlet engine's built-in database connection pooling. The Query Results shown above on the left were generated by the following SQL statement:

select id, lastname, firstname, email from sample order by id

By examining the source of this document, you will be able to see just how simple it is to use Resin's database connection pooling. Only three simple commands are needed to obtain a connection from the pool. The source to this document is located at:

/home/amnon/www/amnon/sample/querydb.jsp

To create a simple JSP document that uses a database connection from the global pool, all you need to do is include the following code into your JSP document. Then simply change the code within the try statement to perform whatever standard JDBC commands you would like.

<%@ page import='javax.sql.*, javax.naming.*, java.sql.*' %>
<%
Context env = (Context) new InitialContext().lookup("java:comp/env");
DataSource source = (DataSource) env.lookup("jdbc/amnon");
Connection conn = source.getConnection();

try {
  // Create a Statement.
  Statement stmt = conn.createStatement();
		
  // Set up the Result Set
  ResultSet rs = stmt.executeQuery ("select lastname from sample");

  // Print out the results
  while (rs.next()) {
    out.println(rs.getString(1)+"<br>");
  }
} catch (SQLException sqle) {
  out.println("ERROR: SQLException thrown.<br>");
  out.println(sqle.getMessage());
  sqle.printStackTrace();
} catch (Exception hre) {
  out.println("ERROR: Unable to process query.<br>");
} finally {
  conn.close();
}

%>

Database Connection Pooling

In order to get the most efficient use of your database connections, it is essential to use some form of database connection pooling. The process of creating a connection to a database is a very time consuming task, often taking up to 2 seconds. It is much more efficient to use database connection pooling than to create a new connection for each request.

When a database connection pool is used, a collection of database connections are initialized when an application starts. Instead of manually creating the database connections, the application simply asks for a connection from a pre-established pool of connections. Because the connection has already been created, it only needs to be assigned to the application. This is a very fast operation. The application can then use it to perform any necessary database commands. When it is done with the connection, it simply releases it back to the connection pool. The connection is never created or destroyed by the application. The application simply "borrows" a connection for a short time and then "gives it back" so that it is again available for future requests.

Not only does using a connection pool profoundly improve the performance for database-intensive applications, but it also helps to manage database connection use more effectively. There are many situations where an application is permitted to use only a certain number of simultaneous database connections. For instance, Servlets.Net permits you to use a certain number of simultaneous database connections with your account (the number varies depending upon the type of account and any add-on services that have been purchased). By using connection pooling, a limit to the number of maximum connections can be made easily.

Samples Home Page