Solved

General question on SQL queries in Java

Posted on 2004-04-06
7
303 Views
Last Modified: 2010-04-01
Hi,
I'm working on a web-based reporting application using Servlets/iText. I need to display reports (in PDF format) which contains info from mutliple tables. I used joins, and mutiple queries within loops to do some reports but these techniques do not work for all scenarios.
A simple example which illustrates my problem is given below.

eg. Student table - (student_id,student_name)
1  Tom
2  Dick
3  Harry

Course table - ( student_id,course_id)
1 CS01
1 CS02
1 CS03
2 CS05
3 CS03

To display course details of students, I join Student and Course tables, and I get a total of 5 records. In the report, I need to print a student name only once, and all courses for the student. However, if I loop thru the ResultSet, I get the student name for all records. How do I ensure that the student name is printed only once. I know I can do some 'ugly' manipulation in my Java code, to do the same. But, I'm looking for an elegant solution. Are they any Java classes that do the task I need to accomplish?

Any help would be greatly appreciated.

Thanks,
Shyam

P.S
I understand that reporting tools like Jasper Reports allow queries to be attached to  report templates and the task I mentioned is automatically taken care of by the tool.
0
Comment
Question by:shyam_anand
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 92

Accepted Solution

by:
objects earned 100 total points
ID: 10770750
> I know I can do some 'ugly' manipulation in my Java code

You code would need to just store the last student id record, and output the student details whenevere it changed
while (more rows)
{
   // read next row

   if (student id != last records student id)
   {
      // output student details
   }
   // output course details

   last student id = student id
}
0
 

Author Comment

by:shyam_anand
ID: 10770824
That's precisely what I meant by 'ugly' code :)...there are around 8 tables in my join.
I was wondering if there are any Java classes that might do the trick ?
Do you know of any such utilities?

Thanks,
Shyam
0
 
LVL 92

Expert Comment

by:objects
ID: 10770837
have u looked at jasper or jfreereport?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Assisted Solution

by:KarcOrigin
KarcOrigin earned 75 total points
ID: 10771015
Hi,
What is your backend DB? Can a SQL solution fine with you. Anyways, I am considering that you are using some RDBMS as your backend database. Basically it can be done by a simple UNION query.

Eg:

SELECT student_id,student_name FROM STUDENT WHERE student_id = 'XXXXX'
UNION
SELECT student_id,student_name, course_id  FROM STUDENT S, COURSE C
WHERE S.student_id = C.student_id
AND student_id = 'XXXXX'

By the above query the first row is your header and the remaining rows are yours details. So whenever you want to print the student details just get it from the very first record otherwise the second onwards records are your details. By doing this there is no need to do anything in Java. So you can put the header columns in the upper union query and details query in the lower union query. It will much cleaner. I hope it helps you.
Thx

0
 
LVL 6

Assisted Solution

by:jarasa
jarasa earned 75 total points
ID: 10773062
to KarcOrigin.

Have you checked that query??
I don't know wich DB you use but in oracle wont work, beside is you make it to work it will repeat the student_id and student_name on all the rows, it should be like this:

SELECT student_id,student_name, '' as course_id FROM STUDENT WHERE student_id = 'XXXXX'
UNION
SELECT '' as student_id, '' as student_name, course_id  FROM STUDENT S, COURSE C
WHERE S.student_id = C.student_id
AND student_id = 'XXXXX';

This will work in Oracle and does not repeat the id and name of the student.

But it will only work one by one you can't make a select that picks all your students so if you want to make a report with all your students you must do a loop on that table and call this sql for each so it will take ages to procced.

AND THAT'S WHY JAVA EXIST IF YOU WOLDN'T BE ABLE TO USE THAT "UGLY" CODE!!!!!

Javier

0
 

Author Comment

by:shyam_anand
ID: 10778309
Thanks to objects,KarcOrigin and Javier for suggestions. I have concluded that SQL based solutions are not suitable and I have to resort to 'ugly' Java code. What I wanted to know in the first place was if there were any Java classes/utilities that could do the job for me. eg: some class to which you could pass a ResultSet, and get the desired results.(similar to how reporting tools like Jasper work). Seems like this is just wishful thinking:) I had looked at Jasper reports but chose iText instead as my reports are simple and straightforward.
Anyway, thanks to all for the suggestions.

Shyam
0
 
LVL 92

Expert Comment

by:objects
ID: 10778672
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
J2SE 5.0 metadata 1 71
reflection example 3 131
servlet cookie finding by name 1 96
timeToIdleSeconds vs timeToLiveSeconds in hibernate second level cache 3 171
Deploying our service is a grudge match between customer benefits and customer pain. In one corner, rolling out fixes (yay!) and delivering new features (double yay!). In the other corner, training on new features (boo – sounds like work), and chan…
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question