使用C3P0进行数据库增删改查操作

一、准备工作

首先导入jar包：
①c3p0-0.9.1.2.jar
②commons-dbutils-1.7.jar
③mysql-connector-java-5.1.19-bin.jar
然后在src中创建c3p0-config.xml

<c3p0-config>
	<default-config>
		<property name="jdbcUrl">jdbc:mysql://localhost:3306/数据库名?useUnicode=true&amp;characterEncoding=utf-8</property>
		<property name="driverClass">com.mysql.jdbc.Driver</property>
		<property name="user">用户名</property>
		<property name="password">密码</property>
		<property name="initialPoolSize">3</property>
		<property name="maxPoolSize">6</property>
		<property name="maxIdleTime">1000</property>
	</default-config>
</c3p0-config>

二、配置

（一）编写C3P0Conn.java

package cn.tx.commons;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class C3P0Conn {
    //数据源
    private static ComboPooledDataSource ds = new ComboPooledDataSource();
    public static Connection getConnection(){
        Connection conn = null;
        try {
            //获取到连接
            conn = ds.getConnection();
            System.out.println("C3P0连接成功！！！");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
    //关闭资源
    public static void closeConntion(Connection conn){
        if(conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

（二）创建实体类Student[依照数据库]

package cn.tx.enitty;

public class Student {

    private int id;
    private String name;
    private String  sex;
    private String birthday;

    public Student() {
    }

    public Student(int id, String name, String sex, String birthday) {
        this.id = id;
        this.name = name;
        this.sex = sex;
        this.birthday = birthday;
    }

    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 getBirthday() {
        return birthday;
    }

    public void setBirthday(String birthday) {
        this.birthday = birthday;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", sex='" + sex + '\'' +
                ", birthday='" + birthday + '\'' +
                '}'+"\n";
    }
}

（二）进行增删改查

package cn.tx.dao;

import cn.tx.commons.C3P0Conn;
import cn.tx.enitty.Student;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class StudentDao {
    private Connection conn = null;
    //DBUtils工具类
    private QueryRunner qr = new QueryRunner();

    //查询所有的学生
    public List<Student> SelectAll(){
        List<Student> List = null;
        //第一步获取连接
        conn = C3P0Conn.getConnection();
        //第二步编写sql语句
        String sql = "select * from students";
        try {
            List = qr.query(conn,sql,new BeanListHandler<Student>(Student.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //关闭资源
            C3P0Conn.closeConntion(conn);
        }
        return List;
    }
    /*条件查询*/
    public Student SelectAll2(int id,String name){
        Student stu = null;
        conn = C3P0Conn.getConnection();
        String sql = "select * from students where id = ? and name = ?";
        try {
            stu = qr.query(conn,sql,new BeanHandler<Student>(Student.class),id,name);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            C3P0Conn.closeConntion(conn);
        }
        return stu;
    }
    //增加
    public int Insert(Student st){
        int i = 0;
        conn = C3P0Conn.getConnection();
        String sql = "insert into students(name,sex,birthday) values(?,?,?)";
        try {
            i = qr.update(conn,sql,st.getName(),st.getSex(),st.getBirthday());
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            C3P0Conn.closeConntion(conn);
        }
        return i;
    }
    //删除
    public int deleteConn(int id){
        int i=0;
        conn = C3P0Conn.getConnection();
        String sql = "delete from students where id = ?";
        try {
            i = qr.update(conn,sql,id);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            C3P0Conn.closeConntion(conn);
        }
        return i;
    }
    //修改
    public int updateConn(Student stu){
        int i=0;
        conn = C3P0Conn.getConnection();
        String sql = "update students set name=?,sex=?,birthday=? where id = ?";
        try {
            i = qr.update(conn,sql,stu.getName(),stu.getSex(),stu.getBirthday(),stu.getId());
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            C3P0Conn.closeConntion(conn);
        }
        return i;
    }
    public static void main(String[] args) {
        StudentDao sd = new StudentDao();
        //查询
//        System.out.println(sd.SelectAll());
        //条件查询
//        System.out.println(sd.SelectAll2(8, "小思达"));
        //增加
//        Student student = new Student();
//        student.setName("梦缘");
//        student.setSex("男");
//        student.setBirthday("2021-2-2");
//        int i = sd.Insert(student);
//        System.out.println(i);
        //删除
//        int i1 = sd.deleteConn(11);
//        System.out.println(i1);

        Student stu1 = sd.SelectAll2(8, "小思达");
        System.out.println(stu1);
        stu1.setName("小梦缘");
        int i = sd.updateConn(stu1);
        System.out.println(i);
    }
}

附上结构图