Spring Data JDBC Tutorial with Examples

A practical Introduction to Spring Data JDBC project. We will write our own Spring Data JDBC Example, while understanding its benefits.

What is Spring Data JDBC

The Spring Data JDBC project belongs to Spring Data family and provides abstractions for the JDBC based Data Access Layer. It provides easy to use Object Relational Mapping (ORM) framework to work with databases. That means, Spring Data JDBC supports using entity objects and repositories. However, it reduces a lot of complexities which are introduced by JPA backed Spring data JPA.

In order to keep the data access layer as simple as possible, it omits some of the JPA features like Lazy Loading, caching of entities etc. Thus, it can be effortlessly used where we intend to perform pure JDBC operations on the entities.

Like any other Spring projects, Spring Data JDBC can be enabled into Spring Boot by adding its own starter dependency.

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>Code language: HTML, XML (xml)

Or, if you are working with a Spring Application you can add its dependency like next. Just make sure, that you use the latest version.

<dependency>
    <groupId>org.springframework.data</groupId>
    <artifactId>spring-data-jdbc</artifactId>
    <version>{version}</version>
</dependency>Code language: HTML, XML (xml)

As it is not a Hibernate Backed JPA Implementation, there are no entities in Spring Data JDBC. However, we can designate any Plain Old Java Object (POJO) as an entity and use it with repositories. In the next section we will see how to do that.

Spring Data JDBC Example

This section covers an example of Spring Data JDBC. We will create a Java POJO to map it to a table and write a simple CRUD Repository interface.

Create Database Table

Unlike the Hibernate and JPA combination, Spring Data JDBC doesn’t generate database tables automatically. Hence, we need to create them manually or use data.sql file or liquibase to generate the database schemas.

Next is the command to create a Student table for our example.

create table student
(
    student_id bigint auto_increment,
    first_name varchar(20) null,
    last_name  varchar(20) null,
    year       int         null,
    constraint student_id
        unique (student_id)
);

alter table student
    add primary key (student_id);Code language: SQL (Structured Query Language) (sql)

Use POJO as Entity

As stated above, Spring Data JDBC can map any POJO to a database table, if,

• Name of the POJO is same as that of table. Otherwise, it uses @Table annotation to refer to the actual table name.
• The POJO has a primary key and that is annotated as @Id.
• All the persistable fields in the POJO have same naming as that of database table columns. Else, we can use @Column annotation to provide column name.

For the table we created, we will create a Students java bean and use @Id on the primary key.

package com.amitph.spring.tutorials.springdatajdbc.repo;

import lombok.Data;
import org.springframework.data.annotation.Id;
import org.springframework.data.relational.core.mapping.Table;

@Data
@Table("student")
public class Student {
    @Id
    private Long studentId;
    private String firstName;
    private String lastName;
    private Integer year;
}Code language: Java (java)

As the name of POJO and the database table is same, we can omit the @Table annotation, which is added for demonstration purpose. We using Lombok @Data annotation to generate default getters and setters. However access methods and parameterised constructors are not mandatory for a POJO to map to database table.

Write Repository Interface

Spring Data JDBC framework supports repositories just like Spring Data JPA. However, there are a few differences which we will discuss soon. Also, it supports Query Methods and @Query annotations.

We will create a repository interface for students, which is a sub interface of CrudRepository.

package com.amitph.spring.tutorials.springdatajdbc.repo;

import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface StudentRepository extends CrudRepository<Student, Long> {
    List<Student> findByLastName(String lastName);
}Code language: Java (java)

Spring Data provides implementations for the query methods on the fly. Also, we can use PagingAndSortingRepository, if we want paginated or sorted results.

Named Queries with Spring Data JDBC

In order to generate custom queries we can derive repository query methods. However, we can also write native queries if we wish to.

One thing to note there is that, unlike JPA, Spring Data JDBC does not support JPQL queries. Thus, we need to write database native SQL statements with @Query annotation.

Next is an example of using Named Query with Spring Data JDBC.

@Query("select * from student where last_name = :lName")
List<Student> findByLastName(@Param("lName") String lastName);Code language: Java (java)

As shown in the example we can use @Param annotation to pass parameters to the query.

@Id Generation in Spring Data JDBC

The @Id field represents primary key of the entity and it is mandatory. When the primary column of the table is auto incrementing the next values in the sequence is used for inserting new records.

Thus, when we pass a POJO instance to repository.save() method and if

• @Id field in POJO is null – The POJO is inserted as a new record in table with next auto incrementing value.
• @Id field in POJO is not null – the operation is considered to be an UPDATE and it throws exception if the given primary key is not find in existing records.

Hence, by default we cannot insert a record using a custom or predefined primary key. However, there is a workaround that we are going to see next.

Insert Record with Custom ID

In order to insert a new record having a custom or predefine ID in Spring Data JDBC, we can implement Persistable interface, which forces you to implement isNew() method. Based on the boolean value returned by this method the record will be considered as new or an update.

package com.amitph.spring.tutorials.springdatajdbc.repo;

import com.fasterxml.jackson.annotation.JsonIgnore;
import lombok.Data;
import org.springframework.data.annotation.Id;
import org.springframework.data.annotation.Transient;
import org.springframework.data.domain.Persistable;
import org.springframework.data.relational.core.mapping.Table;

@Data
@Table("student")
public class Student implements Persistable<Long> {
    @Id
    private Long studentId;
    private String firstName;
    private String lastName;
    private Integer year;

    @Transient
    @JsonIgnore
    private Boolean isInsert;

    @Override
    @JsonIgnore
    public Long getId() {
        return studentId;
    }

    @Override
    @JsonIgnore
    public boolean isNew() {
        return isInsert;
    }
}Code language: Java (java)

Note that we have marked the new boolean flag with @Transient. Thus, this field will not be persisted in the database table.

Now, we can Insert a new record with Custom Id

Student student = new Student();
student.setFirstName("fName");
student.setFirstName("lName");
student.setFirstName("2023");
        
student.setStudentId(123L);
student.setIsInsert(true);
repository.save(student);Code language: Java (java)

Note that we are setting the transient field with value of true. Hence, the record will be considered new and it will be inserted, provided the Id value doesn’t exist already.

Similarly, we can update an existing record by providing custom Id.

student.setStudentId(123L);
student.setIsInsert(false);
repository.save(student);Code language: Java (java)

Summary

This tutorial provided a detailed Introduction to Spring Data JDBC which is a JDBC based Object Relational Mapping (ORM) framework. The framework aims at providing a easy to use, lightweight data access layer by reducing most of the complex features provided by JPA.

We learned that we can use any Java POJO to map into a database table and use Repositories and query methods to let Spring Data generate the low level queries. Also, we have covered practical examples to understand the concepts.

For more on Spring and Spring Boot, please visit Spring Tutorials. For the full source code of the examples used access our Github repository.

---