4.28 JDBC作业

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

使用info 中的 emp表

1 使用DBCP 对表进行增删改查

查询

 增加 成功返回 1

修改成功

 报错是因为 已经增加信息  并且编号为1015的  为主键 只能唯一

删除 job——id为4 的员工 已经删除

package com.qiku.yrc.JDBCday02;

import java.sql.*;

public class DBCPTest {
    /** 测试DBCP连接池 * */
    public static void main(String[] args) throws SQLException {
        //1.从DBCP连接池中拿到连接
        Connection conn = DBCPUtils.getConnection();
        //2.获取Statement对象
        Statement statement = conn.createStatement();
        //3.查询所有员工的姓名
        String sql = "select ename,id ,mgr,salary from emp";
        String sql2="insert into emp values('1015','菜需捆','4','1004','2022-4-28','2500','250','20')";
        String sql3= "update  emp set ename='两年半' where id='1015'";
        //删除 工号为4 的员工
        String sql4= "delete  from emp  where job_id='4'";
        int i3 =statement.executeUpdate(sql4);
        System.out.println("删除成功"+i3);

//        int i2 =statement.executeUpdate(sql3);
//        System.out.println("修改成功："+i2);
//        int i =statement.executeUpdate(sql2);
//        System.out.println("增加成功："+i);

        ResultSet resultSet = statement.executeQuery(sql);
        //4.处理结果集
        while(resultSet.next()){
            String ename = resultSet.getString("ename");
            String id = resultSet.getString("id");
            String mgr = resultSet.getString("mgr");
            String salary = resultSet.getString("salary");
            System.out.println("姓名: " + ename+
                    "   id: " + id
            +"   编号: " + mgr
            +"   薪资: " + salary);
        }
        //5.释放资源
        DBCPUtils.close(conn,statement,resultSet);
        JDBCUtils.close(conn,statement);

    }
}

   2   C3P0 进行增删改查 

查询 id 为1004 的信息

package com.qiku.yrc.JDBCday02;

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

public class C3P0Select
{
    public static void main(String[] args) throws SQLException {
        Connection conn=C3P0Utils.getConnection();
        PreparedStatement ps= conn.prepareStatement("select * from emp where id=?");
        ps.setInt(1,1004);

        ResultSet rs = ps.executeQuery();
        while (rs.next()){
            System.out.println(rs.getString("ename"));
            System.out.println(rs.getInt("job_id"));
            System.out.println(rs.getInt("salary"));
            System.out.println(rs.getInt("bonus"));
        }
        C3P0Utils.close(conn,ps,rs);
    }
}

增加信息  

package com.qiku.yrc.JDBCday02;

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

public class C3P0Insert {
    public static void main(String[] args) throws SQLException {
        Connection conn=C3P0Utils.getConnection();
        PreparedStatement ps= conn.prepareStatement("insert  into emp " +
                "values('1017','橙留香','4','1004','2022-4-28','22500','250','20')");
        int i = ps.executeUpdate();
        System.out.println(i);
        C3P0Utils.close(conn,ps);
    }

}

 

 修改 *** 作 

package com.qiku.yrc.JDBCday02;

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

public class C3P0Update {

        public static void main(String[] args) throws SQLException {
            Connection conn=C3P0Utils.getConnection();
            PreparedStatement ps= conn.prepareStatement("update  emp set ename='嗨嗨嗨' where id=1017");
            int i=ps.executeUpdate();
            System.out.println("修改成功"+i);

//            ps.setString(1,"嗨嗨嗨");
//            ps.setInt(2,1017);
//            ResultSet rs=ps.executeQuery();

            System.out.println(i);
            C3P0Utils.close(conn,ps);
        }

    }

 删除 刘备  

package com.qiku.yrc.JDBCday02;

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

public class C3P0Delete {
    public static void main(String[] args) throws SQLException {
        Connection conn=C3P0Utils.getConnection();
        //删除刘备的所有信息
        PreparedStatement ps= conn.prepareStatement("delete  from emp where ename='刘备'");
        int i = ps.executeUpdate();
        System.out.println(i);
        C3P0Utils.close(conn,ps);
    }

}