From 0c5b4321efd5cc7b3851b452f141459085360960 Mon Sep 17 00:00:00 2001 From: Simon Rettberg Date: Mon, 22 May 2023 11:15:52 +0200 Subject: Update Database classes to match dmsd --- .../java/org/openslx/imagemaster/db/Database.java | 27 ++++---- .../openslx/imagemaster/db/MysqlConnection.java | 68 ++++++++++++------- .../org/openslx/imagemaster/db/MysqlStatement.java | 77 ++++++++++++++-------- .../imagemaster/db/mappers/DbPendingSatellite.java | 4 +- 4 files changed, 111 insertions(+), 65 deletions(-) diff --git a/src/main/java/org/openslx/imagemaster/db/Database.java b/src/main/java/org/openslx/imagemaster/db/Database.java index 80997ea..340ccd4 100644 --- a/src/main/java/org/openslx/imagemaster/db/Database.java +++ b/src/main/java/org/openslx/imagemaster/db/Database.java @@ -44,8 +44,7 @@ public class Database * the class. In most cases that happens when the class is being * accessed for the first time during run time. */ - static - { + static { // Load connection info from class (TODO: Make pretty) Properties properties = new Properties(); try { @@ -103,7 +102,14 @@ public class Database } if ( !busyConnections.add( con ) ) throw new RuntimeException( "Tried to hand out a busy connection!" ); - return con; + try { + // By convention in our program we don't want auto commit + con.setAutoCommit( false ); + return con; + } catch ( SQLException e ) { + con.release(); + continue; + } } // No pooled connection if ( busyConnections.size() > 20 ) { @@ -145,16 +151,6 @@ public class Database pool.add( connection ); } - /** - * Return true if the given sql exception is "duplicate entry XXXX for key YYYY. - */ - public static boolean isDuplicateKeyException( SQLException e ) - { - return e != null && e.getErrorCode() == 1062; - } - - // - public static void printCharsetInformation() { LOGGER.info( "MySQL charset related variables:" ); @@ -182,4 +178,9 @@ public class Database LOGGER.info( "Busy: " + busyConnections.size() ); } + public static boolean isDuplicateKeyException( SQLException e ) + { + return e != null && e.getErrorCode() == 1062; + } + }// end class diff --git a/src/main/java/org/openslx/imagemaster/db/MysqlConnection.java b/src/main/java/org/openslx/imagemaster/db/MysqlConnection.java index 443fce0..d9fe4f2 100644 --- a/src/main/java/org/openslx/imagemaster/db/MysqlConnection.java +++ b/src/main/java/org/openslx/imagemaster/db/MysqlConnection.java @@ -8,9 +8,10 @@ import java.util.List; import org.apache.logging.log4j.LogManager; import org.apache.logging.log4j.Logger; -public class MysqlConnection implements AutoCloseable { +public class MysqlConnection implements AutoCloseable +{ - private static final Logger LOGGER = LogManager.getLogger(MysqlConnection.class); + private static final Logger LOGGER = LogManager.getLogger( MysqlConnection.class ); private static final int CONNECTION_TIMEOUT_MS = 5 * 60 * 1000; @@ -22,62 +23,81 @@ public class MysqlConnection implements AutoCloseable { private List openStatements = new ArrayList<>(); - MysqlConnection(Connection rawConnection) { + MysqlConnection( Connection rawConnection ) + { this.rawConnection = rawConnection; } - - public MysqlStatement prepareStatement(String sql) throws SQLException { - return prepareStatement( sql, false ); - } - public MysqlStatement prepareStatement(String sql, boolean getKeys) throws SQLException { - if (!sql.startsWith("SELECT")) + public MysqlStatement prepareStatement( String sql ) throws SQLException + { + if ( !sql.startsWith( "SELECT" ) && !sql.startsWith( "DESCRIBE" ) && !sql.startsWith( "SHOW" ) ) { hasPendingQueries = true; - MysqlStatement statement = new MysqlStatement(rawConnection, sql, getKeys); - openStatements.add(statement); + } + MysqlStatement statement = new MysqlStatement( rawConnection, sql ); + openStatements.add( statement ); return statement; } - public void commit() throws SQLException { + public void commit() throws SQLException + { rawConnection.commit(); hasPendingQueries = false; } - public void rollback() throws SQLException { + public void rollback() throws SQLException + { rawConnection.rollback(); hasPendingQueries = false; } - boolean isValid() { + boolean isValid() + { return System.currentTimeMillis() < deadline; } @Override - public void close() { - if (hasPendingQueries) { - LOGGER.warn("Mysql connection had uncommited queries on .close()"); + public void close() + { + if ( hasPendingQueries ) { + LOGGER.warn( "Mysql connection had uncommited queries on .close()", + new RuntimeException( "Stack trace" ) ); + for ( MysqlStatement s : openStatements ) { + LOGGER.info( s.getQuery() ); + } hasPendingQueries = false; } try { rawConnection.rollback(); - } catch (SQLException e) { - LOGGER.warn("Rolling back uncommited queries failed!", e); + } catch ( SQLException e ) { + LOGGER.warn( "Rolling back uncommited queries failed!", e ); } - if (!openStatements.isEmpty()) { - for (MysqlStatement statement : openStatements) { + if ( !openStatements.isEmpty() ) { + for ( MysqlStatement statement : openStatements ) { statement.close(); } openStatements.clear(); } - Database.returnConnection(this); + try { + rawConnection.rollback(); + rawConnection.setAutoCommit( true ); + } catch ( SQLException e ) { + LOGGER.warn( "Rolling back uncommited queries failed!", e ); + } + Database.returnConnection( this ); } - void release() { + void release() + { try { rawConnection.close(); - } catch (SQLException e) { + } catch ( SQLException e ) { // Nothing meaningful to do } } + void setAutoCommit( boolean b ) throws SQLException + { + rawConnection.setAutoCommit( b ); + } + } diff --git a/src/main/java/org/openslx/imagemaster/db/MysqlStatement.java b/src/main/java/org/openslx/imagemaster/db/MysqlStatement.java index 3dda36a..f2b80a3 100644 --- a/src/main/java/org/openslx/imagemaster/db/MysqlStatement.java +++ b/src/main/java/org/openslx/imagemaster/db/MysqlStatement.java @@ -5,6 +5,7 @@ import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; +import java.sql.SQLFeatureNotSupportedException; import java.sql.Statement; import java.util.ArrayList; import java.util.HashMap; @@ -15,8 +16,8 @@ import java.util.Map; /** * Class for creating {@link PreparedStatement}s with named parameters. Based on * Named Parameters for PreparedStatement + * "http://www.javaworld.com/article/2077706/core-java/named-parameters-for-preparedstatement.html?page=2">Named + * Parameters for PreparedStatement */ public class MysqlStatement implements Closeable { @@ -29,7 +30,7 @@ public class MysqlStatement implements Closeable private final List openResultSets = new ArrayList<>(); - MysqlStatement( Connection con, String sql, boolean getKeys ) throws SQLException + MysqlStatement( Connection con, String sql ) throws SQLException { PreparsedQuery query; synchronized ( cache ) { @@ -42,11 +43,12 @@ public class MysqlStatement implements Closeable } } this.query = query; - if ( getKeys ) { - this.statement = con.prepareStatement( query.sql, Statement.RETURN_GENERATED_KEYS ); - } else { - this.statement = con.prepareStatement( query.sql ); - } + this.statement = con.prepareStatement( query.sql, Statement.RETURN_GENERATED_KEYS ); + } + + public String getQuery() + { + return query.sql; } /** @@ -207,6 +209,41 @@ public class MysqlStatement implements Closeable return statement.executeUpdate(); } + /** + * Retrieves any auto-generated keys created as a result of executing this + * Statement object. If this Statement object did + * not generate any keys, an empty ResultSet + * object is returned. + * + *

+ * Note:If the columns which represent the auto-generated keys were not specified, + * the JDBC driver implementation will determine the columns which best represent the + * auto-generated keys. + * + * @return a ResultSet object containing the auto-generated key(s) + * generated by the execution of this Statement object + * @exception SQLException if a database access error occurs or + * this method is called on a closed Statement + * @throws SQLFeatureNotSupportedException if the JDBC driver does not support this method + */ + public ResultSet getGeneratedKeys() throws SQLException + { + ResultSet rs = statement.getGeneratedKeys(); + openResultSets.add( rs ); + return rs; + } + + public int lastInsertId() throws SQLException + { + int result = -1; + try ( ResultSet rs = statement.getGeneratedKeys() ) { + if ( rs.next() ) { + result = rs.getInt( 1 ); + } + } + return result; + } + /** * Closes the statement. * @@ -222,6 +259,11 @@ public class MysqlStatement implements Closeable // } } + try { + statement.cancel(); + } catch ( SQLException e ) { + // Nothing to do + } try { statement.close(); } catch ( SQLException e ) { @@ -252,23 +294,6 @@ public class MysqlStatement implements Closeable return statement.executeBatch(); } - /** - * Get the generated key from the last insert. Assumes that one row was inserted, and the - * generated key is an int. - * - * @return the generated key - * @throws SQLException if no key was generated by this statement - */ - public int getGeneratedKeys() throws SQLException - { - try ( ResultSet generatedKeys = statement.getGeneratedKeys() ) { - if ( generatedKeys.next() ) { - return generatedKeys.getInt( 1 ); - } - throw new SQLException( "Could not obtain generated key" ); - } - } - // static methods private static PreparsedQuery parse( String query ) @@ -319,7 +344,7 @@ public class MysqlStatement implements Closeable indexList = new ArrayList<>(); paramMap.put( name, indexList ); } - indexList.add( new Integer( index ) ); + indexList.add( Integer.valueOf( index ) ); index++; } diff --git a/src/main/java/org/openslx/imagemaster/db/mappers/DbPendingSatellite.java b/src/main/java/org/openslx/imagemaster/db/mappers/DbPendingSatellite.java index 03be323..f1d7160 100644 --- a/src/main/java/org/openslx/imagemaster/db/mappers/DbPendingSatellite.java +++ b/src/main/java/org/openslx/imagemaster/db/mappers/DbPendingSatellite.java @@ -24,14 +24,14 @@ public class DbPendingSatellite try ( MysqlConnection connection = Database.getConnection() ) { MysqlStatement stmt = connection.prepareStatement( "INSERT INTO satellite" + " (dateline, userid, organizationid, satellitename, addresses, publickey)" - + " VALUES (UNIX_TIMESTAMP(), :userid, :organizationid, :satellitename, :addresses, :pubkey)", true ); + + " VALUES (UNIX_TIMESTAMP(), :userid, :organizationid, :satellitename, :addresses, :pubkey)" ); stmt.setString( "userid", user.userId ); stmt.setString( "organizationid", user.organizationId ); stmt.setString( "satellitename", displayName ); stmt.setString( "addresses", Json.serialize( address ) ); stmt.setString( "pubkey", Json.serialize( new KeyWrapper( modulus, exponent ) ) ); stmt.executeUpdate(); - int key = stmt.getGeneratedKeys(); + int key = stmt.lastInsertId(); connection.commit(); return key; } catch ( SQLException e ) { -- cgit v1.2.3-55-g7522