一、请使用jdbc技术实现数据库连接,并且保证在一个事务中对表TEST的一条记录做修改(并保证连接正常关闭),例如:
[TEST]
ID NAME
1 test
2 test1
修改为
[TEST]
ID NAME
1 test2
2 test1
二、请根据上题的继续实现:遍历所有数据库记录,遇到ID=1的记录时,NAME修改为test3后立即提交事务,遇到的ID=2的记录时,先将NAME修改为test4后立即回滚事务将ID=2的记录NAME回复为原值(并保证连接正常关闭)。
代码如下,如有更好的实现,请大家贴出来共同学习.
package com.jdbc.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class MYSQLConn {
public static void main(String[] args) {
Connection con = null;
PreparedStatement pstm = null;
ResultSet rs = null;
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/myuser?useUnicode=true&characterEncoding=UTF-8"
,"root","123456");
System.out.println(con);
/*//新增记录
String insertSQL = "insert into user_info() values (2,'test1',12)";
pstm = con.prepareStatement(insertSQL);
pstm.executeUpdate();*/
/*//一
con.setAutoCommit(false);
String updateSQL = "update user_info set name='" + "test2" + "' where id=1";
pstm = con.prepareStatement(updateSQL);
pstm.executeUpdate();
con.rollback();
con.commit();
con.setAutoCommit(true);*/
//二
String sql = "select * from user_info";
pstm = con.prepareStatement(sql);
rs = pstm.executeQuery();
while(rs.next())
{
int ID = rs.getInt("id");
if (ID == 1) {
con.setAutoCommit(false);
String updateSQL = "update user_info set name='" + "test3" + "' where id=1";
pstm = con.prepareStatement(updateSQL);
pstm.executeUpdate();
// con.rollback();
con.commit();
con.setAutoCommit(true);
} else if (ID == 2) {
con.setAutoCommit(false);
String updateSQL = "update user_info set name='" + "test4" + "' where id=2";
pstm = con.prepareStatement(updateSQL);
pstm.executeUpdate();
con.rollback();
con.commit();
con.setAutoCommit(true);
}
System.out.print(ID + " ");
}
} catch (Exception e) {
e.printStackTrace();
// TODO: handle exception
} finally {
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
System.out.println(con);
}
}