usescrollableresultset.java

jsp 应用开发技术光盘 是《jsp应用开发技术》这本书的源代码

package ch13;

import java.sql.*;

public class UseScrollableResultSet {
	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost/sql_test";
		String userName = "root";
		String password = "root";
		Connection conn = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch(ClassNotFoundException e) {
			System.out.println("加载驱动器类时出现异常");
		}
		
		try {
			conn = DriverManager.getConnection(url, userName, password);
			
			//创建返回可滚动结果集的语句对象
			Statement stmt = conn.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE, 
					ResultSet.CONCUR_READ_ONLY);
			//pstmt = conn.prepareStatement("SELECT * FROM student"
			//        ResultSet.TYPE_SCROLL_SENSITIVE, 
			//        ResultSet.CONCUR_READ_ONLY);
			
			//执行SQL查询语句得到可滚动结果集
			ResultSet rs = stmt.executeQuery("SELECT * FROM student");
			
			System.out.println("");
			System.out.println("当前游标是否在第一行之前：" + rs.isBeforeFirst());
			
			System.out.println("");
			System.out.println("由前至后顺序显示结果集：");
			
			//使用next()方法顺序显示结果集
			while (rs.next()) {	
				String id      = rs.getString(1);
				String name    = rs.getString(2);
				String address = rs.getString("address");
				Date birthday = rs.getDate("birthdate");
				
				System.out.println(id + "   " + name + "   " + 
						address + "   " + birthday);
			}
			
			System.out.println("");
			System.out.println("当前游标是否在最后一行之后：" + rs.isAfterLast());
			
			System.out.println("");
			System.out.println("由后至前逆序显示结果集：");
			
			//使用previous()方法逆序显示结果集
			while (rs.previous()) {	
				String id      = rs.getString(1);
				String name    = rs.getString(2);
				String address = rs.getString("address");
				Date birthday = rs.getDate("birthdate");
				
				System.out.println(id + "   " + name + "   " + 
						address + "   " + birthday);
			}
			
			System.out.println("");
			System.out.println("将游标移动到第一行");
			rs.first();
			System.out.println("当前游标是否在第一行：" + rs.isFirst());
			System.out.println("结果集第一行的数据为：");
			System.out.println(rs.getString(1) +  "   " + rs.getString(2) + 
					"   " + rs.getString(3) +  "   " + rs.getDate("birthdate"));
			
			System.out.println("");
			System.out.println("将游标移动到最后一行");
			rs.last();
			System.out.println("当前游标是否在最后一行：" + rs.isLast());
			System.out.println("结果集最后一行的数据为：");
			System.out.println(rs.getString(1) +  "   " + rs.getString(2) + 
				"   " + rs.getString(3) +  "   " + rs.getDate("birthdate"));

			//游标的相对定位
			System.out.println("");
			System.out.println("将游标移动到最后一行的前两行");
			rs.relative(-2);
			System.out.println("结果集最后一行的前两行的数据为：");
			System.out.println(rs.getString(1) +  "   " + rs.getString(2) + 
					"   " + rs.getString(3) +  "   " + rs.getDate("birthdate"));
			
            //游标的绝对定位
			System.out.println("");
			System.out.println("将游标移动到第二行");
			rs.absolute(2);
			System.out.println("结果集第二行的数据为：");
			System.out.println(rs.getString(1) +  "   " + rs.getString(2) + 
					"   " + rs.getString(3) +  "   " + rs.getDate("birthdate"));
			
            //beforeFirst()方法和next()方法的配合使用
			System.out.println("");
			System.out.println("先将游标移动到第一行之前");
			rs.beforeFirst();
			System.out.println("再次由前至后顺序显示结果集：");		
			while (rs.next()) {	
				String id      = rs.getString(1);
				String name    = rs.getString(2);
				String address = rs.getString("address");
				Date birthday = rs.getDate("birthdate");
				
				System.out.println(id + "   " + name + "   " + 
						address + "   " + birthday);
			}
			
			rs.close();
			stmt.close();
			
		} catch(SQLException e) {
			System.out.println("出现SQLException异常");
		} finally {
			//关闭语句和数据库连接
			try {
				if (conn != null) conn.close();
			} catch(SQLException e) {
				System.out.println("关闭数据库连接时出现异常");
			}		
		}
		
	}
}