数据库实验实现

向右看齐 2023-06-16 13:53 4阅读 0赞

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;
            PreparedStatement pstmt3 = null;
            PreparedStatement pstmt4 = null;
            try {
                //1.获取连接
                conn = JDBCUtils.getConnection();
                //开启事务
                conn.setAutoCommit(false);
    
                //2.定义sql
                
                String sql1 = "select *from account";
                String sql2 = "update account set balance = balance - ? where id = ?";
            
                String sql3 = "update account set balance = balance + ? where id = ?";
    
                //3.获取执行sql对象
    
                pstmt1 = conn.prepareStatement(sql2);
                pstmt2 = conn.prepareStatement(sql3);
                pstmt3 = conn.prepareStatement(sql1);
    
                //4. 设置参数
                pstmt1.setDouble(1,500);
                pstmt1.setInt(2,1);
    
                pstmt2.setDouble(1,500);
                pstmt2.setInt(2,2);
                //5.执行sql
                ResultSet resultSet = pstmt3.executeQuery();
                while(resultSet.next()){
    
                    //获取数据
                    int id = resultSet.getInt(1);
                    String name = resultSet.getString("name");
                    double balance = resultSet.getDouble(3);
    
                    System.out.println(id + "---" + name + "---" + balance);
                }
                //执行更改操作
                pstmt1.executeUpdate();
                pstmt2.executeUpdate();
                ResultSet resultSet1 = pstmt3.executeQuery();
                System.out.println("改变后的结果：");
                while(resultSet1.next()){
    
                    //获取数据
                    //6.2 获取数据
                    int id = resultSet1.getInt(1);
                    String name = resultSet1.getString("name");
                    double balance = resultSet1.getDouble(3);
    
                    System.out.println(id + "---" + name + "---" + balance);
                }
                // 手动制造异常
    
    
                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);
            }
        }
    }