休眠、Postgres &数组类型 [英] Hibernate, Postgres & Array Type

问题描述

我在使用 数组类型 在 postgresql 9.3 中映射到休眠 4.1.0.这种类型允许我拥有非常强大的数据模型，而无需构建大量表和连接.

I'm stuck on a specific issue using array type in postgresql 9.3 mapped with hibernate 4.1.0. This type allows me to have really strong data model, without building lots of tables and joins.

为了映射以这种特定类型存储的字段，我使用了 用户类型

In order to map a field stored with this particular type, I have used a UserType

无论如何，它适用于纯休眠 (hql)，但我还需要将 sql 本机查询 发送到我的数据库.当我这样做时，尽管尝试了很多次，但我还没有找到任何方法来做到这一点.

Anyway, it works well with pure hibernate (hql) but I need also to send sql native query to my database. When I do it, in spite of many tries, I have not found any way to do that.

我尝试了许多基于此的语法

I try many syntaxes based on this

String[] values = {"value1", "value2"};
String queryString = "SELECT * FROM instances WHERE values && :values";
Query query = this.getSession().createSQLQuery(queryString).addEntity(Instance.class);
query.setParameterList("values", values);
query.list();

我得到了运算符不存在:text[] &&性格变化

I got Operator does not exists : text[] && character varying

它应该在 jdbc 中给出以下语法:['value1', 'value2'] 并且它似乎给出了 'value1'...

It should give following syntax in jdbc : ['value1', 'value2'] and it seems to give 'value1'...

我尝试了很多语法与

• 收藏
• 纯数组
• [ :values ] 语法:我有["附近的语法错误

我需要发送原生查询，因为我使用物化视图来提高性能.

I need to send native query because I use Materialized View for performance gains.

我的 SQL 查询在 postgresql 控制台中工作.所以这是一个休眠特定的问题.

My SQL Query works in postgresql console. So it is an hibernate specific issue.

推荐答案

我尝试了几个基于JDBC4引入的Array Type的版本:如何将 String[] 参数设置为原生查询?.问题也是 Hibernate(即使在最新版本 4.3.1.final 中)也不适用于此新功能并给我以下错误消息

I tried few versions based on Array Type introduced by JDBC4 : How can I set a String[] parameter to a native query?. Problem is also Hibernate (even in last version 4.3.1.final) does not work with this new features and gave me following error message

Could not determine a type for class: org.postgresql.jdbc4.Jdbc4Array

所以我必须创建一个特定的 UserType(基于 stackoverflow 中的几篇文章和其他来源)

So I had to make a Specific UserType (based on several articles in stackoverflow, and others sources)

我的模型

@Type(type = "fr.mycompany.dao.hibernate.types.ArrayUserType")
private String[] values;

我的ArrayUserType

public class ArrayUserType implements UserType {

/** Constante contenant le type SQL "Array".
 */
protected static final int[] SQL_TYPES = { Types.ARRAY };

/**
 * Return the SQL type codes for the columns mapped by this type. The
 * codes are defined on <tt>java.sql.Types</tt>.
 * 
 * @return int[] the typecodes
 * @see java.sql.Types
 */
public final int[] sqlTypes() {
    return SQL_TYPES;
}

/**
 * The class returned by <tt>nullSafeGet()</tt>.
 * 
 * @return Class
 */
public final Class returnedClass() {
    return String[].class;
}

/**
 * Retrieve an instance of the mapped class from a JDBC resultset. Implementors
 * should handle possibility of null values.
 * 
 * @param resultSet a JDBC result set.
 * @param names the column names.
 * @param session SQL en cours.
 * @param owner the containing entity 
 * @return Object
 * @throws org.hibernate.HibernateException exception levée par Hibernate
 * lors de la récupération des données.
 * @throws java.sql.SQLException exception SQL 
 * levées lors de la récupération des données.
 */
@Override
public final Object nullSafeGet(
        final ResultSet resultSet, 
        final String[] names, 
        final SessionImplementor session, 
        final Object owner) throws HibernateException, SQLException {
    if (resultSet.wasNull()) {
        return null;
    }

    String[] array = (String[]) resultSet.getArray(names[0]).getArray();
    return array;
}

/**
 * Write an instance of the mapped class to a prepared statement. Implementors
 * should handle possibility of null values. A multi-column type should be written
 * to parameters starting from <tt>index</tt>.
 * 
 * @param statement a JDBC prepared statement.
 * @param value the object to write
 * @param index statement parameter index
 * @param session sql en cours
 * @throws org.hibernate.HibernateException exception levée par Hibernate
 * lors de la récupération des données.
 * @throws java.sql.SQLException exception SQL 
 * levées lors de la récupération des données.
 */
@Override
public final void nullSafeSet(final PreparedStatement statement, final Object value, 
        final int index, final SessionImplementor session) throws HibernateException, SQLException {

    if (value == null) {
        statement.setNull(index, SQL_TYPES[0]);
    } else {
        String[] castObject = (String[]) value;
        Array array = session.connection().createArrayOf("text", castObject);
        statement.setArray(index, array);
    }
}

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

@Override
public final boolean isMutable() {
    return false;
}

@Override
public final Object assemble(final Serializable arg0, final Object arg1)
        throws HibernateException {
    // TODO Auto-generated method stub
    return null;
}

@Override
public final Serializable disassemble(final Object arg0) throws HibernateException {
    // TODO Auto-generated method stub
    return null;
}

@Override
public final boolean equals(final Object x, final Object y) throws HibernateException {
    if (x == y) {
        return true;
    } else if (x == null || y == null) {
        return false;
    } else {
        return x.equals(y);
    }
}

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

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

}

最后一个，但最不重要的(这是我错过的):当我需要运行 SQL Native Query 时，我必须使用以下语法强制参数类型

And the last, but least (that's what I missed) : when I need to run SQL Native Query, I have to force the parameter type with the following syntax

String[] values = ...
Type arrayType = new CustomType(new ArrayUserType());
query.setParameter("value", values, arrayType);

这篇关于休眠、Postgres &数组类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！