Resultset within resultset

Question

1. I am facing a slight issue. I am getting data from a table and then I loop the resultset. during each loop I use the row's data returned by resultset to execute another query on another table. I am posting the code below.

2. The question is, I am sensing some problem in my code but cannot identify it. The code is running fine without any errors or exceptions. Help me to find the issue. And explain me about it too.

3. I know that we can use Dao for the first resultset. But won't it create too many objects.

     public ResultSet search_NatSupport() throws ClassNotFoundException,     SQLException  {
   
    String query = "SELECT SYMBOL, Date, sup, res FROM Nat_Support WHERE SYMBOL IS NOT ?;";
   
           PreparedStatement stmt  =  null;
           ResultSet rs            =  null;
           DBHelper helper         =  DBHelper.getInstance();
   
           stmt = helper.getConn().prepareStatement(query);
           stmt.setString(1, null);
           rs   = stmt.executeQuery();
   
            return rs;
       }
   
      public void searchAgain(ResultSet rs) {
   
       String query = "select * from bhav_NSE where symbol = ?";
   
       PreparedStatement stmt = null;
   
       try {
   
           while (rs.next()) {
   
               stmt    = DBHelper.getInstance().getConn().prepareStatement(query);
               stmt.setString(1, rs.getString(1));
               ResultSet res = stmt.executeQuery();
   
               while(res.next()){
                   System.out.println(res.getString(1));
               }
               res.close();
           }
           rs.close();
       } catch (SQLException | ClassNotFoundException e) {
           // TODO Auto-generated catch block
           e.printStackTrace();
       }
   
   }
   
   
   
       public static void main(String[] args)   { 
   
       BhavMaster_NSE finals = new BhavMaster_NSE();
   
       try {
   
           finals.searchAgain(finals.search_NatSupport());
   
       } catch (ClassNotFoundException | SQLException e) {
   
           // TODO Auto-generated catch block
   
           e.printStackTrace();
   
       }
   
   }
   

Answer 1

Instead of two result Set ,Join the tables on keys,(here Symbol) and combine not null in same Query

 public void search()
        { 
            PreparedStatement stmt = null;
            String query = "select bnse.* from bhav_NSE bnse,Nat_Support nsup where bnse.symbol=nsup.symbol and nsup.symbol is not null";
            stmt   = DBHelper.getInstance().getConn().prepareStatement(query);
             ResultSet res = stmt.executeQuery();

                while(res.next()){
                    // Get data res.getString(..);
                }
                res.close();
            }
            rs.close();

        }

Answer 2

1. you don't need to put ; at the end of query.
2. Use try with resource when using any Closeable resource

Even if you want to throw Exception to callee, you can do it with throw keyword.

try(Connection conn = DBHelper.getInstance().getConn()) {
    PreparedStatement stmt = conn.prepareStatement(query);
    ...
    ResultSet res = stmt.executeQuery();
    ...
} catch (SQLException ex) {
   // handle exception here or throw it as below :
   throw ex;
}

try-with-resource will take care of closing all the resources even any failure occur.

OR the old approach is to mind closing the resources in failure and in success too.

Answer 3

I got this problem once i needed to use the return result of a resultset as an input for other one but i found that I can't do this , After debugging i found that when you write a Resultset within a Resultset the first one became equals Null

So here is a Trick :

public void searchAgain(ResultSet rs) {
    try {
        while (rs.next()) {
           bhavNse(rs.getString(1));
        }
        rs.close();
    } catch (SQLException | ClassNotFoundException e) {
        e.printStackTrace();
    }
}
public void bhavNse(String var) {
   try {
      String query = "select * from bhav_NSE where symbol = ?";
      PreparedStatement stmt = null;
      stmt    = DBHelper.getInstance().getConn().prepareStatement(query);
      stmt.setString(1, var);
      ResultSet res = stmt.executeQuery();
      while(res.next()){
          System.out.println(res.getString(1));
      }
      res.close();
      } 
      catch (SQLException | ClassNotFoundException e) {
         e.printStackTrace();
      }
}