001/* 002 * Copyright 2002-2016 the original author or authors. 003 * 004 * Licensed under the Apache License, Version 2.0 (the "License"); 005 * you may not use this file except in compliance with the License. 006 * You may obtain a copy of the License at 007 * 008 * https://www.apache.org/licenses/LICENSE-2.0 009 * 010 * Unless required by applicable law or agreed to in writing, software 011 * distributed under the License is distributed on an "AS IS" BASIS, 012 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 013 * See the License for the specific language governing permissions and 014 * limitations under the License. 015 */ 016 017package org.springframework.test.jdbc; 018 019import java.io.IOException; 020import java.io.LineNumberReader; 021import java.util.List; 022 023import org.apache.commons.logging.Log; 024import org.apache.commons.logging.LogFactory; 025 026import org.springframework.core.io.Resource; 027import org.springframework.core.io.ResourceLoader; 028import org.springframework.core.io.support.EncodedResource; 029import org.springframework.dao.DataAccessException; 030import org.springframework.jdbc.core.JdbcTemplate; 031import org.springframework.jdbc.core.SqlParameterValue; 032import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator; 033import org.springframework.jdbc.datasource.init.ScriptUtils; 034import org.springframework.util.StringUtils; 035 036/** 037 * {@code JdbcTestUtils} is a collection of JDBC related utility functions 038 * intended to simplify standard database testing scenarios. 039 * 040 * @author Thomas Risberg 041 * @author Sam Brannen 042 * @author Juergen Hoeller 043 * @author Phillip Webb 044 * @author Chris Baldwin 045 * @since 2.5.4 046 * @see org.springframework.jdbc.core.JdbcTemplate 047 * @see org.springframework.jdbc.datasource.init.ScriptUtils 048 * @see org.springframework.jdbc.datasource.init.ResourceDatabasePopulator 049 * @see org.springframework.jdbc.datasource.init.DatabasePopulatorUtils 050 */ 051public class JdbcTestUtils { 052 053 private static final Log logger = LogFactory.getLog(JdbcTestUtils.class); 054 055 056 /** 057 * Count the rows in the given table. 058 * @param jdbcTemplate the JdbcTemplate with which to perform JDBC operations 059 * @param tableName name of the table to count rows in 060 * @return the number of rows in the table 061 */ 062 public static int countRowsInTable(JdbcTemplate jdbcTemplate, String tableName) { 063 return jdbcTemplate.queryForObject("SELECT COUNT(0) FROM " + tableName, Integer.class); 064 } 065 066 /** 067 * Count the rows in the given table, using the provided {@code WHERE} clause. 068 * <p>If the provided {@code WHERE} clause contains text, it will be prefixed 069 * with {@code " WHERE "} and then appended to the generated {@code SELECT} 070 * statement. For example, if the provided table name is {@code "person"} and 071 * the provided where clause is {@code "name = 'Bob' and age > 25"}, the 072 * resulting SQL statement to execute will be 073 * {@code "SELECT COUNT(0) FROM person WHERE name = 'Bob' and age > 25"}. 074 * @param jdbcTemplate the JdbcTemplate with which to perform JDBC operations 075 * @param tableName the name of the table to count rows in 076 * @param whereClause the {@code WHERE} clause to append to the query 077 * @return the number of rows in the table that match the provided 078 * {@code WHERE} clause 079 */ 080 public static int countRowsInTableWhere(JdbcTemplate jdbcTemplate, String tableName, String whereClause) { 081 String sql = "SELECT COUNT(0) FROM " + tableName; 082 if (StringUtils.hasText(whereClause)) { 083 sql += " WHERE " + whereClause; 084 } 085 return jdbcTemplate.queryForObject(sql, Integer.class); 086 } 087 088 /** 089 * Delete all rows from the specified tables. 090 * @param jdbcTemplate the JdbcTemplate with which to perform JDBC operations 091 * @param tableNames the names of the tables to delete from 092 * @return the total number of rows deleted from all specified tables 093 */ 094 public static int deleteFromTables(JdbcTemplate jdbcTemplate, String... tableNames) { 095 int totalRowCount = 0; 096 for (String tableName : tableNames) { 097 int rowCount = jdbcTemplate.update("DELETE FROM " + tableName); 098 totalRowCount += rowCount; 099 if (logger.isInfoEnabled()) { 100 logger.info("Deleted " + rowCount + " rows from table " + tableName); 101 } 102 } 103 return totalRowCount; 104 } 105 106 /** 107 * Delete rows from the given table, using the provided {@code WHERE} clause. 108 * <p>If the provided {@code WHERE} clause contains text, it will be prefixed 109 * with {@code " WHERE "} and then appended to the generated {@code DELETE} 110 * statement. For example, if the provided table name is {@code "person"} and 111 * the provided where clause is {@code "name = 'Bob' and age > 25"}, the 112 * resulting SQL statement to execute will be 113 * {@code "DELETE FROM person WHERE name = 'Bob' and age > 25"}. 114 * <p>As an alternative to hard-coded values, the {@code "?"} placeholder can 115 * be used within the {@code WHERE} clause, binding to the given arguments. 116 * @param jdbcTemplate the JdbcTemplate with which to perform JDBC operations 117 * @param tableName the name of the table to delete rows from 118 * @param whereClause the {@code WHERE} clause to append to the query 119 * @param args arguments to bind to the query (leaving it to the PreparedStatement 120 * to guess the corresponding SQL type); may also contain {@link SqlParameterValue} 121 * objects which indicate not only the argument value but also the SQL type and 122 * optionally the scale. 123 * @return the number of rows deleted from the table 124 */ 125 public static int deleteFromTableWhere(JdbcTemplate jdbcTemplate, String tableName, String whereClause, 126 Object... args) { 127 128 String sql = "DELETE FROM " + tableName; 129 if (StringUtils.hasText(whereClause)) { 130 sql += " WHERE " + whereClause; 131 } 132 int rowCount = (args != null && args.length > 0 ? jdbcTemplate.update(sql, args) : jdbcTemplate.update(sql)); 133 if (logger.isInfoEnabled()) { 134 logger.info("Deleted " + rowCount + " rows from table " + tableName); 135 } 136 return rowCount; 137 } 138 139 /** 140 * Drop the specified tables. 141 * @param jdbcTemplate the JdbcTemplate with which to perform JDBC operations 142 * @param tableNames the names of the tables to drop 143 */ 144 public static void dropTables(JdbcTemplate jdbcTemplate, String... tableNames) { 145 for (String tableName : tableNames) { 146 jdbcTemplate.execute("DROP TABLE " + tableName); 147 if (logger.isInfoEnabled()) { 148 logger.info("Dropped table " + tableName); 149 } 150 } 151 } 152 153 /** 154 * Execute the given SQL script. 155 * <p>The script will typically be loaded from the classpath. There should 156 * be one statement per line. Any semicolons and line comments will be removed. 157 * <p><b>Do not use this method to execute DDL if you expect rollback.</b> 158 * @param jdbcTemplate the JdbcTemplate with which to perform JDBC operations 159 * @param resourceLoader the resource loader with which to load the SQL script 160 * @param sqlResourcePath the Spring resource path for the SQL script 161 * @param continueOnError whether or not to continue without throwing an 162 * exception in the event of an error 163 * @throws DataAccessException if there is an error executing a statement 164 * and {@code continueOnError} is {@code false} 165 * @see ResourceDatabasePopulator 166 * @see #executeSqlScript(JdbcTemplate, Resource, boolean) 167 * @deprecated as of Spring 4.0.3, in favor of using 168 * {@link org.springframework.jdbc.datasource.init.ScriptUtils#executeSqlScript} 169 * or {@link org.springframework.jdbc.datasource.init.ResourceDatabasePopulator}. 170 */ 171 @Deprecated 172 public static void executeSqlScript(JdbcTemplate jdbcTemplate, ResourceLoader resourceLoader, 173 String sqlResourcePath, boolean continueOnError) throws DataAccessException { 174 175 Resource resource = resourceLoader.getResource(sqlResourcePath); 176 executeSqlScript(jdbcTemplate, resource, continueOnError); 177 } 178 179 /** 180 * Execute the given SQL script. 181 * <p>The script will typically be loaded from the classpath. Statements 182 * should be delimited with a semicolon. If statements are not delimited with 183 * a semicolon then there should be one statement per line. Statements are 184 * allowed to span lines only if they are delimited with a semicolon. Any 185 * line comments will be removed. 186 * <p><b>Do not use this method to execute DDL if you expect rollback.</b> 187 * @param jdbcTemplate the JdbcTemplate with which to perform JDBC operations 188 * @param resource the resource to load the SQL script from 189 * @param continueOnError whether or not to continue without throwing an 190 * exception in the event of an error 191 * @throws DataAccessException if there is an error executing a statement 192 * and {@code continueOnError} is {@code false} 193 * @see ResourceDatabasePopulator 194 * @see #executeSqlScript(JdbcTemplate, EncodedResource, boolean) 195 * @deprecated as of Spring 4.0.3, in favor of using 196 * {@link org.springframework.jdbc.datasource.init.ScriptUtils#executeSqlScript} 197 * or {@link org.springframework.jdbc.datasource.init.ResourceDatabasePopulator}. 198 */ 199 @Deprecated 200 public static void executeSqlScript(JdbcTemplate jdbcTemplate, Resource resource, boolean continueOnError) 201 throws DataAccessException { 202 203 executeSqlScript(jdbcTemplate, new EncodedResource(resource), continueOnError); 204 } 205 206 /** 207 * Execute the given SQL script. 208 * <p>The script will typically be loaded from the classpath. There should 209 * be one statement per line. Any semicolons and line comments will be removed. 210 * <p><b>Do not use this method to execute DDL if you expect rollback.</b> 211 * @param jdbcTemplate the JdbcTemplate with which to perform JDBC operations 212 * @param resource the resource (potentially associated with a specific encoding) 213 * to load the SQL script from 214 * @param continueOnError whether or not to continue without throwing an 215 * exception in the event of an error 216 * @throws DataAccessException if there is an error executing a statement 217 * and {@code continueOnError} is {@code false} 218 * @see ResourceDatabasePopulator 219 * @deprecated as of Spring 4.0.3, in favor of using 220 * {@link org.springframework.jdbc.datasource.init.ScriptUtils#executeSqlScript} 221 * or {@link org.springframework.jdbc.datasource.init.ResourceDatabasePopulator}. 222 */ 223 @Deprecated 224 public static void executeSqlScript(JdbcTemplate jdbcTemplate, EncodedResource resource, boolean continueOnError) 225 throws DataAccessException { 226 227 new ResourceDatabasePopulator(continueOnError, false, resource.getEncoding(), resource.getResource()).execute(jdbcTemplate.getDataSource()); 228 } 229 230 /** 231 * Read a script from the provided {@code LineNumberReader}, using 232 * "{@code --}" as the comment prefix, and build a {@code String} containing 233 * the lines. 234 * @param lineNumberReader the {@code LineNumberReader} containing the script 235 * to be processed 236 * @return a {@code String} containing the script lines 237 * @see #readScript(LineNumberReader, String) 238 * @deprecated as of Spring 4.0.3, in favor of using 239 * {@link org.springframework.jdbc.datasource.init.ScriptUtils#readScript(LineNumberReader, String, String)} 240 */ 241 @Deprecated 242 public static String readScript(LineNumberReader lineNumberReader) throws IOException { 243 return readScript(lineNumberReader, ScriptUtils.DEFAULT_COMMENT_PREFIX); 244 } 245 246 /** 247 * Read a script from the provided {@code LineNumberReader}, using the supplied 248 * comment prefix, and build a {@code String} containing the lines. 249 * <p>Lines <em>beginning</em> with the comment prefix are excluded from the 250 * results; however, line comments anywhere else — for example, within 251 * a statement — will be included in the results. 252 * @param lineNumberReader the {@code LineNumberReader} containing the script 253 * to be processed 254 * @param commentPrefix the prefix that identifies comments in the SQL script — typically "--" 255 * @return a {@code String} containing the script lines 256 * @deprecated as of Spring 4.0.3, in favor of using 257 * {@link org.springframework.jdbc.datasource.init.ScriptUtils#readScript(LineNumberReader, String, String)} 258 */ 259 @Deprecated 260 public static String readScript(LineNumberReader lineNumberReader, String commentPrefix) throws IOException { 261 return ScriptUtils.readScript(lineNumberReader, commentPrefix, ScriptUtils.DEFAULT_STATEMENT_SEPARATOR); 262 } 263 264 /** 265 * Determine if the provided SQL script contains the specified delimiter. 266 * @param script the SQL script 267 * @param delim character delimiting each statement — typically a ';' character 268 * @return {@code true} if the script contains the delimiter; {@code false} otherwise 269 * @deprecated as of Spring 4.0.3, in favor of using 270 * {@link org.springframework.jdbc.datasource.init.ScriptUtils#containsSqlScriptDelimiters} 271 */ 272 @Deprecated 273 public static boolean containsSqlScriptDelimiters(String script, char delim) { 274 return ScriptUtils.containsSqlScriptDelimiters(script, String.valueOf(delim)); 275 } 276 277 /** 278 * Split an SQL script into separate statements delimited by the provided 279 * delimiter character. Each individual statement will be added to the 280 * provided {@code List}. 281 * <p>Within a statement, "{@code --}" will be used as the comment prefix; 282 * any text beginning with the comment prefix and extending to the end of 283 * the line will be omitted from the statement. In addition, multiple adjacent 284 * whitespace characters will be collapsed into a single space. 285 * @param script the SQL script 286 * @param delim character delimiting each statement — typically a ';' character 287 * @param statements the list that will contain the individual statements 288 * @deprecated as of Spring 4.0.3, in favor of using 289 * {@link org.springframework.jdbc.datasource.init.ScriptUtils#splitSqlScript(String, char, List)} 290 */ 291 @Deprecated 292 public static void splitSqlScript(String script, char delim, List<String> statements) { 293 ScriptUtils.splitSqlScript(script, delim, statements); 294 } 295 296}