Multiple from-elements in JPQL

Hibernate ORM
1

Since upgrading to Spring Boot 3.x and Hibernate 6, pretty much all of my JPQL fails to execute and I’ve had to revert to native SQL queries. I keep trying new version of spring/hibernate as they get released but as of Spring Boot 3.2.2 and Hibernate 6.4.1, this problem has persisted. I don’t know if it’s the way I’ve written my schema, or the way I’m writing my JPQL so I have to ask the community.

(I’ll try to make this make sense. I’m not good at documenting table relations.)

@Entity(name = "workgroup")
class Workgroup {
  @Id
  long id;
  String name;
}

@Entity(name = "user")
class User {
  @Id
  long id;

  String name;

  @ManyToOne
  Workgroup workgroup;
}

@Entity(name = "request")
class Request {
  @Id
  long id;

  @ManyToOne
  Workgroup workgroup;

  @ManyToOne
  User requester;

  @ManyToOne
  User assignedTo;
}

That’s the basic entity structure. Three tables, workgroup, user and request. My JPQL will be:

select r.id, w.name, req.name, asn.name
from request r
inner join user req on r.requester.id = req.id
inner join user asn on r.assignedTo.id = asn.id

I use Query q = entityManager.createQuery(jpql, Tuple.class) and List<Tuple> rs = q.getResultList()

This results in an error of: Multiple from-elements expose unqualified attribute : workgroup

It’s coming from line 148 of SqmPathRegistryImpl.class. The first found fromElement will be one of the paths, and it appears to clearly have the alias in it. When the second path is found, with a different alias, it just checks that found is not null and throws the error. It almost seems like I can’t join to the same table twice in the same query. Something I did a lot of with Hibernate 5.

So my question is, am I not aliasing the tables properly? Or do I have something wrong with my entities or relations?

2

The exception doesn’t make sense since the query you have shown does not even contain the word workgroup. I assume your example “simplification” attempts removed vital information.

Please share the full query and the full stack trace.

I’m assuming that your query actually looks something like the following:

select r.id, w.name, req.name, asn.name
from request r
inner join user req on r.requester.id = req.id
inner join user asn on r.assignedTo.id = asn.id
inner join workgroup w on r.workgroup.id = w.id

There are multiple problems with such a query, and the biggest is IMO that this is not how one should use JPQL/HQL. The query language is rich and supports joining associations directly, so why are you using explicit on clauses?

The reason for the exception is that workgroup in inner join workgroup w ... is assumed to be an attribute. You have 3 from entity nodes that expose this attribute, r, req and asn, because the Request and User entities have an attribute named workgroup.

Naming entities the same as attributes is asking for trouble, which is why it’s a best practice and default naming to have the first letter of an entity name to be upper case, like that class name. In your particular case though you can easily solve this by writing a proper query.

select r.id, w.name, req.name, asn.name
from request r
join r.requester req
join r.assignedTo asn
join r.workgroup w

or even simpler since you use inner joins anyway

select r.id, r.workgroup.name, r.requested.name, r.assignedTo.name
from request r
3

When the error is thrown with the jpql below, these are the values in found, fromElement, and navigableName:

found: SqmEntityJoin(com.example.UserEntity(rq))
fromElement: SqmEntityJoin(com.example.UserEntity(asn))
navigableName: workgroup

My class names are UserEntity, WorkgroupEntity, and RequestEntity.

org.springframework.dao.InvalidDataAccessResourceUsageException: Error interpreting query [Multiple from-elements expose unqualified attribute : workgroup] [select distinct r.id as r_id
,aswg.id as aswg_id
,aswg.name as aswg_name
,rq.id as rq_id
,rq.name as rq_name
,asn.id as asn_id
,asn.name as asn_name
from request r
inner join user rq on rq.id = r.requester.id
left outer join user asn on asn.id = r.assignedTo.id
left outer join workgroup aswg on aswg.id = asn.workgroup.id
] [select distinct r.id as r_id
,aswg.id as aswg_id
,aswg.name as aswg_name
,rq.id as rq_id
,rq.name as rq_name
,asn.id as asn_id
,asn.name as asn_name
from request r
inner join user rq on rq.id = r.requester.id
left outer join user asn on asn.id = r.assignedTo.id
left outer join workgroup aswg on aswg.id = asn.workgroup.id
]
	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:298)
	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:241)
	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:550)
	at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
	at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:335)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:152)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:57)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:164)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:220)
	at jdk.proxy4/jdk.proxy4.$Proxy256.execute(Unknown Source)
	at com.example.getAllRequests(service.java:175)
4

Like I explained above, use qualified attribute joins instead.

5

I apologize, I’m not great with database terms. I figured it was something related to how I’m writing the JPQL. I have not worked with that nearly as much as writing native SQL to get what I need.

What do you mean by “use qualified attribute joins”?

Edit: I think I see what you mean. Instead of:

inner join user rq on rq.id = r.requester.id
left outer join user asn on asn.id = r.assignedTo.id

write it as:

inner join r.requester rq
left outer join r.assignedTo asn

and let the parser figure out the correct “on” syntax? If that’s not correct, please let me know. I will experiment with changing my stuff around to this new syntax.

6

and let the parser figure out the correct “on” syntax?

You already specified everything that Hibernate ORM needs to know to render the on clause for you with your mapping annotations. So yes, write HQL that joins attributes instead of spelling out the on clause yourself.

And do yourself a favor and change the entity name to upper case e.g. @Entity(name = "User") instead of @Entity(name = "user") to avoid such naming collisions.

7

Thank you for all of the help!