Hibernate映射文件不支持union聯合查詢

專門給用戶培訓的環境後台日志最近報出一個異常，

1. org.hibernate.exception.SQLGrammarException: could not execute query
2. at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
3. at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
4. at org.hibernate.loader.Loader.doList(Loader.java:2214)
5. at com.cnpc.oms.dao.BaseHibernateDAO.pagedQuery(BaseHibernateDAO.java:280)
6. ……
7. Caused by: java.sql.SQLException: ORA-00907: 缺失右括號
9. at Oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
10. at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
11. at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
12. at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
13. ... 38 more

取出hibernate後台日志打印的sql語句分析，出問題的這段sql語句為：

1. select *
2. from (select this_.SUB_ID as ACID1_1373_0_,
3. ......
4. this_.OPER_DATE as OPER19_1373_0_,
5. (select t.well_name
6. from BASE_WELL_INFO t
7. where t.well_id =
8. ((select t.well_id
9. from SUPER_TABLE_1 t
10. where t.ID = this_.TASK_ID
11. and rownum = 1 this_.union
12. select t.well_id
13. from SUPER_TABLE_2 t
14. where t.ID =
15. this_.TASK_ID
16. and rownum = 1 this_.union
17. select t.well_id
18. from SUPER_TABLE_3 t
19. where t.ID =
20. this_.TASK_ID
21. and rownum = 1 this_.union
22. select t.well_id
23. from SUPER_TABLE_4　t
24. where t.ID =
25. this_.TASK_ID
26. and rownum = 1
27. ))
28. and rownum = 1) as formula1982_0_,
29. ......
30. from SUB_TABLE this_)
31. where rownum <= ?

Hibernate映射文件中這段sql是這樣記錄的

1. <?xml version="1.0" encoding="utf-8"?>
2. <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
3. "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
4. <!--
5. Mapping file autogenerated by MyEclipse Persistence Tools
6. -->
7. <hibernate-mapping>
8. <class name="……" table="SUB_TABLE">
9. <comment>…… </comment>
10. <id name="subId" type="java.lang.String">
11. <column name="SUB_ID" length="32" />
12. <generator class="uuid.hex" />
13. </id>
14. ……
15. <property name="wellName" type="java.lang.String">
16. <formula>
17. (select t.well_name
18. from base_well_info t
19. where t.well_id = ((select t.well_id
20. from SUPER_TABLE_1 t
21. where t. ID = TASK_ID
22. and rownum = 1
23. union
24. select t.well_id
25. from SUPER_TABLE_2 t
26. where t. ID = TASK_ID
27. and rownum = 1
28. union
29. select t.well_id
30. from SUPER_TABLE_3 t
31. where t. ID = TASK_ID
32. and rownum = 1
33. union
34. select t.well_id
35. from SUPER_TABLE_4 t
36. where t.ID = TASK_ID
37. and rownum = 1))
38. and rownum = 1
39. )
40. </formula>
41. </property>
42. ……
43. </class>
44. </hibernate-mapping>