如何通过HQL查询使用本机sql函数？ [英] How to use native sql function with HQL query?

问题描述

我尝试使用HQL执行类似下面的查询，该查询使用本地SQL函数（ dbms_lob.getlength ）：

  def results = Attachment.executeQuery（
'select id，originalFilename，dbms_lob.getlength（a.fileBytes），dateCreated，createUserName'+ 
'from附件a其中a.id不在'+ 
'（从SpecVersion sv中选择attachmentId，其中sv.attachmentId不为空）'+ 
'和a.dateCreated> sysdate-30'） 
  

• fileBytes列是一个BLOB - 我想要获得大小的BLOB内容。

然而，这会导致下面的错误。

  java.lang.IllegalStateException：节点没有数据类型：org.hibernate.hql.ast.tree.MethodNode 
 +  -  [METHOD_CALL] MethodNode：'（'
 | +  -  [METHOD_NAME] IdentNode：'dbms_lob.getlength'{originalText = dbms_lob.getlength} 
 | \- [EXPR_LIST] SqlNode ：'exprList'
 | \- [DOT] DotNode：'attachment0_.file_bytes'{propertyName = fileBytes，dereferenceType = ALL，propertyPath = fileBytes，path = a.fileBytes，tableAlias = attachment0_，className = com.et .layoutmgr.grails.mapping.Attachment，classAlias = a} 
 | +  -  [ALIAS_REF] IdentNode：'attachment0_.ATTACHMENT_ID'{alias = a，className = com.et.layoutmgr.grails.mapping.Attachment，tableAlias = attachment0_} 
 | \- [IDENT] IdentNode：'fileBytes'{originalText = fileBytes} 
 
 at org.hibernate.hql.ast.tree.SelectClause.initializeExplicitSelectClause（SelectClause.java:156）
 at org.hibernate.hql.ast.HqlSqlWalker.useSelectClause（HqlSqlWalker.java:857）
 at org.hibernate.hql.ast.HqlSqlWalker.processQuery（HqlSqlWalker.java:645）
 org.hibernate。 hql.antlr.HqlSqlBaseWalker.query（HqlSqlBaseWalker.java:685）
处org.hibernate.hql.antlr org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement（HqlSqlBaseWalker.java:301）
。 HQLSqlBaseWalker.statement（HqlSqlBaseWalker.java:244）
 at org.hibernate.hql.ast.QueryTranslatorImpl.analyze（QueryTranslatorImpl.java:256）
 at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile（ QueryTranslatorImpl.java:187）
 at org.hibernate.hql.ast.QueryTranslatorImpl.compile（QueryTranslatorImpl.java:138）
 at org.hibernate.engine.query.HQLQueryPlan。< init&g t;（HQLQueryPlan.java:101）
位于org.hibernate.engine.query.HQLQueryPlan。< init>（HQLQueryPlan.java:80）
位于org.hibernate.engine.query.QueryPlanCache。 getHQLQueryPlan（QueryPlanCache.java:124）
在org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan（AbstractSessionImpl.java:156）
在org.hibernate.impl.AbstractSessionImpl.createQuery（AbstractSessionImpl.java:135）在org.hibernate.impl.SessionImpl.createQuery（SessionImpl.java:1770）
在org.codehaus.groovy.grails.orm.hibernate.metaclass.ExecuteQueryPersistentMethod $ 2.doInHibernate 
（ExecuteQueryPersistentMethod.java:81 ）美元，org.springframework.orm.hibernate3.HibernateTemplate.doExecute（HibernateTemplate.java:406 b $ b）
在org.springframework.orm.hibernate3.HibernateTemplate.executeFind（HibernateTemplate.java:343）
在org.codehaus.groovy.grails.orm.hibernate.metaclass.ExecuteQueryPersistentMethod.doInvokeInternal（ExecuteQueryPersistentMet hod.java:79）
处org.codehaus.groovy.grails.orm org.codehaus.groovy.grails.orm.hibernate.metaclass.AbstractStaticPersistentMethod.invoke（AbstractStaticPersistentMethod.java:72）
。 hibernate.metaclass.AbstractStaticPersistentMethod.invoke（AbstractStaticPersistentMethod.java:65）
  

使用HQL ，还是我需要切换到使用原生SQL 附件域中添加新字段<$ c> $ c> Long fileBytesLength 和内部映射closure添加计算 fileBytes 字段长度的公式。

 类附件{
 
字符串createUserName 
字符串originalFilename 
 
字节[] fileBytes 
长fileBytesLength 
 
日期dateCreated 
 //其他属性
 
 static mapping = {
 //其他映射
 fileLength公式：dbms_lob.getlength（fileBytes）
} 
} 
  

然后将查询修改为：

  def results = Attachment.executeQuery（
'select id，originalFilename，fileBytesLength，dateCreated，createUserName'+ 
'from附件a其中a.id不在'+ 
' （从specVersion sv中选择attachmentId，其中sv.attachmentId不为空）'+ 
'和a.dateCreated> sysdate  -  30'）
  

I am trying to execute a query like the following, using HQL, that uses a native SQL function (dbms_lob.getlength):

def results = Attachment.executeQuery(
    'select id, originalFilename, dbms_lob.getlength(a.fileBytes), dateCreated, createUserName '+
    'from Attachment a where a.id not in '+
         '(select attachmentId from SpecVersion sv where sv.attachmentId is not null) '+
    'and a.dateCreated > sysdate - 30')

• The fileBytes column is a BLOB - I am trying to get the size of the BLOB content.

However, this results in the error below.

java.lang.IllegalStateException: No data type for node: org.hibernate.hql.ast.tree.MethodNode
 +-[METHOD_CALL] MethodNode: '('
 |  +-[METHOD_NAME] IdentNode: 'dbms_lob.getlength' {originalText=dbms_lob.getlength}
 |  \-[EXPR_LIST] SqlNode: 'exprList'
 |     \-[DOT] DotNode: 'attachment0_.file_bytes' {propertyName=fileBytes,dereferenceType=ALL,propertyPath=fileBytes,path=a.fileBytes,tableAlias=attachment0_,className=com.et.layoutmgr.grails.mapping.Attachment,classAlias=a}
 |        +-[ALIAS_REF] IdentNode: 'attachment0_.ATTACHMENT_ID' {alias=a, className=com.et.layoutmgr.grails.mapping.Attachment, tableAlias=attachment0_}
 |        \-[IDENT] IdentNode: 'fileBytes' {originalText=fileBytes}

        at org.hibernate.hql.ast.tree.SelectClause.initializeExplicitSelectClause(SelectClause.java:156)
        at org.hibernate.hql.ast.HqlSqlWalker.useSelectClause(HqlSqlWalker.java:857)
        at org.hibernate.hql.ast.HqlSqlWalker.processQuery(HqlSqlWalker.java:645)
        at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:685)
        at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:301)
        at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:244)
        at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:256)
        at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:187)
        at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:138)
        at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:101)
        at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:80)
        at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:124)
        at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:156)
        at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:135)
        at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1770)
        at org.codehaus.groovy.grails.orm.hibernate.metaclass.ExecuteQueryPersistentMethod$2.doInHibernate(ExecuteQueryPersistentMethod.java:81)
        at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:406)
        at org.springframework.orm.hibernate3.HibernateTemplate.executeFind(HibernateTemplate.java:343)
        at org.codehaus.groovy.grails.orm.hibernate.metaclass.ExecuteQueryPersistentMethod.doInvokeInternal(ExecuteQueryPersistentMethod.java:79)
        at org.codehaus.groovy.grails.orm.hibernate.metaclass.AbstractStaticPersistentMethod.invoke(AbstractStaticPersistentMethod.java:72)
        at org.codehaus.groovy.grails.orm.hibernate.metaclass.AbstractStaticPersistentMethod.invoke(AbstractStaticPersistentMethod.java:65)

Is something like this possible using HQL, or do I need to switch to using native SQL?

解决方案

In Attachment domain add new field Long fileBytesLength and inside mapping closure add the formula for calculating the length of fileBytes field.

class Attachment {

    String createUserName
    String originalFilename

    byte[] fileBytes
    Long fileBytesLength

    Date dateCreated
    //Other Properties

    static mapping = {
        //Other mappings
        fileLength formula: "dbms_lob.getlength(fileBytes)"
    }
}

And then modify the query to:

def results = Attachment.executeQuery(
    'select id, originalFilename, fileBytesLength, dateCreated, createUserName '+
    'from Attachment a where a.id not in '+
         '(select attachmentId from SpecVersion sv where sv.attachmentId is not null) '+
    'and a.dateCreated > sysdate - 30')

这篇关于如何通过HQL查询使用本机sql函数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！