Update Database classes to match dmsdHEADmaster

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<MysqlStatement> 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
  * <a href=
- * "http://www.javaworld.com/article/2077706/core-java/named-parameters-for-preparedstatement.html?page=2"
- * >Named Parameters for PreparedStatement</a>
+ * "http://www.javaworld.com/article/2077706/core-java/named-parameters-for-preparedstatement.html?page=2">Named
+ * Parameters for PreparedStatement</a>
  */
 public class MysqlStatement implements Closeable
 {
@@ -29,7 +30,7 @@ public class MysqlStatement implements Closeable
 
 	private final List<ResultSet> 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;
 	}
 
 	/**
@@ -208,6 +210,41 @@ public class MysqlStatement implements Closeable
 	}
 
 	/**
+	 * Retrieves any auto-generated keys created as a result of executing this
+	 * <code>Statement</code> object. If this <code>Statement</code> object did
+	 * not generate any keys, an empty <code>ResultSet</code>
+	 * object is returned.
+	 *
+	 * <p>
+	 * <B>Note:</B>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 <code>ResultSet</code> object containing the auto-generated key(s)
+	 *         generated by the execution of this <code>Statement</code> object
+	 * @exception SQLException if a database access error occurs or
+	 *               this method is called on a closed <code>Statement</code>
+	 * @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.
 	 * 
 	 * @see Statement#close()
@@ -223,6 +260,11 @@ public class MysqlStatement implements Closeable
 			}
 		}
 		try {
+			statement.cancel();
+		} catch ( SQLException e ) {
+			// Nothing to do
+		}
+		try {
 			statement.close();
 		} catch ( SQLException e ) {
 			// Nothing to do
@@ -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 ) {