spring with DAO Example programs

Spring DAO:                                                                   by,

                                                                                       G.madhav rao

TUTIORALS FROM MADHAV:

     JAVA-SERVLETS     JAVA-JDBC     JAVA-JSP       HIBERNATE-SHCEMABASED 

    HIBERNATE-ANNOTATIONS     SPRING-IOC       SPRING –AOP-SCHEMABASED   

    SPRING-AOP-ANNOTATIONS      SPRING -DAO     SPRIN-MVC     SPRING-SECUTITY  

                                                                                                  

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 

TUTIORALS FROM MADHAV:

     JAVA-SERVLETS     JAVA-JDBC     JAVA-JSP       HIBERNATE-SHCEMABASED 

    HIBERNATE-ANNOTATIONS     SPRING-IOC       SPRING –AOP-SCHEMABASED   

    SPRING-AOP-ANNOTATIONS      SPRING -DAO     SPRIN-MVC     SPRING-SECUTITY  

cxample program 5:

//this program will describes –calling stored procedure from data base.