In the post Data access in Spring framework we have already seen how Spring provides templates for various persistence methods and how templates divide the data access code into fixed part and variable part. Where Spring framework manages the fixed part and custom code which is provided by the user is handled through callbacks. In this post we’ll see how to use Spring JdbcTemplate to insert, update and delete data from the database.
- Refer Spring JdbcTemplate Select Query Example to see how to read data from DB using Select Query.
Note that JdbcTemplate 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 for providing 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.
Database table
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;
Setting up 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 for the properties used here-
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" />
- Refer How to read properties file in Spring Framework to see various ways properties file can be read in Spring framework.
Spring JdbcTemplate Configuration
DataSource bean has to be provided as a reference in JdbcTemplate.
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</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
import org.netjs.model.Employee;
public interface EmployeeDAO {
public int save(Employee employee);
public void update(Employee employee);
public void deleteEmpById(int empId);
}
EmployeeDAOImpl class
import org.netjs.dao.EmployeeDAO;
import org.netjs.model.Employee;
import org.springframework.jdbc.core.JdbcTemplate;
public class EmployeeDAOImpl implements EmployeeDAO {
private JdbcTemplate jdbcTemplate;
final String INSERT_QUERY = "insert into employee (name, age) values (?, ?)";
final String UPDATE_QUERY = "update employee set age = ? where id = ?";
final String DELETE_QUERY = "delete from employee where id = ?";
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
public int save(Employee employee) {
return jdbcTemplate.update(INSERT_QUERY, employee.getEmpName(), employee.getAge());
}
public void update(Employee employee) {
int status = jdbcTemplate.update(UPDATE_QUERY, employee.getAge(),
employee.getEmpId());
if(status != 0){
System.out.println("Employee data updated for ID " + employee.getEmpId());
}else{
System.out.println("No Employee found with ID " + employee.getEmpId());
}
}
public void deleteEmpById(int empId) {
int status = jdbcTemplate.update(DELETE_QUERY, empId);
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 jdbcTemplate property which will be injected by the Spring framework. In the save method insert query is executed and the parameters are provided to it. Note these are indexed parameters.
- Refer Spring NamedParameterJdbcTemplate Insert, Update And Delete Example to see how to use named parameters using NamedParameterJdbcTemplate.
Also notice how you are not writing any code for getting or closing connection, exception handling. All that fixed part is managed by the JdbcTemplate class. Its the JdbcTemplate which is getting the connection using the DataSource provided to it, creating and executing the statement and closing the connection.
If there is any SQLException thrown that is also caught by JdbcTemplate and translated to one of the DataAccessException and rethrown.
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">
<context:property-placeholder location="classpath:config/db.properties" />
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
<bean id="employeeDAO" class="org.netjs.daoimpl.EmployeeDAOImpl">
<property name="jdbcTemplate" ref="jdbcTemplate"></property>
</bean>
<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource">
<property name="driverClassName" value = "com.mysql.jdbc.Driver" />
<property name="url" value = "jdbc:mysql://localhost:3306/netjs" />
<property name="username" value = "root" />
<property name="password" value = "admin" />
<property name="initialSize" value = "5" />
</bean>
</beans>
Test class
You can use the following code in order to test the insertion and update
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(12);
emp.setAge(35);
dao.update(emp);
// For delete
dao.deleteEmpById(10);
}
}
Using automatic configuration with @Repository annotation
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 example program using automatic configuration to know more about automatic configuration.
In that case your EmployeeDAOImpl will look like this -
@Repository
public class EmployeeDAOImpl implements EmployeeDAO {
@Autowired
private JdbcTemplate jdbcTemplate;
final String INSERT_QUERY = "insert into employee (name, age) values (?, ?)";
final String UPDATE_QUERY = "update employee set age = ? where id = ?";
final String DELETE_QUERY = "delete from employee where id = ?";
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
public int save(Employee employee) {
return jdbcTemplate.update(INSERT_QUERY, employee.getEmpName(),
employee.getAge());
}
public void update(Employee employee) {
int status = jdbcTemplate.update(UPDATE_QUERY, employee.getAge(),
employee.getEmpId());
if(status != 0){
System.out.println("Employee data updated for ID " + employee.getEmpId());
}else{
System.out.println("No Employee found with ID " + employee.getEmpId());
}
}
public void deleteEmpById(int empId) {
int status = jdbcTemplate.update(DELETE_QUERY, empId);
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.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd">
<context:component-scan base-package="org.netjs.daoimpl" />
<context:property-placeholder location="classpath:config/db.properties" />
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
<!-- <bean id="employeeDAO" class="org.netjs.daoimpl.EmployeeDAOImpl">
<property name="jdbcTemplate" ref="jdbcTemplate"></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 JdbcTemplate 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-
