可滚动结果集
常用的 ResultSet,返回后,其初始指针在第一行之前(Before First),并且只能使用 next()方法将指针向后移动,不能反向,一次移动一行,不能跳行。
可滚动的结果集:指针可以在结果集中任意移动。使用在需要指针移动的场合,比如分页。获得可滚动的 ResultSet,Statement 或者 PreparedStatement 的创建有所不同:
Statement stmt = conn.createStatement(type, concurrency);
PreparedStatement stmt = conn.prepareStatement(sql, type,concurrency);
package 可滚动结果集和ResultSet和ResultSetMetaData;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import com.mysql.jdbc.Connection;
public class Demo {
static Connection conn = null;
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");
conn = (Connection) DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test50","root","123456");
Statement stmt = conn.createStatement();
String sql = "select * from user";
ResultSet rs = stmt.executeQuery(sql);
ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData();
System.out.println("..........ResultSetMetaData............");
//列数
int columnCount = rsmd.getColumnCount();
//列名
String columnName = null;
for (int i = 1; i <= columnCount; i++) {
columnName=rsmd.getColumnName(i);
System.out.println(columnName);
}
System.out.println("..........可滚动结果集...............");
/*TYPE_FORWARD_ONLY:只能向前移动,默认参数
* TYPE_SCROLL_INSENSITIVE:可滚动,不感知数据变化--------------我的MySQL只支持这个
* TYPE_SCROLL_SENSITIVE:可滚动,感知数据变化
* */
// Statement stmt2 = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY );
// ResultSet rs2 = stmt2.executeQuery(sql);
PreparedStatement stmt2 = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
ResultSet rs2 = stmt2.executeQuery();
//判断一下mysql数据库是否支持TYPE_FORWARD_ONLY
DatabaseMetaData dbmd=conn.getMetaData();
if(dbmd.supportsResultSetType(ResultSet.TYPE_FORWARD_ONLY)){
System.out.println("*******MySQL支持TYPE_FORWARD_ONLY结果集**********");
}else{
System.out.println("*******MySQL不支持TYPE_FORWARD_ONLY结果集**********");
}
//以下带注释的语句只有在设置为“可滚动”时才可以用
rs2.first();//使第一行成为当前行
System.out.println(rs2.getString(1)); //得到第一个雇员号
rs2.last(); //使最后一行成为当前行
System.out.println(rs2.isLast());
System.out.println(rs2.getString(1));
// rs2.next();
System.out.println("0000000000000");
System.out.println(rs2.isAfterLast());//是否位于最后一行之后
System .out.println(rs2.getRow());
rs2.previous(); //向前移动移动一行,此处为倒数第二行
System.out.println(rs2.getString(1));
rs2.absolute(6); //定位到第6行
System.out.println(rs2.getString(1));
} catch (Exception e) {
e.printStackTrace();
}finally{
if (conn!=null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}