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

private static String getRowInfo(ResultSet rs) {
    StringBuilder s = new StringBuilder();
    try {
        ResultSetMetaData md = rs.getMetaData();
        for (int i = 1; i <= md.getColumnCount(); i++) {
            String name = md.getColumnName(i);
            String type = md.getColumnClassName(i);
            if (s.length() > 0)
                s.append(", ");
            s.append(name);
            s.append("=");
            s.append(type);
        }
    } catch (SQLException e1) {
        throw new SQLRuntimeException(e1);
    }
    return s.toString();
}
 

@Ignore("not maintaining")
@Test
public void testPreStatementWithCubeData() throws SQLException {
    Driver driver = new Driver();
    Properties info = new Properties();
    info.put("user", "");
    info.put("password", "");
    Connection conn = driver.connect("jdbc:kylin://localhost/default", info);
    PreparedStatement state = conn.prepareStatement("select * from test_kylin_fact where seller_id=?");
    state.setLong(1, 10000001);
    ResultSet resultSet = state.executeQuery();

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

private ResultSetValueSelector[] constructValueSelectors(ResultSetMetaData metadata) throws SQLException{
  ResultSetValueSelector[] valueSelectors = new ResultSetValueSelector[metadata.getColumnCount()];    
  for (int columnIdx = 0; columnIdx < metadata.getColumnCount(); ++columnIdx) {      
    ResultSetValueSelector valueSelector = determineValueSelector(columnIdx, metadata);
    if(valueSelector==null) {
      int columnNumber = columnIdx + 1;
      String columnName = metadata.getColumnLabel(columnNumber);
      String className = metadata.getColumnClassName(columnNumber);
      String typeName = metadata.getColumnTypeName(columnNumber);
      throw new SQLException(String.format(Locale.ROOT,
          "Unable to determine the valueSelector for column '%s' (col #%d) of java class '%s' and type '%s'",
          columnName, columnNumber, className, typeName));
    }
    valueSelectors[columnIdx] = valueSelector;
  }        
  return valueSelectors;
}
 

@Override
protected ResultSetValueSelector determineValueSelector(int columnIdx, ResultSetMetaData metadata)
    throws SQLException {
  ResultSetValueSelector valueSelector = super.determineValueSelector(columnIdx, metadata);
  if (valueSelector == null) {
    final int columnNumber = columnIdx + 1;
    final String columnName = metadata.getColumnLabel(columnNumber);
    final String className = metadata.getColumnClassName(columnNumber);
    if (Array.class.getName().equals(className)) {
      valueSelector = new ResultSetValueSelector() {
        @Override
        public Object selectValue(ResultSet resultSet) throws SQLException {
          Object o = resultSet.getObject(columnNumber);
          if (resultSet.wasNull()) {
            return null;
          }
          if (o instanceof Array) {
            Array array = (Array) o;
            return array.getArray();
          } else {
            return o;
          }
        }

        @Override
        public String getColumnName() {
          return columnName;
        }
      };
    }
  }
  return valueSelector;
}
 

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

/**
 * To construct StructType from the resultSet given.
 * 
 * @param rs
 *          -- resultSet used to get StructType
 * @return StructType for each row of the resultSet
 */
private static StructTypeImpl getStructType(ResultSet rs) throws SQLException {
  int numOfColumns;
  ResultSetMetaData rsmd;

  rsmd = rs.getMetaData();
  numOfColumns = rsmd.getColumnCount();

  ObjectType[] oTypes = new ObjectType[numOfColumns];
  String[] fieldNames = new String[numOfColumns];
  for (int i = 0; i < numOfColumns; i++) {
    try {
      String className = rsmd.getColumnClassName(i + 1);
      if ("byte[]".equals(className)) {
        oTypes[i] = new ObjectTypeImpl(byte[].class);
      }
      else if ("char[]".equals(className)) {
        oTypes[i] = new ObjectTypeImpl(char[].class);
      }
      else {
        oTypes[i] = new ObjectTypeImpl(Class.forName(rsmd
            .getColumnClassName(i + 1))); // resultSet column starts from 1
      }
    } catch (ClassNotFoundException cnfe) {
      LogWriter logger = Misc.getCacheLogWriter();
      if (logger.warningEnabled()) {
        logger.warning("GfxdDumpLocalResultMessage#getStructType: class "
            + "not found for " + rsmd.getColumnClassName(i + 1), cnfe);
      }
    }
    // resultSet column starts from 1
    fieldNames[i] = rsmd.getColumnName(i + 1);
  }

  StructTypeImpl sType = new StructTypeImpl(fieldNames, oTypes);
  return sType;
}
 

public static @NotNull Type getColumnType(@NotNull ResultSetMetaData metaData, int column) throws SQLException {
    String className = metaData.getColumnClassName(column);
    try {
        return Class.forName(className);
    } catch (ClassNotFoundException e) {
        throw new DatabaseException("Could not find class '" + className + "' specified by ResultSet.", e);
    }
}
 

public static Map<String, ColumnInfo> create(ConfigInfo configInfo, String dbType, String tableName, List<String> primaryKeys, DatabaseMetaData databaseMetaData, ResultSetMetaData metaData) throws SQLException {
    Map<String, ColumnInfo> _returnValue = new LinkedHashMap<String, ColumnInfo>(metaData.getColumnCount());
    //
    for (int _idx = 1; _idx <= metaData.getColumnCount(); _idx++) {
        // 获取字段元数据对象
        ResultSet _column = databaseMetaData.getColumns(configInfo.getDbName(),
                "oracle".equalsIgnoreCase(dbType) ? configInfo.getDbUserName().toUpperCase() : configInfo.getDbUserName(), tableName, metaData.getColumnName(_idx));
        if (_column.next()) {
            // 提取字段定义及字段默认值
            String _name = metaData.getColumnName(_idx).toLowerCase();
            ColumnInfo _columnInfo = new ColumnInfo(
                    configInfo.getNamedFilter(),
                    _name,
                    metaData.getColumnClassName(_idx),
                    metaData.isAutoIncrement(_idx),
                    primaryKeys.contains(_name),
                    metaData.isSigned(_idx),
                    metaData.getPrecision(_idx),
                    metaData.getScale(_idx),
                    metaData.isNullable(_idx),
                    _column.getString("COLUMN_DEF"),
                    _column.getString("REMARKS"));
            _returnValue.put(_name, _columnInfo);
        }
        _column.close();
    }
    return _returnValue;
}
 

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

@Test
public void givenDbConnectionF_whenRSMetaInfo_thenCorrect() throws SQLException {
    int columnCount = 0;
    try (PreparedStatement pstmt = dbConnection.prepareStatement("select * from employees", ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet rs = pstmt.executeQuery()) {
        ResultSetMetaData metaData = rs.getMetaData();
        columnCount = metaData.getColumnCount();
        for (int i = 1; i <= columnCount; i++) {
            String catalogName = metaData.getCatalogName(i);
            String className = metaData.getColumnClassName(i);
            String label = metaData.getColumnLabel(i);
            String name = metaData.getColumnName(i);
            String typeName = metaData.getColumnTypeName(i);
            Integer type = metaData.getColumnType(i);
            String tableName = metaData.getTableName(i);
            String schemaName = metaData.getSchemaName(i);
            boolean isAutoIncrement = metaData.isAutoIncrement(i);
            boolean isCaseSensitive = metaData.isCaseSensitive(i);
            boolean isCurrency = metaData.isCurrency(i);
            boolean isDefiniteWritable = metaData.isDefinitelyWritable(i);
            boolean isReadOnly = metaData.isReadOnly(i);
            boolean isSearchable = metaData.isSearchable(i);
            boolean isReadable = metaData.isReadOnly(i);
            boolean isSigned = metaData.isSigned(i);
            boolean isWritable = metaData.isWritable(i);
            int nullable = metaData.isNullable(i);
        }
    }

    assertEquals("column count", 4, columnCount);
}
 

/**
 * To construct StructType from the resultSet given.
 * 
 * @param rs
 *          -- resultSet used to get StructType
 * @return StructType for each row of the resultSet
 */
private static StructTypeImpl getStructType(ResultSet rs) throws SQLException {
  int numOfColumns;
  ResultSetMetaData rsmd;

  rsmd = rs.getMetaData();
  numOfColumns = rsmd.getColumnCount();

  ObjectType[] oTypes = new ObjectType[numOfColumns];
  String[] fieldNames = new String[numOfColumns];
  for (int i = 0; i < numOfColumns; i++) {
    try {
      String className = rsmd.getColumnClassName(i + 1);
      if ("byte[]".equals(className)) {
        oTypes[i] = new ObjectTypeImpl(byte[].class);
      }
      else if ("char[]".equals(className)) {
        oTypes[i] = new ObjectTypeImpl(char[].class);
      }
      else {
        oTypes[i] = new ObjectTypeImpl(Class.forName(rsmd
            .getColumnClassName(i + 1))); // resultSet column starts from 1
      }
    } catch (ClassNotFoundException cnfe) {
      LogWriter logger = Misc.getCacheLogWriter();
      if (logger.warningEnabled()) {
        logger.warning("GfxdDumpLocalResultMessage#getStructType: class "
            + "not found for " + rsmd.getColumnClassName(i + 1), cnfe);
      }
    }
    // resultSet column starts from 1
    fieldNames[i] = rsmd.getColumnName(i + 1);
  }

  StructTypeImpl sType = new StructTypeImpl(fieldNames, oTypes);
  return sType;
}
 

private static String toString(ResultSetMetaData metadata, int colNum) throws SQLException {
  return "ResultSetMetaData(" + colNum + ")[" +
      "columnName='" + metadata.getColumnName(colNum) + '\'' +
      ", type='" + metadata.getColumnType(colNum) + '\'' +
      ", nullable=" + metadata.isNullable(colNum) +
      ", displaySize=" + metadata.getColumnDisplaySize(colNum) +
      ", precision=" + metadata.getPrecision(colNum) +
      ", scale=" + metadata.getScale(colNum) +
      ", signed=" + metadata.isSigned(colNum) +
      ", className='" + metadata.getColumnClassName(colNum) + '\'' +
      ']';
}
 

public static Optional<Class<?>> getColumnClass(ResultSetMetaData metaData, int column)
	throws SQLException {
	String className = metaData.getColumnClassName(column);
	try {
		Class<?> clazz = Class.forName(className);
		return Optional.of(clazz);
	} catch (ClassNotFoundException e) {
		return Optional.empty();
	}
}
 

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

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
}
 

/**
 * <p>Factory method to create a new DynaProperty for the given index
 * into the result set metadata.</p>
 *
 * @param metadata is the result set metadata
 * @param i is the column index in the metadata
 * @return the newly created DynaProperty instance
 * @throws SQLException If an error occurs accessing the SQL metadata
 */
protected DynaProperty createDynaProperty(
                                final ResultSetMetaData metadata,
                                final int i)
                                throws SQLException {

    String columnName = null;
    if (useColumnLabel) {
        columnName = metadata.getColumnLabel(i);
    }
    if (columnName == null || columnName.trim().length() == 0) {
        columnName = metadata.getColumnName(i);
    }
    final String name = lowerCase ? columnName.toLowerCase() : columnName;
    if (!name.equals(columnName)) {
        if (columnNameXref == null) {
            columnNameXref = new HashMap<>();
        }
        columnNameXref.put(name, columnName);
    }
    String className = null;
    try {
        final int sqlType = metadata.getColumnType(i);
        switch (sqlType) {
            case java.sql.Types.DATE:
                return new DynaProperty(name, java.sql.Date.class);
            case java.sql.Types.TIMESTAMP:
                return new DynaProperty(name, java.sql.Timestamp.class);
            case java.sql.Types.TIME:
                return new DynaProperty(name, java.sql.Time.class);
            default:
                className = metadata.getColumnClassName(i);
        }
    } catch (final SQLException e) {
        // this is a patch for HsqlDb to ignore exceptions
        // thrown by its metadata implementation
    }

    // Default to Object type if no class name could be retrieved
    // from the metadata
    Class<?> clazz = Object.class;
    if (className != null) {
        clazz = loadClass(className);
    }
    return new DynaProperty(name, clazz);

}
 

public static JdbcColumnKey of(ResultSetMetaData metaData, int columnIndex) throws SQLException {
	return new JdbcColumnKey(metaData.getColumnLabel(columnIndex), columnIndex, metaData.getColumnType(columnIndex), metaData.getColumnClassName(columnIndex));
}
 

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

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

/**
 * 有选择性地将resultSet转换成List<Map>
 *
 * @param rs
 * @param exclude 不需要复制的属性名数组
 * @return
 * @throws Exception
 */
public static List<Map<String, Object>> resultSetToMap(ResultSet rs, String[] exclude)
        throws Exception {
    List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
    if (rs == null) {
        return list;
    }

    String formatStr = "yyyy-MM-dd HH:mm:ss";
    SimpleDateFormat sdf = new SimpleDateFormat(formatStr);

    rs.beforeFirst();
    while (rs.next()) {
        ResultSetMetaData rsmd = rs.getMetaData();
        Map<String, Object> map = new HashMap<String, Object>();
        int colNum = rsmd.getColumnCount();
        String columnName = "";
        String columnClassName = "";
        Date date = null;
        for (int i = 1; i <= colNum; i++) {
            columnName = rsmd.getColumnName(i).toUpperCase();
            columnClassName = rsmd.getColumnClassName(i);

            boolean excld = false;
            for (int j = 0; exclude != null && j < exclude.length; j++) {
                if (columnName.equalsIgnoreCase(exclude[j])) {
                    excld = true;
                    break;
                }
            }

            if (excld) {
                continue;
            }

            if ("java.sql.Timestamp".equals(columnClassName)) {
                date = rs.getDate(i);
                if (date == null) {
                    map.put(columnName.toUpperCase(), "");
                } else {
                    map.put(columnName.toUpperCase(), sdf.format(date));
                }
            } else {
                map.put(columnName.toUpperCase(), rs.getString(i));
            }
        }

        list.add(map);
    }

    return list;
}