Skip to content
Related Articles

Related Articles

Spring – JDBC Template

Improve Article
Save Article
  • Last Updated : 07 Apr, 2022
Improve Article
Save Article

Java Database Connectivity (JDBC) is an application programming interface (API) that defines how a client may access a database. It is a data access technology used for Java database connectivity. It provides methods to query and update data in a database and is oriented toward relational databases. JDBC offers a natural Java interface for working with SQL. JDBC is needed to provide a “pure Java” solution for application development. JDBC API uses JDBC drivers to connect with the database. 

There are 4 Types of JDBC Drivers:

  1. JDBC-ODBC Bridge Driver
  2. Native API Driver (partially java driver)
  3. Network Protocol Driver (fully java driver)
  4. Thin Driver (fully java driver)

Java Database Connectivity

The advantages of JDBC API is as follows:  

  1. Automatically creates the XML format of data from the database.
  2. It supports query and stored procedures.
  3. Almost any database for which ODBC driver is installed can be accessed.

The disadvantages of JDBC API is as follows: 

  1. Writing a lot of codes before and after executing the query, such as creating connection, creating a statement, closing result-set, closing connection, etc.
  2. Writing exception handling code on the database logic.
  3. Repetition of these codes from one to another database logic is time-consuming.

These problems of JDBC API are eliminated by Spring JDBC-Template. It provides methods to write the queries directly that saves a lot of time and effort.

Data Access using JDBC Template

There are a number of options for selecting an approach to form the basis for your JDBC database access. Spring framework provides the following approaches for JDBC database access:

  • JdbcTemplate
  • NamedParameterJdbcTemplate
  • SimpleJdbcTemplate
  • SimpleJdbcInsert and SimpleJdbcCall

JDBC Template

JdbcTemplate is a central class in the JDBC core package that simplifies the use of JDBC and helps to avoid common errors. It internally uses JDBC API and eliminates a lot of problems with JDBC API. It executes SQL queries or updates, initiating iteration over ResultSets and catching JDBC exceptions and translating them to the generic. It executes core JDBC workflow, leaving application code to provide SQL and extract results. It handles the exception and provides the informative exception messages with the help of exception classes defined in the org.springframework.dao package.

The common methods of spring JdbcTemplate class.

Methods Description
public int update(String query) Used to insert, update and delete records.
public int update(String query, Object… args) Used to insert, update and delete records using PreparedStatement using given arguments.
public T execute(String sql, PreparedStatementCallback action) Executes the query by using PreparedStatementCallback.
 public void execute(String query) Used to execute DDL query.
public T query(String sql, ResultSetExtractor result) Used to fetch records using ResultSetExtractor.

 JDBC Template Queries

Basic query to count students stored in the database using JdbcTemplate.

int result = jdbcTemplate.queryForObject(
"SELECT COUNT(*) FROM STUDENT", Integer.class);

And here’s a simple INSERT:

public int addStudent(int id) 
{
  return jdbcTemplate.update("INSERT INTO STUDENT VALUES (?, ?, ?)", id, "megan", "India");
}

The standard syntax of providing parameters is using the “?” character.

Implementation: Spring JdbcTemplate

We start with some simple configurations of the data source. We’ll use a MySQL database

Example:

Java




/*package whatever //do not write package name here */
@Configuration
@ComponentScan("com.exploit.jdbc")
public class SpringJdbcConfig {
    @Bean public DataSource mysqlDataSource()
    {
        DriverManagerDataSource dataSource
            = new DriverManagerDataSource();
        dataSource.setDriverClassName(
            "com.mysql.jdbc.Driver");
        dataSource.setUrl(
            "jdbc:mysql://localhost:8800/springjdbc");
        dataSource.setUsername("user");
        dataSource.setPassword("password");
 
        return dataSource;
    }
}


A. File: Student.java

Java




// Java Program to Illustrate Student Class
 
package com.exploit.org;
 
// Class
public class Student {
 
    // Class data members
    private Integer age;
    private String name;
    private Integer id;
 
    // Constructor
    public Student() {}
 
    // Setters and Getters
    public void setAge(Integer age) { this.age = age; }
    public Integer getAge() { return age; }
    public void setName(String name) { this.name = name; }
    public String getName() { return name; }
    public void setId(Integer id) { this.id = id; }
    public Integer getId() { return id; }
}


B. File: StudentDAO.java

Below is the implementation of the Data Access Object interface file StudentDAO.java.

Example:

Java




// Java Program to Illustrate StudentDAO Class
 
package com.exploit.org;
 
// importing required classes
import java.util.List;
import javax.sql.DataSource;
 
// Class
public interface StudentDAO {
 
    // Used to initialize database resources
    // ie. connection
    public void setDataSource(DataSource ds);
 
    // Used to list down all the records
    // from the Student table
    public List<Student> listStudents();
}


C. File: Maven Dependency

Dependency is used in the pom.xml file.

Example:

XML




<dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
 
<dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <scope>runtime</scope>
</dependency>


D. File: StudentJDBCTemplate.java

Below is the implementation class file StudentJDBCTemplate.java for the defined DAO interface StudentDAO.

Example:

Java




// Java Program Illustrating Implementation
// of StudentDAO Class
 
package com.exploit.org;
 
// Importing required classes
import java.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
 
// Class
// Implementing StudentDAO Class
public class StudentJDBCTemp implements StudentDAO {
 
    // Class data members
    private DataSource dataSource;
    private JdbcTemplate jdbcTemplateObject;
 
    // Method 1
    public void setDataSource(DataSource dataSource)
    {
        // This keyword refers to current instance itself
        this.dataSource = dataSource;
        this.jdbcTemplateObject
            = new JdbcTemplate(dataSource);
    }
 
    // Method 2
    public List<Student> listStudents()
    {
        // Custom SQL query
        String SQL = "select * from Student";
        List<Student> students = jdbcTemplateObject.query(
            SQL, new StudentMapper());
 
        return students;
    }
}



My Personal Notes arrow_drop_up
Related Articles

Start Your Coding Journey Now!