Querying SQL Databases Declaratively [Video]
Tags: java, jakartaee, sql, vaadin
Published: 2021-08-27 15:44:14

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:

{% youtube V-jX97FY_hU %}

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](https://dzone.com/articles/connecting-jakarta-ee-applications-to-mysql-databases) to learn how to configure a project). You probably have an `Entity` class. For example:

```Java
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:

```Java
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:

![Available methods](https://dz2cdn1.dzone.com/storage/temp/14959466-inherited-methods.png)

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:

```Java
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:

```Java
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:

```Java
List<User> users = userRepository.findByEmailLike("%@test.com");
```

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](https://vaadin.com/), inject the repository there, and show the results in a grid on the web browser allowing the user to filter by email:

```Java
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](https://deltaspike.apache.org/documentation/data.html#UsingMethodExpressions) for more examples and details on how to use method expressions.