hibernate query how to get data in correct format

Question

I'm having a problem that I've spent far too long trying to figure out.

I am new to hibernate, and I have data that looks like.

enter image description here

and I would like to write a query that returns this data to look something like this.

enter image description here

If this can be done with just an SQL statement that would be ideal as I'm still very unfamiliar with some of the finer features of hibernate. I've tried many things, and I thought I could do it with Unions, but unfortunately hibernate doesn't support unions.

Any suggestions is appreciated. If the suggestion is a more hibernate oriented approach then as much detail as possible would be appreciated.

UPDATE://

FROM ANGELO'S Comments, I was able to get this working using the following:

String newQuery = "";
List<?> list = null;
Query quer = null;
Session session = null;
session = HibernateUtilities.getSessionFactory().openSession();

newQuery = 
"SELECT DISTINCT o.APPLICATION,(SELECT DATA FROM " + dataTable +  " gsm WHERE gsm.APPLICATION = o.APPLICATION AND gsm.NETWORK_TYPE = 'GSM Usage') AS gsm, (SELECT DATA FROM " + dataTable +  " wifi WHERE wifi.APPLICATION = o.APPLICATION AND wifi.NETWORK_TYPE = 'Wi-Fi Usage') AS wifi, (SELECT DATA FROM " + dataTable +  " roam WHERE roam.APPLICATION = o.APPLICATION AND roam.NETWORK_TYPE = 'ROAMING Usage') AS roaming FROM " + dataTable +  " o";

quer = session.createQuery(newQuery);
list = quer.list();
session.close();

also, please don't use screenshots if avoidable, you can use 4 spaces to get a code block (so fixed-width chars) that should do the trick. — Angelo Fuchs
– Angelo Fuchs, Commented Apr 23, 2012 at 17:59
Usage is just the Data column separated from one column into multiple columns — Dave
– Dave, Commented Apr 23, 2012 at 18:06
Sorry for the screen shots, I figured that the clearest way to get my problem across would be this way as a picture is worth a thousand words and I didn't know if I could explain the problem well enough. — Dave
– Dave, Commented Apr 23, 2012 at 18:25

Angelo Fuchs · Accepted Answer · 2012-04-23 20:31:02Z

1

You can use subselects (in SQL, Marko said in the comments that it won't work in Hibernate.)

SELECT DISTINCT o.application, 
    (SELECT sum(DATA) FROM tbl gsm WHERE gsm.application = o.application AND gsm.network_type = 'GSM') AS gsm, 
    (SELECT sum(DATA) FROM tbl wifi WHERE wifi.application = o.application AND wifi.network_type = 'WIFI') AS wifi, 
    (SELECT sum(DATA) FROM tbl roam WHERE roam.application = o.application AND roam.network_type = 'ROAMING') AS roaming
FROM tbl o

(be aware that I assume your table to be called tbl)

edited Apr 23, 2012 at 20:31

answered Apr 23, 2012 at 18:02

Angelo Fuchs

9,9911 gold badge39 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Marko Topolnik Over a year ago

No, with hibernate the subselects don't work within the select clause.

Dave Over a year ago

Thank you very much for your quick response! This approach actually worked really well for me. I had to make a few modifications though. This was returning 22 results instead of 7, so I added DISTINCT in front of the application. Other than that and minor name issues this was perfect. Thank You very much!!

Angelo Fuchs Over a year ago

@Dyarish you're weclome, I added the DISTINCT for future readers.

Collectives™ on Stack Overflow

hibernate query how to get data in correct format

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related