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

bfnvny8b  于 10个月前  发布在  Java
关注(0)|答案(3)|浏览(61)

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

@Entity
public class FooEntity {

  ...

  @Type(type = "hstore")
  HashMap<String, String> tags;
}

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

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

型

Java

3条答案

按热度按时间
jslywgbw

jslywgbw1#

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

public class HstoreUserType 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));

/**
 * The PostgreSQL value for the {@code hstore} data type.
 */
public static final int HSTORE_TYPE = 1111;

@Override
public int[] sqlTypes() {
    return new int[] { HSTORE_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 SessionImplementor 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 SessionImplementor session) throws HibernateException, SQLException {
    st.setObject(index, convertToDatabaseColumn((Map<String,Object>)value), HSTORE_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>();
    if (dbData != null) {
        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;
}

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

@Entity
@Table(name="YourEntityBeanTable")
@TypeDefs({
    @TypeDef(name = "hstore",  typeClass = HstoreUserType.class)
})

public class YourEntityBean {

.....

    @Type(type = "hstore")    
    @Column(name= "an_hstore_column", columnDefinition = "hstore")
    private Map<String, String> anHStoreColumn = new HashMap<>();


}

型

赞(0）分享  回复(0）举报  10个月前
laawzig2

laawzig22#

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

赞(0）分享  回复(0）举报  10个月前
sqougxex

sqougxex3#

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

/**
 * 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

赞(0）分享  回复(0）举报  10个月前
我来回答

相关问题

    查看更多
    微信公众号

    热门标签

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

    最新问答

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