JDBC提交事务时需要注意commint和rollback;commint可以设置为自动提交或取消自动提交,如:
conn.autocommint(false);
如图:
先结合前面数据查询及更新,给出一段测试代码如下:
//数据库连接类定义
package com.amaker.read;
import java.sql.DriverManager;
import java.sql.SQLException;
import org.junit.Test;
import com.mysql.jdbc.Connection;
public class DButil {
String drive = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://114.215.119.11:3306/test";
String user = "root";
String passwd = "root@Dongqx";
@Test
public Connection defDataConn() throws ClassNotFoundException, SQLException{
Connection DBConn = null;
//加载sql驱动类
System.out.println("===========================");
try{
Class.forName(drive);
}catch (ClassNotFoundException e){
e.printStackTrace();
System.out.println("为找到mysql驱动类");
}
try{
DBConn = (Connection) DriverManager.getConnection(url, user, passwd);
}catch(Exception e){
e.printStackTrace();
System.out.println("数据库链接失败");
}
return DBConn;
}
}
测试代码如下:
package com.amaker.read;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.junit.Test;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;
public class dbCounst {
Connection dataconn= null;
//初始化数据链接
public dbCounst() throws ClassNotFoundException, SQLException {
DButil dbConn = new DButil();
this.dataconn = dbConn.defDataConn();
}
@Test
public void updataDB() throws SQLException{
Statement stat = (Statement) dataconn.createStatement();
String SQL = "INSERT INTO `test`.`users` (`id`, `username`, `passwd`, `email`) VALUES ('5', '5', '5', '5')";
stat.executeUpdate(SQL);
}
@Test
public void showTab(){
try{
Statement Statement = (com.mysql.jdbc.Statement) dataconn.createStatement();
String SQL = "SELECT id, username, passwd,email FROM users";
ResultSet res = Statement.executeQuery(SQL);
while(res.next()){
String id = res.getString(1 );
String name =res.getString("username");
String passwd = res.getString(3);
System.out.println("查询内容为:"+id+" "+name+" "+passwd);
}
dataconn.close();
}catch(SQLException e){
e.printStackTrace();
System.out.println("链接出现异常");
}
}
}
当数据库操作时需要考虑操作失败是的回滚情况即dataconn.rollback();回滚一般使用场景即,当多条语句同时修改时,为防止出错一般在catch处添加rollback指令。
如:
public void updataDB() throws SQLException{
try{
Statement stat = (Statement) dataconn.createStatement();
String SQL = "UPDATE `test`.`users` SET `username`='e' WHERE `id`='5';
";
String SQL = "UPDATE `test`.`users` SET `passwd`='e', `email`='e' WHERE `id`='5';
";
stat.executeUpdate(SQL);
}catch(SQLException e){
System.out.println("数据更新失败");
dataconn.rollback();
}
dataconn.close();
}