数据库实验

import java.sql.*;

public class JDBCUtils {
	    private static String url="jdbc:mysql://localhost:3306/person";
	    private static String user="root";
	    private static String password="root";
	    private static String driver="com.mysql.jdbc.Driver";
	    /**
	     * 文件的读取，只需要读取一次即可拿到这些值。使用静态代码块
	     */
	    static{	
	            // 注册驱动
	            try {
					Class.forName(driver);
				} catch (ClassNotFoundException e) {
					e.printStackTrace();
				}   
	    }
	
	    /**
	     * 获取连接
	     * @return 连接对象
	     */
	    public static Connection getConnection() throws SQLException {
	
	        return DriverManager.getConnection(url, user, password);
	    }
	
	    /**
	     * 释放资源
	     * @param stmt
	     * @param conn
	     */
	    public static void close(Statement stmt,Connection conn){
	        if( stmt != null){
	            try {
	            	
	            	stmt.close();
	            } catch (SQLException e) {
	                e.printStackTrace();
	            }
	        }
	
	        if( conn != null){
	            try {
	                conn.close();
	            } catch (SQLException e) {
	                e.printStackTrace();
	            }
	        }
	    }
	
	    /**
	     * 释放资源
	     * @param stmt
	     * @param conn
	     */
	    public static void close(ResultSet rs,Statement stmt, Connection conn){
	        if( rs != null){
	            try {
	                rs.close();
	            } catch (SQLException e) {
	                e.printStackTrace();
	            }
	        }
	
	        if( stmt != null){
	            try {
	                stmt.close();
	            } catch (SQLException e) {
	                e.printStackTrace();
	            }
	        }
	
	        if( conn != null){
	            try {
	                conn.close();
	            } catch (SQLException e) {
	                e.printStackTrace();
	            }
	        }
	    }
	
	}

 

import java.sql.*;

public class Test {

   public static void main(String[] args) {
       Connection conn = null;
       PreparedStatement pstmt1 = null;
       PreparedStatement pstmt2 = null;

       try {
           //1.获取连接
           conn = JDBCUtils.getConnection();
           //开启事务
           conn.setAutoCommit(false);

           //2.定义sql
           //2.1 张三 - 500
           String sql1 = "update account set balance = balance - ? where id = ?";
           //2.2 李四 + 500
           String sql2 = "update account set balance = balance + ? where id = ?";
           //3.获取执行sql对象
           pstmt1 = conn.prepareStatement(sql1);
           pstmt2 = conn.prepareStatement(sql2);
           //4. 设置参数
           pstmt1.setDouble(1,500);
           pstmt1.setInt(2,1);

           pstmt2.setDouble(1,500);
           pstmt2.setInt(2,2);
           //5.执行sql
           pstmt1.executeUpdate();
           // 手动制造异常
           int i = 3/0;

           pstmt2.executeUpdate();
           //提交事务
           conn.commit();
       } catch (Exception e) {
           //事务回滚
           try {
               if(conn != null) {
                   conn.rollback();
               }
           } catch (SQLException e1) {
               e1.printStackTrace();
           }
           e.printStackTrace();
       }finally {
           JDBCUtils.close(pstmt1,conn);
           JDBCUtils.close(pstmt2,null);
       }
   }
}