001/* 002 * Copyright 2002-2018 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 org.apache.commons.logging.Log; 020import org.apache.commons.logging.LogFactory; 021 022import org.springframework.jdbc.core.JdbcTemplate; 023import org.springframework.jdbc.core.SqlParameterValue; 024import org.springframework.util.StringUtils; 025 026/** 027 * {@code JdbcTestUtils} is a collection of JDBC related utility functions 028 * intended to simplify standard database testing scenarios. 029 * 030 * @author Thomas Risberg 031 * @author Sam Brannen 032 * @author Juergen Hoeller 033 * @author Phillip Webb 034 * @author Chris Baldwin 035 * @since 2.5.4 036 * @see org.springframework.jdbc.core.JdbcTemplate 037 * @see org.springframework.jdbc.datasource.init.ScriptUtils 038 * @see org.springframework.jdbc.datasource.init.ResourceDatabasePopulator 039 * @see org.springframework.jdbc.datasource.init.DatabasePopulatorUtils 040 */ 041public abstract class JdbcTestUtils { 042 043 private static final Log logger = LogFactory.getLog(JdbcTestUtils.class); 044 045 046 /** 047 * Count the rows in the given table. 048 * @param jdbcTemplate the JdbcTemplate with which to perform JDBC operations 049 * @param tableName name of the table to count rows in 050 * @return the number of rows in the table 051 */ 052 public static int countRowsInTable(JdbcTemplate jdbcTemplate, String tableName) { 053 Integer result = jdbcTemplate.queryForObject("SELECT COUNT(0) FROM " + tableName, Integer.class); 054 return (result != null ? result : 0); 055 } 056 057 /** 058 * Count the rows in the given table, using the provided {@code WHERE} clause. 059 * <p>If the provided {@code WHERE} clause contains text, it will be prefixed 060 * with {@code " WHERE "} and then appended to the generated {@code SELECT} 061 * statement. For example, if the provided table name is {@code "person"} and 062 * the provided where clause is {@code "name = 'Bob' and age > 25"}, the 063 * resulting SQL statement to execute will be 064 * {@code "SELECT COUNT(0) FROM person WHERE name = 'Bob' and age > 25"}. 065 * @param jdbcTemplate the JdbcTemplate with which to perform JDBC operations 066 * @param tableName the name of the table to count rows in 067 * @param whereClause the {@code WHERE} clause to append to the query 068 * @return the number of rows in the table that match the provided 069 * {@code WHERE} clause 070 */ 071 public static int countRowsInTableWhere(JdbcTemplate jdbcTemplate, String tableName, String whereClause) { 072 String sql = "SELECT COUNT(0) FROM " + tableName; 073 if (StringUtils.hasText(whereClause)) { 074 sql += " WHERE " + whereClause; 075 } 076 Integer result = jdbcTemplate.queryForObject(sql, Integer.class); 077 return (result != null ? result : 0); 078 } 079 080 /** 081 * Delete all rows from the specified tables. 082 * @param jdbcTemplate the JdbcTemplate with which to perform JDBC operations 083 * @param tableNames the names of the tables to delete from 084 * @return the total number of rows deleted from all specified tables 085 */ 086 public static int deleteFromTables(JdbcTemplate jdbcTemplate, String... tableNames) { 087 int totalRowCount = 0; 088 for (String tableName : tableNames) { 089 int rowCount = jdbcTemplate.update("DELETE FROM " + tableName); 090 totalRowCount += rowCount; 091 if (logger.isInfoEnabled()) { 092 logger.info("Deleted " + rowCount + " rows from table " + tableName); 093 } 094 } 095 return totalRowCount; 096 } 097 098 /** 099 * Delete rows from the given table, using the provided {@code WHERE} clause. 100 * <p>If the provided {@code WHERE} clause contains text, it will be prefixed 101 * with {@code " WHERE "} and then appended to the generated {@code DELETE} 102 * statement. For example, if the provided table name is {@code "person"} and 103 * the provided where clause is {@code "name = 'Bob' and age > 25"}, the 104 * resulting SQL statement to execute will be 105 * {@code "DELETE FROM person WHERE name = 'Bob' and age > 25"}. 106 * <p>As an alternative to hard-coded values, the {@code "?"} placeholder can 107 * be used within the {@code WHERE} clause, binding to the given arguments. 108 * @param jdbcTemplate the JdbcTemplate with which to perform JDBC operations 109 * @param tableName the name of the table to delete rows from 110 * @param whereClause the {@code WHERE} clause to append to the query 111 * @param args arguments to bind to the query (leaving it to the PreparedStatement 112 * to guess the corresponding SQL type); may also contain {@link SqlParameterValue} 113 * objects which indicate not only the argument value but also the SQL type and 114 * optionally the scale. 115 * @return the number of rows deleted from the table 116 */ 117 public static int deleteFromTableWhere( 118 JdbcTemplate jdbcTemplate, String tableName, String whereClause, Object... args) { 119 120 String sql = "DELETE FROM " + tableName; 121 if (StringUtils.hasText(whereClause)) { 122 sql += " WHERE " + whereClause; 123 } 124 int rowCount = (args.length > 0 ? jdbcTemplate.update(sql, args) : jdbcTemplate.update(sql)); 125 if (logger.isInfoEnabled()) { 126 logger.info("Deleted " + rowCount + " rows from table " + tableName); 127 } 128 return rowCount; 129 } 130 131 /** 132 * Drop the specified tables. 133 * @param jdbcTemplate the JdbcTemplate with which to perform JDBC operations 134 * @param tableNames the names of the tables to drop 135 */ 136 public static void dropTables(JdbcTemplate jdbcTemplate, String... tableNames) { 137 for (String tableName : tableNames) { 138 jdbcTemplate.execute("DROP TABLE " + tableName); 139 if (logger.isInfoEnabled()) { 140 logger.info("Dropped table " + tableName); 141 } 142 } 143 } 144 145}