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;
9 import java.util.logging.Logger;
11 import javax.sql.DataSource;
13 import junit.framework.TestCase;
15 import org.dbunit.DataSourceDatabaseTester;
16 import org.dbunit.IDatabaseTester;
17 import org.dbunit.database.DatabaseConnection;
18 import org.dbunit.database.DatabaseSequenceFilter;
19 import org.dbunit.database.IDatabaseConnection;
20 import org.dbunit.dataset.FilteredDataSet;
21 import org.dbunit.dataset.IDataSet;
22 import org.dbunit.dataset.filter.ITableFilter;
23 import org.dbunit.dataset.filter.ITableFilterSimple;
24 import org.dbunit.operation.DatabaseOperation;
27 * Database utilities is a simple support class for common tasks in working with
30 public class DatabaseUtils {
32 public static interface TableSet {
33 boolean contains(String aTableName);
36 public static interface JdbcUnitOfWork<T> {
37 T execute(Connection aConnection) throws Exception;
40 public static interface TableSetOperation {
41 void execute(String aTable) throws Exception;
44 private static final Logger LOG = Logger.getLogger(DatabaseUtils.class
50 private static final String SCHEMA_PATTERN = "%";
51 private DataSource dataSource;
52 private ITableFilterSimple tables;
54 public DatabaseUtils(DataSource aDataSource, ITableFilterSimple aTables) {
55 dataSource = aDataSource;
59 public IDatabaseTester createDbTester() throws Exception {
60 return createDbTester(getTableNames(tables));
63 public IDatabaseTester createDbTester(String[] aTables) throws Exception {
64 IDatabaseTester dbtester = new DataSourceDatabaseTester(dataSource);
65 dbtester.setDataSet(dbtester.getConnection().createDataSet(aTables));
69 public void cleanDatabase() throws Exception {
70 cleanDatabase(tables);
73 public void executeOnTables(ITableFilterSimple aTables,
74 final TableSetOperation aOperation) throws Exception {
75 final String[] tables = getTableNames(aTables);
76 executeInTransaction(new JdbcUnitOfWork<Void>() {
77 public Void execute(Connection aConnection) throws Exception {
78 for (int i = tables.length - 1; i >= 0; i--) {
79 aOperation.execute(tables[i]);
84 for (String table : tables) {
89 public void cleanDatabase(ITableFilterSimple aSelection) throws Exception {
91 final String[] tables = getTableNames(aSelection);
92 executeInTransaction(new JdbcUnitOfWork<Void>() {
94 public Void execute(Connection aConnection) throws Exception {
95 IDatabaseConnection connection = new DatabaseConnection(
97 ITableFilter filter = new DatabaseSequenceFilter(connection,
99 IDataSet dataset = new FilteredDataSet(filter, connection
100 .createDataSet(tables));
101 DatabaseOperation.DELETE_ALL.execute(connection, dataset);
108 public <T> T executeInTransaction(JdbcUnitOfWork<T> aCallback)
110 Connection connection = dataSource.getConnection();
112 T value = aCallback.execute(connection);
120 public String[] getTableNames() throws Exception {
121 return getTableNames(tables);
125 * @throws SQLException
127 public String[] getTableNames(ITableFilterSimple aSelection)
130 List<String> result = new ArrayList<String>();
131 LOG.fine("Getting database table names to clean (schema: '" +
132 SCHEMA_PATTERN + "'");
134 ResultSet tables = dataSource.getConnection().getMetaData().getTables(
135 null, SCHEMA_PATTERN, "%", new String[] { "TABLE" });
136 while (tables.next()) {
137 String table = tables.getString("TABLE_NAME");
138 if (aSelection.accept(table)) {
142 return (String[]) result.toArray(new String[0]);
145 public void emptyTables() throws Exception {
146 executeOnTables(tables, new TableSetOperation() {
147 public void execute(String aTable) throws Exception {
155 * @throws SQLException
157 public void emptyTables(final ITableFilterSimple aSelection)
159 executeOnTables(aSelection, new TableSetOperation() {
160 public void execute(String aTable) throws Exception {
168 * @throws SQLException
170 public void emptyTable(String aTable) throws Exception {
171 executeSql("delete from " + aTable);
174 public void dropTables() throws Exception {
175 executeOnTables(tables, new TableSetOperation() {
177 public void execute(String aTable) throws Exception {
183 public void dropTables(ITableFilterSimple aTables) throws Exception {
184 executeOnTables(aTables, new TableSetOperation() {
186 public void execute(String aTable) throws Exception {
194 * @throws SQLException
196 public void dropTable(final String aTable) throws Exception {
197 executeInTransaction(new JdbcUnitOfWork<Void>() {
198 public Void execute(Connection aConnection) throws Exception {
199 executeUpdate(aConnection, "drop table " + aTable);
207 * Executes an SQL statement within a transaction.
211 * @return Return code of the corresponding JDBC call.
213 public int executeSql(final String aSql) throws Exception {
214 return executeSql(aSql, new Object[0]);
218 * Executes an SQL statement within a transaction. See
219 * {@link #setPreparedParam(int, PreparedStatement, Object)}for details on
220 * supported argument types.
225 * Argument of the sql statement.
226 * @return Return code of the corresponding JDBC call.
228 public int executeSql(final String aSql, final Object aArg)
230 return executeSql(aSql, new Object[] { aArg });
234 * Executes an sql statement. See
235 * {@link #setPreparedParam(int, PreparedStatement, Object)}for details on
236 * supported argument types.
239 * SQL query to execute.
242 * @return Number of rows updated.
244 public int executeSql(final String aSql, final Object[] aArgs)
246 return executeInTransaction(new JdbcUnitOfWork<Integer>() {
247 public Integer execute(Connection aConnection) throws Exception {
248 PreparedStatement stmt = aConnection.prepareStatement(aSql);
249 setPreparedParams(aArgs, stmt);
250 return stmt.executeUpdate();
256 * Executes an SQL query.
260 * @return Result set.
262 public ResultSet executeQuery(Connection aConnection, String aSql) {
263 return executeQuery(aConnection, aSql, new Object[0]);
267 * Executes a query with a single argument. See
268 * {@link #setPreparedParam(int, PreparedStatement, Object)}for details on
269 * supported argument types.
275 * @return Result set.
277 public ResultSet executeQuery(Connection aConnection, String aSql,
279 return executeQuery(aConnection, aSql, new Object[] { aArg });
283 * Executes a query. See
284 * {@link #setPreparedParam(int, PreparedStatement, Object)}for details on
285 * supported argument types.
290 * Arguments to the query.
291 * @return Result set.
293 public ResultSet executeQuery(Connection aConnection, final String aSql,
294 final Object[] aArgs) {
296 PreparedStatement statement = aConnection.prepareStatement(aSql);
297 setPreparedParams(aArgs, statement);
299 return statement.executeQuery();
300 } catch (SQLException e) {
301 throw new RuntimeException(e);
305 public int executeUpdate(Connection aConnection, final String aSql,
306 final Object... aArgs) {
308 PreparedStatement statement = aConnection.prepareStatement(aSql);
309 setPreparedParams(aArgs, statement);
311 return statement.executeUpdate();
312 } catch (SQLException e) {
313 throw new RuntimeException(e);
318 * Sets the values of a prepared statement. See
319 * {@link #setPreparedParam(int, PreparedStatement, Object)}for details on
320 * supported argument types.
323 * Arguments to the prepared statement.
326 * @throws SQLException
328 private void setPreparedParams(final Object[] aArgs,
329 PreparedStatement aStatement) throws SQLException {
330 for (int i = 1; i <= aArgs.length; i++) {
331 setPreparedParam(i, aStatement, aArgs[i - 1]);
336 * Sets a prepared statement parameter.
339 * Index of the parameter.
341 * Prepared statement.
343 * Value Must be of type Integer, Long, or String.
344 * @throws SQLException
346 private void setPreparedParam(int aIndex, PreparedStatement aStatement,
347 Object aObject) throws SQLException {
348 if (aObject instanceof Integer) {
349 aStatement.setInt(aIndex, ((Integer) aObject).intValue());
350 } else if (aObject instanceof Long) {
351 aStatement.setLong(aIndex, ((Integer) aObject).longValue());
352 } else if (aObject instanceof String) {
353 aStatement.setString(aIndex, (String) aObject);
355 TestCase.fail("Unsupported object type for prepared statement: " +
356 aObject.getClass() + " value: " + aObject + " statement: " +
363 * @throws SQLException
365 public int getTableSize(final String aTable) throws Exception {
366 return executeInTransaction(new JdbcUnitOfWork<Integer>() {
367 public Integer execute(Connection aConnection) throws Exception {
368 ResultSet resultSet = executeQuery(aConnection,
369 "select count(*) from " + aTable);
371 return resultSet.getInt(1);
377 public int countResultSet(ResultSet aResultSet) throws SQLException {
380 while (aResultSet.next()) {