hibernate映射文件不支持union联合查询

        专门给用户培训的环境后台日志最近报出一个异常，

org.hibernate.exception.SQLGrammarException: could not execute query
	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
	at org.hibernate.loader.Loader.doList(Loader.java:2214)
	at com.cnpc.oms.dao.BaseHibernateDAO.pagedQuery(BaseHibernateDAO.java:280)
……
Caused by: java.sql.SQLException: ORA-00907: 缺失右括号

	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
	at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
	... 38 more

        取出hibernate后台日志打印的sql语句分析，出问题的这段sql语句为：

select *
  from (select this_.SUB_ID as ACID1_1373_0_,
               ......
               this_.OPER_DATE as OPER19_1373_0_,
               (select t.well_name
                  from BASE_WELL_INFO t
                 where t.well_id =
                       ((select t.well_id
                           from SUPER_TABLE_1 t
                          where t.ID = this_.TASK_ID
                            and rownum = 1 this_.union
                           select t.well_id
                                   from SUPER_TABLE_2 t
                                  where t.ID =
                                        this_.TASK_ID
                                    and rownum = 1 this_.union
                                   select t.well_id
                                           from SUPER_TABLE_3 t
                                          where t.ID =
                                                this_.TASK_ID
                                            and rownum = 1 this_.union
                                           select t.well_id
                                                   from SUPER_TABLE_4　t
                                                  where t.ID =
                                                        this_.TASK_ID
                                                    and rownum = 1
                         ))
                   and rownum = 1) as formula1982_0_,
               ......
          from SUB_TABLE this_)
 where rownum <= ?

        Hibernate映射文件中这段sql是这样记录的

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- 
	Mapping file autogenerated by MyEclipse Persistence Tools
-->
<hibernate-mapping>
	<class name="……" table="SUB_TABLE">
		<comment>…… </comment>
		<id name="subId" type="java.lang.String">
			<column name="SUB_ID" length="32" />
			<generator class="uuid.hex" />
		</id>
        ……
		<property name="wellName" type="java.lang.String">
			<formula>
				(select t.well_name
					  from base_well_info t
					 where t.well_id = ((select t.well_id
					                       from SUPER_TABLE_1 t
					                      where t. ID = TASK_ID
					                        and rownum = 1
					                     union
					                     select t.well_id
					                       from SUPER_TABLE_2 t
					                      where t. ID = TASK_ID
					                        and rownum = 1
					                     union
					                     select t.well_id
					                       from SUPER_TABLE_3 t
					                      where t. ID = TASK_ID
					                        and rownum = 1
					                     union
					                     select t.well_id
					                       from SUPER_TABLE_4 t
					                      where t.ID = TASK_ID
					                        and rownum = 1))
					   and rownum = 1
				)
			</formula>
		</property>
		……
	</class>
</hibernate-mapping>

        SUB_TABLE表中的外键TASK_ID字段的数据是来自表SUPER_TABLE_1、SUPER_TABLE_2、SUPER_TABLE_3、SUPER_TABLE_4中的主键ID，表SUPER_TABLE_1、SUPER_TABLE_2、SUPER_TABLE_3、SUPER_TABLE_4中的外键WELL_ID数据来自表BASE_WELL_INFO的主键，主要目的就是通过关联取到表BASE_WELL_INFO中的WELL_NAME井名。

        通过日志可以看出hibernate直接把union关键字解释成了字段this_.union，这样在执行sql查询语句的时候就报出了上面的异常。也不知道当时开发这个模块的人员到底有么有开发完成，并测试通过。

        在网上找了好半天， 也没有找到一个好的处理方法。自己整理了一下处理方式。

        自己建了3张表，模拟项目中的业务表。

        教师和学生都需要考试，考试的结果存放在同一张成绩表中。

--创建教师表
create table TEACHER
(
  ID   VARCHAR2(32) not null,--主键
  NAME VARCHAR2(32) --教师姓名
)
-- Create/Recreate primary, unique and foreign key constraints 
alter table TEACHER
  add constraint TEACHER_PRI_ID primary key (ID)
--创建学生表
create table STUDENT
(
  ID   VARCHAR2(32) not null,--主键
  NAME VARCHAR2(32) --学生姓名
)
-- Create/Recreate primary, unique and foreign key constraints 
alter table STUDENT
  add constraint STUDENT_PRI_ID primary key (ID)
--创建考试成绩表
create table SCORE
(
  ID          VARCHAR2(32) not null,--主键
  PERSON_TYPE VARCHAR2(32), --考试人类别
  SCORE       NUMBER(4,1), --考试分数
  PERSON_ID   VARCHAR2(32) --考试人ID,记录学生表和教师表中的ID
)
-- Create/Recreate primary, unique and foreign key constraints 
alter table SCORE
  add constraint SCORE_PRI_ID primary key (ID)

        jsp页面上要显示成绩表SCORE中的记录，假如显示信息包括考试者姓名和相应的考试成绩，查询成绩数据项好说，直接从成绩表中的SCORE字段取出，但是考试者姓名是存放在不同的表中。成绩表SCORE中的PERSON_ID字段记录的人员ID来自不同的表。

        Hibernate映射配置文件中，如果不支持union联合查询，使用union显然是行不通的，可以通过下面的方式尝试。

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
	<class name="com.test.pojo.Score" table="SCORE">
		<comment>老师、学生的成绩</comment>
		<id name="id" type="java.lang.String">
			<column name="ID" length="32" />
			<generator class="uuid.hex" />
		</id>
		<property name="personType" type="java.lang.String">
			<column name="PERSON_TYPE" length="32">
				<comment>考试人员类别</comment>
			</column>
		</property>
		<property name="personId" type="java.lang.String">
			<column name="PERSON_ID" length="32">
				<comment>考试人员ID</comment>
			</column>
		</property>
		<property name="score" type="java.lang.Double">
			<column name="SCORE" precision="4" scale="1">
				<comment>考试成绩</comment>
			</column>
		</property>
		<!-- 根据成绩表SCORE中的人员主键PERSON_ID，直接分别到TEACHER、STUDENT表中查询相应人员的名字，
				并且存放在不同的映射字段中 -->
		<!-- 老师姓名 -->
		<property name="teaPersonName" type="java.lang.String">
			<formula>
				( SELECT d.NAME
					  FROM TEACHER d
					 WHERE d.ID = PERSON_ID
					   AND ROWNUM = 1
					)
			</formula>
		</property>
		<!-- 学生姓名 -->
		<property name="stuPersonName" type="java.lang.String">
			<formula>
				( SELECT d.NAME
					  FROM STUDENT d
					 WHERE d.ID = PERSON_ID
					   AND ROWNUM = 1
					)
			</formula>
		</property>
	</class>
</hibernate-mapping>

        表SCORE对应的pojo中stuPersonName、teaPersonName两个字段的setter方法需要做一下调整。

import java.io.Serializable;
public class Score implements Serializable{

	private String id;
	private String personType;
	private Double score;
	private String personId;
	private String personName;//存放考试者姓名
	
	private String teaPersonName;//存放教师姓名
	private String stuPersonName;//存放学生姓名
	
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getPersonType() {
		return personType;
	}
	public void setPersonType(String personType) {
		this.personType = personType;
	}
	public Double getScore() {
		return score;
	}
	public void setScore(Double score) {
		this.score = score;
	}
	public String getPersonId() {
		return personId;
	}
	public void setPersonId(String personId) {
		this.personId = personId;
	}
	public String getPersonName() {
		return personName;
	}
	public void setPersonName(String personName) {
		this.personName = personName;
	}
	public String getTeaPersonName() {
		return teaPersonName;
	}
	public void setTeaPersonName(String teaPersonName) {
		if(null != teaPersonName){
			//根据外键PERSON_ID在教师表中查找到对应的姓名，如果查到结果，则直接赋值给personName
			this.personName = teaPersonName;
		}
	}
	public String getStuPersonName() {
		return stuPersonName;
	}
	public void setStuPersonName(String stuPersonName) {
		if(null != stuPersonName){
			//根据外键PERSON_ID在学生表中查找到对应的姓名，如果查到结果，则直接赋值给personName
			this.personName = stuPersonName;
		}
	}
}

        这样在页面直接取出Score.personName的值即可。

        上面记录的是一种处理方式。实际上通过观察发现业务表中还有一个字段存放着和外键相关的信息，就像上面的成绩表SCORE中的PERSON_TYPE字段。如果PERSON_TYPE=’教师’，就表明这一条数据就是教师的考试成绩，可以根据外键PERSON_ID直接到教师表TEACHER中查询姓名。

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
	<class name="com.test.pojo.Score" table="SCORE">
		<comment>老师、学生的成绩</comment>
		<id name="id" type="java.lang.String">
			<column name="ID" length="32" />
			<generator class="uuid.hex" />
		</id>
		<property name="personType" type="java.lang.String">
			<column name="PERSON_TYPE" length="32">
				<comment>考试人员类别</comment>
			</column>
		</property>
		<property name="personId" type="java.lang.String">
			<column name="PERSON_ID" length="32">
				<comment>考试人员ID</comment>
			</column>
		</property>
		<property name="score" type="java.lang.Double">
			<column name="SCORE" precision="4" scale="1">
				<comment>考试成绩</comment>
			</column>
		</property>
		<property name="personName" type="java.lang.String">
			<formula>
				( SELECT (CASE
					         WHEN t.PERSON_TYPE = '教师' THEN
					          (SELECT d.NAME FROM TEACHER d WHERE d.ID = PERSON_ID)
					         WHEN t.PERSON_TYPE = '学生' THEN
					          (SELECT d.NAME FROM STUDENT d WHERE d.ID = PERSON_ID)
					       END) AS PERSONNAME
					  FROM SCORE t
					 WHERE t.PERSON_ID = PERSON_ID
					   AND ROWNUM = 1
					)
			</formula>
		</property>
	</class>
</hibernate-mapping>

import java.io.Serializable;
public class Score implements Serializable{

	private String id;
	private String personType;
	private Double score;
	private String personId;
	private String personName;//存放考试者姓名
	
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getPersonType() {
		return personType;
	}
	public void setPersonType(String personType) {
		this.personType = personType;
	}
	public Double getScore() {
		return score;
	}
	public void setScore(Double score) {
		this.score = score;
	}
	public String getPersonId() {
		return personId;
	}
	public void setPersonId(String personId) {
		this.personId = personId;
	}
	public String getPersonName() {
		return personName;
	}
	public void setPersonName(String personName) {
		this.personName = personName;
	}
}