Panache: Query by Specification, Criteria or Example

[Loïc MATHIEU]

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]

Just throwing a random idea out there, but what about: https://github.com/quarkusio/quarkus/commit/037fa5ebf4362a22b9dff1c277421c9de146cff7

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]

It even maps ok to join support: https://github.com/stuartwdouglas/quarkus/commit/d433b551989fdb60bb422613dffcfd64ddcbbba3

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]

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]

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]

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?

To view this discussion on the web visit https://groups.google.com/d/msgid/quarkus-dev/CAD%2BL2cyG%2BTXDq8Ya%3DPhf4c_bq7YXgbdFz-ZgsqGXZ1LEmPDkRg%40mail.gmail.com.

[Stephane Epardaud]

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.

To view this discussion on the web visit https://groups.google.com/d/msgid/quarkus-dev/CAD%2BL2cxCrYvA147gT0krRUT4OETf5V_WSJ3gxiFKZkAcSviu0g%40mail.gmail.com.

--

Stéphane Épardaud

[Stuart Douglas]

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`?

https://github.com/quarkusio/quarkus/compare/main...stuartwdouglas:brainstorming#diff-a985925c9c0a636b9b34e533134036a306efdc86f2c56f0baccd40bdf5941e0eR39

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