Java Code Examples for java.sql.DatabaseMetaData#getIdentifierQuoteString()

public static TableSchema from(final Connection conn, final String catalog, final String schema, final String tableName,
                               final boolean translateColumnNames, final boolean includePrimaryKeys) throws SQLException {
    final DatabaseMetaData dmd = conn.getMetaData();

    try (final ResultSet colrs = dmd.getColumns(catalog, schema, tableName, "%")) {
        final List<ColumnDescription> cols = new ArrayList<>();
        while (colrs.next()) {
            final ColumnDescription col = ColumnDescription.from(colrs);
            cols.add(col);
        }

        final Set<String> primaryKeyColumns = new HashSet<>();
        if (includePrimaryKeys) {
            try (final ResultSet pkrs = conn.getMetaData().getPrimaryKeys(catalog, null, tableName)) {

                while (pkrs.next()) {
                    final String colName = pkrs.getString("COLUMN_NAME");
                    primaryKeyColumns.add(normalizeColumnName(colName, translateColumnNames));
                }
            }
        }

        return new TableSchema(cols, translateColumnNames, primaryKeyColumns, dmd.getIdentifierQuoteString());
    }
}
 

protected void checkConnectionMetadata() {
    synchronized (configuration) {
        // only done once metadata assumed to be the same for every connection
        if (!metadataChecked) {
            try (Connection connection = context.getConnection()) {
                DatabaseMetaData metadata = connection.getMetaData();
                if (!metadata.supportsTransactions()) {
                    transactionMode = TransactionMode.NONE;
                }
                supportsBatchUpdates = metadata.supportsBatchUpdates();
                String quoteIdentifier = metadata.getIdentifierQuoteString();
                queryOptions = new QueryBuilder.Options(quoteIdentifier, true,
                        configuration.getTableTransformer(),
                        configuration.getColumnTransformer(),
                        configuration.getQuoteTableNames(),
                        configuration.getQuoteColumnNames());
                metadataChecked = true;
            } catch (SQLException e) {
                throw new PersistenceException(e);
            }
        }
    }
}
 

static TableSchema from(final Connection conn, final String catalog, final String schema, final String tableName,
                        final boolean translateColumnNames) throws SQLException {
    final DatabaseMetaData dmd = conn.getMetaData();
    if (!dmd.getTables(catalog, schema, tableName, null).next()) {
        throw new SQLException("Table " + tableName + " does not exist in the database");
    }

    try (final ResultSet colrs = dmd.getColumns(catalog, schema, tableName, "%")) {
        final List<ColumnDescription> cols = new ArrayList<>();
        while (colrs.next()) {
            final ColumnDescription col = ColumnDescription.from(colrs);
            cols.add(col);
        }

        return new TableSchema(cols, translateColumnNames, dmd.getIdentifierQuoteString());
    }
}
 

public static TableSchema from(final Connection conn, final String catalog, final String schema, final String tableName,
                               final boolean translateColumnNames, final boolean includePrimaryKeys) throws SQLException {
    final DatabaseMetaData dmd = conn.getMetaData();

    try (final ResultSet colrs = dmd.getColumns(catalog, schema, tableName, "%")) {
        final List<ColumnDescription> cols = new ArrayList<>();
        while (colrs.next()) {
            final ColumnDescription col = ColumnDescription.from(colrs);
            cols.add(col);
        }

        final Set<String> primaryKeyColumns = new HashSet<>();
        if (includePrimaryKeys) {
            try (final ResultSet pkrs = conn.getMetaData().getPrimaryKeys(catalog, null, tableName)) {

                while (pkrs.next()) {
                    final String colName = pkrs.getString("COLUMN_NAME");
                    primaryKeyColumns.add(normalizeColumnName(colName, translateColumnNames));
                }
            }
        }

        return new TableSchema(cols, translateColumnNames, primaryKeyColumns, dmd.getIdentifierQuoteString());
    }
}
 

public static TableSchema from(final Connection conn, final String catalog, final String schema, final String tableName,
                               final boolean translateColumnNames, final boolean includePrimaryKeys) throws SQLException {
    final DatabaseMetaData dmd = conn.getMetaData();

    try (final ResultSet colrs = dmd.getColumns(catalog, schema, tableName, "%")) {
        final List<ColumnDescription> cols = new ArrayList<>();
        while (colrs.next()) {
            final ColumnDescription col = ColumnDescription.from(colrs);
            cols.add(col);
        }

        final Set<String> primaryKeyColumns = new HashSet<>();
        if (includePrimaryKeys) {
            try (final ResultSet pkrs = dmd.getPrimaryKeys(catalog, null, tableName)) {

                while (pkrs.next()) {
                    final String colName = pkrs.getString("COLUMN_NAME");
                    primaryKeyColumns.add(normalizeColumnName(colName, translateColumnNames));
                }
            }
        }

        return new TableSchema(cols, translateColumnNames, primaryKeyColumns, dmd.getIdentifierQuoteString());
    }
}
 

private String getIdentifierQuoteString(DatabaseMetaData databaseMetaData) {
  try {
    return databaseMetaData.getIdentifierQuoteString();
  } catch (SQLException e) {
    throw new IllegalArgumentException("cannot deduce identifier quote string", e);
  }
}
 

private String getIdentifierQuoteString(DatabaseMetaData databaseMetaData) {
  try {
    return databaseMetaData.getIdentifierQuoteString();
  } catch (SQLException e) {
    throw new IllegalArgumentException("cannot deduce identifier quote string", e);
  }
}
 

@Override
public String escapeIdent(String ident)
{
    if (quoteString == null) {
        try {
            DatabaseMetaData meta = connection.getMetaData();
            quoteString = meta.getIdentifierQuoteString();
        }
        catch (SQLException ex) {
            throw new DatabaseException("Failed to retrieve database metadata to quote an identifier name", ex);
        }
    }
    return quoteString + ident.replaceAll(Pattern.quote(quoteString), quoteString + quoteString) + quoteString;
}
 

private String getIdentifierQuoteString(DatabaseMetaData databaseMetaData) {
  try {
    return databaseMetaData.getIdentifierQuoteString();
  } catch (SQLException e) {
    throw new IllegalArgumentException("cannot deduce identifier quote string", e);
  }
}
 

private void initQuoteString() throws Exception {
    DatabaseMetaData md = getConnection().getMetaData();
    quoteString = md.getIdentifierQuoteString();
}
 

/**
 * Uses a connection from the database to set meta data information about
 * what different JDBC drivers and databases support.
 *
 * @param con the connection.
 * @throws SQLException if an SQL exception occurs.
 */
private static void setMetaData(Connection con) throws SQLException {
    DatabaseMetaData metaData = con.getMetaData();
    // Supports transactions?
    transactionsSupported = metaData.supportsTransactions();
    // Supports subqueries?
    subqueriesSupported = metaData.supportsCorrelatedSubqueries();
    // Supports scroll insensitive result sets? Try/catch block is a
    // workaround for DB2 JDBC driver, which throws an exception on
    // the method call.
    try {
        scrollResultsSupported = metaData.supportsResultSetType(
                ResultSet.TYPE_SCROLL_INSENSITIVE);
    }
    catch (Exception e) {
        scrollResultsSupported = false;
    }
    // Supports batch updates
    batchUpdatesSupported = metaData.supportsBatchUpdates();

    // Set defaults for other meta properties
    streamTextRequired = false;
    maxRowsSupported = true;
    fetchSizeSupported = true;
    identifierQuoteString = metaData.getIdentifierQuoteString();

    // Get the database name so that we can perform meta data settings.
    String dbName = metaData.getDatabaseProductName().toLowerCase();
    String driverName = metaData.getDriverName().toLowerCase();

    // Oracle properties.
    if (dbName.indexOf("oracle") != -1) {
        databaseType = DatabaseType.oracle;
        streamTextRequired = true;
        scrollResultsSupported = false; /* TODO comment and test this, it should be supported since 10g */
        // The i-net AUGURO JDBC driver
        if (driverName.indexOf("auguro") != -1) {
            streamTextRequired = false;
            fetchSizeSupported = true;
            maxRowsSupported = false;
        }
    }
    // Postgres properties
    else if (dbName.indexOf("postgres") != -1) {
        databaseType = DatabaseType.postgresql;
        // Postgres blows, so disable scrolling result sets.
        scrollResultsSupported = false;
        fetchSizeSupported = false;
    }
    // Interbase properties
    else if (dbName.indexOf("interbase") != -1) {
        databaseType = DatabaseType.interbase;
        fetchSizeSupported = false;
        maxRowsSupported = false;
    }
    // SQLServer
    else if (dbName.indexOf("sql server") != -1) {
        databaseType = DatabaseType.sqlserver;
        // JDBC driver i-net UNA properties
        if (driverName.indexOf("una") != -1) {
            fetchSizeSupported = true;
            maxRowsSupported = false;
        }
    }
    // MySQL properties
    else if (dbName.indexOf("mysql") != -1) {
        databaseType = DatabaseType.mysql;
        transactionsSupported = false; /* TODO comment and test this, it should be supported since 5.0 */
    }
    // HSQL properties
    else if (dbName.indexOf("hsql") != -1) {
        databaseType = DatabaseType.hsqldb;
        // scrollResultsSupported = false; /* comment and test this, it should be supported since 1.7.2 */
    }
    // DB2 properties.
    else if (dbName.indexOf("db2") != 1) {
        databaseType = DatabaseType.db2;
    }
}
 

/**
 * Creates a new runner which will execute the statements using the given connection.
 *
 * <p>Some {@code maxRowsPerInsert} parameter values of interest:</p>
 * <ul>
 *   <li>A value of 0 means to create only the schemas without inserting any data in them.</li>
 *   <li>A value of 1 means to use one separated {@code INSERT INTO} statement for each row, which may be slow.</li>
 *   <li>A value of 100 is a value which have been found empirically as giving good results.</li>
 *   <li>A value of {@link Integer#MAX_VALUE} means to not perform any attempt to limit the number of rows in an
 *       {@code INSERT INTO} statement. Note that this causes {@link StackOverflowError} in some JDBC driver.</li>
 * </ul>
 *
 * @param  connection        the connection to the database.
 * @param  maxRowsPerInsert  maximum number of rows per {@code "INSERT INTO"} statement.
 * @throws SQLException if an error occurred while creating a SQL statement.
 */
public ScriptRunner(final Connection connection, final int maxRowsPerInsert) throws SQLException {
    ArgumentChecks.ensureNonNull("connection", connection);
    ArgumentChecks.ensurePositive("maxRowsPerInsert", maxRowsPerInsert);
    final DatabaseMetaData metadata = connection.getMetaData();
    this.maxRowsPerInsert   = maxRowsPerInsert;
    this.dialect            = Dialect.guess(metadata);
    this.identifierQuote    = metadata.getIdentifierQuoteString();
    this.isSchemaSupported  = metadata.supportsSchemasInTableDefinitions() &&
                              metadata.supportsSchemasInDataManipulation();
    this.isCatalogSupported = metadata.supportsCatalogsInTableDefinitions() &&
                              metadata.supportsCatalogsInDataManipulation();
    this.statement          = connection.createStatement();
    switch (dialect) {
        default: {
            isEnumTypeSupported      = false;
            isGrantOnSchemaSupported = false;
            isGrantOnTableSupported  = false;
            isCreateLanguageRequired = false;
            isCommentSupported       = false;
            break;
        }
        case POSTGRESQL: {
            final int version = metadata.getDatabaseMajorVersion();
            isEnumTypeSupported      = (version == 8) ? metadata.getDatabaseMinorVersion() >= 4 : version >= 8;
            isGrantOnSchemaSupported = true;
            isGrantOnTableSupported  = true;
            isCreateLanguageRequired = (version < 9);
            isCommentSupported       = true;
            break;
        }
        case HSQL: {
            isEnumTypeSupported      = false;
            isGrantOnSchemaSupported = false;
            isGrantOnTableSupported  = false;
            isCreateLanguageRequired = false;
            isCommentSupported       = false;
            /*
             * HSQLDB stores tables in memory by default. For storing the tables on files, we have to
             * use "CREATE CACHED TABLE" statement, which is HSQL-specific. For avoiding SQL dialect,
             * the following statement change the default setting on current connection.
             *
             * Reference: http://hsqldb.org/doc/guide/dbproperties-chapt.html#dpc_db_props_url
             */
            statement.execute("SET DATABASE DEFAULT TABLE TYPE CACHED");
            break;
        }
    }
    /*
     * Now build the list of statements to skip, depending of which features are supported by the database.
     * WARNING: do not use capturing group here, because some subclasses (e.g. EPSGInstaller) will use their
     * own capturing groups. A non-capturing group is declared by "(?:A|B)" instead than a plain "(A|B)".
     */
    if (!isEnumTypeSupported) {
        addStatementToSkip("CREATE\\s+(?:TYPE|CAST)\\s+.*");
    }
    if (!isGrantOnSchemaSupported || !isGrantOnTableSupported) {
        addStatementToSkip("GRANT\\s+\\w+\\s+ON\\s+");
        if (isGrantOnSchemaSupported) {
            regexOfStmtToSkip.append("TABLE");
        } else if (isGrantOnTableSupported) {
            regexOfStmtToSkip.append("SCHEMA");
        } else {
            regexOfStmtToSkip.append("(?:TABLE|SCHEMA)");
        }
        regexOfStmtToSkip.append("\\s+.*");
    }
    if (!isCommentSupported) {
        addStatementToSkip("COMMENT\\s+ON\\s+.*");
    }
}
 

/**
 * Creates a new {@code SQLBuilder} initialized from the given database metadata.
 *
 * @param  metadata     the database metadata.
 * @param  quoteSchema  whether the schema name should be written between quotes.
 * @throws SQLException if an error occurred while fetching the database metadata.
 */
public SQLBuilder(final DatabaseMetaData metadata, final boolean quoteSchema) throws SQLException {
    dialect = Dialect.guess(metadata);
    quote   = metadata.getIdentifierQuoteString();
    escape  = metadata.getSearchStringEscape();
    this.quoteSchema = quoteSchema;
}