Different ways on how to acquire database connection in java

There are different ways we can acquire database connections in java. This is a quick post which shows how to acquire connections.

Connection using Spring-managed DriverManager DataSource

Add database properties

JDBC.PROPERTIES
jdbc.driverClassName= com.mysql.jdbc.Driver
jdbc.dialect=org.hibernate.dialect.MySQLDialect
jdbc.databaseurl=jdbc:mysql://localhost:3306/employeedb
jdbc.username=<REPLACE_USERNAME_HERE>
jdbc.password=<REPLACE_PASSWORD_HERE>

Declare Spring-Managed DriverManagerDataSource dataSource Bean

application-rootcontext.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.1.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.1.xsd">

  <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName"> <value>${jdbc.driverClassName}</value> </property>
		<property name="url"> <value>${jdbc.url}</value> </property>
		<property name="username"> <value>${jdbc.username}</value> </property>
		<property name="password"> <value>${jdbc.password}</value> </property>
  </bean>
	
  <bean id="employeeJdbcDaoSupport" class="labs.xsan.web.EmployeeDaoSupport">
	<property name="dataSource" ref="dataSource" />
  </bean>
	
<context:property-placeholder location="jdbc.properties"/>

</beans>

Connection using apache commons Connection pool

Add database properties

JDBC.PROPERTIES
jdbc.driverClassName= com.mysql.jdbc.Driver
jdbc.dialect=org.hibernate.dialect.MySQLDialect
jdbc.databaseurl=jdbc:mysql://localhost:3306/employee
jdbc.username=<REPLACE_USERNAME_HERE>
jdbc.password=<REPLACE_PASSWORD_HERE>

Declare DBCP managed dataSource Bean

application-rootcontext.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.1.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.1.xsd">
	
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
	<property name="driverClassName"> <value>${jdbc.driverClassName}</value> </property>
	<property name="url"> <value>${jdbc.url}</value> </property>
	<property name="username"> <value>${jdbc.username}</value> </property>
	<property name="password"> <value>${jdbc.password}</value> </property>
</bean>

 <bean id="employeeJdbcDaoSupport" class="labs.xsan.web.EmployeeDaoSupport">
	<property name="dataSource" ref="dataSource" />
</bean>

<context:property-placeholder location="jdbc.properties"/>
</beans>

Besides Apache Commons BasicDataSource, other popular open source database connection pool libraries include the C3P0 (www.mchange.com/projects/c3p0/index.html) and BoneCP (http://jolbox.com/).

Connection using C3p0 Connection pool

Add database properties

JDBC.PROPERTIES
jdbc.driverClassName= com.mysql.jdbc.Driver
jdbc.dialect=org.hibernate.dialect.MySQLDialect
jdbc.databaseurl=jdbc:mysql://localhost:3306/employee
jdbc.username=<REPLACE_USERNAME_HERE>
jdbc.password=<REPLACE_PASSWORD_HERE>

Declare C3p0 managed dataSource Bean

application-rootcontext.xml
<?xml version="1.0" encoding="UTF-8"?>
	<beans 	xmlns="http://www.springframework.org/schema/beans" 
	       	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	       	xmlns:p="http://www.springframework.org/schema/p" 
	       	xmlns:tx="http://www.springframework.org/schema/tx"
	       	xmlns:context="http://www.springframework.org/schema/context"
	       	xsi:schemaLocation="
				http://www.springframework.org/schema/beans 
				http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
				http://www.springframework.org/schema/tx 
				http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
				http://www.springframework.org/schema/context
				http://www.springframework.org/schema/context/spring-context-3.0.xsd
		   		">
	
	<context:property-placeholder location="/WEB-INF/spring.properties" />
    
    <!-- Enable annotation style of managing transactions -->
	<tx:annotation-driven transaction-manager="transactionManager" />	
   
	<!-- Declare the Hibernate SessionFactory for retrieving Hibernate sessions -->
	<!-- See http://static.springsource.org/spring/docs/3.0.x/javadoc-api/org/springframework/orm/hibernate3/annotation/AnnotationSessionFactoryBean.html --> 							
	<!-- See http://docs.jboss.org/hibernate/stable/core/api/index.html?org/hibernate/SessionFactory.html -->
	<!-- See http://docs.jboss.org/hibernate/stable/core/api/index.html?org/hibernate/Session.html -->
	<bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean"
			 p:dataSource-ref="dataSource"
			 p:configLocation="${hibernate.config}"
			 p:packagesToScan="com.xsan.persistence"/>

<!-- Declare a datasource that has pooling capabilities-->	 
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
			destroy-method="close"
			p:driverClass="${app.jdbc.driverClassName}"
			p:jdbcUrl="${app.jdbc.url}"
			p:user="${app.jdbc.username}"
			p:password="${app.jdbc.password}"
			p:acquireIncrement="5"
			p:idleConnectionTestPeriod="60"
			p:maxPoolSize="100"
			p:maxStatements="50"
			p:minPoolSize="10" />

	<!-- Declare a transaction manager-->
	<bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager" 
       			p:sessionFactory-ref="sessionFactory" />
  
</beans>

Connection using JNDI Data source via spring JndiObjectFactoryBean

If the application will run in JEE container, you can take advantage of the container-managed connection pooling.

Add a resource reference (resource-ref) in the application descriptor file.

<resource-ref>
<res-ref-name>jdbc/employeedb</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>

Use the JNDI name in the spring managed beans.

application-rootcontext.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.1.xsd">

	<bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
		<property name="jndiName"> <value>java:comp/env/jdbc/employeedb</value> </property>
	</bean>
	
	<bean id="employeeJdbcDaoSupport" class="labs.xsan.web.EmployeeDaoSupport">
		<property name="dataSource" ref="dataSource" />
	</bean>
</beans>

Connection using JNDI Data source via JEE namespace

Add a resource reference (resource-ref) in the application descriptor file.

<resource-ref>
<res-ref-name>jdbc/employeedb</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>

Use the JNDI name using jeee namespace.

application-rootcontext.xml
<?xml version="1.0" encoding="UTF-8"?>
	<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:jee="http://www.springframework.org/schema/jee"
	xsi:schemaLocation="http://www.springframework.org/schema/beans
	http://www.springframework.org/schema/beans/spring-beans-3.1.xsd
	http://www.springframework.org/schema/jee
	http://www.springframework.org/schema/jee/spring-jee-3.1.xsd">
		<jee:jndi-lookup jndi-name="java:comp/env/jdbc/employeedb"/>
</beans>

Version History


Date Description
2013-11-11    Initial Version