One way to solve your problem would be to map the sub-select as an entity. Take this example of widgets which have an associated set of gadgets:
SQL:
Code:
-- drop the tables first
drop table gadgets;
drop table widgets;
drop table widget_gadget;
-- create the tables
create table widgets (
widget_id identity
,widget_desc varchar(30)
,primary key(widget_id));
create table gadgets (
gadget_id identity
,widget_id integer
,gadget_desc varchar(30)
,gadget_price double
,foreign key (widget_id) references widgets (widget_id)
,primary key(gadget_id));
insert into widgets values (null, 'widget1');
* wid_id ~
call identity();
insert into gadgets values (null, *{wid_id}, 'gadget1', 39.50);
insert into gadgets values (null, *{wid_id}, 'gadget2', 53.45);
insert into widgets values (null, 'widget2');
* wid_id ~
call identity();
insert into gadgets values (null, *{wid_id}, 'gadget3', 137.50);
insert into gadgets values (null, *{wid_id}, 'gadget4', 60);
commit;
Here is a sql query that retrieves data much as yours does:
Code:
select widget_id
,(select sum(gadget_price)
from gadgets
where gadgets.widget_id = widgets.widget_id) as _sqry1
,widget_desc
from widgets
order by _sqry1;
and here are the results of that query:
Code:
WIDGET_ID _SQRY1 WIDGET_DESC
--------- ------ -----------
0 92.95 widget1
1 197.5 widget2
If your mapping docs look like this:
Code:
<class name="Widget" table="WIDGETS">
<id name="widgetId" column="WIDGET_ID">
<generator class="native" />
</id>
<property name="widgetDesc" column="WIDGET_DESC" />
<set name="gadgets" lazy="false" cascade="all">
<key column="WIDGET_ID" />
<one-to-many class="Gadget" />
</set>
</class>
<class name="Gadget" table="GADGETS">
<id name="gadgetId" column="GADGET_ID">
<generator class="native" />
</id>
<property name="widgetId" column="WIDGET_ID" />
<property name="gadgetDesc" column="GADGET_DESC" />
<property name="gadgetPrice" column="GADGET_PRICE" />
</class>
Then you can do code like this:
Code:
lgr.info("Let's list Widgets");
List widgets = widgetDao.getAllWidgets();
it = widgets.iterator();
while (it.hasNext()) {
Widget widget = (Widget) it.next();
lgr.info("Widget is: " + widget);
Double sum = 0.0;
Iterator gadIt = widget.getGadgets().iterator();
while (gadIt.hasNext()) {
Gadget gadget = (Gadget) gadIt.next();
sum += gadget.getGadgetPrice();
lgr.info("\tAssociated Gadget: " + gadget);
}
lgr.info("\tTotal price: " + sum);
}
and you will get output like this:
Code:
INFO Main - Widget is: Widget[0,widget1]
INFO Main - Associated Gadget: Gadget[1,gadget2]
INFO Main - Associated Gadget: Gadget[0,gadget1]
INFO Main - Total price: 92.95
INFO Main - Widget is: Widget[1,widget2]
INFO Main - Associated Gadget: Gadget[3,gadget4]
INFO Main - Associated Gadget: Gadget[2,gadget3]
INFO Main - Total price: 197.5
If, however, you need to use SQLQueries, you can do this:
Code:
public List testQuery() {
Session session = getHibernateTemplate().getSessionFactory().openSession();
SQLQuery query = session.createSQLQuery(
"select widget_id " +
",(select sum(gadget_price) " +
"from gadgets " +
"where gadgets.widget_id = widgets.widget_id) as _sqry1 " +
",widget_desc " +
"from widgets " +
"order by _sqry1");
query.addScalar("widget_id", Hibernate.INTEGER);
query.addScalar("_sqry1", Hibernate.DOUBLE);
query.addScalar("widget_desc", Hibernate.STRING);
return query.list();
}
and then use the above method like this:
Code:
lgr.info("Now test query: ");
List objects = widgetDao.testQuery();
it = objects.iterator();
while (it.hasNext()) {
Object [] elem = (Object []) it.next();
lgr.info("widget_id: " + (Integer) elem[0]);
lgr.info("\tSum is: " + (Double) elem[1]);
lgr.info("\tDescription is: " + (String) elem[2]);
}
and get output like this:
Code:
INFO Main - Now test query:
INFO Main - widget_id: 0
INFO Main - Sum is: 92.95
INFO Main - Description is: widget1
INFO Main - widget_id: 1
INFO Main - Sum is: 197.5
INFO Main - Description is: widget2
Login
FAQ
Search
