Spring JDBC - NamedParameterJdbcTemplate Class

---

---

Overview

The org.springframework.jdbc.core.NamedParameterJdbcTemplate class is a 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 to expand a list of values to the appropriate number of placeholders.

Class Declaration

Following is the declaration for org.springframework.jdbc.core.NamedParameterJdbcTemplate class −

public class NamedParameterJdbcTemplate
   extends Object
      implements NamedParameterJdbcOperations

Syntax

MapSqlParameterSource in = new MapSqlParameterSource();
in.addValue("id", id);
in.addValue("description",  new SqlCharacterValue(description));

String SQL = "update Student set description = :description where id = :id";
NamedParameterJdbcTemplate jdbcTemplateObject = new NamedParameterJdbcTemplate(dataSource);
jdbcTemplateObject.update(SQL, in);

Where,

• in − SqlParameterSource object to pass a parameter to update a query.

• SqlCharacterValue − Object to represent an SQL CLOB value parameter.

• jdbcTemplateObject − NamedParameterJdbcTemplate object to update student object in the database.

Example - Usage of NamedParameterJdbcTemplate class

To understand the above-mentioned concepts related to Spring JDBC, let us write an example which will update a query. To write our example, let us have a working Eclipse IDE in place and use the following steps to create a Spring application.

StudentDAO.java

Following is the content of the Data Access Object interface file StudentDAO.java.

package com.tutorialspoint;

import java.util.List;
import javax.sql.DataSource;

public interface StudentDAO {
   /** 
    * This is the method to be used to initialize
    * database resources ie. connection.
   */
   public void setDataSource(DataSource ds);
   
   /** 
    * This is the method to be used to update
    * a record into the Student table.
   */
   public void updateDescription(Integer id, String description);
}

Student.java

Following is the content of the Student.java file.

package com.tutorialspoint;

// Student POJO for Student Table
public class Student {
   private Integer age;
   private String name;
   private Integer id;
   private String description;

   // setter/getter methods
   public void setAge(Integer age) {
      this.age = age;
   }
   public Integer getAge() {
      return age;
   }
   public void setName(String name) {
      this.name = name;
   }
   public String getName() {
      return name;
   }
   public void setId(Integer id) {
      this.id = id;
   }
   public Integer getId() {
      return id;
   }
   public String getDescription() {
      return description;
   }
   public void setDescription(String description) {
      this.description = description;
   }
}

StudentMapper.java

Following is the content of the StudentMapper.java file.

package com.tutorialspoint;

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

// Row Mapper Object to map Student table entry with Student Object
public class StudentMapper implements RowMapper<Student> {
   public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
      Student student = new Student();
      student.setId(rs.getInt("id"));
      student.setName(rs.getString("name"));
      student.setAge(rs.getInt("age"));
      student.setDescription(rs.getString("description"));
      return student;
   }
}

StudentJDBCTemplate.java

Following is the implementation class file StudentJDBCTemplate.java for the defined DAO interface StudentDAO.

package com.tutorialspoint;

import javax.sql.DataSource;

import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.support.SqlCharacterValue;

// DAO instance to persist Student values
public class StudentJDBCTemplate implements StudentDAO {
   private DataSource dataSource;
   private NamedParameterJdbcTemplate jdbcTemplateObject;
   
   // set the datasource and jdbctemplate
   public void setDataSource(DataSource dataSource) {
      this.dataSource = dataSource;
	  this.jdbcTemplateObject = new NamedParameterJdbcTemplate(dataSource);
   }
   
   // Update the description of student based on id
   public void updateDescription(Integer id, String description) {
      MapSqlParameterSource in = new MapSqlParameterSource();
      in.addValue("id", id);
      in.addValue("description",  new SqlCharacterValue(description));

      String SQL = "update Student set description = :description where id = :id";
      
      jdbcTemplateObject.update(SQL, in);
      System.out.println("Updated Record with ID = " + id );
   }
}

MainApp.java

Following is the content of the MainApp.java file.

package com.tutorialspoint;

import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.tutorialspoint.StudentJDBCTemplate;

public class MainApp {
   public static void main(String[] args) {
      // Create the application context by reading Beans.xml  
      ApplicationContext context = new ClassPathXmlApplicationContext("Beans.xml");
      // Create the JDBCTemplate instance from spring context
      StudentJDBCTemplate studentJDBCTemplate = (StudentJDBCTemplate)context.getBean("studentJDBCTemplate");
      // update student description
      studentJDBCTemplate.updateDescription(1, "This can be a very long text upto 4 GB of size."); 
   }
}

Beans.xml

Following is the configuration file Beans.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" 
   xsi:schemaLocation = "http://www.springframework.org/schema/beans
   http://www.springframework.org/schema/beans/spring-beans-3.0.xsd ">

   <!-- Initialization for data source -->
   <bean id = "dataSource" 
      class = "org.springframework.jdbc.datasource.DriverManagerDataSource">
      <property name = "driverClassName" value = "com.mysql.jdbc.Driver"/>
      <property name = "url" value = "jdbc:mysql://localhost:3306/tutorialspoint"/>
      <property name = "username" value = "guest"/>
      <property name = "password" value = "guest123"/>
   </bean>

   <!-- Definition for studentJDBCTemplate bean -->
   <bean id = "studentJDBCTemplate" 
      class = "com.tutorialspoint.StudentJDBCTemplate">
      <property name = "dataSource" ref = "dataSource" />    
   </bean>      
</beans>

Output

Once you are done creating the source and bean configuration files, let us run the application. If everything is fine with your application, it will print the following message.

Updated Record with ID = 1

You can check the description stored by querying the database.