1 package org.wamblee.support.persistence;
3 import java.sql.Connection;
4 import java.sql.PreparedStatement;
5 import java.sql.ResultSet;
6 import java.sql.SQLException;
7 import java.util.ArrayList;
8 import java.util.Iterator;
11 import java.util.TreeMap;
12 import java.util.logging.Logger;
14 import javax.sql.DataSource;
16 import junit.framework.TestCase;
18 import org.dbunit.DataSourceDatabaseTester;
19 import org.dbunit.DatabaseTestCase;
20 import org.dbunit.DatabaseUnitException;
21 import org.dbunit.IDatabaseTester;
22 import org.dbunit.database.DatabaseConnection;
23 import org.dbunit.database.DatabaseSequenceFilter;
24 import org.dbunit.database.IDatabaseConnection;
25 import org.dbunit.dataset.FilteredDataSet;
26 import org.dbunit.dataset.IDataSet;
27 import org.dbunit.dataset.filter.ITableFilter;
28 import org.dbunit.dataset.filter.ITableFilterSimple;
29 import org.dbunit.operation.DatabaseOperation;
32 * Database utilities is a simple support class for common tasks in working with
35 public class DatabaseUtils {
37 public static interface TableSet {
38 boolean contains(String aTableName);
41 public static interface JdbcUnitOfWork<T> {
42 T execute(Connection aConnection) throws Exception;
45 public static interface TableSetOperation {
46 void execute(String aTable) throws Exception;
49 private static final Logger LOG = Logger.getLogger(DatabaseUtils.class
55 private static final String SCHEMA_PATTERN = "%";
56 private DataSource dataSource;
57 private ITableFilterSimple tables;
60 public DatabaseUtils(DataSource aDataSource, ITableFilterSimple aTables) {
61 dataSource = aDataSource;
65 public IDatabaseTester createDbTester() throws Exception {
66 return createDbTester(getTableNames(tables));
69 public IDatabaseTester createDbTester(String[] aTables) throws Exception {
70 IDatabaseTester dbtester = new DataSourceDatabaseTester(dataSource);
71 dbtester.setDataSet(dbtester.getConnection().createDataSet(aTables));
75 public void cleanDatabase() throws Exception {
76 cleanDatabase(tables);
79 public void executeOnTables(ITableFilterSimple aTables,
80 final TableSetOperation aOperation) throws Exception {
81 final String[] tables = getTableNames(aTables);
82 executeInTransaction(new JdbcUnitOfWork<Void>() {
83 public Void execute(Connection aConnection) throws Exception {
84 for (int i = tables.length - 1; i >= 0; i--) {
85 aOperation.execute(tables[i]);
90 for (String table : tables) {
95 public void cleanDatabase(ITableFilterSimple aSelection) throws Exception {
97 final String[] tables = getTableNames(aSelection);
98 executeInTransaction(new JdbcUnitOfWork<Void>() {
100 public Void execute(Connection aConnection) throws Exception {
101 IDatabaseConnection connection = new DatabaseConnection(
103 ITableFilter filter = new DatabaseSequenceFilter(connection,
105 IDataSet dataset = new FilteredDataSet(filter, connection
106 .createDataSet(tables));
107 DatabaseOperation.DELETE_ALL.execute(connection, dataset);
114 public <T> T executeInTransaction(JdbcUnitOfWork<T> aCallback)
116 Connection connection = dataSource.getConnection();
118 T value = aCallback.execute(connection);
126 public String[] getTableNames() throws Exception {
127 return getTableNames(tables);
131 * @throws SQLException
133 public String[] getTableNames(ITableFilterSimple aSelection)
136 List<String> result = new ArrayList<String>();
137 LOG.fine("Getting database table names to clean (schema: '"
138 + SCHEMA_PATTERN + "'");
140 ResultSet tables = dataSource.getConnection().getMetaData().getTables(
141 null, SCHEMA_PATTERN, "%", new String[] { "TABLE" });
142 while (tables.next()) {
143 String table = tables.getString("TABLE_NAME");
144 if (aSelection.accept(table)) {
148 return (String[]) result.toArray(new String[0]);
151 public void emptyTables() throws Exception {
152 executeOnTables(tables, new TableSetOperation() {
153 public void execute(String aTable) throws Exception {
161 * @throws SQLException
163 public void emptyTables(final ITableFilterSimple aSelection)
165 executeOnTables(aSelection, new TableSetOperation() {
166 public void execute(String aTable) throws Exception {
174 * @throws SQLException
176 public void emptyTable(String aTable) throws Exception {
177 executeSql("delete from " + aTable);
180 public void dropTables() throws Exception {
181 executeOnTables(tables, new TableSetOperation() {
183 public void execute(String aTable) throws Exception {
190 public void dropTables(ITableFilterSimple aTables) throws Exception {
191 executeOnTables(aTables, new TableSetOperation() {
193 public void execute(String aTable) throws Exception {
201 * @throws SQLException
203 public void dropTable(final String aTable) throws Exception {
204 executeInTransaction(new JdbcUnitOfWork<Void>() {
205 public Void execute(Connection aConnection) throws Exception {
206 executeUpdate(aConnection, "drop table " + aTable);
214 * Executes an SQL statement within a transaction.
218 * @return Return code of the corresponding JDBC call.
220 public int executeSql(final String aSql) throws Exception {
221 return executeSql(aSql, new Object[0]);
225 * Executes an SQL statement within a transaction. See
226 * {@link #setPreparedParam(int, PreparedStatement, Object)}for details on
227 * supported argument types.
232 * Argument of the sql statement.
233 * @return Return code of the corresponding JDBC call.
235 public int executeSql(final String aSql, final Object aArg)
237 return executeSql(aSql, new Object[] { aArg });
241 * Executes an sql statement. See
242 * {@link #setPreparedParam(int, PreparedStatement, Object)}for details on
243 * supported argument types.
246 * SQL query to execute.
249 * @return Number of rows updated.
251 public int executeSql(final String aSql, final Object[] aArgs)
253 return executeInTransaction(new JdbcUnitOfWork<Integer>() {
254 public Integer execute(Connection aConnection) throws Exception {
255 PreparedStatement stmt = aConnection.prepareStatement(aSql);
256 setPreparedParams(aArgs, stmt);
257 return stmt.executeUpdate();
263 * Executes an SQL query.
267 * @return Result set.
269 public ResultSet executeQuery(Connection aConnection, String aSql) {
270 return executeQuery(aConnection, aSql, new Object[0]);
274 * Executes a query with a single argument. See
275 * {@link #setPreparedParam(int, PreparedStatement, Object)}for details on
276 * supported argument types.
282 * @return Result set.
284 public ResultSet executeQuery(Connection aConnection, String aSql,
286 return executeQuery(aConnection, aSql, new Object[] { aArg });
290 * Executes a query. See
291 * {@link #setPreparedParam(int, PreparedStatement, Object)}for details on
292 * supported argument types.
297 * Arguments to the query.
298 * @return Result set.
300 public ResultSet executeQuery(Connection aConnection, final String aSql,
301 final Object[] aArgs) {
303 PreparedStatement statement = aConnection.prepareStatement(aSql);
304 setPreparedParams(aArgs, statement);
306 return statement.executeQuery();
307 } catch (SQLException e) {
308 throw new RuntimeException(e);
312 public int executeUpdate(Connection aConnection, final String aSql,
313 final Object... aArgs) {
315 PreparedStatement statement = aConnection.prepareStatement(aSql);
316 setPreparedParams(aArgs, statement);
318 return statement.executeUpdate();
319 } catch (SQLException e) {
320 throw new RuntimeException(e);
325 * Sets the values of a prepared statement. See
326 * {@link #setPreparedParam(int, PreparedStatement, Object)}for details on
327 * supported argument types.
330 * Arguments to the prepared statement.
333 * @throws SQLException
335 private void setPreparedParams(final Object[] aArgs,
336 PreparedStatement aStatement) throws SQLException {
337 for (int i = 1; i <= aArgs.length; i++) {
338 setPreparedParam(i, aStatement, aArgs[i - 1]);
343 * Sets a prepared statement parameter.
346 * Index of the parameter.
348 * Prepared statement.
350 * Value Must be of type Integer, Long, or String.
351 * @throws SQLException
353 private void setPreparedParam(int aIndex, PreparedStatement aStatement,
354 Object aObject) throws SQLException {
355 if (aObject instanceof Integer) {
356 aStatement.setInt(aIndex, ((Integer) aObject).intValue());
357 } else if (aObject instanceof Long) {
358 aStatement.setLong(aIndex, ((Integer) aObject).longValue());
359 } else if (aObject instanceof String) {
360 aStatement.setString(aIndex, (String) aObject);
362 TestCase.fail("Unsupported object type for prepared statement: "
363 + aObject.getClass() + " value: " + aObject
364 + " statement: " + aStatement);
370 * @throws SQLException
372 public int getTableSize(final String aTable) throws Exception {
373 return executeInTransaction(new JdbcUnitOfWork<Integer>() {
374 public Integer execute(Connection aConnection) throws Exception {
375 ResultSet resultSet = executeQuery(aConnection,
376 "select count(*) from " + aTable);
378 return resultSet.getInt(1);
384 public int countResultSet(ResultSet aResultSet) throws SQLException {
387 while (aResultSet.next()) {