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

/**
 * Executes the prepared statement with the supplied parameters.
 */
public DbStatement execute(Object... params) throws SQLException {
    try (DatabaseTiming ignored = db.timings("execute: " + query)) {
        try {
            prepareExecute(params);
            resultSet = preparedStatement.executeQuery();
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            int numberOfColumns = resultSetMetaData.getColumnCount();

            resultCols = new String[numberOfColumns];
            // get the column names; column indexes start from 1
            for (int i = 1; i < numberOfColumns + 1; i++) {
                resultCols[i - 1] = resultSetMetaData.getColumnLabel(i);
            }
        } catch (SQLException e) {
            close();
            throw e;
        }
    }
    return this;
}
 

@Override
public List<T> handle(ResultSet resultSet) throws Exception {
    // 分析结果集字段信息
    ResultSetMetaData _metaData = resultSet.getMetaData();
    __columnCount = _metaData.getColumnCount();
    __columnMetas = new ColumnMeta[__columnCount];
    for (int _idx = 0; _idx < __columnCount; _idx++) {
        __columnMetas[_idx] = new ColumnMeta(_metaData.getColumnLabel(_idx + 1), _metaData.getColumnType(_idx + 1));
    }
    //
    List<T> _results = new ArrayList<T>();
    while (resultSet.next()) {
        _results.add(this.__doProcessResultRow(resultSet));
    }
    return _results;
}
 

void prepareFormat(ResultSet rset) throws SQLException {
  ResultSetMetaData meta = rset.getMetaData();
  ncols = meta.getColumnCount();
  widths = new int[ncols];
  labels = new String[ncols];
  for (int i = 0; i < ncols; i++) {
    labels[i] = meta.getColumnLabel(i + 1);
    int displaySize = meta.getColumnDisplaySize(i + 1);

    // NOTE jvs 13-June-2006: I put this in to cap EXPLAIN PLAN,
    // which now returns a very large worst-case display size.
    if (displaySize > 4096) {
      displaySize = 0;
    }
    widths[i] = Math.max(labels[i].length(), displaySize);
  }
}
 

public int getColumnIndex(String columnName) throws IOException {
    if(closed) {
        throw new IOException("Cursor is closed");
    }
    try {
        ResultSetMetaData meta = resultSet.getMetaData();
        int colsCount = meta.getColumnCount();
        for (int i = 0; i < colsCount; i++) {
            String c = meta.getColumnLabel(i+1);                
            if(c.equalsIgnoreCase(columnName)){
                return i;
            }
        }
        return -1;
    } catch (SQLException ex) {
        ex.printStackTrace();
        throw new IOException(ex.getMessage());
    }
}
 

/**
 * Returns the column name for a MariaDB field.
 *
 * @param dbMetaData
 * @param rsMetaData
 * @param index
 * @return The column label.
 * @throws HopDatabaseException
 */
@Override public String getLegacyColumnName( DatabaseMetaData dbMetaData, ResultSetMetaData rsMetaData, int index ) throws HopDatabaseException {
  if ( dbMetaData == null ) {
    throw new HopDatabaseException( BaseMessages.getString( PKG, "MariaDBDatabaseMeta.Exception.LegacyColumnNameNoDBMetaDataException" ) );
  }

  if ( rsMetaData == null ) {
    throw new HopDatabaseException( BaseMessages.getString( PKG, "MariaDBDatabaseMeta.Exception.LegacyColumnNameNoRSMetaDataException" ) );
  }

  try {
    return rsMetaData.getColumnLabel( index );
  } catch ( Exception e ) {
    throw new HopDatabaseException( String.format( "%s: %s", BaseMessages.getString( PKG, "MariaDBDatabaseMeta.Exception.LegacyColumnNameException" ), e.getMessage() ), e );
  }
}
 

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
    }
  }
}
 

/**
 * 查询返回一个map
 * 
 * @param Connection 数据库链接
 * @param sql
 * @param entity
 * @return
 */
public static List<Map<String, Object>> queryMapList(Connection connection, String sql) throws SQLException {
	Statement statement = null;
	List<Map<String, Object>> result = new ArrayList<>();
	try {
		statement = connection.createStatement();
		ResultSet rs = statement.executeQuery(sql);
		ResultSetMetaData rsmd = rs.getMetaData();

		while (rs.next()) {
			int cols = rsmd.getColumnCount();
			Map<String, Object> map = new HashMap<>();
			for (int i = 1; i <= cols; i++) {
				String columnName = rsmd.getColumnLabel(i);
				if ((null == columnName) || (0 == columnName.length())) {
					columnName = rsmd.getColumnName(i);
				}
				map.put(columnName, rs.getObject(i));
			}
			result.add(map);
		}
	} catch (Exception e) {
		logger.error("DbUtils queryMapList failed", e);
		throw new SQLException(e);
	} finally {
		if (connection != null) {
			closeConn(connection);
		}
	}
	return result;
}
 

@Override
public void generateSchemaChanges(BatchContext batchContext) throws SQLException {
  Map<String, Integer> source = new HashMap<>();
  ResultSet rs = tableReadContext.getMoreResultSet();
  String schemaName = "";
  String tableName = "";
  String captureInstanceName = "";

  if (rs != null && rs.next()) {
    ResultSetMetaData data = rs.getMetaData();

    for (int i = 1; i <= data.getColumnCount(); i++) {
      String label = data.getColumnLabel(i);
      if (label.equals(MSQueryUtil.CDC_SOURCE_SCHEMA_NAME)) {
        schemaName = rs.getString(label);
      } else if (label.equals(MSQueryUtil.CDC_SOURCE_TABLE_NAME)) {
        tableName = rs.getString(label);
      } else if (label.equals(MSQueryUtil.CDC_CAPTURE_INSTANCE_NAME)) {
        captureInstanceName = rs.getString(label);
      } else {
        int type = data.getColumnType(i);
        source.put(label, type);
      }
    }

    boolean schemaChanges = getDiff(captureInstanceName, source, tableRuntimeContext.getSourceTableContext().getColumnToType());

    if (schemaChanges) {
      JdbcEvents.SCHEMA_CHANGE.create(context, batchContext)
          .with("source-table-schema-name", schemaName)
          .with("source-table-name", tableName)
          .with("capture-instance-name", captureInstanceName)
          .createAndSend();
      context.processBatch(batchContext);
    }
  }
}
 

private void printColumnHeaders(ResultSetMetaData rsmd, int columnCount) throws SQLException {
  StringBuilder row = new StringBuilder();
  row.append("   Columns: ");
  for (int i = 1; i <= columnCount; i++) {
    if (BLOB_TYPES.contains(rsmd.getColumnType(i))) {
      blobColumns.add(i);
    }
    String colname = rsmd.getColumnLabel(i);
    row.append(colname);
    if (i != columnCount) {
      row.append(", ");
    }
  }
  trace(row.toString(), false);
}
 

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 readMetaData(ResultSet rs) throws SQLException {
    ResultSetMetaData meta = rs.getMetaData();
    int cCount = meta.getColumnCount();
    columnDefs = new ColumnDef[cCount];
    for(int i=0; i<cCount; i++) {
        columnDefs[i] = new ColumnDef(
                meta.getColumnLabel(i+1), 
                meta.getColumnType(i+1)
        );
    }
}
 

private void initMergeColumnIndex(ResultSetMetaData metaData) throws SQLException {
    mergeColumnsIndex = new HashMap<Integer, String>();
    int size = metaData.getColumnCount();
    for (int i = 1; i <= size; i++){
        String name = metaData.getColumnLabel(i);
        if (mergeColumns.containsKey(name)){
            mergeColumnsIndex.put(i, name);
        }
    }
    this.wasNull = new HashMap<String, Boolean>();
}
 

public void init() throws TddlException {
    if (inited) {
        return;
    }
    try {
        myJdbcHandler.executeQuery(meta, isStreaming);
        // ResultSetMetaData rsmd =
        // this.myJdbcHandler.getResultSet().getMetaData();
        returnColumns = new ArrayList();
        // 使用meta做为returncolumns
        // resultset中返回的meta信是物理表名，会导致join在构造返回对象时找不到index(表名不同/为null)
        if (meta != null) {
            returnColumns.addAll(meta.getColumns());
        } else {
            ResultSetMetaData rsmd = this.myJdbcHandler.getResultSet().getMetaData();
            for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                DataType type = TableMetaParser.jdbcTypeToDataType(rsmd.getColumnType(i));
                String name = rsmd.getColumnLabel(i);
                ColumnMeta cm = new ColumnMeta(null, name, type, null, true);
                returnColumns.add(cm);
            }

            meta = CursorMetaImp.buildNew(returnColumns);
            myJdbcHandler.setContext(meta, isStreaming);
        }
        inited = true;
    } catch (SQLException e) {
        throw new TddlException(e);
    }
}
 

private static @NotNull ResultTable.Builder createBuilder(@NotNull ResultSetMetaData metaData) throws SQLException {
    int columnCount = metaData.getColumnCount();
    ColumnMetadata[] result = new ColumnMetadata[columnCount];

    for (int i = 0; i < columnCount; i++)
        result[i] = new ColumnMetadata(i, metaData.getColumnLabel(i + 1), getColumnType(metaData, i+1), metaData.getColumnType(i+1), metaData.getColumnTypeName(i+1));

    return ResultTable.builder(asList(result));
}
 

public List<ColumnDef> describeSelectStatement(String sqlQuery) {
	List<ColumnDef> result = new ArrayList<ColumnDef>();
	try {
		PreparedStatement stmt = connection.prepareStatement(sqlQuery);
		try {
			ResultSetMetaData meta = stmt.getMetaData();
			for (int i = 1; i <= meta.getColumnCount(); i++) {
				String name = meta.getColumnLabel(i);
				int type = meta.getColumnType(i);
				String typeName = meta.getColumnTypeName(i);
				int size = meta.getPrecision(i);
				DataType dataType = vendor.getDataType(type, typeName, size);
				if (dataType == null) {
					log.warn("Unknown datatype '" + 
							(size == 0 ? typeName : (typeName + "(" + size + ")")) + 
							"' (" + type + ")");
				}
				boolean isNullable = meta.isNullable(i) != ResultSetMetaData.columnNoNulls;
				result.add(new ColumnDef(
						Identifier.createDelimited(name), dataType, isNullable));
			}
			return result;
		} finally {
			stmt.close();
		}
	} catch (SQLException ex) {
		throw new D2RQException(ex, D2RQException.D2RQ_SQLEXCEPTION);
	}
}
 

protected void getOriginalColumnMetadata( IValueMeta v, ResultSetMetaData rm, int index, boolean ignoreLength )
  throws SQLException {
  // Grab the comment as a description to the field as well.
  String comments = rm.getColumnLabel( index );
  v.setComments( comments );

  // get & store more result set meta data for later use
  int originalColumnType = rm.getColumnType( index );
  v.setOriginalColumnType( originalColumnType );

  String originalColumnTypeName = rm.getColumnTypeName( index );
  v.setOriginalColumnTypeName( originalColumnTypeName );

  int originalPrecision = -1;
  if ( !ignoreLength ) {
    // Throws exception on MySQL
    originalPrecision = rm.getPrecision( index );
  }
  v.setOriginalPrecision( originalPrecision );

  int originalScale = rm.getScale( index );
  v.setOriginalScale( originalScale );

  // DISABLED FOR PERFORMANCE REASONS : PDI-1788
  //
  // boolean originalAutoIncrement=rm.isAutoIncrement(index); DISABLED FOR
  // PERFORMANCE REASONS : PDI-1788
  // v.setOriginalAutoIncrement(originalAutoIncrement);

  // int originalNullable=rm.isNullable(index); DISABLED FOR PERFORMANCE
  // REASONS : PDI-1788
  // v.setOriginalNullable(originalNullable);
  //

  boolean originalSigned = false;
  try {
    originalSigned = rm.isSigned( index );
  } catch ( Exception ignored ) {
    // This JDBC Driver doesn't support the isSigned method.
    // Nothing more we can do here.
  }
  v.setOriginalSigned( originalSigned );
}
 

@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);
        }
    }
}
 

static private int indent_DisplayBanner(PrintStream out, ResultSetMetaData rsmd, int indentLevel,
										int[] displayColumns, int[] displayColumnWidths )
	throws SQLException	{

	StringBuilder buf = new StringBuilder();

	int numCols = displayColumnWidths.length;
	int rowLen;

	// do some precalculation so the buffer is allocated only once
	// buffer is twice as long as the display length plus one for a newline
	rowLen = (numCols - 1); // for the column separators
	for (int i=1; i <= numCols; i++) {
		rowLen += displayColumnWidths[i-1];
	}
	buf.ensureCapacity(rowLen);

	// get column header info
	// truncate it to the column display width
	// add a bar between each item.
	for (int i=1; i <= numCols; i++) {
		int colnum = displayColumns==null ? i : displayColumns[i-1];

		if (i>1)
			buf.append('|');

		String s = rsmd.getColumnLabel(colnum);

		int w = displayColumnWidths[i-1];

		if (s.length() < w) {
			// build a string buffer to hold the whitespace
			StringBuilder blanks = new StringBuilder(s);
			blanks.ensureCapacity(w);

			// try to paste on big chunks of space at a time.
			for (int k=blanks.length()+64; k<=w; k+=64)
				blanks.append(
         "                                                                ");
			for (int k=blanks.length()+16; k<=w; k+=16)
				blanks.append("                ");
			for (int k=blanks.length()+4; k<=w; k+=4)
				blanks.append("    ");
			for (int k=blanks.length(); k<w; k++)
				blanks.append(' ');

			buf.append(blanks);
			// REMIND: could do more cleverness, like keep around
			// past buffers to reuse...
		}
		else if (s.length() > w)  {
			if (w > 1) 
				buf.append(s.substring(0,w-1));
			if (w > 0) 
				buf.append('&');
		}
		else {
			buf.append(s);
		}
	}

	buf.setLength(Math.min(rowLen, 1024));
	indentedPrintLine( out, indentLevel, buf);

	// now print a row of '-'s
	for (int i=0; i<Math.min(rowLen, 1024); i++)
		buf.setCharAt(i, '-');
	indentedPrintLine( out, indentLevel, buf);

	buf = null;

	return rowLen;
}
 

static private int indent_DisplayBanner(PrintWriter out, ResultSetMetaData rsmd, int indentLevel,
										int[] displayColumns, int[] displayColumnWidths )
	throws SQLException	{

	StringBuilder buf = new StringBuilder();

	int numCols = displayColumnWidths.length;
	int rowLen;

	// do some precalculation so the buffer is allocated only once
	// buffer is twice as long as the display length plus one for a newline
	rowLen = (numCols - 1); // for the column separators
	if ( rowLen < 0 ) {
	  return 0;
	}
	for (int i=1; i <= numCols; i++)
		rowLen += displayColumnWidths[i-1];
	buf.ensureCapacity(rowLen);

	// get column header info
	// truncate it to the column display width
	// add a bar between each item.
	for (int i=1; i <= numCols; i++) {
		int colnum = displayColumns==null ? i : displayColumns[i-1];

		if (i>1)
			buf.append('|');

		String s = rsmd.getColumnLabel(colnum);

		int w = displayColumnWidths[i-1];

		if (s.length() < w) {
			
			buf.append(s);

			// try to paste on big chunks of space at a time.
			int k = w - s.length();
			for (; k >= 64; k -= 64)
				buf.append(
         "                                                                ");
			for (; k >= 16; k -= 16)
				buf.append("                ");
			for (; k >= 4; k -= 4)
				buf.append("    ");
			for (; k > 0; k--)
				buf.append(' ');
		}
		else if (s.length() > w)  {
			if (w > 1) 
				buf.append(s.substring(0,w-1));
			if (w > 0) 
				buf.append('&');
		}
		else {
			buf.append(s);
		}
	}

	buf.setLength(Math.min(rowLen, 1024));
	indentedPrintLine( out, indentLevel, buf);

	// now print a row of '-'s
	for (int i=0; i<Math.min(rowLen, 1024); i++)
		buf.setCharAt(i, '-');
	indentedPrintLine( out, indentLevel, buf);

	buf = null;

	return rowLen;
}
 

/**
 * Determine the column name to use. The column name is determined based on a
 * lookup using ResultSetMetaData.
 * <p>This method implementation takes into account recent clarifications
 * expressed in the JDBC 4.0 specification:
 * <p><i>columnLabel - the label for the column specified with the SQL AS clause.
 * If the SQL AS clause was not specified, then the label is the name of the column</i>.
 * @param resultSetMetaData the current meta-data to use
 * @param columnIndex the index of the column for the look up
 * @return the column name to use
 * @throws SQLException in case of lookup failure
 */
public static String lookupColumnName(ResultSetMetaData resultSetMetaData, int columnIndex) throws SQLException {
	String name = resultSetMetaData.getColumnLabel(columnIndex);
	if (!StringUtils.hasLength(name)) {
		name = resultSetMetaData.getColumnName(columnIndex);
	}
	return name;
}