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
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
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
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
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
207 c = getConnection();
208 if ( isSetAutoCommit() )
209 c.setAutoCommit( true );
210
211
212
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
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
252 c = getConnection();
253
254
255
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
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
307
308 int maxIterations = pNames.size();
309
310
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
326 if ( attempt+1 >= maxIterations )
327 throw se;
328
329
330 remainingTables.add( table );
331 }
332 }
333 }
334 tablesToDrop = remainingTables;
335
336 } while ( ( attempt++ < maxIterations )
337 && ( ! remainingTables.isEmpty() ) );
338 }
339
340
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
431 if ( pTableName == null ||
432 pTableName.length() == 0 )
433 return false;
434
435
436 String sql = getDetermineTableExistsSQL() + " " + pTableName;
437
438
439 try {
440 executeSQL( sql );
441 } catch ( SQLException spe ) {
442
443
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 }