Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

General question on SQL queries in Java

Posted on 2004-04-06
7
Medium Priority
?
305 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 300 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 225 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 225 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Are you an Exchange administrator employed with an organization? And, have you encountered a corrupt Exchange database due to which you are not able to open its EDB file. This article will explain all the steps to repair corrupt Exchange database.
What we learned in Webroot's webinar on multi-vector protection.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

715 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