JavaInterviewPoint

Java Development Tutorials

Spring CRUD Example with JdbcTemplate + Maven + Oracle

by Leave a Comment

Want create site? Find Free WordPress Themes and plugins.

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:

Spring CRUD Example

  1. 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 
  2. 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>
  3. 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

  1. DriverManagerDataSource – DriverManagerDataSource contains database related configurations such as driver class name, connection URL, username and password.
  2. JdbcTemplate – We will be referencing the dataSource id (DriverManagerDataSource ) to the property dataSource of the JdbcTemplate class.
  3. 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

   Download Source Code

Did you find apk for android? You can find new Free Android Games and apps.

Related posts:

  1. How to specify Spring Bean Reference and Spring Inner Bean
  2. Spring Data JPA One To One Foreign Key Example
  3. Spring Dependency Injection With List Collection Example

Leave a Reply

Your email address will not be published. Required fields are marked *

Useful Links

javainterviewpoint.com is a tech blog dedicated to all Java/J2EE developers and Web Developers. We publish useful tutorials on Java, J2EE and all latest frameworks.All examples and tutorials posted here are very well tested in our development environment.Connect with us on facebookPrivacy Policy | Contact Us