jdbc: make connection short-lived (#459)

Valery Kharseko

15.41.2025 b03cfbb451ebc68a9796eb5acb192fdd0d0b7e81

jdbc: make connection short-lived (#459)

 opendj-server-legacy/src/main/java/org/opends/server/backends/jdbc/CachedConnection.java

New file
@@ -0,0 +1,337 @@
/*
 * The contents of this file are subject to the terms of the Common Development and
 * Distribution License (the License). You may not use this file except in compliance with the
 * License.
 *
 * You can obtain a copy of the License at legal/CDDLv1.0.txt. See the License for the
 * specific language governing permission and limitations under the License.
 *
 * When distributing Covered Software, include this CDDL Header Notice in each file and include
 * the License file at legal/CDDLv1.0.txt. If applicable, add the following below the CDDL
 * Header, with the fields enclosed by brackets [] replaced by your own identifying
 * information: "Portions Copyright [year] [name of copyright owner]".
 *
 * Copyright 2024 3A Systems, LLC.
 */
package org.opends.server.backends.jdbc;

import com.google.common.cache.*;

import java.sql.*;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.Executor;
import java.util.concurrent.TimeUnit;

public class CachedConnection implements Connection {
    final Connection parent;

    static LoadingCache<String,Connection> cached= CacheBuilder.newBuilder()
            .expireAfterAccess(Long.parseLong(System.getProperty("org.openidentityplatform.opendj.jdbc.ttl","15000")), TimeUnit.MILLISECONDS)
            .removalListener(new RemovalListener<String, Connection>() {
                @Override
                public void onRemoval(RemovalNotification<String, Connection> notification) {
                    try {
                        if (!notification.getValue().isClosed()) {
                            notification.getValue().close();
                        }
                    } catch (SQLException e) {
                    }
                }
            })
            .build(new CacheLoader<String, Connection>() {
                @Override
                public Connection load(String connectionString) throws Exception {
                    return DriverManager.getConnection(connectionString);
                }
            });

    public CachedConnection(Connection parent) {
        this.parent = parent;
    }

    static CachedConnection getConnection(String connectionString) throws Exception {
        Connection con=cached.get(connectionString);
        try {
            if (con != null && !con.isValid(0)) {
                cached.invalidate(connectionString);
                con.close();
                con = cached.get(connectionString);
            }
        } catch (SQLException e) {
            con = null;
        }
        con.setAutoCommit(false);
        return new CachedConnection(con);
    }

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

    @Override
    public PreparedStatement prepareStatement(String sql) throws SQLException {
        return parent.prepareStatement(sql);
    }

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

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

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

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

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

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

    @Override
    public void close() throws SQLException {
        //rollback();
    }

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

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

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

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

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

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

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

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

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

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

    @Override
    public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException {
        return parent.createStatement(resultSetType, resultSetConcurrency);
    }

    @Override
    public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
        return parent.prepareStatement(sql, resultSetType, resultSetConcurrency);
    }

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

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

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

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

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

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

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

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

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

    @Override
    public Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
        return parent.createStatement(resultSetType, resultSetConcurrency, resultSetHoldability);
    }

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

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

    @Override
    public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException {
        return parent.prepareStatement(sql, autoGeneratedKeys);
    }

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 opendj-server-legacy/src/main/java/org/opends/server/backends/jdbc/Storage.java

@@ -82,13 +82,19 @@
        return statement.execute();
    }

    Connection con;
    Connection getConnection() throws Exception {
        final Connection con=CachedConnection.getConnection(config.getDBDirectory());
        return con;
    }


    AccessMode accessMode=AccessMode.READ_ONLY;
    @Override
    public void open(AccessMode accessMode) throws Exception {
        con=DriverManager.getConnection(config.getDBDirectory());
        con.setAutoCommit(false);
        con.setReadOnly(!AccessMode.READ_WRITE.equals(accessMode));
        storageStatus = StorageStatus.working();
        try (final Connection con=getConnection()) {
            this.accessMode = accessMode;
            storageStatus = StorageStatus.working();
        }
    }

    private StorageStatus storageStatus = StorageStatus.lockedDown(LocalizableMessage.raw("closed"));
@@ -100,14 +106,6 @@
    @Override
    public void close() {
        storageStatus = StorageStatus.lockedDown(LocalizableMessage.raw("closed"));
        try {
            if (con != null && !con.isClosed()) {
                con.close();
            }
        } catch (SQLException e) {
            logger.error(LocalizableMessage.raw("close(): %s",e),e);
        }
        con=null;
    }

    String getTableName(TreeName treeName) {
@@ -124,13 +122,25 @@
                throw new StorageRuntimeException(e);
            }
        }
        try {
            for (TreeName treeName : listTrees()) {
                final PreparedStatement statement=con.prepareStatement("drop table "+getTableName(treeName));
                execute(statement);
        final Set<TreeName> trees=listTrees();
        if (!trees.isEmpty()) {
            try (final Connection con = getConnection()) {
                try {
                    for (final TreeName treeName : trees) {
                        try (final PreparedStatement statement = con.prepareStatement("drop table " + getTableName(treeName))) {
                            execute(statement);
                        }
                    }
                    con.commit();
                } catch (SQLException e) {
                    try {
                        con.rollback();
                    } catch (SQLException e2) {}
                    throw new StorageRuntimeException(e);
                }
            } catch (Exception e) {
                throw new StorageRuntimeException(e);
            }
        }catch (Throwable e) {
            throw new StorageRuntimeException(e);
        }
        if (!isOpen) {
            close();
@@ -140,107 +150,111 @@
    //operation
    @Override
    public <T> T read(ReadOperation<T> readOperation) throws Exception {
        return readOperation.run(new ReadableTransactionImpl());
        try(final Connection con=getConnection()) {
            return readOperation.run(new ReadableTransactionImpl(con));
        }
    }

    @Override
    public void write(WriteOperation writeOperation) throws Exception {
        try {
            writeOperation.run(new WriteableTransactionTransactionImpl());
            con.commit();
        } catch (Exception e) {
        try (final Connection con=getConnection()) {
            try {
                con.rollback();
            } catch (SQLException ex) {}
            throw e;
                writeOperation.run(new WriteableTransactionTransactionImpl(con));
                con.commit();
            } catch (Exception e) {
                try {
                    con.rollback();
                } catch (SQLException ex) {}
                throw e;
            }
        }
    }

    private class ReadableTransactionImpl implements ReadableTransaction {
        final Connection con;
        boolean isReadOnly=true;

        public ReadableTransactionImpl(Connection con) {
            this.con=con;
        }

        @Override
        public ByteString read(TreeName treeName, ByteSequence key) {
            try {
                final PreparedStatement statement=con.prepareStatement("select v from "+getTableName(treeName)+" where k=?");
            try (final PreparedStatement statement=con.prepareStatement("select v from "+getTableName(treeName)+" where k=?")){
                statement.setBytes(1,key.toByteArray());
                try(ResultSet rc=executeResultSet(statement)) {
                    return rc.next() ? ByteString.wrap(rc.getBytes("v")) : null;
                }
            }catch (SQLException e) {
                throw new RuntimeException(e);
                throw new StorageRuntimeException(e);
            }
        }

        @Override
        public Cursor<ByteString, ByteString> openCursor(TreeName treeName) {
            return new CursorImpl(treeName);
            return new CursorImpl(isReadOnly,con,treeName);
        }

        @Override
        public long getRecordCount(TreeName treeName) {
            try {
                final PreparedStatement statement=con.prepareStatement("select count(*) from "+getTableName(treeName));
                try(ResultSet rc=executeResultSet(statement)) {
                    return rc.next() ? rc.getLong(1) : 0;
                }
            try (final PreparedStatement statement=con.prepareStatement("select count(*) from "+getTableName(treeName));
                 final ResultSet rc=executeResultSet(statement)){
                return rc.next() ? rc.getLong(1) : 0;
            }catch (SQLException e) {
                throw new RuntimeException(e);
                throw new StorageRuntimeException(e);
            }
        }
    }
    private final class WriteableTransactionTransactionImpl extends ReadableTransactionImpl implements WriteableTransaction {
        
        public WriteableTransactionTransactionImpl() {
            super();
            try {
                if (con.isReadOnly()) {
                    throw new ReadOnlyStorageException();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
        public WriteableTransactionTransactionImpl(Connection con) {
            super(con);
            if (!accessMode.isWriteable()) {
                throw new ReadOnlyStorageException();
            }
            isReadOnly=false;
        }

        @Override
        public void openTree(TreeName treeName, boolean createOnDemand) {
            if (createOnDemand) {
                try {
                    final PreparedStatement statement=con.prepareStatement("create table if not exists "+getTableName(treeName)+" (k bytea primary key,v bytea)");
                try (final PreparedStatement statement=con.prepareStatement("create table if not exists "+getTableName(treeName)+" (k bytea primary key,v bytea)")){
                    execute(statement);
                    con.commit();
                }catch (SQLException e) {
                    throw new RuntimeException(e);
                    throw new StorageRuntimeException(e);
                }
            }
        }
        
        public void clearTree(TreeName treeName) {
            try {
                final PreparedStatement statement=con.prepareStatement("truncate table "+getTableName(treeName));
            try (final PreparedStatement statement=con.prepareStatement("delete from "+getTableName(treeName))){
                execute(statement);
                con.commit();
            }catch (SQLException e) {
                throw new RuntimeException(e);
                throw new StorageRuntimeException(e);
            }
        }

        @Override
        public void deleteTree(TreeName treeName) {
            try {
                final PreparedStatement statement=con.prepareStatement("drop table "+getTableName(treeName));
            try (final PreparedStatement statement=con.prepareStatement("drop table "+getTableName(treeName))){
                execute(statement);
                con.commit();
            }catch (SQLException e) {
                throw new RuntimeException(e);
                throw new StorageRuntimeException(e);
            }
        }

        @Override
        public void put(TreeName treeName, ByteSequence key, ByteSequence value) {
            try {
                delete(treeName,key);
                final PreparedStatement statement=con.prepareStatement("insert into "+getTableName(treeName)+" (k,v) values(?,?) ");
            delete(treeName,key);
            try (final PreparedStatement statement=con.prepareStatement("insert into "+getTableName(treeName)+" (k,v) values(?,?) ")){
                statement.setBytes(1,key.toByteArray());
                statement.setBytes(2,value.toByteArray());
                execute(statement);
            }catch (SQLException e) {
                throw new RuntimeException(e);
                throw new StorageRuntimeException(e);
            }
        }

@@ -263,12 +277,11 @@

        @Override
        public boolean delete(TreeName treeName, ByteSequence key) {
            try {
                final PreparedStatement statement=con.prepareStatement("delete from "+getTableName(treeName)+" where k=?");
            try (final PreparedStatement statement=con.prepareStatement("delete from "+getTableName(treeName)+" where k=?")){
                statement.setBytes(1,key.toByteArray());
                execute(statement);
            }catch (SQLException e) {
                throw new RuntimeException(e);
                throw new StorageRuntimeException(e);
            }
            return true;
        }
@@ -276,20 +289,20 @@
    
    private final class CursorImpl implements Cursor<ByteString, ByteString> {
        final TreeName treeName;
        //final WriteableTransactionTransactionImpl tx;

        ResultSet rc;

        public CursorImpl(TreeName treeName) {
        final PreparedStatement statement;
        final ResultSet rc;
        final boolean isReadOnly;
        public CursorImpl(boolean isReadOnly, Connection con, TreeName treeName) {
            this.treeName=treeName;
            //this.tx=tx;
            this.isReadOnly=isReadOnly;
            try {
                final PreparedStatement statement=con.prepareStatement("select k,v from "+getTableName(treeName)+" order by k",
                        ResultSet.TYPE_SCROLL_SENSITIVE,
                        ResultSet.CONCUR_UPDATABLE);
                statement=con.prepareStatement("select k,v from "+getTableName(treeName)+" order by k",
                        isReadOnly?ResultSet.TYPE_SCROLL_INSENSITIVE:ResultSet.TYPE_SCROLL_SENSITIVE,
                        isReadOnly?ResultSet.CONCUR_READ_ONLY:ResultSet.CONCUR_UPDATABLE);
                rc=executeResultSet(statement);
            }catch (SQLException e) {
                throw new RuntimeException(e);
                throw new StorageRuntimeException(e);
            }
        }

@@ -298,7 +311,7 @@
            try {
                return rc.next();
            }catch (SQLException e) {
                throw new RuntimeException(e);
                throw new StorageRuntimeException(e);
            }
        }

@@ -307,7 +320,7 @@
            try{
                return rc.getRow()>0;
            }catch (SQLException e) {
                throw new RuntimeException(e);
                throw new StorageRuntimeException(e);
            }
        }

@@ -319,7 +332,7 @@
            try{
                return ByteString.wrap(rc.getBytes("k"));
            }catch (SQLException e) {
                throw new RuntimeException(e);
                throw new StorageRuntimeException(e);
            }
        }

@@ -331,7 +344,7 @@
            try{
                return ByteString.wrap(rc.getBytes("v"));
            }catch (SQLException e) {
                throw new RuntimeException(e);
                throw new StorageRuntimeException(e);
            }
        }

@@ -343,19 +356,17 @@
            try{
                rc.deleteRow();
            }catch (SQLException e) {
                throw new RuntimeException(e);
                throw new StorageRuntimeException(e);
            }
        }

        @Override
        public void close() {
            if (rc!=null) {
                try{
                    rc.close();
                }catch (SQLException e) {
                    throw new RuntimeException(e);
                }
                rc = null;
            try{
                rc.close();
                statement.close();
            }catch (SQLException e) {
                throw new StorageRuntimeException(e);
            }
        }

@@ -366,7 +377,7 @@
                try{
                    rc.first();
                }catch (SQLException e) {
                    throw new RuntimeException(e);
                    throw new StorageRuntimeException(e);
                }
            }
            try{
@@ -379,7 +390,7 @@
                    }
                }while(rc.next());
            }catch (SQLException e) {
                throw new RuntimeException(e);
                throw new StorageRuntimeException(e);
            }
            return false;
        }
@@ -390,7 +401,7 @@
                try{
                    rc.first();
                }catch (SQLException e) {
                    throw new RuntimeException(e);
                    throw new StorageRuntimeException(e);
                }
            }
            if (!isDefined()){
@@ -406,7 +417,7 @@
                    }
                }while(rc.next());
            }catch (SQLException e) {
                throw new RuntimeException(e);
                throw new StorageRuntimeException(e);
            }
            return false;
        }
@@ -417,7 +428,7 @@
            try{
                return rc.last();
            }catch (SQLException e) {
                throw new RuntimeException(e);
                throw new StorageRuntimeException(e);
            }
        }

@@ -426,7 +437,7 @@
            try{
                rc.first();
            }catch (SQLException e) {
                throw new RuntimeException(e);
                throw new StorageRuntimeException(e);
            }
            if (!isDefined()){
                return false;
@@ -440,7 +451,7 @@
                    ct++;
                }while(rc.next());
            }catch (SQLException e) {
                throw new RuntimeException(e);
                throw new StorageRuntimeException(e);
            }
            return false;
        }
@@ -449,20 +460,23 @@
    @Override
    public Set<TreeName> listTrees() {
        final Set<TreeName> res=new HashSet<>();
        try(ResultSet rs = con.getMetaData().getTables(null, null, "OpenDJ%", new String[]{"TABLE"})) {
        try(final Connection con=getConnection();
            final ResultSet rs = con.getMetaData().getTables(null, null, "OpenDJ%", new String[]{"TABLE"})) {
            while (rs.next()) {
                res.add(TreeName.valueOf(rs.getString("TABLE_NAME").substring(6)));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } catch (Exception e) {
            throw new StorageRuntimeException(e);
        }
        return res;
    }
    

    private final class ImporterImpl implements Importer {
        final WriteableTransactionTransactionImpl tx;
		
        final Connection con;
        final ReadableTransactionImpl txr;
        final WriteableTransactionTransactionImpl txw;

        final Boolean isOpen;
        
        public ImporterImpl() {
@@ -474,15 +488,22 @@
                    throw new StorageRuntimeException(e);
                }
            }
            tx=new WriteableTransactionTransactionImpl();
            try {
                con = getConnection();
            }catch (Exception e){
                throw new StorageRuntimeException(e);
            }
            txr =new ReadableTransactionImpl(con);
            txw =new WriteableTransactionTransactionImpl(con);
        }
        
        @Override
        public void close() {
            try {
                con.commit();
                con.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
                throw new StorageRuntimeException(e);
            }
            if (!isOpen) {
                Storage.this.close();
@@ -491,22 +512,22 @@
        
        @Override
        public void clearTree(TreeName name) {
            tx.clearTree(name);
            txw.clearTree(name);
        }
        
        @Override
        public void put(TreeName treeName, ByteSequence key, ByteSequence value) {
            tx.put(treeName, key, value);
            txw.put(treeName, key, value);
        }
        
        @Override
        public ByteString read(TreeName treeName, ByteSequence key) {
            return tx.read(treeName, key);
            return txr.read(treeName, key);
        }
        
        @Override
        public SequentialCursor<ByteString, ByteString> openCursor(TreeName treeName) {
            return tx.openCursor(treeName);
            return txr.openCursor(treeName);
        }
    }