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
– Arvind Katte, Commented Nov 14, 2017 at 13:49
I think this post will help. stackoverflow.com/questions/2444603/… — Arvind Katte
– Arvind Katte, Commented Nov 14, 2017 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
– Eduardo, Commented Nov 14, 2017 at 14:07
I explained solution here, may be it will helpful for you stackoverflow.com/a/48854352/4720910 — Musa
– Musa, Commented Feb 18, 2018 at 17:14

user902383 · Accepted Answer · 2017-11-14 13:53:48Z

0

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()]));

answered Nov 14, 2017 at 13:53

user902383

8,6508 gold badges47 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Daniel Devadoss · Accepted Answer · 2017-11-14 15:27:15Z

0

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

answered Nov 14, 2017 at 15:27

Daniel Devadoss

871 silver badge5 bronze badges

Collectives™ on Stack Overflow

JPA/Hibernate Custom Queries with optional parameters

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related