| 1 | /** |
| 2 | * Copyright 2007 ATG DUST Project |
| 3 | * |
| 4 | * Licensed under the Apache License, Version 2.0 (the "License"); |
| 5 | * you may not use this file except in compliance with the License. |
| 6 | * |
| 7 | * You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 |
| 8 | * |
| 9 | * Unless required by applicable law or agreed to in writing, software |
| 10 | * distributed under the License is distributed on an "AS IS" BASIS, |
| 11 | * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| 12 | * See the License for the specific language governing permissions and limitations under the License. |
| 13 | */ |
| 14 | |
| 15 | package atg.adapter.gsa ; |
| 16 | |
| 17 | import java.sql.Connection; |
| 18 | import java.sql.ResultSet; |
| 19 | import java.sql.SQLException; |
| 20 | import java.sql.Statement; |
| 21 | import java.util.ArrayList; |
| 22 | import java.util.Collection; |
| 23 | import java.util.Iterator; |
| 24 | import java.util.LinkedList; |
| 25 | import java.util.List; |
| 26 | |
| 27 | import javax.sql.DataSource; |
| 28 | import javax.transaction.TransactionManager; |
| 29 | |
| 30 | import org.apache.log4j.Logger; |
| 31 | |
| 32 | import atg.dtm.TransactionDemarcation; |
| 33 | import atg.dtm.TransactionDemarcationException; |
| 34 | import atg.nucleus.GenericService; |
| 35 | import atg.service.jdbc.BasicDataSource; |
| 36 | |
| 37 | /** A generic class to execute SQL actions against a database. |
| 38 | * |
| 39 | * Parts copied from atg.service.idgen.?? by mgk |
| 40 | * |
| 41 | * @author mf |
| 42 | * @version 1.0 |
| 43 | **/ |
| 44 | public |
| 45 | class SQLProcessor |
| 46 | { |
| 47 | // =============== MEMBER VARIABLES ================= |
| 48 | |
| 49 | private static Logger log = Logger.getLogger(SQLProcessor.class); |
| 50 | |
| 51 | DataSource mDataSource; |
| 52 | /** sets the DataSource from which to get DB connections |
| 53 | **/ |
| 54 | public void setDataSource(DataSource pDataSource) { |
| 55 | mDataSource = pDataSource; } |
| 56 | /** returns the DataSource from which db connections are obtained */ |
| 57 | public DataSource getDataSource() { |
| 58 | return mDataSource;} |
| 59 | |
| 60 | TransactionManager mTxManager; |
| 61 | /** sets the TransactionManager that should be used to monitor transactions */ |
| 62 | public void setTransactionManager( TransactionManager pManager ) { |
| 63 | mTxManager = pManager; } |
| 64 | /** returns the TransactionManager that should be used to monitor transaction */ |
| 65 | public TransactionManager getTransactionManager() { |
| 66 | return mTxManager; } |
| 67 | |
| 68 | String mDetermineTableExistsSQL = "SELECT * from "; |
| 69 | /** sets String executed to determine whether a table exists. The table |
| 70 | * name is appended to the end of the string before execution occurs. |
| 71 | */ |
| 72 | public void setDetermineTableExistsSQL( String pStr ) { |
| 73 | mDetermineTableExistsSQL = pStr; } |
| 74 | /** returns String executed to determine whether a table exists. The table |
| 75 | * name is appended to the end of the string before execution occurs. |
| 76 | */ |
| 77 | public String getDetermineTableExistsSQL() { |
| 78 | return mDetermineTableExistsSQL; } |
| 79 | |
| 80 | String mDropTableSQL = "DROP TABLE "; |
| 81 | /** sets String executed to drop a table. The table name is appended to the |
| 82 | * end of the string before execution |
| 83 | */ |
| 84 | public void setDropTableSQL( String pStr ) { |
| 85 | mDropTableSQL = pStr; } |
| 86 | /** returns String executed to drop a table. The table name is appended to the |
| 87 | * end of the string before execution |
| 88 | */ |
| 89 | public String getDropTableSQL() { |
| 90 | return mDropTableSQL; } |
| 91 | |
| 92 | /** String delimiter used to separate a large String passed to |
| 93 | * createTables() into an array of individual Create Table statements |
| 94 | * default value is "CREATE TABLE" |
| 95 | * This delimiter _will_ be included in the final create statements |
| 96 | */ |
| 97 | String mCreateTableBeginDelimiter = "CREATE TABLE "; |
| 98 | public void setCreateTableBeginDelimiter( String pStr ) { |
| 99 | mCreateTableBeginDelimiter = pStr; } |
| 100 | public String getCreateTableBeginDelimiter() { |
| 101 | return mCreateTableBeginDelimiter; } |
| 102 | |
| 103 | /** String delimiter used to separate a large String passed to |
| 104 | * createTables() into an array of individual Create Table statements |
| 105 | * default value is ";" |
| 106 | * This delimiter _will not_ be included in the final create statements |
| 107 | */ |
| 108 | String mCreateTableEndDelimiter = ";"; |
| 109 | public void setCreateTableEndDelimiter( String pStr ) { |
| 110 | mCreateTableEndDelimiter = pStr; } |
| 111 | public String getCreateTableEndDelimiter() { |
| 112 | return mCreateTableEndDelimiter; } |
| 113 | |
| 114 | /** an optional GenericService component whose logging services should be used by |
| 115 | * this component. |
| 116 | */ |
| 117 | private GenericService mLogger; |
| 118 | public void setLoggingManager( GenericService pLogger ) { |
| 119 | mLogger = pLogger; } |
| 120 | public GenericService getLoggingManager() { |
| 121 | return mLogger; } |
| 122 | |
| 123 | // indicates whether to set autoCommit(true) on connections |
| 124 | private boolean mAutoCommit = false; |
| 125 | /** if set to true, then autoCommit will be set to true on all connections used to |
| 126 | * execute SQL. otherwise, autoCommit will not be altered from what is set by the |
| 127 | * DataSource. |
| 128 | */ |
| 129 | public void setAutoCommit( boolean pCommit ) { |
| 130 | mAutoCommit = pCommit; } |
| 131 | /** returns true if autoCommit should be set to true on all connections used to execute |
| 132 | * SQL. |
| 133 | */ |
| 134 | public boolean isSetAutoCommit() { |
| 135 | return mAutoCommit; } |
| 136 | |
| 137 | /* =========== CONSTRUCTORS ============= */ |
| 138 | |
| 139 | /** Construct with specified DataSource |
| 140 | * |
| 141 | * @param TransactionManager manager - the TransactionManager to use to monitor transactions |
| 142 | * @param DataSource dataSource - the DataSource to use for db connections |
| 143 | **/ |
| 144 | public SQLProcessor( TransactionManager pTxManager, DataSource pDataSource ) |
| 145 | { |
| 146 | setDataSource( pDataSource ); |
| 147 | setTransactionManager( pTxManager ); |
| 148 | } |
| 149 | |
| 150 | /** Constructor with specified user/password/driver/URL. specified parameters are used |
| 151 | * to create a DataSource connection to the database. |
| 152 | * |
| 153 | * @param TransactionManager manager - the TransactionManager to use to monitor transactions |
| 154 | * @param String username - name of user to connect to db |
| 155 | * @param String password - pwd to connectc to db |
| 156 | * @param String driver - driver specification to connect to db |
| 157 | * @param String url - url to connect to db |
| 158 | * @exception SQLException if an error occurs creating the DataSource |
| 159 | */ |
| 160 | public SQLProcessor( TransactionManager pTxManager, String pUsername, String pPassword, String pDriver, String pURL ) |
| 161 | throws SQLException |
| 162 | { |
| 163 | setDataSource( createBasicDataSource( pUsername, pPassword, pDriver, pURL ) ); |
| 164 | setTransactionManager( pTxManager ); |
| 165 | } |
| 166 | |
| 167 | // ==================== PUBLIC METHODS =========================== |
| 168 | |
| 169 | /** creates and returns a DataSource based on the user/pwd/driver/url info |
| 170 | * supplied. |
| 171 | */ |
| 172 | public static DataSource createBasicDataSource( String pUsername, |
| 173 | String pPassword, |
| 174 | String pDriver, |
| 175 | String pURL ) |
| 176 | { |
| 177 | BasicDataSource datasource = new BasicDataSource(); |
| 178 | datasource.setUser( pUsername ); |
| 179 | datasource.setPassword( pPassword ); |
| 180 | datasource.setDriver( pDriver ); |
| 181 | datasource.setURL( pURL ); |
| 182 | |
| 183 | return datasource; |
| 184 | } |
| 185 | |
| 186 | /** |
| 187 | * Perform the specified SQL statement in a new transaction which is commited. Autocommit |
| 188 | * on the connection is set to true if isSetAutoCommit() is true. |
| 189 | * |
| 190 | * @param pSQL SQL to execute |
| 191 | * |
| 192 | * @exception SQLException if there is DB problem |
| 193 | * @exception TransactionDemarcationException if there is a tx problem |
| 194 | **/ |
| 195 | public void executeSQL(String pSQL) |
| 196 | throws SQLException, TransactionDemarcationException |
| 197 | { |
| 198 | TransactionDemarcation td = new TransactionDemarcation(); |
| 199 | try |
| 200 | { |
| 201 | td.begin ( getTransactionManager(), TransactionDemarcation.REQUIRES_NEW); |
| 202 | Connection c = null; |
| 203 | Statement s = null; |
| 204 | try |
| 205 | { |
| 206 | // get DB connection |
| 207 | c = getConnection(); |
| 208 | if ( isSetAutoCommit() ) |
| 209 | c.setAutoCommit( true ); |
| 210 | |
| 211 | //most of this method is annoying try/catch/finally blocks |
| 212 | //inflicted on us by JTA. the real work is here. |
| 213 | s = c.createStatement(); |
| 214 | debug("Executing SQL [" + pSQL + "]"); |
| 215 | s.execute(pSQL); |
| 216 | } |
| 217 | finally |
| 218 | { |
| 219 | close(s); |
| 220 | close(c); |
| 221 | } |
| 222 | } |
| 223 | finally |
| 224 | { |
| 225 | td.end(); |
| 226 | } |
| 227 | } |
| 228 | |
| 229 | /** executes the specified query and returns a List of values for the specified column name. |
| 230 | * for example, executeQuery( "select * from user", "first_name" ) would return a List of |
| 231 | * the first names of all entries in the user table. |
| 232 | * |
| 233 | * @return List of Object values |
| 234 | * @exception SQLException if a sql error occurs |
| 235 | * @exception TransactionDemarcationException if a tx error occurs |
| 236 | */ |
| 237 | public List<?> executeQuery( String pQuery, String pColumnName ) |
| 238 | throws SQLException, TransactionDemarcationException |
| 239 | { |
| 240 | List<Object> results = new LinkedList<Object>(); |
| 241 | TransactionDemarcation td = new TransactionDemarcation(); |
| 242 | //int rows = 0; |
| 243 | try |
| 244 | { |
| 245 | td.begin ( getTransactionManager(), TransactionDemarcation.REQUIRES_NEW); |
| 246 | Connection c = null; |
| 247 | Statement s = null; |
| 248 | ResultSet rs = null; |
| 249 | try |
| 250 | { |
| 251 | // get DB connection |
| 252 | c = getConnection(); |
| 253 | |
| 254 | //most of this method is annoying try/catch/finally blocks |
| 255 | //inflicted on us by JTA. the real work is here. |
| 256 | s = c.createStatement(); |
| 257 | debug("Executing query [" + pQuery + "]"); |
| 258 | rs = s.executeQuery( pQuery ); |
| 259 | |
| 260 | while ( rs.next() ) { |
| 261 | results.add( rs.getObject( pColumnName) ); |
| 262 | } |
| 263 | } |
| 264 | finally |
| 265 | { |
| 266 | close(rs); |
| 267 | close(s); |
| 268 | close(c); |
| 269 | } |
| 270 | } |
| 271 | finally |
| 272 | { |
| 273 | td.end(); |
| 274 | } |
| 275 | return results; |
| 276 | } |
| 277 | |
| 278 | /** Method that iteratively attempts to drop tables. An iterative |
| 279 | * effort is utilized in case references exist between tables. |
| 280 | * |
| 281 | * ASSUMPTION: references only exist between tables specified in the |
| 282 | * List. If references exist from tables outside the List, then some |
| 283 | * tables may not be able to be dropped and this method will throw a |
| 284 | * SQLException |
| 285 | * |
| 286 | * @param Collection of names of tables to be dropped |
| 287 | * @param boolean cascadeConstraints. true if 'CASCADE CONSTRAINTS' should be used in |
| 288 | * drop statement. |
| 289 | * @param boolean preview. if true then iterative behavior is disabled and method simply |
| 290 | * prints one drop statement that would be executed for each table. iterative behavior has |
| 291 | * to be disabled since it doesn't make sense if drops are not being executed. |
| 292 | * @exception SQLException thrown if all tables can not be dropped |
| 293 | */ |
| 294 | public void dropTables( Collection<String> pNames, boolean pCascadeConstraints, boolean pPreview ) |
| 295 | throws SQLException, TransactionDemarcationException |
| 296 | { |
| 297 | // just show drops once if preview is true |
| 298 | if ( pPreview ) { |
| 299 | Iterator<String> tables = pNames.iterator(); |
| 300 | while ( tables.hasNext() ) { |
| 301 | dropTable(tables.next(), pCascadeConstraints, pPreview ); |
| 302 | } |
| 303 | return; |
| 304 | } |
| 305 | |
| 306 | // assuming only one table can be dropped each time, this should take |
| 307 | // at most n iterations where n is the nbr of tables being dropped |
| 308 | int maxIterations = pNames.size(); |
| 309 | |
| 310 | // every table is tried at least once |
| 311 | Collection<String> tablesToDrop = pNames; |
| 312 | |
| 313 | List<String> remainingTables; |
| 314 | int attempt = 0; |
| 315 | do { |
| 316 | remainingTables = new ArrayList<String>(); |
| 317 | Iterator<String> tables = tablesToDrop.iterator(); |
| 318 | while ( tables.hasNext() ) { |
| 319 | String table = tables.next(); |
| 320 | if ( tableExists( table ) ) { |
| 321 | try { |
| 322 | dropTable( table, pCascadeConstraints, pPreview ); |
| 323 | debug( "Dropped table: " + table); |
| 324 | } catch ( SQLException se ) { |
| 325 | // if this is the last iteration, throw an exception |
| 326 | if ( attempt+1 >= maxIterations ) |
| 327 | throw se; |
| 328 | |
| 329 | // otherwise track this table for the next try |
| 330 | remainingTables.add( table ); |
| 331 | } |
| 332 | } |
| 333 | } |
| 334 | tablesToDrop = remainingTables; |
| 335 | |
| 336 | } while ( ( attempt++ < maxIterations ) |
| 337 | && ( ! remainingTables.isEmpty() ) ); |
| 338 | } |
| 339 | |
| 340 | // ====================== PRIVATE METHODS ========================== |
| 341 | |
| 342 | /** |
| 343 | * Get a DB connection |
| 344 | * @return the connection |
| 345 | * @exception SQLProcessorException if there is DB trouble or |
| 346 | * DataSource trouble |
| 347 | **/ |
| 348 | Connection getConnection() |
| 349 | throws SQLException |
| 350 | { |
| 351 | if (getDataSource() == null) |
| 352 | throw new SQLException("DataSource is null."); |
| 353 | |
| 354 | return getDataSource().getConnection(); |
| 355 | } |
| 356 | |
| 357 | /** |
| 358 | * Close a DB connection. It is okay to pass a null connection here |
| 359 | * |
| 360 | * @param pConnection connection to close, may be null |
| 361 | * @exception SQLException if an error occurs trying to close a non-null connection |
| 362 | **/ |
| 363 | private final void close(Connection pConnection) |
| 364 | throws SQLException |
| 365 | { |
| 366 | if (pConnection != null) |
| 367 | pConnection.close(); |
| 368 | } |
| 369 | |
| 370 | /** |
| 371 | * Close a result set. It is okay to pass a null here |
| 372 | * |
| 373 | * @param pResultSet result set to close, may be null |
| 374 | * @exception SQLException if an error occurs closing a non-null ResultSet |
| 375 | **/ |
| 376 | private final void close(ResultSet pResultSet) |
| 377 | throws SQLException |
| 378 | { |
| 379 | if (pResultSet != null) |
| 380 | pResultSet.close(); |
| 381 | } |
| 382 | |
| 383 | /** |
| 384 | * Close a statement. It is okay to pass a null here. |
| 385 | * |
| 386 | * @param pStatement statement to close, may be null |
| 387 | * @exception SQLException if an error occurs closing a non-null Statement |
| 388 | **/ |
| 389 | private final void close(Statement pStatement) |
| 390 | throws SQLException |
| 391 | { |
| 392 | if (pStatement != null) |
| 393 | pStatement.close(); |
| 394 | } |
| 395 | |
| 396 | /** This method is used to execute a 'Drop Table' call. The |
| 397 | * method creates the drop table statement by appending the name |
| 398 | * passed as a method with the SQL that has been set as the dropTableSQL |
| 399 | * property. By default, this property is set to "Drop table" |
| 400 | * |
| 401 | * @param String - the name of the table to drop |
| 402 | * @param boolean cascadeConstraints. true if 'CASCADE CONSTRAINTS' should be used in |
| 403 | * drop statement. |
| 404 | * @exception SQLException thrown if an error occurs trying |
| 405 | * to drop the table |
| 406 | */ |
| 407 | private void dropTable( String pName, boolean pCascadeConstraints, boolean pPreview ) |
| 408 | throws SQLException, TransactionDemarcationException |
| 409 | { |
| 410 | String sql = getDropTableSQL() + " " + pName; |
| 411 | if ( pCascadeConstraints ) sql = sql + " CASCADE CONSTRAINTS"; |
| 412 | |
| 413 | if ( pPreview ) log.info( sql ); |
| 414 | else executeSQL( sql ); |
| 415 | } |
| 416 | |
| 417 | /** |
| 418 | * Method to determine whether a table already exists in the |
| 419 | * database. The method operates by appending the name passed |
| 420 | * as a parameter to the String that has been set in the |
| 421 | * determineTableExistsSQL property |
| 422 | * |
| 423 | * @param String - name of table to check for existence of |
| 424 | * @return boolean - true if table exists; false otherwise |
| 425 | * @exception TransactionDemarcationException if a tx error occurs |
| 426 | */ |
| 427 | private boolean tableExists( String pTableName ) |
| 428 | throws TransactionDemarcationException |
| 429 | { |
| 430 | // don't bother with query if name is invalid |
| 431 | if ( pTableName == null || |
| 432 | pTableName.length() == 0 ) |
| 433 | return false; |
| 434 | |
| 435 | // create sql |
| 436 | String sql = getDetermineTableExistsSQL() + " " + pTableName; |
| 437 | |
| 438 | // execute and check for an exception |
| 439 | try { |
| 440 | executeSQL( sql ); |
| 441 | } catch ( SQLException spe ) { |
| 442 | // we should only get an exception here if the table doesn't exist |
| 443 | // so just return false |
| 444 | return false; |
| 445 | } |
| 446 | |
| 447 | return true; |
| 448 | } |
| 449 | |
| 450 | /** a utility method to assist with logging */ |
| 451 | private void debug( Object pMsg ) { |
| 452 | if ( getLoggingManager() != null && getLoggingManager().isLoggingDebug() ) |
| 453 | getLoggingManager().logDebug("SQLProcessor: " + pMsg.toString()); |
| 454 | } |
| 455 | } |