java 有没有一种方法可以使用Hibernate/JPQL查询PostgreSQL hstore？

bfnvny8b  于 2023-08-01  发布在  Java
关注(0)|答案(3)|浏览(186)

假设我有一个Hibernate/JPA实体如下：

  1. @Entity
  2. public class FooEntity {
  4.   ...
  6.   @Type(type = "hstore")
  7.   HashMap<String, String> tags;
  8. }

字符串
...而hstore Type是来自this资源的简单UserType实现。
有没有一种方法可以在JPQL查询中访问hstore，类似于下面的伪代码：

  1. SELECT f FROM FooEntity f WHERE f.tags CONTAINS KEY(:key)

型

Java

3条答案

按热度按时间
jslywgbw

jslywgbw1#

您也可以简单地创建一个Hibernate org. hibernate. usertype. UserType。你扩展了那个类;我们自己实现的一个例子：

  1. public class HstoreUserType implements UserType {
  3. /**
  4.  * PostgreSQL {@code hstore} field separator token.
  5.  */
  6. private static final String HSTORE_SEPARATOR_TOKEN = "=>";
  8. /**
  9.  * {@link Pattern} used to find and split {@code hstore} entries.
  10.  */
  11. private static final Pattern HSTORE_ENTRY_PATTERN = Pattern.compile(String.format("\"(.*)\"%s\"(.*)\"", HSTORE_SEPARATOR_TOKEN));
  13. /**
  14.  * The PostgreSQL value for the {@code hstore} data type.
  15.  */
  16. public static final int HSTORE_TYPE = 1111;
  18. @Override
  19. public int[] sqlTypes() {
  20.     return new int[] { HSTORE_TYPE };
  21. }
  23. @SuppressWarnings("rawtypes")
  24. @Override
  25. public Class returnedClass() {
  26.     return Map.class;
  27. }
  29. @Override
  30. public boolean equals(final Object x, final Object y) throws HibernateException {
  31.     return x.equals(y);
  32. }
  34. @Override
  35. public int hashCode(final Object x) throws HibernateException {
  36.     return x.hashCode();
  37. }
  39. @Override
  40. public Object nullSafeGet(final ResultSet rs, final String[] names,
  41.         final SessionImplementor session, final Object owner)
  42.         throws HibernateException, SQLException {
  43.     return convertToEntityAttribute(rs.getString(names[0]));
  44. }
  46. @SuppressWarnings("unchecked")
  47. @Override
  48. public void nullSafeSet(final PreparedStatement st, final Object value, final int index,
  49.         final SessionImplementor session) throws HibernateException, SQLException {
  50.     st.setObject(index, convertToDatabaseColumn((Map<String,Object>)value), HSTORE_TYPE);
  52. }
  54. @SuppressWarnings("unchecked")
  55. @Override
  56. public Object deepCopy(final Object value) throws HibernateException {
  57.     return new HashMap<String,Object>(((Map<String,Object>)value));
  58. }
  60. @Override
  61. public boolean isMutable() {
  62.     return true;
  63. }
  65. @Override
  66. public Serializable disassemble(final Object value) throws HibernateException {
  67.     return (Serializable) value;
  68. }
  70. @Override
  71. public Object assemble(final Serializable cached, final Object owner)
  72.         throws HibernateException {
  73.     return cached;
  74. }
  76. @Override
  77. public Object replace(final Object original, final Object target, final Object owner)
  78.         throws HibernateException {
  79.     return original;
  80. }
  82. private String convertToDatabaseColumn(final Map<String, Object> attribute) {
  83.     final StringBuilder builder = new StringBuilder();
  84.     for (final Map.Entry<String, Object> entry : attribute.entrySet()) {
  85.         if(builder.length() > 1) {
  86.             builder.append(", ");
  87.         }
  88.         builder.append("\"");
  89.         builder.append(entry.getKey());
  90.         builder.append("\"");
  91.         builder.append(HSTORE_SEPARATOR_TOKEN);
  92.         builder.append("\"");
  93.         builder.append(entry.getValue().toString());
  94.         builder.append("\"");
  95.     }
  96.     return builder.toString();
  97. }
  99. private Map<String, Object> convertToEntityAttribute(final String dbData) {
  100.     final Map<String, Object> data = new HashMap<String, Object>();
  101.     if (dbData != null) {
  102.         final StringTokenizer tokenizer = new StringTokenizer(dbData, ",");
  103.         while(tokenizer.hasMoreTokens()) {
  104.             final Matcher matcher = HSTORE_ENTRY_PATTERN.matcher(tokenizer.nextToken().trim());
  105.             if(matcher.find()) {
  106.                 data.put(matcher.group(1), matcher.group(2));
  107.             }
  108.         }
  109.     }
  110.     return data;
  111. }

字符串
}
现在你可以在Entity bean中使用它，如下所示：

  1. @Entity
  2. @Table(name="YourEntityBeanTable")
  3. @TypeDefs({
  4.     @TypeDef(name = "hstore",  typeClass = HstoreUserType.class)
  5. })
  7. public class YourEntityBean {
  9. .....
  11.     @Type(type = "hstore")    
  12.     @Column(name= "an_hstore_column", columnDefinition = "hstore")
  13.     private Map<String, String> anHStoreColumn = new HashMap<>();
  16. }

型

展开查看全部
赞(0）分享  回复(0）举报  2023-08-01
laawzig2

laawzig22#

Hibernate提供了一个跨许多DB的通用查询抽象，因此很难抽象出非SQL语法。
我会使用一个本地查询来获取id，并使用它们来获取Hibernate实体，如果您确实需要的话。
如果您只对投影感兴趣，那么原生查询是您的最佳选择。

赞(0）分享  回复(0）举报  2023-08-01
sqougxex

sqougxex3#

下面是一个如何将HStore与Hibernate一起使用的示例。
首先编写一个用户类型：

  1. /**
  2.  * Custom Hibernate {@link UserType} used to convert between a {@link Map}
  3.  * and PostgreSQL {@code hstore} data type.
  4.  */
  5. public class HStoreType implements UserType {
  7.     /**
  8.      * PostgreSQL {@code hstore} field separator token.
  9.      */
  10.     private static final String HSTORE_SEPARATOR_TOKEN = "=>";
  12.     /**
  13.      * {@link Pattern} used to find and split {@code hstore} entries.
  14.      */
  15.     private static final Pattern HSTORE_ENTRY_PATTERN = Pattern.compile(
  16.         String.format("\"(.*)\"%s\"(.*)\"", HSTORE_SEPARATOR_TOKEN)
  17.     );
  18.     
  19.     public static final int SQL_TYPE = Types.OTHER;
  21.     @Override
  22.     public int[] sqlTypes() {
  23.         return new int[] { SQL_TYPE };
  24.     }
  26.     @SuppressWarnings("rawtypes")
  27.     @Override
  28.     public Class returnedClass() {
  29.         return Map.class;
  30.     }
  32.     @Override
  33.     public boolean equals(final Object x, final Object y) throws HibernateException {
  34.         return x.equals(y);
  35.     }
  37.     @Override
  38.     public int hashCode(final Object x) throws HibernateException {
  39.         return x.hashCode();
  40.     }
  42.     @Override
  43.     public Object nullSafeGet(final ResultSet rs, final String[] names,
  44.             final SharedSessionContractImplementor session, final Object owner)
  45.             throws HibernateException, SQLException {
  46.         return convertToEntityAttribute(rs.getString(names[0]));
  47.     }
  49.     @SuppressWarnings("unchecked")
  50.     @Override
  51.     public void nullSafeSet(final PreparedStatement st, final Object value, final int index,
  52.             final SharedSessionContractImplementor session) throws HibernateException, SQLException {
  53.         st.setObject(index, convertToDatabaseColumn((Map<String,Object>)value), SQL_TYPE);
  55.     }
  57.     @SuppressWarnings("unchecked")
  58.     @Override
  59.     public Object deepCopy(final Object value) throws HibernateException {
  60.         return new HashMap<String,Object>(((Map<String,Object>)value));
  61.     }
  63.     @Override
  64.     public boolean isMutable() {
  65.         return true;
  66.     }
  68.     @Override
  69.     public Serializable disassemble(final Object value) throws HibernateException {
  70.         return (Serializable) value;
  71.     }
  73.     @Override
  74.     public Object assemble(final Serializable cached, final Object owner)
  75.             throws HibernateException {
  76.         return cached;
  77.     }
  79.     @Override
  80.     public Object replace(final Object original, final Object target, final Object owner)
  81.             throws HibernateException {
  82.         return original;
  83.     }
  85.     private String convertToDatabaseColumn(final Map<String, Object> attribute) {
  86.         final StringBuilder builder = new StringBuilder();
  87.         for (final Map.Entry<String, Object> entry : attribute.entrySet()) {
  88.             if(builder.length() > 1) {
  89.                 builder.append(", ");
  90.             }
  91.             builder.append("\"");
  92.             builder.append(entry.getKey());
  93.             builder.append("\"");
  94.             builder.append(HSTORE_SEPARATOR_TOKEN);
  95.             builder.append("\"");
  96.             builder.append(entry.getValue().toString());
  97.             builder.append("\"");
  98.         }
  99.         return builder.toString();
  100.     }
  102.     private Map<String, Object> convertToEntityAttribute(final String dbData) {
  103.         final Map<String, Object> data = new HashMap<String, Object>();
  104.         final StringTokenizer tokenizer = new StringTokenizer(dbData, ",");
  106.         while(tokenizer.hasMoreTokens()) {
  107.             final Matcher matcher = HSTORE_ENTRY_PATTERN.matcher(tokenizer.nextToken().trim());
  108.             if(matcher.find()) {
  109.                 data.put(matcher.group(1), matcher.group(2));
  110.             }
  111.         }
  113.         return data;
  114.     }
  115. }

字符串
其次编写一个SQL函数适配器：

  1. public class HStoreValueFunction implements SQLFunction {
  3.     @Override
  4.     public boolean hasArguments() {
  5.         return true;
  6.     }
  8.     @Override
  9.     public boolean hasParenthesesIfNoArguments() {
  10.         return false;
  11.     }
  13.     @Override
  14.     public Type getReturnType(Type type, Mapping mpng) throws QueryException {
  15.         return new StringType();
  16.     }
  18.     @Override
  19.     public String render(Type type, List args, SessionFactoryImplementor sfi) throws QueryException {
  20.         if (args.size() < 2) {
  21.             throw new IllegalArgumentException("2 arguments required");
  22.         }
  23.         String field = (String) args.get(0);
  24.         String key = (String) args.get(1);
  25.         return field + " -> " + key;
  26.     }
  27. }

型
并以自定义方言进行注册：

  1. public class ExpandedPostgresDialect extends PostgreSQL95Dialect {
  3.     public ExpandedPostgresDialect() {
  4.         super();
  5.         registerFunction("hstoreValue", new HStoreValueFunction());
  6.     }
  7. }

型
因此，您将能够在以下实体中使用它：

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

型
以及在JPQL查询中，例如：

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

型
有关更深入的文章以及完整的代码示例，请参阅：https://walczak.it/blog/performant-internationalization-in-postgresql-hibernate-jpa-hstore-column-with-translations

展开查看全部
赞(0）分享  回复(0）举报  2023-08-01
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com