Issue
I have two entities for which I implement CRUD operation with REST, Hibernate and Postgres. The API is written with Quarkus.
There is a Tenant and a Member class.
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
@EqualsAndHashCode
@Entity(name = "member")
@Table(name = "member")
public class Member {
@Id
@Column(name = "id", unique = true, nullable = false)
private UUID id;
@Column(name = "name", length = 50, unique = true, nullable = false)
private String name;
}
Tenant class:
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
@Entity(name = "tenant")
@Table(name = "tenant")
public class Tenant {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private UUID id;
@Column(length = 50, unique = true, nullable = false)
private String name;
@Column(name = "display_name", length = 50, unique = true, nullable = false)
private String displayName;
@JsonInclude(Include.NON_NULL)
@Column(length = 140, nullable = true)
private String description;
@JsonInclude(Include.NON_NULL)
@Column(name = "delete_after", nullable = true)
private Timestamp deleteAfter;
@OneToOne
@JoinColumn(name = "owner", nullable = false, unique = false)
private Member owner;
@ManyToMany(fetch = FetchType.EAGER)
private List<Member> members;
public void addMember(Member member) {
if(members == null)
members = new ArrayList<>();
if (!members.contains(member))
members.add(member);
}
}
As you can see a Tenant can have many members. Hibernate created three tables for the two classes:
List of relations
Schema | Name | Type | Owner
--------+---------------+-------+----------
public | member | table | postgres
public | tenant | table | postgres
public | tenant_member | table | postgres
postgres=# select * from member;
id | name
--------------------------------------+------
postgres=# select * from tenant;
id | delete_after | description | display_name | name | owner
--------------------------------------+--------------+-------------+--------------+----------+--------------------------------------
postgres=# select * from tenant_member;
tenant_id | members_id
--------------------------------------+--------------------------------------
My use case is that I want to get all tenants which have a certain member in their members list.
In the command line this would work:
SELECT t FROM tenant t WHERE t.id in (SELECT m.tenant_id FROM tenant_member m WHERE m.members_id = '137e86ce-fb75-4224-87a3-652e8a1d7cff' );
Unfortunately I get an problem, when I put this query into my code:
public List<Tenant> getAllTenantByMember(@PathParam("memberId") UUID memberId) {
Member member = entityManager.find(Member.class, memberId);
if(member == null)
throw new NotFoundException("Owner not found.");
TypedQuery<Tenant> query = entityManager.createQuery("SELECT t FROM tenant t WHERE t.id in (SELECT m.tenant_id FROM tenant_member m WHERE m.members_id = :value )", Tenant.class);
query.setParameter("value", memberId);
List<Tenant> result = query.getResultList();
return result != null ? result : new ArrayList<Tenant>();
}
The concrete problem is an exception:
2021-02-17 00:34:14,933 ERROR [io.qua.ver.htt.run.QuarkusErrorHandler] (executor-thread-1) HTTP Request to /api/v1/members/137e86ce-fb75-4224-87a3-652e8a1d7cff/tenants failed, error id: 33c184bd-6f42-4db8-9439-361642cad8e3-1: org.jboss.resteasy.spi.UnhandledException: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: tenant_member is not mapped [SELECT t FROM mypackage.orm.Tenant t WHERE t.id in (SELECT m.tenant_id FROM tenant_member m WHERE m.members_id = :value )]
at org.jboss.resteasy.core.ExceptionHandler.handleApplicationException(ExceptionHandler.java:106)
at org.jboss.resteasy.core.ExceptionHandler.handleException(ExceptionHandler.java:372)
at org.jboss.resteasy.core.SynchronousDispatcher.writeException(SynchronousDispatcher.java:218)
at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:519)
at org.jboss.resteasy.core.SynchronousDispatcher.lambda$invoke$4(SynchronousDispatcher.java:261)
at org.jboss.resteasy.core.SynchronousDispatcher.lambda$preprocess$0(SynchronousDispatcher.java:161)
at org.jboss.resteasy.core.interception.jaxrs.PreMatchContainerRequestContext.filter(PreMatchContainerRequestContext.java:364)
at org.jboss.resteasy.core.SynchronousDispatcher.preprocess(SynchronousDispatcher.java:164)
at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:247)
at io.quarkus.resteasy.runtime.standalone.RequestDispatcher.service(RequestDispatcher.java:73)
at io.quarkus.resteasy.runtime.standalone.VertxRequestHandler.dispatch(VertxRequestHandler.java:138)
at io.quarkus.resteasy.runtime.standalone.VertxRequestHandler.access$000(VertxRequestHandler.java:41)
at io.quarkus.resteasy.runtime.standalone.VertxRequestHandler$1.run(VertxRequestHandler.java:93)
at io.quarkus.runtime.CleanableExecutor$CleaningRunnable.run(CleanableExecutor.java:231)
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at org.jboss.threads.EnhancedQueueExecutor$Task.run(EnhancedQueueExecutor.java:2415)
at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1452)
at org.jboss.threads.DelegatingRunnable.run(DelegatingRunnable.java:29)
at org.jboss.threads.ThreadLocalResettingRunnable.run(ThreadLocalResettingRunnable.java:29)
at java.base/java.lang.Thread.run(Thread.java:834)
at org.jboss.threads.JBossThread.run(JBossThread.java:501)
Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: tenant_member is not mapped [SELECT t FROM mypackage.orm.Tenant t WHERE t.id in (SELECT m.tenant_id FROM tenant_member m WHERE m.members_id = :value )]
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:138)
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181)
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:188)
at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:725)
at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:816)
at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:113)
at io.quarkus.hibernate.orm.runtime.session.TransactionScopedSession.createQuery(TransactionScopedSession.java:379)
at io.quarkus.hibernate.orm.runtime.session.ForwardingSession.createQuery(ForwardingSession.java:188)
at io.quarkus.hibernate.orm.runtime.session.ForwardingSession.createQuery(ForwardingSession.java:47)
at mypackage.resources.MemberResource.getAllTenantByMember(MemberResource.java:79)
at mypackage.resources.MemberResource_Subclass.getAllTenantByMember$$superaccessor2(MemberResource_Subclass.zig:440)
at mypackage.resources.MemberResource_Subclass$$function$$2.apply(MemberResource_Subclass$$function$$2.zig:33)
at io.quarkus.arc.impl.AroundInvokeInvocationContext.proceed(AroundInvokeInvocationContext.java:54)
at io.quarkus.narayana.jta.runtime.interceptor.TransactionalInterceptorBase.invokeInOurTx(TransactionalInterceptorBase.java:127)
at io.quarkus.narayana.jta.runtime.interceptor.TransactionalInterceptorBase.invokeInOurTx(TransactionalInterceptorBase.java:100)
at io.quarkus.narayana.jta.runtime.interceptor.TransactionalInterceptorRequired.doIntercept(TransactionalInterceptorRequired.java:32)
at io.quarkus.narayana.jta.runtime.interceptor.TransactionalInterceptorBase.intercept(TransactionalInterceptorBase.java:53)
at io.quarkus.narayana.jta.runtime.interceptor.TransactionalInterceptorRequired.intercept(TransactionalInterceptorRequired.java:26)
at io.quarkus.narayana.jta.runtime.interceptor.TransactionalInterceptorRequired_Bean.intercept(TransactionalInterceptorRequired_Bean.zig:340)
at io.quarkus.arc.impl.InterceptorInvocation.invoke(InterceptorInvocation.java:41)
at io.quarkus.arc.impl.AroundInvokeInvocationContext.perform(AroundInvokeInvocationContext.java:41)
at io.quarkus.arc.impl.InvocationContexts.performAroundInvoke(InvocationContexts.java:32)
at mypackage.resources.MemberResource_Subclass.getAllTenantByMember(MemberResource_Subclass.zig:397)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at org.jboss.resteasy.core.MethodInjectorImpl.invoke(MethodInjectorImpl.java:170)
at org.jboss.resteasy.core.MethodInjectorImpl.invoke(MethodInjectorImpl.java:130)
at org.jboss.resteasy.core.ResourceMethodInvoker.internalInvokeOnTarget(ResourceMethodInvoker.java:643)
at org.jboss.resteasy.core.ResourceMethodInvoker.invokeOnTargetAfterFilter(ResourceMethodInvoker.java:507)
at org.jboss.resteasy.core.ResourceMethodInvoker.lambda$invokeOnTarget$2(ResourceMethodInvoker.java:457)
at org.jboss.resteasy.core.interception.jaxrs.PreMatchContainerRequestContext.filter(PreMatchContainerRequestContext.java:364)
at org.jboss.resteasy.core.ResourceMethodInvoker.invokeOnTarget(ResourceMethodInvoker.java:459)
at org.jboss.resteasy.core.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:419)
at org.jboss.resteasy.core.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:393)
at org.jboss.resteasy.core.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:68)
at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:492)
... 18 more
Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: tenant_member is not mapped [SELECT t FROM mypackage.orm.Tenant t WHERE t.id in (SELECT m.tenant_id FROM tenant_member m WHERE m.members_id = :value )]
at org.hibernate.hql.internal.ast.QuerySyntaxException.generateQueryException(QuerySyntaxException.java:79)
at org.hibernate.QueryException.wrapWithQueryString(QueryException.java:103)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:220)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:144)
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:113)
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:73)
at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:162)
at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:604)
at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:716)
... 52 more
Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: tenant_member is not mapped
at org.hibernate.hql.internal.ast.util.SessionFactoryHelper.requireClassPersister(SessionFactoryHelper.java:169)
at org.hibernate.hql.internal.ast.tree.FromElementFactory.addFromElement(FromElementFactory.java:91)
at org.hibernate.hql.internal.ast.tree.FromClause.addFromElement(FromClause.java:77)
at org.hibernate.hql.internal.ast.HqlSqlWalker.createFromElement(HqlSqlWalker.java:333)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromElement(HqlSqlBaseWalker.java:3758)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromElementList(HqlSqlBaseWalker.java:3647)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromClause(HqlSqlBaseWalker.java:732)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:588)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.collectionFunctionOrSubselect(HqlSqlBaseWalker.java:4993)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.inRhs(HqlSqlBaseWalker.java:4893)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.comparisonExpr(HqlSqlBaseWalker.java:4554)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:2161)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.whereClause(HqlSqlBaseWalker.java:827)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:621)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:325)
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:273)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:276)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:192)
... 58 more
Does somebody know how to handle the error or maybe know another way to create queries, where the where condition is an object from a list?
Solution
Your problem is that you try to mix up JPQL and SQL syntax in the entityManager.createQuery method.
Try to use the following query:
TypedQuery<Tenant> query = entityManager.createQuery("SELECT t FROM tenant t
join t.members m where m.id = :value", Tenant.class);
query.setParameter("value", memberId);
List<Tenant> result = query.getResultList();
I would suggest you remove name properties from all @Entity annotations and just use class names in your JPQL like this:
SELECT t FROM Tenant t
join t.members m where m.id = :value
This is common practice and it will allow you to emphasize that this is JPQL, not SQL query.
Answered By - SternK
Answer Checked By - David Marino (JavaFixing Volunteer)
