Joined: Mon Nov 05, 2007 12:12 pm
Posts: 5
|
Hibernate version:Core Plugin 3.2.0.beta9
Name and version of the database you are using: PostgreSQL 8.2
The generated SQL (show_sql=true):Code: Hibernate: select this_.REPORT_ID as y0_ from CUSTOMERS this_ left outer join CUSTOMERSTATS customersi2_ on this_.CUSTOMER_ID=customersi2_.STAT_ID
Hibernate: select this_.uuid as y0_, this_.serialNum as y1_, this_.ipAddr as y2_, this_.operStatus as y3_, this_.label as y4_, cust2_.NICKNAME as y5_ from DEVICES this_ inner join REPORTS rep1_ on this_.REPORT_ID=rep1_.REPORT_ID inner join CUSTOMERS cust2_ on rep1_.CUSTOMER_ID=cust2_.CUSTOMER_ID left outer join CUSTOMERSTATS customersi7_ on cust2_.CUSTOMER_ID=customersi7_.STAT_ID inner join RAID_CONTROLLERS rs3_ on this_.DEVICE_ID=rs3_.DEVICE_ID where this_.REPORT_ID in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hi, I'm having trouble with a criteria query that i'm trying to execute. From the output above, you can see that there is an inner join on the "RAID_CONTROLLERS" table. I want this to be an outer join, and all the documentation that I have read indicates that a fetchmode of "join" is an outer join. Here is the code that I am using to create my criteria. Code: final org.hibernate.Criteria gdCrit = dbSession.createCriteria(com.exagrid.triage.dbstor.GridDevice.class);
final org.hibernate.Criteria custCrit = dbSession.createCriteria(com.exagrid.triage.dbstor.CustomerSite.class);
final org.hibernate.criterion.ProjectionList custProjList = org.hibernate.criterion.Projections.projectionList();
custProjList.add(org.hibernate.criterion.Projections.property("latestReport"));
custCrit.setProjection(custProjList);
final org.hibernate.criterion.ProjectionList projList = org.hibernate.criterion.Projections.projectionList();
projList.add(org.hibernate.criterion.Projections.property("uuid"));
projList.add(org.hibernate.criterion.Projections.property("serialNum"));
projList.add(org.hibernate.criterion.Projections.property("ipAddr"));
projList.add(org.hibernate.criterion.Projections.property("operStatus"));
projList.add(org.hibernate.criterion.Projections.property("label"));
gdCrit.createAlias("report", "rep");
gdCrit.createAlias("rep.fromCustomer","cust");
projList.add(org.hibernate.criterion.Projections.property("cust.nickname"),"nickname");
gdCrit.createAlias("raidsets", "rs").setFetchMode("rs",org.hibernate.FetchMode.JOIN);
gdCrit.setProjection(projList);
gdCrit.add(org.hibernate.criterion.Restrictions.in("report",custCrit.list()));
final java.util.List < ? > qList = gdCrit.list(); Here is a bit from my mapping file that defines the set I'm trying to fetch: Code: <set name="raidsets" inverse="true" cascade="all" lazy="true">
<meta attribute="field-description"> The raid controllers in this device </meta>
<key column="DEVICE_ID" not-null="true" />
<one-to-many class="com.exagrid.triage.dbstor.RaidController" />
</set> And finally, here's an SQL statement of exactly what I am looking for for the joining: Code: select devices.label, raid_disks.slot, raid_disks.serialnum
from devices
left outer join raid_controllers on (raid_controllers.device_id = devices.device_id) ,
customers where devices.report_id = customers.report_id order by devices.label, raid_disks.slot;
I've searched the internet, and haven't come up with an answer for the behavior I'm seeing.
Thanks,
Shawn
|
|
Login
FAQ
Search
