Java tutorial
/* * Copyright 2008, Unitils.org * * 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.unitils.core.dbsupport; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.unitils.core.UnitilsException; import static org.unitils.thirdparty.org.apache.commons.dbutils.DbUtils.closeQuietly; import javax.sql.DataSource; import java.sql.Connection; import java.sql.ResultSet; import java.sql.Statement; import java.util.HashSet; import java.util.Set; /** * Class to which database updates and queries are passed. Is in fact a utility class, but is a concrete instance to * enable decorating it or switching it with another implementation, allowing things like a dry run, creating a script * file or logging updates to a log file or database table. * * @author Filip Neven * @author Tim Ducheyne */ public class DefaultSQLHandler implements SQLHandler { /* The logger instance for this class */ private static Log logger = LogFactory.getLog(DefaultSQLHandler.class); /* The DataSource that provides access to the database, on which all queries and updates are executed */ private DataSource dataSource; /* * Boolean that indicates whether database updates have to executed on the database or not. Setting this value * to false can be useful when running in dry mode */ private boolean doExecuteUpdates; /** * Constructs a new instance that connects to the given DataSource * * @param dataSource The data source, not null */ public DefaultSQLHandler(DataSource dataSource) { this(dataSource, true); } /** * Constructs a new instance that connects to the given DataSource * * @param dataSource The data source, not null * @param doExecuteUpdates Boolean indicating whether updates should effectively be executed on the underlying * database */ public DefaultSQLHandler(DataSource dataSource, boolean doExecuteUpdates) { this.dataSource = dataSource; this.doExecuteUpdates = doExecuteUpdates; } /* (non-Javadoc) * @see org.unitils.core.dbsupport.SQLHandler#executeUpdate(java.lang.String) */ public int executeUpdate(String sql) { logger.debug(sql); if (!doExecuteUpdates) { // skip update return 0; } Connection connection = null; Statement statement = null; try { connection = dataSource.getConnection(); statement = connection.createStatement(); return statement.executeUpdate(sql); } catch (Exception e) { throw new UnitilsException("Error while performing database update: " + sql, e); } finally { closeQuietly(connection, statement, null); } } /* (non-Javadoc) * @see org.unitils.core.dbsupport.SQLHandler#executeQuery(java.lang.String) */ public void executeQuery(String sql) { logger.debug(sql); if (!doExecuteUpdates) { // skip query return; } Connection connection = null; Statement statement = null; ResultSet resultSet = null; try { connection = dataSource.getConnection(); statement = connection.createStatement(); resultSet = statement.executeQuery(sql); } catch (Exception e) { throw new UnitilsException("Error while performing database update: " + sql, e); } finally { closeQuietly(connection, statement, resultSet); } } /* (non-Javadoc) * @see org.dbmaintain.dbsupport.SQLHandler#executeUpdateAndCommit(java.lang.String) */ public int executeUpdateAndCommit(String sql) { logger.debug(sql); if (!doExecuteUpdates) { // skip update return 0; } Connection connection = null; Statement statement = null; try { connection = dataSource.getConnection(); statement = connection.createStatement(); int nbChanges = statement.executeUpdate(sql); if (!connection.getAutoCommit()) { connection.commit(); } return nbChanges; } catch (Exception e) { throw new UnitilsException("Error while performing database update: " + sql, e); } finally { closeQuietly(connection, statement, null); } } /* (non-Javadoc) * @see org.unitils.core.dbsupport.SQLHandler#getItemAsLong(java.lang.String) */ public long getItemAsLong(String sql) { logger.debug(sql); Connection connection = null; Statement statement = null; ResultSet resultSet = null; try { connection = dataSource.getConnection(); statement = connection.createStatement(); resultSet = statement.executeQuery(sql); if (resultSet.next()) { return resultSet.getLong(1); } } catch (Exception e) { throw new UnitilsException("Error while executing statement: " + sql, e); } finally { closeQuietly(connection, statement, resultSet); } // in case no value was found, throw an exception throw new UnitilsException("No item value found: " + sql); } /* (non-Javadoc) * @see org.unitils.core.dbsupport.SQLHandler#getItemAsString(java.lang.String) */ public String getItemAsString(String sql) { logger.debug(sql); Connection connection = null; Statement statement = null; ResultSet resultSet = null; try { connection = dataSource.getConnection(); statement = connection.createStatement(); resultSet = statement.executeQuery(sql); if (resultSet.next()) { return resultSet.getString(1); } } catch (Exception e) { throw new UnitilsException("Error while executing statement: " + sql, e); } finally { closeQuietly(connection, statement, resultSet); } // in case no value was found, throw an exception throw new UnitilsException("No item value found: " + sql); } /* (non-Javadoc) * @see org.unitils.core.dbsupport.SQLHandler#getItemsAsStringSet(java.lang.String) */ public Set<String> getItemsAsStringSet(String sql) { logger.debug(sql); Connection connection = null; Statement statement = null; ResultSet resultSet = null; try { connection = dataSource.getConnection(); statement = connection.createStatement(); resultSet = statement.executeQuery(sql); Set<String> result = new HashSet<String>(); while (resultSet.next()) { result.add(resultSet.getString(1)); } return result; } catch (Exception e) { throw new UnitilsException("Error while executing statement: " + sql, e); } finally { closeQuietly(connection, statement, resultSet); } } /* (non-Javadoc) * @see org.unitils.core.dbsupport.SQLHandler#exists(java.lang.String) */ public boolean exists(String sql) { logger.debug(sql); Connection connection = null; Statement statement = null; ResultSet resultSet = null; try { connection = dataSource.getConnection(); statement = connection.createStatement(); resultSet = statement.executeQuery(sql); return resultSet.next(); } catch (Exception e) { throw new UnitilsException("Error while executing statement: " + sql, e); } finally { closeQuietly(connection, statement, resultSet); } } /* (non-Javadoc) * @see org.unitils.core.dbsupport.SQLHandler#getDataSource() */ public DataSource getDataSource() { return dataSource; } /* (non-Javadoc) * @see org.unitils.core.dbsupport.SQLHandler#isDoExecuteUpdates() */ public boolean isDoExecuteUpdates() { return doExecuteUpdates; } }