JOBS4TIMES

DAO (Data Access Object)

1. Introduction to Spring Framework DAO Module
   
   

Introduction :

• As part of DAO module of spring, we can write regular JDBC code but the developer will not find any advantage of using Spring.
• When we write the JDBC code developer has to write all traditional code as well as developer has to take care of all the exceptions.
• The advantage of using DAO module is we no need to provide huge amount of code as well as we no need to handle error.
• When we use DAO module we can use the pre-defined classes given by the spring because of this we can deliver the project quickly.
• Spring uses "Template Design Pattern"
• As part of org.springframework.jdbc.core package contains JDBCTemplate class,
  As part of this class the common code which is used in all the projects available.

The following is example of JDBC Template :

package com.dao;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.DataSource;

public class JDBCTemplate {

private DataSource dataSource;

public void setDataSource(DataSource dataSource){
	this.dataSource=dataSource;
}
public DataSource getDataSource(){
	return dataSource;
}

public int update(String query) throws SQLException{
Connection con=dataSource.getConnection();
Statement stmt=con.createStatement();
int result=stmt.executeUpdate(query);
stmt.close();
con.close();
return result;
}//update

}

• In spring we can use 3 categories of dataSource objects, they are default the spring guys are develops so many dummy connection pool programs.
  They are available in a package org.springframework.jdbc.dataSource.
• By default spring is integrated with DBCP connection pool and C3P connection pool
• We can use Weblogic connection pool.

Procedure to use DAO module in Spring :

• To the Project add spring capabilities, to add DAO module we need to check the checkbox spring 3.0 persistence JDBC library.
• We try to use JDBCTemplate class by creating the object manually.

package com.dao;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

public class MyApp {
public static void main(String[] args) {
DriverManagerDataSource ds=new DriverManagerDataSource();
ds.setDriverClassName("oracle.jdbc.driver.OracleDriver");
ds.setUrl("jdbc:oracle:thin:@localhost:1521:xe");
ds.setUsername("scott");
ds.setPassword("tiger");

JdbcTemplate jt=new JdbcTemplate();
jt.setDataSource(ds);
jt.update("insert into product values(1,'pone',2015)");
}

}

• In the above example developer is creating the object and establish the dependencies instead of developer doing this work spring container can create the object establish dependencies.

Procedure to use JdbcTemplate in a project :

• JDBCTemplate is dependent on dataSource,
  configure org.springframework.jdbc.datasource.DriverManagerDataSource into spring bean configuration file supply driver class, url, userName, password as dependencies.

applicationContext.xml

<bean id="ds" 
 class="org.springframework.jdbc.datasource.DriverManagerDataSource">
 
 <property name="driverClassName">
   <value>oracle.jdbc.driver.OracleDriver</value>
 </property>
 <property name="url" value="jdbc:oracle:thin:@localhost:1521:xe"/>
 <property name="username" value="scott"/>
 <property name="password" value="tiger"/>
</bean>

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

package com.dao;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;

public class MyApp {
public static void main(String[] args) {
ApplicationContext container=new ClassPathXmlApplicationContext(
                          "com/core/applicationContext.xml");
JdbcTemplate jt=container.getBean("jt",JdbcTemplate.class);
int no=jt.update("insert into product values(23,'pone',3450)");
System.out.println(no);
}

}

Procedure to use DBCP connection pool :

1. create DataBase driver from myeclipse Database explorer.
2. create java project and add spring capabilities.
3. configure DataSource (spring explorer --> beans --> new datasource --> choose db server), this will populate all the required field.
4. configure JdbcTemplate
5. Get the JdbcTemplate object and call the update()

applicationContext.xml

 <bean id="ds" class="org.apache.commons.dbcp.BasicDataSource"> 
 <property name="driverClassName">
   <value>oracle.jdbc.driver.OracleDriver</value>
 </property>
 <property name="url" value="jdbc:oracle:thin:@localhost:1521:xe"/>
 <property name="username" value="scott"/>
 <property name="password" value="tiger"/>
</bean>

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

package com.dao;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;

public class MyApp {
public static void main(String[] args) {
ApplicationContext container=new ClassPathXmlApplicationContext(
                               "com/dao/applicationContext.xml");
JdbcTemplate jt=container.getBean("jt",JdbcTemplate.class);
int no=jt.update("insert into product values(2,'pone', 3450)");
System.out.println("success");
}

}

Using Weblogic connection pool in spring :

1. configure connection pool in weblogic server.
   for JNDI Connection Pool
2. create the project and add spring capabilities.
3. configure JNDI object factory bean

    <bean id="jndiPool" 
        class="org.springframework.jndi.JndiObjectFactoryBean">
    <property name="jndiName" value="myPool"/>
    <property name="jndiEnvironment">
     <props>
      <prop key="java.naming.factory.initial">
       weblogic.jndi.WLInitialContextFactory
      </prop>
      <prop key="java.naming.provider.url">
       t3://localhost:7001/
      </prop>
     </props>
    </property>
   </bean>
   
   <bean id="ds" class=
     "org.springframework.jdbc.datasource.UserCredentialsDataSourceAdapter">
    <property name="targetDataSource" ref="jndiPool"/>
    <property name="username" value="weblogic"/>
    <property name="password" value="weblogic"/>
   </bean>
   
   
   <bean id="jt" class="org.springframework.jdbc.core.JdbcTemplate">
    <property name="dataSource" ref="ds"/>
   </bean>
    

4. Get the spring container object call a method update() to perform any curd operations.

    package com.dao;
   
   import org.springframework.context.ApplicationContext;
   import org.springframework.context.support.*;
   import org.springframework.jdbc.core.JdbcTemplate;
   
   
   public class MyApp {
   public static void main(String[] args) {
   ApplicationContext container=
     new ClassPathXmlApplicationContext("com/dao/applicationContext.xml");
   JdbcTemplate jt=container.getBean("jt",JdbcTemplate.class);
   int no=jt.update("insert into product values(24,'pone',3450)");
   }
   
   }
   

   set the class path to weblogic.jar

Requirement : Write a program JDBC application in DAO module to insert, update, delete the record.

<bean id="ds" class="org.apache.commons.dbcp.BasicDataSource"> 
 <property name="driverClassName">
   <value>oracle.jdbc.driver.OracleDriver</value>
 </property>
 <property name="url" value="jdbc:oracle:thin:@localhost:1521:xe"/>
 <property name="username" value="scott"/>
 <property name="password" value="tiger"/>
</bean>

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

package com.dao;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;

public class MyApp {
public static void main(String[] args) {
ApplicationContext container=new ClassPathXmlApplicationContext(
		                      "com/dao/applicationContext.xml");
JdbcTemplate jt=container.getBean("jt",JdbcTemplate.class);
String query="insert into product values(?,?,?)";

Object obj[]={4,"four",4000};
int no=jt.update(query,obj); //OR

int no1=jt.update(query,"5","five","5000");

System.out.println("success");
}

}

Callback Mechanism :

String internally uses callback mechanism our java application call the methods of spring.
The internal code of spring call the methods of java application, this technique is called as callback mechanism

syntax :

Object extractData(ResultSet rs);

We need to develop a class which provides the implementation of ResultSetExtractor

Develop a class which provides the implementation of ResultSetExtractor.

package com.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;

public class GetData implements ResultSetExtractor {

public Object extractData(ResultSet rs) throws SQLException,
                                       DataAccessException {
 while(rs.next()){
  System.out.println(rs.getString(1));
  System.out.println(rs.getString(2));
  System.out.println(rs.getString(3));
 }//while	
 return null;
}//extractData

}

package com.dao;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;

public class MyApp {
public static void main(String[] args) {
ApplicationContext container=new ClassPathXmlApplicationContext(
		                      "com/dao/applicationContext.xml");
JdbcTemplate jt=container.getBean("jt",JdbcTemplate.class);
String query="select * from product";
int no=jt.query(query, new GetData()); 

System.out.println("success");
}

}

1. When ever we call the query() it sends the query to DataBase and DataBase Server return a ResultSet object.
2. Now the query() will call the extractData() by supplying ResultSet object.

• In the above example extractData(), we are displaying the records in the project, we will never display records in DAO component, we will always display output in view component.
• To achieve this we represent every record in the form of object in extractData() method.

Example

package com.dao;

public class Product {
String pid;
String pname;
String price;
public String getPid() {
	return pid;
}
public void setPid(String pid) {
	this.pid = pid;
}
public String getPname() {
	return pname;
}
public void setPname(String pname) {
	this.pname = pname;
}
public String getPrice() {
	return price;
}
public void setPrice(String price) {
	this.price = price;
}

}

package com.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;

public class GetData implements ResultSetExtractor {

public Object extractData(ResultSet rs) throws SQLException,
                                        DataAccessException {
 ArrayList list=new ArrayList();
 while(rs.next()) {
  Product p=new Product();
  p.setPid(rs.getString(1));
  p.setPname(rs.getString(2));
  p.setPrice(rs.getString(3));
  
  list.add(p);
}//while
	
 return list;
}//extractData

}

package com.dao;

import java.util.ArrayList;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.*;
import org.springframework.jdbc.core.JdbcTemplate;

public class MyApp {
public static void main(String[] args) {
ApplicationContext container=new ClassPathXmlApplicationContext(
		                      "com/dao/applicationContext.xml");
JdbcTemplate jt=container.getBean("jt",JdbcTemplate.class);
String query="select * from product";
ArrayList list=(ArrayList)jt.query(query, new GetData()); 

java.util.Iterator i=list.iterator();

while(i.hasNext()){
Product p= (Product) i.next();
System.out.println(p.getPid());
System.out.println(p.getPname());
System.out.println(p.getPrice());
System.out.println("------");
}//while

System.out.println("success");
}//main

}

Spring guys are provided the following Template classes as part of DAO module.

1. JdbcTemplate
2. NamedParameterJdbcTemplate
3. SimpleJdbcTemplate
4. SimpleJdbcInsert
5. SimpleJdbcCall

NamedParameterJdbcTemplate :

• The purpose of NamedParameterJdbcTemplate is instead of using treditional '?' place holders we use variable names.
• The advantage of using NamedParameterJdbcTemplate is it improves readability of queries.

  Ex:
  
  insert into product values(:pid,:pname,:price);
  

• NamedParameterJdbcTemplate is dependent on DataSource object.
• NamedParameterJdbcTemplate is uses constructor injection.

Procedure to work with NamedParameterJdbcTemplate :

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"> 
 <property name="driverClassName">
   <value>oracle.jdbc.driver.OracleDriver</value>
 </property>
 <property name="url" value="jdbc:oracle:thin:@localhost:1521:xe"/>
 <property name="username" value="scott"/>
 <property name="password" value="tiger"/>
</bean>

<bean id="npjt" 
class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
 <constructor-arg type="javax.sql.DataSource" index="0">
   <ref bean="dataSource"/>
 </constructor-arg>
</bean>

package com.dao;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

public class MyApp {
public static void main(String[] args) {
ApplicationContext container=new ClassPathXmlApplicationContext(
		                      "com/dao/applicationContext.xml");
NamedParameterJdbcTemplate npjt=
  container.getBean("npjt",NamedParameterJdbcTemplate.class);

String query="insert into product values(:pid,:pname,:price)";
MapSqlParameterSource map=new MapSqlParameterSource();
map.addValue("pid", "10");
map.addValue("pname", "scoda");
map.addValue("price", "500000");

npjt.update(query, map);

System.out.println("success");
}//main

}

SimpleJdbcTemplate :

• In the projects the code which is responsible to interact with DataBase must be provided in DAO class.
• The DAO class contains set of methods which are responsible to interact with table to perform all curd operations.

DAO class is dependent on JdbcTemplate the following code :

package com.dao;

public class Product {
String pid;
String pname;
String price;
public String getPid() {
	return pid;
}
public void setPid(String pid) {
	this.pid = pid;
}
public String getPname() {
	return pname;
}
public void setPname(String pname) {
	this.pname = pname;
}
public String getPrice() {
	return price;
}
public void setPrice(String price) {
	this.price = price;
}

}

package com.dao;

import java.util.ArrayList;
import org.springframework.jdbc.core.JdbcTemplate;

public class ProductDAO {
 JdbcTemplate jdbcTemplate;

public JdbcTemplate getJdbcTemplate() {
	return jdbcTemplate;
}

public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
	this.jdbcTemplate = jdbcTemplate;
}
 

public int storeProductDetails(Object... values){
 String query="insert into product values(?,?,?)";
 return jdbcTemplate.update(query,values);
}

public int updateProductDetails(Object... values){
 String query="update product set pid=? where pname=?";
 return jdbcTemplate.update(query,values);
}

public int deleteProductDetails(Object... pid){
 String query="delete from product where pid=?";
 return jdbcTemplate.update(query,pid);
}

public ArrayList getAllProductRecords(){
 String query="select * from product";
 return (ArrayList)jdbcTemplate.query(query,new ProductRowMapper());
}

}

package com.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;

public class ProductRowMapper implements RowMapper {

public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
		
 Product p=new Product();
 p.setPid(rs.getString(1));
 p.setPname(rs.getString(2));
 p.setPrice(rs.getString(3));
 return p;
}

}

<bean id="ds" class="org.apache.commons.dbcp.BasicDataSource"> 
 <property name="driverClassName">
   <value>oracle.jdbc.driver.OracleDriver</value>
 </property>
 <property name="url" value="jdbc:oracle:thin:@localhost:1521:xe"/>
 <property name="username" value="scott"/>
 <property name="password" value="tiger"/>
</bean>

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

<bean id="productDao" class="com.dao.ProductDAO">
 <property name="jdbcTemplate">
  <ref bean="jt"/>
 </property>
</bean>

package com.dao;

import java.util.ArrayList;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class MyApp {
public static void main(String[] args) {
ApplicationContext container=new ClassPathXmlApplicationContext(
		                      "com/dao/applicationContext.xml");

ProductDAO productDAO=container.getBean("productDao",ProductDAO.class);

productDAO.storeProductDetails(6,"six",60000);
productDAO.updateProductDetails(66,"six");
productDAO.deleteProductDetails(66);

ArrayList list=productDAO.getAllProductRecords();
java.util.Iterator i=list.iterator();

while(i.hasNext()){
Product p= (Product) i.next();
System.out.println(p.getPid());
System.out.println(p.getPname());
System.out.println(p.getPrice());
System.out.println("------");
}//while

System.out.println("success");
}//main

}

When we use RowMapper (or) ResultSetExtractor as seperate classes the no. of programs in project are becoming more because of this we get maintanance related problems.

• Who ever call doWork() method they must supply implementation class object of MyInterface.
• Instead of developing a seperate implementation class we can use an anonymous inner class as shown below.

package com.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

public class MyApp {
public static void main(String[] args) {
ApplicationContext container=new ClassPathXmlApplicationContext(
		                      "com/dao/applicationContext.xml");

JdbcTemplate jt=container.getBean("jt",JdbcTemplate.class);

String query="select * from Product";
ArrayList list=(ArrayList)jt.query(query, new RowMapper(){

public Object mapRow(ResultSet rs,int rowNum)throws SQLException{
 Product p=new Product();
 p.setPid(rs.getString(1));
 p.setPname(rs.getString(2));
 p.setPrice(rs.getString(3));
 return p;
}//mapRow
	
}//RowMapper
 );

java.util.Iterator i=list.iterator();

while(i.hasNext()){
Product p= (Product) i.next();
System.out.println(p.getPid());
System.out.println(p.getPname());
System.out.println(p.getPrice());
System.out.println("------");
}//while

System.out.println("success");
}//main

}

The following example demonstrate use batchUpdate in Spring :

public void batchUpdates(final ArrayList products){
 String query="insert into product values(?,?,?)";
jdbcTemplate.batchUpdate(query,new BatchPreparedStatementSetter() {
	
 public void setValues(PreparedStatement ps,int i)throws SQLException{
  Product p=(Product) products.get(i);
  ps.setString(1, p.getPid());
  ps.setString(2, p.getPname());
  ps.setString(3, p.getPrice());
 }
	
 public int getBatchSize() {
  return products.size();
 }
 
}); //batchUpdate
	

}//batchUpdates