Spring
DAO: by,
G.madhav rao
What is JDBCTEMPLATE?
·
The JdbcTemplate
is the basic element and a central class of the spring jdbc abstraction
framework which includes the most common logic(that is ,the parts of code that
remains fixed) in sing JDBC APO.
·
While using the
JdbcTemplate the application developer has to only provide code to prepare
the SQL statement and extract results.
The JdbcTemplate can be used to execute all types of SQL STATEMENTS AND STORED
PROCEDURE CALS.
·
Is is important
to know that the JdbcTemplate class instances are threadsafe once configured.
Mean you can configure a single instance of a JdbcTemplate and then safely
inject this shared reference into multiple DAOS.
·
The Jdbc Temp;ate
central class of spring JDBC abstraction framework is packaged into
org.springframework.jdbc.core package.it is a non-abstraction class and can be instantiated
using any of the following constructors:
1.
JdbdTemplate()
2.
JdbcTemplate(DataSource)
3.
Jdbc Template
methods for executing SQL DML statement.
Method
|
Description
|
Public int update(String sql-statement)
|
Execute the given
SQL statement using the JDBC statement object.
Returns the
update count describing the number of records affected by the statement.
|
Public int update(String sql-statemet,
object[] params)
|
Execute the given
SQL statement using the JDBC
PreparedStatement.
Hear we can palace the method parameter the
Inside the PreparedStatement by using placeholder(?).
Returns the
update count describing the number of records affected by the statement.
|
Public int update(String sql-statement,Prepared
StatementSetter pass) |
Execute the given
sql statement using the JDBC PreparedStatement same
of the above described methods.
This method used
the given PreparedStatementSetter object to set the PreparedStatement.
Returns the
update count describing the number of records affected by the statement.
|
Public int update(String sql-statement,Prepared
StatementCreator psc) |
Executes the
PreparedStatement that is obtained using the given PreparedStatementCreator.
This method used
the given
PreparedStatementSetter
object to set the PreparedStatement.
|
Example
program1
//THIS
PROGRAM WILL DESCRIBES-SENDING SQL STATEMENT TO MYSQL DATA BASE.
Dept.java
package
madhav.pojodao;
public class
Dept {
public int deptno;
public
String dname,loc;
public int
getDeptno() {
return deptno;
}
public void
setDeptno(int deptno) {
this.deptno =
deptno;
}
public
String getDname() {
return dname;
}
public void
setDname(String dname) {
this.dname =
dname;
}
public
String getLoc() {
return loc;
}
public void
setLoc(String loc) {
this.loc =
loc;
}
}
DeptDao.java
package
madhav.daoimpl;
import madhav.pojodao.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import
org.springframework.jdbc.core.*;
import java.util.*;
public class
DeptDao {
JdbcTemplate jt;
public
DeptDao(JdbcTemplate jt)
{
this.jt=jt;
}
public void
changeLocation(int dno,String loc)
{
jt.update("update
dept set loc=? where deptno=?",
new
Object[]{loc,new Integer(dno)});
}
public void
removeDept(int dno)
{
jt.update("delete
from dept where deptno="+dno);
}
}
Spring.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:p="http://www.springframework.org/schema/p"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">
<bean id="datasource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName">
<value>com.mysql.jdbc.Driver</value>
</property>
<property name="url">
<value>jdbc:mysql://localhost:3306/mysql</value>
</property>
<property name="username">
<value>madhav</value>
</property>
<property name="password">
<value>madhav</value>
</property>
</bean>
<bean id="jdbctemp" class="org.springframework.jdbc.core.JdbcTemplate">
<constructor-arg>
<ref local="datasource"/>
</constructor-arg>
</bean>
<bean id="deptdao" class="madhav.daoimpl.DeptDao">
<constructor-arg>
<ref local="jdbctemp"/>
</constructor-arg>
</bean>
</beans>
TestCase.java
import madhav.daoimpl.*;
import
madhav.daoimpl.DeptDao;
import org.springframework.beans.factory.*;
import org.springframework.beans.factory.xml.*;
import
org.springframework.context.ApplicationContext;
import
org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.core.*;
import org.apache.commons.dbcp.BasicDataSource;
public class
TestCase {
/**
* @param
args
*/
public static void
main(String[] args) throws Exception {
DeptDao deptDao;
ApplicationContext beanfactory;
beanfactory=new
ClassPathXmlApplicationContext("applicationcontext.xml");
deptDao =
(DeptDao)beanfactory.getBean("deptdao");
System.out.println("testing
change loc");
deptDao.changeLocation(40,
"hyderabad");
System.out.println("location
has been changed");
System.out.println("testing
romove dept");
deptDao.removeDept(10);
}
}
Output:
testing change loc
location has been
changed
testing romove dept
click hear to download the above example program
example program 2:
//THIS PROGREAM WILL DESCRIBES –WORKIGN
WITH “PreparedStatementCreator” object.
Dept.java
package
madhav.pojodao;
public class
Dept {
public int deptno;
public
String dname,loc;
public int
getDeptno() {
return deptno;
}
public void
setDeptno(int deptno) {
this.deptno =
deptno;
}
public
String getDname() {
return dname;
}
public void
setDname(String dname) {
this.dname =
dname;
}
public
String getLoc() {
return loc;
}
public void
setLoc(String loc) {
this.loc =
loc;
}
}
DeptDao.java
package
madhav.daoimpl;
import
madhav.pojodao.*;
import
java.sql.Connection;
import
java.sql.PreparedStatement;
import java.sql.ResultSet;
import
java.sql.SQLException;
import
org.springframework.jdbc.core.*;
import java.util.*;
public class
DeptDao {
JdbcTemplate jt;
public
DeptDao(JdbcTemplate jt)
{
this.jt=jt;
}
public void
enterRecord()
{
Dept d=new
Dept();
d.setDeptno(30);
d.setDname("hugo");
d.setLoc("kotta
guda");
MyPreparedStatementCreater ms=new
MyPreparedStatementCreater(d);
jt.update(ms);
System.out.println("record
in inserted");
}
private static final class
MyPreparedStatementCreater implements
PreparedStatementCreator {
Dept d;
public
MyPreparedStatementCreater(Dept d)
{
this.d=d;
}
public
PreparedStatement createPreparedStatement(Connection con)
throws
SQLException {
//
TODO Auto-generated method stub
PreparedStatement ps=con.prepareStatement("insert into dept
values(?,?,?)");
ps.setInt(1,d.deptno);
ps.setString(2,d.dname);
ps.setString(3,d.loc);
return ps;
}
}
}
Spring.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:p="http://www.springframework.org/schema/p"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">
<bean id="datasource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName">
<value>com.mysql.jdbc.Driver</value>
</property>
<property name="url">
<value>jdbc:mysql://localhost:3306/mysql</value>
</property>
<property name="username">
<value>madhav</value>
</property>
<property name="password">
<value>madhav</value>
</property>
</bean>
<bean id="jdbctemp" class="org.springframework.jdbc.core.JdbcTemplate">
<constructor-arg>
<ref local="datasource"/>
</constructor-arg>
</bean>
<bean id="deptdao" class="madhav.daoimpl.DeptDao">
<constructor-arg>
<ref local="jdbctemp"/>
</constructor-arg>
</bean>
</beans>
TestCase.java
import madhav.daoimpl.*;
import
madhav.daoimpl.DeptDao;
import org.springframework.beans.factory.*;
import org.springframework.beans.factory.xml.*;
import
org.springframework.context.ApplicationContext;
import
org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.core.*;
import org.apache.commons.dbcp.BasicDataSource;
public class
TestCase {
/**
* @param
args
*/
public static void
main(String[] args) throws Exception {
DeptDao deptDao;
ApplicationContext beanfactory;
beanfactory=new
ClassPathXmlApplicationContext("applicationcontext.xml");
deptDao =
(DeptDao)beanfactory.getBean("deptdao");
deptDao.enterRecord();
System.out.println("record
entered successfully");
}
}
Output:
entering record
record in inserted
record
entered successfully
click hear to download the above example program:
USING JDBCTEMPLATE TO
EXECUTE SQL SELECT QUERIES.
·
JdbcTemplate class
includes query() methods to prepare and execure the SQL queries.
·
The spring JDBC
abstraction framework provides three different types of callbacks to read the
results after executing the SQL query() methods.
·
The three callbacks
of sprig JDBC abstraction framework to retrieve the data are.
1. ResultSetExtractor
2. RowCallbackHandler
3. RowMapper
The
Interface ResultSetExtractor:
Methos:
Public Object extractData(Result rs)
Throws SQLException,DataAccssException
·
The implementation
classes of this interface needs to implement he extractData() method.
·
Which takes the
responsibility of extracting results from a ResultSet,
·
But it does not need
to worry abut handling the SQLException raised while extracting the results.
·
The SQLException will
be caught by and handled by the jdbcTemplate calling this implementation.
Code:
Public class DeptExt implements ResultSetExtracor
{
Public Object extractData(ResultSet rs) throws SQLException
{
List depts.=new ArrayList();
While(rs.next())
{
Dept d=new Dept();
d.deptno=rs.getInt(1);
d.dname=rs.getString(2);
d.loc=rs.getStrig(3);
depts.add(d);
}
Return depts;
}
2.The Interface RowCallBackHandler:
·
The
RowCallbackHandler is a callback interface used by JdbcTemplate,s query() methods.
·
This
interface contains only one method.
Method:
Public void processRow(ResultSet
rs)throws SQLException
Example program 3:
Dept.java
package
madhav.pojodao;
public class
Dept {
public int deptno;
public
String dname,loc;
public int
getDeptno() {
return deptno;
}
public void
setDeptno(int deptno) {
this.deptno =
deptno;
}
public
String getDname() {
return dname;
}
public void
setDname(String dname) {
this.dname =
dname;
}
public
String getLoc() {
return loc;
}
public void
setLoc(String loc) {
this.loc =
loc;
}
}
DeptDao.java
package
madhav.daoimpl;
import
madhav.pojodao.*;
import
java.sql.Connection;
import
java.sql.PreparedStatement;
import
java.sql.ResultSet;
import
java.sql.SQLException;
import
org.springframework.jdbc.core.*;
import
java.util.*;
public
class DeptDao {
JdbcTemplate jt;
public DeptDao(JdbcTemplate jt)
{
this.jt=jt;
}
public void selectRecords()
{
System.out.println(" i am trying to
select from rowcallbackhanler");
String sql="select * from
dept";
DeptResults dr=new
DeptResults();
jt.query(sql, dr);
Iterator it1=dr.list.iterator();
while(it1.hasNext())
{
Dept
d=(Dept)it1.next();
System.out.println("deptno = "+ d.deptno);
System.out.println("dept name= "+ d.dname);
System.out.println("dept loc " + d.loc);
}
}
private static final class
DeptResults implements RowCallbackHandler{
public List list;
public DeptResults()
{
list=new ArrayList();
}
public void
processRow(ResultSet rs) throws SQLException {
// TODO Auto-generated
method stub
Dept d=new Dept();
d.deptno=rs.getInt(1);
d.dname=rs.getString(2)
d.loc=rs.getString(3);
list.add(d);
}
}
}
Spring.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:p="http://www.springframework.org/schema/p"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">
<bean id="datasource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName">
<value>com.mysql.jdbc.Driver</value>
</property>
<property name="url">
<value>jdbc:mysql://localhost:3306/mysql</value>
</property>
<property name="username">
<value>madhav</value>
</property>
<property name="password">
<value>madhav</value>
</property>
</bean>
<bean id="jdbctemp" class="org.springframework.jdbc.core.JdbcTemplate">
<constructor-arg>
<ref local="datasource"/>
</constructor-arg>
</bean>
<bean id="deptdao" class="madhav.daoimpl.DeptDao">
<constructor-arg>
<ref local="jdbctemp"/>
</constructor-arg>
</bean>
</beans>
TestCase.java
import madhav.daoimpl.*;
import madhav.daoimpl.DeptDao;
import org.springframework.beans.factory.*;
import org.springframework.beans.factory.xml.*;
import
org.springframework.context.ApplicationContext;
import
org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.core.*;
import org.apache.commons.dbcp.BasicDataSource;
public class TestCase {
/**
*
@param args
*/
public
static void main(String[] args) throws Exception {
DeptDao deptDao;
ApplicationContext beanfactory;
beanfactory=new ClassPathXmlApplicationContext("applicationcontext.xml");
deptDao = (DeptDao)beanfactory.getBean("deptdao");
System.out.println("selecting data ");
deptDao.selectRecords();
}
}
Output:
selecting data :
i am trying to select from rowcallbackhanler
deptno = 20
dept name= manager
dept loc hyderabad
deptno = 30
dept name= hugo
dept loc
kotta gu
click hear to download the above example program :
The Interface RowMapper:
The
RowMapper is another callback interface used by JdbcTemplate query methods.
This interfaces contains oly one method.
Method:
Public
Object mapRow(Result rs,int nownum)thows SQLException
Example
program 4:
//THIS
PROGRAM WILL DESCTIBES –EXTRACTING DATA USING “RowMapper”
Dept.java
package
madhav.pojodao;
public class
Dept {
public int deptno;
public
String dname,loc;
public int
getDeptno() {
return deptno;
}
public void
setDeptno(int deptno) {
this.deptno =
deptno;
}
public
String getDname() {
return dname;
}
public void
setDname(String dname) {
this.dname =
dname;
}
public
String getLoc() {
return loc;
}
public void
setLoc(String loc) {
this.loc =
loc;
}
}
DeptDao.java
package
madhav.daoimpl;
import
madhav.pojodao.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import
java.sql.ResultSet;
import
java.sql.SQLException;
import
org.springframework.jdbc.core.*;
import
java.util.*;
public class
DeptDao {
JdbcTemplate jt;
public
DeptDao(JdbcTemplate jt)
{
this.jt=jt;
}
public void
selectRecords()
{
String sql="select
* from dept";
System.out.println("select
record form Rommapper");
DeptRecord dd=new
DeptRecord();
List<Dept> depts=jt.query(sql,
dd);
Iterator<Dept> it=depts.iterator();
while(it.hasNext())
{
Dept d=it.next();
System.out.println("deptno
= "+ d.deptno);
System.out.println("dept
name= "+ d.dname);
System.out.println("dept
loc " + d.loc);
}
}
private static final class
DeptRecord implements RowMapper<Dept>
{
public
Dept mapRow(ResultSet rs, int rownum) throws
SQLException {
//
TODO Auto-generated method stub
Dept d=new
Dept();
d.deptno=rs.getInt(1);
d.dname=rs.getString(2);
d.loc=rs.getString(3);
return d;
}
}
}
Spring.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:p="http://www.springframework.org/schema/p"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">
<bean id="datasource" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName">
<value>com.mysql.jdbc.Driver</value>
</property>
<property name="url">
<value>jdbc:mysql://localhost:3306/mysql</value>
</property>
<property name="username">
<value>madhav</value>
</property>
<property name="password">
<value>madhav</value>
</property>
</bean>
<bean id="jdbctemp" class="org.springframework.jdbc.core.JdbcTemplate">
<constructor-arg>
<ref local="datasource"/>
</constructor-arg>
</bean>
<bean id="deptdao" class="madhav.daoimpl.DeptDao">
<constructor-arg>
<ref local="jdbctemp"/>
</constructor-arg>
</bean>
</beans>
TestCase.java
import madhav.daoimpl.*;
import madhav.daoimpl.DeptDao;
import org.springframework.beans.factory.*;
import org.springframework.beans.factory.xml.*;
import org.springframework.context.ApplicationContext;
import
org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.core.*;
import org.apache.commons.dbcp.BasicDataSource;
public class TestCase {
/**
* @param args
*/
public
static void main(String[] args) throws Exception {
DeptDao deptDao;
ApplicationContext beanfactory;
beanfactory=new
ClassPathXmlApplicationContext("applicationcontext.xml");
deptDao
= (DeptDao)beanfactory.getBean("deptdao");
System.out.println("extract data");
deptDao.selectRecords();
}
}
Output:
extract data
select record form
Rommapper
deptno = 20
dept name= manager
dept loc hyderabad
deptno = 30
dept name= hugo
dept loc kotta guda
click hear to download the above example program
cxample
program 5:
//this
program will describes –calling stored procedure from data base.
