//可滚动,可定位,可更新
二,可滚动行,定位
ublic class TestJDBC3 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = "";
try {
conn = JdbcUtils.getConnection();
sql = "select * from student";
// 默认可以前后滚动
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
JdbcUtils.printResult(rs);
rs.absolute(3); // 将指针指向第三行
System.out.println("\n\n移动指针后重新输出:");
JdbcUtils.printResult(rs);
rs.last(); // 将指针指向最后一行
System.out.println("\n\n移动指针后重新输出:");
System.out.println("id\tname\tage");
do {
System.out.print(rs.getInt(1) + "\t");
System.out.print(rs.getString(2) + "\t");
System.out.print(rs.getInt(3) + "\n");
} while (rs.previous()); // 将指针从最后一行逐步向第一行移动
} catch (Exception ex) {
ex.printStackTrace();
} finally {
JdbcUtils.free(rs, pstmt, conn);
}
}
}
三,通过结果集增删改查
1,增加
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = "";
try {
conn = JdbcUtils.getConnection();
sql = "select * from student";
//默认可以前后滚动
pstmt = conn.prepareStatement(sql,
ResultSet.TYPE_SCROLL_INSENSITIVE, //不敏感,对数据库数据更改在记录集中不反应
ResultSet.CONCUR_UPDATABLE); //可以通过记录集修改数据
rs = pstmt.executeQuery();
System.out.println("添加前数据:");
JdbcUtils.printResult(rs);
rs.moveToInsertRow(); //游标定位到插入行(其实是个缓冲区)
rs.updateString("name","tianjia"); //为姓名列赋值
rs.updateInt(3,200); //为年龄列赋值
rs.insertRow(); //将新添加的数据写入数据库,id自动增长
rs = pstmt.executeQuery();
System.out.println("\n\n通过记录集添加数据后重新查询数据并输出:");
JdbcUtils.printResult(rs);
} catch(Exception ex) {
ex.printStackTrace();
} finally {
JdbcUtils.free(rs, pstmt, conn);
}
}
2,更新
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = "";
try {
conn = JdbcUtils.getConnection();
sql = "select * from student";
//默认可以前后滚动
pstmt = conn.prepareStatement(sql,
ResultSet.TYPE_SCROLL_INSENSITIVE, //不敏感,对数据库数据更改在记录集中不反应
ResultSet.CONCUR_UPDATABLE); //可以通过记录集修改数据
rs = pstmt.executeQuery();
System.out.println("修改前数据:");
JdbcUtils.printResult(rs);
rs.absolute(1); //游标定位到第一行数据
rs.updateString(2,"xiugai"); //修改第一行的姓名列
rs.updateInt(3,100); //修改第一行的年龄列
rs.updateRow(); //更新数据库中的数据
rs = pstmt.executeQuery();
System.out.println("\n\n通过记录集修改数据后重新查询数据并输出:");
JdbcUtils.printResult(rs);
} catch(Exception ex) {
ex.printStackTrace();
} finally {
JdbcUtils.free(rs, pstmt, conn);
}
}
3,删除
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = "";
try {
conn = JdbcUtils.getConnection();
sql = "select * from student";
//默认可以前后滚动
pstmt = conn.prepareStatement(sql,
ResultSet.TYPE_SCROLL_INSENSITIVE, //不敏感,对数据库数据更改在记录集中不反应
ResultSet.CONCUR_UPDATABLE); //可以通过记录集修改数据
rs = pstmt.executeQuery();
System.out.println("删除前数据:");
JdbcUtils.printResult(rs);
rs.absolute(3); //游标定位到第一行数据
rs.deleteRow(); //删除游标指定行的数据
rs = pstmt.executeQuery();
System.out.println("\n\n通过记录集删除数据后重新查询数据并输出:");
JdbcUtils.printResult(rs);
} catch(Exception ex) {
ex.printStackTrace();
} finally {
JdbcUtils.free(rs, pstmt, conn);
}
}
四,批量更新
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstmt = null;
CallableStatement cstmt = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
conn.setAutoCommit(false); // 设置事务非自动提交
pstmt = conn.prepareStatement("select * from student");
rs = pstmt.executeQuery();
conn.commit(); // 主动提交事务
System.out.println("callablestatement批量添加前:");
JdbcUtils.printResult(rs);
// 批量更新
cstmt = conn.prepareCall("{call proc_insert_test(?,?)}");
cstmt.setString(1, "xiaoming1");
cstmt.setInt(2, 33);
cstmt.addBatch();
cstmt.setString(1, "xiaoming2");
cstmt.setInt(2, 13);
cstmt.addBatch();
int count[] = cstmt.executeBatch();
conn.commit(); // 主动提交事务
System.out.println("callablestatement批量更新返回值:");
for (int i = 0; i < count.length; i++) {
System.out.print(count[i] + ",\t");
}
System.out.println();
pstmt = conn.prepareStatement("select * from student");
rs = pstmt.executeQuery();
conn.commit(); // 主动提交事务
System.out.println("callablestatement批量添加后:");
JdbcUtils.printResult(rs);
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
if (pstmt != null) {
pstmt.close();
}
if (cstmt != null) {
cstmt.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
二,可滚动行,定位
ublic class TestJDBC3 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = "";
try {
conn = JdbcUtils.getConnection();
sql = "select * from student";
// 默认可以前后滚动
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
JdbcUtils.printResult(rs);
rs.absolute(3); // 将指针指向第三行
System.out.println("\n\n移动指针后重新输出:");
JdbcUtils.printResult(rs);
rs.last(); // 将指针指向最后一行
System.out.println("\n\n移动指针后重新输出:");
System.out.println("id\tname\tage");
do {
System.out.print(rs.getInt(1) + "\t");
System.out.print(rs.getString(2) + "\t");
System.out.print(rs.getInt(3) + "\n");
} while (rs.previous()); // 将指针从最后一行逐步向第一行移动
} catch (Exception ex) {
ex.printStackTrace();
} finally {
JdbcUtils.free(rs, pstmt, conn);
}
}
}
三,通过结果集增删改查
1,增加
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = "";
try {
conn = JdbcUtils.getConnection();
sql = "select * from student";
//默认可以前后滚动
pstmt = conn.prepareStatement(sql,
ResultSet.TYPE_SCROLL_INSENSITIVE, //不敏感,对数据库数据更改在记录集中不反应
ResultSet.CONCUR_UPDATABLE); //可以通过记录集修改数据
rs = pstmt.executeQuery();
System.out.println("添加前数据:");
JdbcUtils.printResult(rs);
rs.moveToInsertRow(); //游标定位到插入行(其实是个缓冲区)
rs.updateString("name","tianjia"); //为姓名列赋值
rs.updateInt(3,200); //为年龄列赋值
rs.insertRow(); //将新添加的数据写入数据库,id自动增长
rs = pstmt.executeQuery();
System.out.println("\n\n通过记录集添加数据后重新查询数据并输出:");
JdbcUtils.printResult(rs);
} catch(Exception ex) {
ex.printStackTrace();
} finally {
JdbcUtils.free(rs, pstmt, conn);
}
}
2,更新
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = "";
try {
conn = JdbcUtils.getConnection();
sql = "select * from student";
//默认可以前后滚动
pstmt = conn.prepareStatement(sql,
ResultSet.TYPE_SCROLL_INSENSITIVE, //不敏感,对数据库数据更改在记录集中不反应
ResultSet.CONCUR_UPDATABLE); //可以通过记录集修改数据
rs = pstmt.executeQuery();
System.out.println("修改前数据:");
JdbcUtils.printResult(rs);
rs.absolute(1); //游标定位到第一行数据
rs.updateString(2,"xiugai"); //修改第一行的姓名列
rs.updateInt(3,100); //修改第一行的年龄列
rs.updateRow(); //更新数据库中的数据
rs = pstmt.executeQuery();
System.out.println("\n\n通过记录集修改数据后重新查询数据并输出:");
JdbcUtils.printResult(rs);
} catch(Exception ex) {
ex.printStackTrace();
} finally {
JdbcUtils.free(rs, pstmt, conn);
}
}
3,删除
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = "";
try {
conn = JdbcUtils.getConnection();
sql = "select * from student";
//默认可以前后滚动
pstmt = conn.prepareStatement(sql,
ResultSet.TYPE_SCROLL_INSENSITIVE, //不敏感,对数据库数据更改在记录集中不反应
ResultSet.CONCUR_UPDATABLE); //可以通过记录集修改数据
rs = pstmt.executeQuery();
System.out.println("删除前数据:");
JdbcUtils.printResult(rs);
rs.absolute(3); //游标定位到第一行数据
rs.deleteRow(); //删除游标指定行的数据
rs = pstmt.executeQuery();
System.out.println("\n\n通过记录集删除数据后重新查询数据并输出:");
JdbcUtils.printResult(rs);
} catch(Exception ex) {
ex.printStackTrace();
} finally {
JdbcUtils.free(rs, pstmt, conn);
}
}
四,批量更新
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstmt = null;
CallableStatement cstmt = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
conn.setAutoCommit(false); // 设置事务非自动提交
pstmt = conn.prepareStatement("select * from student");
rs = pstmt.executeQuery();
conn.commit(); // 主动提交事务
System.out.println("callablestatement批量添加前:");
JdbcUtils.printResult(rs);
// 批量更新
cstmt = conn.prepareCall("{call proc_insert_test(?,?)}");
cstmt.setString(1, "xiaoming1");
cstmt.setInt(2, 33);
cstmt.addBatch();
cstmt.setString(1, "xiaoming2");
cstmt.setInt(2, 13);
cstmt.addBatch();
int count[] = cstmt.executeBatch();
conn.commit(); // 主动提交事务
System.out.println("callablestatement批量更新返回值:");
for (int i = 0; i < count.length; i++) {
System.out.print(count[i] + ",\t");
}
System.out.println();
pstmt = conn.prepareStatement("select * from student");
rs = pstmt.executeQuery();
conn.commit(); // 主动提交事务
System.out.println("callablestatement批量添加后:");
JdbcUtils.printResult(rs);
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
if (pstmt != null) {
pstmt.close();
}
if (cstmt != null) {
cstmt.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}