Spring JDBC DAO Module
In this module first of all we will deal with the importance of DAO module in spring applications.
While writing every application it is essential to obtain DB connection object, create Statement, PreparedStatement etc objects and with this we will persist data into DB. Sometimes we may use ORM frameworks to manage persistence.
Hence initialization of data access frameworks (creating con, stmt, pstmt, cfg, sf, s etc), managing resources and handling various types of exceptions is an essential part of persistence operations. While handling these if any exception raises the potentially information of the project may be corrupted or lost. Hence in Spring data access frameworks are provided to integrate with various persistence technologies like JDBC, Hibernate, JPA, TopLink, OJB, JDO, iBatis.
DAO stands for DataAccessObject. DAO role is to read and write data to and from persistence devices such as DB/File/XML/Spreadsheet/DirService.
Understanding Spring’s DataAccessException
Spring’s DAO frameworks do not throw technology specific exceptions, such as SQLException or HibernateException. Instead, all exceptions thrown in spring DAO package methods are subclasses of the org.springframework.dao.DataAccessException. The main advantage of these exceptions is we need not have to handle these exceptions anywhere in our DAO implementation classes when we are using DAO API because DAO API methods throws these exceptions, these exceptions are sub class of DataAccessException (spring’s own Exception) which is a sub class of RuntimeException.
Our application’s HibDAO Implementation class |
Some of the important DataAccessException sub classes are:
· CleanupFailureDataAccessException
· DataAccessResourceFailureException
· DataIntegrityViolationException
· DataRetrievalFailureException
· DeadLockLoserDataAccessException
· IncorrectUpdateSemanticsDataAccessException
· InvalidDataAccessApiUsageException
· InvalidDataAccessresourceUsageException
· OptimisticLockingFailureException
· TypeMismatchDataAccessException
· UncategorizedDataAccessException
Working with DataSources
Connection pool object DataSource can be obtained in two ways:
· One is getting from DirectoryService of any application server
· Second one is configuring Connection pool in Spring framework itself
Getting DataSource from JNDI
<bean id=”ds” class=”org.springframework.jndi.JndiObjectFactoryBean”>
<property name=”jndiEnvironment”>
<props>
<prop key=”java.naming.initial.factory”>weblogic.jndi.WLInitialContextFactory</prop>
<prop key=”java.naming.provider.url”>t3://localhost:7001</prop>
</props>
</property>
<property name=”jndiName” value=”OP”/>
</bean>
<bean id=”cpmBean” class=”CPMBean” init-method=”init”>
<constructor-arg ref=”ds”/>
</bean>
Creating DataSource connection pool in Spring container
<bean id=”ds” class=”org.springframework.jdbc.datasource.DriverManagerDataSource”>
(or)
<bean id=”ds” class=”org.apache.commons.dbcp.BasicDataSource”>
<property name=”driverClassName” value=”oracle.jdbc.driver.OracleDriver”/>
<property name=”url” value=”jdbc:oracle:thin:@localhost:1521:XE”/>
<property name=”username” value=”username”/>
<property name=”password” value=”password”/>
</bean>
<bean id=”cpmBean” class=”CPMBean” init-method=”init”>
<constructor-arg ref=”ds”/>
</bean>
Accessing DataSource in our DAO components
public class CPMBean {
DataSource ds;
Connection con;
public CPMBean(DataSource ds){
this.ds=ds;
}
public void init(){
con=ds.getConnection();
}
public void useConnection(){
System.out.println(con);
}
}
public class CPMClient {
public static void main(String rags[]){
BeanFactory f=new FileSystemXmlApplicationContext(“applicationContext.xml”);
CPMBean cpmb=( CPMBean)f.,getBean(“cpmBean”);
Cpmb.useConnection();
}
}
Using JdbcTemplate and JdbcDaoSupport classes
The purpose of JdbcTemplate is that it hides JDBC Connection, Statement, PreparedStatement, CallableStatement and ResultSet objects in a single class and provides all required methods in a single class.
To create JdbcTemplate object DataSource object is required as argument in its constructor.
JdbcTemplate jt=new JdbcTemplate(dataSource);
In spring XML file we need to write like this:
<bean id=”jt” class=”org.springframework.jdbc.core.JdbcTemplate”>
<constructor-arg ref=”ds” />
</bean>
<bean id=”ds” class=”CourseDAO”>
<constructor-arg ref=”jt” />
</bean>
In our DAO we need to access like this:
public class CourseDAO{
JdbcTemplate jt;
public CourseDAO(JdbcTemplate jt) {
this.jt=jt;
}
}
Instead of writing a separate constructor or setter method in our class to receive JdbcTemplate object one more class is given in Spring JDBC API called JdbcDaoSupport. If we extend our class from JdbcDaoSupport two methods are derived into our class they are setJdbcTemplate(JdbcTemplate jt) and getJdbcTemplate(). Hence if we extend from JdbcDaoSupport we can configure our class as below:
<bean id=”ds” class=”CourseDAO”>
<property name=”jdbcTemplate”ref=”jt”/>
</bean>
In our DAO class we can directly use JdbcTemplate object like this:
public class CourseDAO extends JdbcDaoSupport {
public int save(Course c) {
int cid=getJdbcTemplate().queryForInt(“SELECT max(cid) FROM course”);
getJdbcTemplate().execute(“INSERT INTO course VALUES (.,.,.,.,.,.)”);
return cid;
}
}
But while writing DAO if we write DAO only using JDBC API, we are also having Hibernate API, iBatis API, TopLink API, JDO API and other ORM APIs to interact with DB. Clients / Development teams may choose different APIs to interact with DB. Hence switching over from one API to another API is difficult in business logic functions. Write one DAO implementation class using each API separately and use any one DAO implementation class in business logic component using constructor injection or setter injection. Any way if you don’t understand this paragraph. Experience the following example to understand it. Best of luck to develop one example on DAOs.
DAO classes are written per table basis like Entity Beans. Each DAO class contains CRUD operations specific to one-one table. |
Example on JdbcDAO and HibernateDAO
// Emp.java
package beans;
import java.io.Serializable;
public class Emp implements Serializable {
private int eid;
private String ename;
private double sal;
private String desig;
// public no param constructor
public Emp(){}
// public params constructor
public Emp(int eid, String en, double sal, String desig)
{
this.eid=eid;
this.ename=en;
this.sal=sal;
this.desig=desig;
}
public int getEid() {
return eid;
}
public void setEid(int eid) {
this.eid = eid;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public double getSal() {
return sal;
}
public void setSal(double sal) {
this.sal = sal;
}
public String getDesig() {
return desig;
}
public void setDesig(String desig) {
this.desig = desig;
}
public String toString()
{
return eid+" "+ename+" "+sal+" "+desig;
}
}
// EmpDAO.java
package beans;
import java.util.List;
public interface EmpDAO {
public int save(Emp e);
public boolean update(Emp e);
public boolean delete(int eid);
public Emp find(int eid);
public List findAll();
}
// EmpJDBCDAO.java
package beans;
import java.sql.*;
import java.util.*;
import org.springframework.dao.*;
import org.springframework.jdbc.core.*;
import org.springframework.jdbc.core.support.*;
import org.springframework.jdbc.support.rowset.*;
public class EmpJDBCDAO extends JdbcDaoSupport implements EmpDAO {
public int save(Emp e) {
// PK generation
int eid=getJdbcTemplate().queryForInt("SELECT emp_seq.nextval FROM dual");
// record insertion
getJdbcTemplate().execute("INSERT INTO emp VALUES ("+eid+",'"+e.getEname()+"',"+e.getSal()+",'"+e.getDesig()+"')");
return eid;
}
public boolean update(final Emp e) {
getJdbcTemplate().execute(
new PreparedStatementCreator(){
public PreparedStatement createPreparedStatement(Connection con) throws SQLException
{
return con.prepareStatement("UPDATE emp SET name=?, sal=?, desig=? WHERE eid=?");
}
},
new PreparedStatementCallback(){
public Object doInPreparedStatement(PreparedStatement pstmt) throws SQLException
{
pstmt.setString(1, e.getEname());
pstmt.setDouble(2, e.getSal());
pstmt.setString(3, e.getDesig());
pstmt.setInt(4, e.getEid());
pstmt.executeUpdate();
return null;
}
});
return true;
}
public boolean delete(int eid) {
getJdbcTemplate().execute("DELETE FROM emp WHERE eid="+eid);
return true;
}
public Emp find(int eid) {
return (Emp)getJdbcTemplate().query("SELECT * FROM emp WHERE eid="+eid, new ResultSetExtractor(){
public Object extractData(ResultSet rs) throws SQLException, DataAccessException
{
Emp e=null;
if(rs.next())
{
e=new Emp(rs.getInt(1), rs.getString(2), rs.getDouble(3), rs.getString(4));
}
return e;
}
});
}
public List findAll() {
SqlRowSet srs=getJdbcTemplate().queryForRowSet("SELECT eid, ename, sal, desig FROM emp");
List list=new ArrayList();
while(srs.next()) {
System.out.println(srs.getString(1));
System.out.println(srs.getString(2));
System.out.println(srs.getString(3));
System.out.println(srs.getString(4));
/* double d=srs.getDouble(1);
Double dd=new Double(d);
int eid=dd.intValue();
String ename=srs.getString(2);
double sal=srs.getDouble(3);
String desig=srs.getString(4);
Emp e=new Emp(eid, ename, sal, desig);
list.add(e); */
}
return list;
/* return getJdbcTemplate().query("SELECT * FROM emp",
new RowMapper()
{
public Object mapRow(ResultSet rs, int rowNum) throws SQLException
{
List list=new ArrayList();
do{
Emp e=new Emp(rs.getInt(1), rs.getString(2), rs.getDouble(3), rs.getString(4));
list.add(e);
}while(rs.next());
return list;
}
}); */
}
}
// EmpHibDAO.java
package beans;
import java.util.List;
import org.springframework.orm.hibernate3.support.*;
public class EmpHibDAO extends HibernateDaoSupport implements EmpDAO {
public int save(Emp e) {
// return ((Integer)getHibernateTemplate().save(e)).intValue();
getHibernateTemplate().save(e);
return e.getEid();
}
public boolean update(Emp e) {
getHibernateTemplate().update(e);
return true;
}
public boolean delete(int eid) {
Emp e=(Emp)getHibernateTemplate().load(Emp.class, new Integer(eid));
getHibernateTemplate().delete(e);
return true;
}
public Emp find(int eid) {
return (Emp)getHibernateTemplate().get(Emp.class, new Integer(eid));
}
public List findAll() {
return getHibernateTemplate().find("FROM Emp");
}
}
// EmpService.java
package beans;
import java.util.*;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
@Transactional
public class EmpService {
EmpDAO edao;
public EmpService(EmpDAO edao) {
this.edao=edao;
}
@Transactional(propagation=Propagation.REQUIRED)
public int saveEmp(Emp e) {
return edao.save(e);
}
@Transactional(propagation=Propagation.REQUIRED)
public boolean updateEmp(Emp e) {
return edao.update(e);
}
@Transactional(propagation=Propagation.REQUIRED)
public boolean deleteEmp(int eid) {
return edao.delete(eid);
}
@Transactional(propagation=Propagation.NOT_SUPPORTED)
public Emp findEmp(int eid) {
return edao.find(eid);
}
@Transactional(propagation=Propagation.NOT_SUPPORTED)
public List findAllEmps() {
return edao.findAll();
}
}
Emp.hbm.xml
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!--
Demonstrates use of a class-level where restriction
-->
<hibernate-mapping package="beans">
<class name="Emp" lazy="false">
<id name="eid">
<generator class="sequence">
<param name="sequence">emp_seq</param>
</generator>
</id>
<property name="ename"/>
<property name="sal"/>
<property name="desig"/>
</class>
</hibernate-mapping>
applicationContext.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:aop="http://www.springframework.org/schema/aop"
xmlns:tx="http://www.springframework.org/schema/tx"
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">
<bean id="ds" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
<property name="url" value="jdbc:oracle:thin:@localhost:1521:XE"/>
<property name="username" value="username"/>
<property name="password" value="password"/>
</bean>
<!-- <bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="ds"></property>
</bean> -->
<!-- <tx:annotation-driven transaction-manager="txManager"/> -->
<bean id="jt" class="org.springframework.jdbc.core.JdbcTemplate">
<constructor-arg ref="ds"/>
</bean>
<bean id="ejdao" class="beans.EmpJDBCDAO">
<property name="jdbcTemplate" ref="jt"/>
</bean>
<bean id="lsfb" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
<property name="dataSource" ref="ds"/>
<property name="hibernateProperties">
<props>
<prop key="dialect">org.hibernate.dialect.OracleDialect</prop>
<prop key="show_sql">true</prop>
<prop key="hbm2ddl.auto">update</prop>
</props>
</property>
<property name="mappingResources">
<list>
<value>Emp.hbm.xml</value>
</list>
</property>
</bean>
<bean id="ht" class="org.springframework.orm.hibernate3.HibernateTemplate">
<constructor-arg ref="lsfb"/>
</bean>
<bean id="ehdao" class="beans.EmpHibDAO">
<property name="hibernateTemplate" ref="ht"/>
</bean>
<bean id="es" class="beans.EmpService">
<constructor-arg ref="ehdao"/>
</bean>
</beans>
// Client.java
import java.util.*;
import org.springframework.beans.factory.*;
import org.springframework.context.support.*;
import beans.*;
public class Client {
public static void main(String rags[]) throws Exception {
// Create Spring container object
BeanFactory f=new FileSystemXmlApplicationContext("src\\applicationContext.xml");
EmpService es=(EmpService)f.getBean("es");
/*
Emp e1=new Emp(0, "ABC", 50000.00, "Manager");
Emp e2=new Emp(0, "XYZ", 45000.00, "Asst Manager");
Emp e3=new Emp(0, "LMN", 35000.00, "Sr Soft Enggr");
Emp e4=new Emp(0, "PQR", 65000.00, "Appl Architect");
int eid=es.saveEmp(e1);
System.out.println(eid);
eid=es.saveEmp(e2);
System.out.println(eid);
eid=es.saveEmp(e3);
System.out.println(eid);
eid=es.saveEmp(e4);
System.out.println(eid);
*/
Emp e=es.findEmp(5);
System.out.println(e);
List<Emp> list=es.findAllEmps();
for(Emp e1:list)
{
System.out.println(e1);
}
/* List list=es.findAllEmps();
System.out.println(list.getClass().getName());
Iterator i=list.iterator();
while(i.hasNext())
{
// System.out.println(i.next().getClass().getName());
ArrayList list1=(ArrayList)i.next();
Iterator i1=list1.iterator();
while(i1.hasNext())
{
System.out.println(i1.next().getClass().getName());
}
}*/
}
}
Understanding spring transaction management support
Spring transaction manager implementation for various persistence implementations:
· org.springframework.jdbc.datasource.DataSourceTransactionManager
· org.springframework.orm.hibernate.HibernateTransactionManager
· org.springframework.orm.jdo.JdoTransactionManager
· org.springframework.transaction.jta.JtaTransactionManager
· org.springframework.orm.ojb.PersistenceBrokerTransactionManager
How to create various TransactionManager
<bean id=“transactionManager” class=”org.springframework.jdbc.datasource.DataSourceTransactionManager”>
<property name=”dataSource” ref=”ds”/>
</bean>
<bean id=“transactionManager” class=”org.springframework.orm.hibernate.HibernateTransactionManager”>
<property name=”sessionFactory” ref=”lsfb”/>
</bean>
<bean id=“transactionManager” class=”org.springframework.orm.ojb.PersistenceBrokerTransactionManager”>
<property name=”transactionManagerName” value=”java:/TransactionManager”/>
</bean>
<bean id=“transactionManager” class=”org.springframework.transaction.jta.JtaTransactionManager”>
<property name=”transactionManagerName” value=”java:/TransactionManager”/>
</bean>
<bean id=”transactionTemplate” class=”org.springframework.transaction.support.TransactionTemplate”>
<constructor-arg ref=”transactionManager”/>
</bean>
Programmatic transactions in Spring DAO
public int saveEmp(finalEmp e) {
int eid=0;
transactionTemplate.execute(new TransactionCallback() {
public Object doInTransaction(TransactionStatus ts) {
try {
eid=edao.save(e);
}
catch(Exception e) {
ts.setRollbackOnly();
}
return null;
}
});
return eid;
}
<bean id=”transactionTemplate” class=”org.springframework.transaction.support.TransactionTemplate”>
<constructor-arg ref=”transactionManager”/>
</bean>
<bean id=”empService” class=”EmpService”>
<property name=”transactionTemplate” ref=”transactionTemplate”/>
</bean>
Declarative/Container managed transactions
<bean id=”empService” class=”org.springframework.transaction.interceptor.TransactionProxyFactoryBean”>
<property name=”proxyInterfaces”>
<list>
<value>EmpService</value>
</list>
</property>
<property name=”target” ref=”es”/>
<property name=”transactionManager” ref=”transactionManager”/>
<property name=”transactionAttributeSource” ref=”attributeSource”/>
</bean>
<bean id=” attributeSource” class=”org.springframework.transaction.interceptor.MatchAlwaysTransactionAttributeSource”>
<property name=”transactionAttribute” ref=”transactionAttribute”/>
</bean>
<bean id=” transactionAttribute” class=”org.springframework.transaction.interceptor.DefaultTransactionAttribute”>
<property name=”propagationBehaviorName”>
<value>PROPAGATION_REQUIRED</value>
</property>
<property name=”isolationLevelName”>
<value>ISOLATION_SERIALIZABLE</value>
</property>
</bean>
NameMatchTransactionAttributeSource is used to specify different transaction attribute for different methods:
<bean id=” attributeSource” class=”org.springframework.transaction.interceptor.NameMatchTransactionAttributeSource”>
<property name=”properties” >
<props>
<prop key=”saveEmp”>PROPAGATION_REQUIRES_NEW, ISOLATION_SERRIALIZABLE, -EmpInsertionException</prop>
<prop key=”updateEmp”>PROPAGATION_REQUIRED</prop>
<prop key=”findEmp”>PROPAGATION_NOTSUPPORTED</prop>
</props>
</property>
</bean>
Other classes and interfaces of JDBC DAO Module are
JdbcTemplate, BatchPreparedStatementSetter, CallableStatementCallback, CallableStatementCreator, ConnectionCallback, PreparedStatementCallback, PreparedStatementCreator, ResultSetExtractor, RowCallbackHandler, RowMapper, StatementCallback, SimpleJdbcTemplate, NamedParameterJdbcTemplate, SimpleJdbcCall, JdbcDaoSupport, SingleConnectionDataSource, DelegatingDataSource, LazyConnectionDataSourceProxy, TransactionAwareDataSourceProxy, UserCredentialsDataSourceAdapter, JndiDataSourceLookup, SqlRowSet, SqlProvider, SqlUpdate, MappingSQLQuery
