In this Spring CRUD Example, we will build a Simple Spring Application and perform CRUD operations using Spring JdbcTemplate. We will create a simple Employee management application which has abilities to create a new employee, update the existing employee, get a particular employee/ all employee and finally delete the existing employee.
Creating table
Create EMPLOYEE Table, simply Copy and Paste the following SQL query in the query editor to get the table created.
CREATE TABLE "EMPLOYEE"
(
"ID" NUMBER(10) NOT NULL ENABLE,
"AGE" NUMBER(10),
"DEPT" VARCHAR2(255 CHAR),
"NAME" VARCHAR2(255 CHAR),
PRIMARY KEY ("ID")
);
Folder Structure:
- Create a simple Maven Project “SpringJDBC” by selecting maven-archetype-quickstart and create a package for our source files “com.javainterviewpoint” under src/main/java
- Now add the following dependency in the POM.xml
<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.javainterviewpoint</groupId> <artifactId>SpringJDBC</artifactId> <version>0.0.1-SNAPSHOT</version> <packaging>jar</packaging> <name>SpringJDBC</name> <url>http://maven.apache.org</url> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <springframework.version>4.3.7.RELEASE</springframework.version> <oracle.connector.version>11.2.0</oracle.connector.version> </properties> <dependencies> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>3.8.1</version> <scope>test</scope> </dependency> <!-- Spring Dependency--> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-core</artifactId> <version>${springframework.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>${springframework.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>${springframework.version}</version> </dependency> <!-- Oracle Dependency--> <dependency> <groupId>com.oracle</groupId> <artifactId>ojdbc14</artifactId> <version>${oracle.connector.version}</version> </dependency> </dependencies> </project> - Create the Java classes Employee.java,EmployeeDAOImpl.java and SpringJDBCExample.java under com.javainterviewpoint folder.
Spring CRUD Example
Employee.java
Our Employee class is a simple POJO class consisting getters and setters of Employee properties id, name, age, dept.
package com.javainterviewpoint;
import java.io.Serializable;
public class Employee implements Serializable
{
private static final long serialVersionUID = -1280037900360314186L;
private int id;
private String name;
private int age;
private String dept;
public Employee()
{
super();
}
public Employee(int id, String name, int age, String dept)
{
super();
this.id = id;
this.name = name;
this.age = age;
this.dept = dept;
}
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 int getAge()
{
return age;
}
public void setAge(int age)
{
this.age = age;
}
public String getDept()
{
return dept;
}
public void setDept(String dept)
{
this.dept = dept;
}
@Override
public String toString()
{
return "Employee [id=" + id + ", name=" + name + ", age=" + age + ", dept=" + dept + "]";
}
@Override
public int hashCode()
{
final int prime = 31;
int result = 1;
result = prime * result + age;
result = prime * result + ((dept == null) ? 0 : dept.hashCode());
result = prime * result + id;
result = prime * result + ((name == null) ? 0 : name.hashCode());
return result;
}
@Override
public boolean equals(Object obj)
{
if (this == obj)
return true;
if (obj == null)
return false;
if (getClass() != obj.getClass())
return false;
Employee other = (Employee) obj;
if (age != other.age)
return false;
if (dept == null)
{
if (other.dept != null)
return false;
} else if (!dept.equals(other.dept))
return false;
if (id != other.id)
return false;
if (name == null)
{
if (other.name != null)
return false;
} else if (!name.equals(other.name))
return false;
return true;
}
}
SpringConfig.xml
In our configuration file, we have defined the three beans
- DriverManagerDataSource – DriverManagerDataSource contains database related configurations such as driver class name, connection URL, username and password.
- JdbcTemplate – We will be referencing the dataSource id (DriverManagerDataSource ) to the property dataSource of the JdbcTemplate class.
- EmployeeDAOImpl – We will be referencing the jdbcTemplate id to the property jdbcTemplate of the EmployeeDAOImpl class.
<?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:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.0.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd">
<bean id="employeeDAOImpl" class="com.javainterviewpoint.EmployeeDAOImpl">
<property name="jdbcTemplate" ref="jdbcTemplate" />
</bean>
<!-- Database Configurations -->
<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
<property name="url" value="jdbc:oracle:thin:@rsh2:40051:dev" />
<property name="username" value="root" />
<property name="password" value="root" />
</bean>
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"/>
</bean>
</beans>
EmployeeDAO.java
package com.javainterviewpoint;
import java.util.List;
public interface EmployeeDAO
{
public void saveEmployee(Employee employee);
public Employee getEmployeeById(int id);
public void updateEmployee(Employee employee);
public void deleteEmployee(int id);
public List getAllEmployees();
}
EmployeeDAOImpl.java
package com.javainterviewpoint;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
public class EmployeeDAOImpl implements EmployeeDAO
{
private JdbcTemplate jdbcTemplate;
// JdbcTemplate setter
public void setJdbcTemplate(JdbcTemplate jdbcTemplate)
{
this.jdbcTemplate = jdbcTemplate;
}
// Saving a new Employee
public void saveEmployee(Employee employee)
{
String sql = "insert into Employee values(?,?,?,?)";
jdbcTemplate.update(sql, new Object[]
{ employee.getId(), employee.getAge(), employee.getDept(), employee.getName() });
}
// Getting a particular Employee
public Employee getEmployeeById(int id)
{
String sql = "select * from Employee where id=?";
Employee employee = (Employee) jdbcTemplate.queryForObject(sql, new Object[]
{ id }, new RowMapper()
{
@Override
public Employee mapRow(ResultSet rs, int rowNum) throws SQLException
{
Employee employee = new Employee();
employee.setId(rs.getInt(1));
employee.setAge(rs.getInt(2));
employee.setDept(rs.getString(3));
employee.setName(rs.getString(4));
return employee;
}
});
return employee;
}
// Getting all the Employees
public List<Employee> getAllEmployees()
{
String sql = "select * from Employee";
List employeeList = jdbcTemplate.query(sql, new ResultSetExtractor<List<Employee>>()
{
@Override
public List<Employee> extractData(ResultSet rs) throws SQLException, DataAccessException
{
List<Employee> list = new ArrayList<Employee>();
while (rs.next())
{
Employee employee = new Employee();
employee.setId(rs.getInt(1));
employee.setAge(rs.getInt(2));
employee.setDept(rs.getString(3));
employee.setName(rs.getString(4));
list.add(employee);
}
return list;
}
});
return employeeList;
}
// Updating a particular Employee
public void updateEmployee(Employee employee)
{
String sql = "update Employee set age =?, dept=?,name=? where id=?";
jdbcTemplate.update(sql, new Object[]
{ employee.getAge(), employee.getDept(), employee.getName(), employee.getId() });
}
// Deletion of a particular Employee
public void deleteEmployee(int id)
{
String sql = "delete employee where id=?";
jdbcTemplate.update(sql, new Object[]
{ id });
}
}
EmployeeDAOImpl class implements the interface EmployeeDAO and overrides all the unimplemented methods. We have the below methods in our EmployeeDAOImpl class
- setJdbcTemplate() – Through Spring setter injection we will be injecting the jdbcTemplate from the Spring configuration file.
- getAllEmployee() – In order to fetch all the records from the database we just need to pass the SQL and the instance of the ResultSetExtractor to the query() method of jdbcTemplate. ResultSetExtractor interface accepts the ResultSet and returns a Java List. We need to override the extractData() method and map each ResultSet to an Employee object add to a list.
- getEmployeeById() – In order to fetch a particular record we just need to pass the SQL and the instance of the RowMapper to the queryForObject() method of jdbcTemplate. RowMapper interface internally iterates the ResultSet and adds it to the Collection (Map). Hence there is no need for us to iterate the ResultSet as we do in the ResultSetExtractor.
- updateEmployee() – We will be updating the corresponding employee by calling the update() method of the jdbcTemplate passing the SQL and the parameters.
- deleteEmployee() – In order to delete an employee, we need to call the update() method of the jdbcTemplate passing the SQL and the id.
SpringJDBC.java
package com.javainterviewpoint;
import java.util.List;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class SpringJDBCExample
{
public static void main(String[] args)
{
//Reading the configuration
ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("springConfig.xml");
//Get EmployeeDAOImpl bean instance
EmployeeDAOImpl dao = (EmployeeDAOImpl) context.getBean("employeeDAOImpl");
//Create a new Employee object
Employee employee = new Employee();
employee.setId(99);
employee.setName("JavaInterviewPoint");
employee.setDept("Blog");
employee.setAge(99);
//creation of Employee
dao.saveEmployee(employee);
//Get a specific Employee
Employee emp1 = dao.getEmployeeById(99);
System.out.println(" *** Getting a specific Employee Details *** ");
System.out.println("Employee ID ::"+emp1.getId());
System.out.println("Employee Name ::"+emp1.getName());
System.out.println("Employee Age ::"+emp1.getAge());
System.out.println("Deptartment ::"+emp1.getDept());
System.out.println();
//Get all the Employees
List employeeList = dao.getAllEmployees();
System.out.println("*** List of all Employee ***");
for(Employee emp2 : employeeList)
{
System.out.println("Employee ID ::"+emp2.getId());
System.out.println("Employee Name ::"+emp2.getName());
System.out.println("Employee Age ::"+emp2.getAge());
System.out.println("Deptartment ::"+emp2.getDept());
System.out.println(" *********************");
}
System.out.println();
//Update existing employee
employee.setAge(1111);
employee.setName("JavaInterviewPoint11");
employee.setDept("Manufacturing");
dao.updateEmployee(employee);
Employee emp3 = dao.getEmployeeById(99);
System.out.println(" *** Updated Employee Details *** ");
System.out.println("Employee ID ::"+emp3.getId());
System.out.println("Employee Name ::"+emp3.getName());
System.out.println("Employee Age ::"+emp3.getAge());
System.out.println("Deptartment ::"+emp3.getDept());
System.out.println();
//Delete an Employee
dao.deleteEmployee(99);
List newEmployeeList = dao.getAllEmployees();
System.out.println("*** Remaining Employees after Deletion ***");
for(Employee emp4 : newEmployeeList)
{
System.out.println("Employee ID ::"+emp4.getId());
System.out.println("Employee Name ::"+emp4.getName());
System.out.println("Employee Age ::"+emp4.getAge());
System.out.println("Deptartment ::"+emp4.getDept());
}
context.close();
}
}
- ClassPathXmlApplicationContext class reads our Configuration File(SpringConfig.xml)
- We will get our EmployeeDAOImpl Class instance by calling the getBean() method over the context.
- Call the saveEmployee(), getEmployeeById(), getAllEmployees(),updateEmployee() and deleteEmployee() methods over the EmployeeDAOImpl instance which we got above.
Output:
Apr 25, 2017 4:33:13 PM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName INFO: Loaded JDBC driver: oracle.jdbc.driver.OracleDriver *** Getting a specific Employee Details *** Employee ID ::99 Employee Name ::JavaInterviewPoint Employee Age ::99 Deptartment ::Blog *** List of all Employee *** Employee ID ::1 Employee Name ::JIP Employee Age ::12 Deptartment ::IT ********************* Employee ID ::99 Employee Name ::JavaInterviewPoint Employee Age ::99 Deptartment ::Blog ********************* *** Updated Employee Details *** Employee ID ::99 Employee Name ::JavaInterviewPoint11 Employee Age ::1111 Deptartment ::Manufacturing *** Remaining Employees after Deletion *** Employee ID ::1 Employee Name ::JIP Employee Age ::12 Deptartment ::IT
Leave a Reply