Temptable in Hibernate, Criteria JPA

Hibernate ORM
1

Hi dear community,
We create a temp table like this (a native query):

CREATE TEMP TABLE filtering_criteria
         (
            fieldA UUID,
            fieldB UUID,
            fieldC UUID
         );
CREATE INDEX fieldA_idx ON filtering_criteria (fieldA);
CREATE INDEX fieldB_idx ON filtering_criteria (fieldB);
CREATE INDEX fieldC_idx ON filtering_criteria (fieldC);

Then, we fill it with some data.
And we’d like to use this temptable like here:

 SELECT f.fieldA, f.fieldB, f.fieldC, p.* FROM filtering_criteria f
        LEFT JOIN source_table p ON p.fieldA = f.fieldA
            AND p.fieldB = f.fieldB
            AND p.fieldC = f.fieldC

We select from the temptable, joining the real table (source_table here).

We’d like to write this query using Criteria API since we want to avoid native queries as much as possible. The problem is that CriteriaQuery.from requires an entity class:
<X> JpaRoot<X> from(Class<X> entityClass);

Is there a way to select from a temptable using Criteria API (avoiding native queries)? Could you propose any ideas, workarounds or “hacks” (in case if there’s no direct support for this)?

Thanks in advance.

PS:

  • We are using Postgres 16.
  • We could have used a CTE instead of temptable, but we’d like to create indexes on the fields of our temptable (indexes cannot be created for CTE).
1 Like
2

The Criteria API requires mapped entities to select from. You should create a mapping for your temporary table, something like:

@Entity
class TempTable {
  private UUID fieldA;
  private UUID fieldB;
  private UUID fieldC;
}

And then simly use query.from( TempTable.class ).

3

If you don’t want to create a separate entity-сlass as suggested by @mbladel.
You could use Tuple instead!
Just call query.from(Tuple.class)