In this article, we will see how to interact with the database through JDBC APIs using Spring Framework and JDBC Template step by step using the example.

Jdbc Template is the core API in Spring’s JDBC Framework. This API provides facility for querying data from the database, performing inserts, deletes/updates and can also be used for extracting results. It hides all the low level exception handling and simplifies the access to database.

When you use the JdbcTemplate for your code, you only need to implement callback interfaces, giving them a clearly defined contract. The PreparedStatementCreator callback interface creates a prepared statement given a Connection provided by this class, providing SQL and any necessary parameters. The same is true for the CallableStatementCreator interface, which creates callable statements. The RowCallbackHandler interface extracts values from each row of a ResultSet.

Popular Tutorials

Spring Tutorial

Spring MVC Web Tutorial

Spring Boot Tutorial

Spring JDBC Tutorial

Spring AOP Tutorial

Spring Security Tutorial

The JdbcTemplate can be used within a DAO implementation through direct instantiation with a DataSource reference, or be configured in a Spring IoC container and given to DAOs as a bean reference.

Examples of JdbcTemplate class usage:Let us see how we can perform CRUD (Create, Read, Update and Delete) operation on database tables using SQL and jdbcTemplate object.
Querying for an integer:

String SQL = "select count(*) from Employee";
int rowCount = jdbcTemplateObject.queryForInt( SQL );

Querying for a long:

String SQL = "select count(*) from Employee";
long rowCount = jdbcTemplateObject.queryForLong( SQL );

A simple query using a bind variable:

String SQL = "select salary from Employee where empid = ?";
long salary = jdbcTemplateObject.queryForLong(SQL, new Object[]{10000});

Querying for a String:

String SQL = "select name from Employee where empid = ?";
String name = jdbcTemplateObject.queryForObject(SQL, new Object[]{10000}, String.class);

Querying and returning an object:

String SQL = "select * from Employee where empid = ?";
Employee employee = jdbcTemplateObject.queryForObject(SQL, 
                  new Object[]{10000}, new EmployeeMapper());

public class EmployeeMapper implements RowMapper<Employee> {
   public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
      Employee employee = new Employee();
      employee.setEmpid(rs.getInt("empid"));
      employee.setName(rs.getString("name"));
      employee.setAge(rs.getInt("age"));
      employee.setSalary(rs.getLong("salary"));
      return employee;
   }
}

Querying and returning multiple objects:

String SQL = "select * from Employee";
List<Employee> employee = jdbcTemplateObject.query(SQL,
                         new EmployeeMapper());

public class EmployeeMapper implements RowMapper<Employee> {
   public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
      Employee employee = new Employee();
      employee.setEmpid(rs.getInt("empid"));
      employee.setName(rs.getString("name"));
      employee.setAge(rs.getInt("age"));
      employee.setSalary(rs.getLong("salary"));
      return employee;
   }
}

Inserting a row into the table:

String SQL = "insert into Employee (name, age) values (?, ?)";
jdbcTemplateObject.update( SQL, new Object[]{"Dinesh", 25} );

Updating a row into the table:

String SQL = "update Employee set name = ? where empid = ?";
jdbcTemplateObject.update( SQL, new Object[]{"Dinesh", 10000} );

Deletng a row from the table:

String SQL = "delete Employee where empid = ?";
jdbcTemplateObject.update( SQL, new Object[]{10000} );

Executing DDL Statements: You can use the execute(..) method from jdbcTemplate to execute any SQL statements or DDL statements. Following is an example to use CREATE statement to create a table:

String SQL = "CREATE TABLE Employee( " +
   "EMPID   INT NOT NULL AUTO_INCREMENT, " +
   "NAME VARCHAR(20) NOT NULL, " +
   "AGE  INT NOT NULL, " +
   "SALARY BIGINT NOT NULL, " +
   "PRIMARY KEY (EMPID));"

jdbcTemplateObject.execute( SQL );

Spring JDBC Framework Examples:

Step 1: Create your database as fallows, Here I am using MySQL as database.

Step 2: Create the Java Project and add Spring framework jar library to it.

Step 3: Create Employee class for table 'employee' in the 'DAVDB' database.
Employee.java

package com.dineshonjava.sdnext.domain;

/**
 * @author Dinesh Rajput
 *
 */
public class Employee {
 private int empid;
 private String name;
 private int age;
 private long salary;
 /**
  * @return the empid
  */
 public int getEmpid() {
  return empid;
 }
 /**
  * @param empid the empid to set
  */
 public void setEmpid(int empid) {
  this.empid = empid;
 }
 /**
  * @return the name
  */
 public String getName() {
  return name;
 }
 /**
  * @param name the name to set
  */
 public void setName(String name) {
  this.name = name;
 }
 /**
  * @return the age
  */
 public int getAge() {
  return age;
 }
 /**
  * @param age the age to set
  */
 public void setAge(int age) {
  this.age = age;
 }
 /**
  * @return the salary
  */
 public long getSalary() {
  return salary;
 }
 /**
  * @param salary the salary to set
  */
 public void setSalary(long salary) {
  this.salary = salary;
 }
 public String toString(){
  return "EMPLOYEE{empid- "+this.empid+" name- "+this.name+
    " age- "+this.age+" salary- "+this.salary+"}";
 }
}

Step 4: Create Employee DAO interface for the abstarct functionality with employee table.
EmpDao.java

package com.dineshonjava.sdnext.dao;

import java.util.List;

import com.dineshonjava.sdnext.domain.Employee;

/**
 * @author Dinesh Rajput
 *
 */
public interface EmpDao {
 
 /** 
  * This is the method to be used to create
  * a record in the Employee table.
  */
 void create(String name, Integer age, Long salary);
 /** 
  * This is the method to be used to list down
  * a record from the Employee table corresponding
  * to a passed Employee id.
  */
 Employee getEmployee(Integer empid);
 /** 
  * This is the method to be used to list down
  * all the records from the Employee table.
  */
 List listEmployees();
 /** 
  * This is the method to be used to delete
  * a record from the Employee table corresponding
  * to a passed Employee id.
  */
 void delete(Integer empid);
 /** 
  * This is the method to be used to update
  * a record into the Employee table.
  */
 void update(Integer empid, Integer age);
}

Step 5: Create Employee DAO implementation for the abstarct functionality of EmpDao.
EmployeeDaoImpl.java

package com.dineshonjava.sdnext.dao.impl;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import com.dineshonjava.sdnext.dao.EmpDao;
import com.dineshonjava.sdnext.domain.Employee;
import com.dineshonjava.sdnext.jdbc.utils.EmployeeMapper;

/**
 * @author Dinesh Rajput
 *
 */
@Component
public class EmployeeDaoImpl implements EmpDao {
 @Autowired
 private JdbcTemplate jdbcTemplateObject;
 
 /**
  * @param jdbcTemplateObject the jdbcTemplateObject to set
  */
 public void setJdbcTemplateObject(JdbcTemplate jdbcTemplateObject) {
  this.jdbcTemplateObject = jdbcTemplateObject;
 }

 @Override
 public void create(String name, Integer age, Long salary) {
  String SQL = "INSERT INTO Employee (name, age, salary) VALUES (?, ?, ?)";
  jdbcTemplateObject.update(SQL, new Object[]{name, age, salary} );
     System.out.println("Created Record Name = " + name + " Age = " + age+ " Salary = " + salary);
 }

 @Override
 public Employee getEmployee(Integer empid) {
  String SQL = "SELECT * FROM Employee WHERE empid = ?";
  Employee employee = (Employee) jdbcTemplateObject.queryForObject(SQL, new Object[]{empid}, new EmployeeMapper());
     return employee;
 }

 @Override
 public List listEmployees() {
  String SQL = "SELECT * FROM Employee";
  List employees = (List) jdbcTemplateObject.query(SQL, new EmployeeMapper());
     return employees;
 }

 @Override
 public void delete(Integer empid) {
  String SQL = "DELETE FROM Employee WHERE empid = ?";
  jdbcTemplateObject.update(SQL, new Object[]{empid});
  System.out.println("Deleted Record with EMPID = " + empid );
 }

 @Override
 public void update(Integer empid, Integer age) {
   String SQL = "UPDATE Employee SET age = ? WHERE empid = ?";
   jdbcTemplateObject.update(SQL, new Object[]{age, empid});
   System.out.println("Updated Record with EMPID = " + empid );
 }
}

Step 6: Create EmployeeMapper class which map the row of table the Employee class which implements the org.springframework.jdbc.core.RowMapper interface of the jdbc core API.
EmployeeMapper.java

package com.dineshonjava.sdnext.jdbc.utils;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

import com.dineshonjava.sdnext.domain.Employee;

/**
 * @author Dinesh Rajput
 *
 */
public class EmployeeMapper implements RowMapper {  
 public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {  
  Employee employee = new Employee();  
  employee.setEmpid(rs.getInt("empid"));  
  employee.setName(rs.getString("name"));  
  employee.setAge(rs.getInt("age"));  
  employee.setSalary(rs.getLong("salary"));  
  return employee;  
 }  
}

Step 7:Create the configuration file for the bean configuratin
spring.xml

<beans xmlns:aop="http://www.springframework.org/schema/aop" xmlns:context="http://www.springframework.org/schema/context" xmlns:p="http://www.springframework.org/schema/p" xmlns:security="http://www.springframework.org/schema/security" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://www.springframework.org/schema/beans" xsi:schemalocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd
     http://www.springframework.org/schema/security http://www.springframework.org/schema/security/spring-security-2.0.4.xsd 
        http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.5.xsd 
  http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd">
  
<context:annotation-config></context:annotation-config>
  
<context:component-scan base-package="com.dineshonjava.sdnext.dao.impl">
</context:component-scan>  
<bean class="org.apache.commons.dbcp.BasicDataSource" id="dataSource">
  <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
  <property name="url" value="jdbc:mysql://localhost:3306/DAVDB"></property>
  <property name="username" value="root"></property>
  <property name="password" value="root"></property>
  <property name="initialSize" value="2"></property>
  <property name="maxActive" value="5"></property>
</bean>
<bean class="org.springframework.jdbc.core.JdbcTemplate" id="jdbcTemplateObject">
  <property name="dataSource" ref="dataSource"></property>
</bean>
</beans>

Step 8: Finally we have to create main class for execution this application
EmpMainApp.java

package com.dineshonjava.sdnext.main;

import java.util.List;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.dineshonjava.sdnext.dao.EmpDao;
import com.dineshonjava.sdnext.domain.Employee;

/**
 * @author Dinesh Rajput
 *
 */
public class EmpMainApp {

 /**
  * @param args
  */
 public static void main(String[] args) {
ApplicationContext context = new ClassPathXmlApplicationContext("spring.xml");
EmpDao empDao = (EmpDao) context.getBean("employeeDaoImpl");
  
  System.out.println("------Records Creation--------" );
  empDao.create("Dinesh", 25, 50000l);
  empDao.create("Anamika", 23, 30000l);
  empDao.create("Nimmo", 24, 30020l);
  empDao.create("Adesh", 24, 30011l);
  empDao.create("Vinesh", 22, 20011l);
  
  System.out.println("------Listing Multiple Records--------" );
  List employees = empDao.listEmployees();
  for (Employee employee : employees) {
          System.out.print(employee);
     }
  
  System.out.println("----Updating Record with EMPID = 2 -----" );
  empDao.update(2, 20);
  
  System.out.println("----Listing Record with ID = 2 -----" );
  Employee employee = empDao.getEmployee(2);
  System.out.print(employee);
 }
}

Once you are done with creating source and bean configuration files, let us run the application. If everything is fine with your application, this will print the following message on the console:

Output:
Dec 8, 2012 3:26:02 PM org.springframework.context.support.AbstractApplicationContext prepareRefresh
INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@1027b4d: display name [org.springframework.context.support.ClassPathXmlApplicationContext@1027b4d]; startup date [Sat Dec 08 15:26:02 IST 2012]; root of context hierarchy
Dec 8, 2012 3:26:02 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [spring.xml]
Dec 8, 2012 3:26:03 PM org.springframework.context.support.AbstractApplicationContext obtainFreshBeanFactory
INFO: Bean factory for application context [org.springframework.context.support.ClassPathXmlApplicationContext@1027b4d]: org.springframework.beans.factory.support.DefaultListableBeanFactory@1e152c5
Dec 8, 2012 3:26:03 PM org.springframework.beans.factory.support.DefaultListableBeanFactory preInstantiateSingletons
INFO: Pre-instantiating singletons in org.springframework.beans.factory.support.DefaultListableBeanFactory@1e152c5: defining beans [org.springframework.context.annotation.internalRequiredAnnotationProcessor,org.springframework.context.annotation.internalAutowiredAnnotationProcessor,org.springframework.context.annotation.internalCommonAnnotationProcessor,employeeDaoImpl,dataSource,jdbcTemplateObject]; root of factory hierarchy

------Records Creation--------
Created Record Name = Dinesh Age = 25 Salary = 50000
Created Record Name = Anamika Age = 23 Salary = 30000
Created Record Name = Nimmo Age = 24 Salary = 30020
Created Record Name = Adesh Age = 24 Salary = 30011
Created Record Name = Vinesh Age = 22 Salary = 20011

------Listing Multiple Records--------
EMPLOYEE{empid- 1 name- Dinesh age- 25 salary- 50000}
EMPLOYEE{empid- 2 name- Anamika age- 23 salary- 30000}
EMPLOYEE{empid- 3 name- Nimmo age- 24 salary- 30020}
EMPLOYEE{empid- 4 name- Adesh age- 24 salary- 30011}
EMPLOYEE{empid- 5 name- Vinesh age- 22 salary- 20011}

----Updating Record with EMPID = 2 -----
Updated Record with EMPID = 2

----Listing Record with EMPID = 2 -----
EMPLOYEE{empid- 2 name- Anamika age- 20 salary- 30000}

Result:

There are other approaches to access the database where you will use NamedParameterJdbcTemplate and SimpleJdbcTemplate classes.

For Code please click
https://github.com/DOJ-SoftwareConsultant/SpringJDBCDemo

<<Spring JDBC Framework |index| Implementing RowMapper>>

JdbcTemplate in Spring

Spring JDBC Framework Examples: