hql 一对一多表查询有关问题

hql 一对一多表查询问题
三个实体:

  项目instance, 
  单位(申报项目的主体)Instancecorp,
  个人(申报项目的主体)Instanceperson

  项目instance, 

XML code

<?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.copote.nontaxteam.hibernate.pojo.Instance" table="INSTANCE" schema="XXZWZX">        <id name="instanceid" type="java.lang.String">            <column name="INSTANCEID" length="20" />            <generator class="assigned" />        </id>        <property name="prjid" type="java.lang.String">            <column name="PRJID" length="20" not-null="true" />        </property>        <property name="name" type="java.lang.String">            <column name="NAME" length="500" />        </property>        <property name="declaretype" type="java.lang.String">            <column name="DECLARETYPE" length="4" >                <comment>申报人类型（单位/个个）</comment>            </column>        </property>        <property name="acceptno" type="java.lang.String">            <column name="ACCEPTNO" length="50" >                <comment>受理编号</comment>            </column>        </property>        <!-- 共享主键方式的一对一关联关系(与个人) -->        <one-to-one name="instanceperson"             class="com.copote.nontaxteam.hibernate.pojo.Instanceperson"            cascade="all"            lazy="false"/>                <!-- 共享主键方式的一对一关联关系 (与单位)-->        <one-to-one name="instancecorp"             class="com.copote.nontaxteam.hibernate.pojo.Instancecorp"            cascade="all"            lazy="false"/>           </class></hibernate-mapping>

  单位(申报项目的主体)Instancecorp,

XML code

<?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.copote.nontaxteam.hibernate.pojo.Instancecorp" table="INSTANCECORP" schema="XXZWZX">        <id name="instanceid" type="java.lang.String">            <column name="INSTANCEID" length="20" />            <generator class="foreign">                <param name="property">instance</param>            </generator>        </id>        <!-- 共享主键方式的一对一关联关系 -->        <one-to-one name="instance"             class="com.copote.nontaxteam.hibernate.pojo.Instance"            constrained="true"/>        <property name="corpname" type="java.lang.String">            <column name="CORPNAME" length="100" not-null="true" />        </property>        <property name="corporatorcertno" type="java.lang.String">            <column name="CORPORATORCERTNO" length="30" >                <comment>证件号码</comment>            </column>        </property>    </class></hibernate-mapping> 
 

  个人(申报项目的主体)Instanceperson

XML code

<?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.copote.nontaxteam.hibernate.pojo.Instanceperson" table="INSTANCEPERSON" schema="XXZWZX">        <id name="instanceid" type="java.lang.String">            <column name="INSTANCEID" length="20" />            <generator class="foreign">                <param name="property">instance</param>            </generator>        </id>        <!-- 共享主键方式的一对一关联关系 -->        <one-to-one name="instance"             class="com.copote.nontaxteam.hibernate.pojo.Instance"            constrained="true"/>                <property name="personname" type="java.lang.String">            <column name="PERSONNAME" length="50" not-null="true" />        </property>        <property name="personcertno" type="java.lang.String">            <column name="PERSONCERTNO" length="50" >                <comment>证件号码</comment>            </column>        </property>    </class></hibernate-mapping>

Instance.java 项目

Java code

public class Instance implements java.io.Serializable {    private static final long serialVersionUID = -6502324611897082164L;    private String instanceid;    private Instanceperson instanceperson;//个人    private Instancecorp instancecorp;//单位//注:个人与单位同时个有其中一个不为空,其中一个为空    .......}

Instancecorp.java 单位

Java code

public class Instancecorp  implements java.io.Serializable {    // Fields    private String instanceid;    private Instance instance;    private String corpname;    private String corporatorcertno;//单位责任人证件号码    .....}

Instanceperson.java 个人

Java code

public class Instanceperson  implements java.io.Serializable {    // Fields    private String instanceid;    private Instance instance;    private String personname;    private String personcertno;//个人证件号码}

通过证件号码查询项目信息,下面是我写的HQL

Java code

hql="select count(*) from Instance as a where a.instancecorp.corporatorcertno='"+certno+"' " +                    "or a.instanceperson.personcertno='"+certno+"' order by a.instanceid desc ";

执行时后台打印的sql:

SQL code

Hibernate:     select        count(*) as col_0_0_     from        XXZWZX.INSTANCE instance0_,        XXZWZX.INSTANCECORP instanceco1_,        XXZWZX.INSTANCEPERSON instancepe2_     where        instance0_.INSTANCEID=instanceco1_.INSTANCEID         and instance0_.INSTANCEID=instancepe2_.INSTANCEID         and (            instanceco1_.CORPORATORCERTNO='123'             or instancepe2_.PERSONCERTNO='123'        )     order by        instance0_.INSTANCEID desc 
 

仔细看sql会发现永远都查不出任何数据.
显然不符合我的目标定位. 因为项目的申报主体要么是单位,要么是个人不可能两者都是
现在是问题是怎么修改? (前提:依然使用hql)

[解决办法]

SQL code

select a.*  from Instance a  left outer join (select instanceid, corporatorcertno certno                     from instancecorp t1                   union all                   select instanceid, personcertno certno                     from instanceperson t2) b    on b.instanceid = a.instanceid where b.certno = 'XXX' order by a.instanceid desc
[解决办法]
使用下面的方法将查询结果映射到Hibernate对象：
Java code
    /** 分页查询指定类的满足条件的持久化对象 */    public List sqlquery(Class clazz, String sql, int pageNo, int pageSize) {        //System.out.println("hql="+hql+",pageNo="+pageNo+",pageSize="+pageSize);        final int pNo = pageNo;        final int pSize = pageSize;        final String sql1 = sql;        List list = this.getSession().createSQLQuery(sql)                .addEntity(clazz)                .setFirstResult((pageNo-1)*pSize)                .setMaxResults(pageSize)                .list();//        List list = sqlQueryfindByPage(sql1, (pNo-1)*pSize, pSize);                return list;    } 

 

查看更多 下一篇