4.28作业

1.使用DBCP连接池，对数据库的一张表进行 增删改查操作

package com.qiku.zy4y28;

import com.qiku.day02.DBCPUtils;

import java.sql.*;

public class zuoye01 {
    //1 使用DBCP连接池，对数据库的一张表进行 增删改查操作，并测试事务，如果发生异常事务回滚（截图展示结果）
    public static void main(String[] args) throws SQLException {
        Connection conn = DBCPUtils.getConnection();
        Statement statement = conn.createStatement();
        //增加
//       PreparedStatement ps = conn.prepareStatement("insert into account values(null , ? , ?)");
//       ps.setString(1 , "许乐");
//       ps.setString( 2, "1000");
//       ps.executeUpdate();
        //删除
//      int i = statement.executeUpdate("delete from account where id = 7");
        //改
//      PreparedStatement ps = conn.prepareStatement("update account set money = money + ? where name = ?");

        //1 韩立 -五百
//       ps.setInt(1, -500);
//       ps.setString(2, "韩立");
//       ps.executeUpdate();
        //查询
        PreparedStatement ps = conn.prepareStatement("select * from account");
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            String name = rs.getString("name");
            String money = rs.getString("money");
            System.out.println("姓名：" + name + "，存款：" + money);
        }

        //释放资源
        DBCPUtils.close(conn, ps, rs);
    }
}

 

 

 

 模拟事务 

package com.qiku.zy4y28;


import com.qiku.day02.DBCPUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;

public class DBUPhuigan {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            //1 获取链接
            conn = DBCPUtils.getConnection();
            //2 开启事务
            conn.setAutoCommit(false);
            //3 获取预处理对象 并模拟转账
            ps = conn.prepareStatement("update account set money = money + ? where name = ?");

            //1 韩立 加五百
            ps.setInt(1, 500);
            ps.setString(2, "韩立");
            ps.executeUpdate();
            //模拟异常
 //           int num = 5 / 0;

            //秦羽 减五百
            ps.setInt(1, -500);
            ps.setString(2, "秦羽");
            ps.executeUpdate();

            //4 提交事务
            DBCPUtils.commit(conn);
            System.out.println("转账成功");
        } catch (Exception e) {
            e.printStackTrace();
            //回滚事务
            DBCPUtils.rollback(conn);
        } finally {
            DBCPUtils.close(conn, ps);
        }
    }
}

 2  C3P0连接池，对数据库的一张表进行 增删改查操作，并测试事务，如果发生异常事务回滚（截图展示结果）

package com.qiku.zy4y28;

import com.qiku.day02.C3P0Utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class zuoye02 {
    //1 C3P0连接池，对数据库的一张表进行 增删改查操作，并测试事务，如果发生异常事务回滚（截图展示结果）
    public static void main(String[] args) throws SQLException {
        Connection conn = C3P0Utils.getConnection();
        PreparedStatement ps =
                conn.prepareStatement("select * from employee where salary between ? and ?");
        ps.setInt(1 ,3000);
        ps.setInt(2, 5000);
        ResultSet rs = ps.executeQuery();//执行
        while(rs.next()){
            System.out.println(rs.getString("ename"));
        }
        C3P0Utils.close(conn,ps,rs);
    }
}

 

事务模拟并发生回滚

package com.qiku.zy4y28;

import com.qiku.day02.C3P0Utils;


import java.sql.Connection;
import java.sql.PreparedStatement;

public class shiwuhuigun {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            //1 获取链接
            conn = C3P0Utils.getConnection();
            //2 开启事务
            conn.setAutoCommit(false);
            //3 获取预处理对象 并模拟转账
            ps = conn.prepareStatement("update account set money = money + ? where name = ?");

            //1 韩立 加五百
            ps.setInt(1, 500);
            ps.setString(2, "韩立");
            ps.executeUpdate();
            //模拟异常
            int num = 5 / 0;

            //秦羽 减五百
            ps.setInt(1, -500);
            ps.setString(2, "秦羽");
            ps.executeUpdate();

            //4 提交事务
            C3P0Utils.commit(conn);
            System.out.println("转账成功");
        } catch (Exception e) {
            e.printStackTrace();
            //回滚事务
            C3P0Utils.rollback(conn);
        } finally {
            C3P0Utils.close(conn, ps);
        }
    }
}