Example usage for java.sql ResultSetMetaData getTableName

Introduction

In this page you can find the example usage for java.sql ResultSetMetaData getTableName.

Prototype

String getTableName(int column) throws SQLException;

Source Link

Document

Gets the designated column's table name.

Usage

From source file:com.nextep.designer.sqlclient.ui.helpers.SQLHelper.java

public static INextepMetadata createOfflineMetadata(ResultSetMetaData md, String sqlQuery) throws SQLException {
    final NextepResultSetMetaData nmd = new NextepResultSetMetaData();
    DMLParseResult parseResult = null;/*from   ww w. ja v a2 s  .  c o  m*/
    nmd.setColumnCount(md.getColumnCount());
    for (int i = 1; i <= md.getColumnCount(); i++) {
        nmd.setColumnName(i, md.getColumnName(i));
        nmd.setColumnType(i, md.getColumnType(i));
        // Fetching tablename from driver
        String tableName = md.getTableName(i);
        // If not available we try to parse ourselves
        if (tableName == null || "".equals(tableName)) {
            // Parsing
            if (parseResult == null) {
                try {
                    parseResult = parseSQL(sqlQuery, 1);
                } catch (RuntimeException e) {
                    LOGGER.error("Error while parsing SQL : " + e.getMessage(), e);
                }
            }
            // Only providing name on single table select
            if (parseResult != null && parseResult.getFromTables().size() == 1) {
                tableName = parseResult.getFromTables().iterator().next().getTableName();
            }
        }
        nmd.setTableName(i, tableName);
    }
    return nmd;
}

---

From source file:org.apache.nifi.processors.standard.util.JdbcCommon.java

/**
 * Creates an Avro schema from a result set. If the table/record name is known a priori and provided, use that as a
 * fallback for the record name if it cannot be retrieved from the result set, and finally fall back to a default value.
 *
 * @param rs         The result set to convert to Avro
 * @param recordName The a priori record name to use if it cannot be determined from the result set.
 * @return A Schema object representing the result set converted to an Avro record
 * @throws SQLException if any error occurs during conversion
 *//*from ww  w. j a va 2s  . co  m*/
public static Schema createSchema(final ResultSet rs, String recordName, boolean convertNames)
        throws SQLException {
    final ResultSetMetaData meta = rs.getMetaData();
    final int nrOfColumns = meta.getColumnCount();
    String tableName = StringUtils.isEmpty(recordName) ? "NiFi_ExecuteSQL_Record" : recordName;
    if (nrOfColumns > 0) {
        String tableNameFromMeta = meta.getTableName(1);
        if (!StringUtils.isBlank(tableNameFromMeta)) {
            tableName = tableNameFromMeta;
        }
    }

    if (convertNames) {
        tableName = normalizeNameForAvro(tableName);
    }

    final FieldAssembler<Schema> builder = SchemaBuilder.record(tableName).namespace("any.data").fields();

    /**
     * Some missing Avro types - Decimal, Date types. May need some additional work.
     */
    for (int i = 1; i <= nrOfColumns; i++) {
        /**
        *   as per jdbc 4 specs, getColumnLabel will have the alias for the column, if not it will have the column name.
        *  so it may be a better option to check for columnlabel first and if in case it is null is someimplementation,
        *  check for alias. Postgres is the one that has the null column names for calculated fields.
        */
        String nameOrLabel = StringUtils.isNotEmpty(meta.getColumnLabel(i)) ? meta.getColumnLabel(i)
                : meta.getColumnName(i);
        String columnName = convertNames ? normalizeNameForAvro(nameOrLabel) : nameOrLabel;
        switch (meta.getColumnType(i)) {
        case CHAR:
        case LONGNVARCHAR:
        case LONGVARCHAR:
        case NCHAR:
        case NVARCHAR:
        case VARCHAR:
        case CLOB:
            builder.name(columnName).type().unionOf().nullBuilder().endNull().and().stringType().endUnion()
                    .noDefault();
            break;

        case BIT:
        case BOOLEAN:
            builder.name(columnName).type().unionOf().nullBuilder().endNull().and().booleanType().endUnion()
                    .noDefault();
            break;

        case INTEGER:
            if (meta.isSigned(i) || (meta.getPrecision(i) > 0 && meta.getPrecision(i) <= MAX_DIGITS_IN_INT)) {
                builder.name(columnName).type().unionOf().nullBuilder().endNull().and().intType().endUnion()
                        .noDefault();
            } else {
                builder.name(columnName).type().unionOf().nullBuilder().endNull().and().longType().endUnion()
                        .noDefault();
            }
            break;

        case SMALLINT:
        case TINYINT:
            builder.name(columnName).type().unionOf().nullBuilder().endNull().and().intType().endUnion()
                    .noDefault();
            break;

        case BIGINT:
            // Check the precision of the BIGINT. Some databases allow arbitrary precision (> 19), but Avro won't handle that.
            // If the precision > 19 (or is negative), use a string for the type, otherwise use a long. The object(s) will be converted
            // to strings as necessary
            int precision = meta.getPrecision(i);
            if (precision < 0 || precision > MAX_DIGITS_IN_BIGINT) {
                builder.name(columnName).type().unionOf().nullBuilder().endNull().and().stringType().endUnion()
                        .noDefault();
            } else {
                builder.name(columnName).type().unionOf().nullBuilder().endNull().and().longType().endUnion()
                        .noDefault();
            }
            break;

        // java.sql.RowId is interface, is seems to be database
        // implementation specific, let's convert to String
        case ROWID:
            builder.name(columnName).type().unionOf().nullBuilder().endNull().and().stringType().endUnion()
                    .noDefault();
            break;

        case FLOAT:
        case REAL:
            builder.name(columnName).type().unionOf().nullBuilder().endNull().and().floatType().endUnion()
                    .noDefault();
            break;

        case DOUBLE:
            builder.name(columnName).type().unionOf().nullBuilder().endNull().and().doubleType().endUnion()
                    .noDefault();
            break;

        // Did not find direct suitable type, need to be clarified!!!!
        case DECIMAL:
        case NUMERIC:
            builder.name(columnName).type().unionOf().nullBuilder().endNull().and().stringType().endUnion()
                    .noDefault();
            break;

        // Did not find direct suitable type, need to be clarified!!!!
        case DATE:
        case TIME:
        case TIMESTAMP:
            builder.name(columnName).type().unionOf().nullBuilder().endNull().and().stringType().endUnion()
                    .noDefault();
            break;

        case BINARY:
        case VARBINARY:
        case LONGVARBINARY:
        case ARRAY:
        case BLOB:
            builder.name(columnName).type().unionOf().nullBuilder().endNull().and().bytesType().endUnion()
                    .noDefault();
            break;

        default:
            throw new IllegalArgumentException("createSchema: Unknown SQL type " + meta.getColumnType(i) + " / "
                    + meta.getColumnTypeName(i) + " (table: " + tableName + ", column: " + columnName
                    + ") cannot be converted to Avro type");
        }
    }

    return builder.endRecord();
}

---

From source file:be.dataminded.nifi.plugins.util.JdbcCommon.java

/**
 * Creates an Avro schema from a result set. If the table/record name is known a priori and provided, use that as a
 * fallback for the record name if it cannot be retrieved from the result set, and finally fall back to a default value.
 *
 * @param rs         The result set to convert to Avro
 * @param recordName The a priori record name to use if it cannot be determined from the result set.
 * @return A Schema object representing the result set converted to an Avro record
 * @throws SQLException if any error occurs during conversion
 *///w w  w. j  a  va2 s . co  m
public static Schema createSchema(final ResultSet rs, String recordName, boolean convertNames)
        throws SQLException {
    final ResultSetMetaData meta = rs.getMetaData();
    final int nrOfColumns = meta.getColumnCount();
    String tableName = StringUtils.isEmpty(recordName) ? "NiFi_ExecuteSQL_Record" : recordName;
    if (nrOfColumns > 0) {
        String tableNameFromMeta = meta.getTableName(1);
        if (!StringUtils.isBlank(tableNameFromMeta)) {
            tableName = tableNameFromMeta;
        }
    }

    if (convertNames) {
        tableName = normalizeNameForAvro(tableName);
    }

    final FieldAssembler<Schema> builder = SchemaBuilder.record(tableName).namespace("any.data").fields();

    /**
     * Some missing Avro types - Decimal, Date types. May need some additional work.
     */
    for (int i = 1; i <= nrOfColumns; i++) {
        /**
        *   as per jdbc 4 specs, getColumnLabel will have the alias for the column, if not it will have the column name.
        *  so it may be a better option to check for columnlabel first and if in case it is null is someimplementation,
        *  check for alias. Postgres is the one that has the null column names for calculated fields.
        */
        String nameOrLabel = StringUtils.isNotEmpty(meta.getColumnLabel(i)) ? meta.getColumnLabel(i)
                : meta.getColumnName(i);
        String columnName = convertNames ? normalizeNameForAvro(nameOrLabel) : nameOrLabel;
        switch (meta.getColumnType(i)) {
        case CHAR:
        case LONGNVARCHAR:
        case LONGVARCHAR:
        case NCHAR:
        case NVARCHAR:
        case VARCHAR:
        case CLOB:
            builder.name(columnName).type().unionOf().nullBuilder().endNull().and().stringType().endUnion()
                    .noDefault();
            break;

        case BIT:
        case BOOLEAN:
            builder.name(columnName).type().unionOf().nullBuilder().endNull().and().booleanType().endUnion()
                    .noDefault();
            break;

        case INTEGER:
            if (meta.isSigned(i)) {
                builder.name(columnName).type().unionOf().nullBuilder().endNull().and().intType().endUnion()
                        .noDefault();
            } else {
                builder.name(columnName).type().unionOf().nullBuilder().endNull().and().longType().endUnion()
                        .noDefault();
            }
            break;

        case SMALLINT:
        case TINYINT:
            builder.name(columnName).type().unionOf().nullBuilder().endNull().and().intType().endUnion()
                    .noDefault();
            break;

        case BIGINT:
            // Check the precision of the BIGINT. Some databases allow arbitrary precision (> 19), but Avro won't handle that.
            // If the precision > 19 (or is negative), use a string for the type, otherwise use a long. The object(s) will be converted
            // to strings as necessary
            int precision = meta.getPrecision(i);
            if (precision < 0 || precision > MAX_DIGITS_IN_BIGINT) {
                builder.name(columnName).type().unionOf().nullBuilder().endNull().and().stringType().endUnion()
                        .noDefault();
            } else {
                builder.name(columnName).type().unionOf().nullBuilder().endNull().and().longType().endUnion()
                        .noDefault();
            }
            break;

        // java.sql.RowId is interface, is seems to be database
        // implementation specific, let's convert to String
        case ROWID:
            builder.name(columnName).type().unionOf().nullBuilder().endNull().and().stringType().endUnion()
                    .noDefault();
            break;

        case FLOAT:
        case REAL:
            builder.name(columnName).type().unionOf().nullBuilder().endNull().and().floatType().endUnion()
                    .noDefault();
            break;

        case DOUBLE:
            builder.name(columnName).type().unionOf().nullBuilder().endNull().and().doubleType().endUnion()
                    .noDefault();
            break;

        // Did not find direct suitable type, need to be clarified!!!!
        case DECIMAL:
        case NUMERIC:
            int scale = meta.getScale(i);
            if (scale == 0) {
                if (meta.getPrecision(i) < 10) {
                    builder.name(columnName).type().unionOf().nullBuilder().endNull().and().intType().endUnion()
                            .noDefault();
                } else {
                    builder.name(columnName).type().unionOf().nullBuilder().endNull().and().longType()
                            .endUnion().noDefault();
                }
            } else {
                builder.name(columnName).type().unionOf().nullBuilder().endNull().and().doubleType().endUnion()
                        .noDefault();
            }
            break;

        // Did not find direct suitable type, need to be clarified!!!!
        case DATE:
        case TIME:
        case TIMESTAMP:
            builder.name(columnName).type().unionOf().nullBuilder().endNull().and().stringType().endUnion()
                    .noDefault();
            break;

        case BINARY:
        case VARBINARY:
        case LONGVARBINARY:
        case ARRAY:
        case BLOB:
            builder.name(columnName).type().unionOf().nullBuilder().endNull().and().bytesType().endUnion()
                    .noDefault();
            break;

        default:
            throw new IllegalArgumentException("createSchema: Unknown SQL type " + meta.getColumnType(i)
                    + " cannot be converted to Avro type");
        }
    }

    return builder.endRecord();
}

---

From source file:org.jabsorb.ext.DataList.java

/**
 * Build an array of ColumnMetaData object from a ResultSetMetaData object.
 *
 * @param rmd ResultSetMetaData to build ColumnMetaData from.
 * @return ColumnMetaData array or null if ResultSetMetaData is null.
 *
 * @throws SQLException if there is a problem processing the 
 * ResultSetMetaData object./*from www .  jav  a  2s  .c o  m*/
 */
public static ColumnMetaData[] buildColumnMetaDataFromResultSetMetaData(ResultSetMetaData rmd)
        throws SQLException {
    if (rmd == null) {
        return null;
    }

    int j = rmd.getColumnCount();

    ColumnMetaData[] cmd = new ColumnMetaData[j];

    for (int i = 1; i <= j; i++) {
        ColumnMetaData c = new ColumnMetaData();

        c.setColumnName(rmd.getColumnName(i));
        c.setCatalogName(rmd.getCatalogName(i));
        c.setColumnClassName(rmd.getColumnClassName(i));
        c.setColumnDisplaySize(rmd.getColumnDisplaySize(i));
        c.setColumnLabel(rmd.getColumnLabel(i));
        c.setColumnType(rmd.getColumnType(i));
        c.setColumnTypeName(rmd.getColumnTypeName(i));
        c.setPrecision(rmd.getPrecision(i));
        c.setScale(rmd.getScale(i));
        c.setSchemaName(rmd.getSchemaName(i));
        c.setTableName(rmd.getTableName(i));
        c.setAutoIncrement(rmd.isAutoIncrement(i));
        c.setCaseSensitive(rmd.isCaseSensitive(i));
        c.setCurrency(rmd.isCurrency(i));
        c.setNullable(rmd.isNullable(i));
        c.setReadOnly(rmd.isReadOnly(i));
        c.setSearchable(rmd.isSearchable(i));
        c.setSigned(rmd.isSigned(i));
        c.setWritable(rmd.isWritable(i));
        c.setDefinitelyWritable(rmd.isDefinitelyWritable(i));

        cmd[i - 1] = c;
    }
    return cmd;
}

---

From source file:ResultsDecoratorSQL.java

public void write(ResultSet rs) throws IOException, SQLException {
    ResultSetMetaData md = rs.getMetaData();
    // This assumes you're not using a Join!!
    String tableName = md.getTableName(1);
    int cols = md.getColumnCount();
    StringBuffer sb = new StringBuffer("insert into ").append(tableName).append("(");
    for (int i = 1; i <= cols; i++) {
        sb.append(md.getColumnName(i));/*from   w  w w.  j  a va 2  s.  c  o m*/
        if (i != cols) {
            sb.append(", ");
        }
    }
    sb.append(") values (");
    String insertCommand = sb.toString();
    while (rs.next()) {
        println(insertCommand);
        for (int i = 1; i <= cols; i++) {
            String tmp = rs.getString(i);
            if (rs.wasNull()) {
                print("null");
            } else {
                int type = md.getColumnType(i);
                // Don't quote numeric types; quote all others for now.
                switch (type) {
                case Types.BIGINT:
                case Types.DECIMAL:
                case Types.DOUBLE:
                case Types.FLOAT:
                case Types.INTEGER:
                    print(tmp);
                    break;
                default:
                    tmp = tmp.replaceAll("'", "''");
                    print("'" + tmp + "'");
                }
            }
            if (i != cols) {
                print(", ");
            }
        }
        println(");");
    }
}

---

From source file:jongo.handler.ResultSetMetaDataHandler.java

@Override
public List<Row> handle(ResultSet rs) throws SQLException {
    List<Row> results = new ArrayList<Row>();
    int rowId = 0;
    ResultSetMetaData metaData = rs.getMetaData();
    Map<String, String> map = null;
    for (int i = 1; i <= metaData.getColumnCount(); i++) {
        map = new HashMap<String, String>(2);
        map.put("tableName", metaData.getTableName(i));
        map.put("columnName", metaData.getColumnName(i));
        map.put("columnLabel", metaData.getColumnLabel(i));
        map.put("columnType", metaData.getColumnTypeName(i));
        map.put("columnSize", String.valueOf(metaData.getColumnDisplaySize(i)));
        map.put("precision", String.valueOf(metaData.getPrecision(i)));
        map.put("scale", String.valueOf(metaData.getScale(i)));

        //            map.put("catalog_name", metaData.getCatalogName(i));
        //            map.put("column_class_name", metaData.getColumnClassName(i));
        //            map.put("schema_name", metaData.getSchemaName(i));
        //            map.put("column_type", String.valueOf(metaData.getColumnType(i)));

        if (map != null)
            results.add(new Row(rowId++, map));
    }/*from   ww w  .j a v  a 2  s .c  o  m*/
    return results;
}

---

From source file:com.micromux.cassandra.jdbc.MetadataResultSetsTest.java

@Test
public void testTableName() throws SQLException {
    CassandraPreparedStatement statement = (CassandraPreparedStatement) con
            .prepareStatement("select * from " + KEYSPACE1 + ".test1");
    ResultSet result = statement.executeQuery();

    System.out.println("--- testTableName() ---");
    ResultSetMetaData meta = result.getMetaData();
    assertEquals("test1", meta.getTableName(1));

}

---

From source file:org.seasar.dbflute.logic.sql2entity.cmentity.DfCustomizeEntityMetaExtractor.java

public Map<String, DfColumnMeta> extractColumnMetaInfoMap(ResultSet rs, String sql,
        DfForcedJavaNativeProvider forcedJavaNativeProvider) throws SQLException {
    final Map<String, DfColumnMeta> columnMetaInfoMap = StringKeyMap.createAsFlexibleOrdered();
    final ResultSetMetaData md = rs.getMetaData();
    for (int i = 1; i <= md.getColumnCount(); i++) {
        final DfColumnMeta metaInfo = new DfColumnMeta();

        String sql2EntityRelatedTableName = null;
        try {/*from w w w.j av  a 2s . c  om*/
            sql2EntityRelatedTableName = md.getTableName(i);
        } catch (SQLException ignored) {
            // Because this table name is not required. This is for classification.
            String msg = "ResultSetMetaData.getTableName(" + i + ") threw the exception:";
            msg = msg + " " + ignored.getMessage();
            _log.info(msg);
        }
        metaInfo.setSql2EntityRelatedTableName(sql2EntityRelatedTableName);

        String columnName = md.getColumnLabel(i);
        final String relatedColumnName = md.getColumnName(i);
        metaInfo.setSql2EntityRelatedColumnName(relatedColumnName);
        if (columnName == null || columnName.trim().length() == 0) {
            columnName = relatedColumnName;
        }
        if (columnName == null || columnName.trim().length() == 0) {
            final String ln = ln();
            String msg = "The columnName is invalid: columnName=" + columnName + ln;
            msg = msg + "ResultSetMetaData returned invalid value." + ln;
            msg = msg + "sql=" + sql;
            throw new IllegalStateException(msg);
        }
        metaInfo.setColumnName(columnName);

        final int columnType = md.getColumnType(i);
        metaInfo.setJdbcDefValue(columnType);

        final String columnTypeName = md.getColumnTypeName(i);
        metaInfo.setDbTypeName(columnTypeName);

        int columnSize = md.getPrecision(i);
        if (!DfColumnExtractor.isColumnSizeValid(columnSize)) {
            // ex) sum(COLUMN)
            columnSize = md.getColumnDisplaySize(i);
        }
        metaInfo.setColumnSize(columnSize);

        final int scale = md.getScale(i);
        metaInfo.setDecimalDigits(scale);

        if (forcedJavaNativeProvider != null) {
            final String sql2entityForcedJavaNative = forcedJavaNativeProvider.provide(columnName);
            metaInfo.setSql2EntityForcedJavaNative(sql2entityForcedJavaNative);
        }

        // column comment is not set here (no comment on meta data)
        // if select column comment is specified, comment will be set later

        columnMetaInfoMap.put(columnName, metaInfo);
    }
    return columnMetaInfoMap;
}

---

From source file:org.openconcerto.sql.model.SQLRow.java

private static final List<String> getFieldNames(SQLTable table, final ResultSetMetaData rsmd,
        final boolean tableOnly) throws SQLException {
    final int colCount = rsmd.getColumnCount();
    final List<String> names = new ArrayList<String>(colCount);
    for (int i = 1; i <= colCount; i++) {
        // n'inclure que les colonnes de la table demande
        // use a boolean since some systems (eg pg) require a request to the db to return the
        // table name
        if (tableOnly || rsmd.getTableName(i).equals(table.getName())) {
            names.add(rsmd.getColumnName(i));
        } else {//from   ww  w . ja  v  a 2 s .  c om
            names.add(null);
        }
    }

    return names;
}

---

From source file:org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.ResultSetTableModelFactory.java

/**
 * No longer used./*from  w ww. j a va2  s.com*/
 *
 * @param rsmd
 * @param metaData
 * @param column
 */
@Deprecated
public static void updateMetaData(final ResultSetMetaData rsmd, final DefaultTableMetaData metaData,
        final int column) {
    try {
        if (rsmd.isCurrency(column + 1)) {
            metaData.setColumnAttribute(column, MetaAttributeNames.Numeric.NAMESPACE,
                    MetaAttributeNames.Numeric.CURRENCY, Boolean.TRUE);
        } else {
            metaData.setColumnAttribute(column, MetaAttributeNames.Numeric.NAMESPACE,
                    MetaAttributeNames.Numeric.CURRENCY, Boolean.FALSE);
        }

        if (rsmd.isSigned(column + 1)) {
            metaData.setColumnAttribute(column, MetaAttributeNames.Numeric.NAMESPACE,
                    MetaAttributeNames.Numeric.SIGNED, Boolean.TRUE);
        } else {
            metaData.setColumnAttribute(column, MetaAttributeNames.Numeric.NAMESPACE,
                    MetaAttributeNames.Numeric.SIGNED, Boolean.FALSE);
        }

        final String tableName = rsmd.getTableName(column + 1);
        if (tableName != null) {
            metaData.setColumnAttribute(column, MetaAttributeNames.Database.NAMESPACE,
                    MetaAttributeNames.Database.TABLE, tableName);
        }
        final String schemaName = rsmd.getSchemaName(column + 1);
        if (schemaName != null) {
            metaData.setColumnAttribute(column, MetaAttributeNames.Database.NAMESPACE,
                    MetaAttributeNames.Database.SCHEMA, schemaName);
        }
        final String catalogName = rsmd.getCatalogName(column + 1);
        if (catalogName != null) {
            metaData.setColumnAttribute(column, MetaAttributeNames.Database.NAMESPACE,
                    MetaAttributeNames.Database.CATALOG, catalogName);
        }
        final String label = rsmd.getColumnLabel(column + 1);
        if (label != null) {
            metaData.setColumnAttribute(column, MetaAttributeNames.Formatting.NAMESPACE,
                    MetaAttributeNames.Formatting.LABEL, label);
        }
        final int displaySize = rsmd.getColumnDisplaySize(column + 1);
        metaData.setColumnAttribute(column, MetaAttributeNames.Formatting.NAMESPACE,
                MetaAttributeNames.Formatting.DISPLAY_SIZE, IntegerCache.getInteger(displaySize));

        final int precision = rsmd.getPrecision(column + 1);
        metaData.setColumnAttribute(column, MetaAttributeNames.Numeric.NAMESPACE,
                MetaAttributeNames.Numeric.PRECISION, IntegerCache.getInteger(precision));
        final int scale = rsmd.getScale(column + 1);
        metaData.setColumnAttribute(column, MetaAttributeNames.Numeric.NAMESPACE,
                MetaAttributeNames.Numeric.SCALE, IntegerCache.getInteger(scale));
    } catch (SQLException sqle) {
        // It is non-fatal if the meta-data cannot be read from the result set. Drivers are
        // buggy all the time ..
    }
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• »