如何在JdbcTemplate中使用PostgreSQL的hstore/json？

Question

如何在JdbcTemplate中使用PostgreSQL的hstore/json？

19

有没有办法在JdbcTemplate中使用PostgreSQL的json/hstore，尤其是查询支持？

例如：

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

对于Json

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

- Aymer

3个回答

5

比使用 JdbcTemplate 更加简单的是，你可以使用开源项目Hibernate Types 来持久化 HStore 属性。

首先，你需要 Maven 依赖：

<dependency>
    <groupId>com.vladmihalcea</groupId>
    <artifactId>hibernate-types-52</artifactId>
    <version>${hibernate-types.version}</version>
</dependency>

假设你有以下的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
}

请注意，我们使用了@Type注解对properties实体属性进行注释，并指定了先前通过@TypeDef定义的hstore类型，以使用自定义Hibernate类型PostgreSQLHStoreType。

现在，当存储以下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", "$44.95");
 
entityManager.persist(book);

Hibernate 执行以下 SQL INSERT 语句：

INSERT INTO book (isbn, properties, id)
VALUES (
    '978-9730228236',
    '"author"=>"Vlad Mihalcea",
     "price"=>"$44.95", "publisher"=>"Amazon",
     "title"=>"High-Performance Java Persistence"',
    1
)

当我们获取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")
);

- Vlad Mihalcea

你好，是否有一种方法可以在自定义转换器中存储Map<Object,Object>以用于每个对象？ - Elyes

1

转换器非常有限，因为它们无法让您访问JDBC的“PreparedStatement”。因此，它们不适用于任何特定于数据库的类型。如果您愿意，仍然可以尝试使用它，但我总是会选择Hibernate类型。 - Vlad Mihalcea

谢谢您的回答，我想了解一下HStoreType是如何工作的。我持久化了一个Object Object的Map，但我注意到它存储的是toString值，而且从数据库到对象时我无法得到正确的对象，这就是为什么我要求转换器或者是否应该实现自己的HStoreType来处理对象。 - Elyes

1

代码在 GitHub 上，所以您可以轻松调试它以了解其工作原理。享受学习的过程吧。 - Vlad Mihalcea

1

虽然这个问题是关于Spring的JDBC模板，但是一些用户可能会选择使用jOOQ，它具有jooq-postgres-extensions模块，实现了对HSTORE和其他数据类型（如CIDR、INET、DATERANGE、INT4RANGE、INT8RANGE、TSRANGE、TSTZRANGE、LTREE等）的支持，并且未来可能支持其他类型。JSON、JSONB和XML类型也被支持，无需上述扩展模块。

然后您可以按照以下方式编写查询：

ctx.insertInto(HSTORE_TEST)
   .columns(HSTORE_TEST.DATA)
   .values(Hstore.valueOf(Map.of("key1", "value1", "key2", "value2")))
   .execute();

ctx.insertInto(JTEST)
   .columns(JTEST.DATA)
   .values(JSONB.valueOf(
        """
        {"k1": 1, "k2": "two"}
        """))
   .execute();

jOOQ 将自动处理数据类型转换（对于 HSTORE）和绑定变量所需的强制转换（例如 ?::hstore）。一些 JSON 函数和运算符可以直接使用，对于其他情况，可以使用普通 SQL 模板。

免责声明：我在 jOOQ 的背后工作。

- Lukas Eder

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- sojin · Accepted Answer

虽然回答晚了一些（关于插入部分的问题），但希望对其他人有所帮助：

从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中：

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（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 );

获取值的方法：

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