Inserting Postgres' json column using wildfly

Question

I'm running a web application which is a rest API and I'm using wildfly 9.0.2 and PostgreSQL. The database connection is using the latest jdbc4.2 driver and the jre is oracle's jr8.

I installed the jdbc following this topic, and got it running perfectly so far I installed the datasource using the wildfly admin console.

So the issue arrived when I attempted to insert a json field. I quickly realized JDBC doesn't have a direct method to insert a json field as it has for let's say String, Integer, Timestamp etc. So I find this solution online somewhere I don't remember now:

oPGobject jsonObject = new PGobject();
jsonObject.setType("json");
jsonObject.setValue(new Gson().toJson(ctrlClienteComunicacao));
preparedStatement.setObject(++i,jsonObject);

This solution works fine as I tried it running from a simple main class and I can insert any json I want, but when I tried this code while running on wildfly I got this class not found error:

java.lang.NoClassDefFoundError: org/postgresql/util/PGobject

My first atempt was to remove the provided attribute from the JDBC library so it would be included it in the .war. And then I got a new error:

org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of org.postgresql.util.PGobject. Use setObject() with an explicit Types value to specify the type to use.

After a few more attempts I realized that including the jar in the war makes wildfly deploy it as a driver.

[org.jboss.as.connector.deployers.jdbc] (MSC service thread 1-2) WFLYJCA0018: Started Driver service with driver-name = VirtualForum.war_org.postgresql.Driver_9_4

So I tried a different thing, while the war was deployed, I changed the datasource from the original pgsql driver to this one started during deployment of the app and it works! I can insert json fields.

But I don't think this is a proper solution as I would prefer it using the driver installed on wildfly.

I apologize for my english and the question layout this is my very first question.

Sounds as if the JDBC driver included in Wildfly is an outdated version. Why do you think using the current driver from Postgres is not a "proper" solution? — user330315
– user330315, Commented Oct 30, 2015 at 17:03
It is the latest as I did I full wildfly configuration from scratch using the same .jar file in both the project and wildfly specificly to test this. — Tiago
– Tiago, Commented Oct 30, 2015 at 17:05
I managed to get it to work but without using postgres's jdbc specific by changing the query to this: VALUES (?,?,to_json(?::json),?) And to set the parameter as String: preparedStatement.setString(++i,json); — Tiago
– Tiago, Commented Nov 2, 2015 at 10:09

dedek · Accepted Answer · 2016-05-05 07:07:57Z

4

Adding org.postgres dependency to the war manifest solved the issue for me.

Here is a maven snippet:

<build>
    <plugins>
        <plugin>
            <artifactId>maven-war-plugin</artifactId>
            <configuration>
                <archive>
                    <manifestEntries>
                        <Dependencies>org.postgres</Dependencies>
                    </manifestEntries>
                </archive>
            </configuration>
        </plugin>
    </plugins>
</build>

And here is the generated MANIFEST.MF (see the last line):

Manifest-Version: 1.0
Archiver-Version: Plexus Archiver
Built-By: dedek
Created-By: Apache Maven 3.3.3
Build-Jdk: 1.8.0_51
Dependencies: org.postgres

edited May 5, 2016 at 7:07

answered May 3, 2016 at 13:07

dedek

8,3943 gold badges45 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tiago Over a year ago

This actually makes a lot of sense and it's the exact solution I was looking for, I will give it a try when I get the chance.

Tiago · Accepted Answer · 2015-11-02 12:11:08Z

2

So managed to work around this by simply changing the insertion method to now require any postgres jdbc specific method by:

Changing the query to this,

INSERT INTO table (json_column) VALUES (to_json(?::json))

And changing the method in the prepared statement to this:

 preparedStatement.setString(1, json);

This solved the issue for me, not sure if it's the best/most eficient solution but it works.

answered Nov 2, 2015 at 12:11

Tiago

74810 silver badges16 bronze badges

Collectives™ on Stack Overflow

Inserting Postgres' json column using wildfly

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related