Google

May 23, 2012

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

Labels: ,

9 Comments:

Anonymous Jirka Pinkas said...

I'd call native query from EntityManager or Session and simply retrieve resulting Object[]. Nowadays you usually don't need to work with JDBC directly very much, so personally I don't see the need for JdbcTemplate (which is nice, but Hibernate is better).

Just my two cents. ;)

4:00 AM, May 25, 2012  
Blogger Arulkumaran Kumaraswamipillai said...

Agree Jirka.

This post is mainly aimed at beginners to Spring to understand how things are wired up together.

9:45 AM, May 25, 2012  
Anonymous Hazel said...

Thanks for posting this blog.I have a doubt.
In the DAO class, the AccountDaoImpl class uses JdbcTemplate [public AccountDaoImpl( JdbcTemplate jdbcTemplate)].

In the spring configuration, HibernateTemplate is injected in place of JdbcTemplate. I looked up the API and there is no relationship between the two template classes.

What am I missing here? Please explain.

Many Thanks

12:03 AM, November 13, 2012  
Anonymous Anonymous said...

This information is worth everyone's attention. How can I find out more?
Feel free to visit my web blog :: leon is awesome

8:27 AM, November 13, 2012  
Blogger Arulkumaran Kumaraswamipillai said...

Thanks for pointing it out Hazel. I have fixed the post to use the JdbcTemplate.

2:01 AM, November 14, 2012  
Anonymous Anonymous said...

Nice guide, but there are a lot of errors in this code.
Am I super beginner in Hibernate and Spring but not Java, so I was hoping this guide would help me get started, but there is no file [hibernate.properties]. So I have no idea what should be in there.

Also line 50 in myAppContext.xml is incorrect.

8:00 AM, November 29, 2012  
Blogger Veeresh G said...

Hi Arul,
Is this example(Spring and Hibernate simple JDBC example)also be there in the book.Could you please tell me if you have published this book recently ?

3:37 AM, March 14, 2013  
Blogger Arulkumaran Kumaraswamipillai said...

No, it won't be.

10:36 PM, March 16, 2013  
Blogger Arulkumaran Kumaraswamipillai said...

Books are more concise Questions & Answers approach to prepare for the job interviews.

5:38 PM, March 17, 2014  

Post a Comment

Subscribe to Post Comments [Atom]

<< Home