Use tuples as query parameters

Hibernate ORM
1

Hi all,

I am using Quarkus with Panache and am encountering an problem with my implementation of a named query which seems to originate from hibernate ORM.
I try to model a query that resembles the following SQL:

select *
from table t
where (t.fk_1_id, t.fk_2_id) in ((13, 113),
                                        (13, 115),
                                        (13, 116));

which looks like this in a Panache named query:

@Entity
@Table(name = "myentity")
@NamedQueries({
        @NamedQuery(
                name = "myentity.queryWithTuples",
                query = "select * myentity t " +
                        "where(val.fk_1.id , val.fk_2.id) in :tuples"
        )
})
public class MyEntity extends PanacheEntity {

//...

// Tuples is a basic record that holds two Long variables
public static List<MyEntity> queryWithTuples(List<Tuple> tuples) {
        return find(
                "#Validity.queryWithTuples",
                Parameters
                        .with("tuples", tuples)
        ).project(MyEntity.class).list();
    }
}

but if i run this query I get the following StackTrace:

"java.lang.UnsupportedOperationException
at org.hibernate.metamodel.model.domain.internal.ArrayTupleType.forEachJdbcType(ArrayTupleType.java:120)
at org.hibernate.metamodel.mapping.Bindable.forEachJdbcType(Bindable.java:38)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.resolveSqmParameter(BaseSqmToSqlAstConverter.java:6107)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.consumeSqmParameter(BaseSqmToSqlAstConverter.java:5677)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.consumeSingleSqmParameter(BaseSqmToSqlAstConverter.java:5766)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.processInSingleParameter(BaseSqmToSqlAstConverter.java:7932)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.processInSingleHqlParameter(BaseSqmToSqlAstConverter.java:7889)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.processInListWithSingleParameter(BaseSqmToSqlAstConverter.java:7878)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitInListPredicate(BaseSqmToSqlAstConverter.java:7799)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitInListPredicate(BaseSqmToSqlAstConverter.java:440)
at org.hibernate.query.sqm.tree.predicate.SqmInListPredicate.accept(SqmInListPredicate.java:147)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitWhereClause(BaseSqmToSqlAstConverter.java:2484)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitQuerySpec(BaseSqmToSqlAstConverter.java:2061)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitQuerySpec(BaseSqmToSqlAstConverter.java:440)
at org.hibernate.query.sqm.tree.select.SqmQuerySpec.accept(SqmQuerySpec.java:127)
at org.hibernate.query.sqm.spi.BaseSemanticQueryWalker.visitQueryPart(BaseSemanticQueryWalker.java:218)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitQueryPart(BaseSqmToSqlAstConverter.java:1915)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitSelectStatement(BaseSqmToSqlAstConverter.java:1600)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.visitSelectStatement(BaseSqmToSqlAstConverter.java:440)
at org.hibernate.query.sqm.tree.select.SqmSelectStatement.accept(SqmSelectStatement.java:228)
at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.translate(BaseSqmToSqlAstConverter.java:776)
at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.buildCacheableSqmInterpretation(ConcreteSqmSelectQueryPlan.java:402)
at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.withCacheableSqmInterpretation(ConcreteSqmSelectQueryPlan.java:327)
at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.performList(ConcreteSqmSelectQueryPlan.java:303)
at org.hibernate.query.sqm.internal.QuerySqmImpl.doList(QuerySqmImpl.java:509)
at org.hibernate.query.spi.AbstractSelectionQuery.list(AbstractSelectionQuery.java:427)
at org.hibernate.query.Query.getResultList(Query.java:120)
at io.quarkus.hibernate.orm.panache.common.runtime.CommonPanacheQueryImpl.list(CommonPanacheQueryImpl.java:280)
at io.quarkus.hibernate.orm.panache.runtime.PanacheQueryImpl.list(PanacheQueryImpl.java:149)
at ai.efs.weischer.model.Validity.getLocationsWithTimeSlotToSegmentAndLocationIdsByTuple(Validity.java:153)
at ai.efs.weischer.service.SegmentService.getCompleteSegmentById2(SegmentService.java:76)
at ai.efs.weischer.service.SegmentService_ClientProxy.getCompleteSegmentById2(Unknown Source)
at ai.efs.weischer.controller.rest.SegmentController.getSegmentById2(SegmentController.java:91)
at ai.efs.weischer.controller.rest.SegmentController_Subclass.getSegmentById2$$superforward(Unknown Source)
at ai.efs.weischer.controller.rest.SegmentController_Subclass$$function$$16.apply(Unknown Source)
at io.quarkus.arc.impl.AroundInvokeInvocationContext.proceed(AroundInvokeInvocationContext.java:73)
at io.quarkus.arc.impl.AroundInvokeInvocationContext$NextAroundInvokeInvocationContext.proceed(AroundInvokeInvocationContext.java:97)
at io.quarkus.security.runtime.interceptor.SecurityHandler.handle(SecurityHandler.java:27)
at io.quarkus.security.runtime.interceptor.AuthenticatedInterceptor.intercept(AuthenticatedInterceptor.java:29)
at io.quarkus.security.runtime.interceptor.AuthenticatedInterceptor_Bean.intercept(Unknown Source)
at io.quarkus.arc.impl.InterceptorInvocation.invoke(InterceptorInvocation.java:42)
at io.quarkus.arc.impl.AroundInvokeInvocationContext.proceed(AroundInvokeInvocationContext.java:70)
at io.quarkus.arc.impl.AroundInvokeInvocationContext$NextAroundInvokeInvocationContext.proceed(AroundInvokeInvocationContext.java:97)
at io.quarkus.narayana.jta.runtime.interceptor.TransactionalInterceptorBase.invokeInOurTx(TransactionalInterceptorBase.java:136)
at io.quarkus.narayana.jta.runtime.interceptor.TransactionalInterceptorBase.invokeInOurTx(TransactionalInterceptorBase.java:107)
at io.quarkus.narayana.jta.runtime.interceptor.TransactionalInterceptorRequired.doIntercept(TransactionalInterceptorRequired.java:38)
at io.quarkus.narayana.jta.runtime.interceptor.TransactionalInterceptorBase.intercept(TransactionalInterceptorBase.java:61)
at io.quarkus.narayana.jta.runtime.interceptor.TransactionalInterceptorRequired.intercept(TransactionalInterceptorRequired.java:32)
at io.quarkus.narayana.jta.runtime.interceptor.TransactionalInterceptorRequired_Bean.intercept(Unknown Source)
at io.quarkus.arc.impl.InterceptorInvocation.invoke(InterceptorInvocation.java:42)
at io.quarkus.arc.impl.AroundInvokeInvocationContext.proceed(AroundInvokeInvocationContext.java:70)
at io.quarkus.arc.impl.AroundInvokeInvocationContext.proceed(AroundInvokeInvocationContext.java:62)
at io.quarkus.resteasy.reactive.server.runtime.StandardSecurityCheckInterceptor.intercept(StandardSecurityCheckInterceptor.java:44)
at io.quarkus.resteasy.reactive.server.runtime.StandardSecurityCheckInterceptor_AuthenticatedInterceptor_Bean.intercept(Unknown Source)
at io.quarkus.arc.impl.InterceptorInvocation.invoke(InterceptorInvocation.java:42)
at io.quarkus.arc.impl.AroundInvokeInvocationContext.perform(AroundInvokeInvocationContext.java:30)
at io.quarkus.arc.impl.InvocationContexts.performAroundInvoke(InvocationContexts.java:27)
at ai.efs.weischer.controller.rest.SegmentController_Subclass.getSegmentById2(Unknown Source)
at ai.efs.weischer.controller.rest.SegmentController_ClientProxy.getSegmentById2(Unknown Source)
at ai.efs.weischer.controller.rest.SegmentController$quarkusrestinvoker$getSegmentById2_4b732ff4c8c7f72a964cdb1a2e32dd218a3fa90d.invoke(Unknown Source)
at org.jboss.resteasy.reactive.server.handlers.InvocationHandler.handle(InvocationHandler.java:29)
at io.quarkus.resteasy.reactive.server.runtime.QuarkusResteasyReactiveRequestContext.invokeHandler(QuarkusResteasyReactiveRequestContext.java:141)
at org.jboss.resteasy.reactive.common.core.AbstractResteasyReactiveContext.run(AbstractResteasyReactiveContext.java:147)
at io.quarkus.vertx.core.runtime.VertxCoreRecorder$14.runWith(VertxCoreRecorder.java:582)
at org.jboss.threads.EnhancedQueueExecutor$Task.run(EnhancedQueueExecutor.java:2513)
at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1538)
at org.jboss.threads.DelegatingRunnable.run(DelegatingRunnable.java:29)
at org.jboss.threads.ThreadLocalResettingRunnable.run(ThreadLocalResettingRunnable.java:29)
at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
at java.base/java.lang.Thread.run(Thread.java:1583)"

I’ve written the follwing AttributeConverter as I read here that this is the correct aproach instead of UserType.

public class TupleTypeConverter implements
        AttributeConverter<Tuple, String> {


    private static final String SEPARATOR = ", ";

    @Override
    public String convertToDatabaseColumn(Tuple tuple) {
        if (tuple == null) {
            return null;
        }

        StringBuilder sb = new StringBuilder();
        if (tuple.getFirst() != null && tuple.getSecond() != null) {
            sb.append('(');
            sb.append(tuple.getFirst());
            sb.append(SEPARATOR);
            sb.append(tuple.getSecond());
            sb.append(')');
        }
        return sb.toString();
    }

    @Override
    public Tuple convertToEntityAttribute(String tupleString) {
        if (tupleString == null || tupleString.isEmpty()) {
            return null;
        }
        String[] pieces = tupleString.split(SEPARATOR);
        if (pieces.length == 0) {
            return null;
        }
        Long first = Long.valueOf(pieces[0].substring(1));
        Long second = Long.valueOf(pieces[1].substring(0, pieces[1].length() - 1));
        return new Tuple(first, second);
    }
}

I register my converter in my root package (as I dont use the Tuple type IN my Entity but just for the query) via a package-info.java (@ConverterRegistration(converter = TupleTypeConverter.class)).

This seems to stem from this particular line: hibernate-orm/hibernate-core/src/main/java/org/hibernate/metamodel/model/domain/internal/ArrayTupleType.java at main · hibernate/hibernate-orm · GitHub

I’ve read that this is done to prevent hibernate from failing in database enviroments that do not support lists/tuple types. But I susspect there IS a way to archive this behavior but I am cloules what I can do diffrent.

Can someone enlighten me how I can tell hibernate to unpack my list of tuples?

2

As the exception implies, this is simply unsupported at this time. You can create a feature request for this if you want.
In the meantime, I would suggest you to rather use a query like this:

from myentity e
where association in :objects

and bind like:

public static List<MyEntity> queryWithTuples(List<Tuple> tuples) {
        List<Association> objects = tuples.stream().map(t -> entityManager.getReference(Association.class, new AssociationId(t.get(0, Long.class), t.get(1, Long.class)))).toList();
        return find(
                "#Validity.queryWithTuples",
                Parameters
                        .with("objects", objects)
        ).project(MyEntity.class).list();
    }
}
1 Like
3

Thanks for the answer, would it be okay to ask if you can explain your code in more detail?

What exactly does the use of an association change and would this mean that in the stream with entityManager.getReference(...) a new query is sent against the DBMS each time?

4

Hibernate ORM supports the use of parameter lists for everything but tuple parameters, so if you want to use a tuple lookup in SQL, you will have to use a managed type like an embedded id or the association itself.
Read the documentation of getReference(), it will return an existing object form the persistence context or create a new proxy.

Alternative is:

from myentity e
where association.id in :ids

with

List<AssociationId> ids = tuples.stream().map(t -> new AssociationId(t.get(0, Long.class), t.get(1, Long.class))).toList();
1 Like