2022-4-28作业

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

package com.qiku.day02;

import org.apache.commons.dbcp.BasicDataSource;

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

public class DBCPUtils {
    public static final String DRIVER_NAME="com.mysql.jdbc.Driver";
    public static final String URL="jdbc:mysql://localhost:3306/db2";
    public static final String USERNAME="root";
    public static final String PASSWORD="root";
 
    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 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 {
                st.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

package com.qiku.day02;

import org.junit.Test;

import java.sql.*;

public class DBCPTest {
    //查找数据
    public static void main(String[] args) throws SQLException {
        Connection conn = DBCPUtils.getConnection();
        PreparedStatement ps =
                conn.prepareStatement("select  * from employee");
        ResultSet 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);
        }
        DBCPUtils.close(conn,ps,rs);
    }
    //增加数据
    @Test
    public void testIncrease() throws SQLException {
        Connection conn = DBCPUtils.getConnection();
        Statement statement=conn.createStatement();
        //2.1 编写sql
        String sql="insert into employee values (NULL,'ww',22,'女',4000,'2018-11-12')";
        //2.2 执行sql
        int i = statement.executeUpdate(sql);
        System.out.println(i);
        //3 释放资源
        JDBCUtils.close(conn,statement);
    }
    //更改数据
    @Test
    public void testUpdate() throws SQLException {
        Connection conn=JDBCUtils.getConnection();
        Statement statement=conn.createStatement();
        String sql="update employee set ename='vv' where eid=1";
        int i=statement.executeUpdate(sql);
        System.out.println(i);
        JDBCUtils.close(conn,statement);
    }
    //删除数据
    @Test
    public void testDelete() throws SQLException {
        Connection conn=JDBCUtils.getConnection();
        Statement statement = conn.createStatement();
        String sql ="delete from employee where eid =1";
        int i = statement.executeUpdate(sql);
        System.out.println(i);
        JDBCUtils.close(conn,statement);
    }

}

增加数据

删除数据

更改数据

查询数据

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

package com.qiku.day02;

import com.mchange.v2.c3p0.ComboPooledDataSource;

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

public class C3P0Utils {
    public static ComboPooledDataSource dataSource=new ComboPooledDataSource("mysql");
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
    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 {
                st.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
    }
}

package com.qiku.day02;

import org.junit.Test;

import java.sql.*;

public class C3P0Test {
    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);
    }

    //增加数据
    @Test
    public void testIncrease() throws SQLException {
        Connection conn = DBCPUtils.getConnection();
        Statement statement = conn.createStatement();
        //2.1 编写sql
        String sql = "insert into employee values (NULL,'ww',22,'女',4000,'2018-11-12')";
        //2.2 执行sql
        int i = statement.executeUpdate(sql);
        System.out.println(i);
        //3 释放资源
        JDBCUtils.close(conn, statement);
    }

    //更改数据
    @Test
    public void testUpdate() throws SQLException {
        Connection conn = JDBCUtils.getConnection();
        Statement statement = conn.createStatement();
        String sql = "update employee set sex='男'  where eid=2";
        int i = statement.executeUpdate(sql);
        System.out.println(i);
        JDBCUtils.close(conn, statement);
    }

    //删除数据
    @Test
    public void testDelete() throws SQLException {
        Connection conn = JDBCUtils.getConnection();
        Statement statement = conn.createStatement();
        String sql = "delete from employee where eid =7";
        int i = statement.executeUpdate(sql);
        System.out.println(i);
        JDBCUtils.close(conn, statement);
    }
}

增加数据

删除数据

更改数据

查询数据