001: /*
002: * Copyright 2006-2007, Unitils.org
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: * http://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: package org.unitils.core.dbsupport;
017:
018: import org.apache.commons.logging.Log;
019: import org.apache.commons.logging.LogFactory;
020: import org.unitils.core.UnitilsException;
021: import static org.unitils.thirdparty.org.apache.commons.dbutils.DbUtils.closeQuietly;
022:
023: import javax.sql.DataSource;
024: import java.sql.Connection;
025: import java.sql.ResultSet;
026: import java.sql.Statement;
027: import java.util.HashSet;
028: import java.util.Set;
029:
030: /**
031: * Class to which database updates and queries are passed. Is in fact a utility class, but is a concrete instance to
032: * enable decorating it or switching it with another implementation, allowing things like a dry run, creating a script
033: * file or logging updates to a log file or database table.
034: *
035: * @author Filip Neven
036: * @author Tim Ducheyne
037: */
038: public class SQLHandler {
039:
040: /* The logger instance for this class */
041: private static Log logger = LogFactory.getLog(SQLHandler.class);
042:
043: /* The DataSource that provides access to the database, on which all queries and updates are executed */
044: private DataSource dataSource;
045:
046: /* Boolean that indicates whether database updates have to executed on the database or not. Setting this value
047: to false can be useful when running in dry mode */
048: private boolean doExecuteUpdates;
049:
050: /**
051: * Constructs a new instance that connects to the given DataSource
052: *
053: * @param dataSource The data source, not null
054: */
055: public SQLHandler(DataSource dataSource) {
056: this (dataSource, true);
057: }
058:
059: /**
060: * Constructs a new instance that connects to the given DataSource
061: *
062: * @param dataSource The data source, not null
063: * @param doExecuteUpdates Boolean indicating whether updates should effectively be executed on the underlying
064: * database
065: */
066: public SQLHandler(DataSource dataSource, boolean doExecuteUpdates) {
067: this .dataSource = dataSource;
068: this .doExecuteUpdates = doExecuteUpdates;
069: }
070:
071: /**
072: * Executes the given statement.
073: *
074: * @param sql The sql string for retrieving the items
075: * @return The nr of updates
076: */
077: public int executeUpdate(String sql) {
078: logger.info(sql);
079:
080: if (!doExecuteUpdates) {
081: // skip update
082: return 0;
083: }
084: Connection connection = null;
085: Statement statement = null;
086: try {
087: connection = dataSource.getConnection();
088: statement = connection.createStatement();
089: return statement.executeUpdate(sql);
090:
091: } catch (Exception e) {
092: throw new UnitilsException(
093: "Error while executing statement: " + sql, e);
094: } finally {
095: closeQuietly(connection, statement, null);
096: }
097: }
098:
099: /**
100: * Executes the given code update statement.
101: *
102: * @param sql The sql string for retrieving the items
103: * @return The nr of updates
104: */
105: public int executeCodeUpdate(String sql) {
106: logger.info(sql);
107:
108: if (!doExecuteUpdates) {
109: // skip update
110: return 0;
111: }
112: Connection connection = null;
113: Statement statement = null;
114: try {
115: connection = dataSource.getConnection();
116: statement = connection.createStatement();
117: return statement.executeUpdate(sql);
118:
119: } catch (Exception e) {
120: throw new UnitilsException(
121: "Error while executing statement: " + sql, e);
122: } finally {
123: closeQuietly(connection, statement, null);
124: }
125: }
126:
127: /**
128: * Returns the long extracted from the result of the given query. If no value is found, a {@link UnitilsException}
129: * is thrown.
130: *
131: * @param sql The sql string for retrieving the items
132: * @return The long item value
133: */
134: public long getItemAsLong(String sql) {
135: logger.debug(sql);
136:
137: Connection connection = null;
138: Statement statement = null;
139: ResultSet resultSet = null;
140: try {
141: connection = dataSource.getConnection();
142: statement = connection.createStatement();
143: resultSet = statement.executeQuery(sql);
144: if (resultSet.next()) {
145: return resultSet.getLong(1);
146: }
147: } catch (Exception e) {
148: throw new UnitilsException(
149: "Error while executing statement: " + sql, e);
150: } finally {
151: closeQuietly(connection, statement, resultSet);
152: }
153:
154: // in case no value was found, throw an exception
155: throw new UnitilsException("No item value found: " + sql);
156: }
157:
158: /**
159: * Returns the value extracted from the result of the given query. If no value is found, a {@link UnitilsException}
160: * is thrown.
161: *
162: * @param sql The sql string for retrieving the items
163: * @return The string item value
164: */
165: public String getItemAsString(String sql) {
166: logger.debug(sql);
167:
168: Connection connection = null;
169: Statement statement = null;
170: ResultSet resultSet = null;
171: try {
172: connection = dataSource.getConnection();
173: statement = connection.createStatement();
174: resultSet = statement.executeQuery(sql);
175: if (resultSet.next()) {
176: return resultSet.getString(1);
177: }
178: } catch (Exception e) {
179: throw new UnitilsException(
180: "Error while executing statement: " + sql, e);
181: } finally {
182: closeQuietly(connection, statement, resultSet);
183: }
184:
185: // in case no value was found, throw an exception
186: throw new UnitilsException("No item value found: " + sql);
187: }
188:
189: /**
190: * Returns the items extracted from the result of the given query.
191: *
192: * @param sql The sql string for retrieving the items
193: * @return The items, not null
194: */
195: public Set<String> getItemsAsStringSet(String sql) {
196: logger.debug(sql);
197:
198: Connection connection = null;
199: Statement statement = null;
200: ResultSet resultSet = null;
201: try {
202: connection = dataSource.getConnection();
203: statement = connection.createStatement();
204: resultSet = statement.executeQuery(sql);
205: Set<String> result = new HashSet<String>();
206: while (resultSet.next()) {
207: result.add(resultSet.getString(1));
208: }
209: return result;
210:
211: } catch (Exception e) {
212: throw new UnitilsException(
213: "Error while executing statement: " + sql, e);
214: } finally {
215: closeQuietly(connection, statement, resultSet);
216: }
217: }
218:
219: /**
220: * Returns true if the query returned a record.
221: *
222: * @param sql The sql string for checking the existence
223: * @return True if a record was returned
224: */
225: public boolean exists(String sql) {
226: logger.debug(sql);
227:
228: Connection connection = null;
229: Statement statement = null;
230: ResultSet resultSet = null;
231: try {
232: connection = dataSource.getConnection();
233: statement = connection.createStatement();
234: resultSet = statement.executeQuery(sql);
235: return resultSet.next();
236:
237: } catch (Exception e) {
238: throw new UnitilsException(
239: "Error while executing statement: " + sql, e);
240: } finally {
241: closeQuietly(connection, statement, resultSet);
242: }
243: }
244:
245: /**
246: * @return The DataSource
247: */
248: public DataSource getDataSource() {
249: return dataSource;
250: }
251:
252: /**
253: * @return Whether updates are executed on the database or not
254: */
255: public boolean isDoExecuteUpdates() {
256: return doExecuteUpdates;
257: }
258: }
|