Alternative to ResultSet for storing data from database

Question

I have a database and at present I am using the PreparedStatement to call data from the database using an SQL statement. However I know that once a PreparedStatement has finished the ResultSet closes.

I need an alternative to this (the resultset closing) as the Prepared Statement is run every time the user clicks a button and the input to the Prepared Statement can change however the ResultSet cannot take any new values.

Any thoughts would be appreciated.

Answer 1

You should copy the data from the ResultSet into objects of your own before closing the PreparedStatement.

For instance:

preparedStatement = conn.prepareStement("select * from people");

resultSet = preparedStatement.executeQuery();

//copying the value
while(resultSet.hasNext()){
    String name = resultSet.getString("name");
    String surname = resultSet.getString("surname");

    //Person is a class of your own
    Person person = new Person(name,surname); 

    //people is a Collection of Person created outside this loop
    people.add(person); 
}

Afterwards, make sure you close the PreparedStatement in a finnally block, and use the object in the people collection instead of using the ResultSet directly.