Roy's musings

There is a new JDBC connection pool implementation that comes with Tomcat, called JDBC-pool. You can read more about this pool implementation here . I can already see that quite a few projects (including the Spring tc server) have started using or supporting this connection pool. I am currently hooked to DBCP, yet to try this pool. There is also an article that compares DBCP and JDBC-pool .

Keep the following things in your mind if you are planning to measure the time taken to execute a query when you make use of Apache DBCP + Spring JdbcTemplate. You will never be able to find out the time taken to execute a query in the database by measuring the the time it takes to complete any variant of JdbcTemplate.query...() or JdbcTemplate.execute...() . Making use of any of these functions involve the following steps: Time taken to borrow a connection from the pool Time taken to create a statement from the connection and bind the parameters if needed Time taken to send the query to the database Time taken to execute the query in the database ( this is the time you are specifically interested in ) Time taken to receive the result from the database (depending on the fetch size you have set, this will vary). For UPDATEs, only the update count is sent back from the database. Time taken in the row mapping (if you are making use of RowMapper) or time taken to consume all the rows from...

Let us say you are making use of DBCP in your application. You are running a load test on your application and you find that the response time is terrible. You suspect that it is the database that is messing up all the performance. Well, as an application developer that would be my first response! But how do I prove that it is the database that is causing the issues, and not my application. The first thing you should be checking is the number of load testing client threads versus the number of connections in your DBCP. If you have more number of client threads than the number of connections in your DBCP, there is a likelihood of your application being the root cause for the bad response. The easiest way to confirm this one is by using the maxWait parameter of DBCP. You just set the maxWait to a value that you expect the average waiting time to be. For e.g. in my case I never expect the wait time to be more than 30 milliseconds. Once you set this watch for errors in your application lo...

I had to make use of DB connection pool in one of the applications that I was implementing. Since I am new to Java, everyone in the team was suggesting why don't you make use of Commons DBCP. Being a fan of reuse, I decided to make use of DBCP and started reading about the parameters that are exposed so that I can customize DBCP to fit my needs. While reading through the available configuration parameters , I was thinking, "I don't need to check the connection to be bad on every borrow or return. It is sufficient if I check for being bad only when the connection is sitting idle in the pool." So I set the testWhileIdle to be true and set the validationQuery to be "SELECT 1 FROM DUAL". I was thinking that the configuration that I had was the optimal for the situation in hand. But one thing I overlooked was, the default value for the testOnBorrow is true. So the moment you set the validationQuery to be any non-null value, every connection you borrow from the po...