• Java
    • JAXB Tutorial
      • What is JAXB
      • JAXB Marshalling Example
      • JAXB UnMarshalling Example
  • Spring Tutorial
    • Spring Core Tutorial
    • Spring MVC Tutorial
      • Quick Start
        • Flow Diagram
        • Hello World Example
        • Form Handling Example
      • Handler Mapping
        • BeanNameUrlHandlerMapping
        • ControllerClassNameHandlerMapping
        • SimpleUrlHandlerMapping
      • Validation & Exception Handling
        • Validation+Annotations
        • Validation+ResourceBundle
        • @ExceptionHandler
        • @ControllerAdvice
        • Custom Exception Handling
      • Form Tag Library
        • Textbox Example
        • TextArea Example
        • Password Example
        • Dropdown Box Example
        • Checkboxes Example
        • Radiobuttons Example
        • HiddenValue Example
      • Misc
        • Change Config file name
    • Spring Boot Tutorial
  • Hibernate Tutorial
  • REST Tutorial
    • JAX-RS REST @PathParam Example
    • JAX-RS REST @QueryParam Example
    • JAX-RS REST @DefaultValue Example
    • JAX-RS REST @Context Example
    • JAX-RS REST @MatrixParam Example
    • JAX-RS REST @FormParam Example
    • JAX-RS REST @Produces Example
    • JAX-RS REST @Consumes Example
    • JAX-RS REST @Produces both XML and JSON Example
    • JAX-RS REST @Consumes both XML and JSON Example
  • Miscellaneous
    • JSON Parser
      • Read a JSON file
      • Write JSON object to File
      • Read / Write JSON using GSON
      • Java Object to JSON using JAXB
    • CSV Parser
      • Read / Write CSV file
      • Read/Parse/Write CSV File – OpenCSV
      • Export data into a CSV File
      • CsvToBean and BeanToCsv – OpenCSV

JavaInterviewPoint

Java Development Tutorials

Spring CRUD Example with JdbcTemplate + Maven + Oracle

May 9, 2017 by javainterviewpoint Leave a Comment

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.

Other interesting articles which you may like …

  • Spring Bean Scopes Example
  • Autowiring in Spring
  • Spring Autowiring byName Example
  • Spring Autowiring byType Example
  • Spring Autowiring constructor Example
  • How to Instantiate Spring IoC Container
  • How to Create and Configure Beans in the Spring IoC Container
  • Spring Constructor Injection – Resolving Ambiguity
  • How to create Spring Beans Using Spring FactoryBean
  • How to specify Spring Bean Reference and Spring Inner Bean
  • Spring Dependency Checking and Spring @Required Annotation
  • @Autowired, @Resource, @Qualifier, @Inject Annotation
  • Spring Bean Life Cycle – Bean Initialization and Destruction
  • Static Factory Method & Instance Factory Method
  • Spring PropertyPlaceholderConfigurer Example

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

Filed Under: J2EE, Java, Spring, Spring Core, Spring Tutorial Tagged With: Spring CRUD Example

Leave a Reply Cancel reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Java Basics

  • JVM Architecture
  • Object in Java
  • Class in Java
  • How to Set Classpath for Java in Windows
  • Components of JDK
  • Decompiling a class file
  • Use of Class.forName in java
  • Use Class.forName in SQL JDBC

Oops Concepts

  • Inheritance in Java
  • Types of Inheritance in Java
  • Single Inheritance in Java
  • Multiple Inheritance in Java
  • Multilevel Inheritance in Java
  • Hierarchical Inheritance in Java
  • Hybrid Inheritance in Java
  • Polymorphism in Java – Method Overloading and Overriding
  • Types of Polymorphism in java
  • Method Overriding in Java
  • Can we Overload static methods in Java
  • Can we Override static methods in Java
  • Java Constructor Overloading
  • Java Method Overloading Example
  • Encapsulation in Java with Example
  • Constructor in Java
  • Constructor in an Interface?
  • Parameterized Constructor in Java
  • Constructor Chaining with example
  • What is the use of a Private Constructors in Java
  • Interface in Java
  • What is Marker Interface
  • Abstract Class in Java

Java Keywords

  • Java this keyword
  • Java super keyword
  • Final Keyword in Java
  • static Keyword in Java
  • Static Import
  • Transient Keyword

Miscellaneous

  • newInstance() method
  • How does Hashmap works internally in Java
  • Java Ternary operator
  • How System.out.println() really work?
  • Autoboxing and Unboxing Examples
  • Serialization and Deserialization in Java with Example
  • Generate SerialVersionUID in Java
  • How to make a class Immutable in Java
  • Differences betwen HashMap and Hashtable
  • Difference between Enumeration and Iterator ?
  • Difference between fail-fast and fail-safe Iterator
  • Difference Between Interface and Abstract Class in Java
  • Difference between equals() and ==
  • Sort Objects in a ArrayList using Java Comparable Interface
  • Sort Objects in a ArrayList using Java Comparator

Follow

  • Coding Utils

Useful Links

  • Spring 4.1.x Documentation
  • Spring 3.2.x Documentation
  • Spring 2.5.x Documentation
  • Java 6 API
  • Java 7 API
  • Java 8 API
  • Java EE 5 Tutorial
  • Java EE 6 Tutorial
  • Java EE 7 Tutorial
  • Maven Repository
  • Hibernate ORM

About JavaInterviewPoint

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 Facebook | Privacy Policy | Sitemap

Copyright ©2023 · Java Interview Point - All Rights Are Reserved ·