KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > dspace > storage > rdbms > DatabaseManager


1 /*
2  * DatabaseManager.java
3  *
4  * Version: $Revision: 1.40 $
5  *
6  * Date: $Date: 2006/07/05 16:17:16 $
7  *
8  * Copyright (c) 2002-2005, Hewlett-Packard Company and Massachusetts
9  * Institute of Technology. All rights reserved.
10  *
11  * Redistribution and use in source and binary forms, with or without
12  * modification, are permitted provided that the following conditions are
13  * met:
14  *
15  * - Redistributions of source code must retain the above copyright
16  * notice, this list of conditions and the following disclaimer.
17  *
18  * - Redistributions in binary form must reproduce the above copyright
19  * notice, this list of conditions and the following disclaimer in the
20  * documentation and/or other materials provided with the distribution.
21  *
22  * - Neither the name of the Hewlett-Packard Company nor the name of the
23  * Massachusetts Institute of Technology nor the names of their
24  * contributors may be used to endorse or promote products derived from
25  * this software without specific prior written permission.
26  *
27  * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
28  * ``AS IS'' AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
29  * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
30  * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
31  * HOLDERS OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
32  * INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
33  * BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS
34  * OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
35  * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR
36  * TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE
37  * USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH
38  * DAMAGE.
39  */

40 package org.dspace.storage.rdbms;
41
42 import java.io.BufferedReader JavaDoc;
43 import java.io.IOException JavaDoc;
44 import java.io.Reader JavaDoc;
45 import java.io.StringReader JavaDoc;
46 import java.io.UnsupportedEncodingException JavaDoc;
47 import java.sql.Connection JavaDoc;
48 import java.sql.DatabaseMetaData JavaDoc;
49 import java.sql.Date JavaDoc;
50 import java.sql.Driver JavaDoc;
51 import java.sql.DriverManager JavaDoc;
52 import java.sql.PreparedStatement JavaDoc;
53 import java.sql.ResultSet JavaDoc;
54 import java.sql.ResultSetMetaData JavaDoc;
55 import java.sql.SQLException JavaDoc;
56 import java.sql.SQLWarning JavaDoc;
57 import java.sql.Statement JavaDoc;
58 import java.sql.Time JavaDoc;
59 import java.sql.Timestamp JavaDoc;
60 import java.sql.Types JavaDoc;
61 import java.util.ArrayList JavaDoc;
62 import java.util.Arrays JavaDoc;
63 import java.util.Collection JavaDoc;
64 import java.util.HashMap JavaDoc;
65 import java.util.HashSet JavaDoc;
66 import java.util.Iterator JavaDoc;
67 import java.util.List JavaDoc;
68 import java.util.Map JavaDoc;
69 import java.util.Set JavaDoc;
70 import java.util.regex.Pattern JavaDoc;
71
72 import org.apache.commons.dbcp.ConnectionFactory;
73 import org.apache.commons.dbcp.DriverManagerConnectionFactory;
74 import org.apache.commons.dbcp.PoolableConnectionFactory;
75 import org.apache.commons.dbcp.PoolingDriver;
76 import org.apache.commons.pool.ObjectPool;
77 import org.apache.commons.pool.impl.GenericKeyedObjectPool;
78 import org.apache.commons.pool.impl.GenericKeyedObjectPoolFactory;
79 import org.apache.commons.pool.impl.GenericObjectPool;
80 import org.apache.log4j.Logger;
81 import org.apache.log4j.Priority;
82 import org.dspace.core.ConfigurationManager;
83 import org.dspace.core.Context;
84
85 /**
86  * Executes SQL queries.
87  *
88  * @author Peter Breton
89  * @author Jim Downing
90  * @version $Revision: 1.40 $
91  */

92 public class DatabaseManager
93 {
94     /** log4j category */
95     private static Logger log = Logger.getLogger(DatabaseManager.class);
96
97     /** True if initialization has been done */
98     private static boolean initialized = false;
99     
100     /**
101      * This regular expression is used to perform sanity checks
102      * on database names (i.e. tables and columns).
103      *
104      * FIXME: Regular expressions can be slow to solve this in the future we should
105      * probably create a system where we don't pass in column and table names to these low
106      * level database methods. This approach is highly exploitable for injection
107      * type attacks because we are unable to determine where the input came from. Instead
108      * we could pass in static integer constants which are then mapped to their sql name.
109      */

110     private static final Pattern JavaDoc DB_SAFE_NAME = Pattern.compile("^[a-zA-Z_1-9]+$");
111
112     /**
113      * A map of database column information. The key is the table name, a
114      * String; the value is an array of ColumnInfo objects.
115      */

116     private static Map JavaDoc info = new HashMap JavaDoc();
117
118     /**
119      * Protected Constructor to prevent instantiation except by derived classes.
120      */

121     protected DatabaseManager()
122     {
123     }
124     
125     /**
126      * Return an iterator with the results of the query. The table parameter
127      * indicates the type of result. If table is null, the column names are read
128      * from the ResultSetMetaData.
129      *
130      * @param context
131      * The context object
132      * @param table
133      * The name of the table which results
134      * @param query
135      * The SQL query
136      * @param parameters
137      * A set of SQL parameters to be included in query. The order of
138      * the parameters must correspond to the order of their reference
139      * within the query.
140      * @return A TableRowIterator with the results of the query
141      * @exception SQLException
142      * If a database error occurs
143      */

144 // FIXME: Use the following prototype when we switch to java 1.5 and remove the
145
// other varants of the methods.
146
//public static TableRowIterator queryTable(Context context, String table,
147
//String query, Object ... parameters ) throws SQLException
148
public static TableRowIterator queryTable(Context context, String JavaDoc table,
149       String JavaDoc query, Object JavaDoc[] parameters ) throws SQLException JavaDoc
150     {
151         if (log.isDebugEnabled())
152         {
153             log.debug("Running query \"" + query + "\"");
154         }
155         
156         PreparedStatement JavaDoc statement = context.getDBConnection().prepareStatement(query);
157         loadParameters(statement,parameters);
158         
159         TableRowIterator retTRI = new TableRowIterator(statement.executeQuery(),
160                 canonicalize(table));
161
162         retTRI.setStatement(statement);
163         return retTRI;
164     }
165
166     // FIXME: Remove for java 1.5:
167
public static TableRowIterator queryTable(Context context, String JavaDoc table,
168     String JavaDoc query) throws SQLException JavaDoc
169     {
170         return queryTable(context,table,query,new Object JavaDoc[0]);
171     }
172       
173     // FIXME: Remove for java 1.5:
174
public static TableRowIterator queryTable(Context context, String JavaDoc table,
175     String JavaDoc query, String JavaDoc string1 ) throws SQLException JavaDoc
176     {
177         Object JavaDoc[] parameters = {string1};
178         return queryTable(context,table,query,parameters);
179     }
180      
181     // FIXME: Remove for java 1.5:
182
public static TableRowIterator queryTable(Context context, String JavaDoc table,
183     String JavaDoc query, String JavaDoc string1, String JavaDoc string2 ) throws SQLException JavaDoc
184     {
185         Object JavaDoc[] parameters = {string1, string2};
186         return queryTable(context,table,query,parameters);
187     }
188     
189     // FIXME: Remove for java 1.5:
190
public static TableRowIterator queryTable(Context context, String JavaDoc table,
191     String JavaDoc query, int int1 ) throws SQLException JavaDoc
192     {
193         Object JavaDoc[] parameters = { new Integer JavaDoc(int1) };
194         return queryTable(context,table,query,parameters);
195     }
196     
197     // FIXME: Remove for java 1.5:
198
public static TableRowIterator queryTable(Context context, String JavaDoc table,
199     String JavaDoc query, int int1, int int2 ) throws SQLException JavaDoc
200     {
201         Object JavaDoc[] parameters = {new Integer JavaDoc(int1), new Integer JavaDoc(int2)};
202         return queryTable(context,table,query,parameters);
203     }
204     
205     // FIXME: Remove for java 1.5:
206
public static TableRowIterator queryTable(Context context, String JavaDoc table,
207     String JavaDoc query, int int1, int int2, int int3) throws SQLException JavaDoc
208     {
209         Object JavaDoc[] parameters = {new Integer JavaDoc(int1), new Integer JavaDoc(int2), new Integer JavaDoc(int3)};
210         return queryTable(context,table,query,parameters);
211     }
212     
213     // FIXME: Remove for java 1.5:
214
public static TableRowIterator queryTable(Context context, String JavaDoc table,
215     String JavaDoc query, int int1, String JavaDoc string2) throws SQLException JavaDoc
216     {
217         Object JavaDoc[] parameters = {new Integer JavaDoc(int1), string2};
218         return queryTable(context,table,query,parameters);
219     }
220     
221     // FIXME: Remove for java 1.5:
222
public static TableRowIterator queryTable(Context context, String JavaDoc table,
223     String JavaDoc query, int int1, String JavaDoc string2, String JavaDoc string3) throws SQLException JavaDoc
224     {
225         Object JavaDoc[] parameters = {new Integer JavaDoc(int1), string2, string3};
226         return queryTable(context,table,query,parameters);
227     }
228     
229     /**
230      * Return an iterator with the results of the query.
231      *
232      * @param context
233      * The context object
234      * @param query
235      * The SQL query
236      * @param parameters
237      * A set of SQL parameters to be included in query. The order of
238      * the parameters must correspond to the order of their reference
239      * within the query.
240      * @return A TableRowIterator with the results of the query
241      * @exception SQLException
242      * If a database error occurs
243      */

244 // FIXME: Use the following prototype when we switch to java 1.5 and remove the
245
// other varants of the methods.
246
//public static TableRowIterator query(Context context, String query, Object ... parameters)
247
//throws SQLException
248
public static TableRowIterator query(Context context, String JavaDoc query, Object JavaDoc[] parameters)
249             throws SQLException JavaDoc
250     {
251         if (log.isDebugEnabled())
252         {
253             log.debug("Running query \"" + query + "\"");
254         }
255
256         PreparedStatement JavaDoc statement = context.getDBConnection().prepareStatement(query);
257         loadParameters(statement,parameters);
258         
259         TableRowIterator retTRI = new TableRowIterator(statement.executeQuery());
260
261         
262         retTRI.setStatement(statement);
263         return retTRI;
264     }
265     
266     // FIXME: remove for java 1.5.
267
public static TableRowIterator query(Context context, String JavaDoc query)
268     throws SQLException JavaDoc
269     {
270         return query(context,query,new Object JavaDoc[0]);
271     }
272     
273     // FIXME: remove for java 1.5.
274
public static TableRowIterator query(Context context, String JavaDoc query, String JavaDoc string1)
275     throws SQLException JavaDoc
276     {
277         Object JavaDoc[] parameters = {string1};
278         return query(context,query,parameters);
279     }
280     
281     // FIXME: remove for java 1.5.
282
public static TableRowIterator query(Context context, String JavaDoc query, String JavaDoc string1, String JavaDoc string2)
283     throws SQLException JavaDoc
284     {
285         Object JavaDoc[] parameters = {string1,string2};
286         return query(context,query,parameters);
287     }
288     
289     // FIXME: remove for java 1.5.
290
public static TableRowIterator query(Context context, String JavaDoc query, int int1)
291     throws SQLException JavaDoc
292     {
293         Object JavaDoc[] parameters = {new Integer JavaDoc(int1)};
294         return query(context,query,parameters);
295     }
296
297     // FIXME: remove for java 1.5.
298
public static TableRowIterator query(Context context, String JavaDoc query, int int1, int int2)
299     throws SQLException JavaDoc
300     {
301         Object JavaDoc[] parameters = {new Integer JavaDoc(int1), new Integer JavaDoc(int2)};
302         return query(context,query,parameters);
303     }
304     
305     // FIXME: remove for java 1.5.
306
public static TableRowIterator query(Context context, String JavaDoc query, String JavaDoc string1, int int2)
307     throws SQLException JavaDoc
308     {
309         Object JavaDoc[] parameters = {string1,new Integer JavaDoc(int2)};
310         return query(context,query,parameters);
311     }
312     
313     /**
314      * Return an iterator with the results of executing statement. The table
315      * parameter indicates the type of result. If table is null, the column
316      * names are read from the ResultSetMetaData. The context is that of the
317      * connection which was used to create the statement.
318      *
319      * @param statement
320      * The prepared statement to execute.
321      * @param table
322      * The name of the table which results
323      * @param parameters
324      * A set of SQL parameters to be included in query. The order of
325      * the parameters must correspond to the order of their reference
326      * within the query.
327      * @return A TableRowIterator with the results of the query
328      * @exception SQLException
329      * If a database error occurs
330      */

331     public static TableRowIterator queryPreparedTable(String JavaDoc table,
332             PreparedStatement JavaDoc statement) throws SQLException JavaDoc
333     {
334         TableRowIterator retTRI = new TableRowIterator(statement.executeQuery(),
335                 canonicalize(table));
336
337         retTRI.setStatement(statement);
338         return retTRI;
339     }
340
341     /**
342      * Return an iterator with the results of executing statement. The context
343      * is that of the connection which was used to create the statement.
344      *
345      * @param statement
346      * The prepared statement to execute.
347      * @return A TableRowIterator with the results of the query
348      * @exception SQLException
349      * If a database error occurs
350      */

351     public static TableRowIterator queryPrepared(PreparedStatement JavaDoc statement)
352             throws SQLException JavaDoc
353     {
354         TableRowIterator retTRI = new TableRowIterator(statement.executeQuery());
355
356         retTRI.setStatement(statement);
357         return retTRI;
358     }
359
360     /**
361      * Return the single row result to this query, or null if no result. If more
362      * than one row results, only the first is returned.
363      *
364      * @param context
365      * Current DSpace context
366      * @param query
367      * The SQL query
368      * @param parameters
369      * A set of SQL parameters to be included in query. The order of
370      * the parameters must correspond to the order of their reference
371      * within the query.
372
373      * @return A TableRow object, or null if no result
374      * @exception SQLException
375      * If a database error occurs
376      */

377 // FIXME: Use the following prototype when we switch to java 1.5 and remove the
378
// other varants of the methods.
379
//public static TableRow querySingle(Context context, String query, Object ... parameters)
380
//throws SQLException
381
public static TableRow querySingle(Context context, String JavaDoc query, Object JavaDoc[] parameters)
382             throws SQLException JavaDoc
383     {
384         TableRowIterator iterator = query(context, query, parameters);
385
386         TableRow retRow = (!iterator.hasNext()) ? null : iterator.next();
387         iterator.close();
388         return (retRow);
389     }
390
391     // FIXME: Remove for java 1.5
392
public static TableRow querySingle(Context context, String JavaDoc query)
393     throws SQLException JavaDoc
394     {
395         return querySingle(context,query,new Object JavaDoc[0]);
396     }
397     
398     // FIXME: Remove for java 1.5
399
public static TableRow querySingle(Context context, String JavaDoc query, String JavaDoc string1)
400     throws SQLException JavaDoc
401     {
402         Object JavaDoc[] parameters = {string1};
403         return querySingle(context,query,parameters);
404     }
405     
406     // FIXME: Remove for java 1.5
407
public static TableRow querySingle(Context context, String JavaDoc query, int int1)
408     throws SQLException JavaDoc
409     {
410         Object JavaDoc[] parameters = {new Integer JavaDoc(int1)};
411         return querySingle(context,query,parameters);
412     }
413     
414     // FIXME: Remove for java 1.5
415
public static TableRow querySingle(Context context, String JavaDoc query, int int1, int int2)
416     throws SQLException JavaDoc
417     {
418         Object JavaDoc[] parameters = {new Integer JavaDoc(int1), new Integer JavaDoc(int2)};
419         return querySingle(context,query,parameters);
420     }
421     
422     /**
423      * Return the single row result to this query, or null if no result. If more
424      * than one row results, only the first is returned.
425      *
426      * @param context
427      * Current DSpace context
428      * @param table
429      * The name of the table which results
430      * @param query
431      * The SQL query
432      * @param parameters
433      * A set of SQL parameters to be included in query. The order of
434      * the parameters must correspond to the order of their reference
435      * within the query.
436      * @return A TableRow object, or null if no result
437      * @exception SQLException
438      * If a database error occurs
439      */

440 // FIXME: Use the following prototype when we switch to java 1.5 and remove the
441
// other varants of the methods.
442
//public static TableRow querySingleTable(Context context, String table,
443
//String query, Object ... parameters) throws SQLException
444
public static TableRow querySingleTable(Context context, String JavaDoc table,
445             String JavaDoc query, Object JavaDoc[] parameters) throws SQLException JavaDoc
446     {
447         TableRowIterator iterator = queryTable(context, canonicalize(table), query, parameters);
448
449         TableRow retRow = (!iterator.hasNext()) ? null : iterator.next();
450         iterator.close();
451         return (retRow);
452     }
453
454     // FIXME: Remove for java 1.5
455
public static TableRow querySingleTable(Context context, String JavaDoc table, String JavaDoc query) throws SQLException JavaDoc
456     {
457         return querySingleTable(context,table,query,new Object JavaDoc[0]);
458     }
459     
460     // FIXME: Remove for java 1.5
461
public static TableRow querySingleTable(Context context, String JavaDoc table, String JavaDoc query,
462             String JavaDoc string1) throws SQLException JavaDoc
463     {
464         Object JavaDoc[] parameters = {string1};
465         return querySingleTable(context,table,query,parameters);
466     }
467     
468     // FIXME: Remove for java 1.5
469
public static TableRow querySingleTable(Context context, String JavaDoc table, String JavaDoc query,
470             int int1) throws SQLException JavaDoc
471     {
472         Object JavaDoc[] parameters = {new Integer JavaDoc(int1)};
473         return querySingleTable(context,table,query,parameters);
474     }
475     
476     // FIXME: Remove for java 1.5
477
public static TableRow querySingleTable(Context context, String JavaDoc table, String JavaDoc query,
478             int int1, int int2) throws SQLException JavaDoc
479     {
480         Object JavaDoc[] parameters = {new Integer JavaDoc(int1), new Integer JavaDoc(int2)};
481         return querySingleTable(context,table,query,parameters);
482     }
483     
484     /**
485      * Execute an update, insert or delete query. Returns the number of rows
486      * affected by the query.
487      *
488      * @param context
489      * Current DSpace context
490      * @param query
491      * The SQL query to execute
492      * @param parameters
493      * A set of SQL parameters to be included in query. The order of
494      * the parameters must correspond to the order of their reference
495      * within the query.
496      * @return The number of rows affected by the query.
497      * @exception SQLException
498      * If a database error occurs
499      */

500 // FIXME: Use the following prototype when we switch to java 1.5 and remove the
501
// other varants of the methods.
502
//public static int updateQuery(Context context, String query, Object ... parameters)
503
//throws SQLException
504
public static int updateQuery(Context context, String JavaDoc query, Object JavaDoc[] parameters)
505             throws SQLException JavaDoc
506     {
507         PreparedStatement JavaDoc statement = null;
508
509         if (log.isDebugEnabled())
510         {
511             log.debug("Running query \"" + query + "\"");
512         }
513
514         try
515         {
516             statement = context.getDBConnection().prepareStatement(query);
517             loadParameters(statement,parameters);
518             
519             return statement.executeUpdate();
520         }
521         finally
522         {
523             if (statement != null)
524             {
525                 try
526                 {
527                     statement.close();
528                 }
529                 catch (SQLException JavaDoc sqle)
530                 {
531                 }
532             }
533         }
534     }
535
536     // FIXME: Remove for java 1.5
537
public static int updateQuery(Context context, String JavaDoc query) throws SQLException JavaDoc
538     {
539         return updateQuery(context,query,new Object JavaDoc[0]);
540     }
541     
542     // FIXME: Remove for java 1.5
543
public static int updateQuery(Context context, String JavaDoc query,
544             String JavaDoc string1) throws SQLException JavaDoc
545     {
546         Object JavaDoc[] parameters = {string1};
547         return updateQuery(context,query,parameters);
548     }
549     
550     // FIXME: Remove for java 1.5
551
public static int updateQuery(Context context, String JavaDoc query,
552             int int1) throws SQLException JavaDoc
553     {
554         Object JavaDoc[] parameters = { new Integer JavaDoc(int1) };
555         return updateQuery(context,query,parameters);
556     }
557     
558     // FIXME: Remove for java 1.5
559
public static int updateQuery(Context context, String JavaDoc query,
560             int int1, int int2) throws SQLException JavaDoc
561     {
562         Object JavaDoc[] parameters = { new Integer JavaDoc(int1), new Integer JavaDoc(int2) };
563         return updateQuery(context,query,parameters);
564     }
565     
566     // FIXME: Remove for java 1.5
567
public static int updateQuery(Context context, String JavaDoc query,
568             int int1, int int2, int int3) throws SQLException JavaDoc
569     {
570         Object JavaDoc[] parameters = { new Integer JavaDoc(int1), new Integer JavaDoc(int2), new Integer JavaDoc(int3) };
571         return updateQuery(context,query,parameters);
572     }
573     
574     /**
575      * Create a new row in the given table, and assigns a unique id.
576      *
577      * @param context
578      * Current DSpace context
579      * @param table
580      * The RDBMS table in which to create the new row
581      * @return The newly created row
582      */

583     public static TableRow create(Context context, String JavaDoc table)
584             throws SQLException JavaDoc
585     {
586         TableRow row = new TableRow(canonicalize(table), getColumnNames(table));
587         insert(context, row);
588
589         return row;
590     }
591
592     /**
593      * Find a table row by its primary key. Returns the row, or null if no row
594      * with that primary key value exists.
595      *
596      * @param context
597      * Current DSpace context
598      * @param table
599      * The table in which to find the row
600      * @param id
601      * The primary key value
602      * @return The row resulting from the query, or null if no row with that
603      * primary key value exists.
604      * @exception SQLException
605      * If a database error occurs
606      */

607     public static TableRow find(Context context, String JavaDoc table, int id)
608             throws SQLException JavaDoc
609     {
610         String JavaDoc ctable = canonicalize(table);
611
612         return findByUnique(context, ctable, getPrimaryKeyColumn(ctable),
613                 Integer.toString(id));
614     }
615
616     /**
617      * Find a table row by a unique value. Returns the row, or null if no row
618      * with that primary key value exists. If multiple rows with the value
619      * exist, one is returned.
620      *
621      * @param context
622      * Current DSpace context
623      * @param table
624      * The table to use to find the object
625      * @param column
626      * The name of the unique column
627      * @param value
628      * The value of the unique column
629      * @return The row resulting from the query, or null if no row with that
630      * value exists.
631      * @exception SQLException
632      * If a database error occurs
633      */

634     public static TableRow findByUnique(Context context, String JavaDoc table,
635             String JavaDoc column, String JavaDoc value) throws SQLException JavaDoc
636     {
637         String JavaDoc ctable = canonicalize(table);
638
639         if ( ! DB_SAFE_NAME.matcher(ctable).matches())
640             throw new SQLException JavaDoc("Unable to execute select query because table name ("+ctable+") contains non alphanumeric characters.");
641
642         if ( ! DB_SAFE_NAME.matcher(column).matches())
643             throw new SQLException JavaDoc("Unable to execute select query because column name ("+column+") contains non alphanumeric characters.");
644         
645         String JavaDoc sql = "select * from " + ctable + " where "+ column +" = ? ";
646
647         return querySingleTable(context, ctable, sql, value);
648     }
649
650     /**
651      * Delete a table row via its primary key. Returns the number of rows
652      * deleted.
653      *
654      * @param context
655      * Current DSpace context
656      * @param table
657      * The table to delete from
658      * @param id
659      * The primary key value
660      * @return The number of rows deleted
661      * @exception SQLException
662      * If a database error occurs
663      */

664     public static int delete(Context context, String JavaDoc table, int id)
665             throws SQLException JavaDoc
666     {
667         String JavaDoc ctable = canonicalize(table);
668
669         return deleteByValue(context, ctable, getPrimaryKeyColumn(ctable),
670                 Integer.toString(id));
671     }
672
673     /**
674      * Delete all table rows with the given value. Returns the number of rows
675      * deleted.
676      *
677      * @param context
678      * Current DSpace context
679      * @param table
680      * The table to delete from
681      * @param column
682      * The name of the column
683      * @param value
684      * The value of the column
685      * @return The number of rows deleted
686      * @exception SQLException
687      * If a database error occurs
688      */

689     public static int deleteByValue(Context context, String JavaDoc table,
690             String JavaDoc column, String JavaDoc value) throws SQLException JavaDoc
691     {
692         String JavaDoc ctable = canonicalize(table);
693
694         if ( ! DB_SAFE_NAME.matcher(ctable).matches())
695             throw new SQLException JavaDoc("Unable to execute delete query because table name ("+ctable+") contains non alphanumeric characters.");
696
697         if ( ! DB_SAFE_NAME.matcher(column).matches())
698             throw new SQLException JavaDoc("Unable to execute delete query because column name ("+column+") contains non alphanumeric characters.");
699         
700         String JavaDoc sql = "delete from "+ctable+" where "+column+" = ? ";
701
702         return updateQuery(context, sql, value);
703     }
704
705     /**
706      * Obtain an RDBMS connection.
707      *
708      * @return A new database connection.
709      * @exception SQLException
710      * If a database error occurs, or a connection cannot be
711      * obtained.
712      */

713     public static Connection JavaDoc getConnection() throws SQLException JavaDoc
714     {
715         initialize();
716
717         return DriverManager
718                 .getConnection("jdbc:apache:commons:dbcp:dspacepool");
719     }
720
721     /**
722      * Release resources associated with this connection.
723      *
724      * @param c
725      * The connection to release
726      */

727     public static void freeConnection(Connection JavaDoc c)
728     {
729         try
730         {
731             if (c != null)
732             {
733                 c.close();
734             }
735         }
736         catch (SQLException JavaDoc e)
737         {
738             log.warn(e.getMessage());
739             e.printStackTrace();
740         }
741     }
742
743     /**
744      * Create a table row object that can be passed into the insert method, not
745      * commonly used unless the table has a referential integrity constraint.
746      *
747      * @param table
748      * The RDBMS table in which to create the new row
749      * @return The newly created row
750      * @throws SQLException
751      */

752     public static TableRow row(String JavaDoc table) throws SQLException JavaDoc
753     {
754         return new TableRow(canonicalize(table), getColumnNames(table));
755     }
756     
757     /**
758      * Insert a table row into the RDBMS.
759      *
760      * @param context
761      * Current DSpace context
762      * @param row
763      * The row to insert
764      * @exception SQLException
765      * If a database error occurs
766      */

767     public static void insert(Context context, TableRow row)
768             throws SQLException JavaDoc
769     {
770         String JavaDoc table = canonicalize(row.getTable());
771
772         // Get an ID (primary key) for this row by using the "getnextid"
773
// SQL function in Postgres, or directly with sequences in Oracle
774
String JavaDoc myQuery = "SELECT getnextid('" + table + "') AS result";
775
776         if ("oracle".equals(ConfigurationManager.getProperty("db.name")))
777         {
778             myQuery = "SELECT " + table + "_seq" + ".nextval FROM dual";
779         }
780
781         Statement JavaDoc statement = context.getDBConnection().createStatement();
782         ResultSet JavaDoc rs = statement.executeQuery(myQuery);
783
784         rs.next();
785
786         int newID = rs.getInt(1);
787         rs.close();
788
789         statement.close();
790
791         // Set the ID in the table row object
792
row.setColumn(getPrimaryKeyColumn(table), newID);
793
794         StringBuffer JavaDoc sql = new StringBuffer JavaDoc().append("INSERT INTO ").append(
795                 table).append(" ( ");
796
797         ColumnInfo[] info = getColumnInfo(table);
798
799         for (int i = 0; i < info.length; i++)
800         {
801             sql.append((i == 0) ? "" : ",").append(info[i].getName());
802         }
803
804         sql.append(") VALUES ( ");
805
806         // Values to insert
807
for (int i = 0; i < info.length; i++)
808         {
809             sql.append((i == 0) ? "" : ",").append("?");
810         }
811
812         // Watch the syntax
813
sql.append(")");
814
815         execute(context.getDBConnection(), sql.toString(), Arrays.asList(info),
816                 row);
817     }
818
819     /**
820      * Update changes to the RDBMS. Note that if the update fails, the values in
821      * the row will NOT be reverted.
822      *
823      * @param context
824      * Current DSpace context
825      * @param row
826      * The row to update
827      * @return The number of rows affected (1 or 0)
828      * @exception SQLException
829      * If a database error occurs
830      */

831     public static int update(Context context, TableRow row) throws SQLException JavaDoc
832     {
833         String JavaDoc table = canonicalize(row.getTable());
834
835         StringBuffer JavaDoc sql = new StringBuffer JavaDoc().append("update ").append(table)
836                 .append(" set ");
837
838         ColumnInfo pk = getPrimaryKeyColumnInfo(table);
839         ColumnInfo[] info = getNonPrimaryKeyColumns(table);
840
841         for (int i = 0; i < info.length; i++)
842         {
843             sql.append((i == 0) ? "" : ", ").append(info[i].getName()).append(
844                     " = ?");
845         }
846
847         sql.append(" where ").append(pk.getName()).append(" = ?");
848
849         List JavaDoc columns = new ArrayList JavaDoc();
850         columns.addAll(Arrays.asList(info));
851         columns.add(pk);
852
853         return execute(context.getDBConnection(), sql.toString(), columns, row);
854     }
855
856     /**
857      * Delete row from the RDBMS.
858      *
859      * @param context
860      * Current DSpace context
861      * @param row
862      * The row to delete
863      * @return The number of rows affected (1 or 0)
864      * @exception SQLException
865      * If a database error occurs
866      */

867     public static int delete(Context context, TableRow row) throws SQLException JavaDoc
868     {
869         String JavaDoc pk = getPrimaryKeyColumn(row);
870
871         if (row.isColumnNull(pk))
872         {
873             throw new IllegalArgumentException JavaDoc("Primary key value is null");
874         }
875
876         return delete(context, row.getTable(), row.getIntColumn(pk));
877     }
878
879     /**
880      * Return metadata about a table.
881      *
882      * @param table
883      * The name of the table
884      * @return An array of ColumnInfo objects
885      * @exception SQLException
886      * If a database error occurs
887      */

888     static ColumnInfo[] getColumnInfo(String JavaDoc table) throws SQLException JavaDoc
889     {
890         Map JavaDoc cinfo = getColumnInfoInternal(table);
891
892         if (cinfo == null)
893         {
894             return null;
895         }
896
897         Collection JavaDoc info = cinfo.values();
898
899         return (ColumnInfo[]) info.toArray(new ColumnInfo[info.size()]);
900     }
901
902     /**
903      * Return info about column in table.
904      *
905      * @param table
906      * The name of the table
907      * @param column
908      * The name of the column
909      * @return Information about the column
910      * @exception SQLException
911      * If a database error occurs
912      */

913     static ColumnInfo getColumnInfo(String JavaDoc table, String JavaDoc column)
914             throws SQLException JavaDoc
915     {
916         Map JavaDoc info = getColumnInfoInternal(table);
917
918         return (info == null) ? null : (ColumnInfo) info.get(column);
919     }
920
921     /**
922      * Return all the columns which are not primary keys.
923      *
924      * @param table
925      * The name of the table
926      * @return All the columns which are not primary keys, as an array of
927      * ColumnInfo objects
928      * @exception SQLException
929      * If a database error occurs
930      */

931     static ColumnInfo[] getNonPrimaryKeyColumns(String JavaDoc table)
932             throws SQLException JavaDoc
933     {
934         String JavaDoc pk = getPrimaryKeyColumn(table);
935         ColumnInfo[] info = getColumnInfo(table);
936         ColumnInfo[] results = new ColumnInfo[info.length - 1];
937         int rcount = 0;
938
939         for (int i = 0; i < info.length; i++)
940         {
941             if (!pk.equals(info[i].getName()))
942             {
943                 results[rcount++] = info[i];
944             }
945         }
946
947         return results;
948     }
949
950     /**
951      * Return the names of all the columns of the given table.
952      *
953      * @param table
954      * The name of the table
955      * @return The names of all the columns of the given table, as a List. Each
956      * element of the list is a String.
957      * @exception SQLException
958      * If a database error occurs
959      */

960     protected static List JavaDoc getColumnNames(String JavaDoc table) throws SQLException JavaDoc
961     {
962         List JavaDoc results = new ArrayList JavaDoc();
963         ColumnInfo[] info = getColumnInfo(table);
964
965         for (int i = 0; i < info.length; i++)
966         {
967             results.add(info[i].getName());
968         }
969
970         return results;
971     }
972
973     /**
974      * Return the names of all the columns of the ResultSet.
975      *
976      * @param table
977      * The ResultSetMetaData
978      * @return The names of all the columns of the given table, as a List. Each
979      * element of the list is a String.
980      * @exception SQLException
981      * If a database error occurs
982      */

983     protected static List JavaDoc getColumnNames(ResultSetMetaData JavaDoc meta)
984             throws SQLException JavaDoc
985     {
986         List JavaDoc results = new ArrayList JavaDoc();
987         int columns = meta.getColumnCount();
988
989         for (int i = 0; i < columns; i++)
990         {
991             results.add(meta.getColumnLabel(i + 1));
992         }
993
994         return results;
995     }
996
997     /**
998      * Return the canonical name for a table.
999      *
1000     * @param table
1001     * The name of the table.
1002     * @return The canonical name of the table.
1003     */

1004    static String JavaDoc canonicalize(String JavaDoc table)
1005    {
1006        // Oracle expects upper-case table names
1007
if ("oracle".equals(ConfigurationManager.getProperty("db.name")))
1008        {
1009            return (table == null) ? null : table.toUpperCase();
1010        }
1011
1012        // default database postgres wants lower-case table names
1013
return (table == null) ? null : table.toLowerCase();
1014    }
1015
1016    ////////////////////////////////////////
1017
// SQL loading methods
1018
////////////////////////////////////////
1019

1020    /**
1021     * Load SQL into the RDBMS.
1022     *
1023     * @param sql
1024     * The SQL to load.
1025     * throws SQLException
1026     * If a database error occurs
1027     */

1028    public static void loadSql(String JavaDoc sql) throws SQLException JavaDoc
1029    {
1030        try
1031        {
1032            loadSql(new StringReader JavaDoc(sql));
1033        }
1034        catch (IOException JavaDoc ioe)
1035        {
1036        }
1037    }
1038
1039    /**
1040     * Load SQL from a reader into the RDBMS.
1041     *
1042     * @param r
1043     * The Reader from which to read the SQL.
1044     * @throws SQLException
1045     * If a database error occurs
1046     * @throws IOException
1047     * If an error occurs obtaining data from the reader
1048     */

1049    public static void loadSql(Reader JavaDoc r) throws SQLException JavaDoc, IOException JavaDoc
1050    {
1051        BufferedReader JavaDoc reader = new BufferedReader JavaDoc(r);
1052        StringBuffer JavaDoc sql = new StringBuffer JavaDoc();
1053        String JavaDoc SQL = null;
1054
1055        String JavaDoc line = null;
1056
1057        Connection JavaDoc connection = null;
1058        Statement JavaDoc statement = null;
1059
1060        try
1061        {
1062            connection = getConnection();
1063            connection.setAutoCommit(true);
1064            statement = connection.createStatement();
1065
1066            boolean inquote = false;
1067
1068            while ((line = reader.readLine()) != null)
1069            {
1070                // Look for comments
1071
int commentStart = line.indexOf("--");
1072
1073                String JavaDoc input = (commentStart != -1) ? line.substring(0,
1074                        commentStart) : line;
1075
1076                // Empty line, skip
1077
if (input.trim().equals(""))
1078                {
1079                    continue;
1080                }
1081
1082                // Put it on the SQL buffer
1083
sql.append(input.replace(';', ' ')); // remove all semicolons
1084
// from sql file!
1085

1086                // Add a space
1087
sql.append(" ");
1088
1089                // More to come?
1090
// Look for quotes
1091
int index = 0;
1092                int count = 0;
1093                int inputlen = input.length();
1094
1095                while ((index = input.indexOf("'", count)) != -1)
1096                {
1097                    // Flip the value of inquote
1098
inquote = !inquote;
1099
1100                    // Move the index
1101
count = index + 1;
1102
1103                    // Make sure we do not exceed the string length
1104
if (count >= inputlen)
1105                    {
1106                        break;
1107                    }
1108                }
1109
1110                // If we are in a quote, keep going
1111
// Note that this is STILL a simple heuristic that is not
1112
// guaranteed to be correct
1113
if (inquote)
1114                {
1115                    continue;
1116                }
1117
1118                int endMarker = input.indexOf(";", index);
1119
1120                if (endMarker == -1)
1121                {
1122                    continue;
1123                }
1124
1125                if (log.isDebugEnabled())
1126                {
1127                    log.debug("Running database query \"" + sql + "\"");
1128                }
1129
1130                SQL = sql.toString();
1131
1132                try
1133                {
1134                    // Use execute, not executeQuery (which expects results) or
1135
// executeUpdate
1136
boolean succeeded = statement.execute(SQL);
1137                }
1138                catch (SQLWarning JavaDoc sqlw)
1139                {
1140                    if (log.isDebugEnabled())
1141                    {
1142                        log.debug("Got SQL Warning: " + sqlw, sqlw);
1143                    }
1144                }
1145                catch (SQLException JavaDoc sqle)
1146                {
1147                    String JavaDoc msg = "Got SQL Exception: " + sqle;
1148                    String JavaDoc sqlmessage = sqle.getMessage();
1149
1150                    // These are Postgres-isms:
1151
// There's no easy way to check if a table exists before
1152
// creating it, so we always drop tables, then create them
1153
boolean isDrop = ((SQL != null) && (sqlmessage != null)
1154                            && (SQL.toUpperCase().startsWith("DROP")) && (sqlmessage
1155                            .indexOf("does not exist") != -1));
1156
1157                    // Creating a view causes a bogus warning
1158
boolean isNoResults = ((SQL != null)
1159                            && (sqlmessage != null)
1160                            && ((SQL.toUpperCase().startsWith("CREATE VIEW")) || (SQL
1161                                    .toUpperCase()
1162                                    .startsWith("CREATE FUNCTION"))) && (sqlmessage
1163                            .indexOf("No results were returned") != -1));
1164
1165                    // If the messages are bogus, give them a low priority
1166
if (isDrop || isNoResults)
1167                    {
1168                        if (log.isDebugEnabled())
1169                        {
1170                            log.debug(msg, sqle);
1171                        }
1172                    }
1173                    // Otherwise, we need to know!
1174
else
1175                    {
1176                        if (log.isEnabledFor(Priority.WARN))
1177                        {
1178                            log.warn(msg, sqle);
1179                        }
1180                    }
1181                }
1182
1183                // Reset SQL buffer
1184
sql = new StringBuffer JavaDoc();
1185                SQL = null;
1186            }
1187        }
1188        finally
1189        {
1190            if (connection != null)
1191            {
1192                connection.close();
1193            }
1194
1195            if (statement != null)
1196            {
1197                statement.close();
1198            }
1199        }
1200    }
1201
1202    ////////////////////////////////////////
1203
// Helper methods
1204
////////////////////////////////////////
1205

1206    /**
1207     * Convert the current row in a ResultSet into a TableRow object.
1208     *
1209     * @param results
1210     * A ResultSet to process
1211     * @param table
1212     * The name of the table
1213     * @return A TableRow object with the data from the ResultSet
1214     * @exception SQLException
1215     * If a database error occurs
1216     */

1217    static TableRow process(ResultSet JavaDoc results, String JavaDoc table)
1218            throws SQLException JavaDoc
1219    {
1220        ResultSetMetaData JavaDoc meta = results.getMetaData();
1221        int columns = meta.getColumnCount() + 1;
1222
1223        List JavaDoc columnNames = (table == null) ? getColumnNames(meta)
1224                : getColumnNames(table);
1225
1226        TableRow row = new TableRow(canonicalize(table), columnNames);
1227
1228        // Process the columns in order
1229
// (This ensures maximum backwards compatibility with
1230
// old JDBC drivers)
1231
for (int i = 1; i < columns; i++)
1232        {
1233            String JavaDoc name = meta.getColumnName(i);
1234            int jdbctype = meta.getColumnType(i);
1235
1236            if (jdbctype == Types.BIT)
1237            {
1238                row.setColumn(name, results.getBoolean(i));
1239            }
1240            else if ((jdbctype == Types.INTEGER) || (jdbctype == Types.NUMERIC)
1241                    || (jdbctype == Types.DECIMAL))
1242            {
1243                row.setColumn(name, results.getInt(i));
1244            }
1245            else if (jdbctype == Types.BIGINT)
1246            {
1247                row.setColumn(name, results.getLong(i));
1248            }
1249            else if (jdbctype == Types.VARCHAR)
1250            {
1251                try
1252                {
1253                    byte[] bytes = results.getBytes(i);
1254
1255                    if (bytes != null)
1256                    {
1257                        String JavaDoc mystring = new String JavaDoc(results.getBytes(i),
1258                                "UTF-8");
1259                        row.setColumn(name, mystring);
1260                    }
1261                    else
1262                    {
1263                        row.setColumn(name, results.getString(i));
1264                    }
1265                }
1266                catch (UnsupportedEncodingException JavaDoc e)
1267                {
1268                    // do nothing, UTF-8 is built in!
1269
}
1270            }
1271            else if (jdbctype == Types.DATE)
1272            {
1273                row.setColumn(name, results.getDate(i));
1274            }
1275            else if (jdbctype == Types.TIME)
1276            {
1277                row.setColumn(name, results.getTime(i));
1278            }
1279            else if (jdbctype == Types.TIMESTAMP)
1280            {
1281                row.setColumn(name, results.getTimestamp(i));
1282            }
1283            else
1284            {
1285                throw new IllegalArgumentException JavaDoc("Unsupported JDBC type: "
1286                        + jdbctype);
1287            }
1288
1289            if (results.wasNull())
1290            {
1291                row.setColumnNull(name);
1292            }
1293        }
1294
1295        return row;
1296    }
1297
1298    /**
1299     * Return the name of the primary key column. We assume there's only one
1300     * primary key per table; if there are more, only the first one will be
1301     * returned.
1302     *
1303     * @param row
1304     * The TableRow to return the primary key for.
1305     * @return The name of the primary key column, or null if the row has no
1306     * primary key.
1307     * @exception SQLException
1308     * If a database error occurs
1309     */

1310    public static String JavaDoc getPrimaryKeyColumn(TableRow row) throws SQLException JavaDoc
1311    {
1312        return getPrimaryKeyColumn(row.getTable());
1313    }
1314
1315    /**
1316     * Return the name of the primary key column in the given table. We assume
1317     * there's only one primary key per table; if there are more, only the first
1318     * one will be returned.
1319     *
1320     * @param table
1321     * The name of the RDBMS table
1322     * @return The name of the primary key column, or null if the table has no
1323     * primary key.
1324     * @exception SQLException
1325     * If a database error occurs
1326     */

1327    protected static String JavaDoc getPrimaryKeyColumn(String JavaDoc table)
1328            throws SQLException JavaDoc
1329    {
1330        ColumnInfo info = getPrimaryKeyColumnInfo(table);
1331
1332        return (info == null) ? null : info.getName();
1333    }
1334
1335    /**
1336     * Return column information for the primary key column, or null if the
1337     * table has no primary key. We assume there's only one primary key per
1338     * table; if there are more, only the first one will be returned.
1339     *
1340     * @param table
1341     * The name of the RDBMS table
1342     * @return A ColumnInfo object, or null if the table has no primary key.
1343     * @exception SQLException
1344     * If a database error occurs
1345     */

1346    static ColumnInfo getPrimaryKeyColumnInfo(String JavaDoc table) throws SQLException JavaDoc
1347    {
1348        ColumnInfo[] cinfo = getColumnInfo(canonicalize(table));
1349
1350        for (int i = 0; i < cinfo.length; i++)
1351        {
1352            ColumnInfo info = cinfo[i];
1353
1354            if (info.isPrimaryKey())
1355            {
1356                return info;
1357            }
1358        }
1359
1360        return null;
1361    }
1362
1363    /**
1364     * Execute SQL as a PreparedStatement on Connection. Bind parameters in
1365     * columns to the values in the table row before executing.
1366     *
1367     * @param connection
1368     * The SQL connection
1369     * @param sql
1370     * The query to execute
1371     * @param columns
1372     * The columns to bind
1373     * @param row
1374     * The row
1375     * @return The number of rows affected by the query.
1376     * @exception SQLException
1377     * If a database error occurs
1378     */

1379    private static int execute(Connection JavaDoc connection, String JavaDoc sql, List JavaDoc columns,
1380            TableRow row) throws SQLException JavaDoc
1381    {
1382        PreparedStatement JavaDoc statement = null;
1383
1384        if (log.isDebugEnabled())
1385        {
1386            log.debug("Running query \"" + sql + "\"");
1387        }
1388
1389        try
1390        {
1391            statement = connection.prepareStatement(sql);
1392            
1393            int count = 0;
1394
1395            for (Iterator JavaDoc iterator = columns.iterator(); iterator.hasNext();)
1396            {
1397                count++;
1398
1399                ColumnInfo info = (ColumnInfo) iterator.next();
1400                String JavaDoc column = info.getName();
1401                int jdbctype = info.getType();
1402
1403                if (row.isColumnNull(column))
1404                {
1405                    statement.setNull(count, jdbctype);
1406
1407                    continue;
1408                }
1409                else if (jdbctype == Types.BIT)
1410                {
1411                    statement.setBoolean(count, row.getBooleanColumn(column));
1412
1413                    continue;
1414                }
1415                else if ((jdbctype == Types.INTEGER)
1416                        || (jdbctype == Types.DECIMAL))
1417                {
1418                    statement.setInt(count, row.getIntColumn(column));
1419
1420                    continue;
1421                }
1422                else if (jdbctype == Types.BIGINT)
1423                {
1424                    statement.setLong(count, row.getLongColumn(column));
1425                }
1426                else if (jdbctype == Types.VARCHAR)
1427                {
1428                    statement.setString(count, row.getStringColumn(column));
1429
1430                    continue;
1431                }
1432                else if (jdbctype == Types.DATE)
1433                {
1434                    java.sql.Date JavaDoc d = new java.sql.Date JavaDoc(row.getDateColumn(
1435                            column).getTime());
1436                    statement.setDate(count, d);
1437
1438                    continue;
1439                }
1440                else if (jdbctype == Types.TIME)
1441                {
1442                    Time JavaDoc t = new Time JavaDoc(row.getDateColumn(column).getTime());
1443                    statement.setTime(count, t);
1444
1445                    continue;
1446                }
1447                else if (jdbctype == Types.TIMESTAMP)
1448                {
1449                    Timestamp JavaDoc t = new Timestamp JavaDoc(row.getDateColumn(column)
1450                            .getTime());
1451                    statement.setTimestamp(count, t);
1452
1453                    continue;
1454                }
1455                else
1456                {
1457                    throw new IllegalArgumentException JavaDoc(
1458                            "Unsupported JDBC type: " + jdbctype);
1459                }
1460            }
1461
1462            return statement.executeUpdate();
1463        }
1464        finally
1465        {
1466            if (statement != null)
1467            {
1468                try
1469                {
1470                    statement.close();
1471                }
1472                catch (SQLException JavaDoc sqle)
1473                {
1474                }
1475            }
1476        }
1477    }
1478
1479    /**
1480     * Return metadata about a table.
1481     *
1482     * @param table
1483     * The name of the table
1484     * @return An map of info.
1485     * @exception SQLException
1486     * If a database error occurs
1487     */

1488    private static Map JavaDoc getColumnInfoInternal(String JavaDoc table) throws SQLException JavaDoc
1489    {
1490        String JavaDoc ctable = canonicalize(table);
1491        Map JavaDoc results = (Map JavaDoc) info.get(ctable);
1492
1493        if (results != null)
1494        {
1495            return results;
1496        }
1497
1498        results = retrieveColumnInfo(ctable);
1499        info.put(ctable, results);
1500
1501        return results;
1502    }
1503
1504    /**
1505     * Read metadata about a table from the database.
1506     *
1507     * @param table
1508     * The RDBMS table.
1509     * @return A map of information about the columns. The key is the name of
1510     * the column, a String; the value is a ColumnInfo object.
1511     * @exception SQLException
1512     * If there is a problem retrieving information from the
1513     * RDBMS.
1514     */

1515    private static Map JavaDoc retrieveColumnInfo(String JavaDoc table) throws SQLException JavaDoc
1516    {
1517        Connection JavaDoc connection = null;
1518
1519        try
1520        {
1521            connection = getConnection();
1522
1523            DatabaseMetaData JavaDoc metadata = connection.getMetaData();
1524            HashMap JavaDoc results = new HashMap JavaDoc();
1525
1526            int max = metadata.getMaxTableNameLength();
1527            String JavaDoc tname = (table.length() >= max) ? table
1528                    .substring(0, max - 1) : table;
1529
1530            ResultSet JavaDoc pkcolumns = metadata.getPrimaryKeys(null, null, tname);
1531            Set JavaDoc pks = new HashSet JavaDoc();
1532
1533            while (pkcolumns.next())
1534                pks.add(pkcolumns.getString(4));
1535
1536            ResultSet JavaDoc columns = metadata.getColumns(null, null, tname, null);
1537
1538            while (columns.next())
1539            {
1540                String JavaDoc column = columns.getString(4);
1541                ColumnInfo cinfo = new ColumnInfo();
1542                cinfo.setName(column);
1543                cinfo.setType((int) columns.getShort(5));
1544
1545                if (pks.contains(column))
1546                {
1547                    cinfo.setIsPrimaryKey(true);
1548                }
1549
1550                results.put(column, cinfo);
1551            }
1552
1553            return results;
1554        }
1555        finally
1556        {
1557            if (connection != null)
1558            {
1559                connection.close();
1560            }
1561        }
1562    }
1563
1564    /**
1565     * Initialize the DatabaseManager.
1566     */

1567    private static void initialize() throws SQLException JavaDoc
1568    {
1569        if (initialized)
1570        {
1571            return;
1572        }
1573
1574        try
1575        {
1576            // Register basic JDBC driver
1577
Class JavaDoc driverClass = Class.forName(ConfigurationManager
1578                    .getProperty("db.driver"));
1579            Driver JavaDoc basicDriver = (Driver JavaDoc) driverClass.newInstance();
1580            DriverManager.registerDriver(basicDriver);
1581
1582            // Read pool configuration parameter or use defaults
1583
// Note we check to see if property is null; getIntProperty returns
1584
// '0' if the property is not set OR if it is actually set to zero.
1585
// But 0 is a valid option...
1586
int maxConnections = ConfigurationManager
1587                    .getIntProperty("db.maxconnections");
1588
1589            if (ConfigurationManager.getProperty("db.maxconnections") == null)
1590            {
1591                maxConnections = 30;
1592            }
1593
1594            int maxWait = ConfigurationManager.getIntProperty("db.maxwait");
1595
1596            if (ConfigurationManager.getProperty("db.maxwait") == null)
1597            {
1598                maxWait = 5000;
1599            }
1600
1601            int maxIdle = ConfigurationManager.getIntProperty("db.maxidle");
1602
1603            if (ConfigurationManager.getProperty("db.maxidle") == null)
1604            {
1605                maxIdle = -1;
1606            }
1607            
1608            boolean useStatementPool = ConfigurationManager.getBooleanProperty("db.statementpool",true);
1609
1610            // Create object pool
1611
ObjectPool connectionPool = new GenericObjectPool(null, // PoolableObjectFactory
1612
// - set below
1613
maxConnections, // max connections
1614
GenericObjectPool.WHEN_EXHAUSTED_BLOCK, maxWait, // don't
1615
// block
1616
// more than 5
1617
// seconds
1618
maxIdle, // max idle connections (unlimited)
1619
true, // validate when we borrow connections from pool
1620
false // don't bother validation returned connections
1621
);
1622
1623            // ConnectionFactory the pool will use to create connections.
1624
ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(
1625                    ConfigurationManager.getProperty("db.url"),
1626                    ConfigurationManager.getProperty("db.username"),
1627                    ConfigurationManager.getProperty("db.password"));
1628
1629            //
1630
// Now we'll create the PoolableConnectionFactory, which wraps
1631
// the "real" Connections created by the ConnectionFactory with
1632
// the classes that implement the pooling functionality.
1633
//
1634
String JavaDoc validationQuery = "SELECT 1";
1635
1636            // Oracle has a slightly different validation query
1637
if ("oracle".equals(ConfigurationManager.getProperty("db.name")))
1638            {
1639                validationQuery = "SELECT 1 FROM DUAL";
1640            }
1641
1642            GenericKeyedObjectPoolFactory statementFactory = null;
1643            if (useStatementPool)
1644            {
1645                // The statement Pool is used to pool prepared statements.
1646
GenericKeyedObjectPool.Config statementFactoryConfig = new GenericKeyedObjectPool.Config();
1647                // Just grow the pool size when needed.
1648
//
1649
// This means we will never block when attempting to
1650
// create a query. The problem is unclosed statements,
1651
// they can never be reused. So if we place a maximum
1652
// cap on them, then we might reach a condition where
1653
// a page can only be viewed X number of times. The
1654
// downside of GROW_WHEN_EXHAUSTED is that this may
1655
// allow a memory leak to exist. Both options are bad,
1656
// but I'd prefer a memory leak over a failure.
1657
//
1658
// FIXME: Perhaps this decision should be derived from config parameters?
1659
statementFactoryConfig.whenExhaustedAction = GenericObjectPool.WHEN_EXHAUSTED_GROW;
1660    
1661                statementFactory = new GenericKeyedObjectPoolFactory(null,statementFactoryConfig);
1662            }
1663            
1664            PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(
1665                    connectionFactory, connectionPool, statementFactory,
1666                    validationQuery, // validation query
1667
false, // read only is not default for now
1668
false); // Autocommit defaults to none
1669

1670            //
1671
// Finally, we create the PoolingDriver itself...
1672
//
1673
PoolingDriver driver = new PoolingDriver();
1674
1675            //
1676
// ...and register our pool with it.
1677
//
1678
driver.registerPool("dspacepool", connectionPool);
1679
1680            // Old SimplePool init
1681
//DriverManager.registerDriver(new SimplePool());
1682
initialized = true;
1683        }
1684        catch (SQLException JavaDoc se)
1685        {
1686            // Simply throw up SQLExceptions
1687
throw se;
1688        }
1689        catch (Exception JavaDoc e)
1690        {
1691            // Need to be able to catch other exceptions. Pretend they are
1692
// SQLExceptions, but do log
1693
log.warn("Exception initializing DB pool", e);
1694            throw new SQLException JavaDoc(e.toString());
1695        }
1696    }
1697
1698    /**
1699     * Iterate over the given parameters and add them to the given prepared statement.
1700     * Only a select number of datatypes are supported by the JDBC driver.
1701     *
1702     * @param statement
1703     * The unparameterized statement.
1704     * @param parameters
1705     * The parameters to be set on the statement.
1706     */

1707    protected static void loadParameters(PreparedStatement JavaDoc statement, Object JavaDoc[] parameters)
1708    throws SQLException JavaDoc{
1709        
1710        statement.clearParameters();
1711        
1712        for(int i=0; i < parameters.length; i++)
1713        {
1714            // Select the object we are setting.
1715
Object JavaDoc parameter = parameters[i];
1716            int idx = i+1; // JDBC starts counting at 1.
1717

1718            if (parameter == null)
1719            {
1720                throw new SQLException JavaDoc("Attempting to insert null value into SQL query.");
1721            }
1722            if (parameter instanceof String JavaDoc)
1723            {
1724                statement.setString(idx,(String JavaDoc) parameters[i]);
1725            }
1726            else if (parameter instanceof Integer JavaDoc)
1727            {
1728                int ii = ((Integer JavaDoc) parameter).intValue();
1729                statement.setInt(idx,ii);
1730            }
1731            else if (parameter instanceof Double JavaDoc)
1732            {
1733                double d = ((Double JavaDoc) parameter).doubleValue();
1734                statement.setDouble(idx,d);
1735            }
1736            else if (parameter instanceof Float JavaDoc)
1737            {
1738                float f = ((Float JavaDoc) parameter).floatValue();
1739                statement.setFloat(idx,f);
1740            }
1741            else if (parameter instanceof Short JavaDoc)
1742            {
1743                short s = ((Short JavaDoc) parameter).shortValue();
1744                statement.setShort(idx,s);
1745            }
1746            else if (parameter instanceof Long JavaDoc)
1747            {
1748                long l = ((Long JavaDoc) parameter).longValue();
1749                statement.setLong(idx,l);
1750            }
1751            else if (parameter instanceof Date JavaDoc)
1752            {
1753                Date JavaDoc date = (Date JavaDoc) parameter;
1754                statement.setDate(idx,date);
1755            }
1756            else if (parameter instanceof Time JavaDoc)
1757            {
1758                Time JavaDoc time = (Time JavaDoc) parameter;
1759                statement.setTime(idx,time);
1760            }
1761            else if (parameter instanceof Timestamp JavaDoc)
1762            {
1763                Timestamp JavaDoc timestamp = (Timestamp JavaDoc) parameter;
1764                statement.setTimestamp(idx,timestamp);
1765            }
1766            else
1767            {
1768                throw new SQLException JavaDoc("Attempting to insert unknown datatype ("+parameter.getClass().getName()+") into SQL statement.");
1769            }
1770        }
1771    }
1772
1773}
1774
1775/**
1776 * Represents a column in an RDBMS table.
1777 */

1778
1779class ColumnInfo
1780{
1781    /** The name of the column */
1782    private String JavaDoc name;
1783
1784    /** The JDBC type of the column */
1785    private int type;
1786
1787    /** True if this column is a primary key */
1788    private boolean isPrimaryKey;
1789
1790    /**
1791     * Constructor
1792     */

1793    ColumnInfo()
1794    {
1795    }
1796
1797    /**
1798     * Constructor
1799     */

1800    ColumnInfo(String JavaDoc name, int type)
1801    {
1802        this.name = name;
1803        this.type = type;
1804    }
1805
1806    /**
1807     * Return the column name.
1808     *
1809     * @return - The column name
1810     */

1811    public String JavaDoc getName()
1812    {
1813        return name;
1814    }
1815
1816    /**
1817     * Set the column name
1818     *
1819     * @param v -
1820     * The column name
1821     */

1822    void setName(String JavaDoc v)
1823    {
1824        name = v;
1825    }
1826
1827    /**
1828     * Return the JDBC type. This is one of the constants from java.sql.Types.
1829     *
1830     * @return - The JDBC type
1831     * @see java.sql.Types
1832     */

1833    public int getType()
1834    {
1835        return type;
1836    }
1837
1838    /**
1839     * Set the JDBC type. This should be one of the constants from
1840     * java.sql.Types.
1841     *
1842     * @param v -
1843     * The JDBC type
1844     * @see java.sql.Types
1845     */

1846    void setType(int v)
1847    {
1848        type = v;
1849    }
1850
1851    /**
1852     * Return true if this column is a primary key.
1853     *
1854     * @return True if this column is a primary key, false otherwise.
1855     */

1856    public boolean isPrimaryKey()
1857    {
1858        return isPrimaryKey;
1859    }
1860
1861    /**
1862     * Set whether this column is a primary key.
1863     *
1864     * @param v
1865     * True if this column is a primary key.
1866     */

1867    void setIsPrimaryKey(boolean v)
1868    {
1869        this.isPrimaryKey = v;
1870    }
1871
1872    /*
1873     * Return true if this object is equal to other, false otherwise.
1874     *
1875     * @return True if this object is equal to other, false otherwise.
1876     */

1877    public boolean equals(Object JavaDoc other)
1878    {
1879        if (!(other instanceof ColumnInfo))
1880        {
1881            return false;
1882        }
1883
1884        ColumnInfo theOther = (ColumnInfo) other;
1885
1886        return ((name != null) ? name.equals(theOther.name)
1887                : (theOther.name == null))
1888                && (type == theOther.type)
1889                && (isPrimaryKey == theOther.isPrimaryKey);
1890    }
1891
1892    /*
1893     * Return a hashCode for this object.
1894     *
1895     * @return A hashcode for this object.
1896     */

1897    public int hashCode()
1898    {
1899        return new StringBuffer JavaDoc().append(name).append(type)
1900                .append(isPrimaryKey).toString().hashCode();
1901    }
1902}
1903
Popular Tags