Is there an elegant and common way to convert PostgreSQL hstore to JPA 2.1 DataType?

Question

I used JPA 2.1 Converter to convert PostgreSQL hstore to Map<String, String> . But I did not find a universal way for different JPA providers, such as EclipseLink and Hibernate. Therefore, I need to write different Converters for each JPA provider.

The following is an example of using different converters for EclipseLink and Hibernate. https://github.com/phstudy/jpa-converter-sample

Is there a universal way for different JPA providers?

Answer 1

The PostgreSQL JDBC driver provides org.postgresql.util.HStoreConverter with / from String and byte[] conversions. You can use it to implement your own JPA 2.1 Converter :

 import javax.persistence.AttributeConverter; import javax.persistence.Converter; import java.util.Map; import org.postgresql.util.HStoreConverter; @Converter public class MyHStoreConverter implements AttributeConverter<Map<String, String>, String> { @Override public String convertToDatabaseColumn(Map<String, String> attribute) { return HStoreConverter.toString(attribute); } @Override public Map<String, String> convertToEntityAttribute(String dbData) { return HStoreConverter.fromString(dbData); } } 

Then use it with the JPA Convert annotation in your entity:

 @Entity public class MyEntity { @Convert(converter = MyHStoreConverter.class) private Map<String, String> hstoreAttribute; } 

This is an implementation that uses only JPA standards, so it must be agnostic to the JPA provider. However, using Converter to map to a general Map<> previously blocked by the Hibernate HHH-8804 error, but this was fixed in Hibernate 5.0.0 and 4.3.11.

Answer 2

I have not used it, but I understand that the new JPA 2.1 @Converter was designed for a universal way to create custom types.

It looks like you have the right solution, but you have problems with the EclipseLink and Hibernate implementations.

You can probably find your way out by handling all the different cases with one convertToEntityAttribute(Object object) method.

Answer 3

I tried (parts) of your code, but I get an exception when creating the table: Internal exception: org.postgresql.util.PSQLException: Error: The type "hstore" does not exist (Postgresql 9.1). This means that I have only an idea. Try defining both methods:

 @Override convertToEntityAttribute(Object object) 

and

 //without @Override convertToEntityAttribute(String string) 

and then do the same for the other method. Finally, change which one is exceeded.

PS: you can use the @ElementCollection annotation to display Map directly, thus avoiding conversions (if you are not working with an obsolete database that already has data)