List of usage examples for java.sql DatabaseMetaData getColumns
ResultSet getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern)
throws SQLException;
From source file:org.pentaho.aggdes.model.mondrian.validate.DimensionFkValidator.java
public List<ValidationMessage> validateCube(Schema schema, Cube cube, Connection conn) { List<ValidationMessage> messages = new ArrayList<ValidationMessage>(); Map<String, Boolean> checkedRelations = new HashMap<String, Boolean>(); // ~ Get DatabaseMetaData ========================================================================================== DatabaseMetaData meta = null; try {/* w ww.j av a 2s . c om*/ meta = conn.getMetaData(); } catch (SQLException e) { if (logger.isErrorEnabled()) { logger.error("an exception occurred", e); //$NON-NLS-1$ } return fatal(e, messages); } if (logger.isDebugEnabled()) { logger.debug("processing cube \"" + cube.name + "\""); //$NON-NLS-1$ //$NON-NLS-2$ } // TODO: include validation support for mondrian views if (!(cube.fact instanceof Table)) { if (logger.isDebugEnabled()) { logger.debug("cube \"" + cube.name + "\" contains unsupported fact type, " + cube.fact); //$NON-NLS-1$ //$NON-NLS-2$ } return messages; } // ~ Check: Foreign key on dimension table========================================================================== String factTableName = ((Table) cube.fact).name; String schemaName = ((Table) cube.fact).schema; for (CubeDimension dim : cube.dimensions) { String foreignKey = dim.foreignKey; if (logger.isDebugEnabled()) { logger.debug("processing dimension \"" + dim.name + "\""); } if (foreignKey == null) { // we are dealing with a degenerate dimension if (logger.isDebugEnabled()) { logger.debug("dimension is degenerate, skipping"); } continue; } if (checkedRelations.containsKey(makeKey(schemaName, factTableName, foreignKey))) { if (logger.isDebugEnabled()) { logger.debug("already checked that foreign key not null on column \"" //$NON-NLS-1$ + (null == schemaName ? "" : schemaName + ".") //$NON-NLS-1$ //$NON-NLS-2$ + factTableName + "." + foreignKey + "\"; skipping"); //$NON-NLS-1$ } continue; } else { if (logger.isDebugEnabled()) { logger.debug("checking that foreign key not null on column \"" //$NON-NLS-1$ + (null == schemaName ? "" : schemaName + ".") //$NON-NLS-1$ //$NON-NLS-2$ + factTableName + "." + foreignKey + "\""); //$NON-NLS-1$ } } ResultSet rs = null; try { rs = meta.getColumns(null, schemaName, factTableName, foreignKey); } catch (SQLException e) { if (logger.isErrorEnabled()) { logger.error("an exception occurred", e); //$NON-NLS-1$ } return fatal(e, messages); } boolean isNullable = true; try { while (rs.next()) { if (rs.getString("IS_NULLABLE").equals("NO")) { if (logger.isDebugEnabled()) { logger.debug("column is not nullable; skipping value check"); } isNullable = false; } break; } } catch (SQLException e) { if (logger.isErrorEnabled()) { logger.error("an exception occurred", e); //$NON-NLS-1$ } return fatal(e, messages); } if (isNullable) { if (logger.isDebugEnabled()) { logger.debug("falling back on checking column values"); } Statement stmt = null; boolean nulls = false; try { String sql = MessageFormat.format("select count(*) as null_count from {0} where {1} is null", (null == schemaName ? "" : schemaName + ".") + factTableName, foreignKey); if (logger.isDebugEnabled()) { logger.debug("executing query: " + sql); } stmt = conn.createStatement(); ResultSet rs2 = stmt.executeQuery(sql); while (rs2.next()) { long nullCount = rs2.getLong("null_count"); if (nullCount > 0) { if (logger.isDebugEnabled()) { logger.debug("foreign key column contains null values"); } nulls = true; } break; } } catch (SQLException e) { if (logger.isErrorEnabled()) { logger.error("an exception occurred", e); } return fatal(e, messages); } finally { try { if (null != stmt) { stmt.close(); } } catch (SQLException e) { if (logger.isErrorEnabled()) { logger.error("an exception occurred", e); } return fatal(e, messages); } } if (nulls) { append(messages, ERROR, "ERROR_CUBE_FK_CHECK", cube.name, factTableName, foreignKey); //$NON-NLS-1$ } else { append(messages, OK, "OK_CUBE_FK_CHECK", cube.name, factTableName, foreignKey); //$NON-NLS-1$ } } else { append(messages, OK, "OK_CUBE_FK_CHECK", cube.name, factTableName, foreignKey); //$NON-NLS-1$ } checkedRelations.put(makeKey(schemaName, factTableName, foreignKey), true); } return messages; }
From source file:org.lockss.db.DbMigrator.java
/** * Extracts the metadata of the tables of a database schema. * /* w ww . j a v a 2 s .c o m*/ * @param conn * A Connection with the database connection to be used. * @param schema * A String with the database schema. * * @return a Map<String, DbTable> with the metadata of the tables. * @throws DbMigratorException * if there are problems extracting the metadata. */ private Map<String, DbTable> extractDbMetadata(Connection conn, String schema) throws DbMigratorException { final String DEBUG_HEADER = "populateDbMetadata(): "; if (log.isDebug2()) log.debug2(DEBUG_HEADER + "schema = " + schema); if (conn == null) { throw new DbMigratorException("Null connection"); } Map<String, DbTable> tableMap = new HashMap<String, DbTable>(); ResultSet tableResultSet = null; String tableName = null; ResultSet columnResultSet = null; ResultSet pkResultSet = null; ResultSet fkResultSet = null; try { DatabaseMetaData metadata = DbManagerSql.getMetadata(conn); // Get the database schema table data. tableResultSet = DbManagerSql.getStandardTables(conn, null, schema, null); // Loop through all the schema tables. while (tableResultSet.next()) { tableName = tableResultSet.getString("TABLE_NAME"); log.debug2(DEBUG_HEADER + "TABLE_NAME = " + tableName); String tableType = tableResultSet.getString("TABLE_TYPE"); log.debug2(DEBUG_HEADER + "TABLE_TYPE = " + tableType); log.debug2(DEBUG_HEADER + ""); // Check that this is not a view, etc. if ("TABLE".equals(tableType)) { // Yes: Get the table column metadata. DbTable table = new DbTable(tableName.toLowerCase()); DbRow row = new DbRow(tableName.toLowerCase()); table.setRow(row); List<DbColumn> columns = row.getColumns(); columnResultSet = metadata.getColumns(null, schema, tableName, null); // Loop through each table column. while (columnResultSet.next()) { String columnName = columnResultSet.getString("COLUMN_NAME").toLowerCase(); log.debug2(DEBUG_HEADER + "columnName = '" + columnName + "'."); int columnType = columnResultSet.getInt("DATA_TYPE"); log.debug2(DEBUG_HEADER + "columnType = '" + columnType + "'."); int position = columnResultSet.getInt("ORDINAL_POSITION"); log.debug2(DEBUG_HEADER + "position = '" + position + "'."); DbColumn column = new DbColumn(columnName, columnType, position); columns.add(column); } // Remember any primary key the table may have. pkResultSet = metadata.getPrimaryKeys(null, schema, tableName); if (pkResultSet.next()) { String pkColumnName = pkResultSet.getString("COLUMN_NAME").toLowerCase(); log.debug2(DEBUG_HEADER + "pkColumnName = '" + pkColumnName + "'."); for (DbColumn column : columns) { if (pkColumnName.equals(column.getName())) { column.setPk(true); break; } } } // Remember any foreign keys the table may have. fkResultSet = metadata.getImportedKeys(null, schema, tableName); while (fkResultSet.next()) { String fkColumnName = fkResultSet.getString("FKCOLUMN_NAME").toLowerCase(); log.debug2(DEBUG_HEADER + "fkColumnName = '" + fkColumnName + "'."); String fkTableName = fkResultSet.getString("PKTABLE_NAME").toLowerCase(); log.debug2(DEBUG_HEADER + "fkTableName = '" + fkTableName + "'."); for (DbColumn column : columns) { if (fkColumnName.equals(column.getName())) { column.setFkTable(fkTableName); break; } } } // Sort the columns by their ordinal position. Collections.sort(columns); if (log.isDebug3()) { for (DbColumn column : columns) { log.debug3(DEBUG_HEADER + "column = '" + column + "'."); } } // Add the table to the result. tableMap.put(tableName.toLowerCase(), table); } } } catch (SQLException sqle) { String message = "Cannot populate DB metadata."; log.error(message); log.error("TABLE_NAME = " + tableName); throw new DbMigratorException(message, sqle); } catch (RuntimeException re) { String message = "Cannot populate DB metadata."; log.error(message); log.error("TABLE_NAME = " + tableName); throw new DbMigratorException(message, re); } finally { DbManagerSql.safeCloseResultSet(fkResultSet); DbManagerSql.safeCloseResultSet(pkResultSet); DbManagerSql.safeCloseResultSet(columnResultSet); DbManagerSql.safeCloseResultSet(tableResultSet); try { DbManagerSql.rollback(conn, log); } catch (SQLException sqle) { throw new DbMigratorException(sqle); } catch (RuntimeException re) { throw new DbMigratorException(re); } } if (log.isDebug2()) log.debug2(DEBUG_HEADER + "tableMap.size() = " + tableMap.size()); return tableMap; }
From source file:ua.utility.kfsdbupgrade.App.java
/** * @param dmd//from w w w . j a v a2s . com * {@link DatabaseMetaData} describing the database to check * @param schema * {@link String} of the schema name to check * @param tname * {@link String} of the table name to check * @param cname * {@link String} of the column name to check * @return <code>true</code> if the column <code>cname</code> in the table * <code>tname</code> in the schema <code>schema</code> in the * database described by <code>dmd</code> is a Java numeric type; * <code>false</code> otherwise * @throws Exception * Any {@link Exception}s encountered will be rethrown * @see {@link #isNumericJavaType(int)} */ private boolean isNumericColumn(DatabaseMetaData dmd, String schema, String tname, String cname) throws Exception { boolean retval = false; ResultSet res = null; try { res = dmd.getColumns(null, schema, tname, cname); if (res.next()) { retval = isNumericJavaType(res.getInt(5)); } } finally { closeDbObjects(null, null, res); } return retval; }
From source file:jef.database.DbMetaData.java
/** * ?null/*from w w w . j a v a 2 s.co m*/ * * @param tableName * ?? * @param column * ?? * @return null * @throws SQLException */ public Column getColumn(String tableName, String column) throws SQLException { tableName = info.profile.getObjectNameToUse(tableName); column = info.profile.getObjectNameToUse(column); Connection conn = getConnection(false); Collection<Index> indexes = getIndexes(tableName); DatabaseMetaData databaseMetaData = conn.getMetaData(); String schema = this.schema; int n = tableName.indexOf('.'); if (n > 0) {// ???schema schema = tableName.substring(0, n); tableName = tableName.substring(n + 1); } ResultSet rs = null; try { rs = databaseMetaData.getColumns(null, schema, tableName, column); Column result = null; if (rs.next()) { result = new Column(); populateColumn(result, rs, tableName, indexes); } return result; } finally { DbUtils.close(rs); releaseConnection(conn); } }
From source file:org.wso2.carbon.apimgt.core.dao.impl.ApiDAOImpl.java
private boolean checkTableColumnExists(DatabaseMetaData databaseMetaData, String tableName, String columnName) throws APIMgtDAOException { try (ResultSet rs = databaseMetaData.getColumns(null, null, tableName, columnName)) { return rs.next(); } catch (SQLException e) { throw new APIMgtDAOException(e); }// w ww.java 2 s. c om }
From source file:jef.database.DbMetaData.java
/** * //from w w w. j a v a 2 s. c o m * * @param tableName * ?? * @return A collection of columns. * @throws SQLException * @see Column */ public List<Column> getColumns(String tableName, boolean needRemark) throws SQLException { tableName = info.profile.getObjectNameToUse(tableName); Connection conn = getConnection(needRemark); DatabaseMetaData databaseMetaData = conn.getMetaData(); String schema = this.schema; int n = tableName.indexOf('.'); if (n > 0) {// ???schema schema = tableName.substring(0, n); tableName = tableName.substring(n + 1); } ResultSet rs = null; List<Column> list = new ArrayList<Column>(); Collection<Index> indexes = null; try { rs = databaseMetaData.getColumns(null, schema, tableName, "%"); while (rs.next()) { if (indexes == null) { // ?Oracle?getIndexInfo()??????? // ???? indexes = getIndexes(tableName); } Column column = new Column(); populateColumn(column, rs, tableName, indexes); list.add(column); } } finally { DbUtils.close(rs); releaseConnection(conn); } Collections.sort(list, (a, b) -> Integer.compare(a.getOrdinal(), b.getOrdinal())); return list; }
From source file:org.eclipse.ecr.core.storage.sql.jdbc.JDBCMapper.java
protected void createTables() throws SQLException { sqlInfo.executeSQLStatements("beforeTableCreation", this); if (testProps.containsKey(TEST_UPGRADE)) { // create "old" tables sqlInfo.executeSQLStatements("testUpgrade", this); }// w ww . j a v a 2 s . co m String schemaName = sqlInfo.dialect.getConnectionSchema(connection); DatabaseMetaData metadata = connection.getMetaData(); Set<String> tableNames = findTableNames(metadata, schemaName); Database database = sqlInfo.getDatabase(); Map<String, List<Column>> added = new HashMap<String, List<Column>>(); Statement st = connection.createStatement(); for (Table table : database.getTables()) { String tableName = getTableName(table.getPhysicalName()); if (tableNames.contains(tableName.toUpperCase())) { sqlInfo.dialect.existingTableDetected(connection, table, model, sqlInfo.database); } else { /* * Create missing table. */ boolean create = sqlInfo.dialect.preCreateTable(connection, table, model, sqlInfo.database); if (!create) { log.warn("Creation skipped for table: " + tableName); continue; } String sql = table.getCreateSql(); logger.log(sql); try { st.execute(sql); } catch (SQLException e) { throw new SQLException("Error creating table: " + sql + " : " + e.getMessage(), e); } for (String s : table.getPostCreateSqls(model)) { logger.log(s); try { st.execute(s); } catch (SQLException e) { throw new SQLException("Error post creating table: " + s + " : " + e.getMessage(), e); } } for (String s : sqlInfo.dialect.getPostCreateTableSqls(table, model, sqlInfo.database)) { logger.log(s); try { st.execute(s); } catch (SQLException e) { throw new SQLException("Error post creating table: " + s + " : " + e.getMessage(), e); } } added.put(table.getKey(), null); // null = table created } /* * Get existing columns. */ ResultSet rs = metadata.getColumns(null, schemaName, tableName, "%"); Map<String, Integer> columnTypes = new HashMap<String, Integer>(); Map<String, String> columnTypeNames = new HashMap<String, String>(); Map<String, Integer> columnTypeSizes = new HashMap<String, Integer>(); while (rs.next()) { String schema = rs.getString("TABLE_SCHEM"); if (schema != null) { // null for MySQL, doh! if ("INFORMATION_SCHEMA".equals(schema.toUpperCase())) { // H2 returns some system tables (locks) continue; } } String columnName = rs.getString("COLUMN_NAME").toUpperCase(); columnTypes.put(columnName, Integer.valueOf(rs.getInt("DATA_TYPE"))); columnTypeNames.put(columnName, rs.getString("TYPE_NAME")); columnTypeSizes.put(columnName, Integer.valueOf(rs.getInt("COLUMN_SIZE"))); } /* * Update types and create missing columns. */ List<Column> addedColumns = new LinkedList<Column>(); for (Column column : table.getColumns()) { String upperName = column.getPhysicalName().toUpperCase(); Integer type = columnTypes.remove(upperName); if (type == null) { log.warn("Adding missing column in database: " + column.getFullQuotedName()); String sql = table.getAddColumnSql(column); logger.log(sql); try { st.execute(sql); } catch (SQLException e) { throw new SQLException("Error adding column: " + sql + " : " + e.getMessage(), e); } for (String s : table.getPostAddSqls(column, model)) { logger.log(s); try { st.execute(s); } catch (SQLException e) { throw new SQLException("Error post adding column: " + s + " : " + e.getMessage(), e); } } addedColumns.add(column); } else { int expected = column.getJdbcType(); int actual = type.intValue(); String actualName = columnTypeNames.get(upperName); Integer actualSize = columnTypeSizes.get(upperName); if (!column.setJdbcType(actual, actualName, actualSize.intValue())) { log.error(String.format("SQL type mismatch for %s: expected %s, database has %s / %s (%s)", column.getFullQuotedName(), Integer.valueOf(expected), type, actualName, actualSize)); } } } if (!columnTypes.isEmpty()) { log.warn("Database contains additional unused columns for table " + table.getQuotedName() + ": " + StringUtils.join(new ArrayList<String>(columnTypes.keySet()), ", ")); } if (!addedColumns.isEmpty()) { if (added.containsKey(table.getKey())) { throw new AssertionError(); } added.put(table.getKey(), addedColumns); } } closeStatement(st); if (testProps.containsKey(TEST_UPGRADE)) { // create "old" content in tables sqlInfo.executeSQLStatements("testUpgradeOldTables", this); } // run upgrade for each table if added columns or test for (Entry<String, List<Column>> en : added.entrySet()) { List<Column> addedColumns = en.getValue(); String tableKey = en.getKey(); upgradeTable(tableKey, addedColumns); } sqlInfo.executeSQLStatements("afterTableCreation", this); sqlInfo.dialect.performAdditionalStatements(connection); }
From source file:org.executequery.databasemediators.spi.DefaultStatementExecutor.java
/** <p>Retrieves a description of the specified table using * the connection's <code>DatabaseMetaData</code> object * and the method <code>getColumns(...)</code>. * * @param the table name to describe/* w ww . jav a2 s . c o m*/ * @return the query result */ private SqlStatementResult getTableDescription(String tableName) throws SQLException { if (!prepared()) { return statementResult; } DatabaseHost host = null; try { /* ------------------------------------------------- * Database meta data values can be case-sensitive. * search for a match and use as returned from dmd. * ------------------------------------------------- */ String name = tableName; String catalog = null; String schema = null; host = new DatabaseObjectFactoryImpl().createDatabaseHost(databaseConnection); int nameDelim = tableName.indexOf('.'); if (nameDelim != -1) { name = tableName.substring(nameDelim + 1); String value = tableName.substring(0, nameDelim); DatabaseMetaData databaseMetaData = host.getDatabaseMetaData(); if (host.supportsCatalogsInTableDefinitions()) { ResultSet resultSet = databaseMetaData.getCatalogs(); while (resultSet.next()) { String _catalog = resultSet.getString(1); if (value.equalsIgnoreCase(_catalog)) { catalog = _catalog; break; } } resultSet.close(); } else if (host.supportsSchemasInTableDefinitions()) { ResultSet resultSet = databaseMetaData.getCatalogs(); while (resultSet.next()) { String _schema = resultSet.getString(1); if (value.equalsIgnoreCase(_schema)) { schema = _schema; break; } } resultSet.close(); } } DatabaseMetaData databaseMetaData = host.getDatabaseMetaData(); ResultSet resultSet = databaseMetaData.getTables(catalog, schema, null, null); String nameToSearchOn = null; while (resultSet.next()) { String _tableName = resultSet.getString(3); if (_tableName.equalsIgnoreCase(name)) { nameToSearchOn = _tableName; break; } } resultSet.close(); if (StringUtils.isNotBlank(nameToSearchOn)) { databaseMetaData = conn.getMetaData(); resultSet = databaseMetaData.getColumns(catalog, schema, nameToSearchOn, null); statementResult.setResultSet(resultSet); } else { statementResult.setMessage("Invalid table name"); } } catch (SQLException e) { statementResult.setSqlException(e); finished(); } catch (OutOfMemoryError e) { statementResult.setMessage(e.getMessage()); releaseResources(); } finally { if (host != null) { host.close(); } } return statementResult; }
From source file:com.mirth.connect.connectors.jdbc.DatabaseConnectorServlet.java
@Override public SortedSet<Table> getTables(String channelId, String channelName, String driver, String url, String username, String password, Set<String> tableNamePatterns, String selectLimit, Set<String> resourceIds) { CustomDriver customDriver = null;//www.j a v a2s.co m Connection connection = null; try { url = replacer.replaceValues(url, channelId, channelName); username = replacer.replaceValues(username, channelId, channelName); password = replacer.replaceValues(password, channelId, channelName); String schema = null; try { MirthContextFactory contextFactory = contextFactoryController.getContextFactory(resourceIds); try { ClassLoader isolatedClassLoader = contextFactory.getIsolatedClassLoader(); if (isolatedClassLoader != null) { customDriver = new CustomDriver(isolatedClassLoader, driver); logger.debug("Custom driver created: " + customDriver.toString() + ", Version " + customDriver.getMajorVersion() + "." + customDriver.getMinorVersion()); } else { logger.debug("Custom classloader is not being used, defaulting to DriverManager."); } } catch (Exception e) { logger.debug("Error creating custom driver, defaulting to DriverManager.", e); } } catch (Exception e) { logger.debug("Error retrieving context factory, defaulting to DriverManager.", e); } if (customDriver == null) { Class.forName(driver); } int oldLoginTimeout = DriverManager.getLoginTimeout(); DriverManager.setLoginTimeout(30); if (customDriver != null) { connection = customDriver.connect(url, username, password); } else { connection = DriverManager.getConnection(url, username, password); } DriverManager.setLoginTimeout(oldLoginTimeout); DatabaseMetaData dbMetaData = connection.getMetaData(); // the sorted set to hold the table information SortedSet<Table> tableInfoList = new TreeSet<Table>(); // Use a schema if the user name matches one of the schemas. // Fix for Oracle: MIRTH-1045 ResultSet schemasResult = null; try { schemasResult = dbMetaData.getSchemas(); while (schemasResult.next()) { String schemaResult = schemasResult.getString(1); if (username.equalsIgnoreCase(schemaResult)) { schema = schemaResult; } } } finally { if (schemasResult != null) { schemasResult.close(); } } // based on the table name pattern, attempt to retrieve the table information tableNamePatterns = translateTableNamePatterns(tableNamePatterns); List<String> tableNameList = new ArrayList<String>(); // go through each possible table name patterns and query for the tables for (String tableNamePattern : tableNamePatterns) { ResultSet rs = null; try { rs = dbMetaData.getTables(null, schema, tableNamePattern, TABLE_TYPES); // based on the result set, loop through to store the table name so it can be used to // retrieve the table's column information while (rs.next()) { tableNameList.add(rs.getString("TABLE_NAME")); } } finally { if (rs != null) { rs.close(); } } } // for each table, grab their column information for (String tableName : tableNameList) { ResultSet rs = null; ResultSet backupRs = null; boolean fallback = false; try { // apparently it's much more efficient to use ResultSetMetaData to retrieve // column information. So each driver is defined with their own unique SELECT // statement to query the table columns and use ResultSetMetaData to retrieve // the column information. If driver is not defined with the select statement // then we'll define to the generic method of getting column information, but // this could be extremely slow List<Column> columnList = new ArrayList<Column>(); if (StringUtils.isEmpty(selectLimit)) { logger.debug("No select limit is defined, using generic method"); rs = dbMetaData.getColumns(null, null, tableName, null); // retrieve all relevant column information for (int i = 0; rs.next(); i++) { Column column = new Column(rs.getString("COLUMN_NAME"), rs.getString("TYPE_NAME"), rs.getInt("COLUMN_SIZE")); columnList.add(column); } } else { logger.debug( "Select limit is defined, using specific select query : '" + selectLimit + "'"); // replace the '?' with the appropriate schema.table name, and use ResultSetMetaData to // retrieve column information final String schemaTableName = StringUtils.isNotEmpty(schema) ? "\"" + schema + "\".\"" + tableName + "\"" : "\"" + tableName + "\""; final String queryString = selectLimit.trim().replaceAll("\\?", Matcher.quoteReplacement(schemaTableName)); Statement statement = connection.createStatement(); try { rs = statement.executeQuery(queryString); ResultSetMetaData rsmd = rs.getMetaData(); // retrieve all relevant column information for (int i = 1; i < rsmd.getColumnCount() + 1; i++) { Column column = new Column(rsmd.getColumnName(i), rsmd.getColumnTypeName(i), rsmd.getPrecision(i)); columnList.add(column); } } catch (SQLException sqle) { logger.info("Failed to execute '" + queryString + "', fall back to generic approach to retrieve column information"); fallback = true; } finally { if (statement != null) { statement.close(); } } // failed to use selectLimit method, so we need to fall back to generic // if this generic approach fails, then there's nothing we can do if (fallback) { // Re-initialize in case some columns were added before failing columnList = new ArrayList<Column>(); logger.debug("Using fallback method for retrieving columns"); backupRs = dbMetaData.getColumns(null, null, tableName.replace("/", "//"), null); // retrieve all relevant column information while (backupRs.next()) { Column column = new Column(backupRs.getString("COLUMN_NAME"), backupRs.getString("TYPE_NAME"), backupRs.getInt("COLUMN_SIZE")); columnList.add(column); } } } // create table object and add to the list of table definitions Table table = new Table(tableName, columnList); tableInfoList.add(table); } finally { if (rs != null) { rs.close(); } if (backupRs != null) { backupRs.close(); } } } return tableInfoList; } catch (Exception e) { throw new MirthApiException(new Exception("Could not retrieve database tables and columns.", e)); } finally { if (connection != null) { try { connection.close(); } catch (SQLException e) { } } } }
From source file:org.talend.metadata.managment.model.DBConnectionFillerImpl.java
@Override public List<TdColumn> fillColumns(ColumnSet colSet, DatabaseMetaData dbJDBCMetadata, List<String> columnFilter, String columnPattern) {//from w w w. j a va 2s . c o m if (colSet == null || dbJDBCMetadata == null) { return null; } List<TdColumn> returnColumns = new ArrayList<TdColumn>(); Map<String, TdColumn> columnMap = new HashMap<String, TdColumn>(); String typeName = null; ExtractMetaDataUtils extractMeta = ExtractMetaDataUtils.getInstance(); try { String catalogName = getName(CatalogHelper.getParentCatalog(colSet)); Schema schema = SchemaHelper.getParentSchema(colSet); if (catalogName == null && schema != null) { catalogName = getName(CatalogHelper.getParentCatalog(schema)); } String schemaPattern = getName(schema); schemaPattern = " ".equals(schemaPattern) ? null : schemaPattern; //$NON-NLS-1$ String tablePattern = getName(colSet); // --- add columns to table boolean isOracle = MetadataConnectionUtils.isOracle(dbJDBCMetadata); if (isOracle && tablePattern.contains("/")) {//$NON-NLS-1$ tablePattern = tablePattern.replaceAll("/", "//");//$NON-NLS-1$ //$NON-NLS-2$ } ResultSet columns = dbJDBCMetadata.getColumns(catalogName, schemaPattern, tablePattern, columnPattern); // MOD qiongli 2012-8-15 TDQ-5898,Odbc Terdata don't support some API. boolean isOdbcTeradata = ConnectionUtils.isOdbcTeradata(dbJDBCMetadata); // get the MappingTypeRetriever according to the DbmsId of the DatabaseConnection MappingTypeRetriever mappingTypeRetriever = null; DatabaseConnection dbConnection = (DatabaseConnection) ConnectionHelper.getConnection(colSet); String dbmsId = JavaSqlFactory.getDbmsId(dbConnection); if (StringUtils.isBlank(dbmsId)) { log.error(Messages.getString("DBConnectionFillerImpl.dbmsIdIsBlank")); //$NON-NLS-1$ } else { mappingTypeRetriever = MetadataTalendType.getMappingTypeRetriever(dbmsId); } if (mappingTypeRetriever == null) { EDatabaseTypeName dbType = EDatabaseTypeName.getTypeFromDbType(dbConnection.getDatabaseType(), false); if (dbType != null) { mappingTypeRetriever = MetadataTalendType.getMappingTypeRetrieverByProduct(dbType.getProduct()); } } while (columns.next()) { int decimalDigits = 0; int numPrecRadix = 0; String columnName = getStringFromResultSet(columns, GetColumn.COLUMN_NAME.name()); TdColumn column = ColumnHelper.createTdColumn(columnName); int dataType = 0; try { // MOD scorreia 2010-07-24 removed the call to column.getSQLDataType() here because obviously the // sql // data type it is null and results in a NPE typeName = getStringFromResultSet(columns, GetColumn.TYPE_NAME.name()); typeName = typeName.toUpperCase().trim(); typeName = ManagementTextUtils.filterSpecialChar(typeName); if (typeName.startsWith("TIMESTAMP(") && typeName.endsWith(")")) { //$NON-NLS-1$ //$NON-NLS-2$ typeName = "TIMESTAMP"; //$NON-NLS-1$ } typeName = MetadataToolHelper.validateValueForDBType(typeName); if (dbJDBCMetadata instanceof DB2ForZosDataBaseMetadata) { // MOD klliu bug TDQ-1164 2011-09-26 dataType = Java2SqlType.getJavaTypeBySqlType(typeName); decimalDigits = getIntFromResultSet(columns, GetColumn.DECIMAL_DIGITS.name()); // ~ } else if (dbJDBCMetadata instanceof TeradataDataBaseMetadata) { // dataType = columns.getInt(GetColumn.TYPE_NAME.name()); dataType = Java2SqlType.getTeradataJavaTypeBySqlTypeAsInt(typeName); typeName = Java2SqlType.getTeradataJavaTypeBySqlTypeAsString(typeName); } else { dataType = getIntFromResultSet(columns, GetColumn.DATA_TYPE.name()); if (!isOdbcTeradata) { numPrecRadix = getIntFromResultSet(columns, GetColumn.NUM_PREC_RADIX.name()); decimalDigits = getIntFromResultSet(columns, GetColumn.DECIMAL_DIGITS.name()); } } if (MetadataConnectionUtils.isMssql(dbJDBCMetadata)) { if (typeName.toLowerCase().equals("date")) { //$NON-NLS-1$ dataType = 91; // MOD scorreia 2010-07-24 removed the call to column.getSQLDataType() here because // obviously // the sql // data type it is null and results in a NPE } else if (typeName.toLowerCase().equals("time")) { //$NON-NLS-1$ dataType = 92; // MOD scorreia 2010-07-24 removed the call to column.getSQLDataType() here because // obviously // the sql // data type it is null and results in a NPE } } if (!isOdbcTeradata) { int column_size = getIntFromResultSet(columns, GetColumn.COLUMN_SIZE.name()); column.setLength(column_size); } } catch (Exception e1) { log.warn(e1, e1); } // SqlDataType TdSqlDataType sqlDataType = MetadataConnectionUtils.createDataType(dataType, typeName, decimalDigits, numPrecRadix); column.setSqlDataType(sqlDataType); // Null able int nullable = 0; if (dbJDBCMetadata instanceof DB2ForZosDataBaseMetadata || dbJDBCMetadata instanceof TeradataDataBaseMetadata || dbJDBCMetadata instanceof EmbeddedHiveDataBaseMetadata) { String isNullable = getStringFromResultSet(columns, "IS_NULLABLE");//$NON-NLS-1$ if (!isNullable.equals("Y")) { //$NON-NLS-1$ nullable = 1; } } else { nullable = getIntFromResultSet(columns, GetColumn.NULLABLE.name()); } column.getSqlDataType().setNullable(NullableType.get(nullable)); // Comment // MOD msjian TDQ-8546: fix the oracle type database column's comment is wrong String colComment = getColumnComment(dbJDBCMetadata, columns, tablePattern, column.getName(), schemaPattern); ColumnHelper.setComment(colComment, column); // TdExpression Object defaultvalue = null; try { if (!isOdbcTeradata) { defaultvalue = columns.getObject(GetColumn.COLUMN_DEF.name()); } } catch (Exception e1) { log.warn(e1, e1); } String defaultStr = (defaultvalue != null) ? String.valueOf(defaultvalue) : null; TdExpression defExpression = createTdExpression(GetColumn.COLUMN_DEF.name(), defaultStr); column.setInitialValue(defExpression); extractMeta.handleDefaultValue(column, dbJDBCMetadata); if (mappingTypeRetriever != null) { String talendType = mappingTypeRetriever.getDefaultSelectedTalendType(typeName, extractMeta.getIntMetaDataInfo(columns, "COLUMN_SIZE"), //$NON-NLS-1$ (dbJDBCMetadata instanceof TeradataDataBaseMetadata) ? 0 : extractMeta.getIntMetaDataInfo(columns, "DECIMAL_DIGITS")); column.setTalendType(talendType); String defaultSelectedDbType = mappingTypeRetriever.getDefaultSelectedDbType(talendType); column.setSourceType(defaultSelectedDbType); } try { column.setNullable(nullable == 1); } catch (Exception e) { // do nothing } returnColumns.add(column); columnMap.put(columnName, column); } columns.close(); if (isLinked()) { ColumnSetHelper.addColumns(colSet, returnColumns); } fillPkandFk(colSet, columnMap, dbJDBCMetadata, catalogName, schemaPattern, tablePattern); } catch (Exception e) { log.error(e, e); } // ~ return returnColumns; }