Understanding Forward Only ResultSet

Question

I have a table in mysql database where some rows are available. I want to retrieve the data from table in my jdbc program where the ResultSet is forward only. My code is:

import java.sql.*;

public class Test1 {
    static{
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static void main(String[] args) {
        Connection con=null;
        Statement st=null;
        ResultSet rs=null;
        int id=0;
        String name=null;
        try {
            con=DriverManager.getConnection("jdbc:mysql://localhost:3306/hiitstudents","root", "rustyiron");
            st=con.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.TYPE_SCROLL_INSENSITIVE);
            String query="select * from students";
            rs=st.executeQuery(query);
            while(rs.next()){
                id=rs.getInt(1);
                name=rs.getString(2);
                System.out.println(id+"\t"+name);
            }               ;
            while(rs.previous()){
                id=rs.getInt(1);
                name=rs.getString(2);
                System.out.println(id+"\t"+name);
            }
            } catch (SQLException e) {
                e.printStackTrace();
        }finally{
            try{
                if(rs!=null)
                    rs.close();

                if(st!=null)
                    st.close();

                if(con!=null)
                    con.close();
            }catch(SQLException se){
                se.printStackTrace();
            }
        }
    }
}

but as the default result sets are forward only we cannot call previous() in it or it should not work, but doing so in my program retrieves the data in reverse order also. What is the problem in my code?

Ok then how to check a result set is forward only? – jcool May 11 '15 at 07:43 — jcool, May 11 '15 at 07:43

score 3 · Accepted Answer · answered May 11 '15 at 07:48

3

This is how MySQL handles it, other databases may behave differently.

By default, ResultSets are completely retrieved and stored in memory.

If you are working with ResultSets that have a large number of rows or large values and cannot allocate heap space in your JVM for the memory required, you can tell the driver to stream the results back one row at a time.

To enable this functionality, create a Statement instance in the following manner:

stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
              java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);

Connector/J Reference Implementation Notes

answered May 11 '15 at 07:48

Kayaman

72,141
5
83
121

does it relate to my question? just asking because i did not understand – jcool May 11 '15 at 07:59
1

By default the whole resultset is stored in memory, so the MySQL driver allows you to traverse it anyway you want. If you want to see an exception, you should try getting the `ResultSet` with the provided code snippet. Many of the JDBC options are merely hints to the driver, so the actual behaviour may differ a lot from the javadoc. – Kayaman May 11 '15 at 08:01
Yeah I got it !! Can you please explain what stmt.setFetchSize(Integer.MIN_VALUE) does – jcool May 11 '15 at 08:09
2

Normally it means how many rows to fetch over the network when more data is needed. Let's say that you had 1000 results and `fetchSize` were 100, if you iterated through the resultset, it would perform 10 network operations to get more data. However in this case it's a magic number (being a negative value) to tell the driver to fetch a single row at a time over the network. – Kayaman May 11 '15 at 08:14
A huge thanks for u to clearing my doubt I was banging my head on it for.two days... :) – jcool May 11 '15 at 15:54
1

No problem. The JDBC API is very vague, the described functionality depends heavily on the database **and** driver used, and often times the promised functionality has no effect, since it's "a hint to the driver", and the drivers often can't or won't implement the extra functionality. – Kayaman May 12 '15 at 05:27

Understanding Forward Only ResultSet

1 Answers1

Linked