How to convert SQL Query using CriteriaQuery

Question

I want to convert my sql query from SQL to CriteriaQuery, I have this sql query:

1)

SELECT * FROM table AS t WHERE id = (SELECT MAX(id) FROM table AS t WHERE t.element_id = 354 AND (name <> 'foo' OR (name = 'bar' AND event = 'foo')));

2)

SELECT tr1.*
FROM table AS tr1
INNER JOIN (SELECT MAX(id) AS id FROM table AS tr WHERE tr.element_id = 354 AND (name <> 'foo' OR (name = 'bar' AND event = 'foo'))) AS tr2
ON tr1.id = tr2.id;

How is the best way to do this ?

public Predicate createsubqueryDateGreaterThanTest(CriteriaBuilder cb, Root<? extends Entity> root, Date inputDate){         
        // create the outer query
        CriteriaQuery<Date> cq = cb.createQuery(Date.class);

        Root<Table> rootQuery = cq.from(Table.class);
        Subquery<Table> sub = cq.subquery(Table.class);
        Root<Table> subRoot = sub.from(Table.class);
        //sub.select(subRoot);
        cq.multiselect(rootQuery);
        sub.select(subRoot)
        //cq.multiselect(rootQuery.select(cb.max(Table_.elementId)));
        //sub.select(cb.greatest(cb.get(Entity_.element).get("id")));
        //cq.multiselect(subRoot.select(cb.greatest(Table_.elementId)))
            .where(
                    cb.greaterThanOrEqualTo(subRoot.get(Table_.date), inputDate),
                    cb.equal(root.get(Entity_.element).get("id"), subRoot.get(Table_.elementId)),
                    cb.notEqual(subRoot.get(Table_.name), "foo"),
                    cb.or(
                         cb.equal(subRoot.get(Table_.name), "bar"),
                         cb.and(cb.equal(subRoot.get(Table_.event), 'foo'))
                        )
                );

        return cb.exists(sub);
    }

The query runs but the result is incorrect.

Please provide some example code of what you have already achieved with the criteria api und describe what your problems are. — second, May 21 '19 at 08:42
@SimonMartinelli I need to use CriteriaQuery to implement a search filter. — Dwarlan, May 21 '19 at 09:01
Please also provide the entities you are using (Table, Date, Entity) — second, May 21 '19 at 09:29
If you are using hibernate as implementation you can configure the 'show_sql'parameter to see the resulting sql query of your implementation. — second, May 21 '19 at 09:36
@second The entities are not related to each other. (I don't think entities are needed to solve the problem). — Dwarlan, May 21 '19 at 09:40
Having the table defintion of the database would make it easier to verify it myself. — second, May 21 '19 at 09:48
What is 'Entity_.element' suppusod to be? I assumed originally you only have one Table with an columns (id, elment_id, name, event). Your queries seem to miss some (or have some incorrect) table prefixes as well. — second, May 21 '19 at 09:55
@second It's a Metamodel class. [link](https://www.baeldung.com/hibernate-criteria-queries-metamodel). There is no prefix error. — Dwarlan, May 21 '19 at 10:14
The 2nd query is only a variation of the 1st? Or is it supposed to return something else? At least its not 'mysql (v8)' compatible. You find the CriteriaQuery for the 1st one in my answer below. — second, May 21 '19 at 13:50

second · Answer 1 · 2019-05-21T13:33:31.480

From what I remember this should be the representation of your 1st query.

Edit: Tested now
~~I can not test it right now, as my database is not responding, so if it does not work I'll edit the answer later.~~

Use ParameterExpression's for non constant parameters to make sure that the server can reuse the query. Else it need to analyze and replan every new query it receives.

But in this case ParamterExpression's do not seem to work correctly, might be an Hibernate issue (I used 5.4.2.Final for testing this).

Ps.: You should avoid using 'sql' keywords for table identifiers, so instead of naming your table 'table' use something else.

SELECT * FROM table AS t WHERE id = (SELECT MAX(id) FROM table AS t WHERE t.element_id = 354 AND (name <> 'foo' OR (name = 'bar' AND event = 'foo')));

        CriteriaQuery<Table> criteriaQuery = cb.createQuery(Table.class);
        Root<Table> root = criteriaQuery.from(Table.class);

        Subquery<Integer> sub = criteriaQuery.subquery(Integer.class);
        Root<Table> subRoot = sub.from(Table.class);

        criteriaQuery.where(
                cb.equal(subRoot.get(Table_.element_id), 354),
                cb.or(cb.notEqual(subRoot.get(Table_.name), "foo"),
                      cb.and(cb.equal(subRoot.get(Table_.name), "bar"), 
                             cb.equal(subRoot.get(Table_.event), "foo"))));

        sub.select(cb.max(subRoot.get(Table_.id)));

        criteriaQuery.where(cb.equal(root.get(Table_.id), sub));
        criteriaQuery.select(root);

        List<Table> result =
        entityManager.createQuery(criteriaQuery)
        .getResultList();

I am currently testing. The second query is a variation of the first. — Dwarlan, May 21 '19 at 14:34

How to convert SQL Query using CriteriaQuery

1 Answers1

Linked