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 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)
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[]
)
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 (?, ?, ?, ?)
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])
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])
I've tried with one-dimensional arrays so far, but PostgreSQL can handle multi-dimensional arrays as well.
@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}}}
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