In the post Spring JdbcTemplate Insert, Update And Delete Example we have already seen how JdbcTemplate can be used in Spring framework for data access. If you have noticed the examples there, indexed parameters are used with the place holder '?' in the SQL queries. With indexed parameter it is very important that you pass the parameters in the correct order in the query. Problem with indexed parameter is that any change in the query may require a change in order of the parameters too. If you want to avoid that another option is named parameters. As the name suggests here each parameter is given a name and you bind the parameters to the query using those names. This post shows how you can insert, update and delete using NamedParameterJdbcTemplate in Spring.
In this Spring NamedParameterJdbcTemplate example we'll see both options using XML configuration as well by using annotations.
NamedParameterJdbcTemplate in Spring
Spring framework provides NamedParameterJdbcTemplate class which adds support for programming JDBC statements using named parameters, as opposed to programming JDBC statements using only classic placeholder ('?') arguments.
Here one thing to note is NamedParameterJdbcTemplate class wraps a JdbcTemplate, and delegates to the wrapped JdbcTemplate to do much of its work.
Spring NamedParameterJdbcTemplate Example
In this example we'll see how to do DB insert, update and delete using NamedParameterJdbcTemplate in Spring. The example shows both the ways- using Spring XML configuration as well as using annotations for component scanning and autowiring.
Note that NamedParameterJdbcTemplate needs a DataSource in order to perform its management of fixed part like getting a DB connection, cleaning up resources. In this post Apache DBCP is used which provides pooled datasource and MYSQL is used as the back end.
Technologies used
- Spring 5.0.4
- Apache DBCP2
- MYSQL 5.1.39
- Java 8
- Apache Maven 3.3.3
Maven Dependencies
If you are using maven then you can provide dependencies in your pom.xml.
- Refer Creating a Maven project in Eclipse to see how to set up Maven project.
With all the dependencies your pom.xml should look something like this-
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>org.netjs.prog</groupId> <artifactId>maven-spring</artifactId> <version>0.0.1-SNAPSHOT</version> <packaging>jar</packaging> <name>maven-spring</name> <url>http://maven.apache.org</url> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <spring.version>5.0.4.RELEASE</spring.version> </properties> <dependencies> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.11</version> <scope>test</scope> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-core</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>javax.inject</groupId> <artifactId>javax.inject</artifactId> <version>1</version> </dependency> <!-- Spring JDBC Support --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>${spring.version}</version> </dependency> <!-- MySQL Driver --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.39</version> </dependency> <!-- Apache DBCP connection pool --> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-dbcp2</artifactId> <version>2.1</version> </dependency> </dependencies> </project>
Alternatively you can download the jars and add them to the class path.
DB table used
For this example I have created a table called employee with the columns id, name and age in the MYSQL DB. Column id is configured as auto increment checked so no need to pass id from your query as DB will provide value for it.
CREATE TABLE `employee` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(35) DEFAULT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
Configuring datasource dependency
First thing is to set up DataSource as a bean. I have used properties file to configure datasource where all the properties are there in the db.properties file.
<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource">
<property name="driverClassName" value = "${db.driverClassName}" />
<property name="url" value = "${db.url}" />
<property name="username" value = "${db.username}" />
<property name="password" value = "${db.password}" />
<property name="initialSize" value = "${pool.initialSize}" />
</bean>
Where as db.properties file which is under the config folder has all the properties.
db.properties
db.driverClassName=com.mysql.jdbc.Driver db.url=jdbc:mysql://localhost:3306/netjs db.username= db.password= pool.initialSize=5
Description of the properties used here is as-
driver class name is the JDBC driver for the DB used. Since MYSQL is used here so the jdbc driver for the same (com.mysql.jdbc.Driver) is provided.
Url – You need to provide url to access your DB server. I have created a schema called netjs and DB is running on the same system so url is jdbc:mysql://localhost:3306/netjs.
Username and password for the DB.
IntialSize is the initial size of the connection pool. It is given as 5 so initially 5 connections will be created and stored in the pool.
To use properties file you need to put following configuration in your XML.
<context:property-placeholder location="classpath:config/db.properties" />
Spring NamedParameterJdbcTemplate configuration
DataSource bean has to be provided as a reference in NamedParameterJdbcTemplate.
<bean id="namedJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
<constructor-arg ref="dataSource"></constructor-arg>
</bean>
Java Classes
Since Spring always promotes to use interfaces and there is also a JEE design pattern for database layer called DAO which also says the same thing - Separate low level data access code from the business layers.
So we have a EmployeeDAO interface with insert, update and delete methods and its implementing class EmployeeDAOImpl. There is also a model class Employee with all the getters/setters.
Employee.java class
public class Employee {
private int empId;
private String empName;
private int age;
public int getEmpId() {
return empId;
}
public void setEmpId(int empId) {
this.empId = empId;
}
public String getEmpName() {
return empName;
}
public void setEmpName(String empName) {
this.empName = empName;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
}
EmployeeDAO interface
public interface EmployeeDAO {
public int save(Employee employee);
public void update(Employee employee);
public void deleteEmpById(int empId);
}
EmployeeDAOImpl class
import java.util.HashMap;
import java.util.Map;
import org.netjs.dao.EmployeeDAO;
import org.netjs.model.Employee;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
public class EmployeeDAOImpl implements EmployeeDAO {
private NamedParameterJdbcTemplate namedJdbcTemplate;
final String INSERT_QUERY = "insert into employee (name, age) values (:name, :age)";
final String UPDATE_QUERY = "update employee set age = :age where id = :id";
final String DELETE_QUERY = "delete from employee where id = :id";
public NamedParameterJdbcTemplate getNamedJdbcTemplate() {
return namedJdbcTemplate;
}
public void setNamedJdbcTemplate(NamedParameterJdbcTemplate namedJdbcTemplate) {
this.namedJdbcTemplate = namedJdbcTemplate;
}
@Override
public int save(Employee employee) {
// Creating map with all required params
Map<String, Object> paramMap = new HashMap<String, Object>();
paramMap.put("name", employee.getEmpName());
paramMap.put("age", employee.getAge());
// Passing map containing named params
return namedJdbcTemplate.update(INSERT_QUERY, paramMap);
}
@Override
public void update(Employee employee) {
// Adding params using MapSqlParameterSource class
SqlParameterSource namedParameters = new MapSqlParameterSource().addValue("age", employee.getAge()).addValue("id", employee.getEmpId());
int status = namedJdbcTemplate.update(UPDATE_QUERY, namedParameters);
if(status != 0){
System.out.println("Employee data updated for ID " + employee.getEmpId());
}else{
System.out.println("No Employee found with ID " + employee.getEmpId());
}
}
@Override
public void deleteEmpById(int empId) {
// Adding params using MapSqlParameterSource class
SqlParameterSource namedParameters = new MapSqlParameterSource("id", empId);
int status = namedJdbcTemplate.update(DELETE_QUERY, namedParameters);
if(status != 0){
System.out.println("Employee data deleted for ID " + empId);
}else{
System.out.println("No Employee found with ID " + empId);
}
}
}
This class contains namedJdbcTemplate property which will be wired by the Spring framework. In the save method named parameters are stored in a Map and that map is passed. In the update method another option MapSqlParameterSource class is used which has addValue method. Using the addValue method key, value pair is stored and later passed to the query.
Also notice how you are not writing any code for getting or closing connection,
exception handling. All that fixed part is managed by the template class itself.
If there is any SQLException thrown that is also caught by JDBCTemplate and translated to one of the
DataAccessException and rethrown.
Full XML Configuration
<?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.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd"> <!-- For reading properties files --> <context:property-placeholder location="classpath:config/db.properties" /> <bean id="namedJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate"> <constructor-arg ref="dataSource"></constructor-arg> </bean> <bean id="employeeDAO" class="org.netjs.daoimpl.EmployeeDAOImpl"> <property name="namedJdbcTemplate" ref="namedJdbcTemplate"></property> </bean> <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource"> <property name="driverClassName" value = "${db.driverClassName}" /> <property name="url" value = "${db.url}" /> <property name="username" value = "${db.username}" /> <property name="password" value = "${db.password}" /> <property name="initialSize" value = "${pool.initialSize}" /> </bean> </beans>
Here note that in the bean definition for NamedParameterJdbcTemplate, data source property is passed as a constructor argument.
Test class
You can use the following code in order to test the insertion, update and deletion.
import org.netjs.dao.EmployeeDAO;
import org.netjs.model.Employee;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class App {
public static void main(String[] args) {
ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext
("appcontext.xml");
EmployeeDAO dao=(EmployeeDAO)context.getBean("employeeDAO");
Employee emp = new Employee();
emp.setEmpName("John");
emp.setAge(25);
int status = dao.save(emp);
System.out.println(status);
// For update
emp.setEmpId(9);
emp.setAge(25);
dao.update(emp);
// For delete
dao.deleteEmpById(10);
}
}
- Refer @Import Annotation in Spring JavaConfig to see how to insert using NamedParameterJdbcTemplate in Spring JavaConfig.
Spring NamedParameterJdbcTemplate with annotations
You can also use component scanning to automatically scan and wire the classes. For that you can use @Repository annotation with your DAO implementation classes and @Autowired annotation to automatically wire dependencies.
- Refer Spring Component Scan Example to know more about automatic configuration.
In that case your EmployeeDAOImpl will look like this -
import java.util.HashMap;
import java.util.Map;
import org.netjs.dao.EmployeeDAO;
import org.netjs.model.Employee;
import org.springframework.beans.factory.annotation.Autowired;
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.Repository;
@Repository
public class EmployeeDAOImpl implements EmployeeDAO {
private NamedParameterJdbcTemplate namedJdbcTemplate;
final String INSERT_QUERY = "insert into employee (name, age) values (:name, :age)";
final String UPDATE_QUERY = "update employee set age = :age where id = :id";
final String DELETE_QUERY = "delete from employee where id = :id";
@Autowired
public EmployeeDAOImpl(NamedParameterJdbcTemplate namedJdbcTemplate){
this.namedJdbcTemplate = namedJdbcTemplate;
}
@Override
public int save(Employee employee) {
// Creating map with all required params
Map<String, Object> paramMap = new HashMap<String, Object>();
paramMap.put("name", employee.getEmpName());
paramMap.put("age", employee.getAge());
// Passing map containing named params
return namedJdbcTemplate.update(INSERT_QUERY, paramMap);
}
@Override
public void update(Employee employee) {
// Adding params using MapSqlParameterSource class
SqlParameterSource namedParameters = new MapSqlParameterSource().addValue("age", employee.getAge()).addValue("id", employee.getEmpId());
int status = namedJdbcTemplate.update(UPDATE_QUERY, namedParameters);
if(status != 0){
System.out.println("Employee data updated for ID " + employee.getEmpId());
}else{
System.out.println("No Employee found with ID " + employee.getEmpId());
}
}
@Override
public void deleteEmpById(int empId) {
// Adding params using MapSqlParameterSource class
SqlParameterSource namedParameters = new MapSqlParameterSource("id", empId);
int status = namedJdbcTemplate.update(DELETE_QUERY, namedParameters);
if(status != 0){
System.out.println("Employee data deleted for ID " + empId);
}else{
System.out.println("No Employee found with ID " + empId);
}
}
}
XML Configuration
XML configuration will also change as you have to provide the base package to scan and you can also comment the bean definition for EmployeeDAO as it will be done automatically.
<?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-4.0.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd"> <context:component-scan base-package="org.netjs.daoimpl" /> <!-- For reading properties files --> <context:property-placeholder location="classpath:config/db.properties" /> <bean id="namedJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate"> <constructor-arg ref="dataSource"></constructor-arg> </bean> <!-- <bean id="employeeDAO" class="org.netjs.daoimpl.EmployeeDAOImpl"> <property name="namedJdbcTemplate" ref="namedJdbcTemplate"></property> </bean> --> <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource"> <property name="driverClassName" value = "${db.driverClassName}" /> <property name="url" value = "${db.url}" /> <property name="username" value = "${db.username}" /> <property name="password" value = "${db.password}" /> <property name="initialSize" value = "${pool.initialSize}" /> </bean> </beans>
Now at the time of running the program you can get the EmployeeDAOImpl bean like this-
EmployeeDAO dao=(EmployeeDAO)context.getBean("employeeDAOImpl");
That's all for this topic Spring NamedParameterJdbcTemplate Insert, Update And Delete Example. If you have any doubt or any suggestions to make please drop a comment. Thanks!
>>>Return to Spring Tutorial Page
Related Topics
You may also like-