NamedParameterJdbcTemplate and JSON in Postgres

Question

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>

in4976 · Accepted Answer · 2022-08-09 03:08:09Z

10

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

Create json_string from Map using jackson library or GSON can be used.
Create PGObject and fill with value like below.
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);";

edited Aug 9, 2022 at 3:08

answered Nov 17, 2015 at 22:47

in4976

1711 silver badge6 bronze badges

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

Comments

epox · Accepted Answer · 2022-11-25 21:43:12Z

1

Put STRING, cast to JSONB, in sql

Add ::JSONB suffix to your SQL parameter.

Set the formatted string value, not a Map:

jdbcTemplate.update(""
    + "INSERT INTO my_table              "
    + "        (my_jsonb_column)         "
    + " VALUES (:my_jsonb_column::JSONB) ",

    Map.of("my_jsonb_column", "{\"my_key\": \"my value!\"}"));

In Spring

In Spring "you" usually convert "your" HashMaps to (json) strings using ObjectMapper:

    Map.of("my_jsonb_column", objectMapper.writeValueAsString(yourMap))

NOTE: Posgres' JsonB only supports Bool, Number, and Text values. So convert nested objects, if any, to string recursively.

edited Nov 25, 2022 at 21:43

answered Nov 25, 2022 at 15:52

epox

11.4k2 gold badges67 silver badges43 bronze badges

Collectives™ on Stack Overflow

NamedParameterJdbcTemplate and JSON in Postgres

2 Answers 2

Comments

Put STRING, cast to JSONB, in sql

In Spring

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Put STRING, cast to JSONB, in sql

In Spring

Comments

Your Answer

Sign up or log in

Post as a guest

Related