#StackBounty: #hibernate #spring-boot #jpa #kotlin #jpql JPQL and list of tuples as parameter for SELECT IN statements

Bounty: 50

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.


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.