How to Process ResultSets the right way?

Question

Which is the right way to process the ResultSet in Java from a Sqlite Database?

At the moment i have a "SQL" class which does all the SQL Queries.

If another class needs some Data it calls a method of the SQL class, the SQL class gets the ResultSet stores it in a Array or something else and returns the array.

But thats kinda stupid and i know that's for sure not the right way.

So how do i get the Data of the ResultSet into my other classes without using such a nasty method?

The point where I struggle is that the ResultSet is closed as soon as the Statement gets closed so i cant return the ResultSet.

Im pretty sure i have a really big conceptual problem but i dont know how to solve it.

Answer 1

When talking about plain JDBC the common practice

1. Define abstractions you program will operate.
2. Map these abstractions to DB structure.
3. Implement classes for these abstractions - entities.
4. Provide a special layer in your code to do CRUD operations for these entities.

For the above design the DAO pattern is suggested - it allows you to separate DB access for logically separated code parts (e.g. separate DAOs for separate entities) resulting in loose coupling.

In terms of ResultSet processing:

• only DAOs should do ResultSet processing
• ResultSets in DAOs should be translated into classes representing program abstractions and only these classes should be returned from DAOs