|
|
|
|
|
|
Hibernate ignores the return value for
{{javax.persistence.Parameter#getParameterType()}} when provided to {{javax.persistence.Query#setParameter(Parameter<T> param, T value)}}.
The
This can cause problems for Oracle when a null value is bound for a parameter (assumed to be BINARY) that is compared with a NUMBER column, because Oracle does not allow conversion from BINARY to NUMBER. Oracle requires that the null value be bound as a type that is compatible with NUMBER. JPA provides a way to specify the type using javax.persistence.Parameter, but since Hibernate ignores the return value from {{javax.persistence.Parameter#getParameterType()}}, the query will fail because the value continues to be bound as a BINARY value.
For example, the following should native query work on Oracle , but it is apparently broken:
String sql = "SELECT * FROM schema.Tabela WHERE integerColumn = ? OR integerColumn is null "
Query query = getEntityManager().createNativeQuery(sql, classe);
Parameter p = new Parameter() {
@Override
public String getName() {
return null;
}
@Override
public Integer getPosition() {
return 1;
}
@Override
public Class getParameterType() {
return Integer.TYPE;
}
};
query.setParameter( p, null );
query.getResultList(); // also fails !!!
A workaround is to get the get underlying org.hibernate.Query and use its API:
String sql = "SELECT * FROM schema.Tabela WHERE integerColumn = ?"
Query query = getEntityManager().createNativeQuery(sql, classe);
q.unwrap( HibernateQuery.class ).getHibernateQuery() // gets org.hibernate.Query
.setParameter(
0, // org.hibernate.Query parameters start at 0
null, // the null value
StandardBasicTypes.INTEGER // the parameter type
);
query.getResultList(); // succeeds
Note that the condition "integerColumn = ?" will always be false if null is bound (because a null value is considered "unknown"; an null (unknown) value will never equal another null (unknown) value. If the intention is for also check for a null value then the query should be:
"SELECT * FROM schema.Tabela WHERE integerColumn = ? OR integerColumn is null"
|
|
|
|
|
|
