is there any way to fetch n number of record start form x row?

Question

I want to fetch 50 records from table which contains millions of record without using limit

I did following

private void createMYSQLConnection() throws SQLException {
        CachedRowSet crs = new CachedRowSetImpl();
        Connection conn = null;
        Statement stmt = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/mydb",
                    "root", "root");

            conn.setAutoCommit(false);
            crs.setPageSize(100);
            crs.setUsername("root");
            crs.setPassword("Glass4#21");
            crs.setCommand("SELECT * FROM trn_22_gouk_final_attendance");
            crs.absolute(10);
            crs.setFetchSize(10);
            crs.execute(conn);
            while (crs.next()) {
                System.out.println(crs.getObject(1));
            }

        } catch (SQLException se) {
            se.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            crs.close();
        }
    }

but it not works... any suggestion

Thanks in advance

I feel you need add row number clause for that rather than fetch count — Subhrajyoti Majumder, Jul 25 '14 at 06:51
This might be helpful: http://java.avdiel.com/Tutorials/JDBCPaging.html — Seb, Jul 25 '14 at 07:27
i dont want to write code to be database specific according to : java.avdiel.com/Tutorials/JDBCPaging.html for mysql i have to use `limit` for oracle i have to use `rowNum` but i want code that is independent to database — Darshan Patel, Jul 25 '14 at 10:25

score 1 · Answer 1 · edited May 23 '17 at 12:27

1

JDBC's 'fetch size' was not intended for limiting the result-set, see this question.

JDBC does not have an API for pagination, see the answers in this question.

Since the keywords for pagination are different for each database type, something needs to translate the specified pagination for the database used. Hibernate can do that, as shown here.

edited May 23 '17 at 12:27

Community

1
1

answered Jul 25 '14 at 11:27

vanOekel

6,358
1
21
56

is there any way to fetch n number of record start form x row?

1 Answers1