Spring – NamedParameterJdbcTemplate

The Java Database Connectivity (JDBC API) provides universal data access from any data sources(including relational databases, spreadsheets & flat files). The JdbcTemplate is the most basic and classic approach for data access. The NamedParameterJdbcTemplate wraps the JdbcTemplate and allows the use of named parameters instead of the traditional JDBC ‘?’ placeholder.

Example

In this example, we will insert student data(id, name, department) into our data source using named parameters. NamedParameterJdbcTemplate has several methods we will use execute() method for this example.

Syntax of execute() method of NamedParameterJdbcTemplate:

public T execute(String sql, Map map, PreparedStatementCallback psc)

For this tutorial, we will be using the following schema for the Student table.

Student(id INT, name VARCHAR(45), department VARCHAR(45))

Step By Step Implementation

Step 1: Create Table

In this step, we will create a Student table to store students’ information. For this tutorial, we will assume you have created the following table in your database.

CREATE TABLE STUDENT(
id INT,
name VARCHAR(45),
department VARCHAR(45));

Step 2: Adding dependencies

In this step, we will add the maven dependencies to our application. Add the following dependencies to your pom.xml 

XML

<dependencies>

          <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
          <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.3.16</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.springframework/spring-context -->
        <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-context</artifactId>
                <version>5.0.8.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.44</version>
        </dependency>

</dependencies>

Step 3: Create a model class

Now, we will create a model class for our students. This class will have three-member variables id, name, and department. We will also define its getters and setters method along with the toString() method and constructors.

Java

public class Student {
    // member variables
    private int id;
    private String name;
    private String department;

    public Student() {}

    // parameterized constructor
    public Student(int id, String name, String department) {
        super();
        this.id = id;
        this.name = name;
        this.department = department;
    }

    // getters and setters method
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getDepartment() {
        return department;
    }
    public void setDepartment(String department) {
        this.department = department;
    }

    // toString() method
    @Override
    public String toString() {
        return "Student [id=" + id + ", name=" + name + ", department=" + department + "]";
    }
}

Step 4: Create a StudentDao class

In this step, we will create a StudentDao class and define the insertStudentInfo() method and provide its definition to insert data in our data source.

Java

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import com.geeksforgeeks.model.Student;

public class StudentDao{

    // Defining NamedParameterJdbcTemplate as member variable in order
    // to use the execute() method of the NamedParameterJdbcTemplate's class
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    // Constructor
    public  StudentDao(NamedParameterJdbcTemplate namedParameterJdbcTemplate) {
        this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
    }

    // User defined method to insert query data in data source
    public void insertStudentInfo(Student student) {
        String sqlQuery = "INSERT INTO student VALUES(:id, :name, :department)";
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("id", student.getId());
        map.put("name", student.getName());
        map.put("department", student.getDepartment());
        namedParameterJdbcTemplate.execute(sqlQuery, map, new PreparedStatementCallback() {
            public Object doInPreparedStatement(PreparedStatement ps) throws SQLException, DataAccessException {
                return ps.executeUpdate();
            }
        });

    }
}

Step 5: Bean Configuration

In this step, we will create the spring configuration file and name it application-context.xml. We will configure our beans and use the factory-method attribute for bean creation. In order to make a connection to the database, we need the following information username, password, database connection, URL, and the driver class name.  All this information is contained in the DriverManagerDataSource class, it has the getConnection() method which returns a connection of java type. We are using the instance of NamedParameterJdbcTemplate in our StudentDao class and passing it using the constructor injection method.

Note: In application-context, you need to define the whole path of your dao class

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.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
        <property name="url" value="jdbc:mysql://localhost:3306/student_db?autoReconnect=true&useSSL=false" />
        <property name="username" value="root" />
        <property name="password" value="root" />
    </bean>

    <bean id="namedParameterJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
        <constructor-arg ref="dataSource"></constructor-arg>
    </bean>

    <bean id="studentDao" class="com.geeksforgeeks.dao.StudentDao">
        <constructor-arg><ref bean="namedParameterJdbcTemplate"/></constructor-arg>
    </bean>

</beans>

Step 6: Creating Utilities Class

Now, we will create a Utility class for testing our application. For this create a new class and name it TestNamedParameterJdbcTemplate.java and add the following code to it.

Java

import org.springframework.beans.factory.BeanFactory;
import org.springframework.beans.factory.xml.XmlBeanFactory;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;

import com.geeksforgeeks.dao.StudentDao;
import com.geeksforgeeks.model.Student;

public class TestNamedParameterJdbcTemplate {

    public static void main(String[] agrs) {

         // Reading the application-context file using
         // class path of spring context xml file
         Resource resource = new ClassPathResource("application-context.xml");
         BeanFactory factory = new XmlBeanFactory(resource);

         // Spring check the blueprint for studentDao bean
         // from application-context.xml file and return it
         StudentDao studentDao = (StudentDao)factory.getBean("studentDao");
         Student studentObj = new Student(1, "geek", "computer science department");
         studentDao.insertStudentInfo(studentObj);
         System.out.println(studentObj);
    }

}

Step 7: Output

Now, we will run our application