jdbcdatabasemetadata.java

非常棒的java数据库

/*
 * Copyright 2004-2008 H2 Group. Licensed under the H2 License, Version 1.0
 * (http://h2database.com/html/license.html).
 * Initial Developer: H2 Group
 */
package org.h2.jdbc;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
//#ifdef JDK16
/*
import java.sql.RowIdLifetime;
*/
//#endif
import java.sql.SQLException;

import org.h2.engine.Constants;
import org.h2.message.Message;
import org.h2.message.Trace;
import org.h2.message.TraceObject;
import org.h2.util.StringUtils;

/**
 * Represents the meta data for a database.
 */
public class JdbcDatabaseMetaData extends TraceObject implements DatabaseMetaData {

    private JdbcConnection conn;

    /**
     * Returns the major version of this driver.
     *
     * @return the major version number
     */
    public int getDriverMajorVersion() {
        debugCodeCall("getDriverMajorVersion");
        return Constants.VERSION_MAJOR;
    }

    /**
     * Returns the minor version of this driver.
     *
     * @return the minor version number
     */
    public int getDriverMinorVersion() {
        debugCodeCall("getDriverMinorVersion");
        return Constants.VERSION_MINOR;
    }

    /**
     * Gets the database product name.
     *
     * @return the product name
     */
    public String getDatabaseProductName() {
        debugCodeCall("getDatabaseProductName");
        return Constants.PRODUCT_NAME;
    }

    /**
     * Gets the product version of the database.
     *
     * @return the product version
     */
    public String getDatabaseProductVersion() {
        debugCodeCall("getDatabaseProductVersion");
        return Constants.getVersion();
    }

    /**
     * Gets the name of the JDBC driver.
     *
     * @return the driver name
     */
    public String getDriverName() {
        debugCodeCall("getDriverName");
        return Constants.DRIVER_NAME;
    }

    /**
     * Gets the version number of the driver in the format
     * [MajorVersion].[MinorVersion].
     *
     * @return the version number
     */
    public String getDriverVersion() {
        debugCodeCall("getDriverVersion");
        return Constants.getVersion();
    }

    /**
     * Gets the list of tables in the database. The result set is sorted by
     * TABLE_TYPE, TABLE_SCHEM, and TABLE_NAME.
     * 
     * <ul>
     * <li>1 TABLE_CAT (String) table catalog </li>
     * <li>2 TABLE_SCHEM (String) table schema </li>
     * <li>3 TABLE_NAME (String) table name </li>
     * <li>4 TABLE_TYPE (String) table type </li>
     * <li>5 REMARKS (String) comment </li>
     * <li>6 SQL (String) the create table statement or NULL for systems tables
     * </li>
     * </ul>
     * 
     * @param catalog null (to get all objects) or the catalog name
     * @param schemaPattern null (to get all objects) or a schema name
     *            (uppercase for unquoted names)
     * @param tableNamePattern null (to get all objects) or a table name
     *            (uppercase for unquoted names)
     * @param types null or a list of table types
     * @return the list of columns
     * @throws SQLException if the connection is closed
     */
    public ResultSet getTables(String catalog, String schemaPattern, String tableNamePattern, String[] types) throws SQLException {
        try {
            if (debug()) {
                debugCode("getTables(" + quote(catalog) + ", " + quote(schemaPattern) + ", " + quote(tableNamePattern)
                        + ", " + quoteArray(types) + ");");
            }
            checkClosed();
            String tableType;
            if (types != null && types.length > 0) {
                StringBuffer buff = new StringBuffer("TABLE_TYPE IN(");
                for (int i = 0; i < types.length; i++) {
                    if (i > 0) {
                        buff.append(", ");
                    }
                    buff.append("?");
                }
                buff.append(")");
                tableType = buff.toString();
            } else {
                tableType = "TRUE";
            }
            PreparedStatement prep = conn.prepareAutoCloseStatement("SELECT "
                    + "TABLE_CATALOG TABLE_CAT, "
                    + "TABLE_SCHEMA TABLE_SCHEM, "
                    + "TABLE_NAME, "
                    + "TABLE_TYPE, "
                    + "REMARKS, "
                    + "SQL "
                    + "FROM INFORMATION_SCHEMA.TABLES "
                    + "WHERE TABLE_CATALOG LIKE ? "
                    + "AND TABLE_SCHEMA LIKE ? "
                    + "AND TABLE_NAME LIKE ? "
                    + "AND (" + tableType + ") "
                    + "ORDER BY TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME");
            prep.setString(1, getCatalogPattern(catalog));
            prep.setString(2, getSchemaPattern(schemaPattern));
            prep.setString(3, getPattern(tableNamePattern));
            for (int i = 0; types != null && i < types.length; i++) {
                prep.setString(4 + i, types[i]);
            }
            return prep.executeQuery();
        } catch (Throwable e) {
            throw logAndConvert(e);
        }
    }

    /**
     * Gets the list of columns. The result set is sorted by TABLE_SCHEM,
     * TABLE_NAME, and ORDINAL_POSITION.
     * 
     * <ul>
     * <li>1 TABLE_CAT (String) table catalog </li>
     * <li>2 TABLE_SCHEM (String) table schema </li>
     * <li>3 TABLE_NAME (String) table name </li>
     * <li>4 COLUMN_NAME (String) column name </li>
     * <li>5 DATA_TYPE (short) data type (see java.sql.Types) </li>
     * <li>6 TYPE_NAME (String) data type name ("INTEGER", "VARCHAR",...) </li>
     * <li>7 COLUMN_SIZE (int) precision </li>
     * <li>8 BUFFER_LENGTH (int) unused </li>
     * <li>9 DECIMAL_DIGITS (int) scale (0 for INTEGER and VARCHAR) </li>
     * <li>10 NUM_PREC_RADIX (int) radix (always 10) </li>
     * <li>11 NULLABLE (int) nullable or not. columnNoNulls or columnNullable
     * </li>
     * <li>12 REMARKS (String) comment (always empty) </li>
     * <li>13 COLUMN_DEF (String) default value </li>
     * <li>14 SQL_DATA_TYPE (int) unused </li>
     * <li>15 SQL_DATETIME_SUB (int) unused </li>
     * <li>16 CHAR_OCTET_LENGTH (int) unused </li>
     * <li>17 ORDINAL_POSITION (int) the column index (1,2,...) </li>
     * <li>18 IS_NULLABLE (String) "NO" or "YES" </li>
     * </ul>
     * 
     * @param catalog null (to get all objects) or the catalog name
     * @param schemaPattern null (to get all objects) or a schema name
     *            (uppercase for unquoted names)
     * @param tableNamePattern null (to get all objects) or a table name
     *            (uppercase for unquoted names)
     * @param columnNamePattern null (to get all objects) or a column name
     *            (uppercase for unquoted names)
     * @return the list of columns
     * @throws SQLException if the connection is closed
     */
    public ResultSet getColumns(String catalog, String schemaPattern,
            String tableNamePattern, String columnNamePattern)
            throws SQLException {
        try {
            if (debug()) {
                debugCode("getColumns(" + quote(catalog)+", "
                        +quote(schemaPattern)+", "
                        +quote(tableNamePattern)+", "
                        +quote(columnNamePattern)+");");
            }
            checkClosed();
            PreparedStatement prep = conn.prepareAutoCloseStatement("SELECT "
                    + "TABLE_CATALOG TABLE_CAT, "
                    + "TABLE_SCHEMA TABLE_SCHEM, "
                    + "TABLE_NAME, "
                    + "COLUMN_NAME, "
                    + "DATA_TYPE, "
                    + "TYPE_NAME, "
                    + "CHARACTER_MAXIMUM_LENGTH COLUMN_SIZE, "
                    + "CHARACTER_MAXIMUM_LENGTH BUFFER_LENGTH, "
                    + "NUMERIC_SCALE DECIMAL_DIGITS, "
                    + "NUMERIC_PRECISION_RADIX NUM_PREC_RADIX, "
                    + "NULLABLE, "
                    + "REMARKS, "
                    + "COLUMN_DEFAULT COLUMN_DEF, "
                    + "DATA_TYPE SQL_DATA_TYPE, "
                    + "ZERO() SQL_DATETIME_SUB, "
                    + "CHARACTER_OCTET_LENGTH CHAR_OCTET_LENGTH, "
                    + "ORDINAL_POSITION, "
                    + "IS_NULLABLE IS_NULLABLE "
                    + "FROM INFORMATION_SCHEMA.COLUMNS "
                    + "WHERE TABLE_CATALOG LIKE ? "
                    + "AND TABLE_SCHEMA LIKE ? "
                    + "AND TABLE_NAME LIKE ? "
                    + "AND COLUMN_NAME LIKE ? "
                    + "ORDER BY TABLE_SCHEM, TABLE_NAME, ORDINAL_POSITION");
            prep.setString(1, getCatalogPattern(catalog));
            prep.setString(2, getSchemaPattern(schemaPattern));
            prep.setString(3, getPattern(tableNamePattern));
            prep.setString(4, getPattern(columnNamePattern));
            return prep.executeQuery();
        } catch (Throwable e) {
            throw logAndConvert(e);
        }
    }

    /**
     * Gets the list of indexes for this database. The primary key index (if
     * there is one) is also listed, with the name PRIMARY_KEY. The result set
     * is sorted by NON_UNIQUE ('false' first), TYPE, TABLE_SCHEM, INDEX_NAME,
     * and ORDINAL_POSITION.
     * 
     * <ul>
     * <li>1 TABLE_CAT (String) table catalog </li>
     * <li>2 TABLE_SCHEM (String) table schema </li>
     * <li>3 TABLE_NAME (String) table name </li>
     * <li>4 NON_UNIQUE (boolean) 'false' for unique, 'true' for non-unique
     * </li>
     * <li>5 INDEX_QUALIFIER (String) index catalog </li>
     * <li>6 INDEX_NAME (String) index name </li>
     * <li>7 TYPE (short) the index type (always tableIndexOther) </li>
     * <li>8 ORDINAL_POSITION (short) column index (1, 2, ...) </li>
     * <li>9 COLUMN_NAME (String) column name </li>
     * <li>10 ASC_OR_DESC (String) ascending or descending (always 'A') </li>
     * <li>11 CARDINALITY (int) numbers of unique values </li>
     * <li>12 PAGES (int) number of pages use (always 0) </li>
     * <li>13 FILTER_CONDITION (String) filter condition (always empty) </li>
     * <li>14 SORT_TYPE (int) the sort type bit map: 1=DESCENDING,
     * 2=NULLS_FIRST, 4=NULLS_LAST </li>
     * </ul>
     * 
     * @param catalog null (to get all objects) or the catalog name
     * @param schema schema name (must be specified)
     * @param tableName table name (must be specified)
     * @param unique only unique indexes
     * @param approximate is ignored
     * @return the list of indexes and columns
     * @throws SQLException if the connection is closed
     */
    public ResultSet getIndexInfo(String catalog, String schema, String tableName, boolean unique, boolean approximate)
            throws SQLException {
        try {
            if (debug()) {
                debugCode("getIndexInfo(" + quote(catalog) + ", " + quote(schema) + ", " + quote(tableName) + ", "
                        + unique + ", " + approximate + ");");
            }
            String uniqueCondition;
            if (unique) {
                uniqueCondition = "NON_UNIQUE=FALSE";
            } else {
                uniqueCondition = "TRUE";
            }
            checkClosed();
            PreparedStatement prep = conn.prepareAutoCloseStatement("SELECT "
                    + "TABLE_CATALOG TABLE_CAT, "
                    + "TABLE_SCHEMA TABLE_SCHEM, "
                    + "TABLE_NAME, "
                    + "NON_UNIQUE, "
                    + "TABLE_CATALOG INDEX_QUALIFIER, "
                    + "INDEX_NAME, "
                    + "INDEX_TYPE TYPE, "
                    + "ORDINAL_POSITION, "
                    + "COLUMN_NAME, "
                    + "ASC_OR_DESC, "
                    + "CARDINALITY, " // TODO meta data for number of unique values in an index
                    + "PAGES, "
                    + "FILTER_CONDITION, "
                    + "SORT_TYPE "
                    + "FROM INFORMATION_SCHEMA.INDEXES "
                    + "WHERE TABLE_CATALOG LIKE ? "
                    + "AND TABLE_SCHEMA LIKE ? "
                    + "AND (" + uniqueCondition + ") "
                    + "AND TABLE_NAME = ? "
                    + "ORDER BY NON_UNIQUE, TYPE, TABLE_SCHEM, INDEX_NAME, ORDINAL_POSITION");
            prep.setString(1, getCatalogPattern(catalog));
            prep.setString(2, getSchemaPattern(schema));
            prep.setString(3, tableName);
            return prep.executeQuery();
        } catch (Throwable e) {
            throw logAndConvert(e);
        }
    }

    /**
     * Gets the primary key columns for a table. The result set is sorted by
     * TABLE_SCHEM, and COLUMN_NAME (and not by KEY_SEQ).
     * 
     * <ul>
     * <li>1 TABLE_CAT (String) table catalog </li>
     * <li>2 TABLE_SCHEM (String) table schema </li>
     * <li>3 TABLE_NAME (String) table name </li>
     * <li>4 COLUMN_NAME (String) column name </li>
     * <li>5 KEY_SEQ (short) the column index of this column (1,2,...) </li>
     * <li>6 PK_NAME (String) always 'PRIMARY_KEY' </li>
     * </ul>
     * 
     * @param catalog null (to get all objects) or the catalog name
     * @param schema schema name (must be specified)
     * @param tableName table name (must be specified)
     * @return the list of primary key columns
     * @throws SQLException if the connection is closed
     */
    public ResultSet getPrimaryKeys(String catalog, String schema, String tableName) throws SQLException {
        try {
            if (debug()) {
                debugCode("getPrimaryKeys("
                        +quote(catalog)+", "
                        +quote(schema)+", "
                        +quote(tableName)+");");
            }
            checkClosed();
            PreparedStatement prep = conn.prepareAutoCloseStatement("SELECT "
                    + "TABLE_CATALOG TABLE_CAT, "
                    + "TABLE_SCHEMA TABLE_SCHEM, "
                    + "TABLE_NAME, "
                    + "COLUMN_NAME, "
                    + "ORDINAL_POSITION KEY_SEQ, "
                    + "INDEX_NAME PK_NAME "
                    + "FROM INFORMATION_SCHEMA.INDEXES "
                    + "WHERE TABLE_CATALOG LIKE ? "
                    + "AND TABLE_SCHEMA LIKE ? "
                    + "AND TABLE_NAME = ? "
                    + "AND PRIMARY_KEY = TRUE "
                    + "ORDER BY COLUMN_NAME");