JDBC——c3p0连接池的使用

本文主要讲解jdbc连接mySQL。

一、在idea上新建maven工程

二、添加依赖包

<!-- 连接mysql工具包-->
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>5.1.38</version>
</dependency>
<!-- 使用c3p0——jdbc连接池-->
<dependency>
  <groupId>com.mchange</groupId>
  <artifactId>c3p0</artifactId>
  <version>0.9.5.4</version>
</dependency>

三、创建资源目录，并在其中创建c3p0的xml文件

resource可以在工程目录或src下创建

resource/c3p0-config.xml

c3p0-config.xml配置如下：

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <!--默认配置-->
    <default-config>
        <!-- initialPoolSize：初始化时获取三个连接，取值应在minPoolSize与maxPoolSize之间。 -->
        <property name="initialPoolSize">10</property>
        <!-- maxIdleTime：最大空闲时间,30秒内未使用则连接被丢弃。若为0则永不丢弃。-->
        <property name="maxIdleTime">30</property>
        <!-- maxPoolSize：连接池中保留的最大连接数 -->
        <property name="maxPoolSize">100</property>
        <!-- minPoolSize: 连接池中保留的最小连接数 -->
        <property name="minPoolSize">10</property>

        <property name="checkoutTimeout">3000</property>
    </default-config>

    <!--配置连接池mysql-->
    <named-config name="single01mysql">
        <!--   连接参数  -->
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://single01:3306/school?useSSL=false</property>
        <property name="user">root</property>
        <property name="password">ok</property>
        <property name="initialPoolSize">10</property>
        <property name="maxPoolSize">100</property>
        <property name="checkoutTimeout">3000</property>
        <property name="maxIdleTime">30</property>
        <property name="minPoolSize">10</property>
    </named-config>

    <!--配置连接池2,可以配置多个-->
</c3p0-config>

三、建立连接池测试类

public class C3p0Utils {
    static ComboPooledDataSource source=new ComboPooledDataSource("single01mysql");

    //创建连接
    public static Connection getConnection(){
        Connection connection=null;
        try {
            connection=source.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    //关闭连接
    public  static void close(Connection conn, PreparedStatement pstmt, ResultSet rs){
        try {
            if (rs!=null) {
                rs.close();
            }
            if (pstmt!=null){
                pstmt.close();
            }
            if (conn!=null){
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

   /* 关闭连接——可变参数
   public  static void close(AutoCloseable...closes){
        if (null !=closes){
            for (AutoCloseable close : closes) {
                try {
                    close.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }*/

    public static void main(String[] args) {
        //打印连接
        for (int i = 0; i < 10; i++) {
            Connection connection = C3p0Utils.getConnection();
            System.out.println(connection+ "  "+i);
            C3p0Utils.close(connection,null,null);
//            C3p0Utils.close(connection);
        }

    }
}

四:通过c3p0连接池往MySQL里插入，查询数据

1.创建接口程序，定义插入，查询方法；

public interface IStudentDao {
    public void insertStudent(List<Student> students);  //插入
    public List<Student> getStudents();  //查询
}

2、创建要查询的表格的数据的实例类；

属性，构造方法，set/get方法，重写toString方法

public class Student {
    private String id;
    private String name;

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

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

    public String getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public void setId(String id) {
        this.id = id;
    }

    public void setName(String name) {
        this.name = name;
    }
}

3.创建实现类；实现插入，查询数据

public class StudentDao  implements IStudentDao{
    //插入mysql表格
    public void insertStudent(List<Student> students){
        //insert into student(id,name) values("001","as"),("002","ls");
        String sql="insert into student(id,name) values";
        if (students!=null && students.size()>0){
            for (Student stu : students) {
                sql=sql+"('"+stu.getId()+"','"+stu.getName()+"'),";
            }
            sql=sql.substring(0,sql.length()-1);
            System.out.println(sql);

            Connection connection = C3p0Utils.getConnection();
           
            PreparedStatement preparedStatement =null;
            try {
                preparedStatement =connection.prepareStatement(sql);
                int i = preparedStatement.executeUpdate();
                System.out.println(i);

            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                C3p0Utils.close(connection,preparedStatement,null);
            }

        }
    }

    //查询mysql表格
    public List<Student> getStudents() {
        Connection connection = C3p0Utils.getConnection();
        String sql ="select id,name from student";
        ArrayList<Student> students = new ArrayList<>();

        PreparedStatement preparedStatement = null;
        ResultSet resultSet=null;
        try {
            preparedStatement=connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                String id = resultSet.getString("id");
                String name = resultSet.getString("name");
                /*String id = resultSet.getString(1);
                String name = resultSet.getString(2);*/
//                System.out.println(id+"  "+name);
                Student student = new Student(id, name);
                students.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            C3p0Utils.close(connection,preparedStatement,resultSet);
        }
        return students;

    }
    
    public static void main(String[] args) {
        StudentDao studentDao = new StudentDao();
        //插入mysql
        Student stu1 = new Student("003", "ww");
        Student stu2 = new Student("004", "zl");
        Student stu3 = new Student("005", "zq");
        ArrayList<Student> students = new ArrayList<>();
        students.add(stu1);
        students.add(stu2);
        students.add(stu3);
        studentDao.insertStudent(students);

        //查询mysql表格
        /*List<Student> students = studentDao.getStudents();
        for (Student stu : students) {
            System.out.println(stu.toString());
        }*/
    }
}