How can I dynamically create a query with a complex base clause using the Criteria API?

Question

I'm trying to dynamically query a database depending on which search parameters a user submits. To implement this dynamism, I'm moving away from using a standard JPA/Crud Repository. However, the base query is quite complex. Suppose that HOUSE and WINDOW are both entity classes and,

select h.*
    from HOUSE h join WINDOW w on h.id = w.house_id
    where ((w.status in ('OPEN', 'HALF OPEN')) or
    (w.status = 'CLOSED' and w.condition = 'GOOD') or
    (w.status = 'CLOSED' and w.condition is null and w.refurbished_date between NOW() - INTERVAL 365 DAY AND NOW()) or
    (w.status = 'CLOSED' and w.manufacture_date <> '1960-01-01 00:00:00' and w.refurbished_date between NOW() - INTERVAL 365 DAY AND NOW()))

(Not the real query but it exemplifies the real one well enough)

This query doesn't change. I only need to append 'AND' clauses to it depending on what's provided by the user.

I'm having a hard time deciding how to approach this. I haven't found anything online that suggests that I can translate the above SQL into the Criteria API's logic.

EDIT: So far, I've tried dynamically creating a query using plain Java and executing it via a Session. However, the results that are returned don't map automatically to my entity class (they're of type Object) and I've had to override the ResultSetTransformer -- a mess!

Answer 1

I let myself assume that your actual need is dynamic query, rather than Criteria API. If this is the case you may use FluentJPA's dynamic queries, which are designed to provide a template-like solution with "placeholders" for dynamic parts.

Also you may keep using JPA Repository pattern for the implementation.

Note, that I'm the FluentJPA library maintainer, but honestly your case is exactly what the library was designed for.