CODEWITHSUNDEEP
javajspservletsjdbc
PhilDin
PhilDin

Reputation: 2842

Approach to JDBC connections in a Servlet/JSP application

I have some existing java code in the form of a number of DAO classes, for example 

class EmployeeDao {    

   public EmployeeDao(Connection conn) {
      // Prepare statements to be used by the methods below
   }

   public Employee getEmployeeById(long id) {
   }

   public Collection<Employee> getEmployeesByDepartment(long departmentId) {
   }
   ...
}

All DAO classes are constructed with a JDBC connection. The contructor prepares any of the statements required by the methods. This works fine in a single threaded environment (for example batch processing) where the caller can create a connection and instantiate the required Dao objects with that connection.

I'd like to be able to use this in a Java web application but I'm just not sure how to handle JDBC connections. Here are some ideas:

  1. Every request creates a new JDBC connection and instantiates the required Dao objects. Obviously expensive both from the connection creation and the Dao instantiation

  2. Every request fetches a connection from a JNDI datasource and instantiates the required Dao objects. This eliminates the overhead of the connection creation but retains the overhead of preparing all of the statements

  3. Every request fetches a connection from a JDNI datasource and instantiates the required Dao objects but the statements are no longer prepared by the constructor, they are prepared lazily as required.

  4. A HttpSessionListener instantiates the Dao objects and stores them in the session (using setAttribute). When the session expires, the connection is closed.

  5. Create a stateless session bean. The bean will instantiate the required Dao objects.

Option 1 is not a real contender, it's just there to show my thought process.

Options 2 and 3 would work but seem to be sub-optimal, having to prepare statements on every request seems like an overhead that should be easy to avoid but I think I'm missing how

Option 4 avoids having the overhead of preparing statements on each call but at the expense of manually maintaining state. I don't think this is the intended usage pattern in Servlet/JSP applications (keeping connections for an extended period).

Option 5 should work but seems like a lot of overhead and means that I need a full EJB container rather than a simple servlet engine.

What approaches have worked for people bearing in mind the objectives of:

  1. Avoid preparing statements on every request if possible
  2. Preferably use a simple servlet engine

PS: I know that frameworks such as Hibernate and Entity Beans can abstract some of this away but I'd like to undestand the "no framework" base case.

Upvotes: 3

Views: 708

Answers (1)

Jorge_B
Jorge_B

Reputation: 9872

I would recommend you use a database connection pool like the ones you can find in every major application server (or any decent implementation like dbcp, c3p0, etc.)

Spring can do a good job in injecting a reference to your datasource in (more or less) cached dao instances, so you can scratch a little more performance (in time as well as in memory consumption).

However, I would say that you prepare your statements each time and don't try to cache them, as they are still linked to the Connection they were created from, and those Connections are managed by the datasource.

And finally, about your concern on the time preparing the statement: that can be negligeable if you write carefully your queries. The time is not consumed in the act of preparing the statement; what is really heavy is the query plan, which is done in the database, behind the scenes. This is the reason your 'first' execution of a query can seem more time consuming. However, if you write parametrized queries into your statement, the database will cache your query execution plan and reuse it in every query execution after it. For example:

PreparedStatement stmt =  conn.prepareStatement("select name from employees where number = ?");

Is cacheable, and the query plan will be reused every time you prepare this statement (the database 'remembers' that it prepared it for another statement)

However, this:

PreparedStatement stmt =  conn.prepareStatement("select name from employees where number = " + employeeNumber);

Means that your database will have to prepare a different execution plan for every time you prepare the statement (it does not 'remember' that query) - the performance problem would be in the way I have built the query instead of the java Statement object. Not to mention that this way is insecure and prone to SQL injection as well.

I hope you find this useful

Upvotes: 6

Related Questions