databasemanager.java

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

     * Load SQL from a reader into the RDBMS.
     * 
     * @param r
     *            The Reader from which to read the SQL.
     * @throws SQLException
     *            If a database error occurs
     * @throws IOException
     *            If an error occurs obtaining data from the reader
     */
    public static void loadSql(Reader r) throws SQLException, IOException
    {
        BufferedReader reader = new BufferedReader(r);
        StringBuffer sql = new StringBuffer();
        String SQL = null;

        String line = null;

        Connection connection = null;
        Statement statement = null;

        try
        {
            connection = getConnection();
            connection.setAutoCommit(true);
            statement = connection.createStatement();

            boolean inquote = false;

            while ((line = reader.readLine()) != null)
            {
                // Look for comments
                int commentStart = line.indexOf("--");

                String input = (commentStart != -1) ? line.substring(0,
                        commentStart) : line;

                // Empty line, skip
                if (input.trim().equals(""))
                {
                    continue;
                }

                // Put it on the SQL buffer
                sql.append(input.replace(';', ' ')); // remove all semicolons
                                                     // from sql file!

                // Add a space
                sql.append(" ");

                // More to come?
                // Look for quotes
                int index = 0;
                int count = 0;
                int inputlen = input.length();

                while ((index = input.indexOf("'", count)) != -1)
                {
                    // Flip the value of inquote
                    inquote = !inquote;

                    // Move the index
                    count = index + 1;

                    // Make sure we do not exceed the string length
                    if (count >= inputlen)
                    {
                        break;
                    }
                }

                // If we are in a quote, keep going
                // Note that this is STILL a simple heuristic that is not
                // guaranteed to be correct
                if (inquote)
                {
                    continue;
                }

                int endMarker = input.indexOf(";", index);

                if (endMarker == -1)
                {
                    continue;
                }

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

                SQL = sql.toString();

                try
                {
                    // Use execute, not executeQuery (which expects results) or
                    // executeUpdate
                    boolean succeeded = statement.execute(SQL);
                }
                catch (SQLWarning sqlw)
                {
                    if (log.isDebugEnabled())
                    {
                        log.debug("Got SQL Warning: " + sqlw, sqlw);
                    }
                }
                catch (SQLException sqle)
                {
                    String msg = "Got SQL Exception: " + sqle;
                    String sqlmessage = sqle.getMessage();

                    // These are Postgres-isms:
                    // There's no easy way to check if a table exists before
                    // creating it, so we always drop tables, then create them
                    boolean isDrop = ((SQL != null) && (sqlmessage != null)
                            && (SQL.toUpperCase().startsWith("DROP")) && (sqlmessage
                            .indexOf("does not exist") != -1));

                    // Creating a view causes a bogus warning
                    boolean isNoResults = ((SQL != null)
                            && (sqlmessage != null)
                            && ((SQL.toUpperCase().startsWith("CREATE VIEW")) || (SQL
                                    .toUpperCase()
                                    .startsWith("CREATE FUNCTION"))) && (sqlmessage
                            .indexOf("No results were returned") != -1));

                    // If the messages are bogus, give them a low priority
                    if (isDrop || isNoResults)
                    {
                        if (log.isDebugEnabled())
                        {
                            log.debug(msg, sqle);
                        }
                    }
                    // Otherwise, we need to know!
                    else
                    {
                        if (log.isEnabledFor(Priority.WARN))
                        {
                            log.warn(msg, sqle);
                        }
                    }
                }

                // Reset SQL buffer
                sql = new StringBuffer();
                SQL = null;
            }
        }
        finally
        {
            if (connection != null)
            {
                connection.close();
            }

            if (statement != null)
            {
                statement.close();
            }
        }
    }

    ////////////////////////////////////////
    // Helper methods
    ////////////////////////////////////////

    /**
     * Convert the current row in a ResultSet into a TableRow object.
     * 
     * @param results
     *            A ResultSet to process
     * @param table
     *            The name of the table
     * @return A TableRow object with the data from the ResultSet
     * @exception SQLException
     *                If a database error occurs
     */
    static TableRow process(ResultSet results, String table)
            throws SQLException
    {
        ResultSetMetaData meta = results.getMetaData();
        int columns = meta.getColumnCount() + 1;

        List columnNames = (table == null) ? getColumnNames(meta)
                : getColumnNames(table);

        TableRow row = new TableRow(canonicalize(table), columnNames);

        // Process the columns in order
        // (This ensures maximum backwards compatibility with
        // old JDBC drivers)
        for (int i = 1; i < columns; i++)
        {
            String name = meta.getColumnName(i);
            int jdbctype = meta.getColumnType(i);

            if (jdbctype == Types.BIT)
            {
                row.setColumn(name, results.getBoolean(i));
            }
            else if ((jdbctype == Types.INTEGER) || (jdbctype == Types.NUMERIC)
                    || (jdbctype == Types.DECIMAL))
            {
                row.setColumn(name, results.getInt(i));
            }
            else if (jdbctype == Types.BIGINT)
            {
                row.setColumn(name, results.getLong(i));
            }
            else if (jdbctype == Types.VARCHAR)
            {
                try
                {
                    byte[] bytes = results.getBytes(i);

                    if (bytes != null)
                    {
                        String mystring = new String(results.getBytes(i),
                                "UTF-8");
                        row.setColumn(name, mystring);
                    }
                    else
                    {
                        row.setColumn(name, results.getString(i));
                    }
                }
                catch (UnsupportedEncodingException e)
                {
                    // do nothing, UTF-8 is built in!
                }
            }
            else if (jdbctype == Types.DATE)
            {
                row.setColumn(name, results.getDate(i));
            }
            else if (jdbctype == Types.TIME)
            {
                row.setColumn(name, results.getTime(i));
            }
            else if (jdbctype == Types.TIMESTAMP)
            {
                row.setColumn(name, results.getTimestamp(i));
            }
            else
            {
                throw new IllegalArgumentException("Unsupported JDBC type: "
                        + jdbctype);
            }

            if (results.wasNull())
            {
                row.setColumnNull(name);
            }
        }

        return row;
    }

    /**
     * Return the name of the primary key column. We assume there's only one
     * primary key per table; if there are more, only the first one will be
     * returned.
     * 
     * @param row
     *            The TableRow to return the primary key for.
     * @return The name of the primary key column, or null if the row has no
     *         primary key.
     * @exception SQLException
     *                If a database error occurs
     */
    public static String getPrimaryKeyColumn(TableRow row) throws SQLException
    {
        return getPrimaryKeyColumn(row.getTable());
    }

    /**
     * Return the name of the primary key column in the given table. We assume
     * there's only one primary key per table; if there are more, only the first
     * one will be returned.
     * 
     * @param table
     *            The name of the RDBMS table
     * @return The name of the primary key column, or null if the table has no
     *         primary key.
     * @exception SQLException
     *                If a database error occurs
     */
    protected static String getPrimaryKeyColumn(String table)
            throws SQLException
    {
        ColumnInfo info = getPrimaryKeyColumnInfo(table);

        return (info == null) ? null : info.getName();
    }

    /**
     * Return column information for the primary key column, or null if the
     * table has no primary key. We assume there's only one primary key per
     * table; if there are more, only the first one will be returned.
     * 
     * @param table
     *            The name of the RDBMS table
     * @return A ColumnInfo object, or null if the table has no primary key.
     * @exception SQLException
     *                If a database error occurs
     */
    static ColumnInfo getPrimaryKeyColumnInfo(String table) throws SQLException
    {
        ColumnInfo[] cinfo = getColumnInfo(canonicalize(table));

        for (int i = 0; i < cinfo.length; i++)
        {
            ColumnInfo info = cinfo[i];

            if (info.isPrimaryKey())
            {
                return info;
            }
        }

        return null;
    }

    /**
     * Execute SQL as a PreparedStatement on Connection. Bind parameters in
     * columns to the values in the table row before executing.
     * 
     * @param connection
     *            The SQL connection
     * @param sql
     *            The query to execute
     * @param columns
     *            The columns to bind
     * @param row
     *            The row
     * @return The number of rows affected by the query.
     * @exception SQLException
     *                If a database error occurs
     */
    private static int execute(Connection connection, String sql, List columns,
            TableRow row) throws SQLException
    {
        PreparedStatement statement = null;

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

        try
        {
            statement = connection.prepareStatement(sql);

            int count = 0;

            for (Iterator iterator = columns.iterator(); iterator.hasNext();)
            {
                count++;

                ColumnInfo info = (ColumnInfo) iterator.next();
                String column = info.getName();
                int jdbctype = info.getType();

                if (row.isColumnNull(column))
                {
                    statement.setNull(count, jdbctype);

                    continue;
                }
                else if (jdbctype == Types.BIT)
                {
                    statement.setBoolean(count, row.getBooleanColumn(column));

                    continue;
                }
                else if ((jdbctype == Types.INTEGER)
                        || (jdbctype == Types.DECIMAL))
                {
                    statement.setInt(count, row.getIntColumn(column));

                    continue;
                }
                else if (jdbctype == Types.BIGINT)
                {