Solved

General question on SQL queries in Java

Posted on 2004-04-06
7
298 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
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
issue with pdf generation 2 85
JSP How to use numberFormat to display a negative number without parenthesis? 1 155
spring example non maven 4 82
countXY challenge 28 146
Large Outlook files lead to various unwanted errors and corruption issues. Furthermore, large outlook files can also make Outlook take longer to start-up, search, navigate, and shut-down. So, In this article, i will discuss a method to make your Out…
Knowledge base software has turned out to be a quite reliable method for storing information, promoting collaborative work and for sharing valuable input and solutions.However, some organizations are trying to develop a knowledge base that works wit…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

920 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

18 Experts available now in Live!

Get 1:1 Help Now