自定義帶監控的資料庫連線池

簡單隨風發表於2020-12-26

我們可以先看一個最基礎的jdbc連線的使用方法，在這裡我們使用同一個connection做了兩次查詢的操作

public static void main(String[] args) throws ClassNotFoundException, SQLException {
    Class.forName("com.mysql.jdbc.Driver");
    Connection connection = DriverManager.getConnection(
      "jdbc:mysql://localhost:3306/database?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true",
      "root",
      "root"
    );
    // sql執行
    PreparedStatement preparedStatement = connection.prepareStatement(
      "select * from table_a;"
    );
    // 獲取返回結果
    ResultSet resultSet = preparedStatement.executeQuery();
    while (resultSet.next()) {
      System.out.println(resultSet.getString("id"));
      System.out.println(resultSet.getString("user_id"));
    }
    resultSet.close();
    preparedStatement.close();


    PreparedStatement preparedStatement2 = connection.prepareStatement(
      "select * from table_b"
    );
    ResultSet resultSet2 = preparedStatement2.executeQuery();
    while (resultSet2.next()) {
      System.out.println(resultSet2.getString(1));
      System.out.println(resultSet2.getString(2));
    }
    resultSet2.close();
    preparedStatement2.close();

    connection.close();

}

我們如何實現連線池呢，有以下幾步：

實現PooledObjectFactory<Connection>

public class ConnectionPooledObjectFactory implements PooledObjectFactory<Connection> {

  @Override
  public PooledObject<Connection> makeObject() throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    Connection connection = DriverManager.getConnection(
      "jdbc:mysql://localhost:3306/database?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true",
      "root",
      "root"
    );
    return new DefaultPooledObject<>(connection);
  }

  @Override
  public void destroyObject(PooledObject<Connection> p) throws Exception {
    p.getObject().close();
  }

  @Override
  public boolean validateObject(PooledObject<Connection> p) {
    Connection connection = p.getObject();
    try {
      PreparedStatement statement = connection.prepareStatement("SELECT 1");
      ResultSet resultSet = statement.executeQuery();
      int i = resultSet.getInt(1);
      return i == 1;
    } catch (SQLException e) {
      return false;
    }
  }

  @Override
  public void activateObject(PooledObject<Connection> p) throws Exception {
    // 可以把connection額外的配置放到這裡
  }

  @Override
  public void passivateObject(PooledObject<Connection> p) throws Exception {
    
  }
}

實現DataSource

public class MyDataSource implements DataSource {
  private GenericObjectPool<MyConnection> pool;

  public GenericObjectPool<MyConnection> getPool() {
    return pool;
  }

  public void setPool(GenericObjectPool<MyConnection> pool) {
    this.pool = pool;
  }

  public MyDataSource() {
    ConnectionPooledObjectFactory factory = new ConnectionPooledObjectFactory();
    this.pool = new GenericObjectPool<>(factory);
  }

  @Override
  public Connection getConnection() throws SQLException {
    try {
      return this.pool.borrowObject();
    } catch (Exception e) {
      throw new SQLException("獲取連線失敗！", e);
    }
  }

  @Override
  public Connection getConnection(String username, String password) throws SQLException {
    return this.getConnection();
  }

  @Override
  public <T> T unwrap(Class<T> iface) throws SQLException {
    throw new UnsupportedOperationException("不支援的操作！");
  }

  @Override
  public boolean isWrapperFor(Class<?> iface) throws SQLException {
    throw new UnsupportedOperationException("不支援的操作！");
  }

  @Override
  public PrintWriter getLogWriter() throws SQLException {
    throw new UnsupportedOperationException("不支援的操作！");
  }

  @Override
  public void setLogWriter(PrintWriter out) throws SQLException {
    throw new UnsupportedOperationException("不支援的操作！");
  }

  @Override
  public void setLoginTimeout(int seconds) throws SQLException {
    throw new UnsupportedOperationException("不支援的操作！");
  }

  @Override
  public int getLoginTimeout() throws SQLException {
    throw new UnsupportedOperationException("不支援的操作！");
  }

  @Override
  public Logger getParentLogger() throws SQLFeatureNotSupportedException {
    throw new UnsupportedOperationException("不支援的操作！");
  }
}

修改啟動類，修改dataSource

public class Application {

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
    
    @Bean
    @Primary
    public MyDataSource dataSource(){
        return new MyDataSource();
    }
}

然後我們啟動專案，嘗試連線資料庫，這裡能正常獲取到資料就表示已經配置成功了！

然後我們實現監控功能，我們新建一個DataSourceEnpoint類，使用@Endpoint(id = "datasource")註解，意思是我們可以使用/actuator/datasource

@Endpoint(id = "datasource")
public class DataSourceEnpoint {
  private MyDataSource dataSource;

  public DataSourceEnpoint(MyDataSource dataSource) {
    this.dataSource = dataSource;
  }

  @ReadOperation
  public Map<String, Object> pool() {
    GenericObjectPool<MyConnection> pool = dataSource.getPool();
    HashMap<String, Object> map = Maps.newHashMap();
    map.put("numActive", pool.getNumActive());
    map.put("numIdle", pool.getNumIdle());
    map.put("createdCount", pool.getCreatedCount());
    return map;
  }
}

修改啟動類

public class Application {

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
    
    @Bean
    @Primary
    public DataSource dataSource(){
        return new MyDataSource();
    }

    @Bean
    public DataSourceEnpoint dataSourceEnpoint() {
        DataSource dataSource = this.dataSource();
        return new DataSourceEnpoint((MyDataSource) dataSource);
    }
}

這裡會有一個型別轉換的報錯，解決方案是在yml檔案中，讓javamelody禁用dataSource

javamelody:
  excluded-datasources: dataSource

然後重啟專案，這個時候我們訪問幾次資料庫，再訪問/actuator/datasource，能看到我們建立了6個物件，活躍6個物件，以及0個空閒，說明我們的connection沒有進行回收。
在這裡插入圖片描述
那如何解決connection回收的問題呢，這個時候我們再建立一個MyConnection

// 1. 回收statement
// 2. 回收resultSet
// 3. 複用Connection
public class MyConnection implements Connection {
  private Connection connection;
  private Statement statement;
  private ObjectPool<MyConnection> objectPool;

  public ObjectPool<MyConnection> getObjectPool() {
    return objectPool;
  }

  public void setObjectPool(ObjectPool<MyConnection> objectPool) {
    this.objectPool = objectPool;
  }

  public Connection getConnection() {
    return connection;
  }

  public void setConnection(Connection connection) {
    this.connection = connection;
  }

  public Statement getStatement() {
    return statement;
  }

  public void setStatement(Statement statement) {
    this.statement = statement;
  }

  @Override
  public Statement createStatement() throws SQLException {
    return this.connection.createStatement();
  }

  @Override
  public PreparedStatement prepareStatement(String sql) throws SQLException {
    PreparedStatement prepareStatement = this.connection.prepareStatement(sql);
    this.statement = prepareStatement;
    return prepareStatement;
  }

  @Override
  public CallableStatement prepareCall(String sql) throws SQLException {
    return this.connection.prepareCall(sql);
  }

  @Override
  public String nativeSQL(String sql) throws SQLException {
    return this.connection.nativeSQL(sql);
  }

  @Override
  public void setAutoCommit(boolean autoCommit) throws SQLException {
    this.connection.setAutoCommit(autoCommit);
  }

  @Override
  public boolean getAutoCommit() throws SQLException {
    return this.connection.getAutoCommit();
  }

  @Override
  public void commit() throws SQLException {
    this.connection.commit();
  }

  @Override
  public void rollback() throws SQLException {
    this.connection.rollback();
  }

  @Override
  public void close() throws SQLException {
    // 如果底層的Connection已經關閉
    if (this.isClosed()) {
      try {
        objectPool.invalidateObject(this);
      } catch (Exception e) {
        throw new SQLException(e);
      }
    }
    // 底層Connection沒有關閉，可以繼續複用
    else {
      try {
        objectPool.returnObject(this);
      } catch (Exception e) {
        this.connection.close();
        throw new SQLException(e);
      }
    }
  }

  @Override
  public boolean isClosed() throws SQLException {
    return this.connection.isClosed();
  }

  @Override
  public DatabaseMetaData getMetaData() throws SQLException {
    return this.connection.getMetaData();
  }

  @Override
  public void setReadOnly(boolean readOnly) throws SQLException {
    this.connection.setReadOnly(readOnly);
  }

  @Override
  public boolean isReadOnly() throws SQLException {
    return this.connection.isReadOnly();
  }

  @Override
  public void setCatalog(String catalog) throws SQLException {
    this.connection.setCatalog(catalog);
  }

  @Override
  public String getCatalog() throws SQLException {
    return this.connection.getCatalog();
  }

  @Override
  public void setTransactionIsolation(int level) throws SQLException {
    this.connection.setTransactionIsolation(level);
  }

  @Override
  public int getTransactionIsolation() throws SQLException {
    return this.connection.getTransactionIsolation();
  }

  @Override
  public SQLWarning getWarnings() throws SQLException {
    return this.connection.getWarnings();
  }

  @Override
  public void clearWarnings() throws SQLException {
    this.connection.clearWarnings();
  }

  @Override
  public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException {
    Statement statement = this.connection.createStatement(resultSetType, resultSetConcurrency);
    this.statement = statement;
    return statement;
  }

  @Override
  public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
    PreparedStatement preparedStatement = this.connection.prepareStatement(sql, resultSetType, resultSetConcurrency);
    this.statement = preparedStatement;
    return preparedStatement;
  }

  @Override
  public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
    return this.connection.prepareCall(sql, resultSetType, resultSetConcurrency);
  }

  @Override
  public Map<String, Class<?>> getTypeMap() throws SQLException {
    return this.connection.getTypeMap();
  }

  @Override
  public void setTypeMap(Map<String, Class<?>> map) throws SQLException {
    this.connection.setTypeMap(map);
  }

  @Override
  public void setHoldability(int holdability) throws SQLException {
    this.connection.setHoldability(holdability);
  }

  @Override
  public int getHoldability() throws SQLException {
    return this.connection.getHoldability();
  }

  @Override
  public Savepoint setSavepoint() throws SQLException {
    return this.connection.setSavepoint();
  }

  @Override
  public Savepoint setSavepoint(String name) throws SQLException {
    return this.connection.setSavepoint(name);
  }

  @Override
  public void rollback(Savepoint savepoint) throws SQLException {
    this.connection.rollback(savepoint);
  }

  @Override
  public void releaseSavepoint(Savepoint savepoint) throws SQLException {
    this.connection.releaseSavepoint(savepoint);
  }

  @Override
  public Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
    Statement statement = this.connection.createStatement(resultSetType, resultSetConcurrency, resultSetHoldability);
    this.statement = statement;
    return statement;
  }

  @Override
  public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
    PreparedStatement statement = this.connection.prepareStatement(sql, resultSetType, resultSetConcurrency, resultSetHoldability);
    this.statement = statement;
    return statement;
  }

  @Override
  public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
    return this.connection.prepareCall(sql, resultSetType, resultSetConcurrency, resultSetHoldability);
  }

  @Override
  public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException {
    PreparedStatement statement = this.connection.prepareStatement(sql, autoGeneratedKeys);
    this.statement = statement;
    return statement;
  }

  @Override
  public PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException {
    PreparedStatement statement = this.connection.prepareStatement(sql, columnIndexes);
    this.statement = statement;
    return statement;
  }

  @Override
  public PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException {
    PreparedStatement statement = this.connection.prepareStatement(sql, columnNames);
    this.statement = statement;
    return statement;
  }

  @Override
  public Clob createClob() throws SQLException {
    return this.connection.createClob();
  }

  @Override
  public Blob createBlob() throws SQLException {
    return this.connection.createBlob();
  }

  @Override
  public NClob createNClob() throws SQLException {
    return this.connection.createNClob();
  }

  @Override
  public SQLXML createSQLXML() throws SQLException {
    return this.connection.createSQLXML();
  }

  @Override
  public boolean isValid(int timeout) throws SQLException {
    return this.connection.isValid(timeout);
  }

  @Override
  public void setClientInfo(String name, String value) throws SQLClientInfoException {
    this.connection.setClientInfo(name, value);
  }

  @Override
  public void setClientInfo(Properties properties) throws SQLClientInfoException {
    this.connection.setClientInfo(properties);
  }

  @Override
  public String getClientInfo(String name) throws SQLException {
    return this.connection.getClientInfo(name);
  }

  @Override
  public Properties getClientInfo() throws SQLException {
    return this.connection.getClientInfo();
  }

  @Override
  public Array createArrayOf(String typeName, Object[] elements) throws SQLException {
    return this.connection.createArrayOf(typeName, elements);
  }

  @Override
  public Struct createStruct(String typeName, Object[] attributes) throws SQLException {
    return this.connection.createStruct(typeName, attributes);
  }

  @Override
  public void setSchema(String schema) throws SQLException {
    this.connection.setSchema(schema);
  }

  @Override
  public String getSchema() throws SQLException {
    return this.connection.getSchema();
  }

  @Override
  public void abort(Executor executor) throws SQLException {
    this.connection.abort(executor);
  }

  @Override
  public void setNetworkTimeout(Executor executor, int milliseconds) throws SQLException {
    this.connection.setNetworkTimeout(executor, milliseconds);
  }

  @Override
  public int getNetworkTimeout() throws SQLException {
    return this.connection.getNetworkTimeout();
  }

  @Override
  public <T> T unwrap(Class<T> iface) throws SQLException {
    return this.connection.unwrap(iface);
  }

  @Override
  public boolean isWrapperFor(Class<?> iface) throws SQLException {
    return this.connection.isWrapperFor(iface);
  }

}

然後將ConnectionPooledObjectFactory中的Connecrion全部修改為MyConnecrion,並補充passivateObject的邏輯

public class ConnectionPooledObjectFactory implements PooledObjectFactory<MyConnection> {
  private ObjectPool<MyConnection> objectPool;

  public ObjectPool<MyConnection> getObjectPool() {
    return objectPool;
  }

  public void setObjectPool(ObjectPool<MyConnection> objectPool) {
    this.objectPool = objectPool;
  }

  @Override
  public PooledObject<MyConnection> makeObject() throws Exception {
    Class.forName("com.mysql.jdbc.Driver");
    Connection connection = DriverManager.getConnection(
      "jdbc:mysql://localhost:3306/database?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true",
      "root",
      "root"
    );
    MyConnection myConnection = new MyConnection();
    myConnection.setConnection(connection);
    myConnection.setObjectPool(objectPool);
    return new DefaultPooledObject<>(myConnection);
  }

  @Override
  public void destroyObject(PooledObject<MyConnection> p) throws Exception {
    p.getObject().close();
  }

  @Override
  public boolean validateObject(PooledObject<MyConnection> p) {
    Connection connection = p.getObject();
    try {
      PreparedStatement statement = connection.prepareStatement("SELECT 1");
      ResultSet resultSet = statement.executeQuery();
      int i = resultSet.getInt(1);
      return i == 1;
    } catch (SQLException e) {
      return false;
    }
  }

  @Override
  public void activateObject(PooledObject<MyConnection> p) throws Exception {
    // 可以把connection額外的配置放到這裡
  }

  @Override
  public void passivateObject(PooledObject<MyConnection> p) throws Exception {
    // 鈍化
    MyConnection myConnection = p.getObject();
    Statement statement = myConnection.getStatement();
    if (statement != null) {
      statement.close();
    }
  }
}

然後修改一下MyDataSource，給factory設一下值

public MyDataSource() {
    ConnectionPooledObjectFactory factory = new ConnectionPooledObjectFactory();
    this.pool = new GenericObjectPool<>(factory);
    factory.setObjectPool(pool);
  }

啟動專案，再次訪問/actuator/datasource，可以看到這個時候物件已經可以正常複用了，變成了一個空閒的連線
在這裡插入圖片描述
到這裡，一個帶監控的資料庫連線池就實現完成了！

【MySQL】自定義資料庫連線池和開源資料庫連線池的使用
2021-08-19
MySql資料庫
自定義連線池
2020-12-31
帶你進入資料庫連線池
2019-03-04
資料庫
Kettle自定義資料庫連線型別連線HGDB
2022-04-24
資料庫型別
資料庫連線池
2003-08-21
資料庫
《四資料庫連線池原始碼》手寫資料庫連線池
2019-04-01
資料庫原始碼
資料庫連線池-Druid資料庫連線池原始碼解析
2023-01-02
資料庫UI原始碼
資料庫連線池的理解
2017-02-16
資料庫
資料庫連線池的使用
2017-02-04
資料庫
WASCE的資料庫連線池
2011-05-30
資料庫
資料庫連線池原理
2018-08-27
資料庫
Proxool資料庫連線池
2017-04-17
資料庫
JAVA資料庫連線池
2010-03-27
Java資料庫
Flask資料庫連線池
2024-03-06
Flask資料庫
資料庫連線池的工作原理
2007-08-21
資料庫
監控資料庫連線遇到的一個小問題
2020-03-28
資料庫
python資料庫連線池
2020-07-07
Python資料庫
手寫資料庫連線池
2022-06-21
資料庫
瞭解資料庫連線池
2016-11-22
資料庫
資料庫連線池淺析
2014-05-21
資料庫
JNDI配置資料庫連線池
2012-07-06
資料庫
PROXOOL資料庫連線池使用
2010-01-06
資料庫
關於資料庫連線池
2003-11-03
資料庫
.net 資料庫連線池配置
2013-03-26
資料庫
java資料庫連線池dbcp的使用
2014-07-16
Java資料庫
利用weblogic的POOL（連線池）連線資料庫 (轉)
2008-01-28
Web資料庫
MySql資料庫連線池專題
2019-02-27
MySql資料庫
JavaWeb之事務&資料庫連線池
2021-09-09
JavaWeb資料庫
mysql資料庫連線池配置教程
2021-09-09
MySql資料庫
Python資料庫連線池DButils
2020-12-13
Python資料庫
聊聊資料庫連線池 Druid
2023-12-09
資料庫UI
JDBC資料庫連線池實現
2017-12-23
JDBC資料庫
請關掉你的資料庫連線!並且請使用資料庫連線池
2016-04-08
資料庫
資料庫連線池的實現及原理
2019-03-04
資料庫
druid資料庫連線池的配置類
2022-01-18
UI資料庫
Python 中的 MySQL 資料庫連線池
2016-08-09
PythonMySql資料庫
hibernate資料庫連線池的問題
2011-06-07
資料庫
一個資料庫連線池的問題
2004-10-18
資料庫

自定義帶監控的資料庫連線池

相關文章