-1

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.

hitz
  • 1,100
  • 8
  • 12
Teifun2
  • 338
  • 2
  • 15
  • Don't use arrays, instead use `List` or, in the *worst* case, use `List>` as result of reading the data from the `ResultSet`. – Luiggi Mendoza May 21 '15 at 02:54

2 Answers2

2

You should map the ResultSet to a class and return the class.

For example, if you had a User class and had a user table with a name column

List<User> results = new ArrayList<User>();
while (resultSet.next()) {
    User user = new User();
    user.setName(rs.getString("name");
    results.add(user);
}
Hank
  • 3,367
  • 10
  • 48
  • 86
  • Ok i already did that sometimes. But then i still have to create a class for every table and view! And select Statements for diffrent tables need diffrent methods. Is there not a more efficent way? – Teifun2 May 20 '15 at 21:52
  • 1
    @Teifun2 That's the way to do it by hand. If you want something more "automagic" look for JPA. – pablosaraiva May 20 '15 at 22:00
2

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
FlasH from Ru
  • 1,165
  • 2
  • 13
  • 19
  • thx that helped a lot. Would it be ok to have a constructor in the entities class wich gets the ResultSet and fills the entite or does the filling of the entitie have to happen in the CRUD operating class? – Teifun2 May 22 '15 at 16:10
  • Well, from the perfectionist's point of view you should leave `ResultSet` processing in DAO - that's simplifies code maintenance especially in case of DB/DB structure changes. – FlasH from Ru May 22 '15 at 21:10
  • Moreover it's suggested to separate busines logic into a separate layer: like having a `PersonProcessor` class for a `Person` and `PersonDAO` (e.g. `PersonProcessor.login(String login)` uses `PersonDAO` to load `Person` by login, but also send him a notification email, etc.). All the other code uses `PersonProcessor` only and preferably knows nothing about `PersonDAO`. But that's a perfect case ) For a simple application this structure looks to complex, so *Processor and *DAO is often implemented as one class and so on. – FlasH from Ru May 22 '15 at 21:10
  • ok i can see the optimization of a Processor class but im not gona do that. But if the ResultSet is just processed in the DAO isnt the DAO class gona be HUUUGE? So for every enitie with all the CRUDs with processing :O is that not "not so nice" if thats all stuck in one class and not somehow separated? – Teifun2 May 22 '15 at 21:51
  • Right, it will be huge if you place _all_ DB access operations for _all_ entities in _one_ DAO class. The point is to create _separate_ DAO classes for _every_ entity class (or at least a group of related entity classes). But yes, it leads to some code duplication as stated in disadvantages section of a DAO wiki article. http://stackoverflow.com/questions/19154202/data-access-object-dao-in-java contains some useful discussions and links for the subject. – FlasH from Ru May 23 '15 at 00:16