Passing Scala Option[List[_]] to Java JDBC Array using statement.setArray()

Question

I'm trying to build an interface from Scala to JDBC callableStatements. For the most part, it's straightforward, except for Lists.

I need to be able to take a Scala List of some type, and convert it into a Java Array that can be passed to statement.setArray(type, array) and I'm not having any luck (partly because I don't know Java and JDBC very well).

Here's what I'm trying to do:

for (parameter <- ps.parameters) {
    case GPArrayIn(None, t) => callableStatement.setNull(index, t)
    case GPIn(v: Some[_], Types.INTEGER) => callableStatement.setInt(index, v.get.asInstanceOf[Int])
    case GPIn(v: Some[_], Types.VARCHAR | Types.LONGVARCHAR) => callableStatement.setString(index, v.get.asInstanceOf[String])
    case GPArrayIn(v: Some[List[_]], Types.INTEGER) => callableStatement.setArray(Types.INTEGER, ???? )
    case GPArrayIn(v: Some[List[_]], Types.VARCHAR | Types.LONGVARCHAR) => callableStatement.setArray(Types.VARCHAR, ???? )
    ...

It's pretty straightforward for simple values but when it comes to the setArray() calls I'm stuck.

Any advice would be sorely appreciated. Been stuck on this for hours...

Answer 1

setArray takes a java.sql.Array as described in the doc:

void setArray(int parameterIndex, Array x)
          throws SQLException

You can create one with:

sqlArray = connection.createArrayOf("VARCHAR", regularJavaArray);

which is of type:

Array createArrayOf(String typeName, Object[] elements)
                throws SQLException

See this doc for examples and explanation if needed.

Bottom line: you need to convert Scala collection to java array if it's not an array already, then convert that Java array to SQL array using createArrayOf. It will do some under the hood magic to map that data to SQL ARRAY.

As for the pattern matching and extraction, you can use something like that:

scala> val numbers = Array(1, 2, 3, 4)
numbers: Array[Int] = Array(1, 2, 3, 4)

scala> def arrayMatcher[T](maybeArray:  Option[Array[T]]): String =
     |     maybeArray match {
     |       case Some(a: Array[Int]) => a.mkString(",")
     |       case Some(b: Array[String]) => b.mkString("-")
     |       case None => "no array"
     |       case _ => "no match"
     |     }
arrayMatcher: [T](maybeArray: Option[Array[T]])String

scala> arrayMatcher(Some(numbers))
res0: String = 1,2,3,4

scala> arrayMatcher(None)
res1: String = no array

scala> arrayMatcher(Some(numbers map(_.toString)))
res2: String = 1-2-3-4

scala> arrayMatcher(Some(Array(1.2, 3.4)))
res3: String = no match

scala> arrayMatcher(Some(List(1, 2)))
<console>:9: error: type mismatch;
 found   : Some[List[Int]]
 required: Option[Array[?]]
              arrayMatcher(Some(List(1, 2)))
                               ^

To convert a list to array use:

scala> List(1, 2, 3).toArray
res6: Array[Int] = Array(1, 2, 3)