Spring Named Parameter JDBC Template

We'll use the NamedParameterJdbcTemplate class to run queries with named placeholders.
SimpleJdbcTemplate is deprecated in Spring 3.1

NamedParameterJdbcTemplate is remaining and should be used for named parameter use. If you don't want to create two templates you can always call getJdbcOperations() on the NamedParameterJdbcTemplate to get access to the regular JdbcOperations interface of the JdbcTemplate.

Template class with a basic set of JDBC operations, allowing the use of named parameters rather than traditional '?' placeholders.

This class delegates to a wrapped JdbcTemplate once the substitution from named parameters to JDBC style '?' placeholders is done at execution time. It also allows for expanding a List of values to the appropriate number of placeholders.

The underlying JdbcTemplate is exposed to allow for convenient access to the traditional JdbcTemplate methods.

JDBCDemo.java

package com.venkat;

import java.util.Iterator;
import java.util.List;

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

import com.venkat.JdbcDao.JdbcDaoImpl;
import com.venkat.model.Student;

public class JDBCDemo {

    /**
     * @param args
     * @throws Exception
     */
    public static void main(String[] args)   {
        // TODO Auto-generated method stub
       
        ApplicationContext context=new ClassPathXmlApplicationContext("applicationContext.xml");
          JdbcDaoImpl dao=context.getBean("jdbcDaoImpl",JdbcDaoImpl.class);
    //dao.createPersonTable();
       
         
          System.out.println(dao.getStudentCount());
          System.out.println(dao.getStudentName(121));
          System.out.println(dao.getStudentForId(121).getStudentrank());
         dao.insertStudent(new Student(444, "pradeep", 48));
         
         List<Student> list=dao.getAllStudents();
            System.out.println("Student Details...............");
         for (Iterator iterator = list.iterator(); iterator.hasNext();)
         {
            Student student = (Student) iterator.next();
            System.out.print(student.getStudentid()+"\t");
            System.out.print(student.getStudentname()+"\t");
            System.out.println(student.getStudentrank());
        }
             }

}
*************************************************************************
JdbcDaoImpl.java

package com.venkat.JdbcDao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Component;

import com.venkat.model.Student;

@Component
public class JdbcDaoImpl {
    private DataSource datasource;
    private JdbcTemplate jdbcTemplate;
    private NamedParameterJdbcTemplate  namedParameterJdbcTemplate;
   

public DataSource getDatasource() {
        return datasource;
    }


    public JdbcTemplate getJdbcTemplate() {
    return jdbcTemplate;
}


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

    @Autowired
    public void setDatasource(DataSource datasource) {
        this.jdbcTemplate = new JdbcTemplate(datasource);
        this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(datasource);
       
    }

public int getStudentCount()
{
    String sql="select count(*) from STUDENT";
    return jdbcTemplate.queryForInt(sql);
           
}

public String getStudentName(int studentid)
{
    String sql="select studentname from STUDENT where studentid=?";
    return jdbcTemplate.queryForObject(sql, new Object[]{studentid}, String.class);
           
}
public Student getStudentForId(int studentid)
{
    String sql="select * from STUDENT where studentid=?";
    return jdbcTemplate.queryForObject(sql, new Object[]{studentid}, new StudentMapper());
           
}
//public void insertStudent(Student student)
//{
//    String sql="insert into STUDENT (studentid,studentname,studentrank) values(?,?,?) ";
//    jdbcTemplate.update(sql,new Object[]{student.getStudentid(),student.getStudentname(),student.getStudentrank()});
//}

public void insertStudent(Student student)
{
    String sql="insert into STUDENT (studentid,studentname,studentrank) values(:studentid,:studentname,:studentrank) ";
SqlParameterSource namedParameters=new MapSqlParameterSource("studentid",student.getStudentid()).addValue("studentname", student.getStudentname()).addValue("studentrank", student.getStudentrank());
namedParameterJdbcTemplate.update(sql, namedParameters);
}

public List<Student> getAllStudents()
{
    String sql="select * from STUDENT ";
    return  jdbcTemplate.query(sql,   new StudentMapper());
           
}

public void createPersonTable()
{
    String sql="create table Person (id Number(3),name varchar2(12))";
    jdbcTemplate.execute(sql);
}
private static final class StudentMapper implements RowMapper<Student>
{

    @Override
    public Student mapRow(ResultSet resultset, int rowNum) throws SQLException {
        Student s=new Student();
        s.setStudentid(resultset.getInt("studentid"));
        s.setStudentname(resultset.getString("studentname"));
        s.setStudentrank(resultset.getInt("studentrank"));
       
        return s;
    }
   
   
   
}
}

***********************************************************************
package com.venkat.model;

public class Student {
   
    private int studentid;
    private String studentname;
    private int studentrank;
    public int getStudentid() {
        return studentid;
    }
    public void setStudentid(int studentid) {
        this.studentid = studentid;
    }
    public String getStudentname() {
        return studentname;
    }
    public void setStudentname(String studentname) {
        this.studentname = studentname;
    }
    public int getStudentrank() {
        return studentrank;
    }
    public void setStudentrank(int studentrank) {
        this.studentrank = studentrank;
    }
    public Student(int studentid, String studentname, int studentrank) {
        super();
        this.studentid = studentid;
        this.studentname = studentname;
        this.studentrank = studentrank;
    }
    public Student() {
        // TODO Auto-generated constructor stub
    }
   

}
*****************************************************************
<?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:context="http://www.springframework.org/schema/context"
  xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-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/context http://www.springframework.org/schema/context/spring-context-2.5.xsd">

<context:annotation-config/>

<context:component-scan base-package="com.venkat"></context:component-scan>



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

Performing Write Operations with JdbcTemplate

We'll now implement some database write operations. We'll write a DAO method to update a value in the database, as well as use the execute() method to run a DDL SQL query.



package com.venkat;


import java.util.Iterator;
import java.util.List;


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


import com.venkat.JdbcDao.JdbcDaoImpl;
import com.venkat.model.Student;


public class JDBCDemo {


/**
* @param args
* @throws Exception 
*/
public static void main(String[] args)   {
// TODO Auto-generated method stub

ApplicationContext context=new ClassPathXmlApplicationContext("applicationContext.xml");
 JdbcDaoImpl dao=context.getBean("jdbcDaoImpl",JdbcDaoImpl.class);
//dao.createPersonTable();

 
 System.out.println(dao.getStudentCount());
 System.out.println(dao.getStudentName(121));
 System.out.println(dao.getStudentForId(121).getStudentrank());
dao.insertStudent(new Student(345, "Sandeep", 8));
 
List<Student> list=dao.getAllStudents();
System.out.println("Student Details...............");
for (Iterator iterator = list.iterator(); iterator.hasNext();) 
{
Student student = (Student) iterator.next();
System.out.print(student.getStudentid()+"\t");
System.out.print(student.getStudentname()+"\t");
System.out.println(student.getStudentrank());
}
}


}

*************************************************************************

package com.venkat.JdbcDao;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.core.RowMapper;

import org.springframework.stereotype.Component;

import com.venkat.model.Student;

@Component

public class JdbcDaoImpl {

private DataSource datasource;

private JdbcTemplate jdbcTemplate;

public DataSource getDatasource() {

return datasource;

}

public JdbcTemplate getJdbcTemplate() {

return jdbcTemplate;

}

public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {

this.jdbcTemplate = jdbcTemplate;

}

@Autowired

public void setDatasource(DataSource datasource) {

this.jdbcTemplate = new JdbcTemplate(datasource);

}

public int getStudentCount()

{

String sql="select count(*) from STUDENT";

return jdbcTemplate.queryForInt(sql);

}

public String getStudentName(int studentid)

{

String sql="select studentname from STUDENT where studentid=?";

return jdbcTemplate.queryForObject(sql, new Object[]{studentid}, String.class);

}

public Student getStudentForId(int studentid)

{

String sql="select * from STUDENT where studentid=?";

return jdbcTemplate.queryForObject(sql, new Object[]{studentid}, new StudentMapper());

}

public void insertStudent(Student student)

{

String sql="insert into STUDENT (studentid,studentname,studentrank) values(?,?,?) ";

jdbcTemplate.update(sql,new Object[]{student.getStudentid(),student.getStudentname(),student.getStudentrank()});

}

public List<Student> getAllStudents()

{

String sql="select * from STUDENT ";

return  jdbcTemplate.query(sql,   new StudentMapper());

}

public void createPersonTable()

{

String sql="create table Person (id Number(3),name varchar2(12))";

jdbcTemplate.execute(sql);

}

private static final class StudentMapper implements RowMapper<Student>

{

@Override

public Student mapRow(ResultSet resultset, int rowNum) throws SQLException {

Student s=new Student();

s.setStudentid(resultset.getInt("studentid"));

s.setStudentname(resultset.getString("studentname"));

s.setStudentrank(resultset.getInt("studentrank"));

return s;

}

}

}

************************************************************************

package com.venkat.model;

public class Student {

private int studentid;

private String studentname;

private int studentrank;

public int getStudentid() {

return studentid;

}

public void setStudentid(int studentid) {

this.studentid = studentid;

}

public String getStudentname() {

return studentname;

}

public void setStudentname(String studentname) {

this.studentname = studentname;

}

public int getStudentrank() {

return studentrank;

}

public void setStudentrank(int studentrank) {

this.studentrank = studentrank;

}

public Student(int studentid, String studentname, int studentrank) {

super();

this.studentid = studentid;

this.studentname = studentname;

this.studentrank = studentrank;

}

public Student() {

// TODO Auto-generated constructor stub

}

}

************************************************************

<?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:context="http://www.springframework.org/schema/context"

  xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-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/context http://www.springframework.org/schema/context/spring-context-2.5.xsd">

<context:annotation-config/>

<context:component-scan base-package="com.venkat"></context:component-scan>

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">

<property name="driverClassName"

value="oracle.jdbc.driver.OracleDriver">

</property>

<property name="url"

value="jdbc:oracle:thin:@localhost:1521:xe">

</property>

<property name="username" value="venkat"></property>

<property name="password" value="venkat"></property>

</bean></beans>

Spring JDBC Implementing RowMapper

In this tutorial, we implement a custom RowMapper class to map our domain objects. We then use this class to write fetch methods that return custom model objects.



 <Student> List<Student> org.springframework.jdbc.core.JdbcTemplate.query(String sql, RowMapper<Student> rowMapper) throws DataAccessException
query
public <T> List<T> query(String sql,
                         RowMapper<T> rowMapper)
              throws DataAccessException
Description copied from interface: JdbcOperations 
Execute a query given static SQL, mapping each row to a Java object via a RowMapper. 
Uses a JDBC Statement, not a PreparedStatement. If you want to execute a static query with a PreparedStatement, use the overloaded query method with null as argument array. 




Specified by:
query in interface JdbcOperations
Parameters:
sql - SQL query to execute
rowMapper - object that will map one object per row 
Returns:
the result List, containing mapped objects 
Throws: 
DataAccessException - if there is any problem executing the query
See Also:
JdbcOperations.query(String, Object[], RowMapper)
--------------------------------------------------------------------------------

An interface used by JdbcTemplate for mapping rows of a ResultSet on a per-row basis. Implementations of this interface perform the actual work of mapping each row to a result object, but don't need to worry about exception handling. SQLExceptions will be caught and handled by the calling JdbcTemplate. 

Typically used either for JdbcTemplate's query methods or for out parameters of stored procedures. RowMapper objects are typically stateless and thus reusable; they are an ideal choice for implementing row-mapping logic in a single place. 

Alternatively, consider subclassing MappingSqlQuery from the jdbc.object package: Instead of working with separate JdbcTemplate and RowMapper objects, you can build executable query objects (containing row-mapping logic) in that style. 

****************************************************************************

package com.venkat;

import java.util.Iterator;

import java.util.List;

import org.springframework.context.ApplicationContext;

import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.venkat.JdbcDao.JdbcDaoImpl;

import com.venkat.model.Student;

public class JDBCDemo {

/**

* @param args

* @throws Exception 

*/

public static void main(String[] args)   {

// TODO Auto-generated method stub

ApplicationContext context=new ClassPathXmlApplicationContext("applicationContext.xml");

 JdbcDaoImpl dao=context.getBean("jdbcDaoImpl",JdbcDaoImpl.class);

 System.out.println(dao.getStudentCount());

 System.out.println(dao.getStudentName(121));

 System.out.println(dao.getStudentForId(121).getStudentrank());

List<Student> list=dao.getAllStudents();

System.out.println("Student Details...............");

for (Iterator iterator = list.iterator(); iterator.hasNext();) 

{

Student student = (Student) iterator.next();

System.out.print(student.getStudentid()+"\t");

System.out.print(student.getStudentname()+"\t");

System.out.println(student.getStudentrank());

}

}

}

******************************************************************

package com.venkat.JdbcDao;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.jdbc.core.RowMapper;

import org.springframework.stereotype.Component;

import com.venkat.model.Student;

@Component

public class JdbcDaoImpl {

private DataSource datasource;

private JdbcTemplate jdbcTemplate;

public DataSource getDatasource() {

return datasource;

}

public JdbcTemplate getJdbcTemplate() {

return jdbcTemplate;

}

public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {

this.jdbcTemplate = jdbcTemplate;

}

@Autowired

public void setDatasource(DataSource datasource) {

this.jdbcTemplate = new JdbcTemplate(datasource);

}

public int getStudentCount()

{

String sql="select count(*) from STUDENT";

return jdbcTemplate.queryForInt(sql);

}

public String getStudentName(int studentid)

{

String sql="select studentname from STUDENT where studentid=?";

return jdbcTemplate.queryForObject(sql, new Object[]{studentid}, String.class);

}

public Student getStudentForId(int studentid)

{

String sql="select * from STUDENT where studentid=?";

return jdbcTemplate.queryForObject(sql, new Object[]{studentid}, new StudentMapper());

}

public List<Student> getAllStudents()

{

String sql="select * from STUDENT ";

return  jdbcTemplate.query(sql,   new StudentMapper());

}

private static final class StudentMapper implements RowMapper<Student>

{

@Override

public Student mapRow(ResultSet resultset, int rowNum) throws SQLException {

Student s=new Student();

s.setStudentid(resultset.getInt("studentid"));

s.setStudentname(resultset.getString("studentname"));

s.setStudentrank(resultset.getInt("studentrank"));

return s;

}

}

}

********************************************************************

package com.venkat.model;

public class Student {

private int studentid;

private String studentname;

private int studentrank;

public int getStudentid() {

return studentid;

}

public void setStudentid(int studentid) {

this.studentid = studentid;

}

public String getStudentname() {

return studentname;

}

public void setStudentname(String studentname) {

this.studentname = studentname;

}

public int getStudentrank() {

return studentrank;

}

public void setStudentrank(int studentrank) {

this.studentrank = studentrank;

}

public Student(int studentid, String studentname, int studentrank) {

super();

this.studentid = studentid;

this.studentname = studentname;

this.studentrank = studentrank;

}

public Student() {

// TODO Auto-generated constructor stub

}

}

*******************************************************************

<?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:context="http://www.springframework.org/schema/context"

  xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-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/context http://www.springframework.org/schema/context/spring-context-2.5.xsd">

<context:annotation-config/>

<context:component-scan base-package="com.venkat"></context:component-scan>

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">

<property name="driverClassName"

value="oracle.jdbc.driver.OracleDriver">

</property>

<property name="url"

value="jdbc:oracle:thin:@localhost:1521:xe">

</property>

<property name="username" value="venkat"></property>

<property name="password" value="venkat"></property>

</bean></beans>

SPRING Returning Other Datatypes from JdbcTemplate

We returned an integer datatype result from our query in the previous tutorial. In this tutorial, we'll learn how to return other datatypes. We'll implement a method that returns a String query output.



 int org.springframework.jdbc.core.JdbcTemplate.queryForInt(String sql) throws DataAccessException
queryForInt
public int queryForInt(String sql)
                throws DataAccessException
Description copied from interface: JdbcOperations 
Execute a query that results in an int value, given static SQL. 
Uses a JDBC Statement, not a PreparedStatement. If you want to execute a static query with a PreparedStatement, use the overloaded queryForInt method with null as argument array. 


This method is useful for running static SQL with a known outcome. The query is expected to be a single row/single column query that results in an int value. 




Specified by:
queryForInt in interface JdbcOperations
Parameters:
sql - SQL query to execute 
Returns:
the int value, or 0 in case of SQL NULL 
Throws: 
IncorrectResultSizeDataAccessException - if the query does not return exactly one row, or does not return exactly one column in that row 
DataAccessException - if there is any problem executing the query
See Also:
JdbcOperations.queryForInt(String, Object[])



****************************************************************


 <String> String org.springframework.jdbc.core.JdbcTemplate.queryForObject(String sql, Object[] args, Class<String> requiredType) throws DataAccessException






queryForObject
public <T> T queryForObject(String sql,
                            Object[] args,
                            Class<T> requiredType)
                 throws DataAccessException
Description copied from interface: JdbcOperations 
Query given SQL to create a prepared statement from SQL and a list of arguments to bind to the query, expecting a result object. 
The query is expected to be a single row/single column query; the returned result will be directly mapped to the corresponding object type. 




Specified by:
queryForObject in interface JdbcOperations
Parameters:
sql - SQL query to execute
args - arguments to bind to the query (leaving it to the PreparedStatement to guess the corresponding SQL type); may also contain SqlParameterValue objects which indicate not only the argument value but also the SQL type and optionally the scale
requiredType - the type that the result object is expected to match 
Returns:
the result object of the required type, or null in case of SQL NULL 
Throws: 
IncorrectResultSizeDataAccessException - if the query does not return exactly one row, or does not return exactly one column in that row 
DataAccessException - if the query fails
See Also:
JdbcOperations.queryForObject(String, Class)
****************************************************************************

package com.venkat;


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


import com.venkat.JdbcDao.JdbcDaoImpl;


public class JDBCDemo {


/**
* @param args
* @throws Exception 
*/
public static void main(String[] args)   {
// TODO Auto-generated method stub

ApplicationContext context=new ClassPathXmlApplicationContext("applicationContext.xml");
 JdbcDaoImpl dao=context.getBean("jdbcDaoImpl",JdbcDaoImpl.class);

 System.out.println(dao.getStudentCount());
 System.out.println(dao.getStudentName(121));
}


}

************************************************************************

package com.venkat.JdbcDao;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.jdbc.core.JdbcTemplate;

import org.springframework.stereotype.Component;

@Component

public class JdbcDaoImpl {

private DataSource datasource;

private JdbcTemplate jdbcTemplate;

public DataSource getDatasource() {

return datasource;

}

public JdbcTemplate getJdbcTemplate() {

return jdbcTemplate;

}

public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {

this.jdbcTemplate = jdbcTemplate;

}

@Autowired

public void setDatasource(DataSource datasource) {

this.jdbcTemplate = new JdbcTemplate(datasource);

}

public int getStudentCount()

{

String sql="select count(*) from STUDENT";

return jdbcTemplate.queryForInt(sql);

}

public String getStudentName(int studentid)

{

String sql="select studentname from STUDENT where studentid=?";

return jdbcTemplate.queryForObject(sql, new Object[]{studentid}, String.class);

}

}

**************************************************************************

package com.venkat.model;

public class Student {

private int studentid;

private String studentname;

private int studentrank;

public int getStudentid() {

return studentid;

}

public void setStudentid(int studentid) {

this.studentid = studentid;

}

public String getStudentname() {

return studentname;

}

public void setStudentname(String studentname) {

this.studentname = studentname;

}

public int getStudentrank() {

return studentrank;

}

public void setStudentrank(int studentrank) {

this.studentrank = studentrank;

}

public Student(int studentid, String studentname, int studentrank) {

super();

this.studentid = studentid;

this.studentname = studentname;

this.studentrank = studentrank;

}

}

************************************************************************

<?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:context="http://www.springframework.org/schema/context"

  xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-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/context http://www.springframework.org/schema/context/spring-context-2.5.xsd">

<context:annotation-config/>

<context:component-scan base-package="com.venkat"></context:component-scan>

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">

<property name="driverClassName"

value="oracle.jdbc.driver.OracleDriver">

</property>

<property name="url"

value="jdbc:oracle:thin:@localhost:1521:xe">

</property>

<property name="username" value="venkat"></property>

<property name="password" value="venkat"></property>

</bean></beans>

Spring JDBCTemplate

We'll use the JdbcTemplate class provided by Spring to implement a DAO method. We'll see how using the template class makes the DAO methods simple, and how it takes care of much of the boilerplate code we'd have to write otherwise.

org.springframework.jdbc.core.JdbcTemplate

This is the central class in the JDBC core package. It simplifies the use of JDBC and helps to avoid common errors. It executes core JDBC workflow, leaving application code to provide SQL and extract results. This class executes SQL queries or updates, initiating iteration over ResultSets and catching JDBC exceptions and translating them to the generic, more informative exception hierarchy defined in the org.springframework.dao package.
Code using this class need only implement callback interfaces, giving them a clearly defined contract. The PreparedStatementCreator callback interface creates a prepared statement given a Connection, providing SQL and any necessary parameters. The ResultSetExtractor interface extracts values from a ResultSet. See also PreparedStatementSetter and RowMapper for two popular alternative callback interfaces.
Can be used within a service implementation via direct instantiation with a DataSource reference, or get prepared in an application context and given to services as bean reference. Note: The DataSource should always be configured as a bean in the application context, in the first case given to the service directly, in the second case to the prepared template.
Because this class is parameterizable by the callback interfaces and the SQLExceptionTranslator interface, there should be no need to subclass it.
All SQL operations performed by this class are logged at debug level, using "org.springframework.jdbc.core.JdbcTemplate" as log category.


************************************************************************
package com.venkat;


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


import com.venkat.JdbcDao.JdbcDaoImpl;
import com.venkat.model.Login;


public class JDBCDemo {


/**
* @param args
* @throws Exception 
*/
public static void main(String[] args) throws Exception  {
// TODO Auto-generated method stub

ApplicationContext context=new ClassPathXmlApplicationContext("applicationContext.xml");
 JdbcDaoImpl dao=context.getBean("jdbcDaoImpl",JdbcDaoImpl.class);

//  Login login=dao.getLogin("venkat");

//System.out.println(login.getPassword());
 System.out.println(dao.getLoginCount());
}


}
************************************************************************
package com.venkat.JdbcDao;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;


import javax.sql.DataSource;


import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;


import com.venkat.model.Login;
@Component
public class JdbcDaoImpl {
private DataSource datasource;
private JdbcTemplate jdbcTemplate;



public DataSource getDatasource() {
return datasource;
}




public JdbcTemplate getJdbcTemplate() {
return jdbcTemplate;
}




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


@Autowired
public void setDatasource(DataSource datasource) {
this.jdbcTemplate = new JdbcTemplate(datasource);
}


public int getLoginCount()
{
String sql="select count(*) from LOGIN";
//jdbcTemplate.setDataSource(getDatasource());
return jdbcTemplate.queryForInt(sql);

}
public Login getLogin(String userid) throws Exception
{
Connection con=datasource.getConnection();
PreparedStatement ps=con.prepareStatement("Select * from LOGIN where userid=?");
ps.setString(1, userid);

Login l=null;
ResultSet rs=ps.executeQuery();

if(rs.next())
{
l=new Login(userid, rs.getString("password"));
}
rs.close();
ps.close();
return l;




}
}
****************************************************************************
package com.venkat.model;


public class Login {

private String userid;
public String getUserid() {
return userid;
}
public void setUserid(String userid) {
this.userid = userid;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
private String password;
public Login(String userid, String password) {
super();
this.userid = userid;
this.password = password;
}
 
}
*************************************************************************
<?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:context="http://www.springframework.org/schema/context"
  xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-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/context http://www.springframework.org/schema/context/spring-context-2.5.xsd">


<context:annotation-config/>


<context:component-scan base-package="com.venkat"></context:component-scan>






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