JDBC中Scrollable ResultSet(滚动结果集)(四)

1.Scrollable ResultSet

Normal ResultSet allows fetching elements in forward only direction. However, Scrollable ResultSet allows us to easily move in forward/backward direction.To create scrollable ResultSet, we must use a Statement/PreparedStatement object and provide scroll type to createStatement/prepareStatement method.

2.Syntax :

1.PreparedStatement pstmt = conn.prepareStatement(sql,Scroll type constant,Concurrency constant);

Statement stmt = conn.createStatement(Scroll type constant,Concurrency constant);

3.Scroll type constant

There are 3 scroll type constants can be used with ResultSets.

ResultSet.TYPE_FORWARD_ONLY

Default type.. only allows forward only fetching

ResultSet.TYPE_SCROLL_INSENSITIVE

Allows both forward and backward movement. Not sensitive to ResultSet updates.

ResultSet.TYPE_SCROLL_SENSITIVE

Allows both forward and backward movement. Not sensitive to ResultSet updates.

4.Concurrency constant

We can use following Concurrency constants for the ResultSets.

ResultSet.CONCUR_READ_ONLY

Default value .. ResultSet can not be updated.

ResultSet.CONCUR_UPDATABLE

Signifies an updatable ResultSet.

1.Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,                                      ResultSet.CONCUR_UPDATABLE);

2.PreparedStatement pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

5.Demo Code

import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;public class ScrollableResultsetDemo {    public static void main(String[] args) throws SQLException {        String url ="jdbc:mysql://localhost:3306/TestDB";        String user = "userid";        String password = "password";        Connection conn = DriverManager.getConnection(url, user, password);        System.out.println("Successfully connected");        getEmployeeData(conn);    }    private static void getEmployeeData(Connection conn) throws SQLException{        String sql = "select id,name,age from employee";        try(PreparedStatement pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,                ResultSet.CONCUR_READ_ONLY);){            ResultSet rs = pstmt.executeQuery();            //First Record            rs.first();            System.out.println("Emp Id : " + rs.getInt("id") + ", Name : " + rs.getString("name") + ", Age : " + rs.getInt("age"));            //Last Record            rs.last();            System.out.println("Emp Id : " + rs.getInt("id") + ", Name : " + rs.getString("name") + ", Age : " + rs.getInt("age"));            //Previous Record            rs.previous();            System.out.println("Emp Id : " + rs.getInt("id") + ", Name : " + rs.getString("name") + ", Age : " + rs.getInt("age"));            //Next Record            rs.next();            System.out.println("Emp Id : " + rs.getInt("id") + ", Name : " + rs.getString("name") + ", Age : " + rs.getInt("age"));        }    }}