1

I try to implement this sort of SQL query in Hibernate native.

val list = listOf((z,1),(w,2),(y,3))
SELECT * FROM table WHERE (a, b) IN (:list)

to finally obtain something like:

SELECT * FROM table WHERE (a, b) IN ((z,1),(w,2),(y,3))

I try several implementation with custom UserType or with inspiration of Hibernate-Types from Vlad Mihalcea, but i can't get it.

Is there in Hibernate a way to convert a parameter or a list of parameters to tuple or tuple list?

EDIT

I've succeed to do it, but i'm not 100% glad of it.

To give more context, the couple (a, b) are of types (uuid, timestamp). For this solution work, i need to create a Postgres complex type:

CREATE TYPE pair_uuid_timestamp AS (
  first uuid,
  second timestamp
);

Then, i need a PostgreSQLPairType based on ImmutableType from hibernate-types-52. It's easy to avoid it, but i've already have it.

class PostgreSQLPairType(
  val first: UUID?,
  val second: LocalDateTime?
) : ImmutableType<PostgreSQLPairType>(PostgreSQLPairType::class.java) {

  constructor(): this(null, null)

  override fun sqlTypes(): IntArray {
    return intArrayOf(Types.OTHER)
  }

  @Throws(SQLException::class)
  override fun get(rs: ResultSet, names: Array<String>, session: SharedSessionContractImplementor, owner: Any): PostgreSQLPairType? {
    return null
  }

  @Throws(SQLException::class)
  override fun set(st: PreparedStatement, pair: PostgreSQLPairType?, index: Int, session: SharedSessionContractImplementor) {
    if (pair == null) {
        st.setNull(index, Types.OTHER)
    } else {
        val pgObject = PGobject()
        pgObject.type = "pair_uuid_timestamp"
        pgObject.value = "(${pair.first},${pair.second?.toInstantStr()})"
        st.setObject(index, pgObject)
    }
  }
}

Today, i don't use it for now, i don't see any difference with my parameter list construct like this:

val list = listOf((uuid1,timestamp1),(uuid2,timestamp2),(uuid3,timestamp3))
val parameter = list.joinToString(",") { "('${it.first}', '${it.second.toInstantStr()}')" }
SELECT * FROM table WHERE (a, b) IN (:list)
Jerome Dupont
  • 203
  • 2
  • 7
  • Does this answer your question? [JPQL and list of tuples as parameter for SELECT IN statements](https://stackoverflow.com/questions/55317347/jpql-and-list-of-tuples-as-parameter-for-select-in-statements) – crizzis Oct 06 '20 at 18:33
  • I've already try this without success. I've edit my post with a working solution! – Jerome Dupont Oct 07 '20 at 08:53
  • But your comment help me a lot digging in hibernate type parameter resolution. /* 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 */ – Jerome Dupont Oct 07 '20 at 09:06

0 Answers0