Is there a elegant and common way for converting PostgreSQL hstore to JPA 2.1 DataType?

Question

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

The following is the example which uses different Converters for EclipseLink and Hibernate. https://github.com/phstudy/jpa-converter-sample

Is there an universal way for different JPA providers?

Vigesimal

Really haven't heard of anything like this. maybe because hstore is not standard SQL datatype. It would be great if you could use something like @elementcollection on it, but even this solution would not allow you to query properly IMO – Owain

EclipseLink will take the object as is from the driver - getObject() is returning a map not a string. So I don't think its an EclipseLink bug. If you remove generics, will the EclipseLink converter not work in Hibernate? – Beggarly

Hi @Chris, Hibernate will throw org.postgresql.util.PSQLException: Unsupported Types value: 494,394,026. The detailed exception – Vigesimal

@Vigesimal - Could you manage to persist data in Postgres using this method? I get a is of type hstore but expression is of type character varying if treating as a String, or Unsupported Types value: <random numbers> if attempting as an Object. – Intendancy

Hi @Pool, which JPA provider do you use? Hibernate or EclipseLink? – Vigesimal

I'm using Hibernate. After reading #15974974 I concluded it is not possible without using Hibernate specific classes or (probably what I will use) modifying Postgres to allow automatic conversion. – Intendancy

Answer 1

The PostgreSQL JDBC driver provides a org.postgresql.util.HStoreConverter utility class with to/from String and byte[] conversion. 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 using only JPA standards, so should be JPA-provider agnostic. However, using the Converter to map to a generic Map<> has earlier been blocked by the Hibernate bug HHH-8804, but that has been fixed in Hibernate 5.0.0 and 4.3.11.

Strawboard

I don't think this is necessary any more. The current Postgres JDBC driver returns a Map instance when calling ResultSet.getObject() – Contumelious

@a_horse_with_no_name - would you care to post it as an answer together with an example? Having the setup as above, I removed the @Convert annotation and the application failed to start with an error Could not determine type for: java.util.Map, at table: MyEntity, for columns: [org.hibernate.mapping.Column(hstoreAttribute)] (Hibernate 4.3.11). So it does not seem to work just out of the box. – Strawboard

No idea. I don't use obfuscation layers like Hibernate. I just thought that Hibernate might be smart enough to properly deal with a Map instance. – Contumelious

@AdamMichalik thanks, I have updated my sample project. And it works with eclipselink and hibernate. github.com/phstudy/jpa-converter-sample – Vigesimal

@AdamMichalik i tried with hibernate 5.2.10 and postgresql 9.6 and i get error: org.postgresql.util.PSQLException: ERROR: column "fields" is of type hstore but expression is of type character varying Indice : You will need to rewrite or cast the expression. – Despair

@roberttrudel I have no clue why that would be. Try posting it as a separate question with more details about your code. – Strawboard

@AdamMichalik already done: #45698165 – Despair

Answer 2

I haven't used it, but my understanding is that the new JPA 2.1 @Converter has been designed to bring a universal way to create a custom types.

It looks like you have the right solution but you're having troubles with the EclipseLink and Hibernate implementations.

You probably can find your way out by handling all different cases in a single convertToEntityAttribute(Object object) method.

Caduceus

I have ever tried convertToEntityAttribute(Object object), it will cause Hibernate to throw a exception. – Vigesimal

Answer 3

I have tried (parts of) your code, but get an exception when the table is generated: Internal Exception: org.postgresql.util.PSQLException: Error: Type »hstore« does not exist (Postgresql 9.1). This means I have only an idea. Try to define both methods:

@Override
convertToEntityAttribute(Object object)

and

//without @Override
convertToEntityAttribute(String string)

and then do the same for the other method. Eventually change which one is overriden.

PS: you could use the @ElementCollection annotation to directly map the Map, this way avoiding the Converters (if you are not working with a legacy DB that already has the data)

Excommunicate

For PostgreSQL 9.1, you need to install the hstore extension manually. Use CREATE EXTENSION hstore; – Vigesimal

I have also tried this, but the method convertToEntityAttribute(String string) will not be invoked. – Vigesimal

Does it need to be invoked? If Hibernate uses the Object one, it would just be used to get around the Hibernate exception you mentioned in an earlier comment. – Beggarly

This method maybe not require to be invoked, but I have declared both of them and also got the same Exception org.postgresql.util.PSQLException: Unsupported Types value: 494,394,026. – Vigesimal