Java Code Examples for java.sql.ResultSetMetaData#getTableName()

/**
 * Returns a list of metadata information for columns in the passed
 * ResultSet.
 *
 * @param rs
 * @return
 * @throws SQLException
 */
@Override
public TableMetaData getMetaDataFor(final ResultSet rs) throws SQLException {
    TableMetaData table = null;
    final ResultSetMetaData rsmd = rs.getMetaData();
    for (int i = 1; i <= rsmd.getColumnCount(); ++i) {
        if (table == null) {
            table = new TableMetaData(rsmd.getSchemaName(i), rsmd.getTableName(i));
        }
        table.addColumn(
            i,
            rsmd.getColumnName(i),
            sqlTypeMap.getTypeFrom(rsmd.getColumnType(i)),
            rsmd.getColumnDisplaySize(i),
            false,
            null
        );
    }
    return table;
}
 

public ResultSetMetaDataHolder(ResultSetMetaData m, int i) throws SQLException {
       String catalogName = m.getCatalogName(i);
       String columnClassName = m.getColumnClassName(i);
       int columnDisplaySize = m.getColumnDisplaySize(i);
       String columnLabel = m.getColumnLabel(i);
       String columnName = m.getColumnName(i);
       
       int columnType = m.getColumnType(i);
       String columnTypeName = m.getColumnTypeName(i);
       int precision = m.getPrecision(i);
       int scale = m.getScale(i);
       
       String schemaName = m.getSchemaName(i);
       String tableName = m.getTableName(i);
       
       this.catalogName = catalogName;
       this.columnClassName = columnClassName ;
       this.columnDisplaySize = columnDisplaySize;
       this.columnLabel = columnLabel;
       this.columnName = columnName;
       this.columnType=  columnType;
       this.columnTypeName = columnTypeName;
       this.precision  = precision;
       this.scale = scale;
       this.schemaName = schemaName;
       this.tableName = tableName;    		
}
 

private static String getTableName(ResultSetMetaData dbMetadata) {
	try {
		return dbMetadata.getTableName(1);
	} catch (SQLException e) {
		return null;
	}
}
 

private static List<String> getPrimaryKeys(Connection connection, ResultSetMetaData resultSetMetaData, ColumnDefinitionProvider<JdbcColumnKey> columnDefinitionProvider) throws SQLException {
    List<String> primaryKeys = new ArrayList<String>();

    for(int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
        JdbcColumnKey key = JdbcColumnKey.of(resultSetMetaData, i);
        if (columnDefinitionProvider.getColumnDefinition(key).has(KeyProperty.class)) {
            primaryKeys.add(key.getName());
        }
    }

    if (!primaryKeys.isEmpty()) {
        return primaryKeys;
    }

    String catalogName = resultSetMetaData.getCatalogName(1);
    String schemaName = resultSetMetaData.getSchemaName(1);
    String tableName = resultSetMetaData.getTableName(1);
    
    try {
        ResultSet set = connection.getMetaData().getPrimaryKeys(catalogName, schemaName, tableName);
        try {
            while (set.next()) {
                primaryKeys.add(set.getString("COLUMN_NAME"));
            }
        } finally {
            set.close();
        }
    } catch (SQLSyntaxErrorException e) {
        // ignore likely mysql view issues
    }
    return primaryKeys;
}
 

private void printResultSetMetaData(ResultSet rs) throws SQLException {
    ResultSetMetaData metadata = rs.getMetaData();
    System.out.println("Metadata:");

    for (int i = 0; i < metadata.getColumnCount(); i++) {
        String metaStr = metadata.getCatalogName(i + 1) + " " + metadata.getColumnClassName(i + 1) + " "
                + metadata.getColumnDisplaySize(i + 1) + " " + metadata.getColumnLabel(i + 1) + " "
                + metadata.getColumnName(i + 1) + " " + metadata.getColumnType(i + 1) + " "
                + metadata.getColumnTypeName(i + 1) + " " + metadata.getPrecision(i + 1) + " "
                + metadata.getScale(i + 1) + " " + metadata.getSchemaName(i + 1) + " "
                + metadata.getTableName(i + 1);
        System.out.println(metaStr);
    }
}
 

public void setColumnSpecificHeaders(
    Record record,
    Set<String> knownTableNames,
    ResultSetMetaData metaData,
    String jdbcNameSpacePrefix
) throws SQLException {
  Record.Header header = record.getHeader();
  Set<String> tableNames = new HashSet<>();

  for (int i=1; i<=metaData.getColumnCount(); i++) {
    header.setAttribute(jdbcNameSpacePrefix + metaData.getColumnLabel(i) + ".jdbcType", String.valueOf(metaData.getColumnType(i)));

    // Additional headers per various types
    switch(metaData.getColumnType(i)) {
      case Types.DECIMAL:
      case Types.NUMERIC:
        header.setAttribute(jdbcNameSpacePrefix + metaData.getColumnLabel(i) + ".scale", String.valueOf(metaData.getScale(i)));
        header.setAttribute(jdbcNameSpacePrefix + metaData.getColumnLabel(i) + ".precision", String.valueOf(metaData.getPrecision(i)));
        break;
    }

    String tableName = metaData.getTableName(i);

    // Store the column's table name (if not empty)
    if (StringUtils.isNotEmpty(tableName)) {
      tableNames.add(tableName);
    }
  }

  if (tableNames.isEmpty()) {
    tableNames.addAll(knownTableNames);
  }

  header.setAttribute(jdbcNameSpacePrefix + "tables", Joiner.on(",").join(tableNames));
}
 

public ResultSetColumnMetaData(final ResultSetMetaData rsMetaData, final int j) throws SQLException {
    // @formatter:off
    this(rsMetaData.getSchemaName(j),
         rsMetaData.getCatalogName(j),
         rsMetaData.getTableName(j),
         rsMetaData.getColumnName(j),
         rsMetaData.getColumnLabel(j),
         rsMetaData.getColumnDisplaySize(j),
         rsMetaData.getColumnType(j),
         rsMetaData.getColumnTypeName(j),
         rsMetaData.getColumnClassName(j),
         rsMetaData.getPrecision(j),
         rsMetaData.getScale(j));
    // @formatter:on
}
 

protected void createTable() {
    try {
        //Quoter qt = SQLIdentifiers.createQuoter(dbmd);
        ResultSet rs = conn.createStatement().executeQuery(context.getSqlSelect());
        ResultSetMetaData rsMeta = rs.getMetaData();
        String aName = rsMeta.getTableName(1);
        String aSchema = rsMeta.getSchemaName(1);
        String aCatalog = rsMeta.getCatalogName(1);
        table = new DBTable(aName, aSchema, aCatalog);
    //table.setQuoter(quoter);
    } catch (SQLException ex) {
        Exceptions.printStackTrace(ex);
    }
}
 

/**
 * Formats an error message for an unexpected null value.
 */
private String nullValue(final ResultSet result, final int columnIndex, final Comparable<?> code) throws SQLException {
    final ResultSetMetaData metadata = result.getMetaData();
    final String column = metadata.getColumnName(columnIndex);
    final String table  = metadata.getTableName (columnIndex);
    result.close();
    return error().getString(Errors.Keys.NullValueInTable_3, table, column, code);
}
 

private void testSelect(String sql, Object... parameters) throws SQLException {
  PreparedStatement ps = connection.prepareStatement(sql);
  for (int i = 1; i <= parameters.length; i++) {
    ps.setObject(i, parameters[i - 1]);
  }
  try (ResultSet rs = ps.executeQuery()) {
    ResultSetMetaData metadata = rs.getMetaData();
    for (int i = 1; i <= metadata.getColumnCount(); i++) {
      metadata.getColumnClassName(i);
      metadata.getColumnDisplaySize(i);
      metadata.getColumnLabel(i);
      metadata.getColumnName(i);
      metadata.getColumnType(i);
      metadata.getColumnTypeName(i);
      metadata.getPrecision(i);
      metadata.getScale(i);
      metadata.getCatalogName(i);
      metadata.getSchemaName(i);
      metadata.getTableName(i);
      metadata.isNullable(i);
      metadata.isAutoIncrement(i);
      metadata.isCaseSensitive(i);
      metadata.isCurrency(i);
      metadata.isDefinitelyWritable(i);
      metadata.isReadOnly(i);
      metadata.isSearchable(i);
      metadata.isSigned(i);
      metadata.isWritable(i);
    }
    while (rs.next()) {
      // do nothing
    }
  }
}
 

private void printResultSetMetaData(ResultSet rs) throws SQLException {
    ResultSetMetaData metadata = rs.getMetaData();
    System.out.println("Metadata:");

    for (int i = 0; i < metadata.getColumnCount(); i++) {
        String metaStr = metadata.getCatalogName(i + 1) + " " + metadata.getColumnClassName(i + 1) + " "
                + metadata.getColumnDisplaySize(i + 1) + " " + metadata.getColumnLabel(i + 1) + " "
                + metadata.getColumnName(i + 1) + " " + metadata.getColumnType(i + 1) + " "
                + metadata.getColumnTypeName(i + 1) + " " + metadata.getPrecision(i + 1) + " "
                + metadata.getScale(i + 1) + " " + metadata.getSchemaName(i + 1) + " "
                + metadata.getTableName(i + 1);
        System.out.println(metaStr);
    }
}
 

public static TableColumnInfo createTableMetadataWithExtraSelect(Connection connection, String schema, String tableName, String fullyQualifiedTableName)
throws SQLException
{
    TableColumnInfo tableInfo = createTableMetadata(connection, schema, tableName, fullyQualifiedTableName);

    if (tableInfo == null || tableInfo.getColumns().length == 0)
    {
        List columns = new ArrayList();
        ResultSet resultSet = null;

        // Get hold of the table catalog and schema separately from the column metadata in case we have a zero-column table/view
        String catalog = null;
        String dbSchema = null;
        String dbName = null;
        Statement stm = null;
        try
        {
            stm = connection.createStatement();
            resultSet = stm.executeQuery("select * from "+fullyQualifiedTableName+" where 0 = 1");
            resultSet.next();
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            int columnCount = resultSetMetaData.getColumnCount();
            dbName = resultSetMetaData.getTableName(1);
            dbSchema = resultSetMetaData.getSchemaName(1);
            if ("".equals(dbSchema))
            {
                dbSchema = null;
            }
            for(int i=0;i< columnCount; i++)
            {
                int col = i + 1;
                ColumnInfo columnInfo = new ColumnInfo(resultSetMetaData.getColumnName(col),
                        resultSetMetaData.getColumnType(col),
                        resultSetMetaData.getColumnDisplaySize(col),
                        resultSetMetaData.getPrecision(col),
                        resultSetMetaData.getScale(col),
                        col,
                        resultSetMetaData.isNullable(col) == DatabaseMetaData.columnNullable);
                columns.add(columnInfo);
            }
        }
        catch(SQLException e)
        {
            //ignore this; it's acceptable for the table to not be found.
        }
        finally
        {
            close(stm);
            close(resultSet);
        }
        if (columns.size() == 0)
        {
            return null;
        }
        tableInfo = new TableColumnInfo(catalog, dbSchema, dbName, (ColumnInfo[]) columns.toArray(new ColumnInfo[columns.size()]));
    }
    return tableInfo;
}
 

@Override
public boolean processEvents(List<Event> events) {
  Log.getLogWriter().info("start processing the list of async events");
  boolean hasFailedEvent = false;
  for (Event event : events){
    /* each listener will set the failed event.
    if (failedEvent != null) {
      if (!isSame(failedEvent, event)) {
        if (!event.isPossibleDuplicate()) Log.getLogWriter().warning("an already processed " +
        		"event does not have isPossibleDuplicate() set to true");
        else Log.getLogWriter().info("this event has been processed before, do not retry");
        continue;
      } else {
        failedEvent = null; //reset the flag.
      }
    } 
    */
         
    String tableName = null;
    ResultSetMetaData meta = event.getResultSetMetaData();
    try {
      tableName = meta.getTableName(1);
    }catch(SQLException se){
      SQLHelper.handleSQLException(se);
    }

    AbstractAsyncListener listener = getListener(tableName);
    //List<Event> aList = new ArrayList<Event>();
    //aList.add(event);

    boolean success = listener.processEvent(event);
    if (!success & testUniqueKeys) {
      //only for testing unique keys case, otherwise test will hang as out of order delivery 
      //could cause the event continuously to fail due to sqlException
      
      hasFailedEvent = true;
      //do not return false right away, as inserts to parent and child table may be queued 
      //out of order even when each thread has its own set of keys
      //this will allow insert to parent to be successful if it is in the same batch
    }
    if (hasFailedEvent) return false;
  }
  return true;
}
 

public MySQLColumnDefinition41Packet(final int sequenceId, final ResultSetMetaData resultSetMetaData, final int columnIndex) throws SQLException {
    this(sequenceId, resultSetMetaData.getSchemaName(columnIndex), resultSetMetaData.getTableName(columnIndex), resultSetMetaData.getTableName(columnIndex), 
            resultSetMetaData.getColumnLabel(columnIndex), resultSetMetaData.getColumnName(columnIndex), resultSetMetaData.getColumnDisplaySize(columnIndex), 
            MySQLColumnType.valueOfJDBCType(resultSetMetaData.getColumnType(columnIndex)), resultSetMetaData.getScale(columnIndex));
}
 

protected String getTableName(PredicateIndicator pi) throws SQLException {
    ResultSetMetaData meta = belsDB.get(pi);
    return meta.getTableName(1);
}
 

@Override
public boolean processEvents(List<Event> events) {
  Log.getLogWriter().info("start processing the list of async events");
  boolean hasFailedEvent = false;
  for (Event event : events){
    /* each listener will set the failed event.
    if (failedEvent != null) {
      if (!isSame(failedEvent, event)) {
        if (!event.isPossibleDuplicate()) Log.getLogWriter().warning("an already processed " +
        		"event does not have isPossibleDuplicate() set to true");
        else Log.getLogWriter().info("this event has been processed before, do not retry");
        continue;
      } else {
        failedEvent = null; //reset the flag.
      }
    } 
    */
         
    String tableName = null;
    ResultSetMetaData meta = event.getResultSetMetaData();
    try {
      tableName = meta.getTableName(1);
    }catch(SQLException se){
      SQLHelper.handleSQLException(se);
    }

    AbstractAsyncListener listener = getListener(tableName);
    //List<Event> aList = new ArrayList<Event>();
    //aList.add(event);

    boolean success = listener.processEvent(event);
    if (!success & testUniqueKeys) {
      //only for testing unique keys case, otherwise test will hang as out of order delivery 
      //could cause the event continuously to fail due to sqlException
      
      hasFailedEvent = true;
      //do not return false right away, as inserts to parent and child table may be queued 
      //out of order even when each thread has its own set of keys
      //this will allow insert to parent to be successful if it is in the same batch
    }
    if (hasFailedEvent) return false;
  }
  return true;
}
 

public DbRecordValuesList[] select( com.axway.ats.common.dbaccess.DbQuery dbQuery,
                                    DbReturnModes dbReturnMode ) throws DbException {

    connection = ConnectionPool.getConnection(dbConnection);

    final String errMsg = "Error running or parsing result of sql query '" + dbQuery.getQuery() + "'";

    ArrayList<DbRecordValuesList> dbRecords = new ArrayList<DbRecordValuesList>();
    log.debug(dbQuery.getQuery()); // debug current query
    try (PreparedStatement st = prepareStatement(connection, dbQuery.getQuery(),
                                                 dbQuery.getArguments());
            ResultSet res = st.executeQuery()) {

        ResultSetMetaData rsmd = res.getMetaData();

        int numberOfColumns = rsmd.getColumnCount();
        int currentRow = 0;
        while (res.next()) {
            currentRow++;
            DbRecordValuesList recordList = new DbRecordValuesList();

            for (int i = 1; i <= numberOfColumns; i++) {
                String tableName = rsmd.getTableName(i);
                String columnName = rsmd.getColumnName(i);
                DbColumn dbColumn = new DbColumn(tableName, columnName);
                dbColumn.setColumnType(rsmd.getColumnTypeName(i));

                DbRecordValue recordValue = null;

                //get the columns in the appropriate type
                try {
                    //get the columns in the appropriate type
                    switch (dbReturnMode) {
                        case OBJECT:
                            recordValue = parseDbRecordAsObject(dbColumn, res, i);
                            break;

                        case INPUT_STREAM:
                            recordValue = parseDbRecordAsInputStream(dbColumn, res, i);
                            break;

                        case STRING:
                        case ESCAPED_STRING:
                            recordValue = parseDbRecordAsString(dbColumn, res, i);
                            break;

                        default:
                            throw new DbException("Getting the values as " + dbReturnMode.name()
                                                  + " is not supported. Table '"
                                                  + dbColumn.getTableName() + "', column '"
                                                  + dbColumn.getColumnName() + "'");
                    }
                } finally {
                    if (recordValue == null) {
                        // help locate error case when we have exception from the underlying calls in try block
                        log.error("Error getting value for table '" + tableName + "', row number "
                                  + currentRow + ",column " + i + ",named '" + columnName + "'");
                    } else {
                        // Trace. This could produce huge data so using lowest possible severity.
                        if (log.isTraceEnabled()) {
                            log.trace("Value for column " + i + ",named '" + columnName + "' is '"
                                      + recordValue.getValue() + "'");
                        }
                    }
                }
                recordList.add(recordValue);
            }

            dbRecords.add(recordList);
        }
        if (log.isDebugEnabled()) {
            log.debug("Select statement returned " + currentRow + " rows");
        }
    } catch (SQLException e) {
        throw new DbException(errMsg, e);
    } catch (IOException ioe) {
        throw new DbException(errMsg, ioe);
    } finally {
        DbUtils.closeConnection(connection);
    }

    return dbRecords.toArray(new DbRecordValuesList[]{});
}
 

@Ignore("not maintaining")
@Test
public void testWithCubeData() throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException {
    Driver driver = (Driver) Class.forName("org.apache.kylin.kylin.jdbc.Driver").newInstance();
    Properties info = new Properties();
    info.put("user", "");
    info.put("password", "");
    Connection conn = driver.connect("jdbc:kylin://localhost/default", info);

    ResultSet catalogs = conn.getMetaData().getCatalogs();
    while (catalogs.next()) {
        System.out.println(catalogs.getString("TABLE_CAT"));
    }

    ResultSet schemas = conn.getMetaData().getSchemas();
    while (schemas.next()) {
        System.out.println(schemas.getString(1));
        System.out.println(schemas.getString(2));
    }

    ResultSet tables = conn.getMetaData().getTables(null, null, null, null);
    while (tables.next()) {
        String tableName = tables.getString(3);
        assertEquals(tables.getString("TABLE_NAME"), tableName);
        ResultSet columns = conn.getMetaData().getColumns(null, null, tableName, null);

        while (columns.next()) {
            System.out.println(columns.getString("COLUMN_NAME"));
            String column = "";
            for (int i = 0; i < 23; i++) {
                column += columns.getString(i + 1) + ", ";
            }

            System.out.println("Column in table " + tableName + ": " + column);
        }
    }

    for (int j = 0; j < 3; j++) {
        Statement state = conn.createStatement();
        ResultSet resultSet = state.executeQuery("select * from test_kylin_fact");

        ResultSetMetaData metadata = resultSet.getMetaData();
        System.out.println("Metadata:");

        for (int i = 0; i < metadata.getColumnCount(); i++) {
            String metaStr = metadata.getCatalogName(i + 1) + " " + metadata.getColumnClassName(i + 1) + " " + metadata.getColumnDisplaySize(i + 1) + " " + metadata.getColumnLabel(i + 1) + " " + metadata.getColumnName(i + 1) + " " + metadata.getColumnType(i + 1) + " " + metadata.getColumnTypeName(i + 1) + " " + metadata.getPrecision(i + 1) + " " + metadata.getScale(i + 1) + " " + metadata.getSchemaName(i + 1) + " " + metadata.getTableName(i + 1);
            System.out.println(metaStr);
        }

        System.out.println("Data:");
        while (resultSet.next()) {
            String dataStr = resultSet.getFloat(1) + " " + resultSet.getInt(2) + " " + resultSet.getInt(3) + " " + resultSet.getLong(4) + " " + resultSet.getDate(5) + " " + resultSet.getString(6);
            System.out.println(dataStr);
        }
    }
}
 

@Override
public String getTableName(ResultSetMetaData metaData, int columnIndex) throws SQLException {
  return metaData.getTableName(columnIndex);
}
 

public RowSetTable(CachedRowSet rowSet) throws SQLException {
	this.rowSet = rowSet;
	ResultSetMetaData md = rowSet.getMetaData();

	Column[] columns = new Column[md.getColumnCount()];

	ResultMetaData.Builder mdBuilder = new ResultMetaData.Builder();
	for (int i = 0; i < columns.length; i++) {
		String className = md.getColumnClassName(i + 1);
		Class<?> clazz;
		try {
			clazz = Class.forName(className);
		} catch (ClassNotFoundException e) {
			clazz = String.class;
		}
		String columnName = md.getColumnName(i + 1);
		columns[i] = new Column(columnName, clazz);
		if (md.getColumnType(i + 1) == Types.JAVA_OBJECT) {
			// Most likely a HeapReference
			final int columnPosition = i;
			String tableName = md.getTableName(i + 1);
			final String label;
			if (tableName == null || tableName.isEmpty()) {
				label = columnName;
			} else {
				label = tableName + "." + columnName;
			}
			mdBuilder.addContext(new ContextProvider(label) {
				@Override
				public IContextObject getContext(Object row) {
					return RowSetTable.getContext(row, columnPosition);
				}
			});
			if (idColumnPosition == -1) {
				// Use first object column as context provider (e.g. in case "this" column is missing)
				idColumnPosition = i;
			}
		}
		if (idColumnPosition == -1 && "this".equals(columns[i].getLabel()))
			idColumnPosition = i;
	}
	this.metaData = mdBuilder.build();
	this.columns = columns;
}