List of usage examples for java.sql DatabaseMetaData columnNoNulls
int columnNoNulls
To view the source code for java.sql DatabaseMetaData columnNoNulls.
Click Source Link
NULL values. From source file:org.apache.openjpa.jdbc.sql.DBDictionary.java
/** * Create a new column from the information in the schema metadata. *//* ww w . j a va 2 s. c om*/ protected Column newColumn(ResultSet colMeta) throws SQLException { Column c = new Column(); c.setSchemaIdentifier(fromDBName(colMeta.getString("TABLE_SCHEM"), DBIdentifierType.SCHEMA)); c.setTableIdentifier(fromDBName(colMeta.getString("TABLE_NAME"), DBIdentifierType.TABLE)); c.setIdentifier(fromDBName(colMeta.getString("COLUMN_NAME"), DBIdentifierType.COLUMN)); c.setType(colMeta.getInt("DATA_TYPE")); c.setTypeIdentifier(fromDBName(colMeta.getString("TYPE_NAME"), DBIdentifierType.COLUMN_DEFINITION)); c.setSize(colMeta.getInt("COLUMN_SIZE")); c.setDecimalDigits(colMeta.getInt("DECIMAL_DIGITS")); c.setNotNull(colMeta.getInt("NULLABLE") == DatabaseMetaData.columnNoNulls); String def = colMeta.getString("COLUMN_DEF"); if (!StringUtils.isEmpty(def) && !"null".equalsIgnoreCase(def)) c.setDefaultString(def); return c; }
From source file:org.dbmaintain.database.impl.DerbyDatabase.java
/** * Returns the names of all columns that have a 'not-null' constraint on them. * <p/>/* w ww .j a v a 2 s .co m*/ * This info is not available in the Derby sys tables. The database meta data is used instead to retrieve it. * * @param schemaName The schema, not null * @param tableName The table, not null * @return The set of column names, not null */ protected Set<String> getNotNullColummnNames(String schemaName, String tableName) { Connection connection = null; ResultSet resultSet = null; try { connection = getDataSource().getConnection(); DatabaseMetaData databaseMetaData = connection.getMetaData(); resultSet = databaseMetaData.getColumns(null, schemaName, tableName, "%"); Set<String> result = new HashSet<String>(); while (resultSet.next()) { if (resultSet.getInt(11) == DatabaseMetaData.columnNoNulls) { // NULLABLE result.add(resultSet.getString(4)); //COLUMN_NAME } } return result; } catch (SQLException e) { throw new DatabaseException("Unable to get not null column names for schema name: " + schemaName + ", table name: " + tableName, e); } finally { closeQuietly(connection, null, resultSet); } }
From source file:org.executequery.databaseobjects.impl.DefaultDatabaseHost.java
/** * Returns the column names of the specified database object. * * @param catalog the table catalog name * @param schema the table schema name/*from w ww . j a v a2 s . c o m*/ * @param table the database object name * @return the column names */ public List<ColumnInformation> getColumnInformation(String catalog, String schema, String table) throws DataSourceException { ResultSet rs = null; List<ColumnInformation> columns = new ArrayList<ColumnInformation>(); try { String _catalog = getCatalogNameForQueries(catalog); String _schema = getSchemaNameForQueries(schema); DatabaseMetaData dmd = getDatabaseMetaData(); if (!isConnected()) { return new ArrayList<ColumnInformation>(0); } // retrieve the base column info rs = dmd.getColumns(_catalog, _schema, table, null); while (rs.next()) { String name = rs.getString(4); columns.add(columnInformationFactory.build(table, name, rs.getString(6), rs.getInt(5), rs.getInt(7), rs.getInt(9), rs.getInt(11) == DatabaseMetaData.columnNoNulls)); } return columns; } catch (SQLException e) { if (Log.isDebugEnabled()) { Log.error("Error retrieving column data for table " + table + " using connection " + getDatabaseConnection(), e); } return columns; } finally { releaseResources(rs); } }
From source file:org.executequery.databaseobjects.impl.DefaultDatabaseHost.java
/** * Returns the columns of the specified database object. * * @param catalog the table catalog name * @param schema the table schema name/*from w w w . jav a 2 s. c o m*/ * @param table the database object name * @return the columns */ public List<DatabaseColumn> getColumns(String catalog, String schema, String table) throws DataSourceException { ResultSet rs = null; List<DatabaseColumn> columns = new ArrayList<DatabaseColumn>(); try { String _catalog = getCatalogNameForQueries(catalog); String _schema = getSchemaNameForQueries(schema); DatabaseMetaData dmd = getDatabaseMetaData(); // retrieve the base column info rs = dmd.getColumns(_catalog, _schema, table, null); /* if (Log.isDebugEnabled()) { Log.debug("Meta data on columns for table - " + table); ResultSetMetaData metaData = rs.getMetaData(); int columnCount = metaData.getColumnCount(); for (int i = 0; i < columnCount; i++) { Log.debug("Column: [ " + (i + 1) + " ] " + metaData.getColumnName(i + 1)); } } */ while (rs.next()) { DefaultDatabaseColumn column = new DefaultDatabaseColumn(); column.setCatalogName(catalog); column.setSchemaName(schema); column.setName(rs.getString(4)); column.setTypeInt(rs.getInt(5)); column.setTypeName(rs.getString(6)); column.setColumnSize(rs.getInt(7)); column.setColumnScale(rs.getInt(9)); column.setRequired(rs.getInt(11) == DatabaseMetaData.columnNoNulls); column.setRemarks(rs.getString(12)); column.setDefaultValue(rs.getString(13)); columns.add(column); } releaseResources(rs); int columnCount = columns.size(); if (columnCount > 0) { // check for primary keys rs = dmd.getPrimaryKeys(_catalog, _schema, table); while (rs.next()) { String pkColumn = rs.getString(4); // find the pk column in the previous list for (int i = 0; i < columnCount; i++) { DatabaseColumn column = columns.get(i); String columnName = column.getName(); if (columnName.equalsIgnoreCase(pkColumn)) { ((DefaultDatabaseColumn) column).setPrimaryKey(true); break; } } } releaseResources(rs); // check for foreign keys rs = dmd.getImportedKeys(_catalog, _schema, table); while (rs.next()) { String fkColumn = rs.getString(8); // find the fk column in the previous list for (int i = 0; i < columnCount; i++) { DatabaseColumn column = columns.get(i); String columnName = column.getName(); if (columnName.equalsIgnoreCase(fkColumn)) { ((DefaultDatabaseColumn) column).setForeignKey(true); break; } } } } return columns; } catch (SQLException e) { if (Log.isDebugEnabled()) { Log.error("Error retrieving column data for table " + table + " using connection " + getDatabaseConnection(), e); } return columns; // throw new DataSourceException(e); } finally { releaseResources(rs); } }
From source file:org.executequery.gui.editor.autocomplete.AutoCompleteSelectionsFactory.java
private void databaseColumnsForTables(DatabaseHost databaseHost, List<AutoCompleteListItem> tables) { trace("Retrieving column names for tables for host [ " + databaseHost.getName() + " ]"); ResultSet rs = null;//from w w w .j av a 2 s . c om List<ColumnInformation> columns = new ArrayList<ColumnInformation>(); List<AutoCompleteListItem> list = new ArrayList<AutoCompleteListItem>(); String catalog = databaseHost.getCatalogNameForQueries(defaultCatalogForHost(databaseHost)); String schema = databaseHost.getSchemaNameForQueries(defaultSchemaForHost(databaseHost)); DatabaseMetaData dmd = databaseHost.getDatabaseMetaData(); for (int i = 0, n = tables.size(); i < n; i++) { try { if (Thread.interrupted() || dmd.getConnection().isClosed()) { return; } } catch (SQLException e) { } AutoCompleteListItem table = tables.get(i); if (table == null) { continue; } trace("Retrieving column names for table [ " + table.getValue() + " ]"); try { rs = dmd.getColumns(catalog, schema, table.getValue(), null); while (rs.next()) { String name = rs.getString(4); columns.add( columnInformationFactory.build(table.getValue(), name, rs.getString(6), rs.getInt(5), rs.getInt(7), rs.getInt(9), rs.getInt(11) == DatabaseMetaData.columnNoNulls)); } for (ColumnInformation column : columns) { list.add(new AutoCompleteListItem(column.getName(), table.getValue(), column.getDescription(), DATABASE_COLUMN_DESCRIPTION, AutoCompleteListItemType.DATABASE_TABLE_COLUMN)); } provider.addListItems(list); releaseResources(rs); columns.clear(); list.clear(); } catch (Throwable e) { // don't want to break the editor here so just log and bail... error("Error retrieving column data for table " + table.getDisplayValue() + " - driver returned: " + e.getMessage()); } finally { releaseResources(rs); } } trace("Finished retrieving column names for tables for host [ " + databaseHost.getName() + " ]"); }
From source file:org.jtester.module.database.support.DerbyDbSupport.java
/** * Returns the names of all columns that have a 'not-null' constraint on * them.//from w w w .j a va2 s.c o m * <p/> * This info is not available in the Derby sys tables. The database meta * data is used instead to retrieve it. * * @param tableName * The table, not null * @return The set of column names, not null */ protected Set<String> getNotNullColummnNames(String tableName) { Connection connection = null; ResultSet resultSet = null; try { connection = getSQLHandler().getDataSource().getConnection(); DatabaseMetaData databaseMetaData = connection.getMetaData(); resultSet = databaseMetaData.getColumns(null, getSchemaName(), tableName, "%"); Set<String> result = new HashSet<String>(); while (resultSet.next()) { if (resultSet.getInt(11) == DatabaseMetaData.columnNoNulls) { // NULLABLE result.add(resultSet.getString(4)); // COLUMN_NAME } } return result; } catch (SQLException e) { throw new JTesterException("Error while querying for Derby primary keys for table name: " + tableName, e); } finally { closeQuietly(connection, null, resultSet); } }
From source file:org.kuali.core.db.torque.KualiTorqueJDBCTransformTask.java
/** * Generates an XML database schema from JDBC metadata. * /*from ww w.ja v a2s . com*/ * @throws Exception * a generic exception. */ public void generateXML() throws Exception { // Load the database Driver. Class.forName(dbDriver); log("DB driver sucessfuly instantiated"); Connection con = null; try { // Attempt to connect to a database. Properties p = new Properties(); p.setProperty("user", dbUser); p.setProperty("password", dbPassword); p.setProperty("oracle.jdbc.mapDateToTimestamp", "false"); // workaround for change in 11g JDBC driver con = DriverManager.getConnection(dbUrl, p); log("DB connection established"); Platform platform = PlatformFactory.getPlatformFor(dbType); // Get the database Metadata. DatabaseMetaData dbMetaData = con.getMetaData(); databaseNode = doc.createElement("database"); databaseNode.setAttribute("name", "kuali"); // JHK added naming method databaseNode.setAttribute("defaultJavaNamingMethod", "nochange"); if (processTables) { List<String> tableList = platform.getTableNames(dbMetaData, dbSchema); // ensure all are upper case before exporting tableList = upperCaseList(tableList); // ensure sorting is consistent (not DB-dependent) Collections.sort(tableList); for (String curTable : tableList) { if (!tableNameRegexPattern.matcher(curTable).matches()) { log("Skipping table: " + curTable); continue; } if (StringUtils.isNotBlank(tableNameExcludeRegex) && tableNameExcludeRegexPattern.matcher(curTable).matches()) { log("Skipping table: " + curTable); continue; } log("Processing table: " + curTable); Element table = doc.createElement("table"); table.setAttribute("name", curTable.toUpperCase()); // Add Columns. // TableMap tblMap = dbMap.getTable(curTable); List columns = getColumns(dbMetaData, curTable); List<String> primKeys = platform.getPrimaryKeys(dbMetaData, dbSchema, curTable); Map<String, Object[]> foreignKeys = getForeignKeys(dbMetaData, curTable); // Set the primary keys. primaryKeys = new HashMap<String, String>(); for (int k = 0; k < primKeys.size(); k++) { String curPrimaryKey = (String) primKeys.get(k); primaryKeys.put(curPrimaryKey, curPrimaryKey); } for (int j = 0; j < columns.size(); j++) { List col = (List) columns.get(j); String name = (String) col.get(0); Integer jdbcType = ((Integer) col.get(1)); int size = ((Integer) col.get(2)).intValue(); int scale = ((Integer) col.get(5)).intValue(); // From DatabaseMetaData.java // // Indicates column might not allow NULL values. Huh? // Might? Boy, that's a definitive answer. /* int columnNoNulls = 0; */ // Indicates column definitely allows NULL values. /* int columnNullable = 1; */ // Indicates NULLABILITY of column is unknown. /* int columnNullableUnknown = 2; */ Integer nullType = (Integer) col.get(3); String defValue = (String) col.get(4); Element column = doc.createElement("column"); column.setAttribute("name", name); ; column.setAttribute("type", platform.getTorqueColumnType(jdbcType)); // TypeMap.getTorqueType( type ).getName() ); if (size > 0 && (jdbcType.intValue() == Types.CHAR || jdbcType.intValue() == Types.VARCHAR || jdbcType.intValue() == Types.DECIMAL || jdbcType.intValue() == Types.NUMERIC)) { column.setAttribute("size", String.valueOf(size)); } if (scale > 0 && (jdbcType.intValue() == Types.DECIMAL || jdbcType.intValue() == Types.NUMERIC)) { column.setAttribute("scale", String.valueOf(scale)); } if (primaryKeys.containsKey(name)) { column.setAttribute("primaryKey", "true"); // JHK: protect MySQL from excessively long column in the PK //System.out.println( curTable + "." + name + " / " + size ); if (column.getAttribute("size") != null && size > 765) { log("updating column " + curTable + "." + name + " length from " + size + " to 255"); column.setAttribute("size", "255"); } } else { if (nullType.intValue() == DatabaseMetaData.columnNoNulls) { column.setAttribute("required", "true"); } } if (StringUtils.isNotEmpty(defValue)) { defValue = platform.getColumnDefaultValue(platform.getTorqueColumnType(jdbcType), defValue); if (StringUtils.isNotEmpty(defValue)) { column.setAttribute("default", defValue); } } table.appendChild(column); } List<String> foreignKeyNames = new ArrayList<String>(foreignKeys.keySet()); Collections.sort(foreignKeyNames); // Foreign keys for this table. for (String fkName : foreignKeyNames) { Element fk = doc.createElement("foreign-key"); fk.setAttribute("name", fkName.toUpperCase()); Object[] forKey = foreignKeys.get(fkName); String foreignKeyTable = (String) forKey[0]; List refs = (List) forKey[1]; fk.setAttribute("foreignTable", foreignKeyTable.toUpperCase()); String onDelete = (String) forKey[2]; // gmcgrego - just adding onDelete if it's cascade so as not to affect kfs behavior if (onDelete == "cascade") { fk.setAttribute("onDelete", onDelete); } for (int m = 0; m < refs.size(); m++) { Element ref = doc.createElement("reference"); String[] refData = (String[]) refs.get(m); ref.setAttribute("local", refData[0]); ref.setAttribute("foreign", refData[1]); fk.appendChild(ref); } table.appendChild(fk); } List<TableIndex> indexes = getIndexes(dbMetaData, curTable); Collections.sort(indexes, new Comparator<TableIndex>() { public int compare(TableIndex o1, TableIndex o2) { return o1.name.compareTo(o2.name); } }); for (TableIndex idx : indexes) { if (foreignKeyNames.contains(idx.name)) { log(idx.name + " is also a foreign key, skipping"); continue; } String tagName = idx.unique ? "unique" : "index"; Element index = doc.createElement(tagName); index.setAttribute("name", idx.name.toUpperCase()); for (String colName : idx.columns) { Element col = doc.createElement(tagName + "-column"); col.setAttribute("name", colName); index.appendChild(col); } table.appendChild(index); } databaseNode.appendChild(table); } } if (processViews) { log("Getting view list..."); List<String> viewNames = platform.getViewNames(dbMetaData, dbSchema); log("Found " + viewNames.size() + " views."); viewNames = upperCaseList(viewNames); Collections.sort(viewNames); for (String viewName : viewNames) { if (!tableNameRegexPattern.matcher(viewName).matches()) { log("Skipping view: " + viewName); continue; } Element view = doc.createElement("view"); view.setAttribute("name", viewName.toUpperCase()); /* * <view name="" viewdefinition="" /> * */ String definition = platform.getViewDefinition(dbMetaData.getConnection(), dbSchema, viewName); definition = definition.replaceAll("\0", ""); view.setAttribute("viewdefinition", definition); databaseNode.appendChild(view); } } if (processSequences) { log("Getting sequence list..."); List<String> sequenceNames = platform.getSequenceNames(dbMetaData, dbSchema); log("Found " + sequenceNames.size() + " sequences."); sequenceNames = upperCaseList(sequenceNames); Collections.sort(sequenceNames); for (String sequenceName : sequenceNames) { if (!tableNameRegexPattern.matcher(sequenceName).matches()) { log("Skipping sequence: " + sequenceName); continue; } Element sequence = doc.createElement("sequence"); sequence.setAttribute("name", sequenceName.toUpperCase()); /* * <view name="" nextval="" /> * */ Long nextVal = platform.getSequenceNextVal(dbMetaData.getConnection(), dbSchema, sequenceName); sequence.setAttribute("nextval", nextVal.toString()); databaseNode.appendChild(sequence); } doc.appendChild(databaseNode); } } finally { if (con != null) { con.close(); con = null; } } }
From source file:org.unitils.core.dbsupport.DerbyDbSupport.java
/** * Returns the names of all columns that have a 'not-null' constraint on them. * <p/>/*from w w w. j a va 2 s.co m*/ * This info is not available in the Derby sys tables. The database meta data is used instead to retrieve it. * * @param tableName The table, not null * @return The set of column names, not null */ protected Set<String> getNotNullColummnNames(String tableName) { Connection connection = null; ResultSet resultSet = null; try { connection = getSQLHandler().getDataSource().getConnection(); DatabaseMetaData databaseMetaData = connection.getMetaData(); resultSet = databaseMetaData.getColumns(null, getSchemaName(), tableName, "%"); Set<String> result = new HashSet<String>(); while (resultSet.next()) { if (resultSet.getInt(11) == DatabaseMetaData.columnNoNulls) { // NULLABLE result.add(resultSet.getString(4)); //COLUMN_NAME } } return result; } catch (SQLException e) { throw new UnitilsException("Error while querying for Derby primary keys for table name: " + tableName, e); } finally { closeQuietly(connection, null, resultSet); } }