NativeQuery with EntityGraphs causes a ClassCastException


#1

I’m trying to use a NativeQuery to return a list of entity objects. The class has several lazy-loaded collections that I want populated.

My code looks like the following:

    EntityGraph entityGraph = entityManager.createEntityGraph(clazz);
    entityGraph.addAttributeNodes("collection1","collection2");
    Query query = entityManager.createNativeQuery(sql, clazz).setHint("javax.persistence.fetchgraph",entityGraph);
    items = query.getResultList();

When I run the code I get the following error:
java.lang.ClassCastException: org.hibernate.internal.SQLQueryImpl cannot be cast to org.hibernate.internal.QueryImpl

Since a native query can return a list of managed objects, I think it’s reasonable to expect this code to work.

Is there a way to make this work? The query isn’t expressible as a JPQL query, so if this can’t work I may be stuck using straight JDBC which is a shame.

Thanks,
Dave


#2

EntityGraphs work with entity queries like JPQL or criteria API.

They are not supposed to work with native SQL queries since the SQL query can be anything (e.g. set statement_timeout to 60000) and Hibernate cannot parse native queries to figure out what it contains.


#3

The fact that we’re passing an entity class to the createNativeQuery() method is a pretty big hint that this query is going to return a resultset that will be transformed into a list of entities. I wouldn’t think Hibernate would need to parse anything. It just needs to make sure that the resultant list of entities conform to the supplied entity graph.


#4

And if you select N entities, the entity graph will then issue N extra SELECT queries to initialize the LAZY associations.

It’s much more efficient to just join fetch those entities in the SQL query itself.

Again, because Hibernate cannot parse the SQL query it cannot add the JOIN clause for you. So, if the JOIN clause is not added, even if this this feature was implemented, it would still not be the best way to fetch data.


#5

Hibernate has the mapping metadata for the entity so it knows what properties/columns are being joined on.
The result set has all of the distinct values returned by the result set for the join properties/columns. Selecting from the child table where the properties/columns in the FK is in the set of distinct values for the join properties/columns would return the child items. if m is the maximum number of values that can participate in an IN clause, this would at least allow for n/m additional selects. Where m is > the number of distinct values in the foreign keys, you get a total 2 queries. Otherwise you get n/m + 1 queries.

In anycase, I gather from your answer that the answer is no, it doesn’t work. So I’m working around this issue.

Thanks,
Dave


#6

Hibernate has the mapping metadata for the entity so it knows what properties/columns are being joined on.

That’s true if you use direct fetching or entity queries.

The result set has all of the distinct values returned by the result set for the join properties/columns.

Hibernate only knows that you fetched objects of type clazz. It has no idea whether those are entities or DTOs or scalar results.

Selecting from the child table where the properties/columns in the FK is in the set of distinct values for the join properties/columns would return the child items. if m is the maximum number of values that can participate in an IN clause, this would at least allow for n/m additional selects. Where m is > the number of distinct values in the foreign keys, you get a total 2 queries. Otherwise you get n/m + 1 queries.

You’re talking about a non-existing feature which, even if it’s implemented, it’s less efficient than fetching the data you need in a single query. For more details, check out this article.

In anycase, I gather from your answer that the answer is no, it doesn’t work. So I’m working around this issue.

The only thing we should do is prevent the EntityGraph from being applied on a native SQL query with a more meaningful exception. I’m going to create a Jira issue for it.


#7

Hibernate does know that clazz is an entity class. It must. How would Hibernate map the values in the resultset into the correct properties in the object if it didn’t?

If I query without the entitiy graph the query works properly except the collections are not initialized. I can’t make the collections EAGER because 1. that would be a bad idea for performance in other use-cases, and 2. there’s more than one of them. My preference would be to move the transaction boundary so that the hibernate session is still active after the call to query.getResultList(), but I haven’t figured out how to do that yet.


#8

As shown in the User Guide, that works only when the columns and the entity properties match perfectly.

More, what if you join a Parent and a Child entity and those share the same column name? The User Guide tells you that will not work unless you provide the right aliases.

So, it happens to work in your case, but there are many other use cases where fetching an entity via native SQL query will require providing an SqlResultSetMapping with an EntityResult.

That’s how the JPA spec defines how you should fetch entities via native SQL queries.


#9

I think you’ve pointed me in a direction that will resolve my issue. It sounds like a SqlResultSetMapping is exactly what I need.

Thanks,
Dave


#10

I’m glad I could help.