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.w3wiki.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.w3wiki.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.w3wiki.dao.StudentDao; import com.w3wiki.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
Contact Us