Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly. Hypersistence Optimizer is that tool!
The open-source hibernate-types project allows you to map a great variety of database types that are not supported natively by Hibernate ORM (e.g. JSON, ARRAY, YearMonth, Month, INET addresses).
In this article, we are going to see how you can map a PostgreSQL HStore type, which allows you to store key/value pairs, to a Java Map entity property when using JPA and Hibernate.
How to map a PostgreSQL HStore entity property with JPA and #Hibernate . @vlad_mihalcea https://t.co/360MtHSYmw pic.twitter.com/KcXP3PD6lO
— Java (@java) January 5, 2019
First of all, you need to set up the following Maven dependency in your project pom.xml configuration file:
<dependency>
<groupId>com.vladmihalcea</groupId>
<artifactId>hibernate-types-52</artifactId>
<version>${hibernate-types.version}</version>
</dependency>
If you’re using older versions of Hibernate (e.g. 5.1 or 4.3), check out the hibernate-types GitHub repository for more info about the matching dependency for your current Hibernate version.
First of all, we need to make sure we have the hstore extension installed in our database:
CREATE EXTENSION IF NOT EXISTS hstore;
Our application needs to store Book entities in the following database table:
Notice that the properties column type is hstore which is not supported by Hibernate ORM.
To map the PostgreSQL hstore column types to a Java Map, you need a custom Hibernate type since the built-in types don’t support persisting database-specific types. Luckily, thanks to the hibernate-types library you can easily map the book table to 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
}
The @TypeDef annotation is used to register the PostgreSQLHStoreType custom Hibernate Type with the hstore name. Afterward, the properties entity attribute uses the @Type annotation to specify that the hstore Hibernate Type should be used to handle this entity attribute.
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")
);
Awesome, right?
If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
The hibernate-types project supports more than hstore PostgreSQL type. You can map PostgreSQL-specific Enums, nullable Character, JSON, or even provide your own immutable Hibernate custom Types.
For more details about the hibernate-types project, check out this article.
FREE EBOOK
Is there an equivalent example for storing XML documents in Postgres using JPA with Hibernate?
I have searched the site for examples but cannot find it.
You can create a PostgreSQLXmlType and send me a Pull Request. I’ll check it out afterwards.
Hi,
I have followed the same. However when I am trying to save the entity, I am getting below exception
Caused by: org.postgresql.util.PSQLException: Can’t infer the SQL type to use for an instance of org.postgresql.util.PGobject. Use setObject() with an explicit Types value to specify the type to use.
at org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:973)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:169)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:169)
at com.vladmihalcea.hibernate.type.basic.PostgreSQLHStoreType.set(PostgreSQLHStoreType.java:52)
at com.vladmihalcea.hibernate.type.basic.PostgreSQLHStoreType.set(PostgreSQLHStoreType.java:25)
at com.vladmihalcea.hibernate.type.ImmutableType.nullSafeSet(ImmutableType.java:62)
at org.hibernate.type.CustomType.nullSafeSet(CustomType.java:158)
at org.hibernate.persister.entity.AbstractEntityPersister.dehydrate(AbstractEntityPersister.java:2843)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3121)
I am using jpa repository save method to save the entity.
Is there any thing missing here?
Thanks
Send me a Pull Request with a replicating test case. I’ll take a look at it once I have the test.
Hi Vlad,
Here is the github link for the code.. Sorry i couldnt replicate in the test case with an in memory database as its a postgres specific issue.
https://github.com/nehakhands/hstoretest
Thanks
NEha
You don’t need to use an in-memory database since this is a PostgreSQL column type. The
hibernate-typesproject already supports PostgreSQL test cases, and you just need to duplicate one of those and add your use case showing it does not work.In this repository, you are also using Spring which can influence the test outcome. For instance, when running the tests in your project this is what I get:
The only way to replicate the issue is to use the
hibernate-typestest cases as if there is a bug, I will need to have a test case so that the issue will never reoccur after it gets fixed.Why in PostgreSQLHStoreType you use PGobject instead of statement.setObject(index, value, Types.OTHER)? In PgStatement class of pg driver same code when setObject called with Map object and type specified.
That’s a very good observation. Send me a Pull Request with a fix and I’ll review it.
I want to write the custom user type for PostgreSQL datatype Citext. Can you please help me how we do that?
You need to fork my hibernate-types GitHub repository and create the new type. Use the existing type to get a better idea of what you need to do. Once you created the user type and the test case that proves its working, send me a Pull Request and I’ll check it out to see if I can integrate it.