java - 了解仅前向结果集

Question

我在 mysql 数据库中有一个表，其中一些行可用。我想从 jdbc 程序中的表中检索数据，其中 ResultSet 仅向前。我的代码是:

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();
            }
        }
    }
}

但由于默认结果集仅向前发送，因此我们无法在其中调用 previous() ，否则它不应该工作，但在我的程序中这样做也会以相反的顺序检索数据。我的代码有什么问题？

Answer 1

这就是 MySQL 处理它的方式，其他数据库的行为可能有所不同。

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