How to use PostgreSQL hstore/json with JdbcTemplate
Even easier than JdbcTemplate
, you can use the Hibernate Types
open-source project to persist HStore properties.
First, you need the Maven dependency:
<dependency>
<groupId>com.vladmihalcea</groupId>
<artifactId>hibernate-types-52</artifactId>
<version>${hibernate-types.version}</version>
</dependency>
Then, assuming you have the following Book
entity:
@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 properties
entity attribute with the @Type
annotation and we specified the hstore
type that was previously defined via @TypeDef
to use the PostgreSQLHStoreType
custom Hibernate Type.
Now, when storing the following Book
entity:
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 executes the following SQL INSERT statement:
INSERT INTO book (isbn, properties, id)
VALUES (
'978-9730228236',
'"author"=>"Vlad Mihalcea",
"price"=>"$44.95", "publisher"=>"Amazon",
"title"=>"High-Performance Java Persistence"',
1
)
And, when we fetch the Book
entity, we can see that all properties are fetched properly:
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")
);
While this question was about Spring's JDBC Template, some users might have an option of using jOOQ instead, which has the jooq-postgres-extensions
module that implements support for HSTORE
and other data types like CIDR
, INET
, DATERANGE
, INT4RANGE
, INT8RANGE
, TSRANGE
, TSTZRANGE
, LTREE
, and possibly others, in the future. JSON
, JSONB
, and XML
types are supported too, without the above extensions module.
Your queries can then be written as follows:
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 will take care of the data type conversion (for HSTORE
) and the required casts of bind variables (e.g. ?::hstore
) transparently.
Some JSON functions and operators are available out of the box, for everything else, you can use plain SQL templating
Disclaimer: I work for the company behind jOOQ.
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:
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:
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) 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 );
And to get the value:
(Map<String, String>) rs.getObject( "hstore_col" ));
((PGobject) rs.getObject("jsonb_col")).getValue();