Solved

General question on SQL queries in Java

Posted on 2004-04-06
7
297 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
7 Comments
 
LVL 92

Accepted Solution

by:
objects earned 100 total points
Comment Utility
> 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
Comment Utility
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
Comment Utility
have u looked at jasper or jfreereport?
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 5

Assisted Solution

by:KarcOrigin
KarcOrigin earned 75 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
communication with a non technical owner 16 97
unit test DAO layer 1 133
EJB MDB example 4 76
spring example non maven 4 74
Marketing can be an uncomfortable undertaking, especially if your material is technology based. Luckily, we’ve compiled some simple and (relatively) painless tips to put an end to your trepidation and start your path to success.
Possible fixes for Windows 7 and Windows Server 2008 updating problem. Solutions mentioned are from Microsoft themselves. I started a case with them from our Microsoft Silver Partner option to open a case and get direct support from Microsoft. If s…
This video discusses moving either the default database or any database to a new volume.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

772 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now