4月28日作业

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

（1）使用DBCP连接池：

import javax.sql.RowSet;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;


/**
 * DBCP连接池工具类
 */
public class DBCPUtils {
    //1.定义常量 保存数据库连接的相关信息
    public static final String DRIVER_NAME = "com.mysql.jdbc.Driver";
    public static final String URL = "jdbc:mysql://localhost:3306/db5";
    public static final String USERNAME = "root";
    public static final String PASSWORD = "991219";
    //2.创建连接池对象 (有DBCP提供的实现类)
    public static BasicDataSource dataSource = new BasicDataSource();

    //3.使用静态代码块进行配置
    static {
        dataSource.setDriverClassName(DRIVER_NAME);
        dataSource.setUrl(URL);
        dataSource.setUsername(USERNAME);
        dataSource.setPassword(PASSWORD);
    }

    //4.获取连接的方法
    public static Connection getConnection() throws Exception {
        //从连接池中获取连接
        Connection connection = dataSource.getConnection();
        return connection;
    }

    //5.释放资源方法
    public static void close(Connection con, Statement statement) {
        if (con != null && statement != null) {
            try {
                statement.close();
                //归还连接
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    public static void close (Connection con, Statement statement, ResultSet resultSet) {
        if (con != null && statement != null && resultSet != null) {
            try {
                resultSet.close();
                statement.close();
                //归还连接
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

}

（2）增删改查：

查：

package com.qiku.day02;

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

public class DBCPTest {
    public static void main(String[] args) throws Exception {
        //所有员工信息
        //获取链接
        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);
    }

}

 

增：

mport java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class ZYX {
    public static void main(String[] args) throws Exception {
        Connection conn = DBCPUtils.getConnection();
        Statement statement = conn.createStatement();
        PreparedStatement ps = conn.prepareStatement("insert into employee values(null,?,?,?,?,?)");
        ps.setString(1, "路飞");
        ps.setInt(2, 20);
        ps.setString(3, "男");
        ps.setInt(4, 2000);
        ps.setString(5, "1999-1-1");
        ps.executeUpdate();
        DBCPUtils.close(conn, ps);

    }
}

 

删：

package com.qiku.day02;

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

public class ZYX {
    public static void main(String[] args) throws Exception {
        Connection conn = DBCPUtils.getConnection();
        Statement statement = conn.createStatement();
        int i = statement.executeUpdate("delete from employee where eid =5");
        DBCPUtils.close(conn,statement);

    }
}

 

改：

package com.qiku.day02;

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

public class ZYX {
    public static void main(String[] args) throws Exception {
        Connection conn = DBCPUtils.getConnection();
        Statement statement = conn.createStatement();
        String sql = "update employee set ename = '哈哈哈哈' ,age = '100' where eid = 1";
        int i = statement.executeUpdate(sql);
        System.out.println(i);
       DBCPUtils.close(conn,statement);
    }
}

 （3）DBCP对事务进行 *** 作：

package com.qiku.day02;

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

public class ZYX {
    public static void main(String[] args) throws Exception {
        Connection conn=null;
        PreparedStatement ps =null;
        try{
            conn = JDBCUtils.getConnection();
            conn.setAutoCommit(false);
            ps = conn.prepareStatement ("update account set money = money + ? where name= ?");
            ps.setInt(1,-500);
            ps.setString(2,"tom");
            ps.executeUpdate();
            //制造回滚条件  int i= 10 / 0;
            ps.setInt(1,500);
            ps.setString(2,"jack");
            ps.executeUpdate();
            JDBCUtils.commit(conn);
            System.out.println("转账成功！！！");
        }catch (Exception e){
            e.printStackTrace();
            JDBCUtils.rollback(conn);
        }finally {
            JDBCUtils.close(conn,ps);
        }
    }
}

 

 

 使用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 {
    //1 创建连接池对象
    //使用 默认配置
    // public static ComboPooledDataSource dataSource = new ComboPooledDataSource();
    //使用 指定配置
    public static ComboPooledDataSource dataSource = new ComboPooledDataSource("mysql");

    //获取链接
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    //释放资源
    public static void close(Connection con, Statement statement) {
        if (con != null && statement != null) {
            try {
                statement.close();
                //归还连接
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void close(Connection con, Statement statement, ResultSet resultSet) {
        if (con != null && statement != null && resultSet != null) {
            try {
                resultSet.close();
                statement.close();
                //归还连接
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

 

查：

package com.qiku.day02;

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

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);
    }
}

 增：

package com.qiku.day02;

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

public class ZYX {
    public static void main(String[] args) throws Exception {
        Connection conn = C3P0Utils.getConnection();
        Statement statement = conn.createStatement();
        String sql = "insert into employee values (null,'山治','12','男','20000','1999-1-1')";
        int i = statement.executeUpdate(sql);
        C3P0Utils.close(conn,statement);
    }
}

 改：

package com.qiku.day02;

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

public class ZYX {
    public static void main(String[] args) throws Exception {
        Connection conn = C3P0Utils.getConnection();
        Statement statement = conn.createStatement();
        String sql = "update employee set ename = '呵呵呵' where eid = 2";
        int i = statement.executeUpdate(sql);
        C3P0Utils.close(conn,statement);
    }
}

 删：

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

public class ZYX {
    public static void main(String[] args) throws Exception {
        Connection conn = C3P0Utils.getConnection();
        Statement statement = conn.createStatement();
        int i = statement.executeUpdate("delete from employee where eid =6");
        C3P0Utils.close(conn,statement);
    }
}

 （3）C3P0对事务进行 *** 作：

package com.qiku.day02;

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

public class ZYX {
    public static void main(String[] args) throws Exception {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        JDBCUtils c3p0Utils = null;
        try {
            connection = c3p0Utils.getConnection();
            preparedStatement = connection.prepareStatement("select *from account where eid in (1,2)");
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                String ename = resultSet.getString("ename");
                String money = resultSet.getString("money");
            }
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement("update account set money = money+? where ename = ?");
            preparedStatement.setInt(1, -500);
            preparedStatement.setString(2, "tom");
            preparedStatement.executeUpdate();
//            //模拟异常
//            int num = 7/0;
            preparedStatement.setInt(1, 500);
            preparedStatement.setString(2, "jack");
            preparedStatement.executeUpdate();
            //提交事务
            c3p0Utils.commit(connection);
            //转账后查询
            preparedStatement = connection.prepareStatement("select *from account where eid in (1,2)");
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                String ename = resultSet.getString("ename");
                String money = resultSet.getString("money");
            }
        } catch (Exception e) {
            e.printStackTrace();
            //回滚事务
            c3p0Utils.rollback(connection);
        } finally {
            //释放资源
            c3p0Utils.close(connection, preparedStatement, resultSet);
        }
    }
}