1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@sho0405(Shoichiro Masuoka)

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

1
Last updated at Posted at 2020-01-31

はじめに

SQLite3でJSON形式のデータが詰められるということで、公式リファレンスを参考に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です。

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オブジェクトを配列として入れることができました。

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を認識してくれないようです…。何かのエスケープシーケンスが必要かと試してみましたがうまくいかず、ここで具体的な数字を入れると動く状態です。
こちらのページに、JDBC:sqliteを用いたJSON1のテストコードがあります。このテスト項目はJSON1公式リファレンスにある程度沿っているのですが、その中で#を使っている

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

select json_array_length(prefecture_array) cnt from prefecture_table where id = 1

といった具合でJSON配列長をcntとして

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オブジェクトを配列要素にさせることに成功しました!

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?