81,091
社区成员
发帖
与我相关
我的任务
分享
<?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>
<?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>
<?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>
public class Instance implements java.io.Serializable {
private static final long serialVersionUID = -6502324611897082164L;
private String instanceid;
private Instanceperson instanceperson;//个人
private Instancecorp instancecorp;//单位
//注:个人与单位同时个有其中一个不为空,其中一个为空
.......
}
public class Instancecorp implements java.io.Serializable {
// Fields
private String instanceid;
private Instance instance;
private String corpname;
private String corporatorcertno;//单位责任人证件号码
.....
}
public class Instanceperson implements java.io.Serializable {
// Fields
private String instanceid;
private Instance instance;
private String personname;
private String personcertno;//个人证件号码
}
hql="select count(*) from Instance as a where a.instancecorp.corporatorcertno='"+certno+"' " +
"or a.instanceperson.personcertno='"+certno+"' order by a.instanceid desc ";
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
/** 分页查询指定类的满足条件的持久化对象 */
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;
}
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