JPQL and list of tuples as parameter for SELECT IN statements

Given the following table layout:

CREATE TABLE things (
    id      BIGINT PRIMARY KEY NOT NULL,
    foo     BIGINT NOT NULL,
    bar     BIGINT NOT NULL
);

An entity class (Kotlin):

@Entity
@Table(name = "things")
class Thing(
        val foo: Long,
        val bar: Long
) : AbstractPersistable<Long>()

And a repository:

interface ThingRepository : JpaRepository<Thing, Long> {
@Query("SELECT t FROM Thing t WHERE t.foo IN ?1")
fun selectByFoos(foos: Iterable<Long>): Iterable<Thing>

@Query("SELECT t FROM Thing t WHERE (t.foo, t.bar) IN ((1, 2), (3, 4))")
fun selectByFoosAndBarsFixed(): Iterable<Thing>

@Query("SELECT t FROM Thing t WHERE (t.foo, t.bar) IN ?1")
fun selectByFoosAndBars(foosAndBars: Iterable<Pair<Long, Long>>): Iterable<Thing>

The following two calls work fine:

repo.selectByFoos(listOf(1L, 3L))
repo.selectByFoosAndBarsFixed()

However the this one does not:

repo.selectByFoosAndBars(listOf(Pair(1L, 2L), Pair(3L, 4L)))

It throws:

org.springframework.dao.DataIntegrityViolationException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.DataException: could not extract ResultSet

Caused by: org.h2.jdbc.JdbcSQLException: Data conversion error converting "aced00057372000b6b6f746c696e2e50616972fa1b06813de78f780200024c000566697273747400124c6a6176612f6c616e672f4f626a6563743b4c00067365636f6e6471007e000178707372000e6a6176612e6c616e672e4c6f6e673b8be490cc8f23df0200014a000576616c7565787200106a6176612e6c616e672e4e756d62657286ac951d0b94e08b020000787000000000000000017371007e00030000000000000002"; SQL statement:
/* SELECT t FROM Thing t WHERE (t.foo, t.bar) IN ?1 */ select thing0_.id as id1_0_, thing0_.bar as bar2_0_, thing0_.foo as foo3_0_ from things thing0_ where (thing0_.foo , thing0_.bar) in (? , ?) [22018-197]

Caused by: java.lang.NumberFormatException: For input string: "aced00057372000b6b6f746c696e2e50616972fa1b06813de78f780200024c000566697273747400124c6a6176612f6c616e672f4f626a6563743b4c00067365636f6e6471007e000178707372000e6a6176612e6c616e672e4c6f6e673b8be490cc8f23df0200014a000576616c7565787200106a6176612e6c616e672e4e756d62657286ac951d0b94e08b020000787000000000000000017371007e00030000000000000002"

I guess the elements of the list passed as parameter are not inserted correctly into the query. How can I correct this?

Sure, I could manually build the query, like so:

@Repository
class SecondThingRepository(private val entityManager: EntityManager) {
    fun selectByFoosAndBars(foosAndBars: Iterable<Pair<Long, Long>>): Iterable<Thing> {
        val pairsRepr = foosAndBars.joinToString(prefix = "(", postfix = ")") { "(${it.first}, '${it.second}')" }
        val query: TypedQuery<Thing> = entityManager.createQuery("SELECT t FROM Thing t WHERE (t.foo, t.bar) IN $pairsRepr", Thing::class.java)
        return query.resultList
    }
}

But this seems note very good.

Solution

First of all, a word of warning: not all databases support the (t.foo, t.bar) IN ((1, 2), (3, 4)) syntax. Using it makes your application non-portable.

I'm assuming the number of Pairs in the List can be arbitrary (if not, there's a much simpler solution involving the modification of the IN expression to e.g. IN (?1, ?2, ?3) and updating the query method to accept three parameters of type List. I suppose that's not what you're asking for, though).

The problem is that Hibernate does not know how to map the Pair class onto a database type. It also seems that the type resolution logic of collection elements is different from the resolution logic for the 'outer' type, so listOf(listOf(1L, 2L), listOf(3L, 4L)) won't work, either.

The solution (and it's a bit of a hack, mind you) is to introduce a Hibernate's UserType capable of mapping Pair objects AND to use this newly created PairType for the elements of the List.

First of all, add the following class to your project:

/* It is absolutely crucial that this class extend Pair. If the Pair class you're using
happens to be final, you will have to implement a Pair class yourself. 
For an explanation of why this is required, have a look at SessionFactory.resolveParameterBindType()
and TypeResolver.heuristicType() methods */
public class PairType extends Pair<Long, Long> implements UserType { 

    public PairType(Long first, Long second) {
        super(first, second);
    }

    public PairType() {
        super(null, null);
    }

    @Override
    public int[] sqlTypes() {
        return new int[] {Types.ARRAY};
    }

    @Override
    public Class returnedClass() {
        return Pair.class;
    }

    @Override
    public void nullSafeSet(PreparedStatement st, Object value, int index, SharedSessionContractImplementor session)
            throws HibernateException, SQLException {
        if (Objects.isNull(value)) {
            st.setNull(index, Types.ARRAY);
        } else {
            final Pair pair = (Pair) value;
            st.setArray(index, new Array() {


                @Override
                public Object getArray() throws SQLException {
                    // TODO Auto-generated method stub
                    return new Object[] {pair.getFirst(), pair.getSecond()};
                }

                ...
                //you can leave the rest of the autogenerated method stubs as they are

            });
        }
    }

    @Override
    public Object deepCopy(Object value) throws HibernateException {
        if (Objects.isNull(value)) {
            return null;
        }
        return Pair.of(((Pair) value).getFirst(), ((Pair) value).getSecond());
    }

    @Override
    public boolean isMutable() {
        return false;
    }

    ...
    //you can leave the rest of the autogenerated method stubs as they are here as well

}

Then, modify your method signature to:

selectByFoosAndBars(foosAndBars: Iterable<PairType>): Iterable<Thing>

Note: the above solution worked for me out of the box for the H2 database. Your mileage may vary.