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.talend.metadata.managment.model.DBConnectionFillerImpl.java
@Override public List<TdColumn> fillColumns(ColumnSet colSet, IMetadataConnection iMetadataConnection, DatabaseMetaData dbJDBCMetadata, List<String> columnFilter, String columnPattern) { if (colSet == null || dbJDBCMetadata == null) { return null; }// w w w. j a v a2s . c om List<TdColumn> returnColumns = new ArrayList<TdColumn>(); List<String> columnLabels = new ArrayList<String>(); 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 // TDI-28578 Metadata wizard doesn't display tables starting with '/' 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); if (MetadataConnectionUtils.isMysql(dbJDBCMetadata)) { boolean check = !Pattern.matches("^\\w+$", tablePattern);//$NON-NLS-1$ if (check && !columns.next()) { columns = dbJDBCMetadata.getColumns(catalogName, schemaPattern, TalendQuoteUtils.addQuotes(tablePattern, TalendQuoteUtils.ANTI_QUOTE), columnPattern); } columns.beforeFirst(); } int index = 0; while (columns.next()) { int decimalDigits = 0; int numPrecRadix = 0; String columnName = getStringFromResultSet(columns, GetColumn.COLUMN_NAME.name()); TdColumn column = ColumnHelper.createTdColumn(columnName); String label = column.getLabel(); label = ManagementTextUtils.filterSpecialChar(label); String label2 = label; ICoreService coreService = CoreRuntimePlugin.getInstance().getCoreService(); if (coreService != null && coreService.isKeyword(label)) { label = "_" + label; //$NON-NLS-1$ } label = MetadataToolHelper.validateColumnName(label, index, columnLabels); column.setLabel(label); column.setOriginalField(label2); int dataType = 0; if (!extractMeta.needFakeDatabaseMetaData(iMetadataConnection)) { dataType = getIntFromResultSet(columns, GetColumn.DATA_TYPE.name()); } // 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 (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 } } try { int column_size = getIntFromResultSet(columns, GetColumn.COLUMN_SIZE.name()); column.setLength(column_size); decimalDigits = getIntFromResultSet(columns, GetColumn.DECIMAL_DIGITS.name()); column.setPrecision(decimalDigits); // Teradata SQL Mode no need this column if (!MetadataConnectionUtils.isTeradataSQLMode(iMetadataConnection)) { numPrecRadix = getIntFromResultSet(columns, GetColumn.NUM_PREC_RADIX.name()); } } catch (Exception e1) { log.warn(e1, e1); } // SqlDataType TdSqlDataType sqlDataType = MetadataConnectionUtils.createDataType(dataType, typeName, decimalDigits, numPrecRadix); column.setSqlDataType(sqlDataType); // Null able if (!extractMeta.needFakeDatabaseMetaData(iMetadataConnection)) { int nullable = getIntFromResultSet(columns, GetColumn.NULLABLE.name()); column.getSqlDataType().setNullable(NullableType.get(nullable)); } // Default value String defaultValue = getStringFromResultSet(columns, GetColumn.COLUMN_DEF.name()); // Comment String colComment = getColumnComment(dbJDBCMetadata, columns, tablePattern, column.getName(), schemaPattern); colComment = ManagementTextUtils.filterSpecialChar(colComment); column.setComment(colComment); ColumnHelper.setComment(colComment, column); // TdExpression Object defaultValueObject = null; try { defaultValueObject = columns.getObject(GetColumn.COLUMN_DEF.name()); } catch (Exception e1) { log.warn(e1, e1); } String defaultStr = (defaultValueObject != null) ? String.valueOf(defaultValueObject) : defaultValue; defaultStr = ManagementTextUtils.filterSpecialChar(defaultStr); TdExpression defExpression = createTdExpression(GetColumn.COLUMN_DEF.name(), defaultStr); column.setInitialValue(defExpression); DatabaseConnection dbConnection = (DatabaseConnection) ConnectionHelper.getConnection(colSet); String dbmsId = dbConnection == null ? null : dbConnection.getDbmsId(); if (dbmsId != null) { MappingTypeRetriever mappingTypeRetriever = MetadataTalendType.getMappingTypeRetriever(dbmsId); if (mappingTypeRetriever == null) { @SuppressWarnings("null") EDatabaseTypeName dbType = EDatabaseTypeName .getTypeFromDbType(dbConnection.getDatabaseType(), false); if (dbType != null) { mappingTypeRetriever = MetadataTalendType .getMappingTypeRetrieverByProduct(dbType.getProduct()); } } if (mappingTypeRetriever != null) { String talendType = mappingTypeRetriever.getDefaultSelectedTalendType(typeName, extractMeta.getIntMetaDataInfo(columns, "COLUMN_SIZE"), //$NON-NLS-1$ ExtractMetaDataUtils.getInstance().getIntMetaDataInfo(columns, "DECIMAL_DIGITS")); column.setTalendType(talendType); String defaultSelectedDbType = mappingTypeRetriever.getDefaultSelectedDbType(talendType); column.setSourceType(defaultSelectedDbType); } } try { column.setNullable("YES".equals(getStringFromResultSet(columns, GetColumn.IS_NULLABLE.name()))); //$NON-NLS-1$ } catch (Exception e) { // do nothing } extractMeta.handleDefaultValue(column, dbJDBCMetadata); returnColumns.add(column); columnLabels.add(column.getLabel()); columnMap.put(columnName, column); index++; } columns.close(); if (isLinked()) { ColumnSetHelper.addColumns(colSet, returnColumns); } fillPkandFk(colSet, columnMap, dbJDBCMetadata, catalogName, schemaPattern, tablePattern); } catch (Exception e) { log.error(e, e); } // ~ return returnColumns; }
From source file:com.mirth.connect.connectors.jdbc.JdbcConnectorService.java
public Object invoke(String method, Object object, String sessionsId) throws Exception { if (method.equals("getInformationSchema")) { // method 'getInformationSchema' will return Set<Table> Connection connection = null; try {/*from w ww . j a va 2 s . co m*/ Properties properties = (Properties) object; String driver = properties.getProperty(DatabaseReaderProperties.DATABASE_DRIVER); String address = properties.getProperty(DatabaseReaderProperties.DATABASE_URL); String user = properties.getProperty(DatabaseReaderProperties.DATABASE_USERNAME); String password = properties.getProperty(DatabaseReaderProperties.DATABASE_PASSWORD); // Although these properties are not persisted, they used by the JdbcConnectorService String tableNamePatternExp = properties .getProperty(DatabaseReaderProperties.DATABASE_TABLE_NAME_PATTERN_EXPRESSION); String selectLimit = properties.getProperty(DatabaseReaderProperties.DATABASE_SELECT_LIMIT); String schema = null; Class.forName(driver); int oldLoginTimeout = DriverManager.getLoginTimeout(); DriverManager.setLoginTimeout(30); connection = DriverManager.getConnection(address, user, 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 (user.equalsIgnoreCase(schemaResult)) { schema = schemaResult; } } } finally { if (schemasResult != null) { schemasResult.close(); } } // based on the table name pattern, attempt to retrieve the table information List<String> tablePatternList = translateTableNamePatternExpression(tableNamePatternExp); List<String> tableNameList = new ArrayList<String>(); // go through each possible table name patterns and query for the tables for (String tableNamePattern : tablePatternList) { 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("\\?", 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, null); // retrieve all relevant column information for (int i = 0; backupRs.next(); i++) { 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 Exception("Could not retrieve database tables and columns.", e); } finally { if (connection != null) { connection.close(); } } } return null; }
From source file:com.mirth.connect.connectors.jdbc.DatabaseConnectorService.java
public Object invoke(String channelId, String method, Object object, String sessionsId) throws Exception { if (method.equals("getInformationSchema")) { // method 'getInformationSchema' will return Set<Table> Connection connection = null; try {/*w ww .j ava 2 s . com*/ DatabaseConnectionInfo databaseConnectionInfo = (DatabaseConnectionInfo) object; String driver = databaseConnectionInfo.getDriver(); String address = replacer.replaceValues(databaseConnectionInfo.getUrl(), channelId); String user = replacer.replaceValues(databaseConnectionInfo.getUsername(), channelId); String password = replacer.replaceValues(databaseConnectionInfo.getPassword(), channelId); // Although these properties are not persisted, they used by the JdbcConnectorService String tableNamePatternExp = databaseConnectionInfo.getTableNamePatternExpression(); String selectLimit = databaseConnectionInfo.getSelectLimit(); String schema = null; Class.forName(driver); int oldLoginTimeout = DriverManager.getLoginTimeout(); DriverManager.setLoginTimeout(30); connection = DriverManager.getConnection(address, user, 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 (user.equalsIgnoreCase(schemaResult)) { schema = schemaResult; } } } finally { if (schemasResult != null) { schemasResult.close(); } } // based on the table name pattern, attempt to retrieve the table information List<String> tablePatternList = translateTableNamePatternExpression(tableNamePatternExp); List<String> tableNameList = new ArrayList<String>(); // go through each possible table name patterns and query for the tables for (String tableNamePattern : tablePatternList) { 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("\\?", 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, null); // retrieve all relevant column information for (int i = 0; backupRs.next(); i++) { 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 Exception("Could not retrieve database tables and columns.", e); } finally { if (connection != null) { connection.close(); } } } return null; }
From source file:org.nuxeo.ecm.core.storage.sql.Mapper.java
protected void createTables() throws SQLException { String schemaName = sqlInfo.dialect.getConnectionSchema(connection); DatabaseMetaData metadata = connection.getMetaData(); Set<String> tableNames = findTableNames(metadata, schemaName); Statement st = connection.createStatement(); for (Table table : sqlInfo.getDatabase().getTables()) { String tableName = getTableName(table.getName()); if (tableNames.contains(tableName) || tableNames.contains(tableName.toUpperCase())) { sqlInfo.dialect.existingTableDetected(connection, table, model, sqlInfo.database); } else {/*from w w w .j av a 2 s. co m*/ /* * Create missing table. */ boolean create = sqlInfo.dialect.preCreateTable(connection, table, model, sqlInfo.database); if (!create) { log.warn("Creation skipped for table: " + table.getName()); continue; } String sql = table.getCreateSql(); log(sql); st.execute(sql); for (String s : table.getPostCreateSqls()) { log(s); st.execute(s); } for (String s : sqlInfo.dialect.getPostCreateTableSqls(table, model, sqlInfo.database)) { log(s); st.execute(s); } } /* * 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. */ 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); log(sql); st.execute(sql); for (String s : table.getPostAddSqls(column)) { log(s); st.execute(s); } } 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()), ", ")); } } st.close(); }
From source file:org.ramadda.repository.database.DatabaseManager.java
/** * Actually write the tables/*from ww w. j a va2 s . co m*/ * * @param pw What to write to * @param packageName Tables.java package name * @param what _more_ * * @throws Exception on badness */ private void writeTables(PrintWriter pw, String packageName, String[] what) throws Exception { String sp1 = " "; String sp2 = sp1 + sp1; String sp3 = sp1 + sp1 + sp1; pw.append( "/**Generated by running: java org.unavco.projects.gsac.repository.UnavcoGsacDatabaseManager**/\n\n"); pw.append("package " + packageName + ";\n\n"); pw.append("import org.ramadda.sql.SqlUtil;\n\n"); pw.append("//J-\n"); pw.append("public abstract class Tables {\n"); pw.append(sp1 + "public abstract String getName();\n"); pw.append(sp1 + "public abstract String getColumns();\n"); Connection connection = getConnection(); DatabaseMetaData dbmd = connection.getMetaData(); ResultSet catalogs = dbmd.getCatalogs(); ResultSet tables = dbmd.getTables(null, null, null, what); HashSet seenTables = new HashSet(); while (tables.next()) { String tableName = tables.getString("TABLE_NAME"); // System.err.println ("NAME:" + tableName); String TABLENAME = tableName.toUpperCase(); if (seenTables.contains(TABLENAME)) { continue; } seenTables.add(TABLENAME); String tableType = tables.getString("TABLE_TYPE"); if (Misc.equals(tableType, "INDEX")) { continue; } if (tableName.indexOf("$") >= 0) { continue; } if (tableType == null) { continue; } if ((tableType != null) && tableType.startsWith("SYSTEM")) { continue; } ResultSet columns = dbmd.getColumns(null, null, tableName, null); List colNames = new ArrayList(); pw.append("\n\n"); pw.append(sp1 + "public static class " + TABLENAME + " extends Tables {\n"); pw.append(sp2 + "public static final String NAME = \"" + tableName.toLowerCase() + "\";\n"); pw.append("\n"); pw.append(sp2 + "public String getName() {return NAME;}\n"); pw.append(sp2 + "public String getColumns() {return COLUMNS;}\n"); System.out.println("processing table:" + TABLENAME); String tableVar = null; List colVars = new ArrayList(); HashSet seen = new HashSet(); while (columns.next()) { String colName = columns.getString("COLUMN_NAME").toLowerCase(); String colSize = columns.getString("COLUMN_SIZE"); String COLNAME = colName.toUpperCase(); if (seen.contains(COLNAME)) { continue; } seen.add(COLNAME); COLNAME = COLNAME.replace("#", ""); colNames.add("COL_" + COLNAME); pw.append(sp2 + "public static final String COL_" + COLNAME + " = NAME + \"." + colName + "\";\n"); pw.append(sp2 + "public static final String COL_NODOT_" + COLNAME + " = \"" + colName + "\";\n"); /* pw.append(sp2 + "public static final String ORA_" + COLNAME + " = \"" + colName + "\";\n"); */ } pw.append("\n"); pw.append(sp2 + "public static final String[] ARRAY = new String[] {\n"); pw.append(sp3 + StringUtil.join(",", colNames)); pw.append("\n"); pw.append(sp2 + "};\n"); pw.append(sp2 + "public static final String COLUMNS = SqlUtil.comma(ARRAY);\n"); pw.append(sp2 + "public static final String NODOT_COLUMNS = SqlUtil.commaNoDot(ARRAY);\n"); pw.append(sp2 + "public static final String INSERT =" + "SqlUtil.makeInsert(NAME, NODOT_COLUMNS," + "SqlUtil.getQuestionMarks(ARRAY.length));\n"); pw.append(sp1 + "public static final " + TABLENAME + " table = new " + TABLENAME + "();\n"); pw.append(sp1 + "}\n\n"); } pw.append("\n\n}\n"); }
From source file:org.nuxeo.ecm.core.storage.sql.jdbc.JDBCMapper.java
protected void createTables() throws SQLException { sqlInfo.executeSQLStatements(null, this); // for missing category sqlInfo.executeSQLStatements("first", this); sqlInfo.executeSQLStatements("beforeTableCreation", this); if (testProps.containsKey(TEST_UPGRADE)) { // create "old" tables sqlInfo.executeSQLStatements("testUpgrade", this); }// w ww.ja va 2s.co m String schemaName = 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 = null; try { st = connection.createStatement(); for (Table table : database.getTables()) { String tableName = getTableName(table.getPhysicalName()); if (tableNames.contains(tableName.toUpperCase())) { dialect.existingTableDetected(connection, table, model, sqlInfo.database); } else { /* * Create missing table. */ boolean create = 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); countExecute(); } catch (SQLException e) { try { closeStatement(st); } finally { throw new SQLException("Error creating table: " + sql + " : " + e.getMessage(), e); } } for (String s : table.getPostCreateSqls(model)) { logger.log(s); try { st.execute(s); countExecute(); } catch (SQLException e) { throw new SQLException("Error post creating table: " + s + " : " + e.getMessage(), e); } } for (String s : dialect.getPostCreateTableSqls(table, model, sqlInfo.database)) { logger.log(s); try { st.execute(s); countExecute(); } 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); countExecute(); } 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); countExecute(); } 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)); } } } for (String col : dialect.getIgnoredColumns(table)) { columnTypes.remove(col.toUpperCase()); } 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); } } } finally { if (st != null) { try { closeStatement(st); } catch (SQLException e) { log.error(e.getMessage(), e); } } } 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.executeSQLStatements("last", this); dialect.performAdditionalStatements(connection); }
From source file:org.ramadda.repository.database.DatabaseManager.java
/** * _more_//w w w. j a v a 2s . c o m * * @param os _more_ * @param all _more_ * * @throws Exception _more_ */ public void makeDatabaseCopyxxx(OutputStream os, boolean all) throws Exception { Connection connection = getConnection(); try { DatabaseMetaData dbmd = connection.getMetaData(); ResultSet catalogs = dbmd.getCatalogs(); ResultSet tables = dbmd.getTables(null, null, null, new String[] { "TABLE" }); ResultSetMetaData rsmd = tables.getMetaData(); for (int col = 1; col <= rsmd.getColumnCount(); col++) { System.err.println(rsmd.getColumnName(col)); } int totalRowCnt = 0; while (tables.next()) { // String tableName = tables.getString("Tables.NAME.NAME"); // String tableType = tables.getString("Tables.TYPE.NAME"); String tableName = tables.getString("TABLE_NAME"); String tableType = tables.getString("TABLE_TYPE"); if ((tableType == null) || Misc.equals(tableType, "INDEX") || tableType.startsWith("SYSTEM")) { continue; } String tn = tableName.toLowerCase(); if (!all) { if (tn.equals(Tables.GLOBALS.NAME) || tn.equals(Tables.USERS.NAME) || tn.equals(Tables.PERMISSIONS.NAME) || tn.equals(Tables.HARVESTERS.NAME) || tn.equals(Tables.USERROLES.NAME)) { continue; } } ResultSet cols = dbmd.getColumns(null, null, tableName, null); int colCnt = 0; String colNames = null; List types = new ArrayList(); while (cols.next()) { String colName = cols.getString("COLUMN_NAME"); if (colNames == null) { colNames = " ("; } else { colNames += ","; } colNames += colName; int type = cols.getInt("DATA_TYPE"); types.add(type); colCnt++; } colNames += ") "; Statement statement = execute("select * from " + tableName, 10000000, 0); SqlUtil.Iterator iter = getIterator(statement); ResultSet results; int rowCnt = 0; List valueList = new ArrayList(); boolean didDelete = false; while ((results = iter.getNext()) != null) { if (!didDelete) { didDelete = true; IOUtil.write(os, "delete from " + tableName.toLowerCase() + ";\n"); } totalRowCnt++; rowCnt++; StringBuffer value = new StringBuffer("("); for (int i = 1; i <= colCnt; i++) { int type = ((Integer) types.get(i - 1)).intValue(); if (i > 1) { value.append(","); } if (type == java.sql.Types.TIMESTAMP) { Timestamp ts = results.getTimestamp(i); // sb.append(SqlUtil.format(new Date(ts.getTime()))); if (ts == null) { value.append("null"); } else { value.append(HtmlUtils.squote(ts.toString())); } } else if (type == java.sql.Types.VARCHAR) { String s = results.getString(i); if (s != null) { //If the target isn't mysql: //s = s.replace("'", "''"); //If the target is mysql: s = s.replace("'", "\\'"); s = s.replace("\r", "\\r"); s = s.replace("\n", "\\n"); value.append("'" + s + "'"); } else { value.append("null"); } } else { String s = results.getString(i); value.append(s); } } value.append(")"); valueList.add(value.toString()); if (valueList.size() > 50) { IOUtil.write(os, "insert into " + tableName.toLowerCase() + colNames + " values "); IOUtil.write(os, StringUtil.join(",", valueList)); IOUtil.write(os, ";\n"); valueList = new ArrayList(); } } if (valueList.size() > 0) { if (!didDelete) { didDelete = true; IOUtil.write(os, "delete from " + tableName.toLowerCase() + ";\n"); } IOUtil.write(os, "insert into " + tableName.toLowerCase() + colNames + " values "); IOUtil.write(os, StringUtil.join(",", valueList)); IOUtil.write(os, ";\n"); } } } finally { closeConnection(connection); } }
From source file:org.kuali.kfs.sys.dataaccess.impl.FieldMetaDataImpl.java
public Object processMetaData(DatabaseMetaData databaseMetaData) throws SQLException, MetaDataAccessException { Class workingBusinessObjectClass = businessObjectClass; String workingPropertyName = propertyName; while (workingPropertyName.contains(".")) { try {//from w w w.ja va 2 s . c o m workingBusinessObjectClass = org.apache.ojb.broker.metadata.MetadataManager.getInstance() .getGlobalRepository().getDescriptorFor(workingBusinessObjectClass) .getObjectReferenceDescriptorByName( workingPropertyName.substring(0, workingPropertyName.indexOf("."))) .getItemClass(); } catch (Exception e1) { LOG.debug( new StringBuffer("Unable to get property type via reference descriptor for property ") .append(workingPropertyName.substring(0, workingPropertyName.indexOf("."))) .append(" of BusinessObject class ").append(workingBusinessObjectClass).toString(), e1); try { workingBusinessObjectClass = org.apache.ojb.broker.metadata.MetadataManager.getInstance() .getGlobalRepository().getDescriptorFor(workingBusinessObjectClass) .getCollectionDescriptorByName( workingPropertyName.substring(0, workingPropertyName.indexOf("."))) .getItemClass(); } catch (Exception e2) { LOG.debug(new StringBuffer("Unable to get property type via collection descriptor of property ") .append(workingPropertyName.substring(0, workingPropertyName.indexOf("."))) .append(" of BusinessObject class ").append(workingBusinessObjectClass).toString(), e2); BusinessObject businessObject = null; try { businessObject = (BusinessObject) workingBusinessObjectClass.newInstance(); } catch (Exception e3) { if (LOG.isDebugEnabled()) { LOG.debug("Unable to instantiate BusinessObject class " + workingBusinessObjectClass, e3); } return populateAndReturnNonPersistableInstance(); } try { workingBusinessObjectClass = PropertyUtils.getPropertyType(businessObject, workingPropertyName.substring(0, workingPropertyName.indexOf("."))); } catch (Exception e4) { LOG.debug(new StringBuffer("Unable to get type of property ") .append(workingPropertyName.substring(0, workingPropertyName.indexOf("."))) .append(" for BusinessObject class ").append(workingBusinessObjectClass).toString(), e4); return populateAndReturnNonPersistableInstance(); } } } if (workingBusinessObjectClass == null) { return populateAndReturnNonPersistableInstance(); } else { workingPropertyName = workingPropertyName.substring(workingPropertyName.indexOf(".") + 1); } } if (!PersistableBusinessObject.class.isAssignableFrom(workingBusinessObjectClass)) { return populateAndReturnNonPersistableInstance(); } ClassDescriptor classDescriptor = org.apache.ojb.broker.metadata.MetadataManager.getInstance() .getGlobalRepository().getDescriptorFor(workingBusinessObjectClass); if (classDescriptor == null) { return populateAndReturnNonPersistableInstance(); } tableName = classDescriptor.getFullTableName(); if (classDescriptor.getFieldDescriptorByName(workingPropertyName) == null) { return populateAndReturnNonPersistableInstance(); } columnName = classDescriptor.getFieldDescriptorByName(workingPropertyName).getColumnName(); ResultSet resultSet = databaseMetaData.getColumns(null, null, tableName, columnName); if (resultSet.next()) { dataType = resultSet.getString("TYPE_NAME"); length = resultSet.getInt("COLUMN_SIZE"); decimalPlaces = resultSet.getInt("DECIMAL_DIGITS"); encrypted = classDescriptor.getFieldDescriptorByName(workingPropertyName) .getFieldConversion() instanceof OjbKualiEncryptDecryptFieldConversion; } resultSet.close(); return this; }
From source file:edu.umass.cs.gigapaxos.SQLPaxosLogger.java
private void sanityCheckTables(String cmdC, String cmdM, String cmdPC) { Statement stmt = null;// w ww. ja va 2 s .c om ResultSet rset = null; Connection conn = null; try { conn = this.getDefaultConn(); stmt = conn.createStatement(); DatabaseMetaData meta = conn.getMetaData(); rset = meta.getColumns(null, null, null, null); if (!rset.next()) { log.severe(this + ": metadata query returned null; exiting"); System.exit(1); } while (rset.next()) { if (rset.getString("TABLE_NAME").equals(getCTable().toUpperCase()) && rset.getString("COLUMN_NAME").equals("STATE")) { log.info(this + " : " + rset.getString("TABLE_NAME") + " : " + rset.getString("COLUMN_NAME") + " : " + rset.getInt("COLUMN_SIZE") + (MAX_CHECKPOINT_SIZE > rset.getInt("COLUMN_SIZE") ? " -> " + MAX_CHECKPOINT_SIZE : "")); if (MAX_CHECKPOINT_SIZE > rset.getInt("COLUMN_SIZE")) { stmt.execute("alter table " + getCTable() + " alter column state set data type " + (getCheckpointBlobOption() ? SQL.getBlobString(maxCheckpointSize, SQL_TYPE) : " varchar(" + maxCheckpointSize + ")")); stmt.execute("alter table " + getPCTable() + " alter column state set data type " + (getCheckpointBlobOption() ? SQL.getBlobString(maxCheckpointSize, SQL_TYPE) : " varchar(" + maxCheckpointSize + ")")); } } if (rset.getString("TABLE_NAME").equals(getMTable().toUpperCase()) && rset.getString("COLUMN_NAME").equals("MESSAGE")) { log.info(this + " : " + rset.getString("TABLE_NAME") + " : " + rset.getString("COLUMN_NAME") + " : " + rset.getInt("COLUMN_SIZE") + (MAX_LOG_MESSAGE_SIZE > rset.getInt("COLUMN_SIZE") ? " -> " + MAX_LOG_MESSAGE_SIZE : "")); if (MAX_LOG_MESSAGE_SIZE > rset.getInt("COLUMN_SIZE")) stmt.execute("alter table " + getMTable() + " alter column message set data type " + (getLogMessageBlobOption() ? SQL.getBlobString(maxLogMessageSize, SQL_TYPE) : " varchar(" + maxLogMessageSize + ")")); } } } catch (Exception sqle) { log.severe("SQLException while sanity checking table schema"); sqle.printStackTrace(); System.exit(1); } finally { cleanup(stmt); cleanup(rset); cleanup(conn); } }
From source file:org.ramadda.repository.database.DatabaseManager.java
/** * _more_//from www .ja v a 2s.c o m * * @param connection _more_ * @param all _more_ * * @return _more_ * * @throws Exception _more_ */ public List<TableInfo> getTableInfos(Connection connection, boolean all) throws Exception { DatabaseMetaData dbmd = connection.getMetaData(); ResultSet tables = dbmd.getTables(null, null, null, new String[] { "TABLE" }); ResultSetMetaData rsmd = tables.getMetaData(); for (int col = 1; col <= rsmd.getColumnCount(); col++) { // System.err.println (rsmd.getColumnName(col)); } List<TableInfo> tableInfos = new ArrayList<TableInfo>(); HashSet<String> seen = new HashSet<String>(); while (tables.next()) { String tableName = tables.getString("TABLE_NAME"); String tn = tableName.toLowerCase(); if (tn.equals("participant")) { //a hack due to some old bad derby db I have continue; } //Just in case if (seen.contains(tn)) { System.err.println("Warning: duplicate table:" + tableName); continue; } seen.add(tn); boolean ok = true; for (TypeHandler typeHandler : getRepository().getTypeHandlers()) { if (!typeHandler.shouldExportTable(tn)) { ok = false; break; } } if (!ok) { continue; } String tableType = tables.getString("TABLE_TYPE"); if ((tableType == null) || tableType.startsWith("SYSTEM") || Misc.equals(tableType, "INDEX")) { continue; } ResultSet indices = dbmd.getIndexInfo(null, null, tableName, false, false); List<IndexInfo> indexList = new ArrayList<IndexInfo>(); while (indices.next()) { indexList.add(new IndexInfo(indices.getString("INDEX_NAME"), indices.getString("COLUMN_NAME"))); } ResultSet cols = dbmd.getColumns(null, null, tableName, null); rsmd = cols.getMetaData(); List<ColumnInfo> columns = new ArrayList<ColumnInfo>(); while (cols.next()) { String colName = cols.getString("COLUMN_NAME"); int type = cols.getInt("DATA_TYPE"); String typeName = cols.getString("TYPE_NAME"); int size = cols.getInt("COLUMN_SIZE"); if (type == -1) { if (typeName.toLowerCase().equals("mediumtext")) { type = java.sql.Types.CLOB; //Just come up with some size if (size <= 0) { size = 36000; } } else if (typeName.toLowerCase().equals("longtext")) { type = java.sql.Types.CLOB; //Just come up with some size if (size <= 0) { size = 36000; } } } if (typeName.equalsIgnoreCase("text")) { if (size <= 0) { size = 36000; } } columns.add(new ColumnInfo(colName, typeName, type, size)); if (tn.indexOf("wiki") >= 0) { System.err.println("COLS:" + columns); } } tableInfos.add(new TableInfo(tn, indexList, columns)); } return tableInfos; }