休眠，Postgres的＆安培;数组类型 [英] Hibernate, Postgres & Array Type
问题描述
我使用的 数组类型 <被困在一个具体问题/ STRONG> PostgreSQL中9.3映射与Hibernate 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();
我
运营商并不存在：文本[]和放大器;＆安培;字符改变
I got Operator does not exists : text[] && character varying
它应该给下面的JDBC语法：['值1'，'值']，这似乎给'值1'...
It should give following syntax in jdbc : ['value1', 'value2'] and it seems to give 'value1'...
我试过很多语法
以
I tried many syntaxes with
- 收藏
- 纯阵列
- [：值]语法：我有
附近有语法错误[
我需要的，因为我使用物化视图的性能提升发送本地查询。
I need to send native query because I use Materialized View for performance gains.
我的SQL查询工作在PostgreSQL的控制台。因此，它是一个Hibernate的具体问题。
My SQL Query works in postgresql console. So it is an hibernate specific issue.
推荐答案
我试过几个版本基于JDBC4介绍阵列类型：<一href=\"http://stackoverflow.com/questions/12042181/how-can-i-set-a-string-parameter-to-a-native-query\">How我可以设置的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
所以我不得不做出一个特定的用户类型（基于计算器几篇文章，以及其他来源）
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")
私有String []值;
private String[] values;
我ArrayUserType
My 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原生查询，我必须强制使用以下语法参数类型
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屋！
