为JPA/Hibernate实现PostgreSQL Hstore

Question

我正在尝试实现Hstore数据类型并将其用于JPA实体。然而，当我尝试持久化一些测试数据时，会出现几个错误。

为了实现Hstore数据类型，我使用了以下教程：在单个数据库列中使用Hibernate和PostgreSQL Hstore类型存储键值对集合 这是我应用程序中的代码：

一个帮助类，用于将Map转换为符合hstore语法的字符串以及反向转换：

public class HstoreHelper {

    private static final String K_V_SEPARATOR = "=>";

    public static String toString(Map<String, String> m) {
        if (m.isEmpty()) {
            return "";
        }
        StringBuilder sb = new StringBuilder();
        int n = m.size();
        for (String key : m.keySet()) {
            sb.append("\"" + key + "\"" + K_V_SEPARATOR + "\"" + m.get(key) + "\"");
            if (n > 1) {
                sb.append(", ");
                n--;
            }
        }
        return sb.toString();
    }

    public static Map<String, String> toMap(String s) {
        Map<String, String> m = new HashMap<String, String>();
        if (s.isEmpty()) {
            return m;
        }
        String[] tokens = s.split(", ");
        for (String token : tokens) {
            String[] kv = token.split(K_V_SEPARATOR);
            String k = kv[0];
            k = k.trim().substring(1, k.length() - 1);
            String v = kv[1];
            v = v.trim().substring(1, v.length() - 1);
            m.put(k, v);
        }
        return m;
    }
}

一个UserType实现：

public class HstoreUserType implements UserType {

      public Object assemble(Serializable cached, Object owner)
              throws HibernateException {
          return cached;
      }

      public Object deepCopy(Object o) throws HibernateException {
          Map m = (Map) o;
          return new HashMap(m);
      }

      public Serializable disassemble(Object o) throws HibernateException {
          return (Serializable) o;
      }

      public boolean equals(Object o1, Object o2) throws HibernateException {
          Map m1 = (Map) o1;
          Map m2 = (Map) o2;
          return m1.equals(m2);
      }

      public int hashCode(Object o) throws HibernateException {
          return o.hashCode();
      }

      public boolean isMutable() {
          return true;
      }

      public Object nullSafeGet(ResultSet rs, String[] strings, Object o)
              throws HibernateException, SQLException {
          String col = strings[0];
          String val = rs.getString(col);
          return HstoreHelper.toMap(val);
      }

      public void nullSafeSet(PreparedStatement ps, Object obj, int i)
              throws HibernateException, SQLException {
          String s = HstoreHelper.toString((Map) obj);
          ps.setObject(i, s, Types.OTHER);
      }

      public Object replace(Object original, Object target, Object owner)
              throws HibernateException {
          return original;
      }

      public Class returnedClass() {
          return Map.class;
      }

      public int[] sqlTypes() {
          return new int[] { Types.INTEGER };
      }
    }

并将其用于实体 Bean：

@Entity
@TypeDef(name = "hstore", typeClass = HstoreUserType.class)
@XmlRootElement
@Table(name = "address")
public class Address {
    .
    .
    .
    @Type(type = "hstore")
    @Column(columnDefinition = "hstore")
    private Map<String, String> hs = new HashMap<String, String>();
    .
    .
    .
}

我尝试使用地址实例发布应用程序，但出现了以下错误：

14:40:47,906 INFO  [stdout] (MSC service thread 1-1) Hibernate: insert into address (housenumber, location, street, zipcode, address_id) values (?, ?, ?, ?, ?)

14:40:47,921 WARN  [com.arjuna.ats.arjuna] (MSC service thread 1-1) ARJUNA012125: TwoPhaseCoordinator.beforeCompletion - failed for SynchronizationImple< 0:ffff0a49215c:-6aa8b5cf:53b3d0e3:133, org.hibernate.engine.transaction.synchronization.internal.RegisteredSynchronization@628b66 >: java.lang.AbstractMethodError
at org.hibernate.type.CustomType.nullSafeSet(CustomType.java:155) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]

14:40:48,062 INFO  [org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl] (MSC service thread 1-1) HHH000010: On release of batch it still contained JDBC statements
14:40:48,093 ERROR [org.jboss.msc.service.fail] (MSC service thread 1-1) MSC00001: Failed to start service jboss.deployment.unit."ductbased.war".component.TestDataGenerator.START: org.jboss.msc.service.StartException in service jboss.deployment.unit."ductbased.war".component.TestDataGenerator.START: Failed to start service
at org.jboss.msc.service.ServiceControllerImpl$StartTask.run(ServiceControllerImpl.java:1767) [jboss-msc-1.0.2.GA.jar:1.0.2.GA]

14:40:49,265 ERROR [org.jboss.as.server.deployment.scanner] (DeploymentScanner-threads - 2) {"JBAS014653: Composite operation failed and was rolled back. Steps that failed:" => {"Operation step-2" => {"JBAS014671: Failed services" => {"jboss.deployment.unit.\"ductbased.war\".component.TestDataGenerator.START" => "org.jboss.msc.service.StartException in service jboss.deployment.unit.\"ductbased.war\".component.TestDataGenerator.START: Failed to start service"}}}}

请问有人能告诉我为什么会出现这个错误吗？教程的作者声称他的代码可以正常运行，所以我感到有些孤立无援 :D 而且这也是我第一次实现自己的数据类型。

谢谢您的帮助！

- Fred Funks

---

你能在Github或其他地方创建一个项目吗？这样调试起来会更容易。 - The Alchemist

你有没有得到过答案？我们正在尝试弄清楚在这里使用哪种Types.X。 - JamesD

不幸的是，我没有源文件的访问权限了。我只有一点时间来尝试这个。但如果有人能以正确的方式实现它并分享出来，那就太好了。 - Fred Funks

请查看我在此类似问题上的回答：https://dev59.com/g33aa4cB1Zd3GeqPblNu#38561178 - Adam Michalik

Answer 1

对于那些在2020年找到这里的人，hibernate-types现在原生支持此功能。所有功劳归Vlad Mihalcea所有。复制代码的部分以保留相同代码。

https://vladmihalcea.com/map-postgresql-hstore-jpa-entity-property-hibernate/

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

在你的数据库中

CREATE EXTENSION IF NOT EXISTS hstore;
ALTER Table <Table_Name> ADD COLUMN <column_name> hstore;

在你的POJO类中

@Entity(name = "Book")
@Table(name = "book")
@TypeDef(name = "hstore", typeClass = PostgreSQLHStoreType.class)
public 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
}

示例条目

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

- Vishnoo Rath

Answer 2

以下是使用Hibernate和HStore的示例。

首先编写一个用户类型：

/**
 * Custom Hibernate {@link UserType} used to convert between a {@link Map}
 * and PostgreSQL {@code hstore} data type.
 */
public class HStoreType implements UserType {

    /**
     * PostgreSQL {@code hstore} field separator token.
     */
    private static final String HSTORE_SEPARATOR_TOKEN = "=>";

    /**
     * {@link Pattern} used to find and split {@code hstore} entries.
     */
    private static final Pattern HSTORE_ENTRY_PATTERN = Pattern.compile(
        String.format("\"(.*)\"%s\"(.*)\"", HSTORE_SEPARATOR_TOKEN)
    );
    
    public static final int SQL_TYPE = Types.OTHER;

    @Override
    public int[] sqlTypes() {
        return new int[] { SQL_TYPE };
    }

    @SuppressWarnings("rawtypes")
    @Override
    public Class returnedClass() {
        return Map.class;
    }

    @Override
    public boolean equals(final Object x, final Object y) throws HibernateException {
        return x.equals(y);
    }

    @Override
    public int hashCode(final Object x) throws HibernateException {
        return x.hashCode();
    }

    @Override
    public Object nullSafeGet(final ResultSet rs, final String[] names,
            final SharedSessionContractImplementor session, final Object owner)
            throws HibernateException, SQLException {
        return convertToEntityAttribute(rs.getString(names[0]));
    }

    @SuppressWarnings("unchecked")
    @Override
    public void nullSafeSet(final PreparedStatement st, final Object value, final int index,
            final SharedSessionContractImplementor session) throws HibernateException, SQLException {
        st.setObject(index, convertToDatabaseColumn((Map<String,Object>)value), SQL_TYPE);

    }

    @SuppressWarnings("unchecked")
    @Override
    public Object deepCopy(final Object value) throws HibernateException {
        return new HashMap<String,Object>(((Map<String,Object>)value));
    }

    @Override
    public boolean isMutable() {
        return true;
    }

    @Override
    public Serializable disassemble(final Object value) throws HibernateException {
        return (Serializable) value;
    }

    @Override
    public Object assemble(final Serializable cached, final Object owner)
            throws HibernateException {
        return cached;
    }

    @Override
    public Object replace(final Object original, final Object target, final Object owner)
            throws HibernateException {
        return original;
    }


    private String convertToDatabaseColumn(final Map<String, Object> attribute) {
        final StringBuilder builder = new StringBuilder();
        for (final Map.Entry<String, Object> entry : attribute.entrySet()) {
            if(builder.length() > 1) {
                builder.append(", ");
            }
            builder.append("\"");
            builder.append(entry.getKey());
            builder.append("\"");
            builder.append(HSTORE_SEPARATOR_TOKEN);
            builder.append("\"");
            builder.append(entry.getValue().toString());
            builder.append("\"");
        }
        return builder.toString();
    }

    private Map<String, Object> convertToEntityAttribute(final String dbData) {
        final Map<String, Object> data = new HashMap<String, Object>();
        final StringTokenizer tokenizer = new StringTokenizer(dbData, ",");

        while(tokenizer.hasMoreTokens()) {
            final Matcher matcher = HSTORE_ENTRY_PATTERN.matcher(tokenizer.nextToken().trim());
            if(matcher.find()) {
                data.put(matcher.group(1), matcher.group(2));
            }
        }

        return data;
    }
}

其次编写一个SQL函数适配器。

public class HStoreValueFunction implements SQLFunction {

    @Override
    public boolean hasArguments() {
        return true;
    }

    @Override
    public boolean hasParenthesesIfNoArguments() {
        return false;
    }

    @Override
    public Type getReturnType(Type type, Mapping mpng) throws QueryException {
        return new StringType();
    }

    @Override
    public String render(Type type, List args, SessionFactoryImplementor sfi) throws QueryException {
        if (args.size() < 2) {
            throw new IllegalArgumentException("2 arguments required");
        }
        String field = (String) args.get(0);
        String key = (String) args.get(1);
        return field + " -> " + key;
    }
}

并在自定义方言中注册它：

public class ExpandedPostgresDialect extends PostgreSQL95Dialect {

    public ExpandedPostgresDialect() {
        super();
        registerFunction("hstoreValue", new HStoreValueFunction());
    }
}

作为结果，您将能够在实体中使用它，例如：

@Embeddable
public class TranslatableField {
    
    private String value;
    
    @Type(type = "it.walczak.examples.in18jpa.concepts.b.hibernate.HStoreType")
    @Column(columnDefinition = "hstore")
    private Map<String, String> translationByLanguage = new HashMap<>();

在JPQL查询中，可以使用以下语句：

select p from Product p
where lower(p.details.name.value) like concat('%', lower(?1), '%')
or lower(hstoreValue(p.details.name.translationsByLanguage, ?2))
like concat('%', lower(?1), '%')

更详细的文章和完整的工作代码示例，请参阅：https://walczak.it/blog/performant-internationalization-in-postgresql-hibernate-jpa-hstore-column-with-translations

- Adam from WALCZAK.IT