How to create datasources with Spring and why favor JNDI?

Datasource is a name given to the connection set up to a database from a server. There are two ways to create datasources in Spring. Datasources are required to create JDBC templates. All non trivial applications need to connect to the databases. Datasources are also supplied to the hibernate session factories.

Method 1:Using Apache commons-dbcp package that has the org.apache.commons.dbcp.BasicDataSource class. The pom.xml file for maven should declare the dependency.

  <properties>
  <commons-dbcp.version>1.4</commons-dbcp.version>
 </properties>
 
    <dependencies>
  <dependency>
   <groupId>commons-dbcp</groupId>
   <artifactId>commons-dbcp</artifactId>
   <version>${commons-dbcp.version}</version>
  </dependency>
 </dependencies>

Next, is the Spring configuration file that uses the Apache datasource.

 <bean id="dataSource_sybase" class="org.apache.commons.dbcp.BasicDataSource">
  <property name="driverClassName" value="com.sybase.jdbc3.jdbc.SybDriver" />
  <property name="url" value="jdbc:sybase:Tds:my_server:20215/my_schema" />
  <property name="username" value="user" />
  <property name="password" value="password" />
 </bean>

Method 2:  Using the JNDI to connect via the application servers' data source configuration. For example, in JBoss, you configure the data source via say my-ds.xml file and copy that to the deploy folder.

<?xml version="1.0" encoding="UTF-8"?>
<datasources>
 <local-tx-datasource> 
        <jndi-name>jdbc.dataSource.my_jndi</jndi-name>
        <use-java-context>false</use-java-context>
  <connection-url>jdbc:sybase:Tds:my-server:20345/my_schema</connection-url>
  <driver-class>com.sybase.jdbc3.jdbc.SybDriver</driver-class>
  <user-name>user</user-name>
  <password>password</password>
  <max-pool-size>50</max-pool-size>
  <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.SybaseExceptionSorter</exception-sorter-class-name>
  <new-connection-sql>select count(1) from my_table</new-connection-sql>
        <check-valid-connection-sql>select count(1) from my_table</check-valid-connection-sql> 
 </local-tx-datasource> 
 
</datasources>

Now the Spring configuration to use the JNDI name

    <bean id="datasource_abc" class="org.springframework.jndi.JndiObjectFactoryBean"
  scope="singleton">
  <property name="jndiName">
   <value>jdbc.dataSource.my_jndi</value>
  </property>
 </bean>

   <bean id="jdbcTemplate_abc" class="org.springframework.jdbc.core.JdbcTemplate">
  <property name="dataSource" ref="datasource_abc" />
 </bean>

Q. Which approach would you favor and why?
A. JNDI based datasource creation because you have to move an application between environments like development to UAT and then to integration and finally to production. If you configure each app server to use the same JNDI name, you can have different databases in each environment and not required to change your code. You just pick up the same environment free WAR file and drop it in any environment. In other words, the environment details are externalized.

JDBC, Spring, and Hibernate tutorials

• Spring and Hibernate simple JDBC example
• JDBC Tutorial with HSQLDB
• Spring SimpleJdbcCall to invoke stored procedures
• Spring JDBC Template examples -- calling stored proc, simple select, and insert with returning the generated key