在clob参数的where子句中使用函数 [英] Using function in where clause with clob parameter
问题描述
我们使用 @NamedNativeQuery
从我们的数据库中获取由存储过程 flexmatch
限定的实体查询的where子句。
这一般工作正常,但当参数 chimeString
超过了4.000个字符时无法引发以下异常:
ORA-01460:请求未执行或不合理的转换
这是有道理的,因为4.000字符是Oracle在String和Clob之间的边界。
我们试图用
$ b
-
使用
org.hibernate.engine.jdbc.ClobProxy
return entityManager
.createNamedQuery( Structure.findByExactMatch,Structure.class)
.setParameter(chime,ClobProxy.generateProxy(chimeString))
.getResultList();
-
使用
javax.persistence.criteria.ParameterExpression
连同org.hibernate.engine.jdbc.ClobProxy
ParameterExpression<&的Clob GT; chimeParam = entityManager
.getCriteriaBuilder()
.parameter(Clob.class,chime);
返回entityManager
.createNamedQuery(Structure.findByExactMatch,Structure.class)
.setParameter(chimeParam,ClobProxy.generateProxy(chimeString))
.getResultList();
- Oracle 11g
- Hibernate 3.6.6 / ul>
use
org.hibernate.engine.jdbc.ClobProxy
return entityManager .createNamedQuery("Structure.findByExactMatch", Structure.class) .setParameter("chime", ClobProxy.generateProxy(chimeString)) .getResultList();
use
javax.persistence.criteria.ParameterExpression
together withorg.hibernate.engine.jdbc.ClobProxy
ParameterExpression<Clob> chimeParam = entityManager .getCriteriaBuilder() .parameter(Clob.class, "chime"); return entityManager .createNamedQuery("Structure.findByExactMatch", Structure.class) .setParameter(chimeParam, ClobProxy.generateProxy(chimeString)) .getResultList();
- Oracle 11g
- Hibernate 3.6.6
Libraries&系统: 查找方法。
$ b
public列表与LT;结构> findByExactMatch(String chimeString){
返回entityManager
.createNamedQuery(Structure.findByExactMatch,Structure.class)
.setParameter(chime,chimeString)
.getResultList() ;
}
结构实体
@Entity
@NamedNativeQueries({
@NamedNativeQuery(
name =Structure.findByExactMatch,
query =SELECT id,molfile(ctab)ctab FROM structure+
where flexmatch(ctab,:chime,'all')= 1,
resultClass = Structure.class)})
public class Structure {
@Id
@Column(name =ID)
私人长ID;
@Lob
@Column(name =CTAB)
private String ctab;
// getter& setter
}
编辑1 pl你可以看到它被重载。
FUNCTION flexmatch(
molobj IN BLOB,
querymol IN VARCHAR2,
args IN VARCHAR2)
RETURN NUMBER
FUNCTION Flexmatch(
molobj IN BLOB,
querymol IN CLOB,
args IN VARCHAR2)
RETURN NUMBER
We are using a @NamedNativeQuery
to fetch entities from our database that are qualified by the stored procedure flexmatch
in the where clause of a query.
This works fine in general, but when the parameter chimeString
exceeds 4.000 characters it fails raising the following exception:
ORA-01460: unimplemented or unreasonable conversion requested
This does make sense, as 4.000 characters are Oracle's border between String and Clob.
We tried to
Libraries & System:
The find method.
public List<Structure> findByExactMatch(String chimeString) {
return entityManager
.createNamedQuery("Structure.findByExactMatch", Structure.class)
.setParameter("chime", chimeString)
.getResultList();
}
The Structure entity.
@Entity
@NamedNativeQueries({
@NamedNativeQuery(
name = "Structure.findByExactMatch",
query = "SELECT id, molfile(ctab) ctab FROM structure " +
"WHERE flexmatch(ctab, :chime, 'all')=1",
resultClass = Structure.class) })
public class Structure {
@Id
@Column(name = "ID")
private long id;
@Lob
@Column(name = "CTAB")
private String ctab;
// getter & setter
}
Edit 1 The pl/sql function, as you can see it is overloaded.
FUNCTION flexmatch(
molobj IN BLOB,
querymol IN VARCHAR2,
args IN VARCHAR2)
RETURN NUMBER
FUNCTION Flexmatch(
molobj IN BLOB,
querymol IN CLOB,
args IN VARCHAR2)
RETURN NUMBER
After some days of trying, we gave up to solve it within Hiberante. We ran the query using SpringJDBC, which is also present in the project, and used the ID to populate a Hiberante entity. You could do this with plain old JDBC also.
这篇关于在clob参数的where子句中使用函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!