# Use tuples as query parameters

**URL:** <https://discourse.hibernate.org/t/use-tuples-as-query-parameters/9690>\
**Category:** Hibernate ORM\
**Created:** [June 4, 2024, 12:00pm UTC](https://discourse.hibernate.org/t/use-tuples-as-query-parameters/9690 "2024-06-04T12:00:24Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![JBartscher](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/jbartscher/32/3260_2.png) [@JBartscher](https://discourse.hibernate.org/u/JBartscher)\
**Post date:** [June 4, 2024, 12:00pm UTC](https://discourse.hibernate.org/t/use-tuples-as-query-parameters/9690/1 "2024-06-04T12:00:24Z")

</div>

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:

```java
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:

```auto
@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:

```auto
"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`.

```java
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](https://github.com/hibernate/hibernate-orm/blob/main/hibernate-core/src/main/java/org/hibernate/metamodel/model/domain/internal/ArrayTupleType.java#L120)

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?

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [June 4, 2024, 2:21pm UTC](https://discourse.hibernate.org/t/use-tuples-as-query-parameters/9690/2 "2024-06-04T14:21:49Z")

</div>

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:

```sql
from myentity e
where association in :objects

```

and bind like:

```java
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();
    }
}

```

---

<div class="post-metadata">

**Author:** ![JBartscher](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/jbartscher/32/3260_2.png) [@JBartscher](https://discourse.hibernate.org/u/JBartscher)\
**Post date:** [June 4, 2024, 3:01pm UTC](https://discourse.hibernate.org/t/use-tuples-as-query-parameters/9690/3 "2024-06-04T15:01:05Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [June 4, 2024, 5:45pm UTC](https://discourse.hibernate.org/t/use-tuples-as-query-parameters/9690/4 "2024-06-04T17:45:44Z")

</div>

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:

```sql
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();

```