databasemanager.java

DSPACE的源码 dspace-1.4-source

    public static TableRow querySingleTable(Context context, String table, String query, 
    		int int1, int int2) throws SQLException
	{
    	Object[] parameters = {new Integer(int1), new Integer(int2)};
    	return querySingleTable(context,table,query,parameters);
	}    
    
    /**
     * Execute an update, insert or delete query. Returns the number of rows
     * affected by the query.
     * 
     * @param context
     *            Current DSpace context
     * @param query
     *            The SQL query to execute
     * @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 The number of rows affected by 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 int updateQuery(Context context, String query, Object ... parameters)
//throws SQLException
    public static int updateQuery(Context context, String query, Object[] parameters)
            throws SQLException
    {
        PreparedStatement statement = null;

        if (log.isDebugEnabled())
        {
            log.debug("Running query \"" + query + "\"");
        }

        try
        {        	
        	statement = context.getDBConnection().prepareStatement(query);
        	loadParameters(statement,parameters);
        	
        	return statement.executeUpdate();
        }
        finally
        {
            if (statement != null)
            {
                try
                {
                    statement.close();
                }
                catch (SQLException sqle)
                {
                }
            }
        }
    }

    // FIXME: Remove for java 1.5
    public static int updateQuery(Context context, String query) throws SQLException
	{
    	return updateQuery(context,query,new Object[0]);
	}
    
    // FIXME: Remove for java 1.5
    public static int updateQuery(Context context, String query, 
    		String string1) throws SQLException
	{
    	Object[] parameters = {string1};
    	return updateQuery(context,query,parameters);
	}
    
    // FIXME: Remove for java 1.5
    public static int updateQuery(Context context, String query, 
    		int int1) throws SQLException
	{
    	Object[] parameters = { new Integer(int1) };
    	return updateQuery(context,query,parameters);
	}
    
    // FIXME: Remove for java 1.5
    public static int updateQuery(Context context, String query, 
    		int int1, int int2) throws SQLException
	{
    	Object[] parameters = { new Integer(int1), new Integer(int2) };
    	return updateQuery(context,query,parameters);
	}
    
    // FIXME: Remove for java 1.5
    public static int updateQuery(Context context, String query, 
    		int int1, int int2, int int3) throws SQLException
	{
    	Object[] parameters = { new Integer(int1), new Integer(int2), new Integer(int3) };
    	return updateQuery(context,query,parameters);
	}
    
    /**
     * Create a new row in the given table, and assigns a unique id.
     * 
     * @param context
     *            Current DSpace context
     * @param table
     *            The RDBMS table in which to create the new row
     * @return The newly created row
     */
    public static TableRow create(Context context, String table)
            throws SQLException
    {
        TableRow row = new TableRow(canonicalize(table), getColumnNames(table));
        insert(context, row);

        return row;
    }

    /**
     * Find a table row by its primary key. Returns the row, or null if no row
     * with that primary key value exists.
     * 
     * @param context
     *            Current DSpace context
     * @param table
     *            The table in which to find the row
     * @param id
     *            The primary key value
     * @return The row resulting from the query, or null if no row with that
     *         primary key value exists.
     * @exception SQLException
     *                If a database error occurs
     */
    public static TableRow find(Context context, String table, int id)
            throws SQLException
    {
        String ctable = canonicalize(table);

        return findByUnique(context, ctable, getPrimaryKeyColumn(ctable),
                Integer.toString(id));
    }

    /**
     * Find a table row by a unique value. Returns the row, or null if no row
     * with that primary key value exists. If multiple rows with the value
     * exist, one is returned.
     * 
     * @param context
     *            Current DSpace context
     * @param table
     *            The table to use to find the object
     * @param column
     *            The name of the unique column
     * @param value
     *            The value of the unique column
     * @return The row resulting from the query, or null if no row with that
     *         value exists.
     * @exception SQLException
     *                If a database error occurs
     */
    public static TableRow findByUnique(Context context, String table,
            String column, String value) throws SQLException
    {
        String ctable = canonicalize(table);

        if ( ! DB_SAFE_NAME.matcher(ctable).matches())
        	throw new SQLException("Unable to execute select query because table name ("+ctable+") contains non alphanumeric characters.");

        if ( ! DB_SAFE_NAME.matcher(column).matches())
        	throw new SQLException("Unable to execute select query because column name ("+column+") contains non alphanumeric characters.");
        
        String sql = "select * from " + ctable + " where "+ column +" = ? ";

        return querySingleTable(context, ctable, sql, value);
    }

    /**
     * Delete a table row via its primary key. Returns the number of rows
     * deleted.
     * 
     * @param context
     *            Current DSpace context
     * @param table
     *            The table to delete from
     * @param id
     *            The primary key value
     * @return The number of rows deleted
     * @exception SQLException
     *                If a database error occurs
     */
    public static int delete(Context context, String table, int id)
            throws SQLException
    {
        String ctable = canonicalize(table);

        return deleteByValue(context, ctable, getPrimaryKeyColumn(ctable),
                Integer.toString(id));
    }

    /**
     * Delete all table rows with the given value. Returns the number of rows
     * deleted.
     * 
     * @param context
     *            Current DSpace context
     * @param table
     *            The table to delete from
     * @param column
     *            The name of the column
     * @param value
     *            The value of the column
     * @return The number of rows deleted
     * @exception SQLException
     *                If a database error occurs
     */
    public static int deleteByValue(Context context, String table,
            String column, String value) throws SQLException
    {
        String ctable = canonicalize(table);

        if ( ! DB_SAFE_NAME.matcher(ctable).matches())
        	throw new SQLException("Unable to execute delete query because table name ("+ctable+") contains non alphanumeric characters.");

        if ( ! DB_SAFE_NAME.matcher(column).matches())
        	throw new SQLException("Unable to execute delete query because column name ("+column+") contains non alphanumeric characters.");
        
        String sql = "delete from "+ctable+" where "+column+" = ? ";

        return updateQuery(context, sql, value);
    }

    /**
     * Obtain an RDBMS connection.
     * 
     * @return A new database connection.
     * @exception SQLException
     *                If a database error occurs, or a connection cannot be
     *                obtained.
     */
    public static Connection getConnection() throws SQLException
    {
        initialize();

        return DriverManager
                .getConnection("jdbc:apache:commons:dbcp:dspacepool");
    }

    /**
     * Release resources associated with this connection.
     * 
     * @param c
     *            The connection to release
     */
    public static void freeConnection(Connection c)
    {
        try
        {
            if (c != null)
            {
                c.close();
            }
        }
        catch (SQLException e)
        {
            log.warn(e.getMessage());
            e.printStackTrace();
        }
    }

    /**
     * Create a table row object that can be passed into the insert method, not
     * commonly used unless the table has a referential integrity constraint.
     * 
     * @param table
     *            The RDBMS table in which to create the new row
     * @return The newly created row
     * @throws SQLException
     */
    public static TableRow row(String table) throws SQLException
    {
        return new TableRow(canonicalize(table), getColumnNames(table));
    }
    
    /**
     * Insert a table row into the RDBMS.
     * 
     * @param context
     *            Current DSpace context
     * @param row
     *            The row to insert
     * @exception SQLException
     *                If a database error occurs
     */
    public static void insert(Context context, TableRow row)
            throws SQLException
    {
        String table = canonicalize(row.getTable());

        // Get an ID (primary key) for this row by using the "getnextid"
        // SQL function in Postgres, or directly with sequences in Oracle
        String myQuery = "SELECT getnextid('" + table + "') AS result";

        if ("oracle".equals(ConfigurationManager.getProperty("db.name")))
        {
            myQuery = "SELECT " + table + "_seq" + ".nextval FROM dual";
        }

        Statement statement = context.getDBConnection().createStatement();
        ResultSet rs = statement.executeQuery(myQuery);

        rs.next();

        int newID = rs.getInt(1);
        rs.close();

        statement.close();

        // Set the ID in the table row object
        row.setColumn(getPrimaryKeyColumn(table), newID);

        StringBuffer sql = new StringBuffer().append("INSERT INTO ").append(
                table).append(" ( ");

        ColumnInfo[] info = getColumnInfo(table);

        for (int i = 0; i < info.length; i++)
        {
            sql.append((i == 0) ? "" : ",").append(info[i].getName());
        }

        sql.append(") VALUES ( ");

        // Values to insert
        for (int i = 0; i < info.length; i++)
        {
            sql.append((i == 0) ? "" : ",").append("?");
        }

        // Watch the syntax
        sql.append(")");

        execute(context.getDBConnection(), sql.toString(), Arrays.asList(info),
                row);
    }

    /**
     * Update changes to the RDBMS. Note that if the update fails, the values in
     * the row will NOT be reverted.
     * 
     * @param context
     *            Current DSpace context
     * @param row
     *            The row to update
     * @return The number of rows affected (1 or 0)
     * @exception SQLException
     *                If a database error occurs
     */
    public static int update(Context context, TableRow row) throws SQLException
    {
        String table = canonicalize(row.getTable());

        StringBuffer sql = new StringBuffer().append("update ").append(table)
                .append(" set ");

        ColumnInfo pk = getPrimaryKeyColumnInfo(table);
        ColumnInfo[] info = getNonPrimaryKeyColumns(table);

        for (int i = 0; i < info.length; i++)
        {
            sql.append((i == 0) ? "" : ", ").append(info[i].getName()).append(
                    " = ?");
        }

        sql.append(" where ").append(pk.getName()).append(" = ?");

        List columns = new ArrayList();
        columns.addAll(Arrays.asList(info));
        columns.add(pk);

        return execute(context.getDBConnection(), sql.toString(), columns, row);
    }

    /**
     * Delete row from the RDBMS.
     * 
     * @param context
     *            Current DSpace context
     * @param row
     *            The row to delete
     * @return The number of rows affected (1 or 0)
     * @exception SQLException
     *                If a database error occurs
     */
    public static int delete(Context context, TableRow row) throws SQLException
    {
        String pk = getPrimaryKeyColumn(row);

        if (row.isColumnNull(pk))
        {
            throw new IllegalArgumentException("Primary key value is null");
        }

        return delete(context, row.getTable(), row.getIntColumn(pk));
    }

    /**
     * Return metadata about a table.
     * 
     * @param table
     *            The name of the table
     * @return An array of ColumnInfo objects
     * @exception SQLException
     *                If a database error occurs
     */
    static ColumnInfo[] getColumnInfo(String table) throws SQLException
    {
        Map cinfo = getColumnInfoInternal(table);

        if (cinfo == null)
        {
            return null;
        }

        Collection info = cinfo.values();

        return (ColumnInfo[]) info.toArray(new ColumnInfo[info.size()]);
    }

    /**
     * Return info about column in table.
     * 
     * @param table
     *            The name of the table
     * @param column
     *            The name of the column
     * @return Information about the column
     * @exception SQLException
     *                If a database error occurs
     */
    static ColumnInfo getColumnInfo(String table, String column)
            throws SQLException
    {
        Map info = getColumnInfoInternal(table);

        return (info == null) ? null : (ColumnInfo) info.get(column);
    }

    /**
     * Return all the columns which are not primary keys.
     * 
     * @param table
     *            The name of the table
     * @return All the columns which are not primary keys, as an array of
     *         ColumnInfo objects
     * @exception SQLException
     *                If a database error occurs
     */
    static ColumnInfo[] getNonPrimaryKeyColumns(String table)
            throws SQLException
    {
        String pk = getPrimaryKeyColumn(table);
        ColumnInfo[] info = getColumnInfo(table);
        ColumnInfo[] results = new ColumnInfo[info.length - 1];
        int rcount = 0;

        for (int i = 0; i < info.length; i++)
        {
            if (!pk.equals(info[i].getName()))
            {
                results[rcount++] = info[i];
            }
        }

        return results;
    }

    /**
     * Return the names of all the columns of the given table.
     *