Before a Spring MVC application implements Java database transactions, a DataSource must be injected first into the ApplicationContext. This object creates a java.sql.Connection object per user who wants to access a database instance, given the username and password of the server, JDBC URL of the database schema, and the driver class name of the JDBC connector for some database transactions.
The immediate way to implement DataSource is to instantiate the DriverManagerDataSource of the Spring JDBC framework. The only problem with this class is the absence of connection pooling capabilities which slows down the performance of the applications once there is more than one user involved in accessing the database schema. It may even cause an OutofMemoryError which causes the Tomcat server to crash:
This degradation is due to the fact that DriverManagerDataSource will create another Connection object when another user is added. For the enterprise application, it is recommended to use third-party JDBC connection pooling that provides a cached logical connection pool rather than instantiating additional Connection objects for the additional user connectivity:
Based on our unit testing using the Metrics API, HikariCP provides us the best case performance, given 1,000 users. After executing the test cases, we will roughly arrive at the following statistics:
|
Connection Pooling Implementation |
Mean call rate (call/second) |
Max time elapsed per call (millisec) |
Min time elapsed per call (millisec) |
Mean time elapsed per call (millisec) |
|
DriverManagerDataSource (Spring) |
140.19 |
839.60 |
5.36 |
7.10 |
|
BasicDataSource (DBCP2) |
194.82 |
739.04 |
4.01 |
5.06 |
|
ComboPooledDataSource (C3P0) |
208.28 |
547.79 |
3.92 |
4.78 |
|
Tomcat's DataSource |
111.89 |
1847.79 |
3.92 |
8.92 |
|
HikariDataSource (HikariCP) |
215.08 |
437.33 |
3.89 |
4.63 |
To wrap up this recipe, it is always a protocol not to hardcode the database details during DataSource injection. Use the jdbc.properties file to store all the credentials in order to avoid messing up once changes happens. Just declare PropertySourcesPlaceholderConfigurer into the container and apply @PropertySource to the context's @Configuration definition to make reference to the jdbc.properties. Aside from establishing reference, the @PropertySource will classify all details as Spring environment variables, thus org.springframework.core.env.Environment can now be used to fetch the key values of jdbc.properties. Before deploying to the production server, it is recommended to configure JNDI or transaction listeners to register JDBC properties.