Querying SQL Databases Declaratively [Video]

Apache Delta Spike is a set of CDI extensions that includes a module to ease JPA usage. This includes the possibility to define repositories and queries declaratively. In this article, I'll explain how to add a query method to a repository interface without having to implement the query itself.

If you prefer, you can watch the video version:

Let's suppose you are working on a Jakarta EE application that has the Data module of Apache DeltaSpike in the classpath (check this article to learn how to configure a project). You probably have an Entity class. For example:

package com.example.app;

import javax.persistence.*;
import java.time.LocalDate;
import java.util.Objects;

@Entity
@Table(name = "users")
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Integer id;

    @Column(name = "email")
    private String email;

    @Column(name = "birth_date")
    private LocalDate birthDate;

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        User user = (User) o;
        return Objects.equals(id, user.id);
    }

    @Override
    public int hashCode() {
        return Objects.hash(id);
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public LocalDate getBirthDate() {
        return birthDate;
    }

    public void setBirthDate(LocalDate birthDate) {
        this.birthDate = birthDate;
    }
}

This Entity class is mapped to a SQL table in MySQL, Oracle, PostgreSQL, or any other database that supports JDBC. You can use JPA to query the database. This would require you to implement queries with, for example, JPQL, and use the API to run the queries and get the results in the form of User objects. The Data module of Apache DeltaSpike allows you to write a Java interface that Apache DeltaSpike implements at runtime. Here's a first iteration of the new UserRepository interface that we need:

package com.example.app;

import org.apache.deltaspike.data.api.EntityRepository;
import org.apache.deltaspike.data.api.Repository;

@Repository
public interface UserRepository extends EntityRepository<User, Integer> {

}

This interface extends EntityRepository which in turn includes several useful and frequently used methods for data manipulation:

But the benefits of using this kind of repository interface don't end there. You can add custom queries to the interface without having to implement them. This works using a name convention. For example, let's add a new method to the UserRepository interface:

package com.example.app;

import org.apache.deltaspike.data.api.EntityRepository;
import org.apache.deltaspike.data.api.Repository;

import java.util.List;

@Repository
public interface UserRepository extends EntityRepository<User, Integer> {

    List<User> findByEmail(String email);

}

Apache DeltaSpike inspects this interface and finds the method. The findBy part in the method's name is a convention, and it means, well, what it says! Find all the users that have the email passed as a parameter. Of course, in the case of an email, there's probably one, but maybe we need a method to find all the users whose email ends in @test.com. We can change the name of the method to achieve this:

package com.example.app;

import org.apache.deltaspike.data.api.EntityRepository;
import org.apache.deltaspike.data.api.Repository;

import java.util.List;

@Repository
public interface UserRepository extends EntityRepository<User, Integer> {

    List<User> findByEmailLike(String email);

}

Now, when you read the name of the method, it should be clear that we want to find the users that have an email like the expression passed as a parameter. The word "like" refers to the SQL operator with the same name. We can use the repository as follows:

List<User> users = userRepository.findByEmailLike("[e-mail]");

The % character represents zero, one, or multiple characters. We can inject a reference of type UserRepository to, say, a backend service class or even to a UI class (you should probably add a service class in the middle, but I'll leave that as an exercise). For example, you can create a web graphical user interface in Java using Vaadin, inject the repository there, and show the results in a grid on the web browser allowing the user to filter by email:

package com.example.app;

import com.vaadin.flow.component.grid.Grid;
import com.vaadin.flow.component.notification.Notification;
import com.vaadin.flow.component.orderedlayout.VerticalLayout;
import com.vaadin.flow.component.textfield.TextField;
import com.vaadin.flow.router.Route;

import javax.inject.Inject;
import java.util.List;

@Route("")
public class MainView extends VerticalLayout {

    @Inject
    public MainView(UserRepository userRepository) {
        Long count = userRepository.count();
        Notification.show("Users: " + count);

        Grid<User> grid = new Grid<>(User.class);
        grid.setColumns("email", "birthDate");
        grid.setItems(userRepository.findAll());

        TextField filter = new TextField();
        filter.setPlaceholder("Filter by email...");
        filter.addValueChangeListener(event -> {
            List<User> users = userRepository.findByEmailLike("%" + filter.getValue() + "%");
            grid.setItems(users);
        });


        add(filter, grid);
    }

}

There are more name conventions in Apache DeltaSpike. For example, we could have a method countByBirthDate(LocalDate birthDate) to return a long with the number of users born on a certain date or removeByEmail(String email) to delete a user given its email. The same happens with the suffixes (likeEmail in the example). We can use EmailNotLike, EmailIgnoreCase, and many more. Check the official documentation for more examples and details on how to use method expressions.