JDBC简单应用——C3P0连接池

工程目录

一、准备工作

1、相关jar包：
mysql驱动包：mysql-connector-java-5.1.37-bin.jar
c3p0依赖包：c3p0-0.9.5.2.jar ，mchange-commons-java-0.2.12.jar
2、c3p0配置文件：c3p0-config.xml

二、代码编写

1、导jar包

2、c3p0-config.xml配置文件

<c3p0-config>
   <!-- 使用默认的配置读取连接池对象 -->
   <default-config>
       <!--  连接参数 -->
       <property name="driverClass">com.mysql.jdbc.Driver</property>
       <property name="jdbcUrl">jdbc:mysql://localhost:3306/my_db</property>
       <property name="user">root</property>
       <property name="password"></property>

       <!-- 连接池参数 -->
       <!--初始化连接数量-->
       <property name="initialPoolSize">5</property>
       <!--最大的连接数量-->
       <property name="maxPoolSize">10</property>
       <!--连接超时时间-->
       <property name="checkoutTimeout">3000</property>
   </default-config>

   <named-config name="otherc3p0">
       <!--  连接参数 -->
       <property name="driverClass">com.mysql.jdbc.Driver</property>
       <property name="jdbcUrl">jdbc:mysql://localhost:3306/my_db</property>
       <property name="user">root</property>
       <property name="password"> </property>

       <!-- 连接池参数 -->
       <property name="initialPoolSize">5</property>
       <property name="maxPoolSize">8</property>
       <property name="checkoutTimeout">1000</property>
   </named-config>
</c3p0-config>

3、JDBCUtils工具类

package com.mollen.demo02;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @ClassNmme: JDBCUTil
 * @Author: Mollen
 * @CreateDate: 2018-09-08 15:48:18
 * @Description:连接池工具类
 */
public class JDBCUtils {
    /**
     * 1.定义成员变量
     */
    private static DataSource ds;

    /**
     * 2.读取配置文件
     */
    static{
        ds = new ComboPooledDataSource();
    }

    /**
     * 3.获得连接
     */
    public static Connection getConn() throws SQLException {
        return ds.getConnection();
    }

    /**
     * 4.释放连接
     */
    public static void close(PreparedStatement pstmt, Connection conn){
        close(null,pstmt,conn);

    }
    public static void close(ResultSet rs, PreparedStatement pstmt, Connection conn){
        if(rs!=null){
            try {
                rs.close();
            }catch(Exception e){
                e.printStackTrace();
            }
        }
        if(pstmt!=null){
            try {
                pstmt.close();
            }catch(Exception e){
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            }catch(Exception e){
                e.printStackTrace();
            }
        }
    }
}

4、实体类

package com.mollen.demo02;

/**
* @ClassNmme: Student
* @Author: Mollen
* @CreateDate: 2018-09-08 20:16:18
* @Description: student实体类<==>student表
*/
public class Student {
   private String name;
   private  int age;
   //无参构造
   public Student() {
   }
   //有参构造
   public Student(String name, int age) {
       this.name = name;
       this.age = age;
   }
   //重写toString
   @Override
   public String toString() {
       return "Student{" +
               "name='" + name + '\'' +
               ", age=" + age +
               '}';
   }
   
	// getter/setter...
}

5、测试类

package com.mollen.demo02;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
* @ClassNmme: C3P0Test
* @Author: Mollen
* @CreateDate: 2018-09-08 08:56:47
* @Description: C3P0连接池测试
*/
public class C3P0Test {
   public static void main(String[] args) {

       Connection conn = null;
       PreparedStatement pstmt = null;
       ResultSet rs = null;
       Student stu = null;
       List<Student> list = new ArrayList<>();

       try {
           //1.获取连接
           conn = JDBCUtils.getConn();
           //2.定义sql
           String sql = "SELECT * FROM student WHERE age>?";
           //3.获取数据库操作对象
           pstmt = conn.prepareStatement(sql);
           //4.解析参数
           pstmt.setInt(1, 26);
           //5.执行操作
           rs = pstmt.executeQuery();
           //6.遍历结果集
           while (rs.next()) {
               stu = new Student(rs.getString("name"), rs.getInt("age"));
               list.add(stu);
           }
       } catch (SQLException e) {
           e.printStackTrace();
       } finally {
           //释放资源
           JDBCUtils.close(rs, pstmt, conn);
       }
		   //遍历打印集合
			......
   }
}

三、注意事项

1、程序默认读取src目录下c3p0-config.xml
2、数据源默认使用<default-config>标签下的连接
3、使用完毕释放资源