Introduction to the Spring Data JDBC

background
Reading Time: 4 minutes

Introduction

Spring Data JDBC, part of the larger Spring Data family, makes it easy to implement JDBC based repositories.

It 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.

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, it 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.

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 @IdSpring 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
}

We don’t need to use the annotation @Table or @Column in the Person class. The default naming strategy of Spring Data JDBC does all the mappings implicitly between the entity and the table.

Declaring JDBC Repositories

It uses a syntax that is similar to Spring Data JPA. We can create a Spring Data JDBC repository by extending the RepositoryCrudRepository, or PagingAndSortingRepository interface. By implementing CrudRepository, we receive the implementation of the most commonly used methods like savedelete, 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.

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, it supports query methods. That is, if we name our query method including the keywords, for example, findByFirstName, it 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 this, 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 it does not support the referencing of parameters with index numbersWe’re able only to reference parameters by name.

Populating the Database

Finally, we need to populate the database with data that will serve for testing the 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('John', 'Depp')");
        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, It 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.

Conclusion

In this blog, 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 using this 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 databaseSpring Data JDBC doesn’t contain most of the Spring Data magic when querying the database.

Reference Link :- https://spring.io/projects/spring-data-jdbc .

Written by 

KRISHNA JAISWAL is Software Consultant Trainee at Knoldus. He is passionate about JAVA , MYSQL , having knowledge of C , C++ and much more. He is recognised as a good team player, a dedicated and responsible professional, and a technology enthusiast. He is a quick learner & curious to learn new technologies. His hobbies include reading Books , listening Music and playing Cricket .

Leave a Reply