JDBC SQLiteでjson_arrayでPreparedStatementを用いて要素を追加しようとして詰まった

# はじめに

SQLite3でJSON形式のデータが詰められるということで、[公式リファレンス](https://sqlite.org/json1.html)を参考に`sqlite.JDBC`を使って試してました。
具体的には、`json_array`関数を用いて`json_object`を配列にして詰めるようなことをやりたかったのですが、これがターミナル上でSQL文を書いてやるのと、JDBCで`PreparedStatement`を書いて詰めるのとではかなり勝手が違ったので、今のところの解決策を時系列順に書いていきたいと思います。

データベース,JDBCなどの初学者なので、もしなにか私の至らないところがあれば、コメント頂ければ幸いです。

# 環境など

- 言語： Kotlin 1.3.61
- JDBC: `org.xerial:sqlite-jdbc:3.30.1`
- ターミナル上のSQLite3のバージョン: 3.31.0

# やったこと（時系列）

今回の例では、一つのカラムに`(id, json)`の形式でデータを入れることを想定します。この入れるJSONは{'都道府県名':'都道府県コード'}のオブジェクトを、47都道府県に対してのArrayにします。

## ターミナル上で配列を詰める

まずはテストとして、北海道(Code = 1), 青森県(Code = 2), 岩手県(Code = 3)あたりまで、手打ちで入れてみます。primary keyとなるidは1です。

```sql
create table prefecture_table (id integer primary key, prefecture_array json);
insert into prefecture_table values (1, json_array()); # 空の配列を作り、配列であると定義します
update prefecture_table set prefecture_array = json_insert(prefecture_array, '$[#]', json_object('code',1, 'name','北海道')) where id = 1;
update prefecture_table set prefecture_array = json_insert(prefecture_array, '$[#]', json_object('code',2, 'name','青森県')) where id = 1;
update prefecture_table set prefecture_array = json_insert(prefecture_array, '$[#]', json_object('code',3, 'name','岩手県')) where id = 1;
select * from prefecture_table;
1|[{"code":1,"name":"北海道"},{"code":2,"name":"青森県"},{"code":3,"name":"岩手県"}]
```
このように、id = 1のレコードにJSONオブジェクトを配列として入れることができました。

```sql
json_insert(json_array, PATH, value)
```
の形式の関数を用いることで、jsonデータを挿入することができます。PATHの`'$[#]'`は、C++でいう`vector::push_back`のようなもので、indexの最後に付け足すように使えるようです。

SQLite3 JSON1において配列のindexの扱いは

> For functions that accept PATH arguments, that PATH must be well-formed or else the function will throw an error. A well-formed PATH is a text value that begins with exactly one '$' character followed by zero or more instances of ".objectlabel" or "[arrayindex]".

>The arrayindex is usually a non-negative integer N. In that case, the array element selected is the N-th element of the array, starting with zero on the left. The arrayindex can also be of the form "#-N" in which case the element selected is the N-th from the right. The last element of the array is "#-1". Think of the "#" characters as the "number of elements in the array". Then the expression "#-1" evaluates is the integer that corresponds to the last entry in the array.

https://sqlite.org/json1.html より

にある通り、`$[N]`の形式で指定します。`#`は、現在のarray sizeを返す文字です。



## そのままJDBCに移植してみる

この調子でプログラムに埋め込んでいきます!
まず、都道府県名と都道府県コードを定義した`enum class`を作ります。

```Prefecture.kt
enum class Prefecture(val prefCode: Int) {
    北海道(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), 鳥取県(31),   島根県(32),
    岡山県(33),   広島県(34),   山口県(35),   徳島県(36),
    香川県(37),   愛媛県(38),   高知県(39),   福岡県(40),
    佐賀県(41),   長崎県(42),   熊本県(43),   大分県(44),
    宮崎県(45),   鹿児島県(46), 沖縄県(47)
}

```

今回では、このような形式でない別の膨大なデータを詰める想定でのテストなので、JSON配列に詰める部分(上の手書き部分で言うUPDATE文)は`PreparedStatement`を用いて、予めSQL文をコンパイルし、`autoCommit`をしないようにします。

```main(失敗例1).kt
import java.sql.DriverManager

fun main() {


    Class.forName("org.sqlite.JDBC")
    val url = "jdbc:sqlite::memory:" // テスト用にメモリで動かします
    val connection = DriverManager.getConnection(url)
    connection.autoCommit = false

    // テーブル・1カラムを作ってCommitします
    with(connection.createStatement()) {
        execute("create table prefecture_table (id integer primary key, prefecture_array json)")
        execute("insert into prefecture_table values (1, json_array())")
    }
    connection.commit()

    val insertSQL =
        "update prefecture_table set prefecture_array = json_insert(prefecture_array, '$[#]', json_object('code',?, 'name',?)) where id = 1"
    val insertPreparedStatement = connection.prepareStatement(insertSQL)

    for (prefecture in Prefecture.values()) {
        with(insertPreparedStatement) {
            setInt(1, prefecture.prefCode)
            setString(2, prefecture.name)
            executeUpdate()
        }

    }
    connection.commit()
    insertPreparedStatement.close()
    println(connection.createStatement().executeQuery("select * from prefecture_table").getString("prefecture_array"))
    connection.close()
}
```
`PreparedStatement`に、先程のUPDATE文をそのまま書いて、各都道府県情報をプレースホルダーに変えただけですが、このようなErrorが…

```
Exception in thread "main" org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (JSON path error near '[#]')
```

### PATH中の`#`が認識されない？

どうやら、`sqlite:JDBC`では`#`が絡んだPATHを認識してくれないようです…。何かのエスケープシーケンスが必要かと試してみましたがうまくいかず、ここで具体的な数字を入れると動く状態です。
[こちらのページ](https://github.com/xerial/sqlite-jdbc/blob/master/src/test/java/org/sqlite/JSON1Test.java)に、`JDBC:sqlite`を用いたJSON1のテストコードがあります。このテスト項目は[JSON1公式リファレンス](https://sqlite.org/json1.html)にある程度沿っているのですが、その中で`#`を使っている

> To append an element onto the end of an array, using json_insert() with an array index of "#". Examples:

> - json_insert('[1,2,3,4]','$[#]',99) → '[1,2,3,4,99]'
> - json_insert('[1,[2,3],4]','$[1][#]',99) → '[1,[2,3,99],4]'

に関してのテストコードは見られませんでした。

### insertする直前のJSON配列のサイズを突っ込めば・・・

そこで思いつきました。Queryを叩いて、Arrayのサイズを取得し、`#`の部分をプレースホルダーにして入れてしまえば良いんや!

そこで、JSON1の`json_array_length`関数を利用して、次に以下のfunctionを追加し、lastIndexを指定させてJSONオブジェクトを入れることにしました。

```main(失敗例2).kt
// Table, ColumnをCommitしたあと

    val getLengthSQL =
        "select json_array_length(prefecture_array) cnt from prefecture_table where id = 1"
    val getLengthPreparedStatement = connection.prepareStatement(getLengthSQL)

    val insertSQL =
        "update prefecture_table set prefecture_array = json_insert(prefecture_array, '$[?]', json_object('code',?, 'name',?)) where id = 1"
    val insertPreparedStatement = connection.prepareStatement(insertSQL)

    for (prefecture in Prefecture.values()) {
        val length = getLengthPreparedStatement.executeQuery().getInt("cnt")
        with(insertPreparedStatement) {
            setInt(1, length)
            setInt(2, prefecture.prefCode)
            setString(3, prefecture.name)
            executeUpdate()
        }

    }
    connection.commit()
    getLengthPreparedStatement.close()
    insertPreparedStatement.close()
    println(connection.createStatement().executeQuery("select * from prefecture_table").getString("prefecture_array"))
    connection.close()
}
```

```sql
select json_array_length(prefecture_array) cnt from prefecture_table where id = 1
```
といった具合でJSON配列長を`cnt`として

```kotlin
val length = getLengthPreparedStatement.executeQuery().getInt("cnt")
```
とQueryを叩いて、Lengthを取得します。
データを入れる毎にQueryを叩くのは気が引けたのですが、これなら出来る!
…と思ったら以下のErrorが

```
Exception in thread "main" java.lang.ArrayIndexOutOfBoundsException: Index 2 out of bounds for length 2
```

### `PreparedStatement`での`'$[?]'`のなかのプレースホルダーが認識されない？

前回から変わったのは`insertSQL`のプレースホルダーが一つ増えたことです。`''`でくくられているからなのか?文字列中はプレースホルダーとして認識してくれないのか…?というところから、いっそのことPATHまるごとStringで投げてしまえ!!!と思い

# 最終形態

```main.kt
import java.sql.DriverManager

fun main() {


    Class.forName("org.sqlite.JDBC")
    val url = "jdbc:sqlite::memory:" // テスト用にメモリで動かします
    val connection = DriverManager.getConnection(url)
    connection.autoCommit = false

    // テーブル・1カラムを作ってCommitします
    with(connection.createStatement()) {
        execute("create table prefecture_table (id integer primary key, prefecture_array json)")
        execute("insert into prefecture_table values (1, json_array())")
    }
    connection.commit()

    val getLengthSQL =
        "select json_array_length(prefecture_array) cnt from prefecture_table where id = 1"
    val getLengthPreparedStatement = connection.prepareStatement(getLengthSQL)

    val insertSQL =
        "update prefecture_table set prefecture_array = json_insert(prefecture_array, ?, json_object('code',?, 'name',?)) where id = 1"
    val insertPreparedStatement = connection.prepareStatement(insertSQL)

    for (prefecture in Prefecture.values()) {
        val length = getLengthPreparedStatement.executeQuery().getInt("cnt")
        with(insertPreparedStatement) {
            setString(1, "$[$length]")
            setInt(2, prefecture.prefCode)
            setString(3, prefecture.name)
            executeUpdate()
        }

    }
    connection.commit()
    getLengthPreparedStatement.close()
    insertPreparedStatement.close()

    println(connection.createStatement().executeQuery("select * from prefecture_table").getString("prefecture_array"))
    connection.close()

}
```

前と変えたのは`insertSQL`のPATH全体をプレースホルダーにして、そこに`"$[length]"`という文字列をまるまる投げるような形にしました。

すると

```
[{"code":1,"name":"北海道"},{"code":2,"name":"青森県"},{"code":3,"name":"岩手県"},{"code":4,"name":"宮城県"},{"code":5,"name":"秋田県"},{"code":6,"name":"山形県"},{"code":7,"name":"福島県"},{"code":8,"name":"茨城県"},{"code":9,"name":"栃木県"},{"code":10,"name":"群馬県"},{"code":11,"name":"埼玉県"},{"code":12,"name":"千葉県"},{"code":13,"name":"東京都"},{"code":14,"name":"神奈川県"},{"code":15,"name":"新潟県"},{"code":16,"name":"富山県"},{"code":17,"name":"石川県"},{"code":18,"name":"福井県"},{"code":19,"name":"山梨県"},{"code":20,"name":"長野県"},{"code":21,"name":"岐阜県"},{"code":22,"name":"静岡県"},{"code":23,"name":"愛知県"},{"code":24,"name":"三重県"},{"code":25,"name":"滋賀県"},{"code":26,"name":"京都府"},{"code":27,"name":"大阪府"},{"code":28,"name":"兵庫県"},{"code":29,"name":"奈良県"},{"code":30,"name":"和歌山県"},{"code":31,"name":"鳥取県"},{"code":32,"name":"島根県"},{"code":33,"name":"岡山県"},{"code":34,"name":"広島県"},{"code":35,"name":"山口県"},{"code":36,"name":"徳島県"},{"code":37,"name":"香川県"},{"code":38,"name":"愛媛県"},{"code":39,"name":"高知県"},{"code":40,"name":"福岡県"},{"code":41,"name":"佐賀県"},{"code":42,"name":"長崎県"},{"code":43,"name":"熊本県"},{"code":44,"name":"大分県"},{"code":45,"name":"宮崎県"},{"code":46,"name":"鹿児島県"},{"code":47,"name":"沖縄県"}]
```

と、JSONオブジェクトを配列要素にさせることに成功しました!