List of usage examples for java.sql DatabaseMetaData getColumns
ResultSet getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern)
throws SQLException;
From source file:com.ccl.jersey.codegen.SimpleMetaDataExporter.java
@SuppressWarnings("unchecked") private void handleTable(DatabaseMetaData md, ResultSet tables, Statement stmt) throws SQLException { String catalog = tables.getString("TABLE_CAT"); String schema = tables.getString("TABLE_SCHEM"); String schemaName = normalize(tables.getString("TABLE_SCHEM")); String tableName = normalize(tables.getString("TABLE_NAME")); String comment = getTableComment(stmt, tableName); String normalizedTableName = namingStrategy.normalizeTableName(tableName); String className = namingStrategy.getClassName(normalizedTableName); EntityType classModel = createEntityType(schemaName, normalizedTableName, className); EntityType modelClassModel = createVOEntityType(schemaName, normalizedTableName, className); tableModelMapper.put(normalizedTableName, modelClassModel.getFullName()); if (exportPrimaryKeys) { // collect primary keys Map<String, PrimaryKeyData> primaryKeyData = keyDataFactory.getPrimaryKeys(md, catalog, schema, tableName);// ww w.j a v a 2 s. c o m if (!primaryKeyData.isEmpty()) { classModel.getData().put(PrimaryKeyData.class, primaryKeyData.values()); } } Map<String, UniqueKeyData> uniqueKeyDatas = new HashMap<>(); ResultSet indexInfo = md.getIndexInfo(catalog, schema, tableName, true, false); while (indexInfo.next()) { String indexName = indexInfo.getString("INDEX_NAME"); if (!"PRIMARY".equals(indexName)) { UniqueKeyData uniqueKeyData = uniqueKeyDatas.get(indexName); if (null == uniqueKeyData) { uniqueKeyData = new UniqueKeyData(indexName); } uniqueKeyDatas.put(indexName, uniqueKeyData); String columnName = indexInfo.getString("COLUMN_NAME"); String normalizedColumnName = namingStrategy.normalizeColumnName(columnName); String propertyName = namingStrategy.getPropertyName(normalizedColumnName, classModel); uniqueKeyData.add(propertyName); } } indexInfo.close(); if (!uniqueKeyDatas.isEmpty()) { Collection<UniqueKeyData> values = uniqueKeyDatas.values(); classModel.getData().put(UniqueKeyData.class, values); List<Unique> uniques = new ArrayList<>(); for (UniqueKeyData uniqueKeyData : values) { UniqueImpl unique = new UniqueImpl( uniqueKeyData.getColumns().toArray(new String[uniqueKeyData.getColumns().size()])); uniques.add(unique); } classModel.addAnnotation(new UniquesImpl(uniques.toArray(new Unique[uniques.size()]))); } if (exportForeignKeys) { // collect foreign keys Map<String, ForeignKeyData> foreignKeyData = keyDataFactory.getImportedKeys(md, catalog, schema, tableName); if (!foreignKeyData.isEmpty()) { classModel.getData().put(ForeignKeyData.class, foreignKeyData.values()); } // collect inverse foreign keys Map<String, InverseForeignKeyData> inverseForeignKeyData = keyDataFactory.getExportedKeys(md, catalog, schema, tableName); if (!inverseForeignKeyData.isEmpty()) { classModel.getData().put(InverseForeignKeyData.class, inverseForeignKeyData.values()); } } // collect columns ResultSet columns = md.getColumns(catalog, schema, tableName.replace("/", "//"), null); try { while (columns.next()) { handleColumn(classModel, tableName, columns); } } finally { columns.close(); } // serialize model LabelImpl label = new LabelImpl(StringUtils.isBlank(comment) ? className : comment); classModel.addAnnotation(label); Set<Property> properties = classModel.getProperties(); Map<String, Type> dataTypeMap = registerDataTypes.get(modelClassModel.getFullName()); String modelPackageName = basePackageName + ".model"; List<BelongsToImpl> belongsTos = new ArrayList<BelongsToImpl>(); for (Property property : properties) { Type propertyType = property.getType(); boolean customType = false; String propertyName = property.getName(); if (null != dataTypeMap) { Type propertyCls = dataTypeMap.get(propertyName); if (null != propertyCls) { propertyType = propertyCls; customType = true; } } Collection<ForeignKeyData> foreignKeyDatas = (Collection<ForeignKeyData>) classModel.getData() .get(ForeignKeyData.class); boolean isForeignKey = false; if (exportBelongsTos && null != foreignKeyDatas && !foreignKeyDatas.isEmpty()) { for (ForeignKeyData foreignKey : foreignKeyDatas) { String propertyName2 = namingStrategy.getPropertyName(foreignKey.getForeignColumns().get(0), classModel); if (propertyName.equals(propertyName2)) { String simpleName = foreignKey.getType().getSimpleName(); simpleName = simpleName.substring(module.getBeanPrefix().length(), simpleName.length()); propertyType = new SimpleType(modelPackageName + "." + simpleName, modelPackageName, simpleName); propertyName = propertyName2.substring(0, propertyName2.length() - 2); belongsTos.add(new BelongsToImpl(propertyName, propertyName2)); isForeignKey = true; break; } } } Property property2 = createProperty(modelClassModel, propertyName, propertyName, propertyName, propertyType); Collection<Annotation> annotations = property.getAnnotations(); for (Annotation annotation : annotations) { if ((isForeignKey || customType) && Size.class.equals(annotation.annotationType())) { continue; } property2.addAnnotation(annotation); } modelClassModel.addProperty(property2); if (customType) { property.addAnnotation(new DictDataTypeImpl((Class<? extends Enum>) propertyType.getJavaClass())); } } Collection<InverseForeignKeyData> inverseForeignKeyDatas = (Collection<InverseForeignKeyData>) classModel .getData().get(InverseForeignKeyData.class); List<HasManyImpl> hasManys = new ArrayList<HasManyImpl>(); if (exportHasManys && null != inverseForeignKeyDatas && !inverseForeignKeyDatas.isEmpty()) { for (InverseForeignKeyData inverseForeignKeyData : inverseForeignKeyDatas) { String simpleName = inverseForeignKeyData.getType().getSimpleName(); simpleName = simpleName.substring(module.getBeanPrefix().length(), simpleName.length()); Type propertyType = new SimpleType(modelPackageName + "." + simpleName, modelPackageName, simpleName); Type propertyListType = new ClassType(TypeCategory.LIST, List.class, propertyType); String propertyName2 = namingStrategy.getPropertyName(inverseForeignKeyData.getTable(), classModel); String propertyName = propertyName2 + "s"; Set<String> propertyNames = modelClassModel.getPropertyNames(); int index = 0; while (propertyNames.contains(propertyName)) { index++; propertyName = propertyName2 + index + "s"; } Property property2 = createProperty(modelClassModel, propertyName, propertyName, propertyName, propertyListType); String tableComment = getTableComment(stmt, inverseForeignKeyData.getTable()); LabelImpl tableLabel = new LabelImpl(StringUtils.isBlank(tableComment) ? propertyName : tableComment + "" + (0 < index ? index : "")); property2.addAnnotation(tableLabel); modelClassModel.addProperty(property2); hasManys.add(new HasManyImpl(propertyName, namingStrategy .getPropertyName(inverseForeignKeyData.getParentColumns().get(0), classModel))); } } if (!belongsTos.isEmpty()) { modelClassModel .addAnnotation(new BelongsTosImpl(belongsTos.toArray(new BelongsToImpl[belongsTos.size()]))); } if (!hasManys.isEmpty()) { modelClassModel.addAnnotation(new HasManysImpl(hasManys.toArray(new HasManyImpl[hasManys.size()]))); } modelClassModel.addAnnotation(label); serialize(classModel, modelClassModel); logger.info("Exported " + tableName + " successfully"); }
From source file:com.netspective.axiom.policy.AnsiDatabasePolicy.java
public void reverseEngineer(Writer writer, Connection conn, String catalog, String schemaPattern) throws IOException, SQLException { Map dataTypesMap = prepareJdbcTypeInfoMap(); DatabaseMetaData dbmd = conn.getMetaData(); TextUtils textUtils = TextUtils.getInstance(); writer.write("<?xml version=\"1.0\"?>\n\n"); writer.write("<!-- Reverse engineered by Axiom\n"); writer.write(" driver: " + dbmd.getDriverName() + "\n"); writer.write(" driver-version: " + dbmd.getDriverVersion() + "\n"); writer.write(" product: " + dbmd.getDatabaseProductName() + "\n"); writer.write(" product-version: " + dbmd.getDatabaseProductVersion() + "\n"); writer.write(" available catalogs:"); ResultSet rs = null;//from w w w .j a v a2s .c o m try { rs = dbmd.getCatalogs(); while (rs.next()) { writer.write(" " + rs.getObject(1).toString()); } } finally { if (rs != null) rs.close(); } writer.write("\n"); writer.write(" available schemas:"); try { rs = dbmd.getSchemas(); while (rs.next()) { writer.write(" " + rs.getObject(1).toString()); } } finally { if (rs != null) rs.close(); } writer.write("\n"); writer.write("-->\n\n"); writer.write("<component xmlns:xdm=\"http://www.netspective.org/Framework/Commons/XMLDataModel\">\n"); writer.write(" <xdm:include resource=\"com/netspective/axiom/conf/axiom.xml\"/>\n"); writer.write(" <schema name=\"" + catalog + "." + schemaPattern + "\">\n"); Map dbmdTypeInfoByName = new HashMap(); Map dbmdTypeInfoByJdbcType = new HashMap(); ResultSet typesRS = null; try { typesRS = dbmd.getTypeInfo(); while (typesRS.next()) { int colCount = typesRS.getMetaData().getColumnCount(); Object[] typeInfo = new Object[colCount]; for (int i = 1; i <= colCount; i++) typeInfo[i - 1] = typesRS.getObject(i); dbmdTypeInfoByName.put(typesRS.getString(1), typeInfo); dbmdTypeInfoByJdbcType.put(new Integer(typesRS.getInt(2)), typeInfo); } } finally { if (typesRS != null) typesRS.close(); } ResultSet tables = null; try { tables = dbmd.getTables(catalog, schemaPattern, null, new String[] { "TABLE" }); while (tables.next()) { String tableNameOrig = tables.getString(3); String tableName = textUtils.fixupTableNameCase(tableNameOrig); writer.write(" <table name=\"" + tableName + "\">\n"); Map primaryKeys = new HashMap(); ResultSet pkRS = null; try { pkRS = dbmd.getPrimaryKeys(null, null, tableNameOrig); while (pkRS.next()) { primaryKeys.put(pkRS.getString(4), pkRS.getString(5)); } } catch (Exception e) { // driver may not support this function } finally { if (pkRS != null) pkRS.close(); } Map fKeys = new HashMap(); ResultSet fkRS = null; try { fkRS = dbmd.getImportedKeys(null, null, tableNameOrig); while (fkRS.next()) { fKeys.put(fkRS.getString(8), textUtils.fixupTableNameCase(fkRS.getString(3)) + "." + fkRS.getString(4).toLowerCase()); } } catch (Exception e) { // driver may not support this function } finally { if (fkRS != null) fkRS.close(); } // we keep track of processed columns so we don't duplicate them in the XML Set processedColsMap = new HashSet(); ResultSet columns = null; try { columns = dbmd.getColumns(null, null, tableNameOrig, null); while (columns.next()) { String columnNameOrig = columns.getString(4); if (processedColsMap.contains(columnNameOrig)) continue; processedColsMap.add(columnNameOrig); String columnName = columnNameOrig.toLowerCase(); writer.write(" <column name=\"" + columnName + "\""); try { if (fKeys.containsKey(columnNameOrig)) writer.write(" lookup-ref=\"" + fKeys.get(columnNameOrig) + "\""); else { short jdbcType = columns.getShort(5); String dataType = (String) dataTypesMap.get(new Integer(jdbcType)); if (dataType == null) dataType = Short.toString(jdbcType); writer.write(" type=\"" + dataType + "\""); } if (primaryKeys.containsKey(columnNameOrig)) writer.write(" primary-key=\"yes\""); if (columns.getString(18).equals("NO")) writer.write(" required=\"yes\""); String defaultValue = columns.getString(13); if (defaultValue != null) writer.write(" default=\"" + defaultValue + "\""); String remarks = columns.getString(12); if (remarks != null) writer.write(" descr=\"" + remarks + "\""); } catch (Exception e) { } writer.write("/>\n"); } } finally { if (columns != null) columns.close(); } writer.write(" </table>\n"); } } finally { tables.close(); } writer.write(" </schema>\n"); writer.write("</component>"); }
From source file:org.apache.ode.bpel.extvar.jdbc.JdbcExternalVariableModule.java
public void configure(QName pid, String extVarId, Element config) throws ExternalVariableModuleException { EVarId evarId = new EVarId(pid, extVarId); DataSource ds = null;// w w w . j a va 2 s .co m Element jndiDs = DOMUtils.findChildByName(config, new QName(JDBC_NS, "datasource-jndi")); Element jndiRef = DOMUtils.findChildByName(config, new QName(JDBC_NS, "datasource-ref")); Element initMode = DOMUtils.findChildByName(config, new QName(JDBC_NS, "init-mode")); if (jndiRef != null) { String refname = jndiRef.getTextContent().trim(); ds = _dataSources.get(refname); if (ds == null) throw new ExternalVariableModuleException( "Data source reference \"" + refname + "\" not found for external variable " + evarId + "; make sure to register the data source with the engine!"); } else if (jndiDs != null) { String name = jndiDs.getTextContent().trim(); Object dsCandidate; InitialContext ctx; try { ctx = new InitialContext(); } catch (Exception ex) { throw new ExternalVariableModuleException( "Unable to access JNDI context for external variable " + evarId, ex); } try { dsCandidate = ctx.lookup(name); } catch (Exception ex) { throw new ExternalVariableModuleException("Lookup of data source for " + evarId + " failed.", ex); } finally { try { ctx.close(); } catch (NamingException e) { /* ignore */ } } if (dsCandidate == null) throw new ExternalVariableModuleException("Data source \"" + name + "\" not found in JNDI!"); if (!(dsCandidate instanceof DataSource)) throw new ExternalVariableModuleException( "JNDI object \"" + name + "\" does not implement javax.sql.DataSource"); ds = (DataSource) dsCandidate; } if (ds == null) { throw new ExternalVariableModuleException( "No valid data source configuration for JDBC external varible " + evarId); } Connection conn = null; DatabaseMetaData metaData; try { conn = ds.getConnection(); metaData = conn.getMetaData(); } catch (Exception ex) { try { if (conn != null) conn.close(); } catch (SQLException e) { // ignore } throw new ExternalVariableModuleException( "Unable to open database connection for external variable " + evarId, ex); } try { DbExternalVariable dbev = new DbExternalVariable(evarId, ds); if (initMode != null) try { dbev._initType = InitType.valueOf(initMode.getTextContent().trim()); } catch (Exception ex) { throw new ExternalVariableModuleException( "Invalid <init-mode> value: " + initMode.getTextContent().trim()); } Element tableName = DOMUtils.findChildByName(config, new QName(JDBC_NS, "table")); if (tableName == null || tableName.getTextContent().trim().equals("")) throw new ExternalVariableModuleException("Must specify <table> for external variable " + evarId); String table = tableName.getTextContent().trim(); String schema = null; if (table.indexOf('.') != -1) { schema = table.substring(0, table.indexOf('.')); table = table.substring(table.indexOf('.') + 1); } if (metaData.storesLowerCaseIdentifiers()) { table = table.toLowerCase(); if (schema != null) schema = table.toLowerCase(); } else if (metaData.storesUpperCaseIdentifiers()) { table = table.toUpperCase(); if (schema != null) schema = schema.toUpperCase(); } dbev.generatedKeys = metaData.supportsGetGeneratedKeys(); ResultSet tables = metaData.getTables(null, schema, table, null); if (tables.next()) { dbev.table = tables.getString("TABLE_NAME"); dbev.schema = tables.getString("TABLE_SCHEM"); } else throw new ExternalVariableModuleException("Table \"" + table + "\" not found in database."); tables.close(); List<Element> columns = DOMUtils.findChildrenByName(config, new QName(JDBC_NS, "column")); for (Element col : columns) { String name = col.getAttribute("name"); String colname = col.getAttribute("column-name"); String key = col.getAttribute("key"); String gentype = col.getAttribute("generator"); String expression = col.getAttribute("expression"); if (key == null || "".equals(key)) key = "no"; if (gentype == null || "".equals(gentype)) gentype = GenType.none.toString(); if (colname == null || "".equals(colname)) colname = name; if (name == null || "".equals(name)) throw new ExternalVariableModuleException( "External variable " + evarId + " <column> element must have \"name\" attribute. "); if (metaData.storesLowerCaseIdentifiers()) colname = colname.toLowerCase(); else if (metaData.storesUpperCaseIdentifiers()) colname = colname.toUpperCase(); GenType gtype; try { gtype = GenType.valueOf(gentype); } catch (Exception ex) { throw new ExternalVariableModuleException("External variable " + evarId + " column \"" + name + "\" generator type \"" + gentype + "\" is unknown."); } if (gtype == GenType.expression && (expression == null || "".equals(expression))) throw new ExternalVariableModuleException("External variable " + evarId + " column \"" + name + "\" used \"expression\" generator, but did not specify an expression"); Column c = dbev.new Column(name, colname, key.equalsIgnoreCase("yes"), gtype, expression); ResultSet cmd = metaData.getColumns(null, dbev.schema, dbev.table, colname); try { if (cmd.next()) { c.dataType = cmd.getInt("DATA_TYPE"); c.nullok = cmd.getInt("NULLABLE") != 0; } else throw new ExternalVariableModuleException("External variable " + evarId + " referenced " + "non-existant column \"" + colname + "\"!"); } finally { cmd.close(); } dbev.addColumn(c); } if (dbev.numColumns() == 0) throw new ExternalVariableModuleException( "External variable " + evarId + " did not have any <column> elements!"); _vars.put(evarId, dbev); } catch (SQLException se) { throw new ExternalVariableModuleException("SQL Error", se); } finally { try { conn.close(); } catch (SQLException e) { } } }
From source file:com.amazon.carbonado.repo.jdbc.JDBCStorableIntrospector.java
/** * Uses the given database connection to query database metadata. This is * used to bind storables to tables, and properties to columns. Other * checks are performed to ensure that storable type matches well with the * definition in the database.//w w w . jav a 2 s. co m */ private static <S extends Storable> JDBCStorableInfo<S> examine(StorableInfo<S> mainInfo, Connection con, final String searchCatalog, final String searchSchema, SchemaResolver resolver, boolean primaryKeyCheckDisabled) throws SQLException, SupportException { final DatabaseMetaData meta = con.getMetaData(); final String databaseProductName = meta.getDatabaseProductName(); final String userName = meta.getUserName(); String[] tableAliases; if (mainInfo.getAliasCount() > 0) { tableAliases = mainInfo.getAliases(); } else { String name = mainInfo.getStorableType().getSimpleName(); tableAliases = generateAliases(name); } // Try to find matching table from aliases. String catalog = null, schema = null, tableName = null, tableType = null; findName: { // The call to getTables may return several matching tables. This // map defines the "best" table type we'd like to use. The higher // the number the better. Map<String, Integer> fitnessMap = new HashMap<String, Integer>(); fitnessMap.put("LOCAL TEMPORARY", 1); fitnessMap.put("GLOBAL TEMPORARY", 2); fitnessMap.put("VIEW", 3); fitnessMap.put("SYSTEM TABLE", 4); fitnessMap.put("TABLE", 5); fitnessMap.put("ALIAS", 6); fitnessMap.put("SYNONYM", 7); for (int i = 0; i < tableAliases.length; i++) { ResultSet rs = meta.getTables(searchCatalog, searchSchema, tableAliases[i], null); try { int bestFitness = 0; while (rs.next()) { String type = rs.getString("TABLE_TYPE"); Integer fitness = fitnessMap.get(type); if (fitness != null) { String rsSchema = rs.getString("TABLE_SCHEM"); if (searchSchema == null) { if (userName != null && userName.equalsIgnoreCase(rsSchema)) { // Favor entities whose schema name matches // the user name. fitness += 7; } } if (fitness > bestFitness) { bestFitness = fitness; catalog = rs.getString("TABLE_CAT"); schema = rsSchema; tableName = rs.getString("TABLE_NAME"); tableType = type; } } } } finally { rs.close(); } if (tableName != null) { // Found a match, so stop checking aliases. break; } } } if (tableName == null && !mainInfo.isIndependent()) { StringBuilder buf = new StringBuilder(); buf.append("Unable to find matching table name for type \""); buf.append(mainInfo.getStorableType().getName()); buf.append("\" by looking for "); appendToSentence(buf, tableAliases); buf.append(" with catalog " + searchCatalog + " and schema " + searchSchema); throw new MismatchException(buf.toString()); } String qualifiedTableName = tableName; String resolvedTableName = tableName; // Oracle specific stuff... // TODO: Migrate this to OracleSupportStrategy. if (tableName != null && databaseProductName.toUpperCase().contains("ORACLE")) { if ("TABLE".equals(tableType) && searchSchema != null) { // Qualified table name references the schema. Used by SQL statements. qualifiedTableName = searchSchema + '.' + tableName; } else if ("SYNONYM".equals(tableType)) { // Try to get the real schema. This call is Oracle specific, however. String select = "SELECT TABLE_OWNER,TABLE_NAME " + "FROM ALL_SYNONYMS " + "WHERE OWNER=? AND SYNONYM_NAME=?"; PreparedStatement ps = con.prepareStatement(select); ps.setString(1, schema); // in Oracle, schema is the owner ps.setString(2, tableName); try { ResultSet rs = ps.executeQuery(); try { if (rs.next()) { schema = rs.getString("TABLE_OWNER"); resolvedTableName = rs.getString("TABLE_NAME"); } } finally { rs.close(); } } finally { ps.close(); } } } // Gather information on all columns such that metadata only needs to // be retrieved once. Map<String, ColumnInfo> columnMap = new TreeMap<String, ColumnInfo>(String.CASE_INSENSITIVE_ORDER); if (resolvedTableName != null) { ResultSet rs = meta.getColumns(catalog, schema, resolvedTableName, null); rs.setFetchSize(1000); try { while (rs.next()) { ColumnInfo info = new ColumnInfo(rs); columnMap.put(info.columnName, info); } } finally { rs.close(); } } // Make sure that all properties have a corresponding column. Map<String, ? extends StorableProperty<S>> mainProperties = mainInfo.getAllProperties(); Map<String, String> columnToProperty = new HashMap<String, String>(); Map<String, JDBCStorableProperty<S>> jProperties = new LinkedHashMap<String, JDBCStorableProperty<S>>( mainProperties.size()); ArrayList<String> errorMessages = new ArrayList<String>(); for (StorableProperty<S> mainProperty : mainProperties.values()) { if (mainProperty.isDerived() || mainProperty.isJoin() || tableName == null) { jProperties.put(mainProperty.getName(), new JProperty<S>(mainProperty, primaryKeyCheckDisabled)); continue; } String[] columnAliases; if (mainProperty.getAliasCount() > 0) { columnAliases = mainProperty.getAliases(); } else { columnAliases = generateAliases(mainProperty.getName()); } JDBCStorableProperty<S> jProperty = null; boolean addedError = false; findName: for (int i = 0; i < columnAliases.length; i++) { ColumnInfo columnInfo = columnMap.get(columnAliases[i]); if (columnInfo != null) { AccessInfo accessInfo = getAccessInfo(mainProperty, columnInfo.dataType, columnInfo.dataTypeName, columnInfo.columnSize, columnInfo.decimalDigits); if (accessInfo == null) { TypeDesc propertyType = TypeDesc.forClass(mainProperty.getType()); String message = "Property \"" + mainProperty.getName() + "\" has type \"" + propertyType.getFullName() + "\" which is incompatible with database type \"" + columnInfo.dataTypeName + '"'; if (columnInfo.decimalDigits > 0) { message += " (decimal digits = " + columnInfo.decimalDigits + ')'; } errorMessages.add(message); addedError = true; break findName; } if (columnInfo.nullable) { if (!mainProperty.isNullable() && !mainProperty.isIndependent()) { errorMessages.add( "Property \"" + mainProperty.getName() + "\" must have a Nullable annotation"); } } else { if (mainProperty.isNullable() && !mainProperty.isIndependent()) { errorMessages.add("Property \"" + mainProperty.getName() + "\" must not have a Nullable annotation"); } } boolean autoIncrement = mainProperty.isAutomatic(); if (autoIncrement) { // Need to execute a little query to check if column is // auto-increment or not. This information is not available in // the regular database metadata prior to jdk1.6. PreparedStatement ps = con.prepareStatement( "SELECT " + columnInfo.columnName + " FROM " + tableName + " WHERE 1=0"); try { ResultSet rs = ps.executeQuery(); try { autoIncrement = rs.getMetaData().isAutoIncrement(1); } finally { rs.close(); } } finally { ps.close(); } } jProperty = new JProperty<S>(mainProperty, columnInfo, autoIncrement, primaryKeyCheckDisabled, accessInfo.mResultSetGet, accessInfo.mPreparedStatementSet, accessInfo.getAdapter()); break findName; } } if (jProperty != null) { jProperties.put(mainProperty.getName(), jProperty); columnToProperty.put(jProperty.getColumnName(), jProperty.getName()); } else { if (mainProperty.isIndependent()) { jProperties.put(mainProperty.getName(), new JProperty<S>(mainProperty, primaryKeyCheckDisabled)); } else if (!addedError) { StringBuilder buf = new StringBuilder(); buf.append("Unable to find matching database column for property \""); buf.append(mainProperty.getName()); buf.append("\" by looking for "); appendToSentence(buf, columnAliases); errorMessages.add(buf.toString()); } } } if (errorMessages.size() > 0) { throw new MismatchException(mainInfo.getStorableType(), errorMessages); } // Now verify that primary or alternate keys match. if (resolvedTableName != null) checkPrimaryKey: { ResultSet rs; try { rs = meta.getPrimaryKeys(catalog, schema, resolvedTableName); } catch (SQLException e) { getLog().info("Unable to get primary keys for table \"" + resolvedTableName + "\" with catalog " + catalog + " and schema " + schema + ": " + e); break checkPrimaryKey; } List<String> pkProps = new ArrayList<String>(); try { while (rs.next()) { String columnName = rs.getString("COLUMN_NAME"); String propertyName = columnToProperty.get(columnName); if (propertyName == null) { errorMessages .add("Column \"" + columnName + "\" must be part of primary or alternate key"); continue; } pkProps.add(propertyName); } } finally { rs.close(); } if (errorMessages.size() > 0) { // Skip any extra checks. break checkPrimaryKey; } if (pkProps.size() == 0) { // If no primary keys are reported, don't even bother checking. // There's no consistent way to get primary keys, and entities // like views and synonyms don't usually report primary keys. // A primary key might even be logically defined as a unique // constraint. break checkPrimaryKey; } if (matchesKey(pkProps, mainInfo.getPrimaryKey())) { // Good. Primary key in database is same as in Storable. break checkPrimaryKey; } // Check if Storable has an alternate key which matches the // database's primary key. boolean foundAnyAltKey = false; for (StorableKey<S> altKey : mainInfo.getAlternateKeys()) { if (matchesKey(pkProps, altKey)) { // Okay. Primary key in database matches a Storable // alternate key. foundAnyAltKey = true; // Also check that declared primary key is a strict subset // of the alternate key. If not, keep checking alt keys. if (matchesSubKey(pkProps, mainInfo.getPrimaryKey())) { break checkPrimaryKey; } } } if (foundAnyAltKey) { errorMessages.add("Actual primary key matches a declared alternate key, " + "but declared primary key must be a strict subset. " + mainInfo.getPrimaryKey().getProperties() + " is not a subset of " + pkProps); } else { errorMessages.add("Actual primary key does not match any " + "declared primary or alternate key: " + pkProps); } } if (errorMessages.size() > 0) { if (primaryKeyCheckDisabled) { for (String errorMessage : errorMessages) { getLog().warn("Suppressed error: " + errorMessage); } errorMessages.clear(); } else { throw new MismatchException(mainInfo.getStorableType(), errorMessages); } } // IndexInfo is empty, as querying for it tends to cause a table analyze to run. IndexInfo[] indexInfo = new IndexInfo[0]; if (needsQuotes(tableName)) { String quote = meta.getIdentifierQuoteString(); if (quote != null && !quote.equals(" ")) { tableName = quote + tableName + quote; qualifiedTableName = quote + qualifiedTableName + quote; } } return new JInfo<S>(mainInfo, catalog, schema, tableName, qualifiedTableName, indexInfo, jProperties); }
From source file:org.apache.openjpa.jdbc.sql.DBDictionary.java
/** * Reflect on the schema to find columns matching the given table and * column patterns./*from w ww . j a v a 2 s . c o m*/ */ public Column[] getColumns(DatabaseMetaData meta, DBIdentifier catalog, DBIdentifier schemaName, DBIdentifier tableName, DBIdentifier columnName, Connection conn) throws SQLException { if (DBIdentifier.isNull(tableName) && !supportsNullTableForGetColumns) return null; String sqlSchemaName = null; if (!DBIdentifier.isNull(schemaName)) { sqlSchemaName = schemaName.getName(); } if (!supportsSchemaForGetColumns) sqlSchemaName = null; else sqlSchemaName = getSchemaNameForMetadata(schemaName); beforeMetadataOperation(conn); ResultSet cols = null; try { cols = meta.getColumns(getCatalogNameForMetadata(catalog), sqlSchemaName, getTableNameForMetadata(tableName), getColumnNameForMetadata(columnName)); List columnList = new ArrayList(); while (cols != null && cols.next()) columnList.add(newColumn(cols)); return (Column[]) columnList.toArray(new Column[columnList.size()]); } finally { if (cols != null) try { cols.close(); } catch (Exception e) { } } }
From source file:io.bibleget.BibleGetDB.java
public boolean renewMetaData() { if (instance.connect()) { try {/*from w w w . j a v a2 s.c om*/ DatabaseMetaData dbMeta; dbMeta = instance.conn.getMetaData(); try (ResultSet rs3 = dbMeta.getTables(null, null, "METADATA", null)) { if (rs3.next()) { //System.out.println("Table METADATA exists..."); try (Statement stmt = instance.conn.createStatement()) { HTTPCaller myHTTPCaller = new HTTPCaller(); String myResponse; myResponse = myHTTPCaller.getMetaData("biblebooks"); if (myResponse != null) { JsonReader jsonReader = Json.createReader(new StringReader(myResponse)); JsonObject json = jsonReader.readObject(); JsonArray arrayJson = json.getJsonArray("results"); if (arrayJson != null) { ListIterator pIterator = arrayJson.listIterator(); while (pIterator.hasNext()) { try (Statement stmt1 = instance.conn.createStatement()) { int index = pIterator.nextIndex(); JsonArray currentJson = (JsonArray) pIterator.next(); String biblebooks_str = currentJson.toString(); //.replaceAll("\"", "\\\\\""); //System.out.println("BibleGetDB line 267: BIBLEBOOKS"+Integer.toString(index)+"='"+biblebooks_str+"'"); String stmt_str = "UPDATE METADATA SET BIBLEBOOKS" + Integer.toString(index) + "='" + biblebooks_str + "' WHERE ID=0"; //System.out.println("executing update: "+stmt_str); int update = stmt1.executeUpdate(stmt_str); //System.out.println("executeUpdate resulted in: "+Integer.toString(update)); stmt1.close(); } } } arrayJson = json.getJsonArray("languages"); if (arrayJson != null) { try (Statement stmt2 = instance.conn.createStatement()) { String languages_str = arrayJson.toString(); //.replaceAll("\"", "\\\\\""); String stmt_str = "UPDATE METADATA SET LANGUAGES='" + languages_str + "' WHERE ID=0"; int update = stmt2.executeUpdate(stmt_str); stmt2.close(); } } } myResponse = myHTTPCaller.getMetaData("bibleversions"); if (myResponse != null) { JsonReader jsonReader = Json.createReader(new StringReader(myResponse)); JsonObject json = jsonReader.readObject(); JsonObject objJson = json.getJsonObject("validversions_fullname"); if (objJson != null) { String bibleversions_str = objJson.toString(); //.replaceAll("\"", "\\\\\""); try (Statement stmt3 = instance.conn.createStatement()) { String stmt_str = "UPDATE METADATA SET VERSIONS='" + bibleversions_str + "' WHERE ID=0"; int update = stmt3.executeUpdate(stmt_str); stmt3.close(); } Set<String> versionsabbrev = objJson.keySet(); if (!versionsabbrev.isEmpty()) { String versionsabbrev_str = ""; for (String s : versionsabbrev) { versionsabbrev_str += ("".equals(versionsabbrev_str) ? "" : ",") + s; } myResponse = myHTTPCaller .getMetaData("versionindex&versions=" + versionsabbrev_str); if (myResponse != null) { jsonReader = Json.createReader(new StringReader(myResponse)); json = jsonReader.readObject(); objJson = json.getJsonObject("indexes"); if (objJson != null) { for (String name : objJson.keySet()) { JsonObjectBuilder tempBld = Json.createObjectBuilder(); JsonObject book_num = objJson.getJsonObject(name); tempBld.add("book_num", book_num.getJsonArray("book_num")); tempBld.add("chapter_limit", book_num.getJsonArray("chapter_limit")); tempBld.add("verse_limit", book_num.getJsonArray("verse_limit")); JsonObject temp = tempBld.build(); String versionindex_str = temp.toString(); //.replaceAll("\"", "\\\\\""); //add new column to METADATA table name+"IDX" VARCHAR(5000) //update METADATA table SET name+"IDX" = versionindex_str try (ResultSet rs1 = dbMeta.getColumns(null, null, "METADATA", name + "IDX")) { boolean updateFlag = false; if (rs1.next()) { //column already exists updateFlag = true; } else { try (Statement stmt4 = instance.conn .createStatement()) { String sql = "ALTER TABLE METADATA ADD COLUMN " + name + "IDX VARCHAR(5000)"; boolean colAdded = stmt4.execute(sql); if (colAdded == false) { int count = stmt4.getUpdateCount(); if (count == -1) { //System.out.println("The result is a ResultSet object or there are no more results."); } else if (count == 0) { //0 rows affected updateFlag = true; } } stmt4.close(); } } if (updateFlag) { try (Statement stmt5 = instance.conn .createStatement()) { String sql1 = "UPDATE METADATA SET " + name + "IDX='" + versionindex_str + "' WHERE ID=0"; boolean rowsUpdated = stmt5.execute(sql1); stmt5.close(); } } } } } } } } } stmt.close(); } } rs3.close(); } instance.disconnect(); } catch (SQLException ex) { Logger.getLogger(BibleGetDB.class.getName()).log(Level.SEVERE, null, ex); return false; } return true; } return false; }
From source file:org.biomart.configurator.controller.MartController.java
public void updateDatasetFromSource(Dataset ds) throws SQLException, DataModelException { Mart mart = ds.getParentMart();//from w w w .j a v a2 s . c o m List<SourceSchema> sss = mart.getIncludedSchemas(); // assuming that only one for now if (McUtils.isCollectionEmpty(sss)) return; SourceSchema ss = sss.get(0); final DatabaseMetaData dmd = ss.getConnection().getMetaData(); final String catalog = ss.getConnection().getCatalog(); // List of objects storing orphan key column and its table name List<ForeignKey> orphanFKList = new ArrayList<ForeignKey>(); StringBuffer orphanSearch = new StringBuffer(); boolean orphanBool = false; /* * try { orphanBool = findOrphanFKFromDB(orphanFKList, orphanSearch, mart); if (orphanBool) { Frame frame = new * Frame(); Object[] options = { "Proceed", "Abort Synchronization" }; int n = JOptionPane .showOptionDialog( * frame, * "Some columns in relations no longer exist in source DB. This may be caused by renaming/dropping tables/columns in source DB.\n" * + * "When choose 'Proceed', you will be prompted to save this information for later use. Do you want to proceed?" * +"\n", "Schema Update Warning", JOptionPane.YES_NO_OPTION, JOptionPane.WARNING_MESSAGE, null, // do // not // * use a // custom // Icon options, // the titles of buttons options[1]); // default button title if (n == * JOptionPane.NO_OPTION) { return; } else{ new SaveOrphanKeyDialog("Orphan Relation", orphanSearch.toString()); * } } } catch (Exception e) { e.printStackTrace(); } // Now that user decides to sync GUI model to DB schema, * remove orphan foreign key clearOrphanForeignKey(orphanFKList); */ // Create a list of existing tables. During this method, we remove // from this list all tables that still exist in the database. At // the end of the method, the list contains only those tables // which no longer exist, so they will be dropped. final Collection<Table> tablesToBeDropped = new HashSet<Table>(ss.getTables()); // Load tables and views from database, then loop over them. ResultSet dbTables; String sName = ss.getJdbcLinkObject().getJdbcType().useSchema() ? ss.getJdbcLinkObject().getSchemaName() : catalog; dbTables = dmd.getTables(catalog, sName, "%", new String[] { "TABLE", "VIEW", "ALIAS", "SYNONYM" }); // Do the loop. final Collection<Table> tablesToBeKept = new HashSet<Table>(); while (dbTables.next()) { // Check schema and catalog. final String catalogName = dbTables.getString("TABLE_CAT"); final String schemaName = dbTables.getString("TABLE_SCHEM"); String schemaPrefix = schemaName; // What is the table called? final String dbTableName = dbTables.getString("TABLE_NAME"); Log.debug("Processing table " + catalogName + "." + dbTableName); // Look to see if we already have a table by this name defined. // If we do, reuse it. If not, create a new table. Table dbTable = ss.getTableByName(dbTableName); if (dbTable == null) try { dbTable = new SourceTable(ss, dbTableName); dbTable.setVisibleModified(true); ss.addTable(dbTable); } catch (final Throwable t) { throw new BioMartError(t); } // Add schema prefix to list. if (schemaPrefix != null) dbTable.addInPartitions(schemaPrefix); // Table exists, so remove it from our list of tables to be // dropped at the end of the method. tablesToBeDropped.remove(dbTable); tablesToBeKept.add(dbTable); } dbTables.close(); // Loop over all columns. for (final Iterator<Table> i = tablesToBeKept.iterator(); i.hasNext();) { final Table dbTable = (Table) i.next(); final String dbTableName = dbTable.getName(); // Make a list of all the columns in the table. Any columns // remaining in this list by the end of the loop will be // dropped. final Collection<Column> colsToBeDropped = new HashSet<Column>(dbTable.getColumnList()); // Clear out the existing schema partition info on all cols. for (final Iterator<Column> j = dbTable.getColumnList().iterator(); j.hasNext();) ((Column) j.next()).cleanInPartitions(); // Load the table columns from the database, then loop over // them. Log.debug("Loading table column list for " + dbTableName); ResultSet dbTblCols; dbTblCols = dmd.getColumns(catalog, sName, dbTableName, "%"); // FIXME: When using Oracle, if the table is a synonym then the // above call returns no results. while (dbTblCols.next()) { // Check schema and catalog. final String catalogName = dbTblCols.getString("TABLE_CAT"); final String schemaName = dbTblCols.getString("TABLE_SCHEM"); String schemaPrefix = null; // No prefix if partitions are empty; /* * if (!this.getPartitions().isEmpty()) { if ("".equals(dmd.getSchemaTerm())) // Use catalog name to get * prefix. schemaPrefix = (String) this.getPartitions().get( catalogName); else // Use schema name to * get prefix. schemaPrefix = (String) this.getPartitions().get( schemaName); // Don't want to include * if prefix is still null. if (schemaPrefix == null) continue; } */ // What is the column called, and is it nullable? final String dbTblColName = dbTblCols.getString("COLUMN_NAME"); Log.debug("Processing column " + dbTblColName); // Look to see if the column already exists on this table. // If it does, reuse it. Else, create it. Column dbTblCol = (Column) dbTable.getColumnByName(dbTblColName); if (dbTblCol == null) try { dbTblCol = new SourceColumn((SourceTable) dbTable, dbTblColName); dbTblCol.setVisibleModified(true); dbTable.addColumn(dbTblCol); } catch (final Throwable t) { throw new BioMartError(t); } // Column exists, so remove it from our list of columns to // be dropped at the end of the loop. colsToBeDropped.remove(dbTblCol); if (schemaPrefix != null) dbTblCol.addInPartitions(schemaPrefix); } dbTblCols.close(); // Drop all columns that are left in the list, as they no longer // exist in the database. for (final Iterator<Column> j = colsToBeDropped.iterator(); j.hasNext();) { final Column column = (Column) j.next(); Log.debug("Dropping redundant column " + column.getName()); dbTable.getColumnList().remove(column); } } // Remove from schema all tables not found in the database, using // the list we constructed above. for (final Iterator<Table> i = tablesToBeDropped.iterator(); i.hasNext();) { final Table existingTable = (Table) i.next(); Log.debug("Dropping redundant table " + existingTable); final String tableName = existingTable.getName(); // By clearing its keys we will also clear its relations. for (final Iterator<Key> j = existingTable.getKeys().iterator(); j.hasNext();) { j.next().removeAllRelations(); } existingTable.setPrimaryKey(null); existingTable.getForeignKeys().clear(); ss.removeTableByName(tableName); } // Get and create primary keys. // Work out a list of all foreign keys currently existing. // Any remaining in this list later will be dropped. final Collection<ForeignKey> fksToBeDropped = new HashSet<ForeignKey>(); for (final Iterator<Table> i = ss.getTables().iterator(); i.hasNext();) { final Table t = (Table) i.next(); fksToBeDropped.addAll(t.getForeignKeys()); // Obtain the primary key from the database. Even in databases // without referential integrity, the primary key is still // defined and can be obtained from the metadata. Log.debug("Loading table primary keys"); String searchCatalog = catalog; String searchSchema = sName; /* * if (!t.getSchemaPartitions().isEmpty()) { // Locate partition with first prefix. final String prefix = * (String) t.getSchemaPartitions() .iterator().next(); String schemaName = (String) new InverseMap(this * .getPartitions()).get(prefix); if (schemaName == null) // Should never happen. throw new BioMartError(); * if ("".equals(dmd.getSchemaTerm())) searchCatalog = schemaName; searchSchema = schemaName; } */ final ResultSet dbTblPKCols = dmd.getPrimaryKeys(searchCatalog, searchSchema, t.getName()); // Load the primary key columns into a map keyed by column // position. // In other words, the first column in the key has a map key of // 1, and so on. We do this because we can't guarantee we'll // read the key columns from the database in the correct order. // We keep the map sorted, so that when we iterate over it later // we get back the columns in the correct order. final Map<Short, Column> pkCols = new TreeMap<Short, Column>(); while (dbTblPKCols.next()) { final String pkColName = dbTblPKCols.getString("COLUMN_NAME"); final Short pkColPosition = new Short(dbTblPKCols.getShort("KEY_SEQ")); pkCols.put(pkColPosition, t.getColumnByName(pkColName)); } dbTblPKCols.close(); // Did DMD find a PK? If not, which is really unusual but // potentially may happen, attempt to find one by looking for a // single column with the same name as the table or with '_id' // appended. // Only do this if we are using key-guessing. if (pkCols.isEmpty() && ss.getJdbcLinkObject().isKeyGuessing()) { Log.debug("Found no primary key, so attempting to guess one"); // Plain version first. Column candidateCol = (Column) t.getColumnByName(t.getName()); // Try with '_id' appended if plain version turned up // nothing. if (candidateCol == null) candidateCol = (Column) t.getColumnByName(t.getName() + Resources.get("primaryKeySuffix")); // Found something? Add it to the primary key columns map, // with a dummy key of 1. (Use Short for the key because // that // is what DMD would have used had it found anything // itself). if (candidateCol != null) pkCols.put(Short.valueOf("1"), candidateCol); } // Obtain the existing primary key on the table, if the table // previously existed and even had one in the first place. final PrimaryKey existingPK = t.getPrimaryKey(); // Did we find a PK on the database copy of the table? if (!pkCols.isEmpty()) { // Yes, we found a PK on the database copy of the table. So, // create a new key based around the columns we identified. PrimaryKey candidatePK; try { candidatePK = new PrimaryKey(new ArrayList<Column>(pkCols.values())); } catch (final Throwable th) { throw new BioMartError(th); } // If the existing table has no PK, or has a PK which // matches and is not incorrect, or has a PK which does not // match // and is not handmade, replace that PK with the one we // found. // This way we preserve any existing handmade PKs, and don't // override any marked as incorrect. try { if (existingPK == null) t.setPrimaryKey(candidatePK); else if (existingPK.equals(candidatePK) && existingPK.getStatus().equals(ComponentStatus.HANDMADE)) existingPK.setStatus(ComponentStatus.INFERRED); else if (!existingPK.equals(candidatePK) && !existingPK.getStatus().equals(ComponentStatus.HANDMADE)) t.setPrimaryKey(candidatePK); } catch (final Throwable th) { throw new BioMartError(th); } } else // No, we did not find a PK on the database copy of the // table, so that table should not have a PK at all. So if the // existing table has a PK which is not handmade, remove it. if (existingPK != null && !existingPK.getStatus().equals(ComponentStatus.HANDMADE)) try { t.setPrimaryKey(null); } catch (final Throwable th) { throw new BioMartError(th); } } // Are we key-guessing? Key guess the foreign keys, passing in a // reference to the list of existing foreign keys. After this call // has completed, the list will contain all those foreign keys which // no longer exist, and can safely be dropped. if (ss.getJdbcLinkObject().isKeyGuessing()) this.synchroniseKeysUsingKeyGuessing(ss, fksToBeDropped); // Otherwise, use DMD to do the same, also passing in the list of // existing foreign keys to be updated as the call progresses. Also // pass in the DMD details so it doesn't have to work them out for // itself. else this.synchroniseKeysUsingDMD(ss, fksToBeDropped, dmd, sName, catalog); // Drop any foreign keys that are left over (but not handmade ones). for (final Iterator<ForeignKey> i = fksToBeDropped.iterator(); i.hasNext();) { final Key k = (Key) i.next(); if (k.getStatus().equals(ComponentStatus.HANDMADE)) continue; Log.debug("Dropping redundant foreign key " + k); for (final Iterator<Relation> r = k.getRelations().iterator(); r.hasNext();) { final Relation rel = (Relation) r.next(); rel.getFirstKey().getRelations().remove(rel); rel.getSecondKey().getRelations().remove(rel); } k.getTable().getForeignKeys().remove(k); } // rebuild mart this.rebuildMartFromSource(mart); }