How to return 2 ResultSet?

Question

Hi I am trying to fetch data from 2 place. One from Database and second from class file. After getting data I want to return those both ResultSet object to other file using return keyword but how to return 2 resultsets I don't know.

My code :

public ResultSet getdata(String query, boolean test, List<CartEntry> items)
    {
        int k;
            try
            {
                Connection conn;
                Statement st;
                public ResultSet rs,result;
                List<Product> prodList = new ArrayList<Product>();
                for(CartEntry ce : items)
                {
                    prodList.add(new Product("p" + ce.getpId(), "test", "prod" + ce.getpId(), (int)ce.getpId(), ce.getpId() + 0.12f, ce.getQuantity()));
                    result = new DummyResultSet(prodList);
                    k = ce.getQuantity();
                    System.out.println(k);
                }

                st=conn.createStatement();
                rs=st.executeQuery(query);          
                querystatus=true;
                return rs;
            }
            catch(Exception e)
            {
                querystatus=false;
            }
        }

the last line in try block i.e. return rs; I want something like return rs,result; but how to implement it I don't know. I have tried it simply like that but it doesn't work, it gives me an error. Anyone have Idea that how to return data to other file and how to retrieve it ??

add it in a arraylist and return that arraylist – AJ. Nov 12 '13 at 06:44 — AJ., Nov 12 '13 at 06:44
@AJ. : How sir ? return new ResultSet[]{rs,result}; – Java Curious ღ Nov 12 '13 at 06:46 — Java Curious ღ, Nov 12 '13 at 06:46

Shoaib Chikate · Answer 1 · 2013-11-12T06:55:34.113

2

First thing very bad practice to return ResultSet from one layer to another layer as it doesn't implements Serializable interface.

Another bad thing is we are revealing our technology to Service layer that we are using JDBC in Data Access Layer where sometimes Service layer development can be done by some third party. We should not reveal to any other layer that what we are using viz JDBC or Hibernate or Spring JDBC.

So you can collect your data from ResultSet and store into a List and then return that List.

edited Nov 12 '13 at 06:55

answered Nov 12 '13 at 06:47

Shoaib Chikate

8,665
12
47
70

Means I ll create List object then put ResultSet data into it and then return list object ? – Java Curious ღ Nov 12 '13 at 06:49
What does serializable have to do with a it? – Sotirios Delimanolis Nov 12 '13 at 06:49
2

Serializable interface isn't really related to the fact that it's bad practice (though it's a hint). It's bad practice since it leaks the database connection and is meant for consuming the result data. – eis Nov 12 '13 at 06:50
@Sotirios i just said that returning ResultSet is bad practice and requested to change return type as list – Shoaib Chikate Nov 12 '13 at 06:50
And one thing is that we should not reveal our technology what we are using in Data Access Layer....here by looking this method anybody from Service layer can say that he has used JDBC which is again bad....we should not reveal that what we have used....whether Hibernate or JDBC or else – Shoaib Chikate Nov 12 '13 at 06:53
1

@user2659972ღ What Shoaib meant, is that you should retrieve all the information from the result set and store it in a custom Java Object of yours (i.e. a POJO). Name it Result or whatever you want, with all attributes that should be stored in it. This object will be your business domain object that contains the data from the result set, but this is not the result set itself. Then you will return this object, instead of the result set. The result set itself, you will properly dispose of in the `finally` clause of your `try` expression. GL! – jlr Nov 12 '13 at 06:58

score 2 · Answer 2 · answered Nov 12 '13 at 06:48

Other people have told you how to do it, so I'm adding my answer that explains that you should not do this.

You shouldn't basically return result sets, they're meant for consuming the answer: instead you should read up the result set, store the results somewhere (for example an array of value objects) and free the result. Otherwise you're easily leaking your database connection.

AJ. · Accepted Answer · 2013-11-12T07:15:37.267

Make changes in your code:

public ArrayList getdata(String query, boolean test, List<CartEntry> items)
    {
ArrayList arr = new ArrayList();// This is a ArrayList
        int k;
            try
            {
                Connection conn;
                Statement st;
                public ResultSet rs,result;
                List<Product> prodList = new ArrayList<Product>();
                for(CartEntry ce : items)
                {
                    prodList.add(new Product("p" + ce.getpId(), "test", "prod" + ce.getpId(), (int)ce.getpId(), ce.getpId() + 0.12f, ce.getQuantity()));
                    result = new DummyResultSet(prodList);
                    k = ce.getQuantity();
                    System.out.println(k);
                }

                st=conn.createStatement();
                rs=st.executeQuery(query);          
                querystatus=true;
arr.add(rs);// Add it to list
arr.add(result);
                return arr; // Return the List
            }
            catch(Exception e)
            {
                querystatus=false;
            }
        }

on receiving function do this:-

 ArrayList arr = getdata(query, test,items);


        ResultSet rs = (ResultSet)arr.get(0);
        ResultSet result = (ResultSet)arr.get(1);

but I am calling something like this `ResultSet rs = getdata(query, test, items);` then what I ll have to do ? `ArrayList arry[] = getdata(query, test, items);` or something else ? — Java Curious ღ, Nov 12 '13 at 07:12

score 0 · Answer 4 · answered Nov 12 '13 at 06:45

You can use ResultSet array or ResultSet List to return more than one ResultSet. Take a look following example which used ResultSet[].

public ResultSet[] getdata(String query, boolean test, List<CartEntry> items)
{
    int k;
        try
        {
            Connection conn;
            Statement st;
            ResultSet[] rs=new ResultSet[2];
            List<Product> prodList = new ArrayList<Product>();
            for(CartEntry ce : items)
            {
                prodList.add(new Product("p" + ce.getpId(), "test", "prod" + ce.getpId(), (int)ce.getpId(), ce.getpId() + 0.12f, ce.getQuantity()));
                rs[1] = new DummyResultSet(prodList);
                k = ce.getQuantity();
                System.out.println(k);
            }

            st=conn.createStatement();
            rs[0]=st.executeQuery(query);          
            querystatus=true;

        }
        catch(Exception e)
        {
            querystatus=false;
        }
       return rs;
    }

How to return 2 ResultSet?

4 Answers4