Try manipulating PostgreSQL arrays with JDBC

Array

PostgreSQL has array types.

JDBC also has an array type.

When thinking about the DB configuration, if you want an array, you feel like you've lost. I've never thought of using array types until now. However, even in recent projects, there seems to be a scene where I use an array without feeling lost, so I did a little research. That memo.

Data class for testing and main testing


  /**
   */
  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"))
    }
  }

The version.

              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)

table definition

Array types in PostgreSQL are defined with [].

  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[]
)

Data input

The Array instance is Connection # createArrayOf () Generated by .lang.Object: A-) method. Note that the type name is specified as follows.

A database-specific name that is built-in, user-defined, or a standard SQL type supported by this database. This is the value returned by 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 (?, ?, ?, ?)

Get a record

  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("Unknown column(${index}: ${meta.getColumnName(index)})")
                  }
                }
          }

          return seledtedRecords.toList()
        }
      }

In order to get the contents of the array, we need to get the ResultSet from the Array type. This ResultSet returns index and value.

The tricky part here is that the ** subscripts in the array don't necessarily have to start at 1.

8.15.4. Change the array ... You can create an array with subscripts other than 1 by subscripting. For example, you can specify an array with subscripts from -2 to 7 with array [-2: 7].

So, if you simply return it as an array or List, it may be more troublesome to receive it, so return a Map with index as the key.

  /**
   *Array type[sqlArray]Is converted to a Map type whose subscript is the key.
   */
  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("Unsupported type${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])

Update data

  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])

Multidimensional array

I've tried with one-dimensional arrays so far, but PostgreSQL can handle multi-dimensional arrays as well.

Reading a two-dimensional array


  @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)
            }
      }

  /**
   *Array type[sqlArray]Is converted to a Map type whose subscript is the key.
   */
  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("Unsupported type${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("Unknown column(${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}}}

Multidimensional array update

Of course, if you write an insert statement as above, you can insert data. So what do you do with JDBC?

Following the one-dimensional example, you have to create an array of arrays with #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()
    }
  }

Checking #getColumnTypeName () for a two-dimensional array of text type returns _text. So, I think it's okay to specify the type name of #createArrayOf () with _text, but it seems to be different.

----.----+----.----+----.----+----.----+----.----+----.----+
insert into ttt2 (id, name, items2, numbers2) values (?, ?, ?, ?)
Exception in thread "main" org.postgresql.util.PSQLException:
Specified name_There is no server array type for text.
	at org.postgresql.jdbc.PgConnection.createArrayOf(PgConnection.java:1425)
	at PgArrayTest3.insertData(PgArrayTest3.kt:147)
	at PgArrayTest3.main(PgArrayTest3.kt:54)
    ...

Nor is it text [].

Exception in thread "main" org.postgresql.util.PSQLException:
Specified name text[]There is no server array type for.

Data input. Clogged state.

Recommended Posts

Try manipulating PostgreSQL arrays with JDBC
Try building Express + PostgreSQL + Sequelize with Docker [Part 2]
Try building Express + PostgreSQL + Sequelize with Docker [Part 1]
Try DI with Micronaut
Try create with Trailblazer
Try WebSocket with jooby
Try WildFly with Docker
Try using Spring JDBC
Try using GloVe with Deeplearning4j
Manipulating List with java8StreamAPI :: reduce
Try using view_component with rails
Try DB connection with Java
Try gRPC with Java, Maven
Use Spring JDBC with Spring Boot
I tried UPSERT with PostgreSQL.
Try reading XML with JDOM
Use SpatiaLite with Java / JDBC
Design patterns to try with Swift-Iterator patterns that support Arrays and Dictionary