# HG changeset patch # User Sascha L. Teichmann # Date 1299256983 0 # Node ID 25d472a67a9fc7a5848958821feeaa58b3d91e4f # Parent 940ed629419f94f196bf3fecd56fb699c2d17555 Reduce the code repetition and the complexity of the backend. artifacts/trunk@1400 c6561f87-3c4e-4783-a992-168aeb5c3f6f diff -r 940ed629419f -r 25d472a67a9f ChangeLog --- a/ChangeLog Fri Mar 04 14:06:56 2011 +0000 +++ b/ChangeLog Fri Mar 04 16:43:03 2011 +0000 @@ -1,3 +1,17 @@ +2011-03-04 Sascha L. Teichmann + + * artifact-database/src/main/java/de/intevation/artifactdatabase/SQLExecutor.java: + New. Helper class to simplify the database connection stuff like + get a connection, prepare statements, commit, rollback in + error cases and so on. This reduces the code repetition + and complexity in the backend significantly. + + * artifact-database/src/main/java/de/intevation/artifactdatabase/Backend.java: + Use the new helper. (Should really have done this before!) + + * artifacts/src/main/java/de/intevation/artifacts/ArtifactCollectionFactory.java: + Removed empty line at end of file. + 2011-03-04 Sascha L. Teichmann * artifact-database/src/main/resources/sql/org-h2-driver.properties, diff -r 940ed629419f -r 25d472a67a9f artifact-database/src/main/java/de/intevation/artifactdatabase/Backend.java --- a/artifact-database/src/main/java/de/intevation/artifactdatabase/Backend.java Fri Mar 04 14:06:56 2011 +0000 +++ b/artifact-database/src/main/java/de/intevation/artifactdatabase/Backend.java Fri Mar 04 16:43:03 2011 +0000 @@ -18,9 +18,6 @@ import de.intevation.artifacts.common.utils.XMLUtils; -import java.sql.Connection; -import java.sql.PreparedStatement; -import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Timestamp; import java.sql.Types; @@ -28,13 +25,10 @@ import java.util.ArrayList; import java.util.Date; -import javax.sql.DataSource; - import org.apache.log4j.Logger; import org.w3c.dom.Document; - /** * The backend implements the low level layer used to store artifacts * in a SQL database. @@ -429,78 +423,63 @@ * @param loader The loader which processes the raw database data. * @return The object created by the loader. */ - public Object loadArtifact(String identifer, ArtifactLoader loader) { - + public Object loadArtifact( + final String identifer, + final ArtifactLoader loader + ) { if (!StringUtils.checkUUID(identifer)) { return null; } - Connection connection = null; - PreparedStatement stmnt_load = null; - ResultSet load_result = null; - - DataSource dataSource = DBConnection.getDataSource(); - try { - connection = dataSource.getConnection(); - stmnt_load = connection.prepareStatement(SQL_LOAD_BY_GID); - stmnt_load.setString(1, identifer); - - load_result = stmnt_load.executeQuery(); - - if (!load_result.next()) { - return null; - } - - int id = load_result.getInt(1); - long ttlX = load_result.getLong(3); - - Long ttl = load_result.wasNull() ? null : Long.valueOf(ttlX); - - if (ttl != null) { // real time to life - long last_access = load_result.getTimestamp(2).getTime(); - if (last_access + ttlX < System.currentTimeMillis()) { - artifactOutdated(id); - return null; - } - } - - String factoryName = load_result.getString(4); + final Object [] loaded = new Object[1]; - if (factoryLookup == null) { - logger.error("factory lookup == null"); - return null; - } - - ArtifactFactory factory = factoryLookup - .getArtifactFactory(factoryName); - - if (factory == null) { - logger.error("factory '" + factoryName + "' not found"); - return null; - } - - byte [] bytes = load_result.getBytes(5); + SQLExecutor exec = new SQLExecutor() { + public boolean doIt() throws SQLException { + prepareStatement(SQL_LOAD_BY_GID); + stmnt.setString(1, identifer); - return loader.load(factory, ttl, bytes, id); - } - catch (SQLException sqle) { - logger.error(sqle.getLocalizedMessage(), sqle); - } - finally { - if (load_result != null) { - try { load_result.close(); } - catch (SQLException sqle) {} + result = stmnt.executeQuery(); + + if (!result.next()) { + return false; + } + + int id = result.getInt(1); + long ttlX = result.getLong(3); + + Long ttl = result.wasNull() ? null : Long.valueOf(ttlX); + + if (ttl != null) { // real time to life + long last_access = result.getTimestamp(2).getTime(); + if (last_access + ttlX < System.currentTimeMillis()) { + artifactOutdated(id); + return false; + } + } + + String factoryName = result.getString(4); + + if (factoryLookup == null) { + logger.error("factory lookup == null"); + return false; + } + + ArtifactFactory factory = factoryLookup + .getArtifactFactory(factoryName); + + if (factory == null) { + logger.error("factory '" + factoryName + "' not found"); + return false; + } + + byte [] bytes = result.getBytes(5); + + loaded[0] = loader.load(factory, ttl, bytes, id); + return true; } - if (stmnt_load != null) { - try { load_result.close(); } - catch (SQLException sqle) {} - } - if (connection != null) { - try { connection.close(); } - catch (SQLException sqle) {} - } - } - return null; + }; + + return exec.runRead() ? loaded[0] : null; } /** @@ -546,28 +525,22 @@ * @return The database id of the stored/updated artifact. */ protected int storeOrReplaceDatabase( - Artifact artifact, - ArtifactFactory factory, - Long ttl + final Artifact artifact, + final ArtifactFactory factory, + final Long ttl ) { - String uuid = artifact.identifier(); + final String uuid = artifact.identifier(); if (!StringUtils.checkUUID(uuid)) { throw new RuntimeException("No valid UUID"); } - Connection connection = null; - PreparedStatement stmnt = null; - ResultSet result = null; + final int [] id = new int[1]; - DataSource dataSource = DBConnection.getDataSource(); - try { - connection = dataSource.getConnection(); - try { - connection.setAutoCommit(false); + SQLExecutor exec = new SQLExecutor() { + public boolean doIt() throws SQLException { - stmnt = connection.prepareStatement(SQL_GET_ID); - + prepareStatement(SQL_GET_ID); stmnt.setString(1, uuid); result = stmnt.executeQuery(); @@ -575,13 +548,10 @@ ? Integer.valueOf(result.getInt(1)) : null; - result.close(); result = null; - stmnt.close(); stmnt = null; + reset(); if (ID != null) { // already in database - int id = ID.intValue(); - - stmnt = connection.prepareStatement(SQL_REPLACE); + prepareStatement(SQL_REPLACE); if (ttl == null) { stmnt.setNull(1, Types.BIGINT); @@ -594,28 +564,82 @@ stmnt.setBytes( 3, factory.getSerializer().toBytes(artifact)); - stmnt.setInt(4, id); + id[0] = ID.intValue(); + stmnt.setInt(4, id[0]); + } + else { // new artifact + prepareStatement(SQL_NEXT_ID); + result = stmnt.executeQuery(); - stmnt.execute(); - connection.commit(); - return id; + if (!result.next()) { + logger.error("No id generated"); + return false; + } + + reset(); + + prepareStatement(SQL_INSERT); + + id[0] = result.getInt(1); + stmnt.setInt(1, id[0]); + stmnt.setString(2, uuid); + if (ttl == null) { + stmnt.setNull(3, Types.BIGINT); + } + else { + stmnt.setLong(3, ttl.longValue()); + } + + stmnt.setString(4, factory.getName()); + + stmnt.setBytes( + 5, + factory.getSerializer().toBytes(artifact)); } + stmnt.execute(); + conn.commit(); + return true; + } + }; - stmnt = connection.prepareStatement(SQL_NEXT_ID); + if (!exec.runWrite()) { + throw new RuntimeException("failed insert artifact into database"); + } + + return id[0]; + } + + /** + * Internal method to store an artifact inside the database. + * @param artifact The artifact to be stored. + * @param factory The factory which created the artifact. + * @param ttl The initial time to live of the artifact. + * @return The database id of the stored artifact. + */ + protected int insertDatabase( + final Artifact artifact, + final ArtifactFactory factory, + final Long ttl + ) { + final int [] id = new int[1]; + + SQLExecutor exec = new SQLExecutor() { + public boolean doIt() throws SQLException { + prepareStatement(SQL_NEXT_ID); result = stmnt.executeQuery(); if (!result.next()) { - throw new RuntimeException("No id generated"); + logger.error("No id generated"); + return false; } - int id = result.getInt(1); + id[0] = result.getInt(1); - result.close(); result = null; - stmnt.close(); stmnt = null; + reset(); + prepareStatement(SQL_INSERT); - stmnt = connection.prepareStatement(SQL_INSERT); - - stmnt.setInt(1, id); + String uuid = artifact.identifier(); + stmnt.setInt(1, id[0]); stmnt.setString(2, uuid); if (ttl == null) { stmnt.setNull(3, Types.BIGINT); @@ -631,118 +655,17 @@ factory.getSerializer().toBytes(artifact)); stmnt.execute(); - connection.commit(); - return id; - } - catch (SQLException sqle) { - connection.rollback(); - throw sqle; - } - } - catch (SQLException sqle) { - logger.error(sqle.getLocalizedMessage(), sqle); - } - finally { - if (result != null) { - try { result.close(); } - catch (SQLException sqle) {} - } - if (stmnt != null) { - try { stmnt.close(); } - catch (SQLException sqle) {} - } - if (connection != null) { - try { connection.close(); } - catch (SQLException sqle) {} - } - } - throw new RuntimeException("failed insert artifact into database"); - } - - /** - * Internal method to store an artifact inside the database. - * @param artifact The artifact to be stored. - * @param factory The factory which created the artifact. - * @param ttl The initial time to live of the artifact. - * @return The database id of the stored artifact. - */ - protected int insertDatabase( - Artifact artifact, - ArtifactFactory factory, - Long ttl - ) { - String uuid = artifact.identifier(); - - Connection connection = null; - PreparedStatement stmnt_next_id = null; - PreparedStatement stmnt_insert = null; - ResultSet res_id = null; - - DataSource dataSource = DBConnection.getDataSource(); - try { - connection = dataSource.getConnection(); - try { - connection.setAutoCommit(false); - - stmnt_next_id = connection.prepareStatement(SQL_NEXT_ID); - stmnt_insert = connection.prepareStatement(SQL_INSERT); - res_id = stmnt_next_id.executeQuery(); - - if (!res_id.next()) { - throw new RuntimeException("No id generated"); - } - - int id = res_id.getInt(1); - - stmnt_insert.setInt(1, id); - stmnt_insert.setString(2, uuid); - if (ttl == null) { - stmnt_insert.setNull(3, Types.BIGINT); - } - else { - stmnt_insert.setLong(3, ttl.longValue()); - } - - stmnt_insert.setString(4, factory.getName()); - - stmnt_insert.setBytes( - 5, - factory.getSerializer().toBytes(artifact)); - - stmnt_insert.execute(); - - connection.commit(); + conn.commit(); + return true; + } + }; - return id; - } - catch (SQLException sqle) { - connection.rollback(); - throw sqle; - } - } - catch (SQLException sqle) { - logger.error(sqle.getLocalizedMessage(), sqle); + if (!exec.runWrite()) { + throw new RuntimeException("failed insert artifact into database"); } - finally { - if (res_id != null) { - try { res_id.close(); } - catch (SQLException sqle) {} - } - if (stmnt_insert != null) { - try { stmnt_insert.close(); } - catch (SQLException sqle) {} - } - if (stmnt_next_id != null) { - try { stmnt_next_id.close(); } - catch (SQLException sqle) {} - } - if (connection != null) { - try { connection.close(); } - catch (SQLException sqle) {} - } - } - throw new RuntimeException("failed insert artifact into database"); + + return id[0]; } /** @@ -750,42 +673,16 @@ * that it will be removed from the database by the database cleaner. * @param artifact The persistent wrapper around the living artifact. */ - public void touch(PersistentArtifact artifact) { - - try { - Connection connection = null; - PreparedStatement stmnt_touch = null; - DataSource dataSource = DBConnection.getDataSource(); - try { - connection = dataSource.getConnection(); - try { - connection.setAutoCommit(false); - stmnt_touch = connection.prepareStatement(SQL_TOUCH); - stmnt_touch.setInt(1, artifact.getId()); - stmnt_touch.execute(); - connection.commit(); - } - catch (SQLException sqle) { - connection.rollback(); - } + public void touch(final PersistentArtifact artifact) { + new SQLExecutor() { + public boolean doIt() throws SQLException { + prepareStatement(SQL_TOUCH); + stmnt.setInt(1, artifact.getId()); + stmnt.execute(); + conn.commit(); + return true; } - catch (SQLException sqle) { - logger.error(sqle.getLocalizedMessage(), sqle); - } - finally { - if (stmnt_touch != null) { - try { stmnt_touch.close(); } - catch (SQLException sqle) {} - } - if (connection != null) { - try { connection.close(); } - catch (SQLException sqle) {} - } - } - } - catch (Exception e) { - logger.error(e.getLocalizedMessage(), e); - } + }.runWrite(); } /** @@ -793,148 +690,86 @@ * @param artifact The persistent wrapper around a living * artifact. */ - public void store(PersistentArtifact artifact) { - - try { - Connection connection = null; - PreparedStatement stmnt_update = null; - DataSource dataSource = DBConnection.getDataSource(); - try { - connection = dataSource.getConnection(); - try { - connection.setAutoCommit(false); - stmnt_update = connection.prepareStatement(SQL_UPDATE); - stmnt_update.setInt(2, artifact.getId()); - - byte [] bytes = artifact - .getSerializer() - .toBytes(artifact.getArtifact()); + public void store(final PersistentArtifact artifact) { + new SQLExecutor() { + public boolean doIt() throws SQLException { + prepareStatement(SQL_UPDATE); + stmnt.setInt(2, artifact.getId()); - stmnt_update.setBytes(1, bytes); - stmnt_update.execute(); - connection.commit(); - } - catch (SQLException sqle) { - connection.rollback(); - } - } - catch (SQLException sqle) { - logger.error(sqle.getLocalizedMessage(), sqle); + byte [] bytes = artifact + .getSerializer() + .toBytes(artifact.getArtifact()); + + stmnt.setBytes(1, bytes); + stmnt.execute(); + conn.commit(); + return true; } - finally { - if (stmnt_update != null) { - try { stmnt_update.close(); } - catch (SQLException sqle) {} - } - if (connection != null) { - try { connection.close(); } - catch (SQLException sqle) {} - } - } - } - catch (Exception e) { - logger.error(e.getLocalizedMessage(), e); - } + }.runWrite(); } public User createUser( - String name, - Document role, - UserFactory factory, - Object context + final String name, + final Document role, + final UserFactory factory, + final Object context ) { - Connection connection = null; - PreparedStatement stmnt_next_id = null; - PreparedStatement stmnt_insert = null; - ResultSet result = null; + final User [] user = new User[1]; - String identifier = newIdentifier(); + SQLExecutor exec = new SQLExecutor() { + public boolean doIt() throws SQLException { - DataSource dataSource = DBConnection.getDataSource(); - try { - connection = dataSource.getConnection(); - try { - connection.setAutoCommit(false); - - stmnt_next_id = connection.prepareStatement(SQL_USERS_NEXT_ID); - stmnt_insert = connection.prepareStatement(SQL_USERS_INSERT); - - result = stmnt_next_id.executeQuery(); + prepareStatement(SQL_USERS_NEXT_ID); + result = stmnt.executeQuery(); if (!result.next()) { - throw new RuntimeException("No id generated"); + return false; } int id = result.getInt(1); - stmnt_insert.setInt(1, id); - stmnt_insert.setString(2, identifier); - stmnt_insert.setString(3, name); + reset(); + + String identifier = newIdentifier(); + + prepareStatement(SQL_USERS_INSERT); + + stmnt.setInt(1, id); + stmnt.setString(2, identifier); + stmnt.setString(3, name); byte [] roleData = role == null ? null : XMLUtils.toByteArray(role); if (roleData == null) { - stmnt_insert.setNull(4, Types.BIGINT); + stmnt.setNull(4, Types.BIGINT); } else { - stmnt_insert.setBytes(4, roleData); + stmnt.setBytes(4, roleData); } - stmnt_insert.execute(); - - connection.commit(); + stmnt.execute(); + conn.commit(); - return factory.createUser(identifier, name, role, context); - } - catch (SQLException sqle) { - connection.rollback(); - throw sqle; + user[0] = factory.createUser( + identifier, name, role, context); + return true; } - } - catch (SQLException sqle) { - logger.error(sqle.getLocalizedMessage(), sqle); - } - finally { - if (result != null) { - try { result.close(); } - catch (SQLException sqle) {} - } - if (stmnt_insert != null) { - try { stmnt_insert.close(); } - catch (SQLException sqle) {} - } - if (stmnt_next_id != null) { - try { stmnt_next_id.close(); } - catch (SQLException sqle) {} - } - if (connection != null) { - try { connection.close(); } - catch (SQLException sqle) {} - } - } - return null; + }; + + return exec.runWrite() ? user[0] : null; } - public boolean deleteUser(String identifier) { - - Connection conn = null; - ResultSet result = null; - PreparedStatement stmnt = null; + public boolean deleteUser(final String identifier) { if (!StringUtils.checkUUID(identifier)) { return false; } - DataSource dataSource = DBConnection.getDataSource(); - try { - conn = dataSource.getConnection(); - try { - conn.setAutoCommit(false); - - // fetch user id - stmnt = conn.prepareStatement(SQL_USERS_SELECT_ID_BY_GID); + SQLExecutor exec = new SQLExecutor() { + public boolean doIt() throws SQLException { + prepareStatement(SQL_USERS_SELECT_ID_BY_GID); stmnt.setString(1, identifier); result = stmnt.executeQuery(); @@ -945,174 +780,119 @@ int id = result.getInt(1); - result.close(); result = null; - stmnt.close(); stmnt = null; + reset(); // outdate the artifacts exclusively used by the user - stmnt = conn.prepareStatement(SQL_OUTDATE_ARTIFACTS_USER); + prepareStatement(SQL_OUTDATE_ARTIFACTS_USER); stmnt.setInt(1, id); stmnt.setInt(2, id); stmnt.execute(); - stmnt.close(); stmnt = null; + reset(); // delete the collection items of the user - stmnt = conn.prepareStatement(SQL_DELETE_USER_COLLECTION_ITEMS); + prepareStatement(SQL_DELETE_USER_COLLECTION_ITEMS); stmnt.setInt(1, id); stmnt.execute(); - stmnt.close(); stmnt = null; + + reset(); // delete the collections of the user - stmnt = conn.prepareStatement(SQL_USERS_DELETE_COLLECTIONS); + prepareStatement(SQL_USERS_DELETE_COLLECTIONS); stmnt.setInt(1, id); stmnt.execute(); - stmnt.close(); stmnt = null; + + reset(); // delete the user - stmnt = conn.prepareStatement(SQL_USERS_DELETE_ID); + prepareStatement(SQL_USERS_DELETE_ID); stmnt.setInt(1, id); stmnt.execute(); conn.commit(); - return true; } - catch (SQLException sqle) { - conn.rollback(); - throw sqle; - } - } - catch (SQLException sqle) { - logger.error(sqle.getLocalizedMessage(), sqle); - } - finally { - if (result != null) { - try { result.close(); } - catch (SQLException sqle) {} - } - if (stmnt != null) { - try { stmnt.close(); } - catch (SQLException sqle) {} - } - if (conn != null) { - try { conn.close(); } - catch (SQLException sqle) {} - } - } + }; - return false; + return exec.runWrite(); } public User getUser( - String identifier, - UserFactory factory, - Object context + final String identifier, + final UserFactory factory, + final Object context ) { - Connection conn = null; - ResultSet result = null; - PreparedStatement stmnt = null; - if (!StringUtils.checkUUID(identifier)) { logger.debug("Invalid UUID: '" + identifier + "'"); return null; } - DataSource dataSource = DBConnection.getDataSource(); - try { - conn = dataSource.getConnection(); - stmnt = conn.prepareStatement(SQL_USERS_SELECT_GID); - stmnt.setString(1, identifier); - result = stmnt.executeQuery(); - if (!result.next()) { // no such user - return null; - } - // omit id - String name = result.getString(2); - byte [] roleData = result.getBytes(3); - - Document role = XMLUtils.fromByteArray(roleData); + final User [] user = new User[1]; - return factory.createUser(identifier, name, role, context); - } - catch (SQLException sqle) { - logger.error(sqle.getLocalizedMessage(), sqle); - } - finally { - if (result != null) { - try { result.close(); } - catch (SQLException sqle) {} + SQLExecutor exec = new SQLExecutor() { + public boolean doIt() throws SQLException { + prepareStatement(SQL_USERS_SELECT_GID); + stmnt.setString(1, identifier); + result = stmnt.executeQuery(); + if (!result.next()) { // no such user + return false; + } + // omit id + String name = result.getString(2); + byte [] roleData = result.getBytes(3); + + Document role = XMLUtils.fromByteArray(roleData); + + user[0] = factory.createUser( + identifier, name, role, context); + return true; } - if (stmnt != null) { - try { stmnt.close(); } - catch (SQLException sqle) {} - } - if (conn != null) { - try { conn.close(); } - catch (SQLException sqle) {} - } - } + }; - return null; + return exec.runRead() ? user[0] : null; } - public User [] getUsers(UserFactory factory, Object context) { - Connection conn = null; - ResultSet result = null; - PreparedStatement stmnt = null; - - DataSource dataSource = DBConnection.getDataSource(); - try { - conn = dataSource.getConnection(); - stmnt = conn.prepareStatement(SQL_USERS_SELECT_ALL); - result = stmnt.executeQuery(); - - ArrayList users = new ArrayList(); - - while (result.next()) { - // omit id - String identifier = result.getString(2); - String name = result.getString(3); - byte [] roleData = result.getBytes(4); + public User [] getUsers( + final UserFactory factory, + final Object context + ) { + final ArrayList users = new ArrayList(); - Document role = XMLUtils.fromByteArray(roleData); - User user = - factory.createUser(identifier, name, role, context); - users.add(user); - } + SQLExecutor exec = new SQLExecutor() { + public boolean doIt() throws SQLException { + prepareStatement(SQL_USERS_SELECT_ALL); + result = stmnt.executeQuery(); - return users.toArray(new User[users.size()]); - } - catch (SQLException sqle) { - logger.error(sqle.getLocalizedMessage(), sqle); - } - finally { - if (result != null) { - try { result.close(); } - catch (SQLException sqle) {} + while (result.next()) { + // omit id + String identifier = result.getString(2); + String name = result.getString(3); + byte [] roleData = result.getBytes(4); + + Document role = XMLUtils.fromByteArray(roleData); + User user = factory.createUser( + identifier, name, role, context); + users.add(user); + } + return true; } - if (stmnt != null) { - try { stmnt.close(); } - catch (SQLException sqle) {} - } - if (conn != null) { - try { conn.close(); } - catch (SQLException sqle) {} - } - } + }; - return null; + return exec.runRead() + ? users.toArray(new User[users.size()]) + : null; } public ArtifactCollection createCollection( - String ownerIdentifier, - String name, - ArtifactCollectionFactory factory, - Document data, - Object context + final String ownerIdentifier, + final String name, + final ArtifactCollectionFactory factory, + final Document data, + final Object context ) { if (name == null) { logger.debug("Name is null"); @@ -1124,43 +904,37 @@ return null; } - Connection conn = null; - ResultSet result = null; - PreparedStatement stmnt = null; + final ArtifactCollection [] collection = + new ArtifactCollection[1]; - DataSource dataSource = DBConnection.getDataSource(); - try { - conn = dataSource.getConnection(); - conn.setAutoCommit(false); - try { + SQLExecutor exec = new SQLExecutor() { + public boolean doIt() throws SQLException { // fetch owner id - stmnt = conn.prepareStatement(SQL_USERS_SELECT_ID_BY_GID); + prepareStatement(SQL_USERS_SELECT_ID_BY_GID); stmnt.setString(1, ownerIdentifier); result = stmnt.executeQuery(); if (!result.next()) { // no such user - return null; + return false; } int ownerId = result.getInt(1); - result.close(); result = null; - stmnt.close(); stmnt = null; + reset(); // fetch new collection seq number. - stmnt = conn.prepareStatement(SQL_COLLECTIONS_NEXT_ID); + prepareStatement(SQL_COLLECTIONS_NEXT_ID); result = stmnt.executeQuery(); if (!result.next()) { // no identifier generated - return null; + return false; } int id = result.getInt(1); - result.close(); result = null; - stmnt.close(); stmnt = null; + reset(); String identifier = newIdentifier(); - stmnt = conn.prepareStatement(SQL_COLLECTIONS_INSERT); + prepareStatement(SQL_COLLECTIONS_INSERT); stmnt.setInt(1, id); stmnt.setString(2, identifier); @@ -1180,13 +954,13 @@ stmnt.execute(); conn.commit(); - stmnt.close(); stmnt = null; + reset(); // fetch creation time from database // done this way to use the time system // of the database. - stmnt = conn.prepareStatement(SQL_COLLECTIONS_CREATION_TIME); + prepareStatement(SQL_COLLECTIONS_CREATION_TIME); stmnt.setInt(1, id); result = stmnt.executeQuery(); @@ -1198,40 +972,22 @@ creationTime = new Date(timestamp.getTime()); } - return factory.createCollection( + collection[0] = factory.createCollection( identifier, name, creationTime, data, context); - } - catch (SQLException sqle) { - conn.rollback(); - throw sqle; + + return true; } - } - catch (SQLException sqle) { - logger.error(sqle.getLocalizedMessage(), sqle); - } - finally { - if (result != null) { - try { result.close(); } - catch (SQLException sqle) {} - } - if (stmnt != null) { - try { stmnt.close(); } - catch (SQLException sqle) {} - } - if (conn != null) { - try { conn.close(); } - catch (SQLException sqle) {} - } - } - return null; + }; + + return exec.runWrite() ? collection[0]: null; } public ArtifactCollection [] listCollections( - String ownerIdentifier, - Document data, - ArtifactCollectionFactory collectionFactory, - UserFactory userFactory, - Object context + final String ownerIdentifier, + final Document data, + final ArtifactCollectionFactory collectionFactory, + final UserFactory userFactory, + final Object context ) { if (ownerIdentifier != null && !StringUtils.checkUUID(ownerIdentifier)) { @@ -1239,72 +995,55 @@ return null; } - Connection conn = null; - ResultSet result = null; - PreparedStatement stmnt = null; - - DataSource dataSource = DBConnection.getDataSource(); - try { - ArrayList collections = - new ArrayList(); - conn = dataSource.getConnection(); - - if (ownerIdentifier != null) { - stmnt = conn.prepareStatement(SQL_COLLECTIONS_SELECT_USER); - stmnt.setString(1, ownerIdentifier); - } - else { - stmnt = conn.prepareStatement(SQL_COLLECTIONS_SELECT_ALL); - } + final ArrayList collections = + new ArrayList(); - result = stmnt.executeQuery(); - - while (result.next()) { - String collectionIdentifier = result.getString(1); - String collectionName = result.getString(2); - Date creationTime = - new Date(result.getTimestamp(3).getTime()); - String userIdentifier = result.getString(4); + SQLExecutor exec = new SQLExecutor() { - ArtifactCollection collection = - collectionFactory.createCollection( - collectionIdentifier, - collectionName, - creationTime, - data, - context); + public boolean doIt() throws SQLException { - if (userIdentifier != null) { - collection.setUser(new LazyBackendUser( - userIdentifier, userFactory, this, context)); + if (ownerIdentifier != null) { + prepareStatement(SQL_COLLECTIONS_SELECT_USER); + stmnt.setString(1, ownerIdentifier); + } + else { + prepareStatement(SQL_COLLECTIONS_SELECT_ALL); } - collections.add(collection); - } + result = stmnt.executeQuery(); - return collections.toArray( - new ArtifactCollection[collections.size()]); - } - catch (SQLException sqle) { - logger.error(sqle.getLocalizedMessage(), sqle); - } - finally { - if (result != null) { - try { result.close(); } - catch (SQLException sqle) {} + while (result.next()) { + String collectionIdentifier = result.getString(1); + String collectionName = result.getString(2); + Date creationTime = + new Date(result.getTimestamp(3).getTime()); + String userIdentifier = result.getString(4); + + ArtifactCollection collection = + collectionFactory.createCollection( + collectionIdentifier, + collectionName, + creationTime, + data, + context); + + if (userIdentifier != null) { + collection.setUser(new LazyBackendUser( + userIdentifier, userFactory, Backend.this, context)); + } + + collections.add(collection); + } + return true; } - if (stmnt != null) { - try { stmnt.close(); } - catch (SQLException sqle) {} - } - if (conn != null) { - try { conn.close(); } - catch (SQLException sqle) {} - } - } - return null; + }; + + return exec.runRead() + ? collections.toArray(new ArtifactCollection[collections.size()]) + : null; } + public boolean deleteCollection(String collectionId) { // TODO: Implement me! return false; diff -r 940ed629419f -r 25d472a67a9f artifact-database/src/main/java/de/intevation/artifactdatabase/SQLExecutor.java --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/artifact-database/src/main/java/de/intevation/artifactdatabase/SQLExecutor.java Fri Mar 04 16:43:03 2011 +0000 @@ -0,0 +1,102 @@ +/* + * Copyright (c) 2011 by Intevation GmbH + * + * This program is free software under the LGPL (>=v2.1) + * Read the file LGPL.txt coming with the software for details + * or visit http://www.gnu.org/licenses/ if it does not exist. + */ +package de.intevation.artifactdatabase; + +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; + +import javax.sql.DataSource; + +import org.apache.log4j.Logger; + +public class SQLExecutor { + + private static Logger logger = Logger.getLogger(SQLExecutor.class); + + public Connection conn; + public PreparedStatement stmnt; + public ResultSet result; + + public SQLExecutor() { + } + + public void reset() throws SQLException { + if (result != null) { + result.close(); + result = null; + } + if (stmnt != null) { + result = null; + stmnt.close(); + } + } + + public PreparedStatement prepareStatement(String query) + throws SQLException { + return stmnt = conn.prepareStatement(query); + } + + public void close() { + if (result != null) { + try { result.close(); } + catch (SQLException sqle) {} + } + if (stmnt != null) { + try { stmnt.close(); } + catch (SQLException sqle) {} + } + if (conn != null) { + try { conn.close(); } + catch (SQLException sqle) {} + } + } + + public boolean runWrite() { + DataSource dataSource = DBConnection.getDataSource(); + try { + conn = dataSource.getConnection(); + try { + conn.setAutoCommit(false); + return doIt(); + } + catch (SQLException sqle) { + conn.rollback(); + throw sqle; + } + } + catch (SQLException sqle) { + logger.error(sqle.getLocalizedMessage(), sqle); + } + finally { + close(); + } + return false; + } + + public boolean runRead() { + DataSource dataSource = DBConnection.getDataSource(); + try { + conn = dataSource.getConnection(); + return doIt(); + } + catch (SQLException sqle) { + logger.error(sqle.getLocalizedMessage(), sqle); + } + finally { + close(); + } + return false; + } + + public boolean doIt() throws SQLException { + return true; + } +} +// vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 : diff -r 940ed629419f -r 25d472a67a9f artifacts/src/main/java/de/intevation/artifacts/ArtifactCollectionFactory.java --- a/artifacts/src/main/java/de/intevation/artifacts/ArtifactCollectionFactory.java Fri Mar 04 14:06:56 2011 +0000 +++ b/artifacts/src/main/java/de/intevation/artifacts/ArtifactCollectionFactory.java Fri Mar 04 16:43:03 2011 +0000 @@ -62,4 +62,3 @@ Long timeToLiveUntouched(ArtifactCollection collection, Object context); } // vim:set ts=4 sw=4 si et sta sts=4 fenc=utf8 : -