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

General question on SQL queries in Java

Posted on 2004-04-06
Last Modified: 2010-04-01
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.


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.
Question by:shyam_anand
LVL 92

Accepted Solution

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

Author Comment

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?

LVL 92

Expert Comment

ID: 10770837
have u looked at jasper or jfreereport?
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.


Assisted Solution

KarcOrigin earned 75 total points
ID: 10771015
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.


SELECT student_id,student_name FROM STUDENT WHERE student_id = 'XXXXX'
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.


Assisted Solution

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'
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.




Author Comment

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.

LVL 92

Expert Comment

ID: 10778672

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
caughtSpeeding java challenge 16 139
Retrieving file from bytes array in spring mvc 8 62
Suggestion on WebSite Template Sites 6 108
by zero exception 10 52
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
The Nano Server Image Builder helps you create a custom Nano Server image and bootable USB media with the aid of a graphical interface. Based on the inputs you provide, it generates images for deployment and creates reusable PowerShell scripts that …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

839 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