001 /*
002 * Copyright 2001,2004 The Apache Software Foundation.
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
017 package org.apache.commons.scaffold.sql;
018
019
020 import java.sql.Connection;
021 import java.sql.SQLException;
022 import java.sql.PreparedStatement;
023 import java.sql.Statement;
024 import java.sql.ResultSet;
025
026 import java.util.Collection;
027
028
029 /**
030 * General purpose SQL Statements.
031 *
032 * @author Ted Husted
033 * @version $Revision: 155464 $ $Date: 2005-02-26 13:26:54 +0000 (Sat, 26 Feb 2005) $
034 */
035 public final class StatementUtils {
036
037 /**
038 * The delimiter used by a SQL "LIKE" expression.
039 */
040 private static final String LIKE_DELIM = "%";
041
042
043 /**
044 * Prepare the parameter for use in a SQL "LIKE" expression.
045 */
046 public static final String like(String parameter) {
047
048 return LIKE_DELIM + parameter + LIKE_DELIM;
049
050 }
051
052
053 /**
054 * Create a new database table in an existing database,
055 * by sending "CREATE TABLE " and the parameters to
056 * the DBMS configured with the ConnectionPool.
057 * <p>
058 * For safety, does <b>not</b> drop table first.
059 * <p>
060 * Returns false if a SQL exception is thrown; exception
061 * is written to the servlet log.
062 *
063 * @param resource The database resource key or null for default
064 * @param tableName The name of the table to create
065 * @param tableCreate The SQL command defining the
066 * fields and indices
067 * @return Result of statement.execute()
068 * @exception SQL Exception if SQL error occurs
069 */
070 public static final int createTable(
071 String resource,
072 String tableName,
073 String tableCreate)
074 throws SQLException {
075
076 return executeUpdate(resource,"CREATE TABLE " +
077 tableName + " " + tableCreate);
078
079 } // end createTable
080
081
082
083 /**
084 * Returns next sequential key for given table.
085 * <p>
086 * This ensures compatibility for DBMS products that do not
087 * support auto-incrementing a key field.
088 * <p>
089 * Intended to generate primary keys, but could be used to
090 * create other serial numbers based on an unsigned int.
091 * <p>
092 * Allocating the key involves reading the current key, and
093 * then incrementing the key for the next user. The method
094 * is synchronized so that two threads do not read the
095 * same key before it is incremented.
096 * <p>
097 * This routine is "unrolled" for efficiency, but the same
098 * effect can be accomplished using <code>getColumn()</code>
099 * to fetch the next key,
100 * and <code>executeUpdate</code> to increment the key.
101 *
102 * @param resource The database resource key or null for default
103 * @param column The column to return from the result set.
104 * @param query The SQL statement to fetch the next key.
105 * @param key The table name or other replaceable parameter.
106 * @param query The SQL statement to increment the next key.
107 * @exception SQLException if SQL error occurs
108 */
109 public static final synchronized Object createKey(
110 String resource,
111 int column,
112 String query,
113 Object key,
114 String update)
115 throws SQLException {
116
117 Connection connection = null;
118 ResultSet resultSet = null;
119 PreparedStatement preparedStatement = null;
120 Object result = null;
121 int resultCode = 0;
122
123 try {
124 connection =
125 ConnectionAdaptor.getPool().getConnection(resource);
126
127 // fetch the next key
128 // - Object next = getColumn(resource,1,query,key);
129 preparedStatement = connection.prepareStatement(query);
130 preparedStatement.setObject(1,key);
131 resultSet = preparedStatement.executeQuery();
132 if (resultSet.next()) {
133 result = resultSet.getObject(column);
134 }
135
136 // increment key (SQL command handles increment)
137 // - int result = executeUpdate(resource,update,key);
138 if (preparedStatement!=null) preparedStatement.close();
139 preparedStatement = connection.prepareStatement(update);
140 preparedStatement.setObject(1,key);
141 resultCode = preparedStatement.executeUpdate();
142 }
143
144 finally {
145 try {
146 if (preparedStatement!=null) preparedStatement.close();
147 if (resultSet!=null) resultSet.close();
148 if (connection!=null) connection.close();
149 }
150 catch (SQLException sqle) {
151 // do nothing
152 }
153 }
154
155 return result;
156
157 } // end createKey
158
159
160 // ------------------------------------------------------ executeUpdate
161
162
163 /**
164 * Used to select correct executeUpdate signature.
165 */
166 private static final Object[] nullParams = null;
167
168
169 /**
170 * Prepares statement using SQL statement and executes
171 * with DBMS configured with the ConnectionPool.
172 * <p>
173 * Command may be an INSERT, UPDATE, or DELETE statement
174 * or anything that returns nothing, such as a DDL
175 * statement.
176 *
177 * @param connection The database connection or null to acquire
178 * @param resource The database resource key or null for default
179 * @param command The SQL statement to execute.
180 * @param parameters An array of parameter objects
181 * @exception SQLException if SQL error occurs
182 */
183 public static final int executeUpdate(
184 Connection connection,
185 String resource,
186 String command,
187 Object[] parameters)
188 throws SQLException {
189
190 boolean acqConnection = (connection==null);
191 Statement statement = null;
192 PreparedStatement preparedStatement = null;
193 int result = 0;
194
195 try {
196
197 if (acqConnection) {
198 connection =
199 ConnectionAdaptor.getPool().getConnection(resource);
200 }
201
202 if ((parameters==null) || (parameters.length==0)) {
203 statement = connection.createStatement();
204 result = statement.executeUpdate(command);
205 }
206 else {
207 preparedStatement = connection.prepareStatement(command);
208 for (int i=0; i<parameters.length; i++) {
209 preparedStatement.setObject(i+1, parameters[i]);
210 }
211 result = preparedStatement.executeUpdate();
212 }
213
214 } // end try
215
216 finally {
217 try {
218 if (preparedStatement != null) preparedStatement.close();
219 if (statement != null) statement.close();
220 if ((acqConnection) && (connection!= null)) connection.close();
221 }
222 catch (SQLException sqle) {
223 // do nothing
224 }
225 }
226
227 return result;
228
229 } // end executeUpdate
230
231
232 /**
233 * Convenience method for calling
234 * <code>executeUpdate(String,String,Object[]);</code>
235 * with a single Object parameter.
236 *
237 * @param resource The database resource key or null for default
238 * @param command The SQL statement to execute.
239 * @param parameter A single parameter to use with command
240 * @exception SQLException if SQL error occurs
241 */
242 public static final int executeUpdate(
243 String resource,
244 String command,
245 Object parameter)
246 throws SQLException {
247
248 Object[] parameters = new Object[1];
249 parameters[0] = parameter;
250
251 return executeUpdate(null,resource,command,parameters);
252
253 } // end executeUpdate
254
255
256 /**
257 * Convenience method for calling
258 * <code>executeUpdate(String,String,Object[]);</code>
259 * without a parameter list.
260 *
261 * @param resource The database resource key or null for default
262 * @param command The SQL statement to execute.
263 * @exception SQLException if SQL error occurs
264 */
265 public static final int executeUpdate(
266 String resource,
267 String command)
268 throws SQLException {
269
270 return executeUpdate(null,resource,command,nullParams);
271
272 } // end executeUpdate
273
274
275 /**
276 * Prepares statement using SQL statement and executes
277 * with DBMS configured with the ConnectionPool.
278 * <p>
279 * Command may be an INSERT, UPDATE, or DELETE statement
280 * or anything that returns nothing, such as a DDL
281 * statement.
282 *
283 * @param resource The database resource key or null for default
284 * @param command The SQL statement to execute.
285 * @param parameters An array of parameter objects
286 * @exception SQLException if SQL error occurs
287 */
288 public static final int executeUpdate(
289 String resource,
290 String command,
291 Object[] parameters)
292 throws SQLException {
293
294 return executeUpdate(null,resource,command,parameters);
295
296 } // end executeUpdate
297
298
299 /**
300 * Convenience method for calling
301 * <code>executeUpdate(String,String,Object[]);</code>
302 * without a parameter list.
303 *
304 * @param resource The database resource key or null for default
305 * @param command The SQL statement to execute.
306 * @exception SQLException if SQL error occurs
307 */
308 public static final int executeUpdate(
309 String command)
310 throws SQLException {
311
312 return executeUpdate(null,null,command,nullParams);
313
314 } // end executeUpdate
315
316
317
318 // ------------------------------------------------------- executeQuery
319
320 /**
321 * Merges a SQL command with an array of parameters.
322 * Any number or no parameters may be passed.
323 * The parameters parameter may also be null.
324 * <p>
325 * The Statement or PreparedStatement used internally
326 * are closed, but the Connection is left open so that the
327 * ResultSet remains valid.
328 * The caller should close the Connection and ResultSet
329 * as soon as possible, especially if they are pooled.
330 *
331 * @param resource The database resource key or null for default
332 * @param target The JavaBean object to return in the collection.
333 * @param command The SQL statement to prepare and execute.
334 * @param parameters The replaceable parameters to use with command.
335 * @exception SQLException if SQL error occurs
336 */
337 public static final ResultSet executeQuery(
338 Connection connection,
339 String command,
340 Object[] parameters)
341 throws SQLException {
342
343 Statement statement = null;
344 PreparedStatement preparedStatement = null;
345 ResultSet resultSet = null;
346
347 try {
348
349 if ((parameters==null) || (parameters.length==0)) {
350 statement = connection.createStatement();
351 resultSet = statement.executeQuery(command);
352 }
353 else {
354 preparedStatement = connection.prepareStatement(command);
355 for (int i=0; i<parameters.length; i++) {
356 preparedStatement.setObject(i+1, parameters[i]);
357 }
358 resultSet = preparedStatement.executeQuery();
359 }
360
361 }
362
363 finally {
364 try {
365 if (statement != null) statement.close();
366 if (preparedStatement != null) preparedStatement.close();
367 }
368 catch (SQLException sqle) {}
369 }
370
371 return resultSet;
372
373 } // end executeQuery
374
375
376 // ---------------------------------------------------------- getColumn
377
378 /**
379 * Merges a SQL command with an array of parameters
380 * and returns a column from the first record of the result set as
381 * an Object.
382 * If an empty set results, null is returned.
383 * Any number or no parameters may be passed.
384 * The parameters parameter may also be null.
385 * The SQL Statement/ResultSet are handled by
386 * <code>ExecuteQuery()</code>.
387 * The ResultSet is converted to a collection using
388 * <code>ResultSetUtils.getCollection(Object,ResultSet)</code>.
389 * The ResultSet is released, and the Collection returned.
390 *
391 * @param resource The database resource key or null for default
392 * @param column The column to return from the result set
393 * @param command The SQL statement to prepare and execute.
394 * @param parameters The replaceable parameters to use with command.
395 * @exception SQLException if SQL error occurs
396 */
397 public static final Object getColumn(
398 String resource,
399 int column,
400 String command,
401 Object[] parameters)
402 throws SQLException {
403
404 Object result = null;
405 Connection connection = null;
406 ResultSet resultSet = null;
407
408 try {
409
410 connection =
411 ConnectionAdaptor.getPool().getConnection(resource);
412 resultSet = executeQuery(connection,command,parameters);
413 if (resultSet.next()) {
414 result = resultSet.getObject(column);
415 }
416
417 }
418
419 finally {
420 try {
421
422 if (resultSet!=null) resultSet.close();
423 if (connection!=null) connection.close();
424 }
425 catch (SQLException sqle) {
426 // do nothing
427 }
428 }
429
430 return result;
431
432 } // end getColumn
433
434
435 /**
436 * Convenience method for calling
437 * <code>getColumn(String,int,String,Object[]);</code>
438 * with a single object parameter.
439 *
440 * @param resource The database resource key or null for default
441 * @param column The column to return from the result set.
442 * @param command The SQL statement to prepare and execute.
443 * @param key The replaceable parameter
444 * @exception SQLException if SQL error occurs
445 */
446 public static final Object getColumn(
447 String resource,
448 int column,
449 String command,
450 Object key)
451 throws SQLException {
452
453 Object[] parameters = new Object[1];
454 parameters[0] = key;
455 return getColumn(resource,column,command,parameters);
456
457 } // end getColumn
458
459
460 /**
461 * Convenience method for calling
462 * <code>getColumn(String,Object,String,Object[]);</code>
463 *
464 * with a single int parameter.
465 * @param resource The database resource key or null for default
466 * @param column The column to return from the result set.
467 * @param command The SQL statement to prepare and execute.
468 * @param key The replaceable parameter to use, if any.
469 * @exception SQLException if SQL error occurs
470 */
471 public static final Object getColumn(
472 String resource,
473 int column,
474 String command,
475 int key)
476 throws SQLException {
477
478 Object[] parameters = new Object[1];
479 parameters[0] = new Integer(key);
480
481 return getColumn(resource,column,command,parameters);
482
483 } // end getColumn
484
485
486 /**
487 * Convenience method for calling
488 * <code>getColumn(String,Object,String,Object[]);</code>
489 * without a parameter list.
490 *
491 * @param resource The database resource key or null for default
492 * @param column The column to return from the result set.
493 * @param command The SQL statement to prepare and execute.
494 * @param key The replaceable parameter.
495 * @exception SQLException if SQL error occurs
496 */
497 public static final Object getColumn(
498 String resource,
499 int column,
500 String command)
501 throws SQLException {
502
503 return getColumn(resource,column,command,null);
504
505 } // end getColumn
506
507
508 // ------------------------------------------------------ getElement
509
510 /**
511 * Merges a SQL command with an array of parameters.
512 * Any number or no parameters may be passed.
513 * The parameters parameter may also be null.
514 * The SQL Statement/ResultSet are handled by
515 * <code>ExecuteQuery()</code>.
516 * The first record of the ResultSet is used to populate
517 * <code>ResultSetUtils.getElement(Object,ResultSet)</code>.
518 * The ResultSet is released, and the bean returned.
519 *
520 * @param resource The database resource key or null for default
521 * @param target The JavaBean object to populate.
522 * @param command The SQL statement to prepare and execute.
523 * @param parameters The replaceable parameters to use with
524 * command.
525 * @exception SQLException if SQL error occurs
526 * @return True if element is found
527 */
528 public static final boolean getElement(
529 String resource,
530 Object target,
531 String command,
532 Object[] parameters)
533 throws SQLException {
534
535 Object collection = null;
536 Connection connection = null;
537 ResultSet resultSet = null;
538 boolean found = false;
539
540 try {
541
542 connection =
543 ConnectionAdaptor.getPool().getConnection(resource);
544 resultSet = executeQuery(connection,command,parameters);
545 found = ResultSetUtils.getElement(target,resultSet);
546 }
547
548 finally {
549 try {
550 if (resultSet!=null) resultSet.close();
551 if (connection!=null) connection.close();
552 }
553 catch (SQLException sqle) {
554 // do nothing
555 }
556 }
557
558 return found;
559
560 } // end getElement
561
562
563 /**
564 * Convenience method for calling
565 * <code>getElement(String,Object,String,Object[]);</code>
566 * with a single object parameter.
567 *
568 * @param resource The database resource key or null for default
569 * @param target The JavaBean object to populate.
570 * @param command The SQL statement to prepare and execute.
571 * @param key The replaceable parameter to use with LIKE.
572 * @exception SQLException if SQL error occurs
573 * @return True if element is found
574 */
575 public static final boolean getElement(
576 String resource,
577 Object target,
578 String command,
579 Object key)
580 throws SQLException {
581
582 Object[] parameters = new Object[1];
583 parameters[0] = key;
584 return getElement(resource,target,command,parameters);
585
586 } // end getElement
587
588
589 /**
590 * Convenience method for calling
591 * <code>getElement(String,Object,String,Object[]);</code>
592 * with a single int parameter.
593 *
594 * @param resource The database resource key or null for default
595 * @param target The JavaBean object to populate.
596 * @param command The SQL statement to prepare and execute.
597 * @param key The replaceable parameter to use with LIKE.
598 * @exception SQLException if SQL error occurs
599 * @return True if element is found
600 */
601 public static final boolean getElement(
602 String resource,
603 Object target,
604 String command,
605 int key)
606 throws SQLException {
607
608 Object[] parameters = new Object[1];
609 parameters[0] = new Integer(key);
610 return getElement(resource,target,command,parameters);
611
612 } // end getElement
613
614
615 /**
616 * Convenience method for calling
617 * <code>getElement(String,Object,String,Object[]);</code>
618 * without a parameter list.
619 *
620 * @param resource The database resource key or null for default
621 * @param target The JavaBean object to populate.
622 * @param command The SQL statement to prepare and execute.
623 * @param key The replaceable parameter to use with LIKE.
624 * @exception SQLException if SQL error occurs
625 * @return True if element is found
626 */
627 public static final boolean getElement(
628 String resource,
629 Object target,
630 String command)
631 throws SQLException {
632
633 return getElement(resource,target,command,null);
634
635 } // end getElement
636
637
638 // ------------------------------------------------------ getCollection
639
640 /**
641 * Merges a SQL command with an array of parameters.
642 * Any number or no parameters may be passed.
643 * The parameters parameter may also be null.
644 * The SQL Statement/ResultSet are handled by
645 * <code>ExecuteQuery()</code>.
646 * The ResultSet is converted to a collection using
647 * <code>ResultSetUtils.getCollection(Object,ResultSet)</code>.
648 * The ResultSet is released, and the Collection returned.
649 *
650 * @param resource The database resource key or null for default
651 * @param target The JavaBean object to return in the collection.
652 * @param command The SQL statement to prepare and execute.
653 * @param parameters The replaceable parameters to use with command.
654 * @exception SQLException if SQL error occurs
655 */
656 public static final Collection getCollection(
657 String resource,
658 Object target,
659 String command,
660 Object[] parameters)
661 throws SQLException {
662
663 Object collection = null;
664 Connection connection = null;
665 ResultSet resultSet = null;
666
667 try {
668
669 connection =
670 ConnectionAdaptor.getPool().getConnection(resource);
671 resultSet = executeQuery(connection,command,parameters);
672 collection = ResultSetUtils.getCollection(target,resultSet);
673 }
674
675 finally {
676 try {
677 if (resultSet!=null) resultSet.close();
678 if (connection!=null) connection.close();
679 }
680 catch (SQLException sqle) {
681 // do nothing
682 }
683 }
684
685 return (Collection) collection;
686
687 } // end getCollection
688
689
690 /**
691 * Convenience method for calling
692 * <code>getCollection(String,Object,String,Object[]);</code>
693 * with a single object parameter.
694 *
695 * @param resource The database resource key or null for default
696 * @param target The JavaBean object to return in the collection.
697 * @param command The SQL statement to prepare and execute.
698 * @param key The replaceable parameter to use with LIKE.
699 * @exception SQLException if SQL error occurs
700 */
701 public static final Collection getCollection(
702 String resource,
703 Object target,
704 String command,
705 Object key)
706 throws SQLException {
707
708 Object[] parameters = new Object[1];
709 parameters[0] = key;
710 return getCollection(resource,target,command,parameters);
711
712 } // end getCollection
713
714
715 /**
716 * Convenience method for calling
717 * <code>getCollection(String,Object,String,Object[]);</code>
718 * with a single int parameter.
719 *
720 * @param resource The database resource key or null for default
721 * @param target The JavaBean object to return in the collection.
722 * @param command The SQL statement to prepare and execute.
723 * @param key The replaceable parameter to use with LIKE.
724 * @exception SQLException if SQL error occurs
725 */
726 public static final Collection getCollection(
727 String resource,
728 Object target,
729 String command,
730 int key)
731 throws SQLException {
732
733 Object[] parameters = new Object[1];
734 parameters[0] = new Integer(key);
735 return getCollection(resource,target,command,parameters);
736
737 } // end getCollection
738
739
740 /**
741 * Convenience method for calling
742 * <code>getCollection(String,Object,String,Object[]);</code>
743 * without a parameter list.
744 *
745 * @param resource The database resource key or null for default
746 * @param target The JavaBean object to return in the collection.
747 * @param command The SQL statement to prepare and execute.
748 * @param key The replaceable parameter to use with LIKE.
749 * @exception SQLException if SQL error occurs
750 */
751 public static final Collection getCollection(
752 String resource,
753 Object target,
754 String command)
755 throws SQLException {
756
757 return getCollection(resource,target,command,null);
758
759 } // end getCollection
760
761
762 } // end StatementUtils
763