Cursor result sets

A cursor is a data processing method that allows you to browse one or more rows forward or backward in a result set at a time, for you to view or process data in the result set.

Result set types

JDBC result sets are divided into the following three types by configuration:

• General result sets: All results are obtained at a time. This achieves fast execution but is prone to cause OOM issues.

• Streaming result sets: Data is read from the socket row by row rather than all at a time. Streaming result sets can be used for statement.setFetchSize(Integer.MIN_VALUE). A streaming result set facilitates fast execution, but the execution cannot be stopped once the reading starts. This is prone to cause network issues.

• Cursor result sets: To return a cursor result set, the database must support COM_STMT_FETCH and useServerPrepStmts must be set to true. Use the useCursorFetch=true syntax, and specify fetchSize. Cursor result sets result in slow execution and are available only when ODP V1.8.5 or later and OBServer V2.2.75 or later are used.

Use useCursorFetch

Perform the following steps to use useCursorFetch:

1. Establish a link and set parameters useCursorFetch and useServerPrepStmts.

   String url = "jdbc:mysql://host:port/test?useServerPrepStmts=false&useCursorFetch=true"conn = DriverManager.getConnection(url,"admin@mysql", "admin");
   

2. Generate the Prepare statement.

   PreparedStatement pstmt = conn.prepareStatement("select * from tab",ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
   

3. Set fetchsize.

   pstmt.setFetchSize(3);
   

4. Execute the statement.

   ResultSet rs = pstmt.executeQuery();
   

5. Cyclically obtain the next row of data.

   while (rs.next()) {}