JDBC in Spring Framework

Back to: Spring Framework Tutorials

JDBC in Spring Framework with Examples

In this article, I am going to discuss JDBC in Spring Framework with Examples. Please read our previous article, where we discussed Spring Framework AspectJ-Based AOP.

What is JDBC?

JDBC (Java Database Connectivity) is a Java API that provides a set of classes and interfaces for connecting to and interacting with relational databases. It allows Java applications to execute SQL queries, retrieve results, and perform database operations. JDBC is an essential part of the Java platform and is widely used in enterprise applications for database connectivity.

Spring is a popular open-source framework for building Java applications. It provides a wide range of features and utilities to simplify application development. In the context of Spring, JDBC is often used as the underlying technology for database access.

The Spring JDBC module builds on top of the JDBC API, providing additional abstractions and features to make database access even more convenient. It offers a higher level of abstraction compared to plain JDBC, reducing boilerplate code and providing better error handling.

One of the key features of Spring JDBC is the use of the JdbcTemplate class. The JdbcTemplate simplifies database operations by encapsulating common tasks such as opening and closing database connections, executing SQL statements, and handling exceptions. It provides methods for executing queries, updating data, and accessing result sets.

The JdbcTemplate class also supports parameterized queries, which help prevent SQL injection attacks and improve performance by allowing the database to reuse query execution plans. It provides methods for setting query parameters and retrieving query results in a type-safe manner.

In addition to the JdbcTemplate, Spring JDBC offers several other useful classes and interfaces. The NamedParameterJdbcTemplate allows named parameters to be used in SQL statements, making the code more readable and reducing the dependency on the parameter position. The SimpleJdbcTemplate provides a simplified API for common database operations, while the MappingSqlQuery and SqlUpdate classes provide advanced features for result set mapping and batch updates, respectively.

Another important feature of Spring JDBC is its support for transaction management. Spring’s transaction management capabilities allow you to define declarative transaction boundaries using annotations or XML configuration. With Spring JDBC, you can easily manage database transactions without having to deal with low-level JDBC transaction APIs.

The Spring JDBC module integrates well with other Spring modules and technologies. For example, it seamlessly integrates with Spring’s core container, enabling easy dependency injection of JdbcTemplate instances. It also works well with Spring’s Object-Relational Mapping (ORM) framework, such as Hibernate or JPA, allowing you to combine the benefits of JDBC and ORM in your application.

In summary, JDBC in Spring provides a convenient and powerful way to access relational databases in Java applications. It simplifies database operations, improves code readability, and enhances error handling. By leveraging the features and abstractions provided by Spring JDBC, developers can focus more on application logic and less on low-level database connectivity details.

Setting up MySQL

Before we use JDBC, we will need to have a database server. We will use MySQL for this example.

Step 1: Download, install, and set up MySQL. You may use MySQL Workbench or MySQL shell. Here we will use MySQL shell:

Step 2: Create a database using the CREATE DATABASE command:

Check that the database has been created:

Step 3: Set the newly created springbootdb database to the current database:

Step 4: Create a table called User:

Implementing JDBC in Spring Framework

Before starting the project, ensure that the required JAR files are in the project’s “Referenced Libraries” folder.

Apart from these JAR files, another JAR file is required for JDBC. This JAR file is the connector to the MySQL server. It can be found at https://dev.mysql.com/downloads/connector/j/. Select “Platform Independent” as the operating system. Download the archive file (as ZIP or TAR). Extract it (to obtain a JAR file) and paste the JAR into the “Referenced Libraries” directory.

Step 1: Create a new file called Employee.java in the src/ directory. Add the following contents to the file:

public class Employee
{
    private int id, age;
    private String name;
    
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }

    public Employee() {
    }

    public Employee(int id, String name, int age) {
        this.id = id;
        this.age = age;
        this.name = name;
    }

    @Override
    public String toString() {
        return "Employee [id=" + id + ", age=" + age + ", name=" + name + "]";
    }
}

This class is a POJO class that represents an employee. Note that the setters, getters, constructors, and toString can be added using the “Source Action …” option in the right-click menu of VS Code.

Step 2: Create a new file called EmployeeMapper.java in the src/ directory. Add the following contents to the file:

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

import org.springframework.jdbc.core.RowMapper;

public class EmployeeMapper implements RowMapper<Employee>
{
    @Override
    public Employee mapRow(ResultSet arg0, int arg1)
    throws SQLException
    {
        return new Employee(
            arg0.getInt("id"),
            arg0.getString("name"),
            arg0.getInt("age"));
    }
}

This class implements the RowMapper interface. This is responsible for creating a new object of type Employee from the SQL result.

Step 3: Create a new file called EmployeeDAO.java in the src/ directory. Add the following contents to the file:

import java.util.List;

import javax.sql.DataSource;

public interface EmployeeDAO
{
    public void setDataSource(DataSource ds);
    public void create (String name, int age);
    public Employee getEmployee (int id);
    public List<Employee> getAllEmployees ();
    public void delete (int id);
    public void update (Employee e);
}

This interface is responsible for defining functions that represent the CRUD operations for the database. These functions will be implemented in another file.

Step 4: Create a new file called EmployeeJDBC.java in the src/ directory. Add the following contents to the file:

import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;

public class EmployeeJDBC implements EmployeeDAO
{
    private DataSource dataSource;
    private JdbcTemplate jdbcTemplate;

    @Override
    public void setDataSource(DataSource ds)
    {
        this.dataSource = ds;
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    @Override
    public void create(String name, int age)
    {
        String SQL = "INSERT INTO Employee (name, age) VALUES (?,?)";
        jdbcTemplate.update(SQL, name, age);
    }

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

    @Override
    public List<Employee> getAllEmployees()
    {
        String SQL = "SELECT * FROM Employee";
        return jdbcTemplate.query(SQL, new EmployeeMapper());
    }

    @Override
    public void delete(int id)
    {
        String SQL = "DELETE FROM Employee WHERE id = ?";
        jdbcTemplate.update(SQL, id);
        System.out.println("Deleted Record with ID = " + id );
    }

    @Override
    public void update(Employee e)
    {
        
        String SQL = "UPDATE Employee SET name = ?, age = ? WHERE id = ?";
        jdbcTemplate.update(SQL, e.getName(), e.getAge(), e.getId());
        System.out.println("Updated Record with ID = " + e.getId());
    }
}

In this file, we have implemented the functions defined in the EmployeeDAO interface.

Step 5: In the src/there must be a file called App.java. This file contains the main function and was created when the project was created (by VS Code). Modify the file as follows:

import java.util.List;

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

public class App
{
    public static void main(String[] args) 
    {
        ApplicationContext context = new ClassPathXmlApplicationContext("Beans.xml");

        EmployeeJDBC jdbc = (EmployeeJDBC) context.getBean("jdbc");

        System.out.println("Adding data ...");
        jdbc.create("Employee A", 25);
        jdbc.create("Employee B", 30);
        jdbc.create("Intern", 20);
        System.out.println();

        System.out.println("Printing data ...");
        List<Employee> employees = jdbc.getAllEmployees();
        for (Employee e : employees)
            System.out.println(e.toString());
        System.out.println();

        System.out.println("Updating data of last employee...");
        int id = employees.get(employees.size() - 1).getId();
        jdbc.update(new Employee(id, "Employee C", 40));
        System.out.println();

        System.out.println("Printing data ...");
        employees = jdbc.getAllEmployees();
        for (Employee e : employees)
            System.out.println(e.toString());
        System.out.println();

    }
}

The main function creates an EmployeeJDBC bean from the Beans.xml file. Then, we perform some CRUD operations on the object.

Step 6: Create a new file called Beans.xml in the src/ directory. Add the following contents to the file:

<?xml version = "1.0" encoding = "UTF-8"?>
<beans xmlns = "http://www.springframework.org/schema/beans"
   xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance" 
   xsi:schemaLocation = "http://www.springframework.org/schema/beans
   http://www.springframework.org/schema/beans/spring-beans-3.0.xsd ">

   <!-- Initialization for data source -->
   <bean id="dataSource" class = "org.springframework.jdbc.datasource.DriverManagerDataSource">
      <property name = "driverClassName" value = "com.mysql.cj.jdbc.Driver" /> 
      <property name = "url" value = "jdbc:mysql://localhost:3306/springframeworkdb"/>
      <property name = "username" value = "root"/>
      <property name = "password" value = "DotNet23"/>
   </bean>

   <!-- Definition for studentJDBCTemplate bean -->
   <bean id = "jdbc" class = "EmployeeJDBC">
      <property name = "dataSource" ref = "dataSource"/>    
   </bean>
</beans>

This file is responsible for defining the data source (which is the MySQL server) and injecting it into the EmployeeJDBC object.

Step 5: Compile and execute the application. Ensure compilation is successful. Ensure that the output is as expected:

As can be seen, all the required SQL operations (CRUD) execute.

Congratulations! You have completed the JDBC application in Spring Framework!

In the next article, I am going to discuss Spring Framework Transaction Management. Here, in this article, I try to explain Spring Framework JDBC with Examples. I hope you enjoy this Spring Framework JDBC with Examples article.