如何在JdbcTemplate中使用PostgreSQL hstore / json [英] How to use PostgreSQL hstore/json with JdbcTemplate
本文介绍了如何在JdbcTemplate中使用PostgreSQL hstore / json的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
是否可以通过 JdbcTemplate
使用PostgreSQL json / hstore? esp查询支持。
Is there a way to use PostgreSQL json/hstore with JdbcTemplate
? esp query support.
例如:
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
insert into jtest (data) values ('{"k1": 1, "k2": "two"}');
select * from jtest where data ->> 'k2' = 'two';
推荐答案
尽管答案很晚(对于插入部分),希望对其他人有用:
Although quite late for an answer (for the insert part), I hope it might be useful someone else:
在HashMap中获取键/值对:
Take the key/value pairs in a 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\"}");
使用以下方式之一将它们插入PostgreSQL:
use one of the following way to insert them to PostgreSQL:
1)
jdbcTemplate.update(conn -> {
PreparedStatement ps = conn.prepareStatement( "INSERT INTO table (hstore_col, jsonb_col) VALUES (?, ?)" );
ps.setObject( 1, hstoreMap );
ps.setObject( 2, jsonbObj );
});
2)
jdbcTemplate.update("INSERT INTO table (hstore_col, jsonb_col) VALUES(?,?)",
new Object[]{ hstoreMap, jsonbObj }, new int[]{Types.OTHER, Types.OTHER});
3)
在POJO中设置hstoreMap / jsonbObj(Map类型和jsonbObjCol类型的hstoreCol为类型为PGObject)
3) Set hstoreMap/jsonbObj in the POJO (hstoreCol of type Map and jsonbObjCol is of type PGObject)
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 );
并获取值:
(Map<String, String>) rs.getObject( "hstore_col" ));
((PGobject) rs.getObject("jsonb_col")).getValue();
这篇关于如何在JdbcTemplate中使用PostgreSQL hstore / json的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文