CODEWITHSUNDEEP
javajdbc
jcool
jcool

Reputation: 324

Understanding Forward Only ResultSet

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?

Upvotes: 2

Views: 13218

Answers (1)

Kayaman
Kayaman

Reputation: 73558

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

Upvotes: 4

Related Questions