JDBC可滚动和可更新结果集
上一篇文章中查询所得到的结果集都是不可滚动的。在这一篇文章中我们将介绍可滚动结果集。
这篇文章主要以代码展示，读者可根据代码及其注释理解这两个知识点。
根据不同的数据库驱动支持不同的功能，具体支持什么功能可根据
conn.getMetaData().supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE) 这个方法进行查询。具体方法使用查阅API。
可滚动结果集：一个可以上下翻阅结果集中的数据的结果集
可更新结果集：可以将结果集中的更新传输到数据库中实现数据库的更新，对数据库实现增删改查。应用getConcurrency()检测结果集是否可更新。
连接数据库
package testJDBCExecSql;
import java.io.IOException;
import java.io.InputStream;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
public class DAO {
private static InputStream in = null;
private static Properties pope = new Properties(); //读取数据库配置文件
private static Connection conn = null; //获得连接
public static Connection getConnectoin() {
try {
in = Files.newInputStream(Paths.get("E:\\IdeaWorkSpace\\database.properties"));
pope.load(in);
String driver = pope.getProperty("jdbc.drivers");
String url = pope.getProperty("jdbc.url");
String username = pope.getProperty("jdbc.username");
String password = pope.getProperty("jdbc.password");
conn = DriverManager.getConnection(url, username, password);
} catch (IOException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static void closeConnecton() {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
package testJDBCExecSql;
public class Student {
private String stuno;
private String stuname;
private String stusex;
public String getStusex() {
return stusex;
}
public void setStusex(String stusex) {
this.stusex = stusex;
}
public String getStuno() {
return this.stuno;
}
public void setStuno(String stuno) {
this.stuno = stuno;
}
public String getStuname() {
return this.stuname;
}
public void setStuname(String stuname) {
this.stuname = stuname;
}
@Override
public String toString() {
return "Student{" +
"stuno='" + stuno + '\'' +
", stuname='" + stuname + '\'' +
", stusex='" + stusex + '\'' +
'}';
}
}
package testJDBCExecSql;
import java.sql.*;
import java.util.ArrayList;
import java.util.Scanner;
/**
* @date：2017/11/29
* @time：13:55
*
* 功能：可滚动结果集，对数据库进行操作
*/
public class RollResultSet {
private ArrayList<Student> students = null; //存储结果集中的数据
public static void main(String[] args){
RollResultSet rrs = new RollResultSet();
rrs.showStudentun(); //可滚动结果集的遍历
rrs.showStudent(); //可滚动个更新
}
public void showStudentun(){ //查询数据，对数据库更新不敏感，同时不能更新数据库
students = new ArrayList<>();
Student student = new Student();
Connection conn = null;
conn = DAO.getConnectoin();
String result = null;
Scanner in = new Scanner(System.in);
boolean br = true;
String Sstudent = "select * from t_student;";
try {
//建立可滚动结果集
/*
TYPE_FORWARD_ONLY 结果集不能滚动，默认
TYPE_SCROLL_INSENSITIVE 可滚动，但对数据库不敏感
TYPE_SCROLL_SENSITIVE 可滚动，对数据库敏感，但是mysql中不允许
CONCUR_READ_ONLY 结果集不能更新数据库，默认
CONCUR_UPDATEBLE 结果集可更新数据库
*/
Statement stat = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stat.executeQuery(Sstudent); //得到可滚动结果集
while (rs.next()){
student.setStuno(rs.getString("stuno"));
student.setStuname(rs.getString("stuname"));
student.setStusex(rs.getString("stusex"));
result = student.toString();
System.out.println(result);
students.add(student);
}
while(br){
System.out.print("输出上一个 1 ,下一个 2, 直接输入序号 3,移动多少个行 4,退出 5：");
switch (in.nextInt()){
case 1:{
if(rs.previous()){ //向上翻阅
student.setStuno(rs.getString("stuno"));
student.setStuname(rs.getString("stuname"));
student.setStusex(rs.getString("stusex"));
result = student.toString();
System.out.println(result);
}else {
System.out.println("这是第一个对象");
}
break;
}
case 2:{
if(rs.next()){ //向下翻阅
student.setStuno(rs.getString("stuno"));
student.setStuname(rs.getString("stuname"));
student.setStusex(rs.getString("stusex"));
result = student.toString();
System.out.println(result);
}else {
System.out.println("这是最后一个对象");
}
break;
}
case 3:{
System.out.print("输入行号：");
int i = in.nextInt();
rs.absolute(i); //跳转到固定的行号
if(rs.getRow() != 0){ //如果超出结果集的范围，返回当前行号为0
student.setStuno(rs.getString("stuno"));
student.setStuname(rs.getString("stuname"));
student.setStusex(rs.getString("stusex"));
result = student.toString();
System.out.println(result);
}else {
System.out.println("这个行号不在范围内");
}
break;
}
case 4:{
System.out.print("移动的行数：");
int i = in.nextInt();
if (rs.relative(i)){ //移动相应的行数，向前为正数，向后为负数
student.setStuno(rs.getString("stuno"));
student.setStuname(rs.getString("stuname"));
student.setStusex(rs.getString("stusex"));
result = student.toString();
System.out.println(result);
}else {
System.out.println("越界");
}
break;
}
case 5:{
br = false;
conn.close();
rs.close();
stat.close();
}
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public void showStudent(){ //查询数据库，对数据库更新敏感，同时可以随时更新数据库
Connection conn = null;
conn = DAO.getConnectoin();
String Sstudent = "select * from t_student;";
boolean s;
try {
s = conn.getMetaData().supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE); //判断是否支持当前的操作
System.out.println(s);
s = conn.getMetaData().supportsResultSetType(ResultSet.CONCUR_UPDATABLE);
System.out.println(s);
} catch (SQLException e) {
e.printStackTrace();
}
try {
//创建可更新的结果集，对数据库更新敏感
Statement stat = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); //支持
ResultSet rs = stat.executeQuery(Sstudent);
Student student = new Student();
String result = null;
boolean br = true;
Scanner in = new Scanner(System.in);
while(br){
System.out.print("直接输入序号 1,插入行 2, 删除 3,退出 4：");
switch (in.nextInt()){
case 1:{
//先将要更改的行打印出来
System.out.print("输入行号：");
int i = in.nextInt();
rs.absolute(i); //跳转到固定的行号
if(rs.getRow() != 0){ //如果超出结果集的范围，返回当前行号为0
student.setStuno(rs.getString("stuno"));
student.setStuname(rs.getString("stuname"));
student.setStusex(rs.getString("stusex"));
result = student.toString();
System.out.println(result);
}else {
System.out.println("这个行号不在范围内");
}
String name = null;
System.out.println("输入更新后的姓名："); //以前出现没有换行问题总是不能输入名字
name = in.next();
rs.updateString("stuname",name); //注：这里表必须有主键 ，将要更改的字段及字段值写进去
System.out.println("确认更新 Y，取消 N");
String n = null;
n = in.next().toUpperCase(); //将输进去的数据转换成大写
if(n.equals("Y")){
rs.updateRow(); //更新行，同时同步到数据库
System.out.println("更新完成：");
student.setStuno(rs.getString("stuno"));
student.setStuname(rs.getString("stuname"));
student.setStusex(rs.getString("stusex"));
result = student.toString();
System.out.println(result);
}else {
// System.out.println(n+"1");
rs.cancelRowUpdates();
}
break;
}
case 2:{
rs.beforeFirst(); //跳转到第一行之前
while (rs.next()){
student.setStuno(rs.getString("stuno"));
student.setStuname(rs.getString("stuname"));
student.setStusex(rs.getString("stusex"));
result = student.toString();
System.out.println(result);
}
rs.beforeFirst(); //重新跳转到第一行之前
rs.moveToInsertRow(); //移到要插入的行
System.out.println("分别输入序号，姓名，性别");
rs.updateString("stuno",in.next()); //输入每一个字段的值，如果不输入，且没有限定为非空，则为空
rs.updateString("stuname",in.next());
rs.updateString("stusex",in.next());
rs.insertRow(); //记住是insert ，更新行
rs.moveToCurrentRow(); //游标跳转回原来的位置
rs.beforeFirst();
System.out.println("插入后的结果");
while (rs.next()){
student.setStuno(rs.getString("stuno"));
student.setStuname(rs.getString("stuname"));
student.setStusex(rs.getString("stusex"));
result = student.toString();
System.out.println(result);
}
break;
}
case 3:{
rs.beforeFirst();
while (rs.next()){
student.setStuno(rs.getString("stuno"));
student.setStuname(rs.getString("stuname"));
student.setStusex(rs.getString("stusex"));
result = student.toString();
System.out.println(result);
}
System.out.println("输入要删除的行号：");
int i = in.nextInt();
rs.absolute(i); //跳转至要删除的行
rs.deleteRow(); //删除当前行
rs.beforeFirst();
System.out.println("插入后的结果");
while (rs.next()){
student.setStuno(rs.getString("stuno"));
student.setStuname(rs.getString("stuname"));
student.setStusex(rs.getString("stusex"));
result = student.toString();
System.out.println(result);
}
break;
}
case 4:{
br = false;
conn.close();
rs.close();
stat.close();
}
}
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
