databasemanager.java

DSPACE的源码 dspace-1.4-source

/*
 * DatabaseManager.java
 *
 * Version: $Revision: 1.40 $
 *
 * Date: $Date: 2006/07/05 16:17:16 $
 *
 * Copyright (c) 2002-2005, Hewlett-Packard Company and Massachusetts
 * Institute of Technology.  All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are
 * met:
 *
 * - Redistributions of source code must retain the above copyright
 * notice, this list of conditions and the following disclaimer.
 *
 * - Redistributions in binary form must reproduce the above copyright
 * notice, this list of conditions and the following disclaimer in the
 * documentation and/or other materials provided with the distribution.
 *
 * - Neither the name of the Hewlett-Packard Company nor the name of the
 * Massachusetts Institute of Technology nor the names of their
 * contributors may be used to endorse or promote products derived from
 * this software without specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
 * ``AS IS'' AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
 * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
 * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
 * HOLDERS OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
 * INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
 * BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS
 * OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR
 * TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE
 * USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH
 * DAMAGE.
 */
package org.dspace.storage.rdbms;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.Reader;
import java.io.StringReader;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Date;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collection;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Pattern;

import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDriver;
import org.apache.commons.pool.ObjectPool;
import org.apache.commons.pool.impl.GenericKeyedObjectPool;
import org.apache.commons.pool.impl.GenericKeyedObjectPoolFactory;
import org.apache.commons.pool.impl.GenericObjectPool;
import org.apache.log4j.Logger;
import org.apache.log4j.Priority;
import org.dspace.core.ConfigurationManager;
import org.dspace.core.Context;

/**
 * Executes SQL queries.
 * 
 * @author Peter Breton
 * @author Jim Downing
 * @version $Revision: 1.40 $
 */
public class DatabaseManager
{
    /** log4j category */
    private static Logger log = Logger.getLogger(DatabaseManager.class);

    /** True if initialization has been done */
    private static boolean initialized = false;
    
    /** 
     * This regular expression is used to perform sanity checks 
     * on database names (i.e. tables and columns). 
     * 
     * FIXME: Regular expressions can be slow to solve this in the future we should
     * probably create a system where we don't pass in column and table names to these low
     * level database methods. This approach is highly exploitable for injection 
     * type attacks because we are unable to determine where the input came from. Instead
     * we could pass in static integer constants which are then mapped to their sql name. 
     */
    private static final Pattern DB_SAFE_NAME = Pattern.compile("^[a-zA-Z_1-9]+$");

    /**
     * A map of database column information. The key is the table name, a
     * String; the value is an array of ColumnInfo objects.
     */
    private static Map info = new HashMap();

    /**
     * Protected Constructor to prevent instantiation except by derived classes.
     */
    protected DatabaseManager()
    {
    }
    
    /**
     * Return an iterator with the results of the query. The table parameter
     * indicates the type of result. If table is null, the column names are read
     * from the ResultSetMetaData.
     * 
     * @param context
     *            The context object
     * @param table
     *            The name of the table which results
     * @param query
     *            The SQL query
     * @param parameters
     * 			  A set of SQL parameters to be included in query. The order of 
     * 			  the parameters must correspond to the order of their reference  
     * 			  within the query.
     * @return A TableRowIterator with the results of the query
     * @exception SQLException
     *                If a database error occurs
     */
//   FIXME: Use the following prototype when we switch to java 1.5 and remove the 
//   other varants of the methods.
//public static TableRowIterator queryTable(Context context, String table,
//String query, Object ... parameters ) throws SQLException
	  public static TableRowIterator queryTable(Context context, String table,
	  String query, Object[] parameters ) throws SQLException
    {
        if (log.isDebugEnabled())
        {
            log.debug("Running query \"" + query + "\"");
        }
        
        PreparedStatement statement = context.getDBConnection().prepareStatement(query);
        loadParameters(statement,parameters);
        
        TableRowIterator retTRI = new TableRowIterator(statement.executeQuery(),
                canonicalize(table));

        retTRI.setStatement(statement);
        return retTRI;
    }

	// FIXME: Remove for java 1.5:
    public static TableRowIterator queryTable(Context context, String table,
    String query) throws SQLException
    {
    	return queryTable(context,table,query,new Object[0]);
    }
	  
	// FIXME: Remove for java 1.5:
    public static TableRowIterator queryTable(Context context, String table,
    String query, String string1 ) throws SQLException
    {
    	Object[] parameters = {string1};
    	return queryTable(context,table,query,parameters);
    }
	 
    // FIXME: Remove for java 1.5:
    public static TableRowIterator queryTable(Context context, String table,
    String query, String string1, String string2 ) throws SQLException
    {
    	Object[] parameters = {string1, string2};
    	return queryTable(context,table,query,parameters);
    }
    
    // FIXME: Remove for java 1.5:
    public static TableRowIterator queryTable(Context context, String table,
    String query, int int1 ) throws SQLException
    {
    	Object[] parameters = { new Integer(int1) };
    	return queryTable(context,table,query,parameters);
    }
    
    // FIXME: Remove for java 1.5:
    public static TableRowIterator queryTable(Context context, String table,
    String query, int int1, int int2 ) throws SQLException
    {
    	Object[] parameters = {new Integer(int1), new Integer(int2)};
    	return queryTable(context,table,query,parameters);
    }
    
    // FIXME: Remove for java 1.5:
    public static TableRowIterator queryTable(Context context, String table,
    String query, int int1, int int2, int int3) throws SQLException
    {
    	Object[] parameters = {new Integer(int1), new Integer(int2), new Integer(int3)};
    	return queryTable(context,table,query,parameters);
    }
    
    // FIXME: Remove for java 1.5:
    public static TableRowIterator queryTable(Context context, String table,
    String query, int int1, String string2) throws SQLException
    {
    	Object[] parameters = {new Integer(int1), string2};
    	return queryTable(context,table,query,parameters);
    }
    
    // FIXME: Remove for java 1.5:
    public static TableRowIterator queryTable(Context context, String table,
    String query, int int1, String string2, String string3) throws SQLException
    {
    	Object[] parameters = {new Integer(int1), string2, string3};
    	return queryTable(context,table,query,parameters);
    }
    
    /**
     * Return an iterator with the results of the query.
     * 
     * @param context
     *            The context object
     * @param query
     *            The SQL query
     * @param parameters
     * 			  A set of SQL parameters to be included in query. The order of 
     * 			  the parameters must correspond to the order of their reference 
     * 			  within the query.
     * @return A TableRowIterator with the results of the query
     * @exception SQLException
     *                If a database error occurs
     */
//  FIXME: Use the following prototype when we switch to java 1.5 and remove the 
//  other varants of the methods.
//public static TableRowIterator query(Context context, String query, Object ... parameters)
//throws SQLException    
    public static TableRowIterator query(Context context, String query, Object[] parameters)
            throws SQLException
    {
        if (log.isDebugEnabled())
        {
            log.debug("Running query \"" + query + "\"");
        }

        PreparedStatement statement = context.getDBConnection().prepareStatement(query);
        loadParameters(statement,parameters);
        
        TableRowIterator retTRI = new TableRowIterator(statement.executeQuery());

        
        retTRI.setStatement(statement);
        return retTRI;
    }
    
    // FIXME: remove for java 1.5.
    public static TableRowIterator query(Context context, String query)
    throws SQLException
    {
    	return query(context,query,new Object[0]);
    }
    
    // FIXME: remove for java 1.5.
    public static TableRowIterator query(Context context, String query, String string1)
    throws SQLException
    {
    	Object[] parameters = {string1};
    	return query(context,query,parameters);
    }
    
    // FIXME: remove for java 1.5.
    public static TableRowIterator query(Context context, String query, String string1, String string2)
    throws SQLException
    {
    	Object[] parameters = {string1,string2};
    	return query(context,query,parameters);
    }
    
    // FIXME: remove for java 1.5.
    public static TableRowIterator query(Context context, String query, int int1)
    throws SQLException
    {
    	Object[] parameters = {new Integer(int1)};
    	return query(context,query,parameters);
    }    

    // FIXME: remove for java 1.5.
    public static TableRowIterator query(Context context, String query, int int1, int int2)
    throws SQLException
    {
    	Object[] parameters = {new Integer(int1), new Integer(int2)};
    	return query(context,query,parameters);
    }    
    
    // FIXME: remove for java 1.5.
    public static TableRowIterator query(Context context, String query, String string1, int int2)
    throws SQLException
    {
    	Object[] parameters = {string1,new Integer(int2)};
    	return query(context,query,parameters);
    }
    
    /**
     * Return an iterator with the results of executing statement. The table
     * parameter indicates the type of result. If table is null, the column
     * names are read from the ResultSetMetaData. The context is that of the
     * connection which was used to create the statement.
     * 
     * @param statement
     *            The prepared statement to execute.
     * @param table
     *            The name of the table which results
     * @param parameters
     * 			  A set of SQL parameters to be included in query. The order of 
     * 			  the parameters must correspond to the order of their reference 
     * 			  within the query.
     * @return A TableRowIterator with the results of the query
     * @exception SQLException
     *                If a database error occurs
     */
    public static TableRowIterator queryPreparedTable(String table,
            PreparedStatement statement) throws SQLException
    {
        TableRowIterator retTRI = new TableRowIterator(statement.executeQuery(),
                canonicalize(table));

        retTRI.setStatement(statement);
        return retTRI;
    }

    /**
     * Return an iterator with the results of executing statement. The context
     * is that of the connection which was used to create the statement.
     * 
     * @param statement
     *            The prepared statement to execute.
     * @return A TableRowIterator with the results of the query
     * @exception SQLException
     *                If a database error occurs
     */
    public static TableRowIterator queryPrepared(PreparedStatement statement)
            throws SQLException
    {
        TableRowIterator retTRI = new TableRowIterator(statement.executeQuery());

        retTRI.setStatement(statement);
        return retTRI;
    }

    /**
     * Return the single row result to this query, or null if no result. If more
     * than one row results, only the first is returned.
     * 
     * @param context
     *            Current DSpace context
     * @param query
     *            The SQL query
     * @param parameters
     * 			  A set of SQL parameters to be included in query. The order of 
     * 			  the parameters must correspond to the order of their reference 
     * 			  within the query.

     * @return A TableRow object, or null if no result
     * @exception SQLException
     *                If a database error occurs
     */
//    FIXME: Use the following prototype when we switch to java 1.5 and remove the 
//    other varants of the methods.
//public static TableRow querySingle(Context context, String query, Object ... parameters)
//throws SQLException
    public static TableRow querySingle(Context context, String query, Object[] parameters)
            throws SQLException
    {
        TableRowIterator iterator = query(context, query, parameters);

        TableRow retRow = (!iterator.hasNext()) ? null : iterator.next();
        iterator.close();
        return (retRow);
    }

    // FIXME: Remove for java 1.5
    public static TableRow querySingle(Context context, String query)
    throws SQLException
	{
    	return querySingle(context,query,new Object[0]);
	}
    
    // FIXME: Remove for java 1.5
    public static TableRow querySingle(Context context, String query, String string1)
    throws SQLException
	{
    	Object[] parameters = {string1};
    	return querySingle(context,query,parameters);
	}
    
    // FIXME: Remove for java 1.5
    public static TableRow querySingle(Context context, String query, int int1)
    throws SQLException
	{
    	Object[] parameters = {new Integer(int1)};
    	return querySingle(context,query,parameters);
	}
    
    // FIXME: Remove for java 1.5
    public static TableRow querySingle(Context context, String query, int int1, int int2)
    throws SQLException
	{
    	Object[] parameters = {new Integer(int1), new Integer(int2)};
    	return querySingle(context,query,parameters);
	}
    
    /**
     * Return the single row result to this query, or null if no result. If more
     * than one row results, only the first is returned.
     * 
     * @param context
     *            Current DSpace context
     * @param table
     *            The name of the table which results
     * @param query
     *            The SQL query
     * @param parameters
     * 			  A set of SQL parameters to be included in query. The order of 
     * 			  the parameters must correspond to the order of their reference 
     * 			  within the query.
     * @return A TableRow object, or null if no result
     * @exception SQLException
     *                If a database error occurs
     */
//  FIXME: Use the following prototype when we switch to java 1.5 and remove the 
//  other varants of the methods.
//public static TableRow querySingleTable(Context context, String table,
//String query, Object ... parameters) throws SQLException
    public static TableRow querySingleTable(Context context, String table,
            String query, Object[] parameters) throws SQLException
    {
        TableRowIterator iterator = queryTable(context, canonicalize(table), query, parameters);

        TableRow retRow = (!iterator.hasNext()) ? null : iterator.next();
        iterator.close();
        return (retRow);
    }

    // FIXME: Remove for java 1.5
    public static TableRow querySingleTable(Context context, String table, String query) throws SQLException
	{
    	return querySingleTable(context,table,query,new Object[0]);
	}
    
    // FIXME: Remove for java 1.5
    public static TableRow querySingleTable(Context context, String table, String query, 
    		String string1) throws SQLException
	{
    	Object[] parameters = {string1};
    	return querySingleTable(context,table,query,parameters);
	}
    
    // FIXME: Remove for java 1.5
    public static TableRow querySingleTable(Context context, String table, String query, 
    		int int1) throws SQLException
	{
    	Object[] parameters = {new Integer(int1)};
    	return querySingleTable(context,table,query,parameters);
	}
    
    // FIXME: Remove for java 1.5