原文地址: http://blog.csdn.net/jxc/archive/2006/12/29/1467550.aspx
想在hibernate里实现如下SQL:
select
*
from t_join_a a
left outer join t_join_a_b c on a.a_id = c.a_id and c.b_id = ' b2 '
where a.a_name is not null
from t_join_a a
left outer join t_join_a_b c on a.a_id = c.a_id and c.b_id = ' b2 '
where a.a_name is not null
需要解决2个问题: 外关联和外关联中的条件, 希望下面的内容能够节省大家学习的时间
需要做下面的工作:
public
class
T_join_a
...
{
private String a_id;
private String a_name;
private Set t_join_b;
private Set t_join_a_b;
// 省略 getter/setter
}
public class T_join_a_b implements Serializable ... {
private static final long serialVersionUID = 8337568440339952023L;
private String a_id;
private String b_id;
private String t_join_a;
private String t_join_b;
// 省略 getter/setter
}
public class T_join_b ... {
private String b_id;
private String b_name;
private Set t_join_a;
// 省略 getter/setter
}
private String a_id;
private String a_name;
private Set t_join_b;
private Set t_join_a_b;
// 省略 getter/setter
}
public class T_join_a_b implements Serializable ... {
private static final long serialVersionUID = 8337568440339952023L;
private String a_id;
private String b_id;
private String t_join_a;
private String t_join_b;
// 省略 getter/setter
}
public class T_join_b ... {
private String b_id;
private String b_name;
private Set t_join_a;
// 省略 getter/setter
}
HBM.XML配置如下:
<?
xml version="1.0"
?>
<! DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
< hibernate-mapping package ="demo.hibernate.join" >
< class name ="T_join_a" table ="t_join_a" >
< id name ="a_id" column ="a_id" type ="string" >
< generator class ="org.hibernate.id.UUIDHexGenerator" />
</ id >
< property name ="a_name" column ="a_name" type ="string" />
<!-- 通过 1对多 关联 t_join_a_b -->
< set name ="t_join_a_b" table ="t_join_a_b" lazy ="true" inverse ="true"
cascade ="all" >
< key column ="a_id" />
< one-to-many class ="T_join_a_b" />
</ set >
<!-- 通过多对多关联 t_join_b -->
< set name ="t_join_b" table ="t_join_a_b" lazy ="true" inverse ="false"
cascade ="save-update" >
< key >
< column name ="a_id" />
</ key >
< many-to-many class ="T_join_b" column ="b_id"
outer-join ="auto" />
</ set >
</ class >
</ hibernate-mapping >
<! DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
< hibernate-mapping package ="demo.hibernate.join" >
< class name ="T_join_a" table ="t_join_a" >
< id name ="a_id" column ="a_id" type ="string" >
< generator class ="org.hibernate.id.UUIDHexGenerator" />
</ id >
< property name ="a_name" column ="a_name" type ="string" />
<!-- 通过 1对多 关联 t_join_a_b -->
< set name ="t_join_a_b" table ="t_join_a_b" lazy ="true" inverse ="true"
cascade ="all" >
< key column ="a_id" />
< one-to-many class ="T_join_a_b" />
</ set >
<!-- 通过多对多关联 t_join_b -->
< set name ="t_join_b" table ="t_join_a_b" lazy ="true" inverse ="false"
cascade ="save-update" >
< key >
< column name ="a_id" />
</ key >
< many-to-many class ="T_join_b" column ="b_id"
outer-join ="auto" />
</ set >
</ class >
</ hibernate-mapping >
<?
xml version="1.0"
?>
<! DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
< hibernate-mapping package ="demo.hibernate.join" >
< class name ="T_join_a_b" table ="t_join_a_b" >
< composite-id >
< key-property name ="a_id" column ="a_id" type ="string" />
< key-property name ="b_id" column ="b_id" type ="string" />
</ composite-id >
< many-to-one name ="t_join_a" class ="T_join_a" cascade ="none"
outer-join ="auto" update ="false" insert ="false" access ="property"
column ="a_id" not-null ="true" />
</ class >
</ hibernate-mapping >
<! DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
< hibernate-mapping package ="demo.hibernate.join" >
< class name ="T_join_a_b" table ="t_join_a_b" >
< composite-id >
< key-property name ="a_id" column ="a_id" type ="string" />
< key-property name ="b_id" column ="b_id" type ="string" />
</ composite-id >
< many-to-one name ="t_join_a" class ="T_join_a" cascade ="none"
outer-join ="auto" update ="false" insert ="false" access ="property"
column ="a_id" not-null ="true" />
</ class >
</ hibernate-mapping >
<?
xml version="1.0"
?>
<! DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
< hibernate-mapping package ="demo.hibernate.join" >
< class name ="T_join_b" table ="t_join_b" >
< id name ="b_id" column ="b_id" type ="string" >
< generator class ="org.hibernate.id.UUIDHexGenerator" />
</ id >
< property name ="b_name" column ="b_name" type ="string" />
< set name ="t_join_a" table ="t_join_a_b" lazy ="true" inverse ="false"
cascade ="save-update" >
< key >
< column name ="b_id" />
</ key >
< many-to-many class ="T_join_a" column ="a_id"
outer-join ="auto" />
</ set >
</ class >
</ hibernate-mapping >
<! DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd" >
< hibernate-mapping package ="demo.hibernate.join" >
< class name ="T_join_b" table ="t_join_b" >
< id name ="b_id" column ="b_id" type ="string" >
< generator class ="org.hibernate.id.UUIDHexGenerator" />
</ id >
< property name ="b_name" column ="b_name" type ="string" />
< set name ="t_join_a" table ="t_join_a_b" lazy ="true" inverse ="false"
cascade ="save-update" >
< key >
< column name ="b_id" />
</ key >
< many-to-many class ="T_join_a" column ="a_id"
outer-join ="auto" />
</ set >
</ class >
</ hibernate-mapping >
最后HQL语法如下, hibernate里外关联不能写 on , 其中外关联里的条件是通过 hibernate 的 with 关键字实现的 :
select
new Map(a.a_id
as
a_id,a.a_name
as
a_name, c.b_id
as
b_id)
from T_join_a a left outer join a.t_join_a_b c
with c.b_id = ' b2 '
from T_join_a a left outer join a.t_join_a_b c
with c.b_id = ' b2 '
主要的java代码如下:
String hql;
Query query;
Iterator it;
hql = " select new Map(a.a_id as a_id,a.a_name as a_name, c.b_id as b_id) "
+ " from T_join_a a " + " left outer join a.t_join_a_b c "
+ " with c.b_id = 'b2' " ;
query = session.createQuery(hql);
it = query.list().iterator();
while (it.hasNext()) ... {
Map map = (Map) it.next();
System.out.print("a_id : " + map.get("a_id"));
System.out.println(" b_id : " + map.get("b_id"));
}
Query query;
Iterator it;
hql = " select new Map(a.a_id as a_id,a.a_name as a_name, c.b_id as b_id) "
+ " from T_join_a a " + " left outer join a.t_join_a_b c "
+ " with c.b_id = 'b2' " ;
query = session.createQuery(hql);
it = query.list().iterator();
while (it.hasNext()) ... {
Map map = (Map) it.next();
System.out.print("a_id : " + map.get("a_id"));
System.out.println(" b_id : " + map.get("b_id"));
}