c3p0实现对数据库的增删改查（JDBC加强版）

旧城等待， 2022-01-26 00:01 172阅读 0赞

1.先将jar包导好（其中第二个和第三个不用导）：

![2019052716340723.png][]

2.创建一个c3p0带.xml的文件且放在src目录下：

![2019052716411061.png][]

3.先创建一个学生类：

public class Student {
        private  int id;
        private String name;
        private String sex;
        private String brithday;
    
        public Student() {
        }
    
        public Student(int id, String name, String sex, String brithday) {
            this.id = id;
            this.name = name;
            this.sex = sex;
            this.brithday = brithday;
        }
    
        public int getId() {
            return id;
        }
    
        public void setId(int id) {
            this.id = id;
        }
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    
        public String getSex() {
            return sex;
        }
    
        public void setSex(String sex) {
            this.sex = sex;
        }
    
        public String getBrithday() {
            return brithday;
        }
    
        public void setBrithday(String brithday) {
            this.brithday = brithday;
        }
    
        @Override
        public String toString() {
            return "Student{" +
                    "name='" + name + '\'' +
                    ", sex='" + sex + '\'' +
                    ", brithday='" + brithday + '\'' +
                    '}';
        }
    }

4.创建C3P0Util

public class C3P0Util {
        //得到一个数据源
        private static ComboPooledDataSource dataSource = new ComboPooledDataSource();
    
        public static ComboPooledDataSource getDataSource() {
            return dataSource;
        }
        //从数据源中得到一个连接对象
        public static Connection getConnection() {
            try {
                return dataSource.getConnection();
            } catch (SQLException e) {
                throw new ExceptionInInitializerError("服务器错误");
            }
        }
    
        //关闭资源
        public static void release(ResultSet rs, Statement stmt, Connection conn) {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

5.创建一个ManagerThreadLocal类来保证线程安全

public class ManagerThreadLocal {
        private static ThreadLocal<Connection> tl = new ThreadLocal<>();
    
        //得到一个连接
        public static Connection getConnection(){
            Connection conn = tl.get(); //从当前线程中取出一个连接
            if (conn == null){
                conn = C3P0Util.getConnection();    //从池中取一个
                tl.set(conn);   //把conn对象放入到当前对象线程中
            }
            return conn;
        }
    
        //开始事务
        public static void startTransacation(){
            try {
                Connection conn = getConnection();
                conn.setAutoCommit(false);   //从当前线程对象取出连接，并开始事务
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    
        public static void commit(){
            try {
                getConnection().commit();   //提交事务
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    
        public static void rollback(){
            try {
                getConnection().rollback(); //回滚事务
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    
        public static void close(){
            try {
                getConnection().close();    //把连接放回池中
                tl.remove();    //把当前线程对象中的conn移除
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

6.创建一个实现增删改查功能的类StudentDAO

public class StudentDAO {
        /**
         * 增加数据库里面一条学生对象信息的方法
         *
         * @param s
         * @return
         */
        public static void insert(Student s){
            QueryRunner qr = new QueryRunner();
            try {
                qr.update(ManagerThreadLocal.getConnection(),"INSERT INTO `student`(`id`,`name`, `sex`, `brithday`) VALUES (?,?,?,?)",
                        s.getId(),s.getName(),s.getSex(),s.getBrithday());
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                ManagerThreadLocal.close();
            }
        }
    
        /**
         * 根据数据库id改名字性别和生日的方法
         *
         * @param s
         * @return
         * @throws
         */
        public static void update(Student s) {
            QueryRunner qr = new QueryRunner();
            try {
                qr.update(ManagerThreadLocal.getConnection(),"UPDATE `student` SET `name` = ?, `sex` = ?, `brithday` = ? WHERE `id` = ?",
                        s.getName(),s.getSex(),s.getBrithday(),s.getId());
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                ManagerThreadLocal.close();
            }
        }
    
        /**
         * 根据对象名删除数据库对象信息的方法
         *
         * @param
         * @return
         * @throws SQLException
         */
        public static void delete(String name) {
            QueryRunner qr = new QueryRunner();
            try {
                qr.update(ManagerThreadLocal.getConnection(),"DELETE FROM `student` WHERE `name` = ?",name);
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                ManagerThreadLocal.close();
            }
        }
    
        /**
         * 查询表格的方法
         *
         * @throws SQLException
         */
        public static List<Student> select(){
            QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
            List<Student> students = null;
            try {
                students = qr.query("SELECT * FROM `student`;", new BeanListHandler<>(Student.class));
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                ManagerThreadLocal.close();
            }
            return students;
        }
    }

7.测试类（可以实现选择菜单的一个测试类，可以自己根据提示来）

public class Test {
        private static int op = 0;
        public static void main(String[] args) throws SQLException {
            while (showMenu()){
                switch (op) {
                    case 0:
                        add();
                        break;
                    case 1:
                        remove();
                        break;
                    case 2:
                        change();
                        break;
                    case 3:
                        search();
                        break;
                    case 4:
                        return;
                }
            }
        }
    
        public static boolean showMenu(){
            Scanner sc=new Scanner(System.in);
            System.out.println("请输入您要对表格的操作按键（0-4）");
            System.out.println("0:增添对象");
            System.out.println("1:删除对象");
            System.out.println("2:改变对象属性");
            System.out.println("3:查询表格");
            System.out.println("4:退出选项");
            op = sc.nextInt();
            if (op>=0 && op<=4){
                return true;
            }else {
                System.out.println("请根据条件重新输入符合条件的数值");
                return false;
            }
        }
    
        public static void search() {
            List<Student> Students = StudentDAO.select();
            for (Student s : Students)
                System.out.printf("%d\t%s\t%s\t%s\n",s.getId(), s.getName(), s.getSex(), s.getBrithday());
        }
    
        public static void add(){
            System.out.println("请分别输入ID,姓名，性别，生日");
            Scanner sc=new Scanner(System.in);
            int id = sc.nextInt();
            String name = sc.next();
            String sex = sc.next();
            String bri = sc.next();
            Student s = new Student(id,name,sex,bri);
            StudentDAO.insert(s);
        }
    
        public static void remove(){
            System.out.println("请输入您要删除对象的姓名");
            Scanner sc=new Scanner(System.in);
            String name = sc.next();
            StudentDAO.delete(name);
        }
    
        public static void change(){
            System.out.println("请分别输入ID,姓名，性别，生日");
            Scanner sc=new Scanner(System.in);
            int id = sc.nextInt();
            String name = sc.next();
            String sex = sc.next();
            String bri = sc.next();
            Student s = new Student(id,name,sex,bri);
            StudentDAO.update(s);
        }
    }

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NobGVpXw_size_16_color_FFFFFF_t_70][]

我们可以看到当我们不选择跳出时，我们不用再次运行 ，而是可以继续选择菜单。

[2019052716340723.png]: /images/20220126/0bf323c7d7f84487aaafda141dad63a8.png
[2019052716411061.png]: /images/20220126/ffe8afa3036841fd872fafbc286e1e5d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NobGVpXw_size_16_color_FFFFFF_t_70]: /images/20220126/31de31c369484c5995c4b01c6681f3ff.png