Inserting JSON data into Postgres using JDBC driver

EDIT:  Marcus(1st comment provider) helped me to write much cleaner and secure code. It doesn’t require CAST function and uses  PGobject with jdbc’s setObject. You could download updated code from git-repo. Thanks Marcus !!

One of the clients of OmniTI requested help to provide sample application to insert JSON data into Postgres using Java JDBC driver . I’m not Java expert so it took a while for me to write a simple java code to insert data. TBH, I took help to write test application from one of our Java engineers at OmniTI. Now, test application is ready and next step is to make it work with JSON datatype ! After struggling a little to find out work around for string escaping in JAVA code, I stumbled upon data type issue! Here is the test application code to connect to my local Postgres installation and insert JSON data into sample table:

postgres=# \d sample
Table "public.sample"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
data | json |
denishs-MacBook-Air-2:java denish$ java -cp $CLASSPATH PgJSONExample
-------- PostgreSQL JDBC Connection Testing ------------
PostgreSQL JDBC Driver Registered!
You made it, take control your database now!
Something exploded running the insert: ERROR: column "data" is of type json but expression is of type character varying
Hint: You will need to rewrite or cast the expression.
Position: 42

After some research , I found out that there is no standard JSON type on java side so adding support for json to postgres jdbc is not straight forward ! StackOverflow answer helped me for testing out the JSON datatype handling at psql level. As Craig mentioned in the answer that the correct way to solve this problem is to write a custom Java mapping type that uses the JDBC setObject method. This can be a tricky though.  A simpler workaround is to tell PostgreSQL to cast implicitly from text to json:
postgres=# create cast (text as json) without function as implicit;
CREATE CAST

The WITHOUT FUNCTION clause is used because text and json have the same on-disk and in-memory representation, they’re basically just aliases for the same data type. AS IMPLICIT tells PostgreSQL it can convert without being explicitly told to, allowing things like this to work:

postgres=# prepare test(text) as insert into sample (data) values ($1);
PREPARE
postgres=# execute test('{}');
INSERT 0 1
postgres=# select data from sample;
data
----
{}
(1 row)

Awesome ! That worked :-) Let’s try similar approach in Java application code.

denishs-MacBook-Air-2:java denish$ export CLASSPATH=/usr/share/postgresql/java/postgresql-9.2-1002.jdbc4.jar:
denishs-MacBook-Air-2:java denish$ javac -classpath $CLASSPATH PgJSONExample.java
denishs-MacBook-Air-2:java denish$ java -cp $CLASSPATH PgJSONExample
-------- PostgreSQL JDBC Connection Testing ------------
PostgreSQL JDBC Driver Registered!
You made it, take control your database now!
postgres=# select * from sample;
id | data
----+------------------------------------------------------------------------
1 | {"username":"denish","posts":10122,"emailaddress":"denish@omniti.com"}
(1 row)

Yay! It worked as well :-)

Next in my list to figure out installing PL/Java on Mac and/or Linux !! Let me know, if you have instructions for installation and test application using PL/Java.

7 thoughts on “Inserting JSON data into Postgres using JDBC driver

  1. If you want to avoid the text cast on the database side and you are willing to tie yourself to the Postgres JDBC implementation, you can use PGobject with jdbc’s setObject (http://docs.oracle.com/javase/6/docs/api/java/sql/PreparedStatement.html#setObject(int, java.lang.Object))

    String yourJsonString = “{\”username\”:\”denish\”,\”posts\”:10122,\”emailaddress\”:\”denish@omniti.com\”}”;

    PGobject jsonObject = new PGobject();
    jsonObject.setType(“json”);
    jsonObject.setValue(yourJsonString);

    PreparedStatement preparedStatement = …
    preparedStatement.setObject(1, jsonObject);

    Cheers

    1. Not only is this a better solution – the cast solution is broken and should not ever be implemented. It allows invalid json to be put in the database – something that we have gone to a lot of trouble to prevent:

      andrew=# create cast (text as json) without function as implicit;
      CREATE CAST
      andrew=# select ‘foo’::text::json;
      json
      ——
      foo

      Please don’t suggest such horrid ways of getting corrupted data.

      1. @Andrew Thanks for your comment. Appreciated! I have searched various sites for solution and everyone provided similar solution but I knew this is not good solution. Now, I have correct solution and it’s available for others to read/review.

        This is EXACTLY the reason I blogged about it :-)

      2. I’ve corrected the answer to use the json_in function correctly while still allowing an implicit cast for those who need it.

        http://stackoverflow.com/a/15983429/398670

        CREATE OR REPLACE FUNCTION json_intext(text) RETURNS json AS $$
        SELECT json_in($1::cstring);
        $$ LANGUAGE SQL IMMUTABLE;

        CREATE CAST (text AS json) WITH FUNCTION json_intext(text) AS IMPLICIT;

        Thanks for pointing out the issue.

  2. If you do not want to have compile time dependencies to postgres jdbc then

    to_json(?::json)

    is your answer.


    String data = “{\”key1\”:\”value1\”,\”key2\”:\”value2\”}”;
    PreparedStatement preparedStatement = connection.prepareStatement(“INSERT INTO sample (id, date) VALUES (?, to_json(?::json))”);

    preparedStetement.setString(2, jsonString);

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>