Spring and Hibernate simple JDBC example

Q. How would you go about using Spring and Hibernate frameworks to make JDBC calls?
A.

STEP 1: Firstly, define the environment specific data in a properties file named datastore.properties with datasource details

jdbc.driver=com.sybase.jdbc3.jdbc.SybDriver
jdbc.url=jdbc:sybase:Tds:serverName:2000/my_database
jdbc.username=user
jdbc.password=password

STEP 2: Define the spring context file (myAppContext.xml) to wire up the data source and data access objects

<?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:aop="http://www.springframework.org/schema/aop"
 xmlns:tx="http://www.springframework.org/schema/tx" xmlns:context="http://www.springframework.org/schema/context" xmlns:util="http://www.springframework.org/schema/util"
 xmlns:batch="http://www.springframework.org/schema/batch"
 xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd
   http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.0.xsd
   http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.0.xsd
   http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-2.0.xsd
   http://www.springframework.org/schema/batch http://www.springframework.org/schema/batch/spring-batch-2.0.xsd
   http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd">

 

 <!-- STEP1: read configuration data  from .properties file           -->
 <context:property-placeholder location="classpath:datastore.properties" />
 
 <!-- STEP2: Data Source                     -->
 <bean id="dataSourceMyDs" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
  <property name="driverClassName" value="${jdbc.driver}" />
  <property name="url" value="${jdbc.url}" />
  <property name="username" value="${jdbc.username}" />
  <property name="password" value="${jdbc.password}" />
 </bean>
 
 <!-- STEP3: Hibernate Properties like dialect, showSql, etc  -->
 <bean id="hibernateProperties" class="org.springframework.beans.factory.config.PropertiesFactoryBean">
  <property name="properties">
   <props>
    <prop key="hibernate.dialect">org.hibernate.dialect.SybaseDialect</prop>
    <prop key="hibernate.generate_statistics">false</prop>
    <prop key="hibernate.hbm2ddl.auto">verify</prop>
    <prop key="hibernate.jdbc.batch_size">50</prop>
    <prop key="hibernate.show_sql">false</prop>
    <prop key="hibernate.format_sql">false</prop>
    <prop key="hibernate.cache.use_query_cache">false</prop>
    <prop key="hibernate.cache.use_second_level_cache">false</prop>
    <prop key="hibernate.query.factory_class">org.hibernate.hql.classic.ClassicQueryTranslatorFactory</prop>
   </props>
  </property>
  <property name="location">
   <value>classpath:/hibernate.properties</value>
  </property>
 </bean>
 
 <!-- Hibernate annotated classes for ORM -->
 <bean id="hibernateAnnotatedClasses" class="org.springframework.beans.factory.config.ListFactoryBean">
        <property name="sourceList">
            <list>
                <value>com.myapp.domain.model.Account</value>
                <
            </list>
        </property>
    </bean>
 
 <!-- STEP 4: define the hibernate session factory that makes use of the dataSourceMyDs & hibernateProperties defined in step 2 & 3 respectively-->
 <bean id="hibernateSessionFactoryMpApp"
  class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
  <property name="dataSource" ref="dataSourceMyDs" />
  <property name="hibernateProperties">
   <ref bean="hibernateProperties" />
  </property>
  <property name="annotatedClasses">
   <list>
   </list>
  </property>
  <property name="annotatedPackages">
   <list></list>
  </property>
 </bean>
 
 <!-- STEP 5:  DAO Template that uses the hibernateSessionFactoryMpApp for direct JDBC and ORM calls-->
 <bean id="daoTemplate" class="org.springframework.orm.hibernate3.HibernateTemplate">
  <constructor-arg index="0" ref="hibernateSessionFactoryMpApp" />
  <constructor-arg index="1" value="true" />
 </bean>
 
 <!-- STEP 6:  DAO Template gets injected into a DAO class -->
    
    <bean id="accountDao" class="com.myapp.dao.impl.AccountDaoImpl">
        <property name="dataSource" ref="dataSourceMyDs" />
    </bean>
    
    <!--  If you want to use HibernateTemplate -->
    <bean id="accountHibDao" class="com.myapp.dao.impl.AccountHibDaoImpl">
        <constructor-arg ref="daoTemplate" />
    </bean>
 
</beans>

The above config shows HibernateTemplate configuration as well, but the ensuing steps focuses on using the data source and the JdbcTemplate.

STEP 3: Define the interface for AccountDaoImpl.

package com.myapp.dao.impl;

public interface AccountDao {

    Account getAccount(String accountCode);

}

STEP 4: Define the implementations. The value object (i.e. POJO - Plain Old Java Object) class to map the relational data.

public class Account {
    
 private String accountCode;
 private String accountName;

 //getters & setters
    ....
}

The row mapper class that maps relational data to the value object (i.e a POJO)

import java.sql.ResultSet;
import java.sql.SQLException;

public class AccountRowMapper implements RowMapper {

   public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
  Account account = new Account();
  account.setAccountCode(rs.getString("account_code"));
  account.setAccountName(rs.getString(account_name"));
  return account;
 }

}

STEP 5: The data access object implementation that makes use of the POJO and the row mapper classes.

package com.myapp.dao.impl;

public class AccountDaoImpl implements AccountDao {

    private JdbcTemplate jdbcTemplate;
    
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    } 
 
   
 
 public Account getAccount(String accountCode) {
 
     String sql =  "Select account_code, account_name from account where account_code = ?"
  Account account = jdbcTemplate.queryForObject(sql, new Object[]{accountCode}, new AccountRowMapper());
  return account;
 }

}

STEP 6: Finally, you can invoke the data access object from your service or processor classes. Here is a simple class
for demo.

public class MyService {

   public static void main(String[] args) throws ClassNotFoundException {
      ApplicationContext applicationContext = new ClassPathXmlApplicationContext("myAppContext.xml");
      AccountDao dao = applicationContext.getBean("accountDao", AccountDao.class);
      Account account = dao.getAccount("1234");
   //do something with the account
   }
}

The JdbcTemplate class comes with many useful overloaded query methods and options to map data.



Q. If you are running your application on an application server, how would you define your datasource properties?
A. The data source will be configured via the application server and the spring config file myAppContext.xml will look up via JNDI as shown below

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

Q. How will you go about defining the dependency jars?
A. You need the following framework libraries (Spring and Hibernate) in addition to Java, and the
dependency can be configured via maven pom.xml file.

 <!-- Spring framework -->
    <dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring</artifactId>
    <version>3.0.5.RELEASE</version>
    </dependency>
 
    <!-- Spring AOP dependency -->
    <dependency>
        <groupId>cglib</groupId>
  <artifactId>cglib</artifactId>
  <version>2.2</version>
 </dependency>
 
    <!-- Sybase database driver -->
 <dependency>
  <groupId>com.sybase</groupId>
  <artifactId>jconn3</artifactId>
  <version>6.0</version>
 </dependency>
 
 <!-- Hibernate framework -->
 <dependency>
  <groupId>hibernate</groupId>
  <artifactId>hibernate3</artifactId>
  <version>3.2.3.GA</version>
 </dependency>
 
 
 <!-- Hibernate library dependecy start -->
 <dependency>
  <groupId>dom4j</groupId>
  <artifactId>dom4j</artifactId>
  <version>1.6.1</version>
 </dependency>
 
 <dependency>
  <groupId>commons-logging</groupId>
  <artifactId>commons-logging</artifactId>
  <version>1.1.1</version>
 </dependency>
 
 <dependency>
  <groupId>commons-collections</groupId>
  <artifactId>commons-collections</artifactId>
  <version>3.2.1</version>
 </dependency>
 
 <dependency>
  <groupId>antlr</groupId>
  <artifactId>antlr</artifactId>
  <version>2.7.7</version>
 </dependency> 

JDBC, Spring, and Hibernate tutorials

• 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
• How to create datasources with Spring and why favor JNDI?