databasemanager.java

dspace 用j2ee架构的一个数字图书馆.开源程序

     * @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);

        // Need a pair of single quote marks:
        // MessageFormat treats this: '{2}' as the literal {2}
        String sql = MessageFormat.format(
                "delete from {0} where {1} = ''{2}''", new Object[] { ctable,
                        column, value });

        return updateQuery(context, sql);
    }

    /**
     * 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.
     * 
     * @param table
     *            The name of the table
     * @return The names of all the columns of the given table, as a List. Each
     *         element of the list is a String.
     * @exception SQLException
     *                If a database error occurs
     */
    protected static List getColumnNames(String table) throws SQLException
    {
        List results = new ArrayList();
        ColumnInfo[] info = getColumnInfo(table);

        for (int i = 0; i < info.length; i++)
        {
            results.add(info[i].getName());
        }

        return results;
    }

    /**
     * Return the names of all the columns of the ResultSet.
     * 
     * @param table
     *            The ResultSetMetaData
     * @return The names of all the columns of the given table, as a List. Each
     *         element of the list is a String.
     * @exception SQLException
     *                If a database error occurs
     */
    protected static List getColumnNames(ResultSetMetaData meta)
            throws SQLException
    {
        List results = new ArrayList();
        int columns = meta.getColumnCount();

        for (int i = 0; i < columns; i++)
        {
            results.add(meta.getColumnLabel(i + 1));
        }

        return results;
    }

    /**
     * Return the canonical name for a table.
     * 
     * @param table
     *            The name of the table.
     * @return The canonical name of the table.
     */
    static String canonicalize(String table)
    {
        // Oracle expects upper-case table names
        if ("oracle".equals(ConfigurationManager.getProperty("db.name")))
        {
            return (table == null) ? null : table.toUpperCase();
        }

        // default database postgres wants lower-case table names
        return (table == null) ? null : table.toLowerCase();
    }

    ////////////////////////////////////////
    // SQL loading methods
    ////////////////////////////////////////

    /**
     * Load SQL into the RDBMS.
     * 
     * @param sql
     *            The SQL to load.
     * throws SQLException
     *            If a database error occurs
     */
    public static void loadSql(String sql) throws SQLException
    {
        try
        {
            loadSql(new StringReader(sql));
        }
        catch (IOException ioe)
        {
        }
    }

    /**