Introduction to Spring Data JDBC

1. Overview

Spring Data JDBC is a persistence framework that is not as complex as Spring Data JPA. It doesn’t provide cache, lazy loading, write-behind, or many other features of JPA. Nevertheless, it has its own ORM and provides most of the features we’re used with Spring Data JPA like mapped entities, repositories, query annotations, and JdbcTemplate.

An important thing to keep in mind is that Spring Data JDBC doesn’t offer schema generation. As a result, we are responsible for explicitly creating the schema.

2. Adding Spring Data JDBC to the Project

Spring Data JDBC is available to Spring Boot applications with the JDBC dependency starter. This dependency starter does not bring the database driver, though. That decision must be taken by the developer. Let’s add the dependency starter for Spring Data JPA:

<dependency> 
    <groupId>org.springframework.boot</groupId> 
    <artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency> 

In this example, we’re using the H2 database. As we mentioned early, Spring Data JDBC doesn’t offer schema generation. In such a case, we can create a custom schema.sql file that will have the SQL DDL commands for creating the schema objects. Automatically, Spring Boot will pick this file and use it for creating database objects.

3. Adding Entities

As with the other Spring Data projects, we use annotations to map POJOs with database tables. In Spring Data JDBC, the entity is required to have an @Id. Spring Data JDBC uses the @Id annotation to identify entities.

Similar to Spring Data JPA, Spring Data JDBC uses, by default, a naming strategy that maps Java entities to relational database tables, and attributes to column names. By default, the Camel Case names of entities and attributes are mapped to snake case names of tables and columns, respectively. For example, a Java entity named AddressBook is mapped to a database table named address_book.

Also, we can map entities and attributes with tables and columns explicitly by using the @Table and @Column annotations. For example, below we have defined the entity that we’re going to use in this example:

public class Person {
    @Id
    private long id;
    private String firstName;
    private String lastName;
    // constructors, getters, setters
}

4. Declaring JDBC Repositories

Spring Data JDBC uses a syntax that is similar to Spring Data JPA. We can create a Spring Data JDBC repository by extending the Repository, CrudRepository, or PagingAndSortingRepository interface. By implementing CrudRepository, we receive the implementation of the most commonly used methods like save, delete, and findById, among others.

Let’s create a JDBC repository that we’re going to use in our example:

@Repository 
public interface PersonRepository extends CrudRepository<Person, Long> {
}

If we need to have pagination and sorting features, the best choice would be to extend the PagingAndSortingRepository interface.

5. Customizing JDBC Repositories

Despite the CrudRepository‘s built-in methods, we need to create our methods for specific cases.

Now, let’s customize our PersonRepository with a non-modifying query and a modifying query:

@Repository
public interface PersonRepository extends CrudRepository<Person, Long> {

    List<Person> findByFirstName(String firstName);

    @Modifying
    @Query("UPDATE person SET first_name = :name WHERE id = :id")
    boolean updateByFirstName(@Param("id") Long id, @Param("name") String name);
}

Since version 2.0, Spring Data JDBC supports query methods. That is, if we name our query method including the keywords, for example, findByFirstName, Spring Data JDBC will generate the query object automatically.

However, for the modifying query, we use the @Modifying annotation to annotate the query method that modifies the entity. Also, we decorate it with the @Query annotation.

Inside the @Query annotation, we add our SQL command. In Spring Data JDBC, we write queries in plain SQL. We don’t use any higher-level query language like JPQL. As a result, the application becomes tightly coupled with the database vendor.

For this reason, it also becomes more difficult to change to a different database.

One thing we need to keep in mind is that Spring Data JDBC does not support the referencing of parameters with index numbers. We’re able only to reference parameters by name.

6. Populating the Database

Finally, we need to populate the database with data that will serve for testing the Spring Data JDBC repository we created above. So, we’re going to create a database seeder that will insert dummy data. Let’s add the implementation of database seeder for this example:

@Component
public class DatabaseSeeder {

    @Autowired
    private JdbcTemplate jdbcTemplate;
    public void insertData() {
        jdbcTemplate.execute("INSERT INTO Person(first_name,last_name) VALUES('Victor', 'Hugo')");
        jdbcTemplate.execute("INSERT INTO Person(first_name,last_name) VALUES('Dante', 'Alighieri')");
        jdbcTemplate.execute("INSERT INTO Person(first_name,last_name) VALUES('Stefan', 'Zweig')");
        jdbcTemplate.execute("INSERT INTO Person(first_name,last_name) VALUES('Oscar', 'Wilde')");
    }
}

As seen above, we’re using Spring JDBC for executing the INSERT statements. In particular, Spring JDBC handles the connection with the database and lets us execute SQL commands using JdbcTemplates. This solution is very flexible because we have complete control over the executed queries.

7. Conclusion

To summarize, Spring Data JDBC offers a solution that is as simple as using Spring JDBC — there is no magic behind it. Nonetheless, it also offers a majority of features that we’re accustomed to using Spring Data JPA.

One of the biggest advantages of Spring Data JDBC is the improved performance when accessing the database as compared to Spring Data JPA. This is due to Spring Data JDBC communicating directly to the database. Spring Data JDBC doesn’t contain most of the Spring Data magic when querying the database.

One of the biggest disadvantages when using Spring Data JDBC is the dependency on the database vendor. If we decide to change the database from MySQL to Oracle, we might have to deal with problems that arise from databases having different dialects.