Using nested Resultsets in Routines

package routines;
import java.io.*;
import java.sql.*;

public class GetCategoryName {

public static void getCategoryName() {
try {
Connection con=null;
Class.forName("oracle.jdbc.driver.OracleDriver");
con=DriverManager.getConnection(
"jdbc:oracle:thin:@10.210.236.32:1521:xe",
"proj_staging",
"proj");
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(rs_breadcrumb.next()) {

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 + ")");

rs_category.next();
list[0] = rs_category.getString(1);
for(int i=1;rs_category.next();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.
}

s.close();
con.close();
} catch(Exception e){e.printStackTrace();}
}

Problem 1: First loop. rs_breacrumb.next() 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.

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.

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.

Use two different Statement objects for two different ResultSets.

Thanks again.

You're underestimating the powers of the DB. Checkout the SQL JOIN clause.