Java Code Examples for com.mysql.cj.util.StringUtils#unQuoteIdentifier()

The following examples show how to use com.mysql.cj.util.StringUtils#unQuoteIdentifier() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.

Example 1

Source File: DatabaseMetaDataUsingInfoSchema.java From FoxTelem with GNU General Public License v3.0

4 votes

@Override
public java.sql.ResultSet getColumnPrivileges(String catalog, String schema, String table, String columnNamePattern) throws SQLException {
    if (catalog == null && this.nullCatalogMeansCurrent) {
        catalog = this.database;
    }

    catalog = this.pedantic ? catalog : StringUtils.unQuoteIdentifier(catalog, this.quotedId);

    StringBuilder sqlBuf = new StringBuilder("SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM, TABLE_NAME,");
    sqlBuf.append(" COLUMN_NAME, NULL AS GRANTOR, GRANTEE, PRIVILEGE_TYPE AS PRIVILEGE, IS_GRANTABLE FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES WHERE");
    if (catalog != null) {
        sqlBuf.append(" TABLE_SCHEMA LIKE ? AND");
    }

    sqlBuf.append(" TABLE_NAME =?");
    if (columnNamePattern != null) {
        sqlBuf.append(" AND COLUMN_NAME LIKE ?");
    }
    sqlBuf.append(" ORDER BY COLUMN_NAME, PRIVILEGE_TYPE");

    java.sql.PreparedStatement pStmt = null;

    try {
        pStmt = prepareMetaDataSafeStatement(sqlBuf.toString());
        int nextId = 1;
        pStmt.setString(nextId++, catalog);
        pStmt.setString(nextId++, table);
        if (columnNamePattern != null) {
            pStmt.setString(nextId, columnNamePattern);
        }

        ResultSet rs = executeMetadataQuery(pStmt);
        ((com.mysql.cj.jdbc.result.ResultSetInternalMethods) rs).getColumnDefinition()
                .setFields(new Field[] { new Field("", "TABLE_CAT", this.getMetadataCollationIndex(), this.getMetadataEncoding(), MysqlType.CHAR, 64),
                        new Field("", "TABLE_SCHEM", this.getMetadataCollationIndex(), this.getMetadataEncoding(), MysqlType.CHAR, 1),
                        new Field("", "TABLE_NAME", this.getMetadataCollationIndex(), this.getMetadataEncoding(), MysqlType.CHAR, 64),
                        new Field("", "COLUMN_NAME", this.getMetadataCollationIndex(), this.getMetadataEncoding(), MysqlType.CHAR, 64),
                        new Field("", "GRANTOR", this.getMetadataCollationIndex(), this.getMetadataEncoding(), MysqlType.CHAR, 77),
                        new Field("", "GRANTEE", this.getMetadataCollationIndex(), this.getMetadataEncoding(), MysqlType.CHAR, 77),
                        new Field("", "PRIVILEGE", this.getMetadataCollationIndex(), this.getMetadataEncoding(), MysqlType.CHAR, 64),
                        new Field("", "IS_GRANTABLE", this.getMetadataCollationIndex(), this.getMetadataEncoding(), MysqlType.CHAR, 3) });

        return rs;
    } finally {
        if (pStmt != null) {
            pStmt.close();
        }
    }
}

Example 2

Source File: DatabaseMetaDataUsingInfoSchema.java From FoxTelem with GNU General Public License v3.0

4 votes

@Override
public java.sql.ResultSet getFunctions(String catalog, String schemaPattern, String functionNamePattern) throws SQLException {

    if (catalog == null && this.nullCatalogMeansCurrent) {
        catalog = this.database;
    }

    catalog = this.pedantic ? catalog : StringUtils.unQuoteIdentifier(catalog, this.quotedId);

    StringBuilder sqlBuf = new StringBuilder(
            "SELECT ROUTINE_SCHEMA AS FUNCTION_CAT, NULL AS FUNCTION_SCHEM, ROUTINE_NAME AS FUNCTION_NAME, ROUTINE_COMMENT AS REMARKS, ");
    sqlBuf.append(getFunctionNoTableConstant());
    sqlBuf.append(" AS FUNCTION_TYPE, ROUTINE_NAME AS SPECIFIC_NAME FROM INFORMATION_SCHEMA.ROUTINES");
    sqlBuf.append(" WHERE ROUTINE_TYPE LIKE 'FUNCTION'");
    if (catalog != null) {
        sqlBuf.append(" AND ROUTINE_SCHEMA LIKE ?");
    }
    if (functionNamePattern != null) {
        sqlBuf.append(" AND ROUTINE_NAME LIKE ?");
    }

    sqlBuf.append(" ORDER BY FUNCTION_CAT, FUNCTION_SCHEM, FUNCTION_NAME, SPECIFIC_NAME");

    java.sql.PreparedStatement pStmt = null;

    try {
        pStmt = prepareMetaDataSafeStatement(sqlBuf.toString());
        int nextId = 1;
        if (catalog != null) {
            pStmt.setString(nextId++, catalog);
        }
        if (functionNamePattern != null) {
            pStmt.setString(nextId, functionNamePattern);
        }

        ResultSet rs = executeMetadataQuery(pStmt);
        ((com.mysql.cj.jdbc.result.ResultSetInternalMethods) rs).getColumnDefinition()
                .setFields(new Field[] { new Field("", "FUNCTION_CAT", this.getMetadataCollationIndex(), this.getMetadataEncoding(), MysqlType.CHAR, 255),
                        new Field("", "FUNCTION_SCHEM", this.getMetadataCollationIndex(), this.getMetadataEncoding(), MysqlType.CHAR, 255),
                        new Field("", "FUNCTION_NAME", this.getMetadataCollationIndex(), this.getMetadataEncoding(), MysqlType.CHAR, 255),
                        new Field("", "REMARKS", this.getMetadataCollationIndex(), this.getMetadataEncoding(), MysqlType.CHAR, 255),
                        new Field("", "FUNCTION_TYPE", this.getMetadataCollationIndex(), this.getMetadataEncoding(), MysqlType.SMALLINT, 6),
                        new Field("", "SPECIFIC_NAME", this.getMetadataCollationIndex(), this.getMetadataEncoding(), MysqlType.CHAR, 255) });

        return rs;
    } finally {
        if (pStmt != null) {
            pStmt.close();
        }
    }
}

Example 3

Source File: DatabaseMetaDataUsingInfoSchema.java From FoxTelem with GNU General Public License v3.0

4 votes

@Override
public ResultSet getVersionColumns(String catalog, String schema, String table) throws SQLException {

    if (catalog == null && this.nullCatalogMeansCurrent) {
        catalog = this.database;
    }

    if (table == null) {
        throw SQLError.createSQLException(Messages.getString("DatabaseMetaData.2"), MysqlErrorNumbers.SQL_STATE_ILLEGAL_ARGUMENT,
                getExceptionInterceptor());
    }

    catalog = this.pedantic ? catalog : StringUtils.unQuoteIdentifier(catalog, this.quotedId);

    StringBuilder sqlBuf = new StringBuilder("SELECT NULL AS SCOPE, COLUMN_NAME, ");
    appendJdbcTypeMappingQuery(sqlBuf, "DATA_TYPE", "COLUMN_TYPE");
    sqlBuf.append(" AS DATA_TYPE, UPPER(COLUMN_TYPE) AS TYPE_NAME,");
    sqlBuf.append(" CASE WHEN LCASE(DATA_TYPE)='date' THEN 10 WHEN LCASE(DATA_TYPE)='time' THEN 8");
    sqlBuf.append(" WHEN LCASE(DATA_TYPE)='datetime' THEN 19 WHEN LCASE(DATA_TYPE)='timestamp' THEN 19");
    sqlBuf.append(" WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN NUMERIC_PRECISION WHEN CHARACTER_MAXIMUM_LENGTH > ");
    sqlBuf.append(Integer.MAX_VALUE);
    sqlBuf.append(" THEN ");
    sqlBuf.append(Integer.MAX_VALUE);
    sqlBuf.append(" ELSE CHARACTER_MAXIMUM_LENGTH END AS COLUMN_SIZE, ");
    sqlBuf.append(maxBufferSize);
    sqlBuf.append(" AS BUFFER_LENGTH,NUMERIC_SCALE AS DECIMAL_DIGITS, ");
    sqlBuf.append(Integer.toString(java.sql.DatabaseMetaData.versionColumnNotPseudo));
    sqlBuf.append(" AS PSEUDO_COLUMN FROM INFORMATION_SCHEMA.COLUMNS WHERE");
    if (catalog != null) {
        sqlBuf.append(" TABLE_SCHEMA LIKE ? AND");
    }
    sqlBuf.append(" TABLE_NAME LIKE ?");
    sqlBuf.append(" AND EXTRA LIKE '%on update CURRENT_TIMESTAMP%'");

    java.sql.PreparedStatement pStmt = null;

    try {
        pStmt = prepareMetaDataSafeStatement(sqlBuf.toString());
        int nextId = 1;
        if (catalog != null) {
            pStmt.setString(nextId++, catalog);
        }
        pStmt.setString(nextId, table);

        ResultSet rs = executeMetadataQuery(pStmt);
        ((com.mysql.cj.jdbc.result.ResultSetInternalMethods) rs).getColumnDefinition()
                .setFields(new Field[] { new Field("", "SCOPE", this.getMetadataCollationIndex(), this.getMetadataEncoding(), MysqlType.SMALLINT, 5),
                        new Field("", "COLUMN_NAME", this.getMetadataCollationIndex(), this.getMetadataEncoding(), MysqlType.CHAR, 32),
                        new Field("", "DATA_TYPE", this.getMetadataCollationIndex(), this.getMetadataEncoding(), MysqlType.INT, 5),
                        new Field("", "TYPE_NAME", this.getMetadataCollationIndex(), this.getMetadataEncoding(), MysqlType.CHAR, 16),
                        new Field("", "COLUMN_SIZE", this.getMetadataCollationIndex(), this.getMetadataEncoding(), MysqlType.INT, 16),
                        new Field("", "BUFFER_LENGTH", this.getMetadataCollationIndex(), this.getMetadataEncoding(), MysqlType.INT, 16),
                        new Field("", "DECIMAL_DIGITS", this.getMetadataCollationIndex(), this.getMetadataEncoding(), MysqlType.SMALLINT, 16),
                        new Field("", "PSEUDO_COLUMN", this.getMetadataCollationIndex(), this.getMetadataEncoding(), MysqlType.SMALLINT, 5) });

        return rs;
    } finally {
        if (pStmt != null) {
            pStmt.close();
        }
    }
}

Example 4

Source File: DatabaseMetaDataUsingInfoSchema.java From FoxTelem with GNU General Public License v3.0

4 votes

@Override
public ResultSet getTables(String catalog, String schemaPattern, String tableNamePattern, String[] types) throws SQLException {
    if (catalog == null && this.nullCatalogMeansCurrent) {
        catalog = this.database;
    }

    catalog = this.pedantic ? catalog : StringUtils.unQuoteIdentifier(catalog, this.quotedId);

    if (tableNamePattern != null) {
        List<String> parseList = StringUtils.splitDBdotName(tableNamePattern, catalog, this.quotedId,
                this.session.getServerSession().isNoBackslashEscapesSet());
        //There *should* be 2 rows, if any.
        if (parseList.size() == 2) {
            tableNamePattern = parseList.get(1);
        }
    }

    java.sql.PreparedStatement pStmt = null;

    StringBuilder sqlBuf = new StringBuilder("SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM, TABLE_NAME, ");
    sqlBuf.append("CASE WHEN TABLE_TYPE='BASE TABLE' THEN CASE WHEN TABLE_SCHEMA = 'mysql' OR TABLE_SCHEMA = 'performance_schema' THEN 'SYSTEM TABLE' ");
    sqlBuf.append("ELSE 'TABLE' END WHEN TABLE_TYPE='TEMPORARY' THEN 'LOCAL_TEMPORARY' ELSE TABLE_TYPE END AS TABLE_TYPE, ");
    sqlBuf.append("TABLE_COMMENT AS REMARKS, NULL AS TYPE_CAT, NULL AS TYPE_SCHEM, NULL AS TYPE_NAME, NULL AS SELF_REFERENCING_COL_NAME, ");
    sqlBuf.append("NULL AS REF_GENERATION FROM INFORMATION_SCHEMA.TABLES");

    if (catalog != null || tableNamePattern != null) {
        sqlBuf.append(" WHERE");
    }

    if (catalog != null) {
        sqlBuf.append("information_schema".equalsIgnoreCase(catalog) || "performance_schema".equalsIgnoreCase(catalog) || !StringUtils.hasWildcards(catalog)
                ? " TABLE_SCHEMA = ?" : " TABLE_SCHEMA LIKE ?");
    }

    if (tableNamePattern != null) {
        if (catalog != null) {
            sqlBuf.append(" AND");
        }
        sqlBuf.append(StringUtils.hasWildcards(tableNamePattern) ? " TABLE_NAME LIKE ?" : " TABLE_NAME = ?");
    }
    if (types != null && types.length > 0) {
        sqlBuf.append(" HAVING TABLE_TYPE IN (?,?,?,?,?)");
    }
    sqlBuf.append(" ORDER BY TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME");
    try {
        pStmt = prepareMetaDataSafeStatement(sqlBuf.toString());

        int nextId = 1;
        if (catalog != null) {
            pStmt.setString(nextId++, catalog != null ? catalog : "%");
        }
        if (tableNamePattern != null) {
            pStmt.setString(nextId++, tableNamePattern);
        }

        if (types != null && types.length > 0) {
            for (int i = 0; i < 5; i++) {
                pStmt.setNull(nextId + i, MysqlType.VARCHAR.getJdbcType());
            }
            for (int i = 0; i < types.length; i++) {
                TableType tableType = TableType.getTableTypeEqualTo(types[i]);
                if (tableType != TableType.UNKNOWN) {
                    pStmt.setString(nextId++, tableType.getName());
                }
            }
        }

        ResultSet rs = executeMetadataQuery(pStmt);

        ((com.mysql.cj.jdbc.result.ResultSetInternalMethods) rs).setColumnDefinition(createTablesFields());

        return rs;
    } finally {
        if (pStmt != null) {
            pStmt.close();
        }
    }
}

Example 5

Source File: DatabaseMetaDataUsingInfoSchema.java From FoxTelem with GNU General Public License v3.0

4 votes

@Override
public ResultSet getProcedures(String catalog, String schemaPattern, String procedureNamePattern) throws SQLException {

    if (catalog == null && this.nullCatalogMeansCurrent) {
        catalog = this.database;
    }

    catalog = this.pedantic ? catalog : StringUtils.unQuoteIdentifier(catalog, this.quotedId);

    StringBuilder sqlBuf = new StringBuilder(
            "SELECT ROUTINE_SCHEMA AS PROCEDURE_CAT, NULL AS PROCEDURE_SCHEM, ROUTINE_NAME AS PROCEDURE_NAME, NULL AS RESERVED_1,");
    sqlBuf.append(" NULL AS RESERVED_2, NULL AS RESERVED_3, ROUTINE_COMMENT AS REMARKS, CASE WHEN ROUTINE_TYPE = 'PROCEDURE' THEN ");
    sqlBuf.append(procedureNoResult);
    sqlBuf.append(" WHEN ROUTINE_TYPE='FUNCTION' THEN ");
    sqlBuf.append(procedureReturnsResult);
    sqlBuf.append(" ELSE ");
    sqlBuf.append(procedureResultUnknown);
    sqlBuf.append(" END AS PROCEDURE_TYPE, ROUTINE_NAME AS SPECIFIC_NAME FROM INFORMATION_SCHEMA.ROUTINES");

    StringBuilder conditionBuf = new StringBuilder();
    if (!this.conn.getPropertySet().getBooleanProperty(PropertyKey.getProceduresReturnsFunctions).getValue()) {
        conditionBuf.append(" ROUTINE_TYPE = 'PROCEDURE'");
    }
    if (catalog != null) {
        if (conditionBuf.length() > 0) {
            conditionBuf.append(" AND");
        }
        conditionBuf.append(" ROUTINE_SCHEMA LIKE ?");
    }
    if (procedureNamePattern != null) {
        if (conditionBuf.length() > 0) {
            conditionBuf.append(" AND");
        }
        conditionBuf.append(" ROUTINE_NAME LIKE ?");
    }

    if (conditionBuf.length() > 0) {
        sqlBuf.append(" WHERE");
        sqlBuf.append(conditionBuf);
    }
    sqlBuf.append(" ORDER BY ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE");

    java.sql.PreparedStatement pStmt = null;

    try {
        pStmt = prepareMetaDataSafeStatement(sqlBuf.toString());
        int nextId = 1;
        if (catalog != null) {
            pStmt.setString(nextId++, catalog);
        }
        if (procedureNamePattern != null) {
            pStmt.setString(nextId, procedureNamePattern);
        }

        ResultSet rs = executeMetadataQuery(pStmt);
        ((com.mysql.cj.jdbc.result.ResultSetInternalMethods) rs).getColumnDefinition().setFields(createFieldMetadataForGetProcedures());

        return rs;
    } finally {
        if (pStmt != null) {
            pStmt.close();
        }
    }
}

Example 6

Source File: DatabaseMetaDataUsingInfoSchema.java From FoxTelem with GNU General Public License v3.0

4 votes

@Override
public java.sql.ResultSet getPrimaryKeys(String catalog, String schema, String table) throws SQLException {

    if (catalog == null && this.nullCatalogMeansCurrent) {
        catalog = this.database;
    }

    if (table == null) {
        throw SQLError.createSQLException(Messages.getString("DatabaseMetaData.2"), MysqlErrorNumbers.SQL_STATE_ILLEGAL_ARGUMENT,
                getExceptionInterceptor());
    }

    catalog = this.pedantic ? catalog : StringUtils.unQuoteIdentifier(catalog, this.quotedId);

    StringBuilder sqlBuf = new StringBuilder("SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM, TABLE_NAME,");
    sqlBuf.append(" COLUMN_NAME, SEQ_IN_INDEX AS KEY_SEQ, 'PRIMARY' AS PK_NAME FROM INFORMATION_SCHEMA.STATISTICS WHERE");
    if (catalog != null) {
        sqlBuf.append(" TABLE_SCHEMA LIKE ? AND");
    }
    sqlBuf.append(" TABLE_NAME LIKE ?");
    sqlBuf.append(" AND INDEX_NAME='PRIMARY' ORDER BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX");

    java.sql.PreparedStatement pStmt = null;

    try {
        pStmt = prepareMetaDataSafeStatement(sqlBuf.toString());
        int nextId = 1;
        if (catalog != null) {
            pStmt.setString(nextId++, catalog);
        }
        pStmt.setString(nextId, table);

        ResultSet rs = executeMetadataQuery(pStmt);
        ((com.mysql.cj.jdbc.result.ResultSetInternalMethods) rs).getColumnDefinition()
                .setFields(new Field[] { new Field("", "TABLE_CAT", this.getMetadataCollationIndex(), this.getMetadataEncoding(), MysqlType.CHAR, 255),
                        new Field("", "TABLE_SCHEM", this.getMetadataCollationIndex(), this.getMetadataEncoding(), MysqlType.CHAR, 0),
                        new Field("", "TABLE_NAME", this.getMetadataCollationIndex(), this.getMetadataEncoding(), MysqlType.CHAR, 255),
                        new Field("", "COLUMN_NAME", this.getMetadataCollationIndex(), this.getMetadataEncoding(), MysqlType.CHAR, 32),
                        new Field("", "KEY_SEQ", this.getMetadataCollationIndex(), this.getMetadataEncoding(), MysqlType.SMALLINT, 5),
                        new Field("", "PK_NAME", this.getMetadataCollationIndex(), this.getMetadataEncoding(), MysqlType.CHAR, 32) });

        return rs;
    } finally {
        if (pStmt != null) {
            pStmt.close();
        }
    }
}

Example 7

Source File: DatabaseMetaDataUsingInfoSchema.java From FoxTelem with GNU General Public License v3.0

4 votes

@Override
public ResultSet getIndexInfo(String catalog, String schema, String table, boolean unique, boolean approximate) throws SQLException {
    if (catalog == null && this.nullCatalogMeansCurrent) {
        catalog = this.database;
    }

    catalog = this.pedantic ? catalog : StringUtils.unQuoteIdentifier(catalog, this.quotedId);

    StringBuilder sqlBuf = new StringBuilder("SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM, TABLE_NAME, NON_UNIQUE,");
    sqlBuf.append("TABLE_SCHEMA AS INDEX_QUALIFIER, INDEX_NAME,");
    sqlBuf.append(tableIndexOther);
    sqlBuf.append(" AS TYPE, SEQ_IN_INDEX AS ORDINAL_POSITION, COLUMN_NAME,");
    sqlBuf.append("COLLATION AS ASC_OR_DESC, CARDINALITY, NULL AS PAGES, NULL AS FILTER_CONDITION FROM INFORMATION_SCHEMA.STATISTICS WHERE");
    if (catalog != null) {
        sqlBuf.append(" TABLE_SCHEMA LIKE ? AND");
    }
    sqlBuf.append(" TABLE_NAME LIKE ?");

    if (unique) {
        sqlBuf.append(" AND NON_UNIQUE=0 ");
    }
    sqlBuf.append("ORDER BY NON_UNIQUE, INDEX_NAME, SEQ_IN_INDEX");

    java.sql.PreparedStatement pStmt = null;

    try {

        pStmt = prepareMetaDataSafeStatement(sqlBuf.toString());
        int nextId = 1;
        if (catalog != null) {
            pStmt.setString(nextId++, catalog);
        }
        pStmt.setString(nextId, table);

        ResultSet rs = executeMetadataQuery(pStmt);

        ((com.mysql.cj.jdbc.result.ResultSetInternalMethods) rs).getColumnDefinition().setFields(createIndexInfoFields());

        return rs;
    } finally {
        if (pStmt != null) {
            pStmt.close();
        }
    }
}

Example 8

Source File: DatabaseMetaDataUsingInfoSchema.java From FoxTelem with GNU General Public License v3.0

4 votes

@Override
public java.sql.ResultSet getImportedKeys(String catalog, String schema, String table) throws SQLException {
    if (table == null) {
        throw SQLError.createSQLException(Messages.getString("DatabaseMetaData.2"), MysqlErrorNumbers.SQL_STATE_ILLEGAL_ARGUMENT,
                getExceptionInterceptor());
    }

    if (catalog == null && this.nullCatalogMeansCurrent) {
        catalog = this.database;
    }

    catalog = this.pedantic ? catalog : StringUtils.unQuoteIdentifier(catalog, this.quotedId);

    StringBuilder sqlBuf = new StringBuilder("SELECT A.REFERENCED_TABLE_SCHEMA AS PKTABLE_CAT, NULL AS PKTABLE_SCHEM,");
    sqlBuf.append(" A.REFERENCED_TABLE_NAME AS PKTABLE_NAME, A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME, A.TABLE_SCHEMA AS FKTABLE_CAT,");
    sqlBuf.append(" NULL AS FKTABLE_SCHEM, A.TABLE_NAME AS FKTABLE_NAME, A.COLUMN_NAME AS FKCOLUMN_NAME, A.ORDINAL_POSITION AS KEY_SEQ,");
    sqlBuf.append(generateUpdateRuleClause());
    sqlBuf.append(" AS UPDATE_RULE,");
    sqlBuf.append(generateDeleteRuleClause());
    sqlBuf.append(" AS DELETE_RULE, A.CONSTRAINT_NAME AS FK_NAME, (SELECT CONSTRAINT_NAME FROM");
    sqlBuf.append(" INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = A.REFERENCED_TABLE_SCHEMA AND");
    sqlBuf.append(" TABLE_NAME = A.REFERENCED_TABLE_NAME AND CONSTRAINT_TYPE IN ('UNIQUE','PRIMARY KEY') LIMIT 1) AS PK_NAME,");
    sqlBuf.append(importedKeyNotDeferrable);
    sqlBuf.append(" AS DEFERRABILITY FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A");
    sqlBuf.append(" JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS B USING (CONSTRAINT_NAME, TABLE_NAME) ");
    sqlBuf.append(generateOptionalRefContraintsJoin());
    sqlBuf.append("WHERE B.CONSTRAINT_TYPE = 'FOREIGN KEY'");
    if (catalog != null) {
        sqlBuf.append(" AND A.TABLE_SCHEMA LIKE ?");
    }
    sqlBuf.append(" AND A.TABLE_NAME=?");
    sqlBuf.append(" AND A.REFERENCED_TABLE_SCHEMA IS NOT NULL");
    sqlBuf.append(" ORDER BY A.REFERENCED_TABLE_SCHEMA, A.REFERENCED_TABLE_NAME, A.ORDINAL_POSITION");

    java.sql.PreparedStatement pStmt = null;

    try {
        pStmt = prepareMetaDataSafeStatement(sqlBuf.toString());
        int nextId = 1;
        if (catalog != null) {
            pStmt.setString(nextId++, catalog);
        }
        pStmt.setString(nextId, table);

        ResultSet rs = executeMetadataQuery(pStmt);

        ((com.mysql.cj.jdbc.result.ResultSetInternalMethods) rs).getColumnDefinition().setFields(createFkMetadataFields());

        return rs;
    } finally {
        if (pStmt != null) {
            pStmt.close();
        }
    }
}

Example 9

Source File: DatabaseMetaDataUsingInfoSchema.java From FoxTelem with GNU General Public License v3.0

4 votes

@Override
public java.sql.ResultSet getExportedKeys(String catalog, String schema, String table) throws SQLException {
    // TODO: Can't determine actions using INFORMATION_SCHEMA yet...

    if (table == null) {
        throw SQLError.createSQLException(Messages.getString("DatabaseMetaData.2"), MysqlErrorNumbers.SQL_STATE_ILLEGAL_ARGUMENT,
                getExceptionInterceptor());
    }

    if (catalog == null && this.nullCatalogMeansCurrent) {
        catalog = this.database;
    }

    catalog = this.pedantic ? catalog : StringUtils.unQuoteIdentifier(catalog, this.quotedId);

    //CASCADE, SET NULL, SET DEFAULT, RESTRICT, NO ACTION

    StringBuilder sqlBuf = new StringBuilder(
            "SELECT A.REFERENCED_TABLE_SCHEMA AS PKTABLE_CAT, NULL AS PKTABLE_SCHEM, A.REFERENCED_TABLE_NAME AS PKTABLE_NAME,");
    sqlBuf.append(" A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME, A.TABLE_SCHEMA AS FKTABLE_CAT, NULL AS FKTABLE_SCHEM, A.TABLE_NAME AS FKTABLE_NAME,");
    sqlBuf.append(" A.COLUMN_NAME AS FKCOLUMN_NAME, A.ORDINAL_POSITION AS KEY_SEQ,");
    sqlBuf.append(generateUpdateRuleClause());
    sqlBuf.append(" AS UPDATE_RULE,");
    sqlBuf.append(generateDeleteRuleClause());
    sqlBuf.append(" AS DELETE_RULE, A.CONSTRAINT_NAME AS FK_NAME, (SELECT CONSTRAINT_NAME FROM");
    sqlBuf.append(" INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = A.REFERENCED_TABLE_SCHEMA AND");
    sqlBuf.append(" TABLE_NAME = A.REFERENCED_TABLE_NAME AND CONSTRAINT_TYPE IN ('UNIQUE','PRIMARY KEY') LIMIT 1) AS PK_NAME,");
    sqlBuf.append(importedKeyNotDeferrable);
    sqlBuf.append(" AS DEFERRABILITY FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A");
    sqlBuf.append(" JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS B USING (TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_NAME) ");
    sqlBuf.append(generateOptionalRefContraintsJoin());
    sqlBuf.append(" WHERE B.CONSTRAINT_TYPE = 'FOREIGN KEY'");
    if (catalog != null) {
        sqlBuf.append(" AND A.REFERENCED_TABLE_SCHEMA LIKE ?");
    }
    sqlBuf.append(" AND A.REFERENCED_TABLE_NAME=?");
    sqlBuf.append(" ORDER BY A.TABLE_SCHEMA, A.TABLE_NAME, A.ORDINAL_POSITION");

    java.sql.PreparedStatement pStmt = null;

    try {
        pStmt = prepareMetaDataSafeStatement(sqlBuf.toString());
        int nextId = 1;

        if (catalog != null) {
            pStmt.setString(nextId++, catalog);
        }
        pStmt.setString(nextId, table);

        ResultSet rs = executeMetadataQuery(pStmt);

        ((com.mysql.cj.jdbc.result.ResultSetInternalMethods) rs).getColumnDefinition().setFields(createFkMetadataFields());

        return rs;
    } finally {
        if (pStmt != null) {
            pStmt.close();
        }
    }

}

Example 10

Source File: DatabaseMetaDataUsingInfoSchema.java From FoxTelem with GNU General Public License v3.0

4 votes

@Override
public java.sql.ResultSet getCrossReference(String primaryCatalog, String primarySchema, String primaryTable, String foreignCatalog, String foreignSchema,
        String foreignTable) throws SQLException {
    if (primaryTable == null) {
        throw SQLError.createSQLException(Messages.getString("DatabaseMetaData.2"), MysqlErrorNumbers.SQL_STATE_ILLEGAL_ARGUMENT,
                getExceptionInterceptor());
    }

    if (primaryCatalog == null && this.nullCatalogMeansCurrent) {
        primaryCatalog = this.database;
    }

    if (foreignCatalog == null && this.nullCatalogMeansCurrent) {
        foreignCatalog = this.database;
    }

    primaryCatalog = this.pedantic ? primaryCatalog : StringUtils.unQuoteIdentifier(primaryCatalog, this.quotedId);
    foreignCatalog = this.pedantic ? foreignCatalog : StringUtils.unQuoteIdentifier(foreignCatalog, this.quotedId);

    StringBuilder sqlBuf = new StringBuilder(
            "SELECT A.REFERENCED_TABLE_SCHEMA AS PKTABLE_CAT,NULL AS PKTABLE_SCHEM, A.REFERENCED_TABLE_NAME AS PKTABLE_NAME,");
    sqlBuf.append(" A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME, A.TABLE_SCHEMA AS FKTABLE_CAT, NULL AS FKTABLE_SCHEM,");
    sqlBuf.append(" A.TABLE_NAME AS FKTABLE_NAME, A.COLUMN_NAME AS FKCOLUMN_NAME, A.ORDINAL_POSITION AS KEY_SEQ,");
    sqlBuf.append(generateUpdateRuleClause());
    sqlBuf.append(" AS UPDATE_RULE,");
    sqlBuf.append(generateDeleteRuleClause());
    sqlBuf.append(" AS DELETE_RULE, A.CONSTRAINT_NAME AS FK_NAME,");
    sqlBuf.append(" (SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = A.REFERENCED_TABLE_SCHEMA");
    sqlBuf.append(" AND TABLE_NAME = A.REFERENCED_TABLE_NAME AND CONSTRAINT_TYPE IN ('UNIQUE','PRIMARY KEY') LIMIT 1) AS PK_NAME, ");
    sqlBuf.append(importedKeyNotDeferrable);
    sqlBuf.append(" AS DEFERRABILITY FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE A");
    sqlBuf.append(" JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS B USING (TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_NAME) ");
    sqlBuf.append(generateOptionalRefContraintsJoin());
    sqlBuf.append("WHERE B.CONSTRAINT_TYPE = 'FOREIGN KEY'");
    if (primaryCatalog != null) {
        sqlBuf.append(" AND A.REFERENCED_TABLE_SCHEMA LIKE ?");
    }
    sqlBuf.append(" AND A.REFERENCED_TABLE_NAME=?");
    if (foreignCatalog != null) {
        sqlBuf.append(" AND A.TABLE_SCHEMA LIKE ?");
    }
    sqlBuf.append(" AND A.TABLE_NAME=?");
    sqlBuf.append(" ORDER BY A.TABLE_SCHEMA, A.TABLE_NAME, A.ORDINAL_POSITION");

    java.sql.PreparedStatement pStmt = null;

    try {
        pStmt = prepareMetaDataSafeStatement(sqlBuf.toString());
        int nextId = 1;
        if (primaryCatalog != null) {
            pStmt.setString(nextId++, primaryCatalog);
        }
        pStmt.setString(nextId++, primaryTable);
        if (foreignCatalog != null) {
            pStmt.setString(nextId++, foreignCatalog);
        }
        pStmt.setString(nextId, foreignTable);

        ResultSet rs = executeMetadataQuery(pStmt);
        ((com.mysql.cj.jdbc.result.ResultSetInternalMethods) rs).getColumnDefinition().setFields(createFkMetadataFields());

        return rs;
    } finally {
        if (pStmt != null) {
            pStmt.close();
        }
    }
}

Example 11