Java文档阅读笔记-C3P0连接池的使用

这篇博文如何在应用程序中使用和配置C3P0

prom.xml如下:

<dependency>
  <groupId>com.mchange</groupId>
  <artifactId>c3p0</artifactId>
  <version>0.9.5.5</version>
</dependency>

数据库使用MySQL,这个例子连接knpcode库,并且检索EMPLOYEE表,这个EMPOLYEE表包含的域有ID,FIREST_NAME,LAST_NAME以及DEPARTMENT。

通过实例化ComboPooledDataSource类来创建C3P0连接池。

下面是一些数据库相关的配置:

acquireIncrement:如果连接池中的连接被使用殆尽,将会以多少的数量进行增加,默认值为3。

initialPoolSize:程序启动时连接池的数量,默认为3。

maxPoolSize:最大连接数,默认为15.

maxIdleTime:几秒未使用的连接就会被丢弃,默认为0,不丢弃。

minPoolSize:连接池中最小的数量。

数据库和链接池相关的配置都在db.properties文件中:

DB.DRIVER_CLASS=com.mysql.cj.jdbc.Driver
DB.DB_URL=jdbc:mysql://localhost:3306/knpcode
DB.DB_USER=root
DB.DB_PASSWORD=admin
DB.INITIAL_POOL_SIZE=5
DB.MAX_POOL_SIZE=5

下面是创建ComboPooledDataSource相关的代码:

import java.beans.PropertyVetoException;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;

public class DSCreator {
  private static ComboPooledDataSource pooledDS;
  static {
    try {
    	pooledDS = new ComboPooledDataSource();
      Properties properties = new Properties();
      // Loading properties file from classpath
      InputStream inputStream = DSCreator.class
                                         .getClassLoader()
                                         .getResourceAsStream("db.properties");
      if(inputStream == null){
        throw new IOException("File not found");
      }
      properties.load(inputStream);	
      pooledDS.setDriverClass(properties.getProperty("DB.DRIVER_CLASS"));
      pooledDS.setJdbcUrl(properties.getProperty("DB.DB_URL"));
      pooledDS.setUser(properties.getProperty("DB.DB_USER"));
      pooledDS.setPassword(properties.getProperty("DB.DB_PASSWORD"));

      pooledDS.setInitialPoolSize(Integer.parseInt(properties.getProperty("DB.INITIAL_POOL_SIZE")));
      // Default anyway
      pooledDS.setAcquireIncrement(3);
      pooledDS.setMaxPoolSize(Integer.parseInt(properties.getProperty("DB.MAX_POOL_SIZE")));
    }catch(IOException | PropertyVetoException e) {
      e.printStackTrace();
    }
  }
  public static DataSource getDataSource() {
    return pooledDS;
  } 
}

Test类用于创建连接以及获取DataSource对象以及查询数据库:

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;

public class DSDemo {
  public static void main(String[] args) {
    DSDemo dsDemo = new DSDemo();
    dsDemo.displayEmployeeById(16);
  }
  private void displayEmployeeById(int id){
    Connection connection = null; 
    String selectSQL = "SELECT * FROM EMPLOYEE WHERE id = ?";
    PreparedStatement prepStmt = null;
    try {
      DataSource ds = DSCreator.getDataSource();
      connection = ds.getConnection();
      prepStmt = connection.prepareStatement(selectSQL);
      prepStmt.setInt(1, id);
      ResultSet rs = prepStmt.executeQuery();
      while(rs.next()){
        System.out.println("id: " + rs.getInt("id"));
        System.out.println("First Name: " + rs.getString("FIRST_NAME"));
        System.out.println("Last Name: " + rs.getString("LAST_NAME"));
        System.out.println("Department: " + rs.getString("DEPARTMENT"));
      }
    } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }finally{
      if(connection != null){
        try {
          connection.close();
        } catch (SQLException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
        }
      }
    }
  }
}

 

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 数字20 设计师:CSDN官方博客 返回首页