How about dynamic queries with Spring Data Jpa?

Sometimes we need to combine some values and have a dynamic query instead of a static one.

One way of having this is to use the Example API of Spring Data JPA and in this text, I will give an example.

At first, you must create a Spring project and use this dependency:

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

In this case, I used MySQL and you also need this:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

Now, let’s create an entity:

@Entity
@Table(name = "entity_example")
public class EntityExample {

    @Id
    @Column(name = "id", length = 36)
    private String id;
    @NotNull
    @Column(name = "name")
    private String name;                            
    @Column(name = "type", length = 9)
    private String type;                            
    @Column(name = "value", length = 77)
    private String value;                           
    @Column(name = "account_type", length = 10)
    private String accountType;                     
    @Column(name = "agency_number", length = 4)
    private String agencyNumber;                    
    @Column(name = "account_number", length = 8)
    private String accountNumber;                   
    @Column(name = "account_holder_first_name")
    private String accountHolderName;               
    @Column(name = "account_holder_last_name", length = 45)
    private String accountHolderLastName;           
    @Column(name = "date_time_inclusion")
    private LocalDateTime dateTimeInclusion;
    @Column(name = "date_time_update")
    private LocalDateTime dateTimeUpdate;
    @Column(name = "active")
    private Integer active;
    //omitted
}

A builder for this class will help a lot, believe me:

public class EntityExampleBuilder {

    private EntityExample entityExample;

    public EntityExampleBuilder() {
        this.entityExample = new EntityExample();
    }

    public static EntityExampleBuilder builder() {
        return new EntityExampleBuilder();
    }

    publicEntityExampleBuilder addName(final String nome) {
        this.entityExample.setName(nome);
        return this;
    }

    public EntityExampleBuilder addType(final String type) {
        this.entityExample.setType(type);
        return this;
    }

    public EntityExampleBuilder addValue(final String value) {
        this.entityExample.setValue(value);
        return this;
    }

    public EntityExampleBuilder addAccountType(final String accountType) {
        this.entityExample.setAccountType(accountType);
        return this;
    }

    public EntityExampleBuilder addAgencyNumber(final String agencyNumber) {
        this.entityExample.setAgencyNumber(agencyNumber);
        return this;
    }

    public EntityExampleBuilder addAccountNumber(final String accountNumber) {
        this.entityExample.setAccountNumber(accountNumber);
        return this;
    }

    public EntityExampleBuilder addAccountHolderName(final String accountHolderName) {
        this.entityExample.setAccountHolderName(accountHolderName);
        return this;
    }

    public EntityExampleBuilder addAccountHolderLastName(final String accountHolderLastName) {
        this.entityExample.setAccountHolderLastName(accountHolderLastName);
        return this;
    }

    public EntityExampleBuilder addDateTimeInclusion(final LocalDateTime dateTimeInclusion) {
        this.entityExample.setDateTimeInclusion(dateTimeInclusion);
        return this;
    }

    public EntityExampleBuilder addDateTimeUpdate(final LocalDateTime dateTimeUpdate) {
        this.entityExample.setDateTimeUpdate(dateTimeUpdate);
        return this;
    }

    public EntityExampleBuilder addActive(final Integer active) {
        this.entityExample.setActive(active);
        return this;
    }

    public EntityExample build() {
        return this.entityExample;
    }

}

Now it’s time to create a repository:

@Repository
public interface EntityExampleRepository extends JpaRepository<EntityExample, String> {
    boolean existsByValue(final String value);
    Optional<EntityExample> findByValue(final String value);
    List<EntityExample> findByName(final String accountHolderName);
}

Note that you can create methods just by adding the property, like in the examples above.

In our case, we want to use the findAll(Example) method. This is an existent method from Spring Data JPA take a look at this example:

@Override
public List<EntityExample> findByCombinedFilter(
        final String type,
        final String accountHolderName,
        final String accountHolderLastName,
        final String agencyNumber,
        final String accountNumber) {

    final EntityExample entityExample = EntityExampleBuilder
            .builder()
            .addType(type)
            .addAccountHolderName(accountHolderName)
            .addAccountHolderLastName(accountHolderLastName)
            .addAgencyNumber(agencyNumber)
            .addAccountNumber(accountNumber)
            .addActive(1)
            .build();
    return entityExampleRepository.findAll(Example.of(entityExample));
}

The Builder was useful to construct the example.

From here you could use this example in services, controllers, etc.

The code below is here.

That’s it for today.