JDBC数据库操作

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();   
}