Implements Specification or Example for Panache

loicmathieu commented 4 years ago

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 commented 4 years ago

/cc @FroMage @loicmathieu

loicmathieu commented 4 years ago

/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 commented 4 years ago

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

emmanuelbernard commented 4 years ago

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 commented 4 years ago

@gavinking had some ideas too.

marceloverdijk commented 4 years ago

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 commented 4 years ago

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 👀.

hogmuzzle commented 3 years ago

I have seen really big projects (60+ tables) where Spring Specifications and Criteria API were used. Of course there is no "one size fits all" approach but for complex queries QueryDSL is much more convenient and sql-looking and you can use JPA or SQL OracleQuery for vendor specific queries for example in the same project. It would be great to have QueryDSL in Quarkus

kivan-mih commented 3 years ago

I have seen really big projects (60+ tables) where Spring Specifications and Criteria API were used. Of course there is no "one size fits all" approach but for complex queries QueryDSL is much more convenient and sql-looking and you can use JPA or SQL OracleQuery for vendor specific queries for example in the same project. It would be great to have QueryDSL in Quarkus

Anything going on the way to typesafe compile time queries in Quarkus at this moment?

By the way QueryDSL status is now again "maintained" for more than a year, so probably it will be a good idea to integrate it https://github.com/querydsl/querydsl/graphs/contributors . And yeah in my experience typesafe queries are good not only for big projects, you mostly benefit from them when the project is actively changed, no matter of its size and it saves much time. Lack of typesafe queries is the the thing which stops me from migrating to Quarkus unfortunately.

Sanne commented 3 years ago

@gavinking , might want to comment? ^

diospam82 commented 2 years ago

IMHO, a copy of Spring Specifications is not necessary, but a replication of the core features is IMHO essential for Quarkus:

Type-safe construction of query search terms, I want to pass strings without fear of SQL-injection attacks, I want to pass floating point numbers or time-based types without worrying about formats and types. Equally I want to pass int or long (signed or unsigned) without fear of out-of bounds errors.
Build a search criteria that implements expected behavior for strings ("equal", "like", "starts_with", "ends_with", UPPER, LOWER etc.) and numericals ( >, <, =, <=, >= ), this also includes handling time in the format of unix timestamps and/or Joda Time
Combining individual query criteria through implementation of the decorator pattern and operators of 'and', 'or', 'not' but also SQLs like INNER / OUTER JOIN.
Built-in pagination support

Optional: -Type-safe references to entity columns thanks to @column annotation?

zambotn commented 2 years ago

Having a query language that can work agnostically, in both Mongo and RDBMS, would be very nice.

For the same reason, I'm also quite interested in the query by example feature (which is closer to query RDBMS in the "mongo-way"); it is a very compact way to perform typed queries and it doesn't add too many lines of code. This is pretty important since the reactive way of implementing services is, in my experience, already multiplying by a factor of 3 the amount of code needed to perform the same blocking operations.

rockdodos commented 2 years ago

I use dynamic query on Quarkus 2.9.1.Final below

CriteriaBuilder criteriaBuilder = getEntityManager().getCriteriaBuilder();
CriteriaQuery<xxx> query = criteriaBuilder.createQuery(xxx.class);
Root<xxx> root = query.from(xxx.class);
List<Predicate> predicates = new ArrayList<>();
if (condition1) {
    predicates.add(criteriaBuilder.equal(root.get("field1"), field1));
}
if (condition2) {
    predicates.add(criteriaBuilder.greeterThan(root.get("field2"), field2));
}
query.where(predicates.toArray(Predicate[]::new));
return getEntityManager().createQuery(query).getResultList();

quarkusio / quarkus

Implements Specification or Example for Panache #8136

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.

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.