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>