Example usage for java.sql DatabaseMetaData columnNullable

List of usage examples for java.sql DatabaseMetaData columnNullable

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. DatabaseMetaData
  6. columnNullable

Introduction

In this page you can find the example usage for java.sql DatabaseMetaData columnNullable.

Prototype

int columnNullable

To view the source code for java.sql DatabaseMetaData columnNullable.

Click Source Link

Document

Indicates that the column definitely allows NULL values.

Usage

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getMySqlConnection();
    System.out.println("Got Connection.");

    ResultSet rsColumns = null;/*www. j av  a2  s  . co  m*/
    DatabaseMetaData meta = conn.getMetaData();
    rsColumns = meta.getColumns(null, "%", "code", "%");

    while (rsColumns.next()) {
        String columnType = rsColumns.getString("TYPE_NAME");
        String columnName = rsColumns.getString("COLUMN_NAME");
        int size = rsColumns.getInt("COLUMN_SIZE");
        int nullable = rsColumns.getInt("NULLABLE");
        int position = rsColumns.getInt("ORDINAL_POSITION");

        System.out.println("column name=" + columnName);
        System.out.println("type=" + columnType);
        System.out.println("size=" + size);
        if (nullable == DatabaseMetaData.columnNullable) {
            System.out.println("nullable is true");
        } else {
            System.out.println("nullable is false");
        }
        System.out.println("position" + position);
    }

    conn.close();
}

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getMySqlConnection();
    System.out.println("Got Connection.");
    Statement st = conn.createStatement();
    st.executeUpdate("drop table survey;");
    st.executeUpdate("create table survey (id int,name varchar(30));");
    st.executeUpdate("insert into survey (id,name ) values (1,'nameValue')");

    ResultSet rsColumns = null;/*from w  w w. j a  va 2  s.c o m*/
    DatabaseMetaData meta = conn.getMetaData();
    rsColumns = meta.getColumns(null, null, "survey", null);
    while (rsColumns.next()) {
        String columnName = rsColumns.getString("COLUMN_NAME");
        System.out.println("column name=" + columnName);
        String columnType = rsColumns.getString("TYPE_NAME");
        System.out.println("type:" + columnType);
        int size = rsColumns.getInt("COLUMN_SIZE");
        System.out.println("size:" + size);
        int nullable = rsColumns.getInt("NULLABLE");
        if (nullable == DatabaseMetaData.columnNullable) {
            System.out.println("nullable true");
        } else {
            System.out.println("nullable false");
        }
        int position = rsColumns.getInt("ORDINAL_POSITION");
        System.out.println("position:" + position);

    }

    st.close();
    conn.close();
}

From source file:ca.sqlpower.sqlobject.TestSQLTable.java

@Override
protected void setUp() throws Exception {
    super.setUp();

    sqlx("CREATE TABLE REGRESSION_TEST1 (t1_c1 numeric(10), t1_c2 numeric(5))");
    sqlx("CREATE TABLE REGRESSION_TEST2 (t2_c1 char(10))");
    sqlx("CREATE VIEW REGRESSION_TEST1_VIEW AS SELECT * FROM REGRESSION_TEST1");

    sqlx("CREATE TABLE SQL_TABLE_POPULATE_TEST (\n" + " cow numeric(10) NOT NULL, \n"
            + " CONSTRAINT test4pk PRIMARY KEY (cow))");
    sqlx("CREATE TABLE SQL_TABLE_1_POPULATE_TEST (\n" + " cow numeric(10) NOT NULL, \n"
            + " CONSTRAINT test5pk PRIMARY KEY(cow))");
    sqlx("ALTER TABLE SQL_TABLE_1_POPULATE_TEST " + "ADD CONSTRAINT TEST_FK FOREIGN KEY (cow) "
            + "REFERENCES SQL_TABLE_POPULATE_TEST (cow)");

    table = new SQLTable(null, true);
    table.setParent(new StubSQLObject());

    table.addColumn(new SQLColumn(table, "one", Types.INTEGER, 10, 0));
    table.addColumn(new SQLColumn(table, "two", Types.INTEGER, 10, 0));
    table.addColumn(new SQLColumn(table, "three", Types.INTEGER, 10, 0));
    table.addColumn(new SQLColumn(table, "four", Types.INTEGER, 10, 0));
    table.addColumn(new SQLColumn(table, "five", Types.INTEGER, 10, 0));
    table.addColumn(new SQLColumn(table, "six", Types.INTEGER, 10, 0));
    table.getPrimaryKeyIndex().addIndexColumn(table.getColumn(0));
    table.getPrimaryKeyIndex().addIndexColumn(table.getColumn(1));
    table.getPrimaryKeyIndex().addIndexColumn(table.getColumn(2));
    table.getColumn(0).setNullable(DatabaseMetaData.columnNullable);
    table.getColumn(0).setAutoIncrement(true);

    db.addTable(table);/* w ww. j  ava  2  s. com*/
}

From source file:org.agnitas.dao.impl.ImportRecipientsDaoImpl.java

@Override
public LinkedHashMap<String, Map<String, Object>> getColumnInfoByColumnName(int companyId, String column) {
    DataSource ds = (DataSource) applicationContext.getBean("dataSource");
    LinkedHashMap<String, Map<String, Object>> list = new LinkedHashMap<String, Map<String, Object>>();
    ResultSet resultSet = null;// w  w w. ja v a2  s.  co  m

    Connection con = DataSourceUtils.getConnection(ds);
    try {
        if (AgnUtils.isOracleDB()) {
            resultSet = con.getMetaData().getColumns(null,
                    AgnUtils.getDefaultValue("jdbc.username").toUpperCase(), "CUSTOMER_" + companyId + "_TBL",
                    column.toUpperCase());
        } else {
            resultSet = con.getMetaData().getColumns(null, null, "customer_" + companyId + "_tbl", column);
        }
        if (resultSet != null) {
            while (resultSet.next()) {
                String type;
                String col = resultSet.getString(4).toLowerCase();
                Map<String, Object> mapping = new HashMap<String, Object>();

                mapping.put("column", col);
                mapping.put("shortname", col);
                type = ImportUtils.dbtype2string(resultSet.getInt(5));
                mapping.put("type", type);
                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);
            }
        }
        resultSet.close();
    } catch (Exception e) {
        logger.error(MessageFormat.format("Failed to get column ({0}) info for admin ({1})", column, companyId),
                e); // TODO: Check this: is "admin" in combination with companyId correct here???
    } finally {
        DataSourceUtils.releaseConnection(con, ds);
    }
    return list;

}

From source file:ca.sqlpower.sqlobject.TestSQLColumn.java

public void testPKAttributes() throws Exception {
    SQLColumn cowCol = table1pk.getColumnByName("cow");
    SQLColumn fooCol = table1pk.getColumnByName("foo");

    // check for PK vs non PK attributes
    assertTrue("table1pk.cow should have been flagged as PK", cowCol.isPrimaryKey());
    assertEquals("table1pk.cow nullability incorrect", cowCol.getNullable(), DatabaseMetaData.columnNoNulls);
    assertFalse("table1pk.cow isDefinitelyNullable incorrect", cowCol.isDefinitelyNullable());

    assertFalse("table1pk.foo should NOT have been flagged as PK", fooCol.isPrimaryKey());
    assertEquals("table1pk.foo nullability incorrect", fooCol.getNullable(), DatabaseMetaData.columnNullable);
    assertTrue("table1pk.foo isDefinitelyNullable incorrect", fooCol.isDefinitelyNullable());
}

From source file:ca.sqlpower.sqlobject.TestSQLColumn.java

public void testMegaConstructor() throws Exception {
    SQLColumn col = new SQLColumn(table0pk, "test_column_2", Types.INTEGER, "my_test_integer", 44, 33,
            DatabaseMetaData.columnNullable, "test remarks", "test default", true);
    assertEquals(table0pk, col.getParent());
    assertEquals("test_column_2", col.getName());
    assertEquals(Types.INTEGER, col.getType());
    assertEquals("my_test_integer", col.getSourceDataTypeName());
    assertEquals(44, col.getPrecision());
    assertEquals(33, col.getScale());//from   w w  w  . ja v a 2  s  .co m
    assertEquals(DatabaseMetaData.columnNullable, col.getNullable());
    assertEquals("test remarks", col.getRemarks());
    assertEquals("test default", col.getDefaultValue());
    assertTrue(col.isAutoIncrement());
    assertEquals(1, col.getReferenceCount());
    assertEquals(1, col.getChildCount());
}

From source file:databaseadapter.GenerateMojo.java

protected Map<Table, List<Column>> collectColumns(Collection<Table> tables) throws MojoExecutionException {
    ResultSet rscolumns = null;/*from ww w  .  j a v  a2 s  . co m*/
    try {
        Map<Table, List<Column>> map = new LinkedHashMap<Table, List<Column>>();
        DatabaseMetaData metaData = connection.getMetaData();
        rscolumns = metaData.getColumns(null, schemaPattern, "%", "%");
        while (rscolumns.next()) {
            String tableName = rscolumns.getString("TABLE_NAME");
            Table table = find(tables, tableName);
            if (table == null)
                continue;

            List<Column> columns = map.get(table);
            if (columns == null) {
                columns = new LinkedList<Column>();
                map.put(table, columns);
            }

            String columnName = rscolumns.getString("COLUMN_NAME");
            int dataType = rscolumns.getInt("DATA_TYPE");
            int columnSize = rscolumns.getInt("COLUMN_SIZE");
            int decimalDigits = rscolumns.getInt("DECIMAL_DIGITS");
            int nullable = rscolumns.getInt("NULLABLE");
            String remarks = rscolumns.getString("REMARKS");

            Column column = new Column(columnName, dataType, columnSize, decimalDigits,
                    nullable == DatabaseMetaData.columnNullable, remarks);
            columns.add(column);
        }

        return map;
    } catch (SQLException e) {
        throw new MojoExecutionException("Unable to generate database adapter due to a '"
                + e.getClass().getName() + "' with message '" + e.getMessage() + "'", e);
    } finally {
        if (rscolumns != null) {
            try {
                rscolumns.close();
            } catch (SQLException ignore) {
            }
        }
    }
}

From source file:ca.sqlpower.sqlobject.TestSQLTable.java

public void testMoveToPKClearsNullability() throws SQLObjectException {
    SQLTable t = db.getTableByName("REGRESSION_TEST1");
    SQLColumn c = t.getColumnByName("t1_c1");
    assertFalse("Column shouldn't be in PK to begin", c.isPrimaryKey());
    c.setNullable(DatabaseMetaData.columnNullable);

    // Now c is not in the PK and is nullable.  Let's add it to PK
    t.changeColumnIndex(0, 0, true);//from  w ww .  ja  v  a 2  s  .co m

    assertTrue(c.isPrimaryKey());
    assertEquals(DatabaseMetaData.columnNoNulls, c.getNullable());
}

From source file:de.erdesignerng.dialect.JDBCReverseEngineeringStrategy.java

/**
 * Reverse engineer an existing table./*from   ww w.  j  av a 2 s .c om*/
 *
 * @param aModel     the model
 * @param aOptions     the options
 * @param aNotifier   the notifier
 * @param aTableEntry the table
 * @param aConnection the connection
 * 
 * @return a Map of former ModelProperties of model items in case they have been replaced during reverse engineering into an existing model
 * 
 * @throws SQLException   is thrown in case of an error
 * @throws ReverseEngineeringException is thrown in case of an error
 */
protected final Map<String, ModelProperties> reverseEngineerTable(Model aModel,
        ReverseEngineeringOptions aOptions, ReverseEngineeringNotifier aNotifier, TableEntry aTableEntry,
        Connection aConnection) throws SQLException, ReverseEngineeringException {
    Map<String, ModelProperties> theExistingModelItemProperties = null;
    aNotifier.notifyMessage(ERDesignerBundle.ENGINEERINGTABLE, aTableEntry.getTableName());

    DatabaseMetaData theMetaData = aConnection.getMetaData();

    String theTablePattern = getEscapedPattern(theMetaData, aTableEntry.getTableName());
    String theSchemaPattern = getEscapedPattern(theMetaData, aTableEntry.getSchemaName());

    ResultSet theTablesResultSet = theMetaData.getTables(aTableEntry.getCatalogName(), theSchemaPattern,
            theTablePattern, new String[] { aTableEntry.getTableType().toString() });

    while (theTablesResultSet.next()) {

        String theTableRemarks = theTablesResultSet.getString("REMARKS");

        Table theNewTable = new Table();

        theNewTable.setName(dialect.getCastType().cast(aTableEntry.getTableName()));
        theNewTable.setOriginalName(aTableEntry.getTableName());
        switch (aOptions.getTableNaming()) {
        case INCLUDE_SCHEMA:
            theNewTable.setSchema(aTableEntry.getSchemaName());
            break;
        default:
        }

        if (!StringUtils.isEmpty(theTableRemarks)) {
            theNewTable.setComment(theTableRemarks);
        }

        // Reverse engineer attributes
        ResultSet theColumnsResultSet = theMetaData.getColumns(aTableEntry.getCatalogName(), theSchemaPattern,
                theTablePattern, null);
        while (theColumnsResultSet.next()) {

            String theColumnName = null;
            String theTypeName = null;
            Integer theSize = null;
            Integer theFraction = null;
            int theRadix = 0;
            int theNullable = 0;
            String theDefaultValue = null;
            String theColumnRemarks = null;

            try {
                theColumnName = theColumnsResultSet.getString("COLUMN_NAME");
            } catch (SQLException e) {
            }

            try {
                theTypeName = theColumnsResultSet.getString("TYPE_NAME");
            } catch (SQLException e) {
            }

            try {
                theSize = theColumnsResultSet.getInt("COLUMN_SIZE");
            } catch (SQLException e) {
            }

            try {
                theFraction = theColumnsResultSet.getInt("DECIMAL_DIGITS");
            } catch (SQLException e) {
            }

            try {
                theRadix = theColumnsResultSet.getInt("NUM_PREC_RADIX");
            } catch (SQLException e) {
            }

            try {
                theNullable = theColumnsResultSet.getInt("NULLABLE");
            } catch (SQLException e) {
            }

            try {
                theDefaultValue = theColumnsResultSet.getString("COLUMN_DEF");
                if (!StringUtils.isEmpty(theDefaultValue)) {
                    theDefaultValue = theDefaultValue.trim();
                }
            } catch (SQLException e) {
            }

            try {
                theColumnRemarks = theColumnsResultSet.getString("REMARKS");
            } catch (SQLException e) {
            }

            Attribute<Table> theAttribute = new Attribute<>();

            theAttribute.setName(dialect.getCastType().cast(theColumnName));
            if (!StringUtils.isEmpty(theColumnRemarks)) {
                theAttribute.setComment(theColumnRemarks);
            }

            // Search for the datatype in the domains, the dialect specific and the user defined datatypes
            DataType theDataType = aModel.getAvailableDataTypes()
                    .findByName(dialect.convertTypeNameToRealTypeName(theTypeName));

            if (theDataType == null) {
                throw new ReverseEngineeringException("Unknown data type " + theTypeName + " for "
                        + aTableEntry.getTableName() + "." + theColumnName);
            }

            boolean isNullable = true;

            switch (theNullable) {
            case DatabaseMetaData.columnNoNulls:
                isNullable = false;
                break;
            case DatabaseMetaData.columnNullable:
                isNullable = true;
                break;
            default:
                LOGGER.warn("Unknown nullability : " + theNullable + " for " + theColumnName + " of table "
                        + theNewTable.getName());
            }

            theAttribute.setDatatype(theDataType);
            theAttribute.setSize(theSize);
            theAttribute.setFraction(theFraction);
            theAttribute.setScale(theRadix);
            theAttribute.setDefaultValue(theDefaultValue);
            theAttribute.setNullable(isNullable);

            reverseEngineerAttribute(theAttribute, aTableEntry, aConnection);

            try {
                theNewTable.addAttribute(aModel, theAttribute);
            } catch (ElementAlreadyExistsException | ElementInvalidNameException e) {
                throw new ReverseEngineeringException(e.getMessage(), e);
            }
        }
        theColumnsResultSet.close();

        // Reverse engineer primary keys
        reverseEngineerPrimaryKey(aModel, aTableEntry, theMetaData, theNewTable);

        // Reverse engineer indexes
        try {
            reverseEngineerIndexes(aModel, aTableEntry, theMetaData, theNewTable, aNotifier);
        } catch (SQLException e) {
            // if there is an sql exception, just ignore it
        }

        // We are done here
        try {
            aModel.addTable(theNewTable);
        } catch (ElementAlreadyExistsException e1) {
            //this manages the reverse engineering into an existing model and cares only about the table names of the model that conflict with the new table names from the connection
            //TODO: also care about tables that are no longer part of the connection, but still exist in the local model. E.g. show a dialog and ask the user what to do (delete/keep)
            try {
                //buffer the properties (e.g. position in model) of the existing table and its relations (e.g. the offset of the title) that are going to be replaced
                Table theExistingTable = aModel.getTables().findByName(theNewTable.getName());
                RelationList theExistingRelations = aModel.getRelations().getAllRelataionsOf(theExistingTable);

                if (theExistingModelItemProperties == null) {
                    theExistingModelItemProperties = new HashMap<>();
                }

                //store former layouting data for the table and its relations in the old graph
                theExistingModelItemProperties.put(theExistingTable.getName(),
                        theExistingTable.getProperties());
                for (Relation anExistingRelation : theExistingRelations) {
                    theExistingModelItemProperties.put(anExistingRelation.getName(),
                            anExistingRelation.getProperties());
                }

                //remove old table and its relations
                aModel.removeTable(theExistingTable);

                //add the new table without relations
                aModel.addTable(theNewTable);
            } catch (ElementAlreadyExistsException | ElementInvalidNameException | VetoException e2) {
                throw new ReverseEngineeringException(e2.getMessage());
            }
        } catch (ElementInvalidNameException | VetoException e3) {
            throw new ReverseEngineeringException(e3.getMessage());
        }
    }

    theTablesResultSet.close();

    return theExistingModelItemProperties;
}

From source file:com.treasuredata.jdbc.TDDatabaseMetaData.java

@SuppressWarnings("unchecked")
public ResultSet getColumns(String catalog, final String schemaPattern, final String tableNamePattern,
        final String columnNamePattern) throws SQLException {
    if (catalog == null) {
        catalog = "default";
    }//from   www.  ja v  a  2 s . c  o m

    String tableNamePattern1 = convertPattern(tableNamePattern);
    String columnNamePattern1 = convertPattern(columnNamePattern);

    List<TableSummary> ts = null;
    try {
        ts = api.showTables();
        if (ts == null) {
            ts = new ArrayList<TableSummary>();
        }
    } catch (ClientException e) {
        throw new SQLException(e);
    }

    List<TDColumn> columns = new ArrayList<TDColumn>();
    for (TableSummary t : ts) {
        if (!t.getName().matches(tableNamePattern1)) {
            continue;
        }

        List<List<String>> schemaFields = null;
        try {
            schemaFields = (List<List<String>>) JSONValue.parse(t.getSchema());
        } catch (Exception e) {
            continue;
        }

        boolean hasTimeColumn = false;
        int ordinal = 1;
        for (List<String> schemaField : schemaFields) {
            String fname = schemaField.get(0);
            String ftype = schemaField.get(1);

            if (fname.equals("time")) {
                hasTimeColumn = true;
            }

            if (!fname.matches(columnNamePattern1)) {
                continue;
            }

            TDColumn c = new TDColumn(fname, t.getName(), catalog, ftype, "comment", ordinal);
            columns.add(c);
            ordinal++;
        }

        if (t.getType() == Table.Type.LOG && !hasTimeColumn && "time".matches(columnNamePattern1)) {
            TDColumn c = new TDColumn("time", t.getName(), catalog, "int", "comment", ordinal);
            columns.add(c);
            ordinal++;
        }
    }
    Collections.sort(columns, new Comparator<TDColumn>() {
        /**
         * We sort the output of getColumns to guarantee jdbc compliance.
         * First check by table name then by ordinal position
         */
        public int compare(TDColumn o1, TDColumn o2) {
            int compareName = o1.getTableName().compareTo(o2.getTableName());
            if (compareName == 0) {
                if (o1.getOrdinal() > o2.getOrdinal()) {
                    return 1;
                } else if (o1.getOrdinal() < o2.getOrdinal()) {
                    return -1;
                }
                return 0;
            } else {
                return compareName;
            }
        }
    });

    List<String> names = Arrays.asList("TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "COLUMN_NAME", "DATA_TYPE",
            "TYPE_NAME", "COLUMN_SIZE", "BUFFER_LENGTH", "DECIMAL_DIGITS", "NUM_PREC_RADIX", "NULLABLE",
            "REMARKS", "COLUMN_DEF", "SQL_DATA_TYPE", "SQL_DATETIME_SUB", "CHAR_OCTET_LENGTH",
            "ORDINAL_POSITION", "IS_NULLABLE", "SCOPE_CATLOG", "SCOPE_SCHEMA", "SCOPE_TABLE",
            "SOURCE_DATA_TYPE", "IS_AUTOINCREMENT");

    List<String> types = Arrays.asList("STRING", // TABLE_CAT
            "STRING", // TABLE_SCHEM
            "STRING", // TABLE_NAME
            "STRING", // COLUMN_NAME
            "INT", // DATA_TYPE
            "STRING", // TYPE_NAME
            "INT", // COLUMN_SIZE
            "INT", // BUFFER_LENGTH
            "INT", // DECIMAL_DIGITS
            "INT", // NUM_PREC_RADIX
            "INT", // NULLABLE
            "STRING", // REMARKS
            "STRING", // COLUMN_DEF
            "INT", // SQL_DATA_TYPE
            "INT", // SQL_DATEIME_SUB
            "INT", // CHAR_OCTET_LENGTH
            "INT", // ORDINAL_POSITION
            "STRING", // IS_NULLABLE
            "STRING", // SCOPE_CATLOG
            "STRING", // SCOPE_SCHEMA
            "STRING", // SCOPE_TABLE
            "INT", // SOURCE_DATA_TYPE
            "STRING" // IS_AUTOINCREMENT
    );

    try {
        return new TDMetaDataResultSet<TDColumn>(names, types, columns) {
            private int cnt = 0;

            public boolean next() throws SQLException {
                if (cnt >= data.size()) {
                    return false;
                }

                TDColumn column = data.get(cnt);
                List<Object> a = new ArrayList<Object>(23);
                a.add(column.getTableCatalog()); // TABLE_CAT String =>
                // table catalog (may be
                // null)
                a.add(null); // TABLE_SCHEM String => table schema (may be
                // null)
                a.add(column.getTableName()); // TABLE_NAME String => table
                // name
                a.add(column.getColumnName()); // COLUMN_NAME String =>
                // column name
                a.add(column.getSqlType()); // DATA_TYPE short => SQL type
                // from java.sql.Types
                a.add(column.getType()); // TYPE_NAME String => Data source
                // dependent type name.
                a.add(column.getColumnSize()); // COLUMN_SIZE int => column
                // size.
                a.add(null); // BUFFER_LENGTH is not used.
                a.add(column.getDecimalDigits()); // DECIMAL_DIGITS int =>
                // number of fractional
                // digits
                a.add(column.getNumPrecRadix()); // NUM_PREC_RADIX int =>
                // typically either 10 or 2
                a.add(DatabaseMetaData.columnNullable); // NULLABLE int =>
                // is NULL allowed?
                a.add(column.getComment()); // REMARKS String => comment
                // describing column (may be
                // null)
                a.add(null); // COLUMN_DEF String => default value (may be
                // null)
                a.add(null); // SQL_DATA_TYPE int => unused
                a.add(null); // SQL_DATETIME_SUB int => unused
                a.add(null); // CHAR_OCTET_LENGTH int
                a.add(column.getOrdinal()); // ORDINAL_POSITION int
                a.add("YES"); // IS_NULLABLE String
                a.add(null); // SCOPE_CATLOG String
                a.add(null); // SCOPE_SCHEMA String
                a.add(null); // SCOPE_TABLE String
                a.add(null); // SOURCE_DATA_TYPE short
                a.add("NO"); // IS_AUTOINCREMENT String

                row = a;
                cnt++;
                return true;
            }
        };
    } catch (Exception e) {
        throw new SQLException(e);
    }
}