Quarkus: Implements Specification or Example for Panache

[loicmathieu]

Description
Today, both Hibernate and MongoDB with Panache allow to make query based on un-validated strings.

While being easy to use and allow to handle complex cases, this has some drawbacks:

• The query is not validated.
• The query is not type safe (as validated by the compiler and limited to the property of a precise type).
• Dynamic query (where some part of the query needs to be added conditionaly) relies on string concatenation which is not very handy.

A common solution for this situation is to implements query by Specification/Criteria or by Example.

Alternatives has been discuss on:

• #2303 where optional predicates has been proposed to workaround the dynamic query creation issue (a predicate will be removed if null) . Query predicate will mandates a parser for the query string so it will be validated at runtime before hitting the database.
• #5552 propose to validate the query at compile time via an annotation processor.
• #3965 jpa criteria support: this will not be possible for MongoDB. A way to implement this is to allow override of find/list/stream methods with JPA Query to allow passing a CriteriaQuery. We can build our own Criteria API to avoid using the JPA specific one (this is the Specification proposal of this issue).
• #4016 QueryDsl support: it has the advantage to provides both Hibernate and Mongo implementation. If we choose to implements a Secification/Criteria API we can choose to support QuerySql to avoid implementing our own API.

Query by example has been asked in #4015

Specification has been asked in #4040 for our Spring Data support, it needs Specification to be implemented inside Panache first.

Some argumentation of this one vs #2303 can be found here: https://github.com/quarkusio/quarkus/issues/2303#issuecomment-518617812

Proposed impementation for example:

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

The implementation can use Example semantic, or we can directly write Person.findByExample(p) if we don't care to enforce a semantic by having a carrier Example class.
Same for list/stream/delete/count methods.

Proposed implementation for Specification/Criteria:

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

This is less type safe than Example as we can create criteria on non-existing fields but we can express more operations (eq, lt, gt, ...) and can better compose predicates ('and' and 'or').
Implementation is harder than Example but we can use the QueryDsl library for this instead of implementing our own.

Side note: _this issue states that we choose Specification/Criteria or Example but we can of course choose to implement both ;)_

[quarkusbot]

/cc @FroMage @loicmathieu

[loicmathieu]

/cc @emmanuelbernard I try to include all disscussions from all related issue in this one and link as many existing issue as possible so we don't loose tracks on the various possible way of providing more query capabilities and type safety to Panache.

[geoand]

cc @Sanne who I am sure also has ideas in this area

[emmanuelbernard]

Thanks @loicmathieu. So reading your main concern, the type safe query checking combined with the optional predicate option might address your concerns. That's why I'd prefer to test this before going on the example API which I find flawed for a few reasons.

[FroMage]

@gavinking had some ideas too.

[marceloverdijk]

https://github.com/quarkusio/quarkus/issues/9036 provides some ideas to use HIbernate's @Filter mechanism as mentioned by @Sanne in this discussion: https://groups.google.com/forum/#!topic/quarkus-dev/fkj6VW4xV7Y

Personally I'm still in favour of something like Spring Data's Specification as it gives me more flexibility (and yes I understand it's not compile save).
As I need to dynamically build the WHERE clause with dynamic filter operation (eq lt lte like etc.) I have to build my WHERE string using concatenation. With something like Specifications I would have an api for that.

[marcoblos]

I've been reading a little about how dynamic queries/filters works on Panache, here and on google groups, I also have some experience with spring data and the way that I see, something similar to the code below solve a lot of problems, at least for me:

public class ProductFilter {
    private int page;
    private int quantityPerPage;
    private boolean active;
    private Long idStore;
    private List<Long> listOfIdCategories;
    ...

    public Criteria getCriteria() {
        Criteria criteria = new Criteria();
        criteria.page(this.page, this.quantityPerPage);
        criteria.eq("sp.active", this.active);
        criteria.orderBy("sp.stock", Order.DESC);
        if (this.idStore != null) {
            criteria.eq("s.id", this.idStore);
        }
        if (this.listOfIdCategories != null && !this.listOfIdCategories.isEmpty()) {
            criteria.in("cp.idCategory", this.listOfIdCategories.toArray());
        }
        return criteria;
    }
}

public interface ProductRepositoryCustom {
    List<Product> findProductsByCriteria(Criteria criteria);
}

public class ProductRepositoryCustomImpl implements ProductRepositoryCustom {
    private EntityManager em;

    public List<Product> findProductsByCriteria(Criteria criteria) {
        StringBuilder builder = new StringBuilder();
        builder.append("SELECT "
            + " new com.organization.dto.Product(...)"
            + " FROM Product p"
            + " LEFT JOIN StoreProduct sp ON sp.idProduct = p.id"
            + " LEFT JOIN Store s ON s.id = sp.idStore"
            + " LEFT JOIN CategoryProduct cp ON cp.idProduct = p.id"
            + " WHERE ");
        builder.append(criteria.getSQL());
        Query query = em.createQuery(builder.toString(), Product.class);
        criteria.getParameters().forEach((key, value) -> {
            query.setParameter(key, value);
        });
        query.setFirstResult((int) criteria.getPage().getOffset());
        query.setMaxResults(criteria.getPage().getPageSize());
        return query.getResultList();
    }
}

@Path("/products")
public class ProductsController {

    @Inject
    private ProductRepositoryCustom repository;

    @GET
    public Response filterProducts(ProductFilter filter) {
        List<Product> listOfProducts = repository.findProductsByCriteria(filter.getCriteria());
        return Response.ok(listOfProducts).build();
    }
}

I just wrote all this code to say that it's important to have some flexibility, and a combination of JPQL/HQL + custom WHERE clause cover a lot of different problems and scenarios, especially where you don't know what parameters the user will pick on frontend. As you guys can see, this is a custom implementation, building the query "by my hand", and of course a lot of custom code inside the Criteria class that I used on the example above, but the use itself of the implementation is very easy and useful.

It would be nice to see something like:

@ApplicationScoped
public class ProductRepository implements PanacheRepository<Product> {
    @Query("SELECT "
            + " new com.organization.dto.Product(...)"
            + " FROM Product p"
            + " LEFT JOIN StoreProduct sp ON sp.idProduct = p.id"
            + " LEFT JOIN Store s ON s.id = sp.idStore"
            + " LEFT JOIN CategoryProduct cp ON cp.idProduct = p.id"
            + " WHERE ")
    List<Product> findProductsByCriteria(Criteria criteria);
}

Please, don't get me wrong, but the @Filters approach looks like very bureaucratic and verbose, declaring all filters that I want to use and also looks like don't give me all the flexiblity that I can have with this custom implementation. I read something abouyt 80% 20%, I agree with that, it's probably the 20%, but it would be nice that this 20% works like a charm 👀.