add retry for possibly transient SQL exceptions
diff --git a/core/org.eclipse.smila.jdbc/code/src/org/eclipse/smila/jdbc/internal/JdbcWriter.java b/core/org.eclipse.smila.jdbc/code/src/org/eclipse/smila/jdbc/internal/JdbcWriter.java
index c3d7914..3080163 100644
--- a/core/org.eclipse.smila.jdbc/code/src/org/eclipse/smila/jdbc/internal/JdbcWriter.java
+++ b/core/org.eclipse.smila.jdbc/code/src/org/eclipse/smila/jdbc/internal/JdbcWriter.java
@@ -4,7 +4,8 @@
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.SQLException;
-import java.sql.SQLSyntaxErrorException;
+import java.sql.SQLNonTransientConnectionException;
+import java.sql.SQLNonTransientException;
import java.sql.Types;
import java.util.List;
import java.util.Map;
@@ -21,6 +22,9 @@
/** Maximum time to wait for a queue element to become available. in ms. */
public static final long MAX_WAIT_TIME = 3000;
+ /** Maximum number of tries to write to the database in case of connection errors. */
+ private static final int MAX_WRITE_COUNT = 3;
+
private final Log _log = LogFactory.getLog(getClass());
final String _name;
@@ -75,28 +79,49 @@
}
private void writeToDB(final Map<String, Object> writeParams) {
- try {
- final Connection con = _jdbcWriterService.getConnection(writeParams);
- final String sql = (String) writeParams.get(JdbcWriterServiceImpl.PARAM_STATEMENT);
- final List<Value> values = (List<Value>) writeParams.get(JdbcWriterServiceImpl.PARAM_VALUES);
- try (final PreparedStatement stmt = con.prepareStatement(sql)) {
- final ParameterMetaData paramTypes = stmt.getParameterMetaData();
- // PreparedStatement uses list of parameters
- for (int j = 0; j < values.size(); j++) {
- final Any param = values.get(j);
- if (param == null) {
- stmt.setNull(j + 1, getNullType(paramTypes, j + 1));
- } else {
- stmt.setObject(j + 1, param.asValue().getObject()); // PreparedStatement params start with '1'!
- }
-
- }
- stmt.execute();
+ @SuppressWarnings("unchecked")
+ final List<Value> values = (List<Value>) writeParams.get(JdbcWriterServiceImpl.PARAM_VALUES);
+ final String sql = (String) writeParams.get(JdbcWriterServiceImpl.PARAM_STATEMENT);
+ for (int retryCount = 0; retryCount < MAX_WRITE_COUNT; retryCount++) {
+ try {
+ final Connection con = _jdbcWriterService.getConnection(writeParams);
+ writeToDB(con, sql, values);
+ return;
+ } catch (final SQLNonTransientConnectionException e) {
+ // retry does not make sense, but the connection should be recreated.
+ handleSqlException(e, writeParams);
+ return;
+ } catch (final SQLNonTransientException e) {
+ // not a connection issue, so no retry and the connection can be kept.
+ handleSqlException(e, null);
+ return;
+ } catch (final SQLException e) {
+ // drop the connection, maybe retry
+ handleSqlException(e, writeParams);
}
- } catch (final SQLSyntaxErrorException e) {
- _log.error("Error writing entry for queue " + _name, e);
- } catch (final SQLException e) {
- _log.error("Error writing entry for queue " + _name, e);
+ }
+ }
+
+ private void writeToDB(final Connection con, final String sql, final List<Value> values) throws SQLException {
+ try (final PreparedStatement stmt = con.prepareStatement(sql)) {
+ final ParameterMetaData paramTypes = stmt.getParameterMetaData();
+ // PreparedStatement uses list of parameters
+ for (int j = 0; j < values.size(); j++) {
+ final Any param = values.get(j);
+ if (param == null) {
+ stmt.setNull(j + 1, getNullType(paramTypes, j + 1));
+ } else {
+ stmt.setObject(j + 1, param.asValue().getObject()); // PreparedStatement params start with '1'!
+ }
+ }
+ stmt.execute();
+ }
+ }
+
+ /** log the exception. if writeParams is not null: drop the connection. */
+ private void handleSqlException(final SQLException e, final Map<String, Object> writeParams) {
+ _log.warn("Error writing entry for queue " + _name, e);
+ if (writeParams != null) {
_jdbcWriterService.dropConnection(writeParams);
}
}
