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

The following examples show how to use java.sql.DatabaseMetaData#columnNullable() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: MetaImpl.java    From calcite-avatica with Apache License 2.0 6 votes vote down vote up 
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;
}
Example 2
Source File: ColumnMetaData.java    From calcite-avatica with Apache License 2.0 6 votes vote down vote up 
/** 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());
}
Example 3
Source File: CalcitePrepareImpl.java    From Quicksql with MIT License 5 votes vote down vote up 
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());
}
Example 4
Source File: JDBCStorableIntrospector.java    From Carbonado with Apache License 2.0 5 votes vote down vote up 
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;
}
Example 5
Source File: CalcitePrepareImpl.java    From calcite with Apache License 2.0 5 votes vote down vote up 
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());
}
Example 6
Source File: JDBCAvroRegistryString.java    From components with Apache License 2.0 5 votes vote down vote up 
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);
    }
}
Example 7
Source File: JDBCAvroRegistry.java    From components with Apache License 2.0 5 votes vote down vote up 
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);
    }
}
Example 8
Source File: PlanExecutor.java    From quark with Apache License 2.0 5 votes vote down vote up 
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());
}
Example 9
Source File: ProtobufTranslationImplTest.java    From calcite-avatica with Apache License 2.0 5 votes vote down vote up 
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");
}
Example 10
Source File: JDBCUtils.java    From netbeans with Apache License 2.0 5 votes vote down vote up 
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;
    }
}
Example 11
Source File: ImportRecipientsDaoImpl.java    From openemm with GNU Affero General Public License v3.0 5 votes vote down vote up 
@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;
}
Example 12
Source File: TableColumnInfo.java    From reladomo with Apache License 2.0 4 votes vote down vote up 
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;
}
Example 13
Source File: TableColumnInfo.java    From reladomo with Apache License 2.0 4 votes vote down vote up 
/**
 * 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()]));
}
Example 14
Source File: MetaImpl.java    From calcite-avatica with Apache License 2.0 4 votes vote down vote up 
private static int intForColumnNullable(boolean nullable) {
  return nullable ? DatabaseMetaData.columnNullable : DatabaseMetaData.columnNoNulls;
}
Example 15
Source File: SchemaInferer.java    From components with Apache License 2.0 4 votes vote down vote up 
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);
    }
}
Example 16
Source File: DBConnectionTest.java    From Mario with Apache License 2.0 4 votes vote down vote up 
@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());
}
Example 17
Source File: KylinColumnMetaData.java    From Kylin with Apache License 2.0 4 votes vote down vote up 
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);
}