Java 如何在 JdbcTemplate 中使用 PostgreSQL hstore/json
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21383457/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use PostgreSQL hstore/json with JdbcTemplate
提问by Aymer
Is there a way to use PostgreSQL json/hstore with JdbcTemplate? esp query support.
有没有办法将 PostgreSQL json/hstore 与 一起使用JdbcTemplate？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';
采纳答案by sojin
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：
1)
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)
2)
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（hstoreCol为Map类型，jsonbObjCol为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 );
And to get the value:
并获得价值：
(Map<String, String>) rs.getObject( "hstore_col" ));
((PGobject) rs.getObject("jsonb_col")).getValue();
回答by Vlad Mihalcea
Even easier than JdbcTemplate, you can use the hibernate-typesopen-source project to persist HStore properties.
比 更容易JdbcTemplate，您可以使用hibernate-types开源项目来持久化 HStore 属性。
First, you need the Maven dependency:
首先，您需要 Maven 依赖项：
<dependency>
<groupId>com.vladmihalcea</groupId>
<artifactId>hibernate-types-52</artifactId>
<version>${hibernate-types.version}</version>
</dependency>
Then, assuming you have the following Bookentity:
然后，假设您有以下Book实体：
@Entity(name = "Book")
@Table(name = "book")
@TypeDef(name = "hstore", typeClass = PostgreSQLHStoreType.class)
public static class Book {
@Id
@GeneratedValue
private Long id;
@NaturalId
@Column(length = 15)
private String isbn;
@Type(type = "hstore")
@Column(columnDefinition = "hstore")
private Map<String, String> properties = new HashMap<>();
//Getters and setters omitted for brevity
}
Notice that we annotated the propertiesentity attribute with the @Typeannotation and we specified the hstoretype that was previously defined via @TypeDefto use the PostgreSQLHStoreTypecustom Hibernate Type.
请注意，我们使用注释对properties实体属性进行了@Type注释，并且我们指定了hstore之前通过@TypeDef使用PostgreSQLHStoreType自定义 Hibernate 类型定义的类型。
Now, when storing the following Bookentity:
现在，当存储以下Book实体时：
Book book = new Book();
book.setIsbn("978-9730228236");
book.getProperties().put("title", "High-Performance Java Persistence");
book.getProperties().put("author", "Vlad Mihalcea");
book.getProperties().put("publisher", "Amazon");
book.getProperties().put("price", ".95");
entityManager.persist(book);
Hibernate executes the following SQL INSERT statement:
Hibernate 执行以下 SQL INSERT 语句：
INSERT INTO book (isbn, properties, id)
VALUES (
'978-9730228236',
'"author"=>"Vlad Mihalcea",
"price"=>".95", "publisher"=>"Amazon",
"title"=>"High-Performance Java Persistence"',
1
)
And, when we fetch the Bookentity, we can see that all properties are fetched properly:
而且，当我们获取Book实体时，我们可以看到所有属性都被正确获取：
Book book = entityManager
.unwrap(Session.class)
.bySimpleNaturalId(Book.class)
.load("978-9730228236");
assertEquals(
"High-Performance Java Persistence",
book.getProperties().get("title")
);
assertEquals(
"Vlad Mihalcea",
book.getProperties().get("author")
);
For more details, check out this article.
有关更多详细信息，请查看这篇文章。
