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}