Advantages of Spring JdbcTemplate over JDBC API

Advantages of Spring JdbcTemplate over JDBC API

       Spring JdbcTemplate is a powerful mechanism to connect to the database and execute SQL queries. It internally uses JDBC API, but eliminate the lot of problems of JDBC API.

Problems While using JDBC API :- 

The common problems while using JDBC API are as below,

1) We need to write a lot of code before and after executing the query such as creating connection,
     statement and resultset and closing the resultset, statement and connection etc.

2) We need to perform exception handling code on the database logic.

3) We need to handle transaction.

4) Repetition of these codes i.e connection, statement and resultset codes for every transaction, so it's
    time consuming task.

Advantages of Spring JdbcTemplate : 

        Spring provides simplification in handling database access with the Spring JdbcTemplate which is in org.springframework.jdbc.core package.

  1) The Spring JdbcTemplate allows to clean-up the resources automatically, no need to write the
       extra code .

  2) The Spring JdbcTemplate converts the standard JDBC SqlExceptions into RuntimeExceptions.
      This allows the programmer to react more flexible to the errors.  And it also converts the vendor
      specific error messages to the better understandable error messages.

  3) The Spring JdbcTemplate offers several ways to query the database e.g queryForList() returns a
     list of HashMaps.  key is the column name of database and value is the actual column data.

  4) More convenient is the usage of ResultSetExtractor or RowMapper which allows to translates
       the SQL result direct into an Object or a list of Objects .

Example of JdbcTemplate :- 

Create one simple table employee, having id, name and salary are columns.

    create table employee( 
       id integer(10), 
       name character varying(100), 
       salary integer(10) 
    ); 

The Employee class have properties id, name and salary with setters and getters. it's POJO class.

    package com.adnblog; 
     
    public class Employee { 
         private int id; 
         private String name; 
         private int salary; 
    //no-arg and parameterized constructors 
    //getters and setters 
    } 

The EmployeeDao class is having JdbcTemplate property and some methods having database operations.

package com.adnblog; 
import org.springframework.jdbc.core.JdbcTemplate; 
 
public class EmployeeDao { 
     
      private JdbcTemplate jdbcTemplate; 
 
      public void setJdbcTemplate(JdbcTemplate jdbcTemplate) { 
            this.jdbcTemplate = jdbcTemplate; 
      } 
 
      public JdbcTemplate getJdbcTemplate() {
            return jdbcTemplate;
      }
      public int saveEmployee(int id, String name, int salary){ 
            String query="INSERT INTO employee VALUES(?, ?, ?)"; 
            return jdbcTemplate.update(query, id, salary); 
      } 
   
      public int updateEmployee(int id, String name, int salary){ 
            String query="UPDATE employee
              SET name = ?, salary = ? WHERE id = ?"; 
            return jdbcTemplate.update(query, name, salary, id); 
      } 

      public List<Employee> findAll(){

           String sql = "SELECT * FROM employee";

           List<Employee> employeeList = new ArrayList<Employee>();

           List<Map> rows = getJdbcTemplate().queryForList(sql);
           for (Map row : rows) {
               Employee emp = new Employee();
               emp.setId((Integer)(row.get("ID")));
               emp.setName((String)row.get("NAME"));
               emp.setSalary((Integer)row.get("SALARY"));
               employeeList.add(emp);
           }
           return employeeList;
     }
}

        Another one interface ResultSetExtractor is interface used to fetch data from the database, it will take resultset data as input and will return in the form of list.