如何使用CriteriaQuery转换SQL查询 [英] How to convert SQL Query using CriteriaQuery

问题描述

我想将我的sql查询从SQL转换为CriteriaQuery，我有这个sql查询:

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;

最好的方法是什么?

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.

推荐答案

根据我的记忆，这应该是您的第一个查询的表示形式.

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

立即测试
由于数据库没有响应，我现在无法对其进行测试，因此，如果它无法正常工作，我将在稍后编辑答案.

对非常量参数使用ParameterExpression，以确保服务器可以重用查询.否则，它需要分析和重新计划收到的每个新查询.

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.

但是在这种情况下，ParamterExpression似乎无法正常工作，可能是一个Hibernate问题(我使用5.4.2.Final进行了测试).

Ps .: 您应该避免使用"sql"关键字作为表标识符，因此不要使用其他名称来命名表"table".

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();

这篇关于如何使用CriteriaQuery转换SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！