Implement next and previous from database to form

Question

I have a problem in implementing this case.

1) some examples show that we can use as in the following:

        try{   
        bd.conectarBaseDeDatos();
                PreparedStatement stmt;
                String sql=("SELECT * FROM cab_pedido a, det_pedido b WHERE a.numero = b.numero and a.cod_agencia = b.cod_agencia");     
                System.out.println(sql);
                stmt = bd.conexion.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY); 
                bd.result = stmt.executeQuery(sql);
        }
    catch (InstantiationException | IllegalAccessException | SQLException e){ System.out.println(e);}  
       return rs;
}

This returns my resultSet and I can use rs.next(). rs.previous(), etc to solve my problem, but I see some comments that say we should close rs and db connection. How dangerous is that? I can implement without closing the resultset and connection? Because when we close resultSet, I will not able to get data anymore.

2) Store the data into Hashmap or list This is another possibility but if I want to get the last or the first values how can I do that?

I need the next, prev, last, and first functions but I'm not sure about my first implementation.

Can anybody give me some advices of how start this.
I need solutions, advices. that duplicate means nothing.

score 0 · Answer 1 · answered Apr 01 '15 at 18:52

I am going to try to start an answer here since I don't have option to comment yet All that is understandable from your code and question is:

You have a resultset from which you want to read first, last, previous, next etc set of data.
Closing the db connection resource
Either to use HashMap or List

Well, from what you have so far, you can definitely read the data from your resultset into List or HashMap

Secondly on closing the resources, Yes! You should close the db connection resources always to avoid memory leak. You can do that by adding a finally block after the catch block in your code.

On HasMap or List. If you aren't sure about the type of data you will be reading from your resultset then go with some implementation of List, e.g. ArrayList<String>. If you know the data will have Keys and Values, then you can go for HashMap.

These are just general guidelines. If you post some more code, maybe we can help further.

score 0 · Answer 2 · edited May 23 '17 at 12:08

ANSWER for #1 -- This is a repeated question.

When you close your DB connection. ResultSet and Statement are also closed. But that is not a guaranteed action. Therefore you should close all your DB resources.

Need for closing DB resources separately Also consider scenario where max no of DB connection allowed to be open in the connection pool is set to a fix number. You will eventually reach that and your application will simply crash or not respond correctly.

This not only applies to DB connection/resulorces but to all IO resources. You should (as a good practice) always close all your IO resources after you are done using them.

Not doing so you are simply leaving reason for memory leaks.

Answer to #2

Its a good to move the information/data into a proper data structure before you may want to close resultset. This is more of implementation scenario which we always face. Form of data structure to use again will be based on the scenario. But in all cases you may want to use a Bean class/POJO to store relevant information for each row fetched because you are getting multiple values each row.

Another suggestion would be not to do a select * call via JDBC. This is not very useful. You should be preferably mentioning column names, this will allow you to control the data you are fetching into the resultset, also in many cases make the query execution faster.

Implement next and previous from database to form

2 Answers2