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

灰太狼 2022-05-19 02:17 258阅读 0赞

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

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">
    
    <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>

![1351149898_7341.jpg][]

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>
    		
    		
    		<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;
    	}
    }

[1351149898_7341.jpg]: https://img-my.csdn.net/uploads/201210/25/1351149898_7341.jpg