Java API By Example, From Geeks To Geeks.

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 ; 43 import java.io.IOException ; 44 import java.io.Reader ; 45 import java.io.StringReader ; 46 import java.io.UnsupportedEncodingException ; 47 import java.sql.Connection ; 48 import java.sql.DatabaseMetaData ; 49 import java.sql.Date ; 50 import java.sql.Driver ; 51 import java.sql.DriverManager ; 52 import java.sql.PreparedStatement ; 53 import java.sql.ResultSet ; 54 import java.sql.ResultSetMetaData ; 55 import java.sql.SQLException ; 56 import java.sql.SQLWarning ; 57 import java.sql.Statement ; 58 import java.sql.Time ; 59 import java.sql.Timestamp ; 60 import java.sql.Types ; 61 import java.util.ArrayList ; 62 import java.util.Arrays ; 63 import java.util.Collection ; 64 import java.util.HashMap ; 65 import java.util.HashSet ; 66 import java.util.Iterator ; 67 import java.util.List ; 68 import java.util.Map ; 69 import java.util.Set ; 70 import java.util.regex.Pattern ; 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 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 info = new HashMap (); 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 table, 149       String query, Object [] parameters ) throws SQLException 150     { 151         if (log.isDebugEnabled()) 152         { 153             log.debug("Running query \"" + query + "\""); 154         } 155          156         PreparedStatement 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 table, 168     String query) throws SQLException 169     { 170         return queryTable(context,table,query,new Object [0]); 171     } 172        173     // FIXME: Remove for java 1.5: 174 public static TableRowIterator queryTable(Context context, String table, 175     String query, String string1 ) throws SQLException 176     { 177         Object [] 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 table, 183     String query, String string1, String string2 ) throws SQLException 184     { 185         Object [] 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 table, 191     String query, int int1 ) throws SQLException 192     { 193         Object [] parameters = { new Integer (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 table, 199     String query, int int1, int int2 ) throws SQLException 200     { 201         Object [] parameters = {new Integer (int1), new Integer (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 table, 207     String query, int int1, int int2, int int3) throws SQLException 208     { 209         Object [] parameters = {new Integer (int1), new Integer (int2), new Integer (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 table, 215     String query, int int1, String string2) throws SQLException 216     { 217         Object [] parameters = {new Integer (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 table, 223     String query, int int1, String string2, String string3) throws SQLException 224     { 225         Object [] parameters = {new Integer (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 query, Object [] parameters) 249             throws SQLException 250     { 251         if (log.isDebugEnabled()) 252         { 253             log.debug("Running query \"" + query + "\""); 254         } 255 256         PreparedStatement 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 query) 268     throws SQLException 269     { 270         return query(context,query,new Object [0]); 271     } 272      273     // FIXME: remove for java 1.5. 274 public static TableRowIterator query(Context context, String query, String string1) 275     throws SQLException 276     { 277         Object [] 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 query, String string1, String string2) 283     throws SQLException 284     { 285         Object [] 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 query, int int1) 291     throws SQLException 292     { 293         Object [] parameters = {new Integer (int1)}; 294         return query(context,query,parameters); 295     } 296 297     // FIXME: remove for java 1.5. 298 public static TableRowIterator query(Context context, String query, int int1, int int2) 299     throws SQLException 300     { 301         Object [] parameters = {new Integer (int1), new Integer (int2)}; 302         return query(context,query,parameters); 303     } 304      305     // FIXME: remove for java 1.5. 306 public static TableRowIterator query(Context context, String query, String string1, int int2) 307     throws SQLException 308     { 309         Object [] parameters = {string1,new Integer (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 table, 332             PreparedStatement statement) throws SQLException 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 statement) 352             throws SQLException 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 query, Object [] parameters) 382             throws SQLException 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 query) 393     throws SQLException 394     { 395         return querySingle(context,query,new Object [0]); 396     } 397      398     // FIXME: Remove for java 1.5 399 public static TableRow querySingle(Context context, String query, String string1) 400     throws SQLException 401     { 402         Object [] 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 query, int int1) 408     throws SQLException 409     { 410         Object [] parameters = {new Integer (int1)}; 411         return querySingle(context,query,parameters); 412     } 413      414     // FIXME: Remove for java 1.5 415 public static TableRow querySingle(Context context, String query, int int1, int int2) 416     throws SQLException 417     { 418         Object [] parameters = {new Integer (int1), new Integer (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 table, 445             String query, Object [] parameters) throws SQLException 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 table, String query) throws SQLException 456     { 457         return querySingleTable(context,table,query,new Object [0]); 458     } 459      460     // FIXME: Remove for java 1.5 461 public static TableRow querySingleTable(Context context, String table, String query, 462             String string1) throws SQLException 463     { 464         Object [] 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 table, String query, 470             int int1) throws SQLException 471     { 472         Object [] parameters = {new Integer (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 table, String query, 478             int int1, int int2) throws SQLException 479     { 480         Object [] parameters = {new Integer (int1), new Integer (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 query, Object [] parameters) 505             throws SQLException 506     { 507         PreparedStatement 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 sqle) 530                 { 531                 } 532             } 533         } 534     } 535 536     // FIXME: Remove for java 1.5 537 public static int updateQuery(Context context, String query) throws SQLException 538     { 539         return updateQuery(context,query,new Object [0]); 540     } 541      542     // FIXME: Remove for java 1.5 543 public static int updateQuery(Context context, String query, 544             String string1) throws SQLException 545     { 546         Object [] 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 query, 552             int int1) throws SQLException 553     { 554         Object [] parameters = { new Integer (int1) }; 555         return updateQuery(context,query,parameters); 556     } 557      558     // FIXME: Remove for java 1.5 559 public static int updateQuery(Context context, String query, 560             int int1, int int2) throws SQLException 561     { 562         Object [] parameters = { new Integer (int1), new Integer (int2) }; 563         return updateQuery(context,query,parameters); 564     } 565      566     // FIXME: Remove for java 1.5 567 public static int updateQuery(Context context, String query, 568             int int1, int int2, int int3) throws SQLException 569     { 570         Object [] parameters = { new Integer (int1), new Integer (int2), new Integer (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 table) 584             throws SQLException 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 table, int id) 608             throws SQLException 609     { 610         String 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 table, 635             String column, String value) throws SQLException 636     { 637         String ctable = canonicalize(table); 638 639         if ( ! DB_SAFE_NAME.matcher(ctable).matches()) 640             throw new SQLException ("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 ("Unable to execute select query because column name ("+column+") contains non alphanumeric characters."); 644          645         String 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 table, int id) 665             throws SQLException 666     { 667         String 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 table, 690             String column, String value) throws SQLException 691     { 692         String ctable = canonicalize(table); 693 694         if ( ! DB_SAFE_NAME.matcher(ctable).matches()) 695             throw new SQLException ("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 ("Unable to execute delete query because column name ("+column+") contains non alphanumeric characters."); 699          700         String 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 getConnection() throws SQLException 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 c) 728     { 729         try 730         { 731             if (c != null) 732             { 733                 c.close(); 734             } 735         } 736         catch (SQLException 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 table) throws SQLException 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 769     { 770         String 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 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 statement = context.getDBConnection().createStatement(); 782         ResultSet 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 sql = new StringBuffer ().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 832     { 833         String table = canonicalize(row.getTable()); 834 835         StringBuffer sql = new StringBuffer ().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 columns = new ArrayList (); 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 868     { 869         String pk = getPrimaryKeyColumn(row); 870 871         if (row.isColumnNull(pk)) 872         { 873             throw new IllegalArgumentException ("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 table) throws SQLException 889     { 890         Map cinfo = getColumnInfoInternal(table); 891 892         if (cinfo == null) 893         { 894             return null; 895         } 896 897         Collection 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 table, String column) 914             throws SQLException 915     { 916         Map 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 table) 932             throws SQLException 933     { 934         String 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 getColumnNames(String table) throws SQLException 961     { 962         List results = new ArrayList (); 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 getColumnNames(ResultSetMetaData meta) 984             throws SQLException 985     { 986         List results = new ArrayList (); 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 canonicalize(String 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 sql) throws SQLException 1029    { 1030        try 1031        { 1032            loadSql(new StringReader (sql)); 1033        } 1034        catch (IOException 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 r) throws SQLException , IOException 1050    { 1051        BufferedReader reader = new BufferedReader (r); 1052        StringBuffer sql = new StringBuffer (); 1053        String SQL = null; 1054 1055        String line = null; 1056 1057        Connection connection = null; 1058        Statement 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 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 sqlw) 1139                { 1140                    if (log.isDebugEnabled()) 1141                    { 1142                        log.debug("Got SQL Warning: " + sqlw, sqlw); 1143                    } 1144                } 1145                catch (SQLException sqle) 1146                { 1147                    String msg = "Got SQL Exception: " + sqle; 1148                    String 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 (); 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 results, String table) 1218            throws SQLException 1219    { 1220        ResultSetMetaData meta = results.getMetaData(); 1221        int columns = meta.getColumnCount() + 1; 1222 1223        List 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 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 mystring = new String (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 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 ("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 getPrimaryKeyColumn(TableRow row) throws SQLException 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 getPrimaryKeyColumn(String table) 1328            throws SQLException 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 table) throws SQLException 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 connection, String sql, List columns, 1380            TableRow row) throws SQLException 1381    { 1382        PreparedStatement 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 iterator = columns.iterator(); iterator.hasNext();) 1396            { 1397                count++; 1398 1399                ColumnInfo info = (ColumnInfo) iterator.next(); 1400                String 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 d = new java.sql.Date (row.getDateColumn( 1435                            column).getTime()); 1436                    statement.setDate(count, d); 1437 1438                    continue; 1439                } 1440                else if (jdbctype == Types.TIME) 1441                { 1442                    Time t = new Time (row.getDateColumn(column).getTime()); 1443                    statement.setTime(count, t); 1444 1445                    continue; 1446                } 1447                else if (jdbctype == Types.TIMESTAMP) 1448                { 1449                    Timestamp t = new Timestamp (row.getDateColumn(column) 1450                            .getTime()); 1451                    statement.setTimestamp(count, t); 1452 1453                    continue; 1454                } 1455                else 1456                { 1457                    throw new IllegalArgumentException ( 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 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 getColumnInfoInternal(String table) throws SQLException 1489    { 1490        String ctable = canonicalize(table); 1491        Map results = (Map ) 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 retrieveColumnInfo(String table) throws SQLException 1516    { 1517        Connection connection = null; 1518 1519        try 1520        { 1521            connection = getConnection(); 1522 1523            DatabaseMetaData metadata = connection.getMetaData(); 1524            HashMap results = new HashMap (); 1525 1526            int max = metadata.getMaxTableNameLength(); 1527            String tname = (table.length() >= max) ? table 1528                    .substring(0, max - 1) : table; 1529 1530            ResultSet pkcolumns = metadata.getPrimaryKeys(null, null, tname); 1531            Set pks = new HashSet (); 1532 1533            while (pkcolumns.next()) 1534                pks.add(pkcolumns.getString(4)); 1535 1536            ResultSet columns = metadata.getColumns(null, null, tname, null); 1537 1538            while (columns.next()) 1539            { 1540                String 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 1568    { 1569        if (initialized) 1570        { 1571            return; 1572        } 1573 1574        try 1575        { 1576            // Register basic JDBC driver 1577 Class driverClass = Class.forName(ConfigurationManager 1578                    .getProperty("db.driver")); 1579            Driver basicDriver = (Driver ) 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 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 se) 1685        { 1686            // Simply throw up SQLExceptions 1687 throw se; 1688        } 1689        catch (Exception 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 (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 statement, Object [] parameters) 1708    throws SQLException { 1709         1710        statement.clearParameters(); 1711         1712        for(int i=0; i < parameters.length; i++) 1713        { 1714            // Select the object we are setting. 1715 Object parameter = parameters[i]; 1716            int idx = i+1; // JDBC starts counting at 1. 1717 1718            if (parameter == null) 1719            { 1720                throw new SQLException ("Attempting to insert null value into SQL query."); 1721            } 1722            if (parameter instanceof String ) 1723            { 1724                statement.setString(idx,(String ) parameters[i]); 1725            } 1726            else if (parameter instanceof Integer ) 1727            { 1728                int ii = ((Integer ) parameter).intValue(); 1729                statement.setInt(idx,ii); 1730            } 1731            else if (parameter instanceof Double ) 1732            { 1733                double d = ((Double ) parameter).doubleValue(); 1734                statement.setDouble(idx,d); 1735            } 1736            else if (parameter instanceof Float ) 1737            { 1738                float f = ((Float ) parameter).floatValue(); 1739                statement.setFloat(idx,f); 1740            } 1741            else if (parameter instanceof Short ) 1742            { 1743                short s = ((Short ) parameter).shortValue(); 1744                statement.setShort(idx,s); 1745            } 1746            else if (parameter instanceof Long ) 1747            { 1748                long l = ((Long ) parameter).longValue(); 1749                statement.setLong(idx,l); 1750            } 1751            else if (parameter instanceof Date ) 1752            { 1753                Date date = (Date ) parameter; 1754                statement.setDate(idx,date); 1755            } 1756            else if (parameter instanceof Time ) 1757            { 1758                Time time = (Time ) parameter; 1759                statement.setTime(idx,time); 1760            } 1761            else if (parameter instanceof Timestamp ) 1762            { 1763                Timestamp timestamp = (Timestamp ) parameter; 1764                statement.setTimestamp(idx,timestamp); 1765            } 1766            else 1767            { 1768                throw new SQLException ("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 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 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 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 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 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 ().append(name).append(type) 1900                .append(isPrimaryKey).toString().hashCode(); 1901    } 1902} 1903

A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame Free Books   Free Magazines   Popular Tags