Panache: Query by Specification, Criteria or Example

185 views
Skip to first unread message

Loïc MATHIEU

unread,
Oct 26, 2021, 7:57:32 AM10/26/21
to Quarkus Development mailing list
Hello,

I want to open the discussion again, to offers typed queries for Panache (Hibernate / JPA will be the target but ideally also for MongoDB).

Today, when using Hibernate with Panache, you must write your queries as String (using PanacheQL, JPQL, SQL or using a named query).
But for complex use case (think search engine for example), you may want to dynamically create the creary and having a types API instead of creating a String is a nice to have.

I saw this subject going back a few times these days so I really think we should do something about it (or decide we will not and close the 6 related issues).

A long time ago I create this issue that tries to summarize the various issues and proposition we have bout it, and we didn't succeed in having any decision: https://github.com/quarkusio/quarkus/issues/8136

We can do a lot of things:
1. Integrate with the JPA Criteria API (but the API is a little complex to use and doesn't match MongoDB usage) by allowing you to query with a CriteriaQuery.
2. Create a Specification API: we will need to design ours (there are plenty of existing one out)
3. Create an example API : this is very easy to do but doesn't offers complex query creation
4. Keep the status quo for one more year :(

Proposed implementation for example:
Person p = new Person();
p.lastName = "Mathieu";
PanacheQuery<Person> mathieus = Person.findByExample(Example.of(p));
Proposed implementation for specification (or criteria):
Criteria criteria = Criteria.eq("lastName", "Mathieu")
    .and(Criteria.eq("firstName, "Loïc");
PanacheQuery<Person> me = Person.findByCriteria(criteria);
An alternative for 1. would be to allow querying with a JPA Query in addition to the existing String facilities, this will allow extending the querying facility to everything that can create JPA queries (MongoDB with Panache allow querying via a Document for such things).

Except for 4, we will need to add overload to list/stream/find/update/count/delete Panache methods to allow the new query functionality.

Let's the fight begin again :)

Regards,

Loïc


Stuart Douglas

unread,
Oct 27, 2021, 10:11:48 PM10/27/21
to Loïc MATHIEU, Quarkus Development mailing list

Basically to query for a person with an age greater than 18 and name Stuart you would have:


        PanacheQuery<Person> query = Person.findByCriteria(and(gt((s -> s.age = 18)), eq((s -> s.name = "Stuart"))));

Basically the 'eq' and 'gt' operations take a Consumer, and in that consumer you set the fields that you want this to apply to (potentially we could re-use the hibernate dirty tracking here to make this super efficient).

Compare your 'find by example'

Person p = new Person();
p.lastName = "Mathieu";
PanacheQuery<Person> mathieus = Person.findByCriteria(Example.of(p));

Something similar with this approach is:

        PanacheQuery<Person> query = Person.findByCriteria(eq(s -> s.lastName = "Mathieu"));

Your criteria example:

Criteria criteria = Criteria.eq("lastName", "Mathieu")
    .and(Criteria.eq("firstName, "Loïc");
PanacheQuery<Person> me = Person.findByCriteria(criteria);
Would be:

        PanacheQuery<Person> query = Person.findByCriteria(and(eq(s -> s.firstName = "Loïc"), eq(s-> s.lastName = "Mathieu")));

Alternatively:

        PanacheQuery<Person> query = Person.findByCriteria(eq(s -> {s.firstName = "Loïc"; s.lastName = "Mathieu";}));

Thoughts?

Stuart

--
You received this message because you are subscribed to the Google Groups "Quarkus Development mailing list" group.
To unsubscribe from this group and stop receiving emails from it, send an email to quarkus-dev...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/quarkus-dev/CAJLxjVEKYYRaL_MEx3vJ4wKcmbqM9JiqXcRWdO-29f5h1h7DqA%40mail.gmail.com.

Stuart Douglas

unread,
Oct 27, 2021, 10:26:11 PM10/27/21
to Loïc MATHIEU, Quarkus Development mailing list

Note that this won't be the absolute fastest way to run a query, as there will be additional allocations, but in practice I don't think it will matter if the end result is a really nice API.

Stuart

Loïc MATHIEU

unread,
Oct 28, 2021, 4:44:38 AM10/28/21
to Stuart Douglas, Quarkus Development mailing list
Hi Stuart,

This is interesting, a way to make a type safe Criteria/Specification API using an instance of the entity as with the Example API.
Using lambda may seem a little complicated for some people but I like it. It also play well if people uses setters/getters as they can now use method references.

Note that it could be strange to do 
PanacheQuery<Person> query = Person.findByCriteria(eq(s -> s.lastName = "Mathieu"));
And people may want to do instead
PanacheQuery<Person> query = Person.findByCriteria(s -> s.lastName.equals("Mathieu"));
So using a Predicate instead, but I don't know if it's really possible.
Then we will just gives methods to compose the query (and/or)

Stuart Douglas

unread,
Oct 29, 2021, 12:12:48 AM10/29/21
to Loïc MATHIEU, Quarkus Development mailing list
On Thu, 28 Oct 2021 at 19:44, Loïc MATHIEU <loik...@gmail.com> wrote:
Hi Stuart,

This is interesting, a way to make a type safe Criteria/Specification API using an instance of the entity as with the Example API.
Using lambda may seem a little complicated for some people but I like it. It also play well if people uses setters/getters as they can now use method references.

Note that it could be strange to do 
PanacheQuery<Person> query = Person.findByCriteria(eq(s -> s.lastName = "Mathieu"));

Note that there could also be a version without the lambda that just takes a constructed object, the lambda just allows it to be expressed in a single line.
 

And people may want to do instead
PanacheQuery<Person> query = Person.findByCriteria(s -> s.lastName.equals("Mathieu"));
So using a Predicate instead, but I don't know if it's really possible.

Theoretically possible yes, something we want to do, hell no :-) You would need to parse the bytecode and try and figure out what is going on, but this is very complex and error prone.

Stuart

Eduardo Ramirez Ronco

unread,
Nov 26, 2021, 5:42:56 AM11/26/21
to Quarkus Development mailing list
Hi everyone,

This reminded me to try JinQ (http://www.jinq.org/) in a Quarkus app: https://github.com/ebramirez/quarkus-jinq

When trying the native tests, it fails with this error:

Caused by: java.lang.Exception: java.lang.ClassNotFoundException: org.github.ebramirez.JinqLinesResource$$Lambda$d6a79f0ae093bd19432744272ea5fb493fcd74c0
	at com.user00.thunk.SerializedLambda.extractLambda(SerializedLambda.java:65)
	at org.jinq.jpa.transform.LambdaInfo.analyze(LambdaInfo.java:30)
	... 36 more
Caused by: java.lang.ClassNotFoundException: org.github.ebramirez.JinqLinesResource$$Lambda$d6a79f0ae093bd19432744272ea5fb493fcd74c0
	at java.lang.Class.forName(DynamicHub.java:1433)
	at java.io.ObjectInputStream.resolveClass(ObjectInputStream.java:756)
	at java.io.ObjectInputStream.readNonProxyDesc(ObjectInputStream.java:1995)
	at java.io.ObjectInputStream.readClassDesc(ObjectInputStream.java:1862)
	at java.io.ObjectInputStream.readOrdinaryObject(ObjectInputStream.java:2169)
	at java.io.ObjectInputStream.readObject0(ObjectInputStream.java:1679)
	at java.io.ObjectInputStream.readObject(ObjectInputStream.java:493)
	at java.io.ObjectInputStream.readObject(ObjectInputStream.java:451)
	at com.user00.thunk.SerializedLambda.extractLambda(SerializedLambda.java:60)

I know this is not directly related to Quarkus, but does anyone have an idea of why this happens?


Stephane Epardaud

unread,
Dec 1, 2021, 5:18:53 AM12/1/21
to Stuart Douglas, Loïc MATHIEU, Quarkus Development mailing list
Thanks Stuart, this is very interesting.
I wonder how many of the existing ORM Criteria predicates can actually be mapped to this model. I'm not sure we can really express them all. Here we're interpreting assignment (=) as an equality operation (==), but what about relations such as `IN` for example? Or aggregates like `SUM`?
I wonder how far we can push this idea.



--
Stéphane Épardaud

Stuart Douglas

unread,
Dec 1, 2021, 6:10:22 PM12/1/21
to Stephane Epardaud, Loïc MATHIEU, Quarkus Development mailing list
On Wed, 1 Dec 2021 at 21:18, Stephane Epardaud <stephane...@gmail.com> wrote:
Thanks Stuart, this is very interesting.
I wonder how many of the existing ORM Criteria predicates can actually be mapped to this model. I'm not sure we can really express them all. Here we're interpreting assignment (=) as an equality operation (==), but what about relations such as `IN` for example? Or aggregates like `SUM`?


So the idea here is that for 'in'  we would init an entity at build time and give each field a unique value, then see which value is returned so we know at build time which field is being acted on.

Panache does not really seem to have support for aggregate queries atm, but we should be able to support it using a similar technique to above.

Stuart
Reply all
Reply to author
Forward
0 new messages