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) > 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) > max life seconds * 1000
* <p>