Question 268958 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Jooq converter from Postgres array to scala list

I have a scala case class

case class MyRecord(
    id: String,
    dimensions: List[String], // postgres text[]
    dimensionCodes: List[Long] // postgres integer[]
)

and a Jooq query

        JooqBuilder
          .default(conn)
          .select(
            field("id", classOf[String]),
            field("dimensions", classOf[List[String]]),
            field("dimensionCodes", classOf[List[Long]]),
           
          )
          .from(table(MY_TABLE))
        
          .fetchInto(classOf[MyRecord])
          .asScala
          .toList

Ofc, it doesn’t work since jooq can’t map array to scala List.
I’m actively using org.jooq.Converter which is quite cool and flexible thing to have. I don’t understand how to make it for Postgres array

UPD
What I’ve tried:

class JooqArrayConverterOld extends Converter[PgArray, List[Long]]

doesn’t compile. Got deeper into the code, seems like jooq makes it much-much easier for the user. DB type should be Array[Object]

I think I’ve found a right way to do it.

class JooqStringArrayConverter extends Converter[Array[Object], List[String]]

  private val jooqStringArrayConverter = org.jooq.impl.SQLDataType.OTHER.getArrayDataType.asConvertedDataType(
    new JooqStringArrayConverter()
  )

worked well for
field("dimensions", jooqStringArrayConverter) so this case closed.
Still struggling with List[Long]


class JooqLongArrayConverter extends Converter[Array[Object], List[Long]] {
  override def from(databaseObject: Array[Object]): List[Long] =
    Option(databaseObject)
      .map(_.asInstanceOf[Array[Int]]) // Exception!
      .map(_.toList.map(_.toLong))
      .getOrElse(List.empty)

  override def to(userValue: List[Long]): Array[Object] =
    ???

  override def fromType(): Class[Array[Object]] = classOf[Array[Object]]

  override def toType: Class[List[Long]] = classOf[List[Long]]
}

throws

Error while reading field: dimensionCodes, at JDBC index: 10
...
Cause: java.lang.ClassCastException: class [Ljava.lang.Integer; cannot be cast to class [I ([Ljava.lang.Integer; and [I are in module java.base of loader 'bootstrap')

I guess again painful java primitives. Hope we will get rid of them some time in the future.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    This worked worked for me.

    class JooqLongArrayConverter extends Converter[Array[Object], List[Long]] {
  override def from(databaseObject: Array[Object]): List[Long] =
    Option(databaseObject)
      .map(_.map(e => e.asInstanceOf[Int]))
      .map(_.toList.map(_.toLong))
      .getOrElse(List.empty)

  override def to(userValue: List[Long]): Array[Object] =
    ???

  override def fromType(): Class[Array[Object]] = classOf[Array[Object]]

  override def toType: Class[List[Long]] = classOf[List[Long]]
}


  private val jooqLongArrayConverter = org.jooq.impl.SQLDataType.OTHER.getArrayDataType.asConvertedDataType(
    new JooqLongArrayConverter()
  )

          .select(
            field("dimensionCodes", jooqLongArrayConverter)
//
).from(table(MY_TABLE))

  2. 0

    The Scala type Array[Int] corresponds to int[] on the JVM (encoded as [I). You want it to be java.lang.Integer[], so use Array[java.lang.Integer] instead.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search