配列

PostgreSQLには配列型がある。

配列 https://www.postgresql.jp/document/11/html/arrays.html

JDBCにも配列型がある。

java.sql.Array

DB構成を考えるとき、配列が欲しくなったら負けたような気分になるので、
いままで配列型を使おうとおもったことはなかった。けれど、最近の案件でもしかしたら負けた気分にならずに配列を使う場面がありそうなので、ちょっと調べてみた。そのメモ。

テスト用のdata classとテストのメイン


  /**
   */
  data class HogeData(
      val id: Int = Int.MIN_VALUE,
      val name: String = "",
      val items: List<String> = listOf(),
      val numbers: List<Int> = listOf()
  )
  
  @JvmStatic
  fun main(args: Array<String>) {
    DriverManager.getConnection("jdbc:postgresql://localhost:5432/test", "xxx", "xxx").use { conn ->

      println("              conn::class.java: ${conn::class.java}")
      println("      conn.metaData.driverName: ${conn.metaData.driverName}")
      println("   conn.metaData.driverVersion: ${conn.metaData.driverVersion}")
      println("conn.metaData.jdbcMajorVersion: ${conn.metaData.jdbcMajorVersion}")
      println("conn.metaData.jdbcMinorVersion: ${conn.metaData.jdbcMinorVersion}")
      println("conn.metaData.databaseProductVersion: ${conn.metaData.databaseProductVersion}")

      val separator = "----.----+".repeat(6)

      //
      println(separator)
      createTable(conn)

      //
      println(separator)
      val initialData = listOf(
          HogeData(id = 123, name = "hogege", items = listOf("aa", "zz"), numbers = listOf(111, 222, 333, 444, 555)),
          HogeData(id = 987, name = "foo", items = listOf("bar", "hoge", "chome"), numbers = (1..30).toList())
      )
      insertData(conn, initialData)

      //
      println(separator)
      val selectedDataA = selectData(conn)
      println(selectedDataA.joinToString("\n"))

      //
      println(separator)
      val newData = selectedDataA
          .map{hoge ->
            hoge.copy(
                items = (hoge.items.map{it.toUpperCase()} + listOf("xxx")).shuffled(),
                numbers = hoge.numbers.map{it  * it}
            )
          }
      updateData(conn, newData)

      //
      println(separator)
      val selectedDataB = selectData(conn)
      println(selectedDataB.joinToString("\n"))
    }
  }

バージョンとか。

              conn::class.java: class org.postgresql.jdbc.PgConnection
      conn.metaData.driverName: PostgreSQL JDBC Driver
   conn.metaData.driverVersion: 42.2.12
conn.metaData.jdbcMajorVersion: 4
conn.metaData.jdbcMinorVersion: 2
conn.metaData.databaseProductVersion: 11.7 (Debian 11.7-0+deb10u1)

テーブル定義

PostgreSQLの配列型は[]をつけて定義する。

  private val tableName = "ttt"

  //
  fun createTable(conn: Connection) =
      conn.createStatement().use { stmt ->
        val sqls = arrayOf(
            """
drop table if exists ${tableName}
            """.trimIndent(),
            """
create table ${tableName} (
    id int,
    name text,
    items text[],
    numbers int[]
)
            """.trimIndent(),
            "")
            .filter { it.isNotBlank() }
            .forEach { sql ->
              println(sql)
              stmt.execute(sql)
            }
      }

----.----+----.----+----.----+----.----+----.----+----.----+
drop table if exists ttt
create table ttt (
    id int,
    name text,
    items text[],
    numbers int[]
)

データ投入

Arrayのインスタンスは、Connection#createArrayOf()メソッドで生成する。なお、型名の指定は次のとおりなので、注意。

データベース固有の名前で、組込み型、ユーザー定義型、またはこのデータベースでサポートされる標準SQL型の名前のこと。これは、Array.getBaseTypeNameで返される値

  fun insertData(conn: Connection, newData:List<HogeData>) {
    val sql = """
insert into ${tableName} (id, name, items, numbers) values (?, ?, ?, ?)
      """.trimIndent()
    println(sql)

    conn.prepareStatement(sql).use { pstmt ->
      newData.forEach { hoge ->
        pstmt.setInt(1, hoge.id)
        pstmt.setString(2, hoge.name)
        val items = conn.createArrayOf("text", hoge.items.toTypedArray())
        pstmt.setArray(3, items)
        val numbers = conn.createArrayOf("int", hoge.numbers.toTypedArray())
        pstmt.setArray(4, numbers)
        pstmt.addBatch()
      }
      pstmt.executeBatch()
    }
  }

----.----+----.----+----.----+----.----+----.----+----.----+
insert into ttt (id, name, items, numbers) values (?, ?, ?, ?)

レコードを取得する

  fun selectData(conn: Connection): List<HogeData> =
      conn.createStatement().use { stmt ->
        val seledtedRecords = mutableListOf<HogeData>()

        val sql = "select id, name, items, numbers from ${tableName}"
        println(sql)

        stmt.executeQuery(sql).use { rs ->
          val meta = rs.metaData
          while (rs.next()) {
            seledtedRecords +=
                (1..meta.columnCount).fold(HogeData()) { hoge, index ->
                  when (index) {
                    1 -> hoge.copy(id = rs.getInt(index))
                    2 -> hoge.copy(name = rs.getString(index))
                    3 -> hoge.copy(items = sqlArrayToIndexValueMap(rs.getArray(index)).values.toList() as List<String>)
                    4 -> hoge.copy(numbers = sqlArrayToIndexValueMap(rs.getArray(index)).values.toList() as List<Int>)
                    else -> error("未知のカラム(${index}: ${meta.getColumnName(index)})")
                  }
                }
          }

          return seledtedRecords.toList()
        }
      }

配列の内容を取得するために、Array型からResultSetを取得する必要がある。このResultSetは、indexとvalueを返す。

ここでややこしいのは、配列の添字は必ずしも1から始まる必要はないということ。

配列 https://www.postgresql.jp/document/11/html/arrays.html

8.15.4. 配列の変更
...添字指定の代入で1始まり以外の添字がある配列を作れます。例えば添字が-2から7までの値を持つ配列をarray[-2:7]で指定できます。

ということなので、単純に配列やListで返すと、受け取ったほうが困るかもしれないので、indexをキーとしたMapを返す。

  /**
   * 配列型[sqlArray]を、添字がキーのMap型に変換する。
   */
  fun sqlArrayToIndexValueMap(sqlArray: java.sql.Array): Map<Int, Any> {
    val INDEX_INDEX = 1
    val VALUE_INDEX = 2
    
    sqlArray.resultSet.use { rs ->
      val meta = rs.metaData
      val values = mutableMapOf<Int, Any>()
      while (rs.next()) {
        val index = rs.getInt(INDEX_INDEX)
        values += (
            index to
                when (meta.getColumnType(VALUE_INDEX)) {
                  Types.ARRAY -> sqlArrayToIndexValueMap(rs.getArray(VALUE_INDEX))
                  else ->
                    when (sqlArray.baseType) {
                      Types.BIGINT -> rs.getLong(VALUE_INDEX)
                      Types.CHAR -> rs.getString(VALUE_INDEX)
                      Types.INTEGER -> rs.getInt(VALUE_INDEX)
                      Types.NUMERIC -> rs.getBigDecimal(VALUE_INDEX)
                      Types.VARCHAR -> rs.getString(VALUE_INDEX)
                      else -> error("サポートしていない型${sqlArray.baseTypeName}(${sqlArray.baseType})")
                    }
                })
      }

      return values
    }
  }

----.----+----.----+----.----+----.----+----.----+----.----+
select id, name, items, numbers from ttt
HogeData(id=123, name=hogege, items=[aa, zz], numbers=[111, 222, 333, 444, 555])
HogeData(id=987, name=foo, items=[bar, hoge, chome], numbers=[1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30])

データを更新する

  fun updateData(conn: Connection, insertData:List<HogeData>) {
    val sql = """
update ${tableName} set id = ?, name = ?, items = ?, numbers = ? where id = ?
      """.trimIndent()
    println(sql)

    conn.prepareStatement(sql).use { pstmt ->
      insertData.forEach { hoge ->
        pstmt.setInt(1, hoge.id)
        pstmt.setInt(5, hoge.id)
        pstmt.setString(2, hoge.name)
        val items = conn.createArrayOf("text", hoge.items.toTypedArray())
        pstmt.setArray(3, items)
        val numbers = conn.createArrayOf("int", hoge.numbers.toTypedArray())
        pstmt.setArray(4, numbers)
        pstmt.addBatch()
      }
      pstmt.executeBatch()
    }
  }

----.----+----.----+----.----+----.----+----.----+----.----+
update ttt set id = ?, name = ?, items = ?, numbers = ? where id = ?
----.----+----.----+----.----+----.----+----.----+----.----+
select id, name, items, numbers from ttt
HogeData(id=123, name=hogege, items=[AA, ZZ, xxx], numbers=[12321, 49284, 110889, 197136, 308025])
HogeData(id=987, name=foo, items=[CHOME, HOGE, BAR, xxx], numbers=[1, 4, 9, 16, 25, 36, 49, 64, 81, 100, 121, 144, 169, 196, 225, 256, 289, 324, 361, 400, 441, 484, 529, 576, 625, 676, 729, 784, 841, 900])

多次元配列

ここまで一次元の配列で試してみたけれど、PostgreSQLは多次元の配列も扱える。

二次元配列の読み込み


  @JvmStatic
  fun main(args: Array<String>) {
    DriverManager.getConnection("jdbc:postgresql://localhost:5432/test", "xxx", "xxx").use { conn ->

      val separator = "----.----+".repeat(6)

      //
      println(separator)
      createTable(conn)

      println(separator)
      println(selectAsMap(conn).joinToString("\n"))
    }
  }

  private val tableName = "ttt2"

  /**
   */
  fun createTable(conn: Connection) =
      conn.createStatement().use { stmt ->
        val sqls = arrayOf(
            """
drop table if exists ${tableName}
            """.trimIndent(),
            """
CREATE TABLE ${tableName} (
    id int,
    name text,
    items2 text[][],
    numbers2 int[][]
)
            """.trimIndent(),
            """
insert into ${tableName} (id, name, items2, numbers2)
  values(-1, 'aaa',
    array[array['a','b']] :: text[],
    array[array[1,2,3],array[4,5,6], array[7,8,9]]
  )
            """.trimIndent(),
            "")
            .filter { it.isNotBlank() }
            .forEach { sql ->
              println(sql)
              stmt.execute(sql)
            }
      }

  /**
   * 配列型[sqlArray]を、添字がキーのMap型に変換する。
   */
  fun sqlArrayToIndexValueMap(sqlArray: java.sql.Array): Map<Int, Any> {
    val INDEX_INDEX = 1
    val VALUE_INDEX = 2
    sqlArray.resultSet.use { rs ->
      val meta = rs.metaData
      val values = mutableMapOf<Int, Any>()
      while (rs.next()) {
        //        (1..meta.columnCount).forEach { idx ->
        //          println("  (${idx}) ${meta.getColumnName(idx)} ${meta.getColumnTypeName(idx)} ${meta.getColumnType(idx)}")
        //        }
        val index = rs.getInt(INDEX_INDEX)
        values += (
            index to
                when (meta.getColumnType(VALUE_INDEX)) {
                  Types.ARRAY -> sqlArrayToIndexValueMap(rs.getArray(VALUE_INDEX))
                  else ->
                    when (sqlArray.baseType) {
                      Types.BIGINT -> rs.getLong(VALUE_INDEX)
                      Types.CHAR -> rs.getString(VALUE_INDEX)
                      Types.INTEGER -> rs.getInt(VALUE_INDEX)
                      Types.NUMERIC -> rs.getBigDecimal(VALUE_INDEX)
                      Types.VARCHAR -> rs.getString(VALUE_INDEX)
                      else -> error("サポートしていない型${sqlArray.baseTypeName}(${sqlArray.baseType})")
                    }
                })
      }

      return values
    }
  }

  /**
   */
  fun selectAsMap(conn: Connection): List<Map<String, Any>> =
      conn.createStatement().use { stmt ->
        val seledtedRecords = mutableListOf<Map<String, Any>>()

        val sql = "select id, name, items2, numbers2 from ${tableName}"
        println(sql)

        stmt.executeQuery(sql).use { rs ->
          val meta = rs.metaData
          while (rs.next()) {
            val row = mutableMapOf<String, Any>()
            (1..meta.columnCount).map { index ->
              row +=
                  (
                      meta.getColumnName(index) to
                          when (index) {
                            1 -> rs.getInt(index)
                            2 -> rs.getString(index)
                            3 -> sqlArrayToIndexValueMap(rs.getArray(index))
                            4 -> sqlArrayToIndexValueMap(rs.getArray(index))
                            else -> error("未知のカラム(${index}: ${meta.getColumnName(index)})")
                          }
                      )
            }

            seledtedRecords += row
          }

          return seledtedRecords.toList()
        }
      }

----.----+----.----+----.----+----.----+----.----+----.----+
drop table if exists ttt2
CREATE TABLE ttt2 (
    id int,
    name text,
    items2 text[][],
    numbers2 int[][]
)
insert into ttt2 (id, name, items2, numbers2)
  values(-1, 'aaa',
    array[array['a','b']] :: text[],
    array[array[1,2,3],array[4,5,6], array[7,8,9]]
  )
----.----+----.----+----.----+----.----+----.----+----.----+
select id, name, items2, numbers2 from ttt2
{id=-1, name=aaa, items2={1={1=a, 2=b}}, numbers2={1={1=1, 2=2, 3=3}, 2={1=4, 2=5, 3=6}, 3={1=7, 2=8, 3=9}}}

多次元配列の更新

上記のようにinsert文を書けば、もちろんデータを投入できる。では、JDBCではどうするのか。

一次元の例に倣えば、#createArrayOf()で配列の配列を生成しなくてはいけない。

  data class HogeData2(
      val id: Int = Int.MIN_VALUE,
      val name: String = "",
      val items2: List<List<String>> = listOf(),
      val numbers2: List<List<Int>> = listOf()
  )

  fun main(args: Array<String>) {
      ...
      val initialData = listOf(
          HogeData2(
              id = 123, name = "hogege",
              items2 = listOf(listOf("aa", "zz")),
              numbers2 = listOf(listOf(111, 222, 333, 444, 555))),
          HogeData2(
              id = 987, name = "foo",
              items2 = listOf(listOf("bar", "hoge", "chome")),
              numbers2 = listOf((1..30).toList()))
      )
      insertData(conn, initialData)
  }

  fun insertData(conn: Connection, newData: List<HogeData2>) {
    val sql = """
insert into ${tableName} (id, name, items2, numbers2) values (?, ?, ?, ?)
      """.trimIndent()

    println(sql)

    conn.prepareStatement(sql).use { pstmt ->
      newData.forEach { hoge ->
        pstmt.setInt(1, hoge.id)
        pstmt.setString(2, hoge.name)
        hoge.items2.map { conn.createArrayOf("text", it.toTypedArray()) }.let { items ->
          conn.createArrayOf("_text", items.toTypedArray()).let { arr ->
            pstmt.setArray(3, arr)
          }
        }
        hoge.numbers2.map { conn.createArrayOf("int", hoge.numbers2.toTypedArray()) }.let { numbers ->
          conn.createArrayOf("_int4", numbers.toTypedArray()).let { arr ->
            pstmt.setArray(4, arr)
          }
        }
        pstmt.addBatch()
      }
      pstmt.executeBatch()
    }
  }

text型の二次元配列の#getColumnTypeName()を確認すると、_textが返される。だから、#createArrayOf()の型名指定するのは_textでよかろうと思うのだけれど、違うらしい。

----.----+----.----+----.----+----.----+----.----+----.----+
insert into ttt2 (id, name, items2, numbers2) values (?, ?, ?, ?)
Exception in thread "main" org.postgresql.util.PSQLException:
                             指定された名前 _text のサーバ配列型はありません。
	at org.postgresql.jdbc.PgConnection.createArrayOf(PgConnection.java:1425)
	at PgArrayTest3.insertData(PgArrayTest3.kt:147)
	at PgArrayTest3.main(PgArrayTest3.kt:54)
    ...

text[]でもない。

Exception in thread "main" org.postgresql.util.PSQLException:
                             指定された名前 text[] のサーバ配列型はありません。

データ投入。詰んだ状態。