In this post, We will learn about Spring JDBC Annotation Example using a Demo Project.
Spring Framework provides easy integration with JDBC API and provides org.springframework.jdbc.core.JdbcTemplate utility class that we can use to avoid writing boiler-plate code from our database operations logic such as Opening/Closing Connection, ResultSet, PreparedStatement, etc.
Let’s have a look at a Spring JDBC example application and we will understand how the org.springframework.jdbc.core.JdbcTemplate class can help us in writing modular code with ease without worrying about resources are closed properly or not.
pom.xml
We need to add Spring and MySQL dependencies in Maven pom.xml file.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
<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>com.kkhindigyan.org</groupId> <artifactId>SpringJdbcAnnotationExample</artifactId> <version>0.0.1-SNAPSHOT</version> <properties> <org.springframework.version>5.2.13.RELEASE</org.springframework.version> </properties> <dependencies> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>${org.springframework.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>${org.springframework.version}</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.23</version> </dependency> </dependencies> </project> |
Employee.java
Employee model class to store employee data
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
package com.kkhindigyan.org.model; public class Employee { private Integer employeeId; private String employeeName; private String email; private String gender; private Double salary; public Integer getEmployeeId() { return employeeId; } public void setEmployeeId(Integer employeeId) { this.employeeId = employeeId; } public String getEmployeeName() { return employeeName; } public void setEmployeeName(String employeeName) { this.employeeName = employeeName; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public String getGender() { return gender; } public void setGender(String gender) { this.gender = gender; } public Double getSalary() { return salary; } public void setSalary(Double salary) { this.salary = salary; } @Override public String toString() { return "Employee [employeeId=" + employeeId + ", employeeName=" + employeeName + ", email=" + email + ", gender=" + gender + ", salary=" + salary + "]"; } } |
EmployeeService.java
Employee Service Interface
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
package com.kkhindigyan.org.service; import java.util.List; import com.kkhindigyan.org.model.Employee; public interface EmployeeService { public abstract void createEmployee(Employee employee); public abstract Employee getEmployeeById(Integer employeeId); public abstract List<Employee> getAllEmployees(); public abstract void updateEmployeeEmailById(String newEmail,Integer employeeId); public abstract void deleteEmployeeById(Integer employeeId); } |
EmployeeServiceImpl.java
Employee Service Implementation class
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
package com.kkhindigyan.org.service; import java.util.List; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import com.kkhindigyan.org.dao.EmployeeDao; import com.kkhindigyan.org.model.Employee; @Service public class EmployeeServiceImpl implements EmployeeService { @Autowired private EmployeeDao employeeDao; public EmployeeDao getEmployeeDao() { return employeeDao; } public void setEmployeeDao(EmployeeDao employeeDao) { this.employeeDao = employeeDao; } @Override public void createEmployee(Employee employee) { getEmployeeDao().createEmployee(employee); } @Override public Employee getEmployeeById(Integer employeeId) { return getEmployeeDao().fetchEmployeeById(employeeId); } @Override public List<Employee> getAllEmployees() { return getEmployeeDao().fetchAllEmployees(); } @Override public void updateEmployeeEmailById(String newEmail, Integer employeeId) { getEmployeeDao().updateEmployeeEmailById(newEmail, employeeId); } @Override public void deleteEmployeeById(Integer employeeId) { getEmployeeDao().deleteEmployeeById(employeeId); } } |
EmployeeDao.java
Employee DAO(Data Access Object) Interface
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
package com.kkhindigyan.org.dao; import java.util.List; import com.kkhindigyan.org.model.Employee; public interface EmployeeDao { public abstract void createEmployee(Employee employee); public abstract Employee fetchEmployeeById(Integer employeeId); public abstract List<Employee> fetchAllEmployees(); public abstract void updateEmployeeEmailById(String newEmail,Integer employeeId); public abstract void deleteEmployeeById(Integer employeeId); } |
EmployeeDaoImpl.java
Employee DAO(Data Access Object) Interface implementation class to perform CRUD operations with MySQL database.
Spring org.springframework.jdbc.core.JdbcTemplate is the central class in the Spring JDBC core package and it provides many methods to execute SQL queries and It automatically parse ResultSet to get the Object or list of Objects.
This DAO class is annotated with @Repository annotation that It will be autodetected by spring framework through classpath scanning.
The second annotation we have used @Autowired before jdbcTemplate instance variable so that Spring Container will inject jdbcTemplate dependency at runtime by calling setter method of this property
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
package com.kkhindigyan.org.dao; import java.util.List; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Repository; import com.kkhindigyan.org.mapper.EmployeeRowMapper; import com.kkhindigyan.org.model.Employee; @Repository public class EmployeeDaoImpl implements EmployeeDao { @Autowired private JdbcTemplate jdbcTemplate; public JdbcTemplate getJdbcTemplate() { return jdbcTemplate; } public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { this.jdbcTemplate = jdbcTemplate; } @Override public void createEmployee(Employee employee) { String SQL = "INSERT INTO employee_table(employee_name ,email,gender,salary) VALUES (?,?,?,?)"; int update = getJdbcTemplate().update(SQL, employee.getEmployeeName(),employee.getEmail(),employee.getGender(),employee.getSalary()); if(update == 1) { System.out.println("Employee is created.."); } } @Override public Employee fetchEmployeeById(Integer employeeId) { System.out.println("Fetching Employee details from DB with ID = "+employeeId); String SQL = "SELECT * FROM employee_table WHERE employee_id = ?"; return getJdbcTemplate().queryForObject(SQL, new EmployeeRowMapper(), employeeId); } @Override public List<Employee> fetchAllEmployees() { System.out.println("Fetching All Employees details from DB"); String SQL = "SELECT * FROM employee_table"; return getJdbcTemplate().query(SQL, new EmployeeRowMapper()); } @Override public void updateEmployeeEmailById(String newEmail, Integer employeeId) { System.out.println("Updating email whose Id = "+employeeId); String SQL = "UPDATE employee_table set email = ? WHERE employee_id = ?"; int update = getJdbcTemplate().update(SQL, newEmail,employeeId); if(update == 1) { System.out.println("Employee email is updated for ID = "+employeeId); } } @Override public void deleteEmployeeById(Integer employeeId) { String SQL = "DELETE FROM employee_table WHERE employee_id = ?"; int update = getJdbcTemplate().update(SQL,employeeId); if(update == 1) { System.out.println("Employee is deleted with ID = "+employeeId); } } } |
EmployeeRowMapper.java
RowMapper interface allows us to map a row of the relations with the instance of the user-defined class. It usually iterates the ResultSet internally and adds it into the collection. So we don’t need to write a lot of boilerplate code to fetch the records as ResultSetExtractor.
This interface has only one method mapRow that accepts ResultSet instance and int as rowNum the number of the current row
T mapRow(ResultSet rs, int rowNum) throws SQLException;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
package com.kkhindigyan.org.mapper; import java.sql.ResultSet; import java.sql.SQLException; import org.springframework.jdbc.core.RowMapper; import com.kkhindigyan.org.model.Employee; public class EmployeeRowMapper implements RowMapper<Employee> { @Override public Employee mapRow(ResultSet rs, int rowNum) throws SQLException { Employee employee = new Employee(); employee.setEmployeeId(rs.getInt("employee_Id")); employee.setEmployeeName(rs.getString("employee_name")); employee.setEmail(rs.getString("email")); employee.setGender(rs.getString("gender")); employee.setSalary(rs.getDouble("salary")); return employee; } } |
applicationContext.xml
The org.springframework.jdbc.datasource.DriverManagerDataSource class is mainly used to contain information about the database such as driver class name, connection URL, username, and password.
There is a property Spring bean with the name as datasource in the org.springframework.jdbc.core.JdbcTemplate class of org.springframework.jdbc.datasource.DriverManagerDataSource type. So, we need to provide the reference of the DriverManagerDataSource object in the JdbcTemplate class for the data source property.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
<?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:context="http://www.springframework.org/schema/context" xsi:schemaLocation="http://www.springframework.org/schema/beans https://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd"> <context:component-scan base-package="com.kkhindigyan.org.service"></context:component-scan> <context:component-scan base-package="com.kkhindigyan.org.dao"></context:component-scan> <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource"></property> </bean> <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <constructor-arg name="url" value="jdbc:mysql://localhost:3306/test"></constructor-arg> <constructor-arg name="username" value="root"></constructor-arg> <constructor-arg name="password" value="root"></constructor-arg> </bean> </beans> |
DB.sql
Here we are going to use the below database table in the MySQL database.
1 2 3 4 5 6 7 8 |
CREATE TABLE `employee_table` ( `employee_Id` int(11) NOT NULL AUTO_INCREMENT, `employee_name` varchar(50) NOT NULL, `email` varchar(40) NOT NULL, `gender` varchar(20) DEFAULT NULL, `salary` double DEFAULT NULL, PRIMARY KEY (`employee_Id`) ) ; |
ClientTest.java
This ClientTest class gets the bean from the applicationContext.xml file and calls the different methods of EmployeeService to perform CRUD operation with MySQL database.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
package com.kkhindigyan.org.client; import org.springframework.context.support.AbstractApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import com.kkhindigyan.org.model.Employee; import com.kkhindigyan.org.service.EmployeeService; import com.kkhindigyan.org.service.EmployeeServiceImpl; public class ClientTest { public static void main(String[] args) { AbstractApplicationContext ctx = null; try { ctx = new ClassPathXmlApplicationContext("applicationContext.xml"); EmployeeService employeeService = ctx.getBean(EmployeeServiceImpl.class); createEmployee(employeeService); getEmployeeById(employeeService); fetchAllEmployeesInfo(employeeService); employeeService.deleteEmployeeById(2); } catch (Exception e) { e.printStackTrace(); }finally { if(ctx != null) ctx.close(); } } private static void fetchAllEmployeesInfo(EmployeeService employeeService) { employeeService.getAllEmployees().forEach(System.out::println); } private static void getEmployeeById(EmployeeService employeeService) { Employee employee = employeeService.getEmployeeById(1); System.out.println(employee); } private static void createEmployee(EmployeeService employeeService) { Employee employee = getEmployee() ; employeeService.createEmployee(employee); } private static Employee getEmployee() { Employee employee = new Employee(); employee.setEmployeeName("KK"); employee.setGender("Male"); employee.setSalary(95000.00); return employee; } } |
If you run ClientTest.java as Java Application then it will give the below output:
1 2 3 4 5 6 7 |
Employee is created.. Fetching Employee details from DB with ID = 1 Employee [employeeId=1, employeeName=KK, email=kk.m@gmail.com, gender=Male, salary=95000.0] Fetching All Employees details from DB Employee [employeeId=1, employeeName=KK, email=kk.m@gmail.com, gender=Male, salary=95000.0] Updating email whose Id = 1 Employee email is updated for ID = 1 |
MySQL Database Before and After running above Client Program:
Initial MySQL Database Screen Shot After Creating Table
MySQL Database Screen Shot After running ClientTest.java:
That’s all about Spring JDBC Annotation Example
You May Also Like:
Spring BeanFactory Container Example
Spring ApplicationContext Container Example
Annotation-based Configuration in Spring Framework Example
Spring Java-based Configuration Example
Spring Setter Dependency Injection Example
Spring @Autowired Annotation With Setter Injection Example
Spring Constructor based Dependency Injection Example
Spring @Autowired Annotation With Constructor Injection Example
Spring Autowiring byName & byType Example
getBean() overloaded methods in Spring Framework
Spring Inner bean example
Spring Dependency Injection with Factory Method
Spring Framework @Qualifier example
Injecting Collections in Spring Framework Example
Spring Bean Definition Inheritance Example
Spring bean scopes with example
Spring JSR-250 Annotations with Example
Spring BeanPostProcessor Example
Spring JDBC Integration Example
Spring with Jdbc java based configuration example
Spring JDBC NamedParameterJdbcTemplate Example
How to call stored procedures in the Spring Framework?
If you have any feedback or suggestion please feel free to drop in below comment box.