File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Object Relational Mapping and the fly likes Replacing resultset values Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Replacing resultset values" Watch "Replacing resultset values" New topic
Author

Replacing resultset values

Eric Bresie
Greenhorn

Joined: Apr 05, 2011
Posts: 20
I have an event entity with a composite event id. I have a basic named query like the following which works as expected.



The (:param is null or evt.parm = :param) in the where clause expression "short circuits" which allows me to use one query for multiple cases (with different parameters set to null).

I have facade code like:





Which would return results something like:

//uid, pos, sn, date, rating
1,1,1,"01-03-2010", 1
2,1,1,"01-03-2010", 2
3,1,1,"01-04-2010", 3
4,1,1,"01-05-2010", 4
5,1,1,"01-06-2010", 3

Now I would like a similar query that return a List<Event> with aggregate Sum values instead of the individual rating, to get something like:

//pos, sn, rating
1,1,3
1,2,10

I think I may need to use something like

select evt.sn, evt.pos, sum(evt.rating) as rating from Event evt where evt.sn in ( 1,2) and date >= DATE '01-01-2010' and date <= DATE '02-01-2010' Group by sn, pos;

or

select NEW Event( evt.eventId, evt.uid, evt.sn, evt.pos, sum(evt.rating) as rating) from Event evt where evt.sn in ( 1,2) and date >= DATE '01-01-2010' and date <= DATE '02-01-2010' Group by sn, pos;

Some of my confusion on this may be due to an entity design issue, but not sure. Is this good way to do this or is there a better way to do this? Should I have a new class (i.e. for use in a List<ResultClass>) for the results? Or use an Object [] type for the results? Or use an Object[] and then populate an individual Event?

Hope this makes sense..

Eric
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Replacing resultset values