¿Cómo puedo insertar un objeto JSON en Postgres utilizando Java prepareStatement?

postgresql prepared-statement

Question

Estoy luchando para insertar un objeto JSON en mi postgres v9.4 DB. He definido la columna llamada "evtjson" como tipo json (no jsonb ).

Estoy tratando de usar una declaración preparada en Java (jdk1.8) para insertar un objeto Json (creado con las bibliotecas jEEJ.json de JEE) en la columna, pero sigo teniendo errores de SQLException.

Creo el objeto JSON usando:

JsonObject mbrLogRec = Json.createObjectBuilder().build();
…
mbrLogRec = Json.createObjectBuilder()
                .add("New MbrID", newId)
                .build();

Luego paso este objeto como parámetro a otro método para escribirlo en la base de datos utilizando una declaración preparada. (junto con varios otros campos) como:

pStmt.setObject(11, dtlRec);

Usando este método, recibo el siguiente error:

org.postgresql.util.PSQLException: No se ha instalado la extensión hstore. en org.postgresql.jdbc.PgPreparedStatement.setMap (PgPreparedStatement.java:553) en org.postgresql.jdbc.PgPreparedStatement.setObject (PgPreparedStatement.java:1036)

También he intentado:

pStmt.setString(11, dtlRec.toString());
pStmt.setObject(11, dtlRec.toString());

Que producen un error diferente:

Evento JSON: {"New MbrID": 29}

SQLException: ERROR: la columna "evtjson" es de tipo json pero la expresión es de tipo de carácter variable

Sugerencia: deberá volver a escribir o emitir la expresión.

Pero, al menos esto me dice que la base de datos está reconociendo la columna como tipo JSON. Intenté instalar la extensión hstore, pero luego me dijo que no era un objeto hstore.

OracleDocs muestra varios métodos para establecer el valor del parámetro en el estado preparado, pero prefiero no probarlos todos si alguien sabe la respuesta. ( http://docs.oracle.com/javase/8/docs/api/java/sql/PreparedStatement.html ) Estos también hacen referencia a un parámetro adicional, SQLType, pero no puedo encontrar ninguna referencia a estos.

¿Debo probar setAsciiStream ? CharacterStream ? CLOB?

Answer 1

En lugar de pasar el objeto json, pase su valor de cadena y conviértalo a json en la consulta. Ejemplo:

JSONObject someJsonObject=..........

String yourJsonString = someJsonObject.toString();

String query = "INSERT INTO table (json_field) VALUES (to_json(yourJsonString::json))";

esto funcionó para mí.

Answer 2

Este comportamiento es bastante molesto ya que las cadenas JSON se aceptan sin problemas cuando se usan como cadenas literales en los comandos SQL.

Ya hay un issue para esto en el repositorio Github del controlador de Postgres (incluso si el problema parece ser el procesamiento del lado del servidor).

Además de usar un cast (vea la respuesta de @a_horse_with_no_name) en la cadena sql, el autor del problema ofrece dos soluciones adicionales:

1. Utilice un parámetro stringtype=unspecified en la URL / opciones de conexión JDBC.

Esto le dice a PostgreSQL que todos los parámetros de texto o varchar son en realidad de tipo desconocido, lo que permite inferir sus tipos más libremente.

2. Envuelva el parámetro en un org.postgresql.util.PGobject :

 PGobject jsonObject = new PGobject();
 jsonObject.setType("json");
 jsonObject.setValue(yourJsonString);
 pstmt.setObject(11, jsonObject);

Answer 3

Pasar el JSON como una cadena es el enfoque correcto, pero como el mensaje de error le indica, debe convertir el parámetro en la INSERT a un valor JSON :

insert into the_table
   (.., evtjson, ..) 
values 
   (.., cast(? as json), ..)

Luego puede usar pStmt.setString(11, dtlRec.toString()) para pasar el valor

Answer 4

PostgreSQL es excesivamente, molesto y estricto sobre las conversiones de tipos de datos. No emitirá implícitamente texto incluso a valores similares a texto, como xml y json.

por lo que la solución original en esta respuesta omite la validación JSON por

crear elenco (CHARACTER VARYING como json) sin función como implícita;

Aquí, CHARACTER VARYING es en realidad que está intentando pasar a su objeto de declaración preparado.

Answer 5

Puedes hacerlo así y solo necesitas la cadena json:

Cambie la consulta a:

String query = "INSERT INTO table (json_field) VALUES (to_json(?::json))"

Y establece el parámetro como una cadena.

pStmt.setString(1, json);

Answer 6

Tienes dos opciones:

1. Use statement.setString (jsonStr) y luego maneje la conversión en la declaración sql:

`

PreparedStatement statement = con.prepareStatement("insert into table 
 (jsonColumn) values (?::json)");
 statement.setString(1, jsonStr);

2. Another option is to use PGobject to create a custom value wrapper.

PGobject jsonObject = new PGobject();
 PreparedStatement statement = con.prepareStatement("insert into table 
 (jsonColumn) values (?)");
 jsonObject.setType("json");
 jsonObject.setValue(jsonStr);
 statement.setObject(1, jsonObject);

`Personalmente prefiero este último ya que la consulta es más limpia