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

/**
 * Extract a value for the single column in the current row.
 * <p>Validates that there is only one column selected,
 * then delegates to {@code getColumnValue()} and also
 * {@code convertValueToRequiredType}, if necessary.
 * @see java.sql.ResultSetMetaData#getColumnCount()
 * @see #getColumnValue(java.sql.ResultSet, int, Class)
 * @see #convertValueToRequiredType(Object, Class)
 */
@Override
@SuppressWarnings("unchecked")
public T mapRow(ResultSet rs, int rowNum) throws SQLException {
	// Validate column count.
	ResultSetMetaData rsmd = rs.getMetaData();
	int nrOfColumns = rsmd.getColumnCount();
	if (nrOfColumns != 1) {
		throw new IncorrectResultSetColumnCountException(1, nrOfColumns);
	}

	// Extract column value from JDBC ResultSet.
	Object result = getColumnValue(rs, 1, this.requiredType);
	if (result != null && this.requiredType != null && !this.requiredType.isInstance(result)) {
		// Extracted value does not match already: try to convert it.
		try {
			return (T) convertValueToRequiredType(result, this.requiredType);
		}
		catch (IllegalArgumentException ex) {
			throw new TypeMismatchDataAccessException(
					"Type mismatch affecting row number " + rowNum + " and column type '" +
					rsmd.getColumnTypeName(1) + "': " + ex.getMessage());
		}
	}
	return (T) result;
}
 

private void getMetaDataInfo() throws SQLException {
    ResultSetMetaData metaData = rs.getMetaData();
    columnCount                = metaData.getColumnCount();
    int numColumns             = columnCount;
    columnNames                = new String[numColumns];
    columnTypes                = new String[numColumns];
    columnLengths              = new int[numColumns];

    for (int i=0; i<numColumns; i++) {
        int jdbcTypeId = metaData.getColumnType(i+1);
        columnNames[i] = metaData.getColumnName(i+1);
        columnTypes[i] = metaData.getColumnTypeName(i+1);
        if(!ColumnInfo.importExportSupportedType(jdbcTypeId))
        {
            throw LoadError.nonSupportedTypeColumn(
                        columnNames[i], columnTypes[i]); 
        }
     
        columnLengths[i] = metaData.getColumnDisplaySize(i+1);
    }
}
 

private Map<String, String> getColumnData(Connection c, String tableName) throws SQLException {
    try (
        Statement stmt = c.createStatement()) {
        HashMap<String, String> data = new HashMap<>();
        stmt.setFetchSize(1);
        stmt.setMaxRows(1);
        System.out.println(tableName + ":");
        try (
            ResultSet rs = stmt.executeQuery("select * from " + tableName)) {
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            for (int i = 1; i <= columnCount; i++) {
                String columnName = rsmd.getColumnName(i);
                String columnType = rsmd.getColumnTypeName(i);
                data.put(columnName, columnType);
                System.out.println("    " + columnName + ":" + columnType);
            }
        }
        return data;
    }
}
 

BaseDatatype findFieldDataType(String field, ResultSet rs) {
	field = field.trim();
	if (verbose) log.info("Figuring out datatype of field: " + field);
	try {
		ResultSetMetaData rsMeta = rs.getMetaData();
		if (verbose) log.info("Table name " + rsMeta.getTableName(1));
		for (int i = 1; i <= rsMeta.getColumnCount(); i++) {
			if (verbose) log.info("Column name is " + rsMeta.getColumnName(i));
			if (rsMeta.getColumnName(i).equals(field)) {
				String sqlType = rsMeta.getColumnTypeName(i);
				if (verbose) log.info("Column " + i + " with name " + rsMeta.getColumnName(i) + " is of type " + sqlType);
				return util.findDataTypeFromSql(sqlType);
			}
		}
	} catch (SQLException e) {
		e.printStackTrace();
	}
	
	return null;
}
 

/**
 * Returns the array of column information from the result-set meta-data
 * @param metaData      the result set meta data
 * @param platform      the database platform
 * @param request       the request descriptor
 * @return              the array of column information
 * @throws SQLException if there is a database access error
 */
private List<ColumnInfo> getColumnInfo(ResultSetMetaData metaData, SQLPlatform platform, DbSourceOptions<R> request) throws SQLException {
    final int rowCapacity = request.getRowCapacity();
    final int columnCount = metaData.getColumnCount();
    final List<ColumnInfo> columnInfoList = new ArrayList<>(columnCount);
    final SQLType.TypeResolver typeResolver = SQLType.getTypeResolver(platform);
    for (int i=0; i<columnCount; ++i) {
        final int colIndex = i+1;
        final String colName = metaData.getColumnName(colIndex);
        if (!request.getExcludeColumnSet().contains(colName)) {
            final int typeCode = metaData.getColumnType(colIndex);
            final String typeName = metaData.getColumnTypeName(colIndex);
            final SQLType sqlType = typeResolver.getType(typeCode, typeName);
            final SQLExtractor extractor = request.getExtractors().getOrDefault(colName, SQLExtractor.with(sqlType.typeClass(), platform));
            columnInfoList.add(new ColumnInfo(i, colIndex, colName, rowCapacity, extractor));
        }
    }
    return columnInfoList;
}
 

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

/**
 * Extract a value for the single column in the current row.
 * <p>Validates that there is only one column selected,
 * then delegates to {@code getColumnValue()} and also
 * {@code convertValueToRequiredType}, if necessary.
 * @see java.sql.ResultSetMetaData#getColumnCount()
 * @see #getColumnValue(java.sql.ResultSet, int, Class)
 * @see #convertValueToRequiredType(Object, Class)
 */
@Override
@SuppressWarnings("unchecked")
@Nullable
public T mapRow(ResultSet rs, int rowNum) throws SQLException {
	// Validate column count.
	ResultSetMetaData rsmd = rs.getMetaData();
	int nrOfColumns = rsmd.getColumnCount();
	if (nrOfColumns != 1) {
		throw new IncorrectResultSetColumnCountException(1, nrOfColumns);
	}

	// Extract column value from JDBC ResultSet.
	Object result = getColumnValue(rs, 1, this.requiredType);
	if (result != null && this.requiredType != null && !this.requiredType.isInstance(result)) {
		// Extracted value does not match already: try to convert it.
		try {
			return (T) convertValueToRequiredType(result, this.requiredType);
		}
		catch (IllegalArgumentException ex) {
			throw new TypeMismatchDataAccessException(
					"Type mismatch affecting row number " + rowNum + " and column type '" +
					rsmd.getColumnTypeName(1) + "': " + ex.getMessage());
		}
	}
	return (T) result;
}
 

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
}
 

@Override
public List<AbstractParameterHost> extract(ResultSet rs) throws SQLException {
    List<AbstractParameterHost> hosts = new ArrayList<>();
    if (rs == null) {
        return hosts;
    }

    ResultSetMetaData metaData = rs.getMetaData();
    int count = metaData.getColumnCount();
    for (int i = 1; i <= count; i++) {
        CSharpParameterHost host = new CSharpParameterHost();
        String columnName = metaData.getColumnLabel(i);
        host.setName(columnName);
        String typeName = metaData.getColumnTypeName(i);
        boolean isUnsigned = DbUtils.isColumnUnsigned(typeName);
        int dataType = metaData.getColumnType(i);
        int length = metaData.getColumnDisplaySize(i);
        // 特殊处理
        DbType dbType = DbUtils.getDotNetDbType(typeName, dataType, length, isUnsigned, dbCategory);
        host.setDbType(dbType);
        String type = DbType.getCSharpType(host.getDbType());
        host.setType(type);
        host.setIdentity(false);
        host.setNullable(metaData.isNullable(i) == 1 ? true : false);
        host.setPrimary(false);
        host.setLength(length);
        host.setValueType(Consts.CSharpValueTypes.contains(host.getType()));
        hosts.add(host);
    }

    return hosts;
}
 

@Override
protected ColumnData createColumnData(ResultSet columnSet) throws SQLException {
	ColumnData columnData = super.createColumnData(columnSet);
	String type = Format.null2blank(columnData.type).toLowerCase();
	
	if (type.startsWith("timestamp")) {
		columnData.size = columnData.decimalDegits;
	}
	
	else if(type.startsWith("datetime")) {
		String tableName = columnSet.getString("TABLE_NAME");
		
		Statement st = con.createStatement();
		ResultSet rs = st.executeQuery("SELECT FIRST 1 * FROM " + tableName);
		ResultSetMetaData metaData = rs.getMetaData();

		for (int i = 0; i < metaData.getColumnCount(); i++)
		{
	            int col_no = i + 1;
	            String columnName = metaData.getColumnName(col_no);
	            String columnTypeName = metaData.getColumnTypeName(col_no);
	            
	            if(columnData.columnName.equals(columnName))
	            	columnData.type = columnTypeName;
		}
		
		if (columnData.type.indexOf("fraction") != -1) {
            final Pattern p = Pattern.compile("(.*fraction)\\((\\d+)\\)");
            final Matcher m = p.matcher(columnData.type);

            if (m.matches()) {
            	columnData.type = m.group(1);
                columnData.size = Integer.parseInt(m.group(2));
            }
		} 

	}
	
	return columnData;
}
 

public static ResultRow fromResultSet(ResultSet resultSet, 
		List<ProjectionSpec> projectionSpecs, SQLConnection database) 
throws SQLException {
	Map<ProjectionSpec,String> result = new HashMap<ProjectionSpec,String>();
	ResultSetMetaData metaData = resultSet.getMetaData();
	for (int i = 0; i < projectionSpecs.size(); i++) {
		ProjectionSpec key = projectionSpecs.get(i);
		int jdbcType = metaData == null ? Integer.MIN_VALUE : metaData.getColumnType(i + 1);
		String name = metaData == null ? "UNKNOWN" : metaData.getColumnTypeName(i + 1);
		result.put(key, database.vendor().getDataType(jdbcType, name.toUpperCase(), -1).value(resultSet, i + 1));
	}
	return new ResultRow(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);
	}
}
 

public static List<Attribute> getTableAttributes(ResultSet resultSet, String tableName) throws SQLException {
    List<Attribute> result = new ArrayList<Attribute>();
    ResultSetMetaData metadata = resultSet.getMetaData();
    int columns = metadata.getColumnCount();
    for (int col = 1; col <= columns; col++) {
        String attributeName = metadata.getColumnName(col);
        String attributeType = metadata.getColumnTypeName(col);
        Attribute attribute = new Attribute(tableName, attributeName, DBMSUtility.convertDBTypeToDataSourceType(attributeType));
        result.add(attribute);
    }
    return result;
}
 

public static void printColTypes(ResultSetMetaData rsmd, PrintWriter out) throws java.sql.SQLException {
  int columns = rsmd.getColumnCount();
  for (int i = 1; i <= columns; i++) {
    int jdbcType = rsmd.getColumnType(i);
    String name = rsmd.getColumnTypeName(i);
    out.write("Column " + i + " is JDBC type " + jdbcType);
    out.write(", which the DBMS calls " + name + "<br>\n");
  }
}
 

public cfData execute(cfSession _session, List<cfData> parameters) throws cfmRunTimeException {
	if (parameters.size() == 0) {
		cfComponentData activeComponent = _session.getActiveComponentData();
		if (activeComponent != null) {
			return activeComponent.getMetaData();
		} else {
			return new cfStructData(); // CFMX returns an empty struct
		}
	}

	cfData data = parameters.get(0);
	if (data instanceof cfComponentData) {
		return ((cfComponentData) data).getMetaData();
	} else if (data instanceof userDefinedFunction) {
		return ((userDefinedFunction) data).getMetaData();
	} else if (data instanceof cfQueryResultData) {
		try {
			ResultSetMetaData metaData = ((cfQueryResultData) data).getMetaData();
			int cols = metaData.getColumnCount();
			cfArrayData returnData = cfArrayData.createArray(1);
			for (int i = 1; i <= cols; i++) {
				cfStructData nextColInfo = new cfStructData();

				nextColInfo.setData("IsCaseSensitive", cfBooleanData.getcfBooleanData(metaData.isCaseSensitive(i)));
				nextColInfo.setData("Name", new cfStringData(metaData.getColumnName(i)));

				// if the type wasn't set in QueryNew then the type name won't be set so don't include it
				String colTypeName = metaData.getColumnTypeName(i);
				if (colTypeName != null) {
					nextColInfo.setData("TypeName", new cfStringData(colTypeName));
				}
				returnData.addElement(nextColInfo);
			}

			return returnData;
		} catch (SQLException e) {
			throwException(_session, "Failed to obtain query metadata due to SQLException: " + e.getMessage());
		}

	} else if (data instanceof cfJavaObjectData) { // undocumented handling of other types
		return new cfJavaObjectData(((cfJavaObjectData) data).getInstanceClass());
	}

	// CFMX doesn't throw an exception when the passed in element isn't a
	// component. Instead it returns a java.lang.Class object. We don't see how this object
	// can be useful so we return a null object instead. Refer to bug #2297.
	return cfNullData.NULL;
}
 

@SuppressWarnings("unchecked")
public static List<IdName> getValues(String select, Connection con) throws Exception {
    List values = new ArrayList();
    Dialect dialect;

    DatabaseMetaData dbmd = con.getMetaData();
    String dbName = dbmd.getDatabaseProductName();
    String dbVersion = dbmd.getDatabaseProductVersion();
    dialect = DialectFactory.determineDialect(dbName, dbVersion);

    ResultSet rs = null;
    Statement stmt = null;
    try {
        stmt = con.createStatement();
        rs = stmt.executeQuery(select);
        ResultSetMetaData rsmd = rs.getMetaData();
        String type = rsmd.getColumnTypeName(1);
        int precision = rsmd.getPrecision(1);
        int scale = rsmd.getScale(1);
        int typeCode = dialect.getJdbcType(type, precision, scale);
        while (rs.next()) {
            Serializable s;
            switch (typeCode) {
                case Types.INTEGER:
                    s = rs.getInt(1);
                    break;
                case Types.DOUBLE:
                    s = rs.getDouble(1);
                    break;
                case Types.DATE:
                    s = rs.getDate(1);
                    break;
                case Types.VARCHAR:
                    s = rs.getString(1);
                    break;
                default:
                    s = rs.getString(1);
                    break;
            }
            IdName in = new IdName();
            in.setId(s);
            in.setName(rs.getString(2));
            values.add(in);                
        }
    } finally {
        ConnectionUtil.closeResultSet(rs);
        ConnectionUtil.closeStatement(stmt);
    }
    return values;
}
 

protected void getOriginalColumnMetadata( ValueMetaInterface 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 );
}
 

@Override
public JsonArray getSchema(CommandOutput<?, ?> response) throws SchemaException, IOException {
  log.debug("Extract schema from resultset");
  ResultSet resultset = null;
  Iterator<ResultSet> itr = (Iterator<ResultSet>) response.getResults().values().iterator();
  if (itr.hasNext()) {
    resultset = itr.next();
  } else {
    throw new SchemaException("Failed to get schema from Teradata - empty schema resultset");
  }

  JsonArray fieldJsonArray = new JsonArray();
  try {
      Schema schema = new Schema();
      ResultSetMetaData rsmd = resultset.getMetaData();

      String columnName, columnTypeName;
      for (int i = 1; i <= rsmd.getColumnCount(); i++) {
        columnName = rsmd.getColumnName(i);
        columnTypeName = rsmd.getColumnTypeName(i);

        schema.setColumnName(columnName);

        List<String> mapSymbols = null;
        JsonObject newDataType = this.convertDataType(columnName, columnTypeName, ELEMENT_DATA_TYPE, mapSymbols);

        schema.setDataType(newDataType);
        schema.setLength(rsmd.getColumnDisplaySize(i));
        schema.setPrecision(rsmd.getPrecision(i));
        schema.setScale(rsmd.getScale(i));
        schema.setNullable(rsmd.isNullable(i) == ResultSetMetaData.columnNullable);
        schema.setComment(rsmd.getColumnLabel(i));

        String jsonStr = gson.toJson(schema);
        JsonObject obj = gson.fromJson(jsonStr, JsonObject.class).getAsJsonObject();
        fieldJsonArray.add(obj);
      }

  } catch (Exception e) {
    throw new SchemaException("Failed to get schema from Teradaa; error - " + e.getMessage(), e);
  }

  return fieldJsonArray;
}
 

private static SQLResponse executeAndConvertResult(PreparedStatement preparedStatement) throws SQLException {
    if (preparedStatement.execute()) {
        ResultSetMetaData metaData = preparedStatement.getMetaData();
        ResultSet resultSet = preparedStatement.getResultSet();
        List<Object[]> rows = new ArrayList<>();
        List<String> columnNames = new ArrayList<>(metaData.getColumnCount());
        DataType[] dataTypes = new DataType[metaData.getColumnCount()];
        for (int i = 0; i < metaData.getColumnCount(); i++) {
            columnNames.add(metaData.getColumnName(i + 1));
        }
        while (resultSet.next()) {
            Object[] row = new Object[metaData.getColumnCount()];
            for (int i = 0; i < row.length; i++) {
                Object value;
                String typeName = metaData.getColumnTypeName(i + 1);
                value = getObject(resultSet, i, typeName);
                row[i] = value;
            }
            rows.add(row);
        }
        return new SQLResponse(
            columnNames.toArray(new String[0]),
            rows.toArray(new Object[0][]),
            dataTypes,
            rows.size()
        );
    } else {
        int updateCount = preparedStatement.getUpdateCount();
        if (updateCount < 0) {
            /*
             * In Crate -1 means row-count unknown, and -2 means error. In JDBC -2 means row-count unknown and -3 means error.
             * See {@link java.sql.Statement#EXECUTE_FAILED}
             */
            updateCount += 1;
        }
        return new SQLResponse(
            new String[0],
            new Object[0][],
            new DataType[0],
            updateCount
        );
    }
}
 

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