2022-4-28作业

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

先创建一个数据库

#创建数据库 
CREATE DATABASE db6 CHARACTER SET utf8; 
#使用数据库 
USE db6; 
#创建员工表 
CREATE TABLE emp ( 
	eid INT PRIMARY KEY AUTO_INCREMENT , 
	ename VARCHAR (20), -- 员工姓名 
	age INT , -- 员工年龄 
	sex VARCHAR (6), -- 员工性别 
	salary DOUBLE , -- 薪水 
	empdate DATE -- 入职日期 
);

#插入数据 
INSERT INTO emp (eid, ename, age, sex, salary, empdate) VALUES(NULL,'张三',22,'女',4000,'2018-11-12'); 
INSERT INTO emp (eid, ename, age, sex, salary, empdate) VALUES(NULL,'李四',20,'女',5000,'2019-03-14'); 
INSERT INTO emp (eid, ename, age, sex, salary, empdate) VALUES(NULL,'王二',40,'男',6000,'2020-01-01'); 
INSERT INTO emp (eid, ename, age, sex, salary, empdate) VALUES(NULL,'麻子',25,'男',3000,'2017-10-01');

 

 

DBCP

public class zy01 {
    public static final String DRIVER_NAME = "com.mysql.jdbc.Driver";
    public static final String URl = "jdbc:mysql://localhost:3306/db6";
    public static final String USERNAME = "root";
    public static final String PASSWORD = "12343456";
    public static BasicDataSource dataSource = new BasicDataSource();
    static {
        dataSource.setDriverClassName(DRIVER_NAME);
        dataSource.setUrl(URl);
        dataSource.setUsername(USERNAME);
        dataSource.setPassword(PASSWORD);
    }
    public static Connection getConnection() throws SQLException {

        Connection conn = dataSource.getConnection();
        return conn;
    }

    //提供事务回滚的方法
    public static void rollback(Connection conn){
        try {
            conn.rollback();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void close(Connection conn, Statement st){
        if (conn != null && st != null){

            try {
                st.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    public static void close(Connection conn, Statement st, ResultSet rs){
        if (conn != null && st != null && rs != null){

            try {
                rs.close();
                st.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

public class zy01Test {
    public static void main(String[] args)  {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = zy01.getConnection();
            ps = conn.prepareStatement("select * from emp");
            rs = ps.executeQuery();
            while (rs.next()) {
                String name = rs.getString("ename");
                String age = rs.getString("age");
                String salary = rs.getString("salary");
                System.out.println("姓名：" + name + ",年龄：" + age + ",薪资" + salary);

            }
        }catch (Exception e){
            e.printStackTrace();
            zy01.rollback(conn);
        }finally {
            DBCPUtils.close(conn,ps,rs);
        }

    }

}

增

 

@Test
    public void testInsert() throws Exception {
        //1.工具类获取链接
        Connection conn = zy01.getConnection();
        //2.获取 Statement
        Statement statement = conn.createStatement();
        //2.1编写sql
        String sql = "insert into emp values (null,'哈哈','11','女','1000','2022-4-28')";
        //2.2 执行sql
        int i = statement.executeUpdate(sql);
        //3.释放资源
        zy01.close(conn,statement);
    }

删

@Test
    public void testDelete() throws SQLException {
        Connection conn = zy01.getConnection();
        Statement statement = conn.createStatement();
        String sql = "delete from emp where eid = 2 ";
        statement.executeUpdate(sql);
        zy01.close(conn,statement);

    }

 

 改

@Test
    public void testUpDate() throws SQLException {
        Connection conn = zy01.getConnection();
        Statement statement = conn.createStatement();
        String sql ="update emp set ename ='嘿嘿' where eid =3 ";
        int i =statement.executeUpdate(sql);
        System.out.println(i);
        zy01.close(conn,statement);
    }

查

 

@Test
    public void testSelect() throws SQLException {
        Connection conn = zy01.getConnection();
        Statement statement = conn.createStatement();
        String sql = "select * from emp where eid in (1,3,5)";
        ResultSet rs= statement.executeQuery(sql);
        //解析结果集
        while (rs.next()){
            String eid = rs.getString("eid");
            String ename = rs.getString("ename");
            String age = rs.getString("age");
            String sex = rs.getString("sex");
            System.out.println("eid:" + eid +",ename:" +ename +",age:" + age +",sex:" + sex);
        }
        zy01.close(conn,statement,rs);

    }

C3P0 

 我重新建了表employeezy如下，操作一样就不展示了

public class zy02 {
    public static ComboPooledDataSource dataSource = new ComboPooledDataSource("mysql");

    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    public static void rollback(Connection conn){
        try {
            conn.rollback();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    public static void close(Connection conn, Statement st){
        if (conn != null && st != null){

            try {
                st.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    public static void close(Connection conn, Statement st, ResultSet rs){
        if (conn != null && st != null && rs != null){

            try {
                rs.close();
                st.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


}

输出工资在4000-5000的名字 

public class zy02Test {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = zy02.getConnection();
            ps = conn.prepareStatement("select * from employeezy where salary between ? and ?");
            ps.setInt(1,4000);
            ps.setInt(2,5000);
            //执行sql
            rs = ps.executeQuery();
            //获取结果集
            while (rs.next()){
                System.out.println(rs.getString("ename"));
            }

        }catch (Exception e){
            e.printStackTrace();
            zy02.rollback(conn);
        }finally {
            zy02.close(conn,ps,rs);
        }
    }

}

结果：

增：

@Test
    public void testInsert() throws Exception {
        Connection conn = zy02.getConnection();
        Statement statement = conn.createStatement();
        String sql = "insert into employeezy values (null,'呵呵','11','女','7000','2022-4-28')";
        int i = statement.executeUpdate(sql);

        zy02.close(conn,statement);
    }

删：

 @Test
    public void testDelete() throws SQLException {
        Connection conn = zy02.getConnection();
        Statement statement = conn.createStatement();
        String sql = "delete from employeezy where eid = 3 ";
        statement.executeUpdate(sql);
        zy02.close(conn,statement);

    }

 ​​​​​​​​​​​​​​

 改：

@Test
    public void testUpDate() throws SQLException {
        Connection conn = zy02.getConnection();
        Statement statement = conn.createStatement();
        String sql ="update employeezy set ename ='扣扣大有' where eid =4 ";
        int i =statement.executeUpdate(sql);
        System.out.println(i);
        zy02.close(conn,statement);
    }

查：

 

 @Test
    public void testSelect() throws SQLException {
        Connection conn = zy02.getConnection();
        Statement statement = conn.createStatement();
        String sql = "select * from employeezy where eid in (2,4)";
        ResultSet rs= statement.executeQuery(sql);
        //解析结果集
        while (rs.next()){
            String eid = rs.getString("eid");
            String ename = rs.getString("ename");
            String age = rs.getString("age");
            String sex = rs.getString("sex");
            System.out.println("eid:" + eid +",ename:" +ename +",age:" + age +",sex:" + sex);
        }
        zy02.close(conn,statement,rs);

    }