You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@commons.apache.org by tv...@apache.org on 2024/06/12 06:20:15 UTC

(commons-jcs) 01/03: Introduce JDBCConnection to handle commit/rollback properly

This is an automated email from the ASF dual-hosted git repository.

tv pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/commons-jcs.git

commit d20016e48cbef19d79e78000ba12069be7363b6a
Author: Thomas Vandahl <tv...@apache.org>
AuthorDate: Wed Jun 12 08:15:35 2024 +0200

    Introduce JDBCConnection to handle commit/rollback properly
---
 .../jcs3/auxiliary/disk/jdbc/JDBCConnection.java   | 107 ++++++++++
 .../jcs3/auxiliary/disk/jdbc/JDBCDiskCache.java    | 219 +++++++++++----------
 .../auxiliary/disk/jdbc/mysql/MySQLDiskCache.java  |  15 --
 3 files changed, 226 insertions(+), 115 deletions(-)

diff --git a/commons-jcs3-core/src/main/java/org/apache/commons/jcs3/auxiliary/disk/jdbc/JDBCConnection.java b/commons-jcs3-core/src/main/java/org/apache/commons/jcs3/auxiliary/disk/jdbc/JDBCConnection.java
new file mode 100644
index 00000000..4763265d
--- /dev/null
+++ b/commons-jcs3-core/src/main/java/org/apache/commons/jcs3/auxiliary/disk/jdbc/JDBCConnection.java
@@ -0,0 +1,107 @@
+package org.apache.commons.jcs3.auxiliary.disk.jdbc;
+
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   https://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+import java.sql.Connection;
+import java.sql.DatabaseMetaData;
+import java.sql.PreparedStatement;
+import java.sql.SQLException;
+
+/**
+ * Encapsulates connection status tracking and allows AutoClosing within JCS.
+ */
+public class JDBCConnection implements AutoCloseable
+{
+    /** Has this transaction already been committed? */
+    private boolean committed;
+
+    /** Save state of auto-commit */
+    private boolean autoCommit;
+
+    /** The wrapped connection instance */
+    private Connection connection;
+
+    /**
+     * Constructor
+     * @param con Connection object
+     * @throws SQLException if a database error occurs
+     */
+    public JDBCConnection(Connection con) throws SQLException
+    {
+        this.committed = false;
+        this.autoCommit = con.getAutoCommit();
+        this.connection = con;
+        this.connection.setAutoCommit(false);
+    }
+
+    /**
+     * @see java.sql.Connection#prepareStatement(java.lang.String)
+     */
+    public PreparedStatement prepareStatement(String sql) throws SQLException
+    {
+        return connection.prepareStatement(sql);
+    }
+
+    /**
+     * @see java.sql.Connection#commit()
+     */
+    public void commit() throws SQLException
+    {
+        connection.commit();
+        this.committed = true;
+    }
+
+    /**
+     * @see java.sql.Connection#rollback()
+     */
+    public void rollback() throws SQLException
+    {
+        connection.rollback();
+        this.committed = false;
+    }
+
+    /**
+     * @see java.sql.Connection#close()
+     */
+    @Override
+    public void close() throws SQLException
+    {
+        if (!this.committed)
+        {
+            connection.rollback();
+        }
+
+        connection.setAutoCommit(this.autoCommit);
+
+        // Justin Case
+        if (!connection.isClosed())
+        {
+            connection.close();
+        }
+    }
+
+    /**
+     * @see java.sql.Connection#getMetaData()
+     */
+    public DatabaseMetaData getMetaData() throws SQLException
+    {
+        return connection.getMetaData();
+    }
+}
diff --git a/commons-jcs3-core/src/main/java/org/apache/commons/jcs3/auxiliary/disk/jdbc/JDBCDiskCache.java b/commons-jcs3-core/src/main/java/org/apache/commons/jcs3/auxiliary/disk/jdbc/JDBCDiskCache.java
index 70c6cdbf..a160a9dc 100644
--- a/commons-jcs3-core/src/main/java/org/apache/commons/jcs3/auxiliary/disk/jdbc/JDBCDiskCache.java
+++ b/commons-jcs3-core/src/main/java/org/apache/commons/jcs3/auxiliary/disk/jdbc/JDBCDiskCache.java
@@ -20,7 +20,6 @@ package org.apache.commons.jcs3.auxiliary.disk.jdbc;
  */
 
 import java.io.IOException;
-import java.sql.Connection;
 import java.sql.DatabaseMetaData;
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
@@ -100,6 +99,28 @@ public class JDBCDiskCache<K, V>
     /** Tracks optimization */
     private TableState tableState;
 
+    /** SQL prepared statements */
+    private static final String SQL_DELETE_EXPIRED =
+        "delete from %s where IS_ETERNAL = ? and REGION = ? and ? > SYSTEM_EXPIRE_TIME_SECONDS";
+    private static final String SQL_GET_SIZE = "select count(*) from %s where REGION = ?";
+    private static final String SQL_DOES_ELEMENT_EXIST = "select CACHE_KEY from %s where REGION = ? and CACHE_KEY = ?";
+    private static final String SQL_REMOVE_ALL = "delete from %s where REGION = ?";
+    private static final String SQL_REMOVE_PARTIAL = "delete from %s where REGION = ? and CACHE_KEY like ?";
+    private static final String SQL_REMOVE_SINGLE = "delete from %s where REGION = ? and CACHE_KEY = ?";
+    private static final String SQL_GET_ELEMENT_MATCHING = "select ELEMENT from %s where REGION = ? and CACHE_KEY like ?";
+    private static final String SQL_GET_ELEMENT = "select ELEMENT from %s where REGION = ? and CACHE_KEY = ?";
+    private static final String SQL_INSERT_ROW = """
+        insert into %s\
+         (CACHE_KEY, REGION, ELEMENT, MAX_LIFE_SECONDS, IS_ETERNAL, CREATE_TIME, UPDATE_TIME_SECONDS,\
+         SYSTEM_EXPIRE_TIME_SECONDS)\s\
+         values (?, ?, ?, ?, ?, ?, ?, ?)""";
+
+    private static final String SQL_UPDATE_ROW = """
+            update %s\
+             set ELEMENT  = ?, CREATE_TIME = ?, UPDATE_TIME_SECONDS = ?,\s\
+             SYSTEM_EXPIRE_TIME_SECONDS = ?\s\
+             where CACHE_KEY = ? and REGION = ?""";
+
     /**
      * Constructs a JDBC Disk Cache for the provided cache attributes. The table state object is
      * used to mark deletions.
@@ -128,7 +149,7 @@ public class JDBCDiskCache<K, V>
      * @param pattern
      * @return String to use in the like query.
      */
-    public String constructLikeParameterFromPattern( final String pattern )
+    protected String constructLikeParameterFromPattern( final String pattern )
     {
         String likePattern = pattern.replace( ".+", "%" );
         likePattern = likePattern.replace( ".", "_" );
@@ -138,6 +159,17 @@ public class JDBCDiskCache<K, V>
         return likePattern;
     }
 
+    /**
+     * Get a connection from the data source and prepare it for use
+     *
+     * @return A database connection
+     * @throws SQLException if getting the connection goes wrong
+     */
+    protected JDBCConnection getConnection() throws SQLException
+    {
+        return new JDBCConnection(getDataSource().getConnection());
+    }
+
     /**
      * Removed the expired. (now - create time) &gt; max life seconds * 1000
      * <p>
@@ -147,21 +179,19 @@ public class JDBCDiskCache<K, V>
     {
         int deleted = 0;
 
-        try (Connection con = getDataSource().getConnection())
+        try (JDBCConnection con = getConnection())
         {
             // The shrinker thread might kick in before the table is created
             // So check if the table exists first
             final DatabaseMetaData dmd = con.getMetaData();
-            final ResultSet result = dmd.getTables(null, null,
-                    getJdbcDiskCacheAttributes().getTableName(), null);
+            final ResultSet result = dmd.getTables(null, null, getTableName(), null);
 
             if (result.next())
             {
                 getTableState().setState( TableState.DELETE_RUNNING );
                 final long now = System.currentTimeMillis() / 1000;
 
-                final String sql = String.format("delete from %s where IS_ETERNAL = ? and REGION = ?"
-                        + " and ? > SYSTEM_EXPIRE_TIME_SECONDS", getJdbcDiskCacheAttributes().getTableName());
+                final String sql = String.format(SQL_DELETE_EXPIRED, getTableName());
 
                 try (PreparedStatement psDelete = con.prepareStatement( sql ))
                 {
@@ -184,8 +214,7 @@ public class JDBCDiskCache<K, V>
             }
             else
             {
-                log.warn( "Trying to shrink non-existing table [{0}]",
-                        getJdbcDiskCacheAttributes().getTableName() );
+                log.warn( "Trying to shrink non-existing table [{0}]", getTableName() );
             }
         }
         catch ( final SQLException e )
@@ -210,12 +239,11 @@ public class JDBCDiskCache<K, V>
      * @param con a database connection
      * @return boolean
      */
-    protected boolean doesElementExist( final ICacheElement<K, V> ce, final Connection con )
+    protected boolean doesElementExist( final ICacheElement<K, V> ce, final JDBCConnection con )
     {
         boolean exists = false;
         // don't select the element, since we want this to be fast.
-        final String sqlS = String.format("select CACHE_KEY from %s where REGION = ? and CACHE_KEY = ?",
-                getJdbcDiskCacheAttributes().getTableName());
+        final String sqlS = String.format(SQL_DOES_ELEMENT_EXIST, getTableName());
 
         try (PreparedStatement psSelect = con.prepareStatement( sqlS ))
         {
@@ -275,6 +303,14 @@ public class JDBCDiskCache<K, V>
         return jdbcDiskCacheAttributes;
     }
 
+    /**
+     * @param jdbcDiskCacheAttributes The jdbcDiskCacheAttributes to set.
+     */
+    protected void setJdbcDiskCacheAttributes( final JDBCDiskCacheAttributes jdbcDiskCacheAttributes )
+    {
+        this.jdbcDiskCacheAttributes = jdbcDiskCacheAttributes;
+    }
+
     /**
      * Return the keys in this cache.
      * <p>
@@ -298,10 +334,9 @@ public class JDBCDiskCache<K, V>
         int size = 0;
 
         // region, key
-        final String selectString = String.format("select count(*) from %s where REGION = ?",
-                getJdbcDiskCacheAttributes().getTableName());
+        final String selectString = String.format(SQL_GET_SIZE, getTableName());
 
-        try (Connection con = getDataSource().getConnection())
+        try (JDBCConnection con = getConnection())
         {
             try (PreparedStatement psSelect = con.prepareStatement( selectString ))
             {
@@ -340,7 +375,7 @@ public class JDBCDiskCache<K, V>
         elems.add(new StatElement<>( "Update Count", updateCount ) );
         elems.add(new StatElement<>( "Get Count", getCount ) );
         elems.add(new StatElement<>( "Get Matching Count", getMatchingCount ) );
-        elems.add(new StatElement<>( "DB URL", getJdbcDiskCacheAttributes().getUrl()) );
+        elems.add(new StatElement<>( "DB URL", getDiskLocation()) );
 
         stats.setStatElements( elems );
 
@@ -354,12 +389,8 @@ public class JDBCDiskCache<K, V>
      */
     protected String getTableName()
     {
-        String name = "UNDEFINED";
-        if ( this.getJdbcDiskCacheAttributes() != null )
-        {
-            name = this.getJdbcDiskCacheAttributes().getTableName();
-        }
-        return name;
+        return this.jdbcDiskCacheAttributes != null ?
+                this.jdbcDiskCacheAttributes.getTableName() : "UNDEFINED";
     }
 
     /**
@@ -370,6 +401,14 @@ public class JDBCDiskCache<K, V>
         return tableState;
     }
 
+    /**
+     * @param tableState The tableState to set.
+     */
+    public void setTableState( final TableState tableState )
+    {
+        this.tableState = tableState;
+    }
+
     /**
      * If test before insert it true, we check to see if the element exists. If the element exists
      * we will update. Otherwise, we try inserting.  If this fails because the item exists, we will
@@ -379,7 +418,7 @@ public class JDBCDiskCache<K, V>
      * @param con
      * @param element
      */
-    private void insertOrUpdate( final ICacheElement<K, V> ce, final Connection con, final byte[] element )
+    private void insertOrUpdate( final ICacheElement<K, V> ce, final JDBCConnection con, final byte[] element )
     {
         boolean exists = false;
 
@@ -410,14 +449,10 @@ public class JDBCDiskCache<K, V>
      * @param element
      * @return true if the insertion fails because the record exists.
      */
-    private boolean insertRow( final ICacheElement<K, V> ce, final Connection con, final byte[] element )
+    private boolean insertRow( final ICacheElement<K, V> ce, final JDBCConnection con, final byte[] element )
     {
         boolean exists = false;
-        final String sqlI = String.format("""
-        	insert into %s\
-        	 (CACHE_KEY, REGION, ELEMENT, MAX_LIFE_SECONDS, IS_ETERNAL, CREATE_TIME, UPDATE_TIME_SECONDS,\
-        	 SYSTEM_EXPIRE_TIME_SECONDS)\s\
-        	 values (?, ?, ?, ?, ?, ?, ?, ?)""", getJdbcDiskCacheAttributes().getTableName());
+        final String sqlI = String.format(SQL_INSERT_ROW, getTableName());
 
         try (PreparedStatement psInsert = con.prepareStatement( sqlI ))
         {
@@ -459,6 +494,42 @@ public class JDBCDiskCache<K, V>
         return exists;
     }
 
+    /**
+     * This updates a row in the database.
+     * <p>
+     * @param ce
+     * @param con
+     * @param element
+     */
+    private void updateRow( final ICacheElement<K, V> ce, final JDBCConnection con, final byte[] element )
+    {
+        final String sqlU = String.format(SQL_UPDATE_ROW, getTableName());
+
+        try (PreparedStatement psUpdate = con.prepareStatement( sqlU ))
+        {
+            psUpdate.setBytes( 1, element );
+
+            final Timestamp createTime = new Timestamp( ce.getElementAttributes().getCreateTime() );
+            psUpdate.setTimestamp( 2, createTime );
+
+            final long now = System.currentTimeMillis() / 1000;
+            psUpdate.setLong( 3, now );
+
+            final long expireTime = now + ce.getElementAttributes().getMaxLife();
+            psUpdate.setLong( 4, expireTime );
+
+            psUpdate.setString( 5, (String) ce.getKey() );
+            psUpdate.setString( 6, this.getCacheName() );
+            psUpdate.execute();
+
+            log.debug( "ran update {0}", sqlU );
+        }
+        catch ( final SQLException e )
+        {
+            log.error( "Error executing update sql [{0}]", sqlU, e );
+        }
+    }
+
     /** Shuts down the pool */
     @Override
     public void processDispose()
@@ -501,10 +572,9 @@ public class JDBCDiskCache<K, V>
         ICacheElement<K, V> obj = null;
 
         // region, key
-        final String selectString = String.format("select ELEMENT from %s where REGION = ? and CACHE_KEY = ?",
-                getJdbcDiskCacheAttributes().getTableName());
+        final String selectString = String.format(SQL_GET_ELEMENT, getTableName());
 
-        try (Connection con = getDataSource().getConnection())
+        try (JDBCConnection con = getConnection())
         {
             try (PreparedStatement psSelect = con.prepareStatement( selectString ))
             {
@@ -534,6 +604,8 @@ public class JDBCDiskCache<K, V>
                     }
                 }
             }
+
+            con.commit();
         }
         catch ( final SQLException sqle )
         {
@@ -566,10 +638,9 @@ public class JDBCDiskCache<K, V>
         final Map<K, ICacheElement<K, V>> results = new HashMap<>();
 
         // region, key
-        final String selectString = String.format("select ELEMENT from %s where REGION = ? and CACHE_KEY like ?",
-                getJdbcDiskCacheAttributes().getTableName());
+        final String selectString = String.format(SQL_GET_ELEMENT_MATCHING, getTableName());
 
-        try (Connection con = getDataSource().getConnection())
+        try (JDBCConnection con = getConnection())
         {
             try (PreparedStatement psSelect = con.prepareStatement( selectString ))
             {
@@ -597,6 +668,8 @@ public class JDBCDiskCache<K, V>
                     }
                 }
             }
+
+            con.commit();
         }
         catch ( final SQLException sqle )
         {
@@ -618,13 +691,11 @@ public class JDBCDiskCache<K, V>
     protected boolean processRemove( final K key )
     {
         // remove single item.
-        final String sqlSingle = String.format("delete from %s where REGION = ? and CACHE_KEY = ?",
-                getJdbcDiskCacheAttributes().getTableName());
+        final String sqlSingle = String.format(SQL_REMOVE_SINGLE, getTableName());
         // remove all keys of the same name group.
-        final String sqlPartial = String.format("delete from %s where REGION = ? and CACHE_KEY like ?",
-                getJdbcDiskCacheAttributes().getTableName());
+        final String sqlPartial = String.format(SQL_REMOVE_PARTIAL, getTableName());
 
-        try (Connection con = getDataSource().getConnection())
+        try (JDBCConnection con = getConnection())
         {
             boolean partial = key.toString().endsWith(ICache.NAME_COMPONENT_DELIMITER);
             String sql = partial ? sqlPartial : sqlSingle;
@@ -644,6 +715,7 @@ public class JDBCDiskCache<K, V>
                 psSelect.executeUpdate();
 
                 setAlive(true);
+                con.commit();
             }
             catch ( final SQLException e )
             {
@@ -669,10 +741,9 @@ public class JDBCDiskCache<K, V>
         // it should never get here from the abstract disk cache.
         if ( this.jdbcDiskCacheAttributes.isAllowRemoveAll() )
         {
-            final String sql = String.format("delete from %s where REGION = ?",
-                    getJdbcDiskCacheAttributes().getTableName());
+            final String sql = String.format(SQL_REMOVE_ALL, getTableName());
 
-            try (Connection con = getDataSource().getConnection())
+            try (JDBCConnection con = getConnection())
             {
                 try (PreparedStatement psDelete = con.prepareStatement( sql ))
                 {
@@ -685,6 +756,8 @@ public class JDBCDiskCache<K, V>
                     log.error( "Problem creating statement.", e );
                     setAlive(false);
                 }
+
+                con.commit();
             }
             catch ( final SQLException e )
             {
@@ -713,7 +786,7 @@ public class JDBCDiskCache<K, V>
 
         log.debug( "updating, ce = {0}", ce );
 
-        try (Connection con = getDataSource().getConnection())
+        try (JDBCConnection con = getConnection())
         {
             log.debug( "Putting [{0}] on disk.", ce::getKey);
 
@@ -726,6 +799,8 @@ public class JDBCDiskCache<K, V>
             {
                 log.error( "Could not serialize element", e );
             }
+
+            con.commit();
         }
         catch ( final SQLException e )
         {
@@ -741,22 +816,6 @@ public class JDBCDiskCache<K, V>
         // nothing
     }
 
-    /**
-     * @param jdbcDiskCacheAttributes The jdbcDiskCacheAttributes to set.
-     */
-    protected void setJdbcDiskCacheAttributes( final JDBCDiskCacheAttributes jdbcDiskCacheAttributes )
-    {
-        this.jdbcDiskCacheAttributes = jdbcDiskCacheAttributes;
-    }
-
-    /**
-     * @param tableState The tableState to set.
-     */
-    public void setTableState( final TableState tableState )
-    {
-        this.tableState = tableState;
-    }
-
     /**
      * For debugging.
      * <p>
@@ -767,44 +826,4 @@ public class JDBCDiskCache<K, V>
     {
         return this.getStats();
     }
-
-    /**
-     * This updates a row in the database.
-     * <p>
-     * @param ce
-     * @param con
-     * @param element
-     */
-    private void updateRow( final ICacheElement<K, V> ce, final Connection con, final byte[] element )
-    {
-        final String sqlU = String.format("""
-        	update %s\
-        	 set ELEMENT  = ?, CREATE_TIME = ?, UPDATE_TIME_SECONDS = ?,\s\
-        	 SYSTEM_EXPIRE_TIME_SECONDS = ?\s\
-        	 where CACHE_KEY = ? and REGION = ?""", getJdbcDiskCacheAttributes().getTableName());
-
-        try (PreparedStatement psUpdate = con.prepareStatement( sqlU ))
-        {
-            psUpdate.setBytes( 1, element );
-
-            final Timestamp createTime = new Timestamp( ce.getElementAttributes().getCreateTime() );
-            psUpdate.setTimestamp( 2, createTime );
-
-            final long now = System.currentTimeMillis() / 1000;
-            psUpdate.setLong( 3, now );
-
-            final long expireTime = now + ce.getElementAttributes().getMaxLife();
-            psUpdate.setLong( 4, expireTime );
-
-            psUpdate.setString( 5, (String) ce.getKey() );
-            psUpdate.setString( 6, this.getCacheName() );
-            psUpdate.execute();
-
-            log.debug( "ran update {0}", sqlU );
-        }
-        catch ( final SQLException e )
-        {
-            log.error( "Error executing update sql [{0}]", sqlU, e );
-        }
-    }
 }
diff --git a/commons-jcs3-core/src/main/java/org/apache/commons/jcs3/auxiliary/disk/jdbc/mysql/MySQLDiskCache.java b/commons-jcs3-core/src/main/java/org/apache/commons/jcs3/auxiliary/disk/jdbc/mysql/MySQLDiskCache.java
index fe004b3a..ec1668f8 100644
--- a/commons-jcs3-core/src/main/java/org/apache/commons/jcs3/auxiliary/disk/jdbc/mysql/MySQLDiskCache.java
+++ b/commons-jcs3-core/src/main/java/org/apache/commons/jcs3/auxiliary/disk/jdbc/mysql/MySQLDiskCache.java
@@ -64,21 +64,6 @@ public class MySQLDiskCache<K, V>
         log.debug( "MySQLDiskCacheAttributes = {0}", attributes );
     }
 
-    /**
-     * @param pattern
-     * @return String to use in the like query.
-     */
-    @Override
-    public String constructLikeParameterFromPattern( final String pattern )
-    {
-        String likePattern = pattern.replace( ".+", "%" );
-        likePattern = likePattern.replace( ".", "_" );
-
-        log.debug( "pattern = [{0}]", likePattern );
-
-        return likePattern;
-    }
-
     /**
      * Removed the expired. (now - create time) &gt; max life seconds * 1000
      * <p>