ResultSet Not working in Java

Question

PFB the code I've used:

ResultSet rs1 = stmt.executeQuery("select dfd.document_id,dfd.country_id,dfi.folder_name from dossier_folder_documents dfd,dossier_folder_info dfi where dfd.fk_dossier_folder_id=dfi.pk_dossier_folder_id and dfd.is_core is null and dfd.action='N' and dfd.fk_dossier_basic_info_id in (select pk_dossier_id from dossier_basic_info where dossier_id= '"+vdlist.get(g)+"' and (dfd.document_id,dfi.folder_name) not in(select dfd.document_id,dfi.folder_name from dossier_folder_documents dfd,dossier_folder_info dfi where dfd.fk_dossier_folder_id=dfi.pk_dossier_folder_id  and dfd.fk_dossier_basic_info_id in (select pk_dossier_id from dossier_basic_info where dossier_id= '"+sdlist.get(g)+"' and data_state='C')))");



ResultSet rs2 = stmt.executeQuery("select dfd.document_id,dfd.country_id,dfi.folder_name from dossier_folder_documents dfd,dossier_folder_info dfi where dfd.fk_dossier_folder_id=dfi.pk_dossier_folder_id and dfd.is_core is null and dfd.action='O' and dfd.fk_dossier_basic_info_id in (select pk_dossier_id from dossier_basic_info where dossier_id='"+vdlist.get(g)+"') and (dfd.document_id,dfi.folder_name) in (select dfd.document_id,dfi.folder_name from dossier_folder_documents dfd,dossier_folder_info dfi where  dfd.fk_dossier_folder_id=dfi.pk_dossier_folder_id  and dfd.fk_dossier_basic_info_id in (select pk_dossier_id from dossier_basic_info where dossier_id='"+sdlist.get(g)+"' and data_state='C'))");

            ResultSetMetaData rsmd1 = rs1.getMetaData();
            //ResultSetMetaData rsmd2 = rs2.getMetaData();
            String temp5 = new String();

            for(int f=1;f<=rsmd1.getColumnCount();f++)
                {
                    data3+=rsmd1.getColumnName(f)+"/ /";}
                    temp4=data3;
                    data3="";

                    while(rs1.next())
                        {
                            System.out.println("In here...");
                            num2=g;
                            for(int x=1;x<=rsmd1.getColumnCount();x++)
                                {
                                    temp5+=rs1.getString(x)+"/ /";

                                }
                            String data5 = new String();
                            data5=temp5;
                            sublist1.add(data5);
                            //System.out.println(sublist);
                            errvdlist.add(vdlist.get(num2));
                            errsdlist.add(sdlist.get(num2));
                            System.out.println("Validation Dossier ID for New Corruption: "+vdlist.get(num2));
                            System.out.println("Submission Dossier ID for New Corruption: "+sdlist.get(num2));

                        }

                    while(rs2.next() )
                    {
                        num2=g;
                        for(int x=1;x<=rsmd1.getColumnCount();x++)
                            {

                                temp6+=rs2.getString(x)+"/ /";
                            }

                        //String data6 = new String();

                        data6=temp6;

                        sublist2.add(data6);
                        //System.out.println(sublist);
                        errvdlist.add(vdlist.get(num2));
                        errsdlist.add(sdlist.get(num2));
                        System.out.println("Validation Dossier ID for Old: "+vdlist.get(num2));
                        System.out.println("Submission Dossier ID for Old: "+sdlist.get(num2));

                    }

                }

I have to execute two queries, and I've used two resultsets: rs1 and rs2. In my output, the while loop for rs2 gets executed but not rs1. I initially thought that there was a problem with the query I used for rs1, but that is not the case. The print statement to print 'In here...' does not get executed, which means it won't even go in the loop. Any suggestions?

Are you sure that if you run the query for rs1 in database then data is fetched? — codeLover, Jul 19 '18 at 05:29
Yes, I have tried running it directly in the database. It returns a row. — TRV, Jul 19 '18 at 05:38
Why are you executing the second statement before you've processed the result of the first statement? — user207421, Jul 19 '18 at 05:48
Please see this, maybe that can help you https://stackoverflow.com/questions/8579613/java-jdbc-accessing-multiple-resultsets — himanshu bisht, Jul 19 '18 at 06:06

score 0 · Accepted Answer · answered Jul 19 '18 at 06:13

From the ResultSet javadoc :

A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.

So basicly, a Statement can only give you one ResultSet at a time, so you loose the first result when you execute the second query.

Use two instances of Statement to be able to keep both ResultSet opened together.

ResultSet Not working in Java

1 Answers1