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...

Be aware that support for Array is spotty at best in most drivers (if the database itself supports them at all). — Mark Rotteveel, May 28 '15 at 06:36

yǝsʞǝla · Accepted Answer · 2015-05-28T06:34:22.587

5

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)

edited May 28 '15 at 06:34

answered May 28 '15 at 06:10

yǝsʞǝla

16,272
2
44
65

Thanks... This much I'm aware of (although you use `createArrayOf(string, array)` whereas I'm using `createArray(intType, array)` – not sure if that's an important difference. Problem is, I can't seem to get my Option[List[_]] instances converted into a Java Array. That's the part that I am chiefly stuck on. Thanks for confirming how the Java/JDBC end of it works though. – Zaphod May 28 '15 at 06:18
Oh, do you mean the pattern match itself with extraction? – yǝsʞǝla May 28 '15 at 06:20
That gets me closer... Unfortunately just passing a Scala `Array` to JDBC's `createArray()` doesn't work... Tried a cast using `asInstanceOf[java.sql.Array]` but that results in `java.lang.ClassCastException: [Ljava.lang.Object; cannot be cast to java.sql.Array`... Still working on it. – Zaphod May 28 '15 at 08:53
Scala array can be used in place of Java array. However, in your case you need `java.sql.Array` which is not the same as plain Java array `[]`. Thus you need to convert scala/java array to `java.sql.Array` as I explained in my answer. – yǝsʞǝla May 28 '15 at 10:11
1

Yes... and that is non-trivial. We posted this to support, and got back the following regarding how to go about the conversion: `callableStatement.setArray(index, callableStatement.getConnection.createArrayOf("NUMERIC", v.get.toArray[Any].asInstanceOf[Array[AnyRef]]))` although we are still working on implementing it. – Zaphod May 28 '15 at 12:42
@Zac, that's similar to what I was saying except for the casting part. How about a completely different approach: create a java class that deals with it and call it from scala. Basically a Java wrapper which can be even compiled with regular scala build tools like maven or sbt. Unless the whole problem is not in conversion but in some buggy/non-portable JDBC driver implementation. Please post what you find out, I'm very curious. – yǝsʞǝla May 28 '15 at 14:30

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

1 Answers1

Linked