Symfoware

Symfowareについての考察blog

PostgreSQLのJSON列にJavaからデータを登録する

PostgreSQL 9.2からJSON型がサポートされたそうです。
FreeBSD 10.0にインストールしたPostgreSQL 9.3.2にデータを登録してみます。



テーブルの作成



こんな感じで、json型の列をもつテーブルを作成しました。


CREATE TABLE post_json (address json)



299_01.png




データの登録



登録はJSON形式の文字列を指定すれば良いようです。


INSERT INTO post_json (address) VALUES ('{"key1":"value1-1", "key2":"value1-2"}')

INSERT INTO post_json (address) VALUES ('{"key1":"value2-1", "key2":"value2-2"}')

INSERT INTO post_json (address) VALUES ('{"key1":"value3-1", "key2":"value3-2"}')




登録後、データを検索してみます。


SELECT * FROM post_json



まだ文字列型との違いがわからない感じです。

299_02.png






データの変換




[json型]->'jsonのキー'



という指定で、オブジェクトとして値を取得できます。
こんなクエリを実行してみました。


SELECT
address->'key1',
address->'key2'
FROM
post_json




結果がこちら。

299_03.png


マニュアルを見てみます。
9.15. JSON関数と演算子


演算子は


->
JSON オブジェクトフィールド取得

->>
JSON オブジェクトフィールドをテキストとして取得

#>
指定されたパスにてJSONオブジェクトを取得

#>>
指定されたパスにてJSONオブジェクトをテキストとして取得



があるようです。


SELECT
address->'key1',
address->>'key1',
address#>'{key1}',
address#>>'{key1}'
FROM
post_json



というクエリを実行した結果がこちら。

299_04.png






条件を指定した検索




「key1」の値が「value2-1」のデータを検索してみます。


SELECT * FROM post_json
WHERE
address->>'key1' = 'value2-1'



実行結果がこちら。

299_05.png






あいまい検索



普通にlikeも使えました。


SELECT * FROM post_json
WHERE
address->>'key1' LIKE '%lue%'




実行結果はこちら。

299_06.png





Javaから登録



Javaから登録してみます。

Statementを使用した登録は、普通にクエリと同じ文字列を実行すればOK。


  1. package sample;
  2. import java.sql.Connection;
  3. import java.sql.DriverManager;
  4. import java.sql.Statement;
  5. public class MainProcess {
  6.     public static void main(String[] args) throws Exception {
  7.         // 接続文字列は
  8.         // jdbc:postgresql://[ホスト名]:[ポート番号]/[データベース名]
  9.         String master_url = "jdbc:postgresql://192.168.1.101:5432/sample";
  10.         
  11.         try (Connection con = DriverManager.getConnection(master_url, "pgadmin", "password");
  12.                 Statement stmt = con.createStatement()) {
  13.             
  14.             String query = "INSERT INTO post_json(address) VALUES('{\"key1\":\"value4-1\", \"key2\":\"value4-2\"}')";
  15.             
  16.             stmt.execute(query);
  17.             
  18.         } catch (Exception e) {
  19.             e.printStackTrace();
  20.         }
  21.         
  22.     }
  23. }






PreparedStatementに変更しようと、こんなプログラムにしてみたところ
エラーになりました。


  1. package sample;
  2. import java.sql.Connection;
  3. import java.sql.DriverManager;
  4. import java.sql.PreparedStatement;
  5. public class MainProcess {
  6.     public static void main(String[] args) throws Exception {
  7.         // 接続文字列は
  8.         // jdbc:postgresql://[ホスト名]:[ポート番号]/[データベース名]
  9.         String master_url = "jdbc:postgresql://192.168.1.101:5432/sample";
  10.         String query = "INSERT INTO post_json(address) VALUES(?)";
  11.         try (Connection con = DriverManager.getConnection(master_url, "pgadmin", "password");
  12.                 PreparedStatement stmt = con.prepareStatement(query)) {
  13.             stmt.setString(1, "{\"key1\":\"value4-1\", \"key2\":\"value4-2\"}");
  14.             stmt.execute();
  15.         } catch (Exception e) {
  16.             e.printStackTrace();
  17.         }
  18.     }
  19. }




エラーの内容は以下のとおり。


org.postgresql.util.PSQLException: ERROR: column "address" is of type json but expression is of type character varying
ヒント: You will need to rewrite or cast the expression.
ポジション: 39
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2161)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1890)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:560)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:410)
    at sample.MainProcess.main(MainProcess.java:20)






こちらを参考に「PGobject」を使用する方法で登録出来ました。
Inserting JSON data into Postgres using JDBC driver



  1. package sample;
  2. import java.sql.Connection;
  3. import java.sql.DriverManager;
  4. import java.sql.PreparedStatement;
  5. import org.postgresql.util.PGobject;
  6. public class MainProcess {
  7.     public static void main(String[] args) throws Exception {
  8.         // 接続文字列は
  9.         // jdbc:postgresql://[ホスト名]:[ポート番号]/[データベース名]
  10.         String master_url = "jdbc:postgresql://192.168.1.101:5432/sample";
  11.         String query = "INSERT INTO post_json(address) VALUES(?)";
  12.         try (Connection con = DriverManager.getConnection(master_url, "pgadmin", "password");
  13.                 PreparedStatement stmt = con.prepareStatement(query)) {
  14.             
  15.             PGobject jsonObject = new PGobject();
  16.             jsonObject.setType("json");
  17.             jsonObject.setValue("{\"key1\":\"value4-1\", \"key2\":\"value4-2\"}");
  18.             
  19.             stmt.setObject(1, jsonObject);
  20.             stmt.execute();
  21.         } catch (Exception e) {
  22.             e.printStackTrace();
  23.         }
  24.     }
  25. }






PreparedStatement
こちらをよく見てみると、


setObject(int parameterIndex, Object x, int targetSqlType)



このように、第三引数でデータ型を指定できます。
簡単なプログラムを書いて、JSON型のsqlTypeを調べてみました。


  1. package sample;
  2. import java.sql.Connection;
  3. import java.sql.DatabaseMetaData;
  4. import java.sql.DriverManager;
  5. import java.sql.ResultSet;
  6. public class MainProcess {
  7.     public static void main(String[] args) throws Exception {
  8.         // 接続文字列は
  9.         // jdbc:postgresql://[ホスト名]:[ポート番号]/[データベース名]
  10.         String master_url = "jdbc:postgresql://192.168.1.101:5432/sample";
  11.         try (Connection con = DriverManager.getConnection(master_url, "pgadmin", "password")) {
  12.             
  13.             DatabaseMetaData dmd = con.getMetaData();
  14.             ResultSet rs = dmd.getColumns(null, "public", "post_json", "%");
  15.             
  16.             while(rs.next()) {
  17.                 
  18.                 String name = rs.getString("COLUMN_NAME");
  19.                 String type = rs.getString("DATA_TYPE");
  20.                 
  21.                 System.out.println(name + "\t" + type);
  22.             }
  23.         } catch (Exception e) {
  24.             e.printStackTrace();
  25.         }
  26.     }
  27. }




実行結果はこのようになりました。


address    1111




sqlTypeに「1111」を指定すれば良さそうです。

こんなプログラムを書いて試してみたところ、ちゃんと登録出来ました。



  1. package sample;
  2. import java.sql.Connection;
  3. import java.sql.DriverManager;
  4. import java.sql.PreparedStatement;
  5. public class MainProcess {
  6.     public static void main(String[] args) throws Exception {
  7.         // 接続文字列は
  8.         // jdbc:postgresql://[ホスト名]:[ポート番号]/[データベース名]
  9.         String master_url = "jdbc:postgresql://192.168.1.101:5432/sample";
  10.         String query = "INSERT INTO post_json(address) VALUES(?)";
  11.         try (Connection con = DriverManager.getConnection(master_url, "pgadmin", "password");
  12.                 PreparedStatement stmt = con.prepareStatement(query)) {
  13.                         
  14.             stmt.setObject(1, "{\"key1\":\"value4-1\", \"key2\":\"value4-2\"}", 1111);
  15.             stmt.execute();
  16.         } catch (Exception e) {
  17.             e.printStackTrace();
  18.         }
  19.     }
  20. }

関連記事

テーマ:データベース - ジャンル:コンピュータ

  1. 2014/02/15(土) 23:51:37|
  2. PostgreSQL
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集
<<PostgreSQLのJSON列に郵便番号情報を登録、検索する | ホーム | PostgreSQLのXML列をあいまい検索する(contains)>>

コメント

コメントの投稿


管理者にだけ表示を許可する

トラックバック

トラックバック URL
http://symfoware.blog68.fc2.com/tb.php/1246-1dea0d3c
この記事にトラックバックする(FC2ブログユーザー)