JPA/Hibernate Custom Queries with optional parameters

Question

I have an Entity

@Entity
@Table(name = "EVENT")
public class Event {

    @Id
    @GeneratedValue(strategy= GenerationType.AUTO)
    private Long id;

    @Column(name = "EVENT", columnDefinition="VARCHAR(100)", nullable = false)
    private String event;

    @Column(name = "DATE", columnDefinition="DATETIME", nullable = false)
    @Convert(converter = InstantConverter.class)
    private Instant date;
}

And a SearchParams class where all of the fields are optional

public class SearchParams {
    private Long id;
    private Instant startDate;
    private Instant endDate;
    private String event;

    public Optional<Long> getID() {
        return Optional.ofNullable(id);
    }
    // ... Similar for other fields ...
}

I would like to be able to search like:

public List<Event> search(SearchParams searchParams) {
    // Do something with Entity Manager/another hibernate class to create a custom query based off the search params.
    return events;
}

What's the easiest way to achieve this in JPA? Or is this not the right framework to use?

I could always resort to sql string building but its not a nice way of doing it.

how your saving the `SearchParams` class fields into database — Arvind Katte, Nov 14 '17 at 13:49
I think this post will help. https://stackoverflow.com/questions/2444603/optional-parameters-with-named-query-in-hibernate — Arvind Katte, Nov 14 '17 at 13:54
@ArvindKatte I'm not persisting that object, it is purely a class containing search filters to be used in the where clause, for example if `SearchParams` has the `event` field populated the database query would select rows with the event field matching the filter — Eduardo, Nov 14 '17 at 14:07
I explained solution here, may be it will helpful for you https://stackoverflow.com/a/48854352/4720910 — Musa, Feb 18 '18 at 17:14

score 0 · Answer 1 · answered Nov 14 '17 at 13:53

As far as remember JPA has method where, which takes array of Predicates as parameter.

So what you could do is create dynamic list of predicates from your params and then pass it to your query.

so your code will be something like that

List<Predicate> predicates= new ArrayList<>();
if (!searchParams.getId().isEmpty()){
predicates.add(cb.equal(root.get("id"), searchParams.getId());
}
....
query.where(predicates.toArray(new Predicate[predicates.size()]));

score 0 · Answer 2 · answered Nov 14 '17 at 15:27

The Spring Data JPA query-by-example technique uses Examples and ExampleMatchers to convert entity instances into the underlying query. The current official documentation clarifies that only exact matching is available for non-string attributes. Since your requirement involves a Date field, you can only have exact matching with the query-by-example technique.

Refer https://stackoverflow.com/a/39555487/4939174

You can also refer this link for Spring Data JPA - Specifications and Querydsl

JPA/Hibernate Custom Queries with optional parameters

2 Answers2