How can I Insert JSON object into Postgres using Java preparedStatement?

by

This behaviour is quite annoying since JSON strings are accepted without problems when used as literal strings in SQL commands.

There is a already an issue for this in the postgres driver Github repository (even if the problem seems the be the serverside processing).

Besides using a cast (see answer of
@a_horse_with_no_name) in the sql string, the issue author offers two additional solutions:

  1. Use a parameter stringtype=unspecified in the JDBC connection URL/options.

This tells PostgreSQL that all text or varchar parameters are actually
of unknown type, letting it infer their types more freely.

  1. Wrap the parameter in a org.postgresql.util.PGobject:

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

More Related Contents:

  1. Mapping PostgreSQL JSON column to a Hibernate entity property
  2. How do I use PostgreSQL JSON(B) operators containing a question mark “?” via JDBC
  3. How to use Postgres JSONB datatype with JPA?
  4. HTTP POST using JSON in Java
  5. Deserialize a List object with Gson?
  6. How can I get the SQL of a PreparedStatement?
  7. How to parse a dynamic JSON key in a Nested JSON result?
  8. Different names of JSON property during serialization and deserialization
  9. How to check whether a given string is valid JSON in Java
  10. Gson – convert from Json to a typed ArrayList
  11. Spring MVC – How to return simple String as JSON in Rest Controller
  12. How does Java’s PreparedStatement work?
  13. org.postgresql.util.PSQLException: FATAL: sorry, too many clients already
  14. How do I use the Jersey JSON POJO support?
  15. Deserializing Generic Types with GSON
  16. JSON character encoding
  17. How to create a custom deserializer in Jackson for a generic type?
  18. How to convert XML to JSON using only Jackson?
  19. How to return a partial JSON response using Java?
  20. Jackson – Required property?
  21. Jackson JSON serialization, recursion avoidance by level defining
  22. Java – Gson parsing nested within nested
  23. Unmarshal JSON to Java POJO in JAX-RS
  24. Value of type java.lang.String cannot be converted to JSONArray
  25. Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment]
  26. JSON Jackson parse different keys into same field
  27. PSQLException: ERROR: relation “TABLE_NAME” does not exist
  28. Jackson JSON Deserialization: array elements in each line
  29. Sending JSONP vs. JSON data?
  30. How to handle a NumberFormatException with Gson in deserialization a JSON response

Leave a Comment