Java Code Examples for java.sql.DatabaseMetaData#columnNullable()

protected static int getColumnNullability(Field field) {
  // Check annotations first
  if (field.isAnnotationPresent(ColumnNoNulls.class)) {
    return DatabaseMetaData.columnNoNulls;
  }

  if (field.isAnnotationPresent(ColumnNullable.class)) {
    return DatabaseMetaData.columnNullable;
  }

  if (field.isAnnotationPresent(ColumnNullableUnknown.class)) {
    return DatabaseMetaData.columnNullableUnknown;
  }

  // check the field type to decide if annotated, as a fallback
  if (field.getType().isPrimitive()) {
    return DatabaseMetaData.columnNoNulls;
  }

  return DatabaseMetaData.columnNullable;
}
 

/** Creates a ColumnMetaData for result sets that are not based on a struct
 * but need to have a single 'field' for purposes of
 * {@link java.sql.ResultSetMetaData}. */
public static ColumnMetaData dummy(AvaticaType type, boolean nullable) {
  return new ColumnMetaData(
      0,
      false,
      true,
      false,
      false,
      nullable
          ? DatabaseMetaData.columnNullable
          : DatabaseMetaData.columnNoNulls,
      true,
      -1,
      null,
      null,
      null,
      -1,
      -1,
      null,
      null,
      type,
      true,
      false,
      false,
      type.columnClassName());
}
 

private ColumnMetaData metaData(JavaTypeFactory typeFactory, int ordinal,
    String fieldName, RelDataType type, RelDataType fieldType,
    List<String> origins) {
  final ColumnMetaData.AvaticaType avaticaType =
      avaticaType(typeFactory, type, fieldType);
  return new ColumnMetaData(
      ordinal,
      false,
      true,
      false,
      false,
      type.isNullable()
          ? DatabaseMetaData.columnNullable
          : DatabaseMetaData.columnNoNulls,
      true,
      type.getPrecision(),
      fieldName,
      origin(origins, 0),
      origin(origins, 2),
      getPrecision(type),
      getScale(type),
      origin(origins, 1),
      null,
      avaticaType,
      true,
      false,
      false,
      avaticaType.columnClassName());
}
 

ColumnInfo(ResultSet rs) throws SQLException {
    columnName = intern(rs.getString("COLUMN_NAME"));
    dataTypeName = intern(rs.getString("TYPE_NAME"));
    columnSize = rs.getInt("COLUMN_SIZE");
    decimalDigits = rs.getInt("DECIMAL_DIGITS");
    nullable = rs.getInt("NULLABLE") == DatabaseMetaData.columnNullable;
    charOctetLength = rs.getInt("CHAR_OCTET_LENGTH");
    ordinalPosition = rs.getInt("ORDINAL_POSITION");

    int dt = rs.getInt("DATA_TYPE");
    if (dt == OTHER) {
        if ("BLOB".equalsIgnoreCase(dataTypeName)) {
            dt = BLOB;
        } else if ("CLOB".equalsIgnoreCase(dataTypeName)) {
            dt = CLOB;
        } else if ("FLOAT".equalsIgnoreCase(dataTypeName)) {
            dt = FLOAT;
        } else if ("TIMESTAMP".equalsIgnoreCase(dataTypeName)) {
            dt = TIMESTAMP;
        } else if (dataTypeName.toUpperCase().contains("TIMESTAMP")) {
            dt = TIMESTAMP;
        } else if (dataTypeName.equalsIgnoreCase("INT UNSIGNED")) {
            dt = BIGINT;
        }
    } else if (dt == LONGVARBINARY && "BLOB".equalsIgnoreCase(dataTypeName)) {
        // Workaround MySQL bug.
        dt = BLOB;
    } else if (dt == LONGVARCHAR && "CLOB".equalsIgnoreCase(dataTypeName)) {
        // Workaround MySQL bug.
        dt = CLOB;
    }

    dataType = dt;
}
 

private ColumnMetaData metaData(JavaTypeFactory typeFactory, int ordinal,
    String fieldName, RelDataType type, RelDataType fieldType,
    List<String> origins) {
  final ColumnMetaData.AvaticaType avaticaType =
      avaticaType(typeFactory, type, fieldType);
  return new ColumnMetaData(
      ordinal,
      false,
      true,
      false,
      false,
      type.isNullable()
          ? DatabaseMetaData.columnNullable
          : DatabaseMetaData.columnNoNulls,
      true,
      type.getPrecision(),
      fieldName,
      origin(origins, 0),
      origin(origins, 2),
      getPrecision(type),
      getScale(type),
      origin(origins, 1),
      null,
      avaticaType,
      true,
      false,
      false,
      avaticaType.columnClassName());
}
 

protected Schema inferSchemaResultSet(JDBCTableMetadata tableMetadata) throws SQLException {
    DatabaseMetaData databaseMetdata = tableMetadata.getDatabaseMetaData();

    Set<String> keys = getPrimaryKeys(databaseMetdata, tableMetadata.getCatalog(), tableMetadata.getDbSchema(),
            tableMetadata.getTablename());

    Set<String> existNames = new HashSet<String>();
    int index = 0;
    
    try (ResultSet metadata = databaseMetdata.getColumns(tableMetadata.getCatalog(), tableMetadata.getDbSchema(),
            tableMetadata.getTablename(), null)) {
        if (!metadata.next()) {
            return null;
        }

        List<Field> fields = new ArrayList<>();
        String tablename = metadata.getString("TABLE_NAME");

        do {
            int size = metadata.getInt("COLUMN_SIZE");
            int scale = metadata.getInt("DECIMAL_DIGITS");
            int dbtype = metadata.getInt("DATA_TYPE");
            boolean nullable = DatabaseMetaData.columnNullable == metadata.getInt("NULLABLE");

            String columnName = metadata.getString("COLUMN_NAME");
            boolean isKey = keys.contains(columnName);

            String defaultValue = metadata.getString("COLUMN_DEF");

            String validName = NameUtil.correct(columnName, index++, existNames);
            existNames.add(validName);
            
            Field field = sqlType2Avro(size, scale, dbtype, nullable, validName, columnName, defaultValue, isKey);

            fields.add(field);
        } while (metadata.next());

        return Schema.createRecord(NameUtil.correct(tablename, 0, new HashSet<String>()), null, null, false, fields);
    }
}
 

protected Schema inferSchemaResultSet(JDBCTableMetadata tableMetadata) throws SQLException {
    DatabaseMetaData databaseMetdata = tableMetadata.getDatabaseMetaData();

    Set<String> keys = getPrimaryKeys(databaseMetdata, tableMetadata.getCatalog(), tableMetadata.getDbSchema(),
            tableMetadata.getTablename());

    try (ResultSet metadata = databaseMetdata.getColumns(tableMetadata.getCatalog(), tableMetadata.getDbSchema(),
            tableMetadata.getTablename(), null)) {
        if (!metadata.next()) {
            return null;
        }

        List<Field> fields = new ArrayList<>();
        String tablename = metadata.getString("TABLE_NAME");

        do {
            int size = metadata.getInt("COLUMN_SIZE");
            int scale = metadata.getInt("DECIMAL_DIGITS");
            int dbtype = metadata.getInt("DATA_TYPE");
            boolean nullable = DatabaseMetaData.columnNullable == metadata.getInt("NULLABLE");

            String columnName = metadata.getString("COLUMN_NAME");
            boolean isKey = keys.contains(columnName);

            String defaultValue = metadata.getString("COLUMN_DEF");
            boolean isAutoIncremented = checkAutoIncremented(metadata);
            Field field = sqlType2Avro(size, scale, dbtype, nullable, columnName, columnName, defaultValue, isKey, isAutoIncremented);

            fields.add(field);
        } while (metadata.next());

        return Schema.createRecord(
                AvroNamesValidationHelper.getAvroCompatibleName(NameUtil.correct(tablename, 0, Collections.<String>emptySet())), null, null, false, fields);
    }
}
 

private ColumnMetaData metaData(JavaTypeFactory typeFactory, int ordinal,
                                String fieldName, RelDataType type, RelDataType fieldType,
                                List<String> origins) {
  final ColumnMetaData.AvaticaType avaticaType =
      avaticaType(typeFactory, type, fieldType);
  return new ColumnMetaData(
      ordinal,
      false,
      true,
      false,
      false,
      type.isNullable()
          ? DatabaseMetaData.columnNullable
          : DatabaseMetaData.columnNoNulls,
      true,
      type.getPrecision(),
      fieldName,
      origin(origins, 0),
      origin(origins, 2),
      getPrecision(type),
      getScale(type),
      origin(origins, 1),
      null,
      avaticaType,
      true,
      false,
      false,
      avaticaType.columnClassName());
}
 

private static ColumnMetaData getArrayColumnMetaData(ScalarType componentType, int index,
    String name) {
  ArrayType arrayType = ColumnMetaData.array(componentType, "Array", Rep.ARRAY);
  return new ColumnMetaData(
      index, false, true, false, false, DatabaseMetaData.columnNullable,
      true, -1, name, name, null,
      0, 0, null, null, arrayType, true, false, false,
      "ARRAY");
}
 

public static Nullable getColumnNullable(int dbmdColumnNullable) {
    switch (dbmdColumnNullable) {
        case DatabaseMetaData.columnNoNulls:
            return Nullable.NOT_NULLABLE;
        case DatabaseMetaData.columnNullable:
            return Nullable.NULLABLE;
        case DatabaseMetaData.columnNullableUnknown:
        default:
            return Nullable.UNKNOWN;
    }
}
 

@Override
public Map<String, Map<String, Object>> getColumnInfoByColumnName(@VelocityCheck int companyId, String column) {
	LinkedHashMap<String, Map<String, Object>> list = new LinkedHashMap<>();
	try(final Connection connection = getDataSource().getConnection()) {

		DatabaseMetaData metaData = connection.getMetaData();

		boolean isOracle = isOracleDB();

		String tableName = "customer_" + companyId + "_tbl";

		String schemaPattern = isOracle ? metaData.getUserName() : null;
		String tableNamePattern = isOracle ? tableName.toUpperCase() : tableName.toLowerCase();
		String columnNamePattern = isOracle ? column.toUpperCase() : column;
		
		try(ResultSet resultSet = metaData.getColumns(null, schemaPattern, tableNamePattern, columnNamePattern)) {
			if (resultSet != null) {
				while (resultSet.next()) {
					Map<String, Object> mapping = new HashMap<>();
					mapping.put("column", resultSet.getString(4).toLowerCase());
					mapping.put("shortname", resultSet.getString(4).toLowerCase());
					mapping.put("type", ImportUtils.dbtype2string(resultSet.getInt(5)));
					mapping.put("length", resultSet.getInt(7));
					if (resultSet.getInt(11) == DatabaseMetaData.columnNullable) {
						mapping.put("nullable", 1);
					} else {
						mapping.put("nullable", 0);
					}

					list.put((String) mapping.get("shortname"), mapping);
				}
			}
		}
		
	} catch(Exception e) {
		logger.error(MessageFormat.format("Failed to get column ({0}) info for companyId ({1})", column, companyId), e);
	}
	
	return list;
}
 

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

/**
 * Creates the <code>TableColumnInfo</code> using the standard JDBC table metadata mechanisms.
 *
 * @param connection A connection to lookup the <code>TableColumnInfo</code> data on.
 * @param schema The schema of the table, or <code>null</code> if none should be used to lookup the table.
 * @param tableName The name of the table.
 * @param fullyQualifiedTableName
 * @return The metadata about the table.
 * @throws SQLException if there was a problem looking up the metadata.
 */
public static TableColumnInfo createTableMetadata(Connection connection, String schema, String tableName, String fullyQualifiedTableName)
throws SQLException
{
    DatabaseMetaData metaData = connection.getMetaData();
    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;
    boolean tableExists = false;

    try
    {
        resultSet = metaData.getTables(null, schema, tableName, null);

        if (resultSet.next())
        {
            catalog = resultSet.getString("TABLE_CAT");
            dbSchema = resultSet.getString("TABLE_SCHEM");
            dbName = resultSet.getString("TABLE_NAME");

            // TABLE_SCHEM could be null so use the passed in schema name if this is the case
            if (dbSchema == null)
            {
                dbSchema = schema;
            }

            // Make sure we didn't get too many tables
            if (resultSet.next())
            {
                throw new SQLException("Too many tables matching tablename '" + tableName + "' and schema '" + schema + "'.");
            }

            tableExists = true;
        }
    }
    finally
    {
        close(resultSet);
    }

    // Only continue if the table exists
    if (! tableExists)
    {
        return null;
    }

    // Get hold of the column metadata
    List columns = new ArrayList();
    try
    {
        resultSet = metaData.getColumns(null, schema, tableName, null);

        while (resultSet.next())
        {
            String columnName = resultSet.getString("COLUMN_NAME");
            int dataType = resultSet.getInt("DATA_TYPE");
            int columnSize = resultSet.getInt("COLUMN_SIZE");
            int scale = resultSet.getInt("DECIMAL_DIGITS");
            int ordinalPosition = resultSet.getInt("ORDINAL_POSITION");
            int nullable = resultSet.getInt("NULLABLE");
            ColumnInfo columnInfo = new ColumnInfo(
                    columnName,
                    dataType,
                    columnSize,
                    columnSize,
                    scale,
                    ordinalPosition,
                    nullable == DatabaseMetaData.columnNullable
            );

            columns.add(columnInfo);
        }
    }
    finally
    {
        close(resultSet);
    }

    return new TableColumnInfo(catalog, dbSchema, dbName, (ColumnInfo[]) columns.toArray(new ColumnInfo[columns.size()]));
}
 

private static int intForColumnNullable(boolean nullable) {
  return nullable ? DatabaseMetaData.columnNullable : DatabaseMetaData.columnNoNulls;
}
 

public static Schema infer(JDBCTableMetadata tableMetadata, Dbms mapping, boolean enableSpecialTableName) throws SQLException {
    DatabaseMetaData databaseMetdata = tableMetadata.getDatabaseMetaData();

    Set<String> keys = getPrimaryKeys(databaseMetdata, tableMetadata.getCatalog(), tableMetadata.getDbSchema(),
            tableMetadata.getTablename());

    Set<String> existNames = new HashSet<String>();
    int index = 0;
    
    try (ResultSet metadata = databaseMetdata.getColumns(tableMetadata.getCatalog(), tableMetadata.getDbSchema(),
            tableMetadata.getTablename(), null)) {
        if (!metadata.next()) {
            return null;
        }

        List<Field> fields = new ArrayList<>();
        String tablename = metadata.getString("TABLE_NAME");

        do {
            int size = metadata.getInt("COLUMN_SIZE");
            int scale = metadata.getInt("DECIMAL_DIGITS");
            int dbtype = metadata.getInt("DATA_TYPE");
            boolean nullable = DatabaseMetaData.columnNullable == metadata.getInt("NULLABLE");

            String columnName = metadata.getString("COLUMN_NAME");
            boolean isKey = keys.contains(columnName);

            String defaultValue = metadata.getString("COLUMN_DEF");
            
            String columnTypeName = metadata.getString("TYPE_NAME");
            
            String validName = NameUtil.correct(columnName, index++, existNames);
            existNames.add(validName);

            Field field = sqlType2Avro(size, scale, dbtype, nullable, validName, columnName, defaultValue, isKey, mapping,
                    columnTypeName);
            if(enableSpecialTableName && !validName.equals(columnName)){
                field.addProp(ENABLE_SPECIAL_TABLENAME,"true");
            }
            fields.add(field);
        } while (metadata.next());

        return Schema.createRecord(NameUtil.correct(tablename, 0, new HashSet<String>()), null, null, false, fields);
    }
}
 

@Test
public void testDBConnection() throws SQLException {
    DBConnection dbConnection = new DBConnection();
    Connection connection = dbConnection.getConn();

    DatabaseMetaData metaData = connection.getMetaData();
    ResultSet columns = metaData.getColumns(null, schema, table, null);
    List<SqlColumn> sqlColumns = new ArrayList<SqlColumn>();

    String COLUMN_NAME = "COLUMN_NAME";
    String DATA_TYPE = "DATA_TYPE";
    String TYPE_NAME = "TYPE_NAME";
    String COLUMN_SIZE = "COLUMN_SIZE";
    String DECIMAL_DIGITS = "DECIMAL_DIGITS";
    String NULLABLE = "NULLABLE";
    String COLUMN_DEF = "COLUMN_DEF";
    String REMARKS = "REMARKS";

    while (columns.next()) {
        String colname = columns.getString(COLUMN_NAME);
        short coltype = columns.getShort(DATA_TYPE);
        int colsize = columns.getInt(COLUMN_SIZE);
        int digits = 0;
        String coltypname = columns.getString(TYPE_NAME).toUpperCase();
        boolean nullable = false;
        boolean withDefault = true;
        String remark = columns.getString(REMARKS);

        if (coltype == Types.DECIMAL || coltype == Types.NUMERIC) {
            digits = columns.getInt(DECIMAL_DIGITS);
        }
        if (columns.getInt(NULLABLE) == DatabaseMetaData.columnNullable) nullable = true;
        else nullable = false;
        withDefault = (columns.getString(COLUMN_DEF) != null);

        sqlColumns.add(new SqlColumn(colname, colname, coltype, colsize, digits, coltypname,
                nullable, withDefault, remark));
    }

    columns.close();

    assertTrue(sqlColumns.size() > 0);
    assertEquals(3, sqlColumns.size());
}
 

public static ColumnMetaData dummy(int ordinal, String label, String columnName, AvaticaType type, boolean nullable) {
    return new ColumnMetaData(ordinal, false, true, false, false, nullable ? DatabaseMetaData.columnNullable : DatabaseMetaData.columnNoNulls, true, -1, label, columnName, null, -1, -1, null, null, type, true, false, false, null);
}