JDBC——c3p0连接池的使用

本文主要讲解jdbc连接mySQL。

一、在idea上新建maven工程

二、添加依赖包

  mysql
  mysql-connector-java
  5.1.38



  com.mchange
  c3p0
  0.9.5.4

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

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

resource/c3p0-config.xml

c3p0-config.xml配置如下：

    
    
        
        10
        
        30
        
        100
        
        10

        3000
    

    
    
        
        com.mysql.jdbc.Driver
        jdbc:mysql://single01:3306/school?useSSL=false
        root
        ok
        10
        100
        3000
        30
        10
    

    

三、建立连接池测试类

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 students);  //插入
    public List 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 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 getStudents() {
        Connection connection = C3p0Utils.getConnection();
        String sql ="select id,name from student";
        ArrayList 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 students = new ArrayList<>();
        students.add(stu1);
        students.add(stu2);
        students.add(stu3);
        studentDao.insertStudent(students);

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