[FIXED] NamedParameterJdbcTemplate and JSON in Postgres

Issue

Is it possible to insert json into a postgresql json column using the NamedParameterJDBCTemplate class in SpringFramework and I am getting a PSQLException: No hstore extension installed. From this I gather that the NamedParameterJDBCTemplateis trying to store the value as hstore.

Is there a way I can tell the NamedParameterJDBCTemplate to insert the value into the query as json?

The value is stored in java as a Map<String, String>

Solution

This works for me to insert Map as jsonb using namedjdbctemplate.

1. Create json_string from Map using jackson library or GSON can be used.
2. Create PGObject and fill with value like below.
3. Use the PGObject in your SQL prepare statement.

Hope this helps.

ObjectMapper objectMapper = new ObjectMapper();
PGobject jsonObject = new PGobject();
String Map_Json_String = objectMapper.writeValueAsString(your_map);
jsonObject.setType("jsonb");
jsonObject.setValue(Map_Json_String);

String final insertSql = "INSERT INTO \"Table_Name\""
            + " VALUES (:jsonObject);";

Answered By - in4976
Answer Checked By - Mary Flores (JavaFixing Volunteer)