sql练习 使用储存池

分别使用DBCP和C3P0连接池，对数据库的一张表进行 增删改查操作，并测试事务，如果发生异常事务回滚
DBCP

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

public class DBCPTest {
    public static void main(String[] args) throws SQLException {
        //增删改查 测试事务，如果发生异常事务回滚
        Connection conn = DBCPUtils.getConnection();
        Statement st = conn.createStatement();
        //删除 user 表中 id 为 1 的数据
        String sql = "delete from user where id = 1";
        int i = st.executeUpdate(sql);
        DBCPUtils.close(conn,st);
    }
}

表中 为 id 为1 的数据被删除了

    @Test
    public void Insert() throws SQLException {
        // 插入
        Connection conn = DBCPUtils.getConnection();
        Statement st = conn.createStatement();
        //插入 id 为 1 的数据
        String sql = "insert into user values (1,'周武王','F',56,12,'1-1-1',990)";
        st.executeUpdate(sql);
        DBCPUtils.close(conn,st);
    }
}

插入成功

    @Test
    public void testUpdate() throws SQLException {
        Connection conn = DBCPUtils.getConnection();
        Statement st = conn.createStatement();
        //修改id2 的名字
        String sql = "update user set name = '楚庄王' where id = 2 ";
        st.executeUpdate(sql);
        DBCPUtils.close(conn, st);
    }

    @Test
    public void Select() throws SQLException {
        Connection conn = DBCPUtils.getConnection();
        Statement st = conn.createStatement();
        //查询金钱小于10的用户id name money
        String sql = "select id,name,money from user where money < 10";
        ResultSet rs = st.executeQuery(sql);
        while (rs.next()){
            String id = rs.getString("id");
            String name = rs.getString("name");
            String money = rs.getString("money");
            System.out.println("id: "+id+",\t姓名: "+name+",\t金钱: "+money);
        }
        DBCPUtils.close(conn,st ,rs);
    }

//测试事务
    public static void Transaction() {
        Connection conn = null;
        PreparedStatement ps = null;
        Scanner sc=null;
        try {
            conn = DBCPUtils.getConnection();
            //开启事务
            conn.setAutoCommit(false);
            ps = conn.prepareStatement("insert into dogs values (null,?,?)");
             sc = new Scanner(System.in);
            int i = 0;
            while (i<3){
                i++;
                System.out.println("请输入狗狗的姓名：");
                String name = sc.next();
                System.out.println("请输入狗狗的年龄：");
                int age = sc.nextInt();
                ps.setString(1, name);
                ps.setInt(2, age);
                ps.executeUpdate();
            }
            DBCPUtils.commit(conn);
            System.out.println("新增成功..");
        } catch (SQLException e) {
            e.printStackTrace();
            //回滚事务
            DBCPUtils.rollback(conn);
        }finally {
            DBCPUtils.close(conn,ps);
            assert sc != null;
            sc.close();
        }
    }

插入成功

插入输入数据不是int类型的 发生异常 插入失败 自动回滚 对原有数据没有产生影响

C3B0

package com.qiku.day02.hw;

import org.junit.Test;

import java.sql.*;
import java.util.Scanner;

public class C3B0Test {
    public static void main(String[] args){
        Transaction();
    }

    @Test
    public void Del() throws SQLException {
        Connection conn = C3P0Utils.getConnection();
        Statement st = conn.createStatement();
        //删除 dogs 表中 id 为 1 的数据
        String sql = "delete from dogs where id = 1";
        int i = st.executeUpdate(sql);
        C3P0Utils.close(conn, st);
    }

    @Test
    public void Insert() throws SQLException {
        // 插入

        Connection conn = C3P0Utils.getConnection();
        Statement st = conn.createStatement();
        //插入 id 为 1 的数据
        String sql = "insert into dogs values (1,'周武王',3)";
        st.executeUpdate(sql);
        C3P0Utils.close(conn, st);
    }

    @Test
    public void testUpdate() throws SQLException {
        Connection conn = C3P0Utils.getConnection();
        Statement st = conn.createStatement();
        //修改id2 的名字
        String sql = "update dogs set dname = '楚庄王' where id = 2 ";
        st.executeUpdate(sql);
        C3P0Utils.close(conn, st);
    }

    @Test
    public void Select() throws SQLException {
        Connection conn = C3P0Utils.getConnection();

        Statement st = conn.createStatement();
        //查询dogs表
        String sql = "select * from dogs";
        ResultSet rs = st.executeQuery(sql);
        while (rs.next()) {
            String id = rs.getString("id");
            String dname = rs.getString("dname");
            String age = rs.getString("age");
            System.out.println("id: " + id + ",\t狗名: " + dname + ",\t狗龄: " + age);
        }
        C3P0Utils.close(conn, st, rs);
    }


    public static void Transaction() {
        Connection conn = null;
        PreparedStatement ps = null;
        Scanner sc=null;
        try {
            conn = C3P0Utils.getConnection();
            //开启事务
            conn.setAutoCommit(false);
            ps = conn.prepareStatement("insert into dogs values (null,?,?)");
            sc = new Scanner(System.in);
            int i = 0;
            while (i<3){
                i++;
                System.out.println("请输入狗狗的姓名：");
                String name = sc.next();
                System.out.println("请输入狗狗的年龄：");
                int age = sc.nextInt();
                ps.setString(1, name);
                ps.setInt(2, age);
                ps.executeUpdate();
            }
            C3P0Utils.commit(conn);
            System.out.println("新增成功..");
        } catch (SQLException e) {
            e.printStackTrace();
            //回滚事务
            C3P0Utils.rollback(conn);
        }finally {
            C3P0Utils.close(conn,ps);
            assert sc != null;
            sc.close();
        }
    }
}

B3C0