从SQL转换为HQL会给出错误Grails [英] convert from SQL to HQL gives error Grails
本文介绍了从SQL转换为HQL会给出错误Grails的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我试图将此sql转换为hql以从工件表中获取数据。我有这个SQL查询，它在数据库上工作正常
从分类中选择a。*
c
（c.node_id = h.id）
加入工件a on（a.id = c.artifact_id ）
其中a.DOCUMENT_ID = 10462 AND c.active = 1
我试图将其转换为HQL
artifacts = Artifact.executeQuery（FROM classification c+
JOIN（SELECT id FROM taxonomy_node START WITH id =：nodeId+
CONNECT BY NOCYCLE PRIOR ID = parent_id）h+
ON（c.node_id = h.id）+
JOIN神器a ON（a.id = c.artifactId）+
WHERE a.DOCUMENT_ID =：docid AND c.active = 1，
[nodeId：NodeId，docid：document.id]，
[max：limit，offset：startIndex]）
域类看起来像
分类
class Classification {
public static final String USER_DEFAULT =USER
public static final String USER_SYSTEM =SYSTEM
TaxonomyNode节点
字符串artifactId // TODO can we用一个带注释的对象驱动这个东西？
布尔活动
字符串createdBy
字符串updatedBy
日期dateCreated
日期lastUpdated
//或者我们应该创建一个Artifact Toolkit插件吗？
static constraints = {
node nullable：false，blank：false
artifactId nullable：false，blank：false，unique：['node']
主动可空：false，空白：false
createdBy可为空：false，空白：false
updatedBy可空：false，空白：false
}
静态映射= {
id generator：'sequence'，params：[sequence：'classification_seq']
artifactId index：'classify_by_artifact_node'
节点索引：'classify_by_artifact_node'
active defaultValue：1
}
}
工件看起来像
class Artifact {
public enum ArtifactType {
CLAUSE（Clause），
TITLE（Title），
DEFINED_TERMS（Defined Terms）
private final字符串值
ArtifactType（字符串值）{this.value = value}
String toString（）{value}
String getKey（）{name（）}
}
public enum ArtifactStatus {
ALL（All），
REVIEWED（评论），
FOR_REVIEW（评论），
UNCLASSIFIED（未分类）
私人最终字符串值
ArtifactStatus（String value）{this.value = value}
String toString（）{value}
String getKey（）{name（）}
}
文档文档
字符串artifactType
字符串文本
字符串状态
字符串createdBy
字符串updatedBy
日期dateCreated
日期lastUpdated
static belongsTo = Document
static hasMany = [分类：分类]
静态约束= {
artifactType maxSize：50，可为空，false，空：false
文本可为空：false，空白：false，maxSize：4000
状态可空：true，blan k：true
createdBy可为空：false，空白：false
updatedBy可空：false，空白：false
}
静态映射= {
id生成器：'sequence'，params：[sequence：'artifact_seq']
文档索引：'artifact_by_doc'
artifactType索引：'artifact_by_doc，artifact_by_type'
文本索引：'artifact_by_doc，artifact_by_text'
分类节点看起来像
class TaxonomyNode {
public static final String USER_DEFAULT =USER
public static final字符串USER_SYSTEM =SYSTEM
字符串hierarchyId
字符串nodeId
字符串标签
字符串inputFormat
布尔活动
字符串createdBy
字符串updatedBy
日期dateCreated
lastUpdated日期
static hasMany = [元数据：TaxonomyNodeMetadata]
TaxonomyNode parent
$ b static constraints = {
inputFormat nullable：true，blank：true // TODO现在我们希望允许用户输入节点为空/空
父可空： true // TODO我们应该强制每个hierarchyId只有一个节点可以有一个空父母
hierarchyId nullable：false，blank：false // TODO我们应该强制父母的hierarchyId是相同的
活动可空：false ，空白：false
nodeId可为空：false，空白：false，唯一：['hierarchyId']
标签可空：true，空白：真
createdBy可空：false，空：false
updatedBy nullable：false，blank：false
}
static mapping = {
id generator：'sequence'，params：[sequence：'taxonomy_node_seq']
hierarchyId索引：'taxonomy_by_hier'
标签索引：'taxonomy_by_hier，taxonomy_by_label'
父索引：'taxonomy_by_hier，taxonomy_by_par ent'
active defaultValue：1
}
}
我得到这个错误
错误|
2014-10-22 09：22：38,633 [http-bio-8080-exec-4]错误ast.ErrorCounter - 行1：28：意外标记：（
错误|
2014 -10-22 09：22：38,634 [http-bio-8080-exec-4]错误ast.ErrorCounter - 行1:28：意外标记：（
消息：意外标记：（
行|方法
- >> 281 | $ tt__index in com.ald.aeandsdx.ArtifactController
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| 198 | doFilter in grails.plugin.cache.web.filter.PageFragmentCachingFilter
| 63 | doFilter。在grails.plugin.cache.web中。 filter.AbstractFilter
| 1145 | java.util.concurrent.ThreadPoolExecutor中的runWorker
| 615 |在java.util.concurrent.ThreadPoolExecutor中运行... $ Worker
^ 745 |在java中运行.lang.Thread
错误|
org.springframework.orm.hibernate4.HibernateQueryException：意外标记:(在第1行第28列[FROM分类c JOIN（SELECT id FROM taxonomy_node START WITH id =：nodeId CO NNON BY NOCYCLE PRIOR ID = parent_id）hON（c.node_id = h.id）JOIN工件a ON（a.id = c.artifactId）WHERE a.DOCUMENT_ID =：docid AND c.active = 1];嵌套异常是org.hibernate.hql.internal.ast.QuerySyntaxException：意外标记：（在第1行第28列[FROM分类c JOIN（SELECT id FROM taxonomy_node START WITH id =：nodeId CONNECT BY NOCYCLE PRIOR id = parent_id）hON （c.node_id = h.id）JOIN工件a ON（a.id = c.artifactId）WHERE a.DOCUMENT_ID =：docid AND c.active = 1]
解决方案
org / hibernate / orm / 3.3 / reference / en / html / queryhql.html＃queryhql-subqueriesrel =nofollow> http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/ queryhql.html＃queryhql-subqueries
I am trying to convert this sql to hql to fetch the data from artifact table. I have this sql query which works fine on the database
select a.*
from classification c
join (select id from taxonomy_node start with id = 5067 connect by nocycle prior id = parent_id) h
on (c.node_id = h.id)
join artifact a on (a.id = c.artifact_id)
Where a.DOCUMENT_ID = 10462 AND c.active=1
I am trying to convert it to HQL
artifacts = Artifact.executeQuery("FROM classification c "+
"JOIN (SELECT id FROM taxonomy_node START WITH id = :nodeId "+
"CONNECT BY NOCYCLE PRIOR id = parent_id) h "+
"ON (c.node_id = h.id) "+
"JOIN artifact a ON (a.id = c.artifactId) "+
"WHERE a.DOCUMENT_ID = :docid AND c.active=1",
[nodeId: NodeId ,docid: document.id],
[max:limit, offset:startIndex])
The domain classes look like Classification
class Classification {
public static final String USER_DEFAULT = "USER"
public static final String USER_SYSTEM = "SYSTEM"
TaxonomyNode node
String artifactId //TODO can we drive this with an annotated object or something?
Boolean active
String createdBy
String updatedBy
Date dateCreated
Date lastUpdated
// or should we create an Artifact Toolkit plugin?
static constraints = {
node nullable:false, blank:false
artifactId nullable:false, blank:false, unique: ['node']
active nullable: false, blank: false
createdBy nullable:false, blank:false
updatedBy nullable:false, blank:false
}
static mapping = {
id generator:'sequence', params:[sequence:'classification_seq']
artifactId index: 'classify_by_artifact_node'
node index: 'classify_by_artifact_node'
active defaultValue: "1"
}
}
Artifact looks like
class Artifact {
public enum ArtifactType {
CLAUSE("Clause"),
TITLE("Title"),
DEFINED_TERMS("Defined Terms")
private final String value
ArtifactType(String value) { this.value = value }
String toString() { value }
String getKey() { name() }
}
public enum ArtifactStatus {
ALL("All"),
REVIEWED("Reviewed"),
FOR_REVIEW("For Review"),
UNCLASSIFIED("Unclassified")
private final String value
ArtifactStatus(String value) { this.value = value }
String toString() { value }
String getKey() { name() }
}
Document document
String artifactType
String text
String status
String createdBy
String updatedBy
Date dateCreated
Date lastUpdated
static belongsTo = Document
static hasMany = [classification: Classification]
static constraints = {
artifactType maxSize: 50, nullable: false, blank: false
text nullable: false, blank: false, maxSize: 4000
status nullable:true, blank:true
createdBy nullable:false, blank:false
updatedBy nullable:false, blank:false
}
static mapping = {
id generator:'sequence', params:[sequence:'artifact_seq']
document index: 'artifact_by_doc'
artifactType index: 'artifact_by_doc,artifact_by_type'
text index: 'artifact_by_doc,artifact_by_text'
}
}
the taxonomy Node looks like
class TaxonomyNode {
public static final String USER_DEFAULT = "USER"
public static final String USER_SYSTEM = "SYSTEM"
String hierarchyId
String nodeId
String label
String inputFormat
Boolean active
String createdBy
String updatedBy
Date dateCreated
Date lastUpdated
static hasMany = [metadata: TaxonomyNodeMetadata]
TaxonomyNode parent
static constraints = {
inputFormat nullable:true, blank:true //TODO for now we want to allow null/blank for user-entered nodes
parent nullable:true //TODO we should enforce that only one node per hierarchyId can have a null parent
hierarchyId nullable:false, blank:false //TODO we should enforce the parent's hierarchyId is the same
active nullable: false, blank: false
nodeId nullable:false, blank:false, unique: ['hierarchyId']
label nullable:true, blank:true
createdBy nullable:false, blank:false
updatedBy nullable:false, blank:false
}
static mapping = {
id generator:'sequence', params:[sequence:'taxonomy_node_seq']
hierarchyId index: 'taxonomy_by_hier'
label index: 'taxonomy_by_hier,taxonomy_by_label'
parent index: 'taxonomy_by_hier,taxonomy_by_parent'
active defaultValue: "1"
}
}
I am getting this error
Error |
2014-10-22 09:22:38,633 [http-bio-8080-exec-4] ERROR ast.ErrorCounter - line 1:28: unexpected token: (
Error |
2014-10-22 09:22:38,634 [http-bio-8080-exec-4] ERROR ast.ErrorCounter - line 1:28: unexpected token: (
Message: unexpected token: (
Line | Method
->> 281 | $tt__index in com.ald.aeandsdx.ArtifactController
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| 198 | doFilter in grails.plugin.cache.web.filter.PageFragmentCachingFilter
| 63 | doFilter . in grails.plugin.cache.web.filter.AbstractFilter
| 1145 | runWorker in java.util.concurrent.ThreadPoolExecutor
| 615 | run . . . in java.util.concurrent.ThreadPoolExecutor$Worker
^ 745 | run in java.lang.Thread
Error |
org.springframework.orm.hibernate4.HibernateQueryException: unexpected token: ( near line 1, column 28 [FROM classification c JOIN (SELECT id FROM taxonomy_node START WITH id = :nodeId CONNECT BY NOCYCLE PRIOR id = parent_id) hON (c.node_id = h.id) JOIN artifact a ON (a.id = c.artifactId) WHERE a.DOCUMENT_ID = :docid AND c.active=1]; nested exception is org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: ( near line 1, column 28 [FROM classification c JOIN (SELECT id FROM taxonomy_node START WITH id = :nodeId CONNECT BY NOCYCLE PRIOR id = parent_id) hON (c.node_id = h.id) JOIN artifact a ON (a.id = c.artifactId) WHERE a.DOCUMENT_ID = :docid AND c.active=1]
解决方案
HQL supports subqueries only in SELECT and WHERE clauses, see: http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/queryhql.html#queryhql-subqueries
这篇关于从SQL转换为HQL会给出错误Grails的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
