CODEWITHSUNDEEP
javajsonpostgresqljdbcwildfly
Tiago
Tiago

Reputation: 748

Inserting Postgres' json column using wildfly

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.

Upvotes: 3

Views: 2370

Answers (2)

dedek
dedek

Reputation: 8301

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

Upvotes: 4

Tiago
Tiago

Reputation: 748

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.

Upvotes: 2

Related Questions