File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Using nested Resultsets in Routines Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Using nested Resultsets in Routines " Watch "Using nested Resultsets in Routines " New topic

Using nested Resultsets in Routines

dipti khullar

Joined: Oct 23, 2008
Posts: 6
package routines;
import java.sql.*;

public class GetCategoryName {

public static void getCategoryName() {
try {
Connection con=null;
Statement s=con.createStatement();
String category = "";
String [] list = {};
String list_cat = "";
int id_av=0;
int count=0;
ResultSet rs_category=null;

ResultSet rs_breadcrumb = s.executeQuery("select ID_AUDIOVIDEO, SOURCE_SYSTEM_BREADCRUMB from AUDIOVIDEO");

while( {

id_av = rs_breadcrumb.getInt(1);
category = rs_breadcrumb.getString(2);
System.out.println("id_av " + rs_breadcrumb.getInt(1));
System.out.println("category_ID " + rs_breadcrumb.getString(2));

rs_category = s.executeQuery("select CATEGORY_NAME from RBAR_CATEGORY WHERE ID_CATEGORY IN (" + category + ")");;
list[0] = rs_category.getString(1);
for(int i=1;;i++)
{ list[i] = list[0] + "," + rs_category.getString(1);
System.out.println("list " + list[i]);
//I will be able to update the record, but the value in list is incorrect.

} catch(Exception e){e.printStackTrace();}

Problem 1: First loop. executes only once for the first row and it breaks
Problem 2: The list[i] doesn't store correct values, it throws exception.

Summarizing the issue:

SOURCE_SYSTEM_BREADCRUMB stores the ID_CATEGORY values as "1,2,3,4,5"
which I need to replace with "cat1,cat2,cat3.cat4", corresponding to ID_AUDIOVIDEO, which means using nested resultsets.
Swastik Dey

Joined: Jan 08, 2009
Posts: 1578

For your second issue most likely its throwing an ArrayIndexOutOfBoundsException.

String [] list = {};

probably you are missing the array dimension

String list[]=new String[2];

and most likely the exception itself is leading the resultset iteration problem.

dipti khullar

Joined: Oct 23, 2008
Posts: 6
Thanks a lot for your reply.

You were absolutely right.
But that resultset iteration problem is still not resolved.

I found another way of implementing that some time back using function calls and passing relevant info into that.

Thanks. It really helped and saved my considerable time of debugging.
Swastik Dey

Joined: Jan 08, 2009
Posts: 1578

Use two different Statement objects for two different ResultSets.
dipti khullar

Joined: Oct 23, 2008
Posts: 6
Thanks again.
Bauke Scholtz
Ranch Hand

Joined: Oct 08, 2006
Posts: 2458
You're underestimating the powers of the DB. Checkout the SQL JOIN clause.
I agree. Here's the link:
subject: Using nested Resultsets in Routines
It's not a secret anymore!