Java JDBC storing ResultSet in ArrayList

Question

I am trying to work myself into using SQLite databases in Java.

(I am also using the sqlite-jdbc-3.20.0 library, but I think this problem isn't influenced by that.)

I have created some functions I want to use later on to simplify my workflow. For that I created the class DBInterface.java, from which I created an object in my Main.java class.

Simplified content of DBInterface.java:

/**
 * Executes a sql String and returns it's results as ResultSet[].
 *
 * @param sql as String holidng the SQL statement to execute
 * @return ResultSet[] containing the results of the query, or empty ResultSet[] if no results
 */
public ArrayList<ResultSet> excecute(String sql)
{
    if(!isConnected()) return null;
    try
    {
        return getResultSets(statement.execute(sql));
    }
    catch (SQLException e)
    {
        Main.log(tag, "An SQLException has occured for statement '"+sql+"':");
        Main.log(tag, e);
    }
    return null;
}

/**
 * Returns an array containing all resultSets of the last executed statement.
 *
 * @param isResultSet as boolean defining if the statement resulted in an result set
 * @return
 */
private ArrayList<ResultSet> getResultSets(boolean isResultSet)
{
    ArrayList<ResultSet> results = new ArrayList<>();

    try
    {
        int count = 0;
        while(true) {
            if(isResultSet)
            {
                ResultSet set = statement.getResultSet();
                results.add(set);
                /*
                while(set.next())
                {
                    //Here the information can still be retrieved
                    Main.log(tag, ""+set.getString("name")+", row:"+set.getRow());
                }
                */

            }
            else
            {
                if(statement.getUpdateCount() == -1)
                {
                    break;
                }

                Main.log(tag, "Result '"+count+"' is just a count: '"+statement.getUpdateCount()+"'");
            }

            count ++;
            isResultSet = statement.getMoreResults();
        }
    }
    catch(SQLException e)
    {
        Main.log(tag, "An SQLException has occured while processing a request:");
        Main.log(tag, e);
    }

    return results;
}

Simplified content of Main.java:

public static void main(String[] args)
{
    [...]

    dbInterface.excecute("DROP TABLE IF EXISTS Users");
    dbInterface.excecute("CREATE TABLE IF NOT EXISTS Users (id INTEGER, name STRING(20));");
    dbInterface.excecute("INSERT INTO Users (id, name) VALUES (1, \"Hans\");");
    dbInterface.excecute("INSERT INTO Users (id, name) VALUES (2, \"Peter\");");

    ArrayList<ResultSet> results = dbInterface.excecute("SELECT * FROM Users;");

    log(tag, ""+results.size());
    for(int i = 0; i < results.size(); i++)
    {
        ResultSet set = results.get(i);
        try
        {
            ///*
            while(set.next())
            {
                //TODO Here the information can no longer be retrieved... :(
                Main.log(tag, ""+set.getString("name")+", row:"+set.getRow());
            }
            //*/
        }
        catch (Exception e)
        {
            Main.log(tag, e);
        }
    }
}

Since from how I understand it is possible in some cases that an SQL query creates multiple ResultSet's, I've though that I will simply add all ResultSet objects to an ArrayList, so I can see when I'm processing the results somewhere else how many ResultSet's exist. Stranegly though, this doesn't work. By uncommenting the block in getResultSets() in DBInterface.java I can confirm that the result set does contain the information.

However, doing the same thing in Main.java from the ArrayList, for some reason I can no longer access the information. I have tested to change the functions to instead return the ResultSet object directly, which did work, however it does not fulfill my goal, since I want to return all possible ResultSet objetcs, not just one.

are You sure not think 'array list of rows from one ResultSet' ? My preferred structure to return will be List<Map<String,Object>> — Jacek Cz
– Jacek Cz, Commented Oct 5, 2017 at 14:55
ResultSet is not something you can pass around and use, since it's associated with a query (i.e. Statement) and Connection. You need to read it, preferably parse the results into your own domain objects and pass those around. — Kayaman
– Kayaman, Commented Oct 5, 2017 at 14:59
Hmmm, then maybe I didn't understand ResultSet correcly. I thought of it as an object that holds the results of a query, and since it is an object I can pass it (or rather the reference to it) around. How would I then proceed and store the results of the ResultSet in my own object, if I do not know what it will contain / if there are more then one ResultSet's for the query? — Uebertreiberman
– Uebertreiberman, Commented Oct 5, 2017 at 15:04
I suggest you read the JDBC apidoc, result sets are closed if you 1a) execute a new statement on the same Statement object, 1b execute any statement on the same connection in auto-commit, 2) close the Statement, 3) close the Connection, 4) commit/rollback a transaction (and the result set is not holdable over commit), or 5) call getMoreResults() on a statement (which you do). Result sets should be processed and closed ASAP, not passed around. — Mark Rotteveel
– Mark Rotteveel, Commented Oct 5, 2017 at 16:03

Hung Vo · Accepted Answer · 2017-10-05 15:39:45Z

5

You can write a method to convert ResultSet to list, map or class and store the results of ResultSet in your object

Something like this

public List resultSetToArrayList(ResultSet rs) throws SQLException {
    ResultSetMetaData md = rs.getMetaData();
    int columns = md.getColumnCount();
    List<Map<String, Object>> list = new ArrayList<>();
    while (rs.next()) {
        Map<String, Object> row = new HashMap<>(columns);
        for (int i = 1; i <= columns; ++i) {
            row.put(md.getColumnName(i), rs.getObject(i));
        }
        list.add(row);
    }

    return list;
}

Remember to close ResultSet, Statement and Connection when you don't use it

Closing ResultSet explicitly gives chance to garbage collector to recollect memory as early as possible because ResultSet object may occupy lot of memory depending on query.

edited Oct 5, 2017 at 15:39

answered Oct 5, 2017 at 15:34

Hung Vo

2991 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Michael Peacock · Accepted Answer · 2017-10-05 15:45:14Z

Granted, this is much simpler using Spring, but it's good to know how to use plain old JDBC.

Rather than returning a List object, create a Java class to hold the result objects and return those in your list.

You can do this in a method that is specifically designed to return these objects.

For example, if you created a User class, you could do something like this:

private static String SQL = "SELECT * FROM Users";
...

private List<User> getUsers()
{
    List<User> results = new ArrayList<>();

    try
    {
        PreparedStatement statement = connection.prepareStatement(SQL);
        ResultSet rs = statement.executeQuery(SQL);
        while(rs.next()) {
          User aUser = new User();
           aUser.setUserid(rs.getString("USER_ID"));
           aUser.setUserName(rs.getString("USERNAME"));
           ...
         results.add(aUser);
        }
    }
    catch(SQLException e) {
     // log any exceptions ...
    }
    finally() {
     // always close your JDBC resources - google it
     // example
     try{
        if (statement != null) {
           statement.close();
           statement=null;
        }
      }
      catch(SQLException e) {
        // couldn't close statement
      }
      ...
    }

    return results;
}

If you are not using a JDBC DataSource, then you have to manage your DB connections yourself - which can be tedious and is (was) a common source of application errors. Almost all modern Java applications use DataSource implementations (and pooling DataSources are very common) rather than simple JDBC connections.

Collectives™ on Stack Overflow

Java JDBC storing ResultSet in ArrayList

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related