如何在jdbctemplate中使用PostgreSQL hstore/json

Is there a way to use PostgreSQL json/hstore with jdbctemplate? esp query support.

有办法使用jdbctemplate来使用PostgreSQL json/hstore吗?esp查询支持。

for eg:

如:

hstore:

INSERT INTO hstore_test (data) VALUES ('"key1"=>"value1", "key2"=>"value2", "key3"=>"value3"')

SELECT data -> 'key4' FROM hstore_test
SELECT item_id, (each(data)).* FROM hstore_test WHERE item_id = 2

for Json

为Json

insert into jtest (data) values ('{"k1": 1, "k2": "two"}');
select * from jtest where data ->> 'k2' = 'two';

1 个解决方案

#1

Although quite late for an answer (for the insert part), I hope it might be useful someone else:

虽然回答(插入部分)已经很晚了，但我希望它可能对其他人有用:

Take the key/value pairs in a HashMap:

取HashMap中的键/值对:

Map<String, String> hstoreMap = new HashMap<>();
hstoreMap.put("key1", "value1");
hstoreMap.put("key2", "value2");

PGobject jsonbObj = new PGobject();
jsonbObj.setType("json");
jsonbObj.setValue("{\"key\" : \"value\"}");

use one of the following way to insert them to PostgreSQL:

使用以下方法之一将它们插入到PostgreSQL:

jdbcTemplate.update(conn -> {
     PreparedStatement ps = conn.prepareStatement( "INSERT INTO table (hstore_col, jsonb_col)" );
     ps.setObject( 1, hstoreMap );
     ps.setObject( 2, jsonbObj );
});

jdbcTemplate.update("INSERT INTO table (hstore_col, jsonb_col) VALUES(?,?)", 
new Object[]{ hstoreMap, jsonbObj }, new int[]{Types.OTHER, Types.OTHER});

3) Set hstoreMap/jsonbObj in the POJO (hstoreCol of type Map and jsonbObjCol is of type PGObject)

3)在POJO中设置hstoreMap/jsonbObj (Map类型的hstoreCol, PGObject类型的jsonbObjCol)

BeanPropertySqlParameterSource sqlParameterSource = new BeanPropertySqlParameterSource( POJO );
sqlParameterSource.registerSqlType( "hstore_col", Types.OTHER );
sqlParameterSource.registerSqlType( "jsonb_col", Types.OTHER );
namedJdbcTemplate.update( "INSERT INTO table (hstore_col, jsonb_col) VALUES (:hstoreCol, :jsonbObjCol)", sqlParameterSource );

And to get the value:

为了得到这个值:

(Map<String, String>) rs.getObject( "hstore_col" ));
((PGobject) rs.getObject("jsonb_col")).getValue();

#1