/*
* Copyright 2002-2008 the original author or authors.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package org.springframework.test.jdbc;
import java.io.IOException;
import java.io.LineNumberReader;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.core.io.Resource;
import org.springframework.core.io.ResourceLoader;
import org.springframework.core.io.support.EncodedResource;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.DataAccessResourceFailureException;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.util.StringUtils;
/**
* A Java-5-based collection of JDBC related utility functions intended to
* simplify standard database testing scenarios.
*
* @author Sam Brannen
* @author Juergen Hoeller
* @author Thomas Risberg
* @since 2.5
*/
public abstract class SimpleJdbcTestUtils {
private static final Log logger = LogFactory.getLog(SimpleJdbcTestUtils.class);
/**
* Count the rows in the given table.
* @param simpleJdbcTemplate the SimpleJdbcTemplate with which to perform JDBC operations
* @param tableName table name to count rows in
* @return the number of rows in the table
*/
public static int countRowsInTable(SimpleJdbcTemplate simpleJdbcTemplate, String tableName) {
return simpleJdbcTemplate.queryForInt("SELECT COUNT(0) FROM " + tableName);
}
/**
* Delete all rows from the specified tables.
* @param simpleJdbcTemplate the SimpleJdbcTemplate with which to perform JDBC operations
* @param tableNames the names of the tables from which to delete
* @return the total number of rows deleted from all specified tables
*/
public static int deleteFromTables(SimpleJdbcTemplate simpleJdbcTemplate, String... tableNames) {
int totalRowCount = 0;
for (int i = 0; i < tableNames.length; i++) {
int rowCount = simpleJdbcTemplate.update("DELETE FROM " + tableNames[i]);
totalRowCount += rowCount;
if (logger.isInfoEnabled()) {
logger.info("Deleted " + rowCount + " rows from table " + tableNames[i]);
}
}
return totalRowCount;
}
/**
* Execute the given SQL script.
* <p>The script will normally be loaded by classpath. There should be one statement
* per line. Any semicolons will be removed. <b>Do not use this method to execute
* DDL if you expect rollback.</b>
* @param simpleJdbcTemplate the SimpleJdbcTemplate with which to perform JDBC operations
* @param resourceLoader the resource loader (with which to load the SQL script
* @param sqlResourcePath the Spring resource path for the SQL script
* @param continueOnError whether or not to continue without throwing an
* exception in the event of an error
* @throws DataAccessException if there is an error executing a statement
* and continueOnError was <code>false</code>
*/
public static void executeSqlScript(SimpleJdbcTemplate simpleJdbcTemplate,
ResourceLoader resourceLoader, String sqlResourcePath, boolean continueOnError)
throws DataAccessException {
Resource resource = resourceLoader.getResource(sqlResourcePath);
executeSqlScript(simpleJdbcTemplate, resource, continueOnError);
}
/**
* Execute the given SQL script. The script will normally be loaded by classpath.
* <p>Statements should be delimited with a semicolon. If statements are not delimited with
* a semicolon then there should be one statement per line. Statements are allowed to span
* lines only if they are delimited with a semicolon.
* <p><b>Do not use this method to execute DDL if you expect rollback.</b>
* @param simpleJdbcTemplate the SimpleJdbcTemplate with which to perform JDBC operations
* @param resource the resource to load the SQL script from.
* @param continueOnError whether or not to continue without throwing an
* exception in the event of an error.
* @throws DataAccessException if there is an error executing a statement
* and continueOnError was <code>false</code>
*/
public static void executeSqlScript(SimpleJdbcTemplate simpleJdbcTemplate,
Resource resource, boolean continueOnError) throws DataAccessException {
executeSqlScript(simpleJdbcTemplate, new EncodedResource(resource), continueOnError);
}
/**
* Execute the given SQL script.
* <p>The script will normally be loaded by classpath. There should be one statement
* per line. Any semicolons will be removed. <b>Do not use this method to execute
* DDL if you expect rollback.</b>
* @param simpleJdbcTemplate the SimpleJdbcTemplate with which to perform JDBC operations
* @param resource the resource (potentially associated with a specific encoding)
* to load the SQL script from.
* @param continueOnError whether or not to continue without throwing an
* exception in the event of an error.
* @throws DataAccessException if there is an error executing a statement
* and continueOnError was <code>false</code>
*/
public static void executeSqlScript(SimpleJdbcTemplate simpleJdbcTemplate,
EncodedResource resource, boolean continueOnError) throws DataAccessException {
if (logger.isInfoEnabled()) {
logger.info("Executing SQL script from " + resource);
}
long startTime = System.currentTimeMillis();
List<String> statements = new LinkedList<String>();
try {
LineNumberReader lnr = new LineNumberReader(resource.getReader());
String script = JdbcTestUtils.readScript(lnr);
char delimiter = ';';
if (!JdbcTestUtils.containsSqlScriptDelimiters(script, delimiter)) {
delimiter = '\n';
}
JdbcTestUtils.splitSqlScript(script, delimiter, statements);
for (Iterator<String> itr = statements.iterator(); itr.hasNext();) {
String statement = itr.next();
try {
int rowsAffected = simpleJdbcTemplate.update(statement);
if (logger.isDebugEnabled()) {
logger.debug(rowsAffected + " rows affected by SQL: " + statement);
}
}
catch (DataAccessException ex) {
if (continueOnError) {
if (logger.isWarnEnabled()) {
logger.warn("SQL: " + statement + " failed", ex);
}
}
else {
throw ex;
}
}
}
long elapsedTime = System.currentTimeMillis() - startTime;
if (logger.isInfoEnabled()) {
logger.info("Done executing SQL scriptBuilder from " + resource + " in " + elapsedTime + " ms.");
}
}
catch (IOException ex) {
throw new DataAccessResourceFailureException("Failed to open SQL script from " + resource, ex);
}
}
}