mysql - Hibernate 查询语法错误

Question

这可能是个基本问题，
我正在尝试使用 Hibernate 执行插入选择查询，

insert into A(col1, col2) select xxx, yyy from (
    select b.col1 as xxx, c.col1 as yyy from B b,C c where someCondition1
      UNION
    select b.col1 as xxx, c.col1 as yyy from B b,C c where someCondition2
) as Z

Hibernate 正在给予

syntax error: Unexpected Token ( at <br/>
xxx, yyy from (...

虽然相同的查询在作为 SQL 执行时有效。

完整的堆栈跟踪是

org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ( near line 1, column 69 [Its whole query here, pasting from above question: insert into A(col1, col2) select xxx, yyy from (
    select b.col1 as xxx, c.col1 as yyy from B b,C c where someCondition1
      UNION
    select b.col1 as xxx, c.col1 as yyy from B b,C c where someCondition2
)]
    at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:54)
    at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:47)
    at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:82)
    at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:281)
    at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:180)
    at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:134)
    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:94)
    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:1650)
    at table.handler.HibernateHandler.executeUpdate(HibernateHandler.java:404)
.
.
.
.
.
    at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:40)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:116)
    at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:124)
    at com.modelling.models.ProjectDetailController$CopyProjectRunner.run(ProjectDetailController.groovy:1170)
    at java.lang.Thread.run(Thread.java:619)

这里是不是少了什么？

Answer 1

为内部查询提供别名。

试试这个:

INSERT INTO A(col1, col2) 
SELECT xxx, yyy 
FROM (
    SELECT b.col1 AS xxx, c.col1 AS yyy FROM B b,C c WHERE someCondition1
      UNION
    SELECT b.col1 AS xxx, c.col1 AS yyy FROM B b,C c WHERE someCondition2
) AS A

编辑::

INSERT INTO A (col1, col2) 
SELECT b.col1 AS xxx, c.col1 AS yyy FROM B b,C c WHERE someCondition1
UNION
SELECT b.col1 AS xxx, c.col1 AS yyy FROM B b,C c WHERE someCondition2