List of usage examples for java.sql ResultSetMetaData getPrecision
int getPrecision(int column) throws SQLException;
From source file:com.healthmarketscience.jackcess.ImportUtil.java
/** * Copy an existing JDBC ResultSet into a new (or optionally existing) table * in this database./*from w w w .j av a2 s. c om*/ * * @param name Name of the new table to create * @param source ResultSet to copy from * @param filter valid import filter * @param useExistingTable if {@code true} use current table if it already * exists, otherwise, create new table with unique * name * * @return the name of the imported table * * @see Builder */ public static String importResultSet(ResultSet source, Database db, String name, ImportFilter filter, boolean useExistingTable) throws SQLException, IOException { ResultSetMetaData md = source.getMetaData(); name = Database.escapeIdentifier(name); Table table = null; if (!useExistingTable || ((table = db.getTable(name)) == null)) { List<Column> columns = new LinkedList<Column>(); for (int i = 1; i <= md.getColumnCount(); i++) { Column column = new Column(); column.setName(Database.escapeIdentifier(md.getColumnName(i))); int lengthInUnits = md.getColumnDisplaySize(i); column.setSQLType(md.getColumnType(i), lengthInUnits); DataType type = column.getType(); // we check for isTrueVariableLength here to avoid setting the length // for a NUMERIC column, which pretends to be var-len, even though it // isn't if (type.isTrueVariableLength() && !type.isLongValue()) { column.setLengthInUnits((short) lengthInUnits); } if (type.getHasScalePrecision()) { int scale = md.getScale(i); int precision = md.getPrecision(i); if (type.isValidScale(scale)) { column.setScale((byte) scale); } if (type.isValidPrecision(precision)) { column.setPrecision((byte) precision); } } columns.add(column); } table = createUniqueTable(db, name, columns, md, filter); } List<Object[]> rows = new ArrayList<Object[]>(COPY_TABLE_BATCH_SIZE); int numColumns = md.getColumnCount(); while (source.next()) { Object[] row = new Object[numColumns]; for (int i = 0; i < row.length; i++) { row[i] = source.getObject(i + 1); } row = filter.filterRow(row); if (row == null) { continue; } rows.add(row); if (rows.size() == COPY_TABLE_BATCH_SIZE) { table.addRows(rows); rows.clear(); } } if (rows.size() > 0) { table.addRows(rows); } return table.getName(); }
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;/*w w w. j av a 2s . c o 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:ro.nextreports.engine.util.QueryUtil.java
public List<NameType> executeQueryForColumnNames(String sql) throws Exception { // long t = System.currentTimeMillis(); StringWriter sw = new StringWriter(100); // sw.append("SELECT * FROM ("); sw.append(sql);//from w ww.ja v a2 s . c om // sw.append(") A WHERE 1 = -1"); String sqlForHeader = sw.toString(); LOG.info("call for header columns = " + sqlForHeader); ResultSet rs = null; Statement stmt = null; try { if (isProcedureCall(sqlForHeader)) { Dialect dialect = DialectUtil.getDialect(con); CallableStatement cs = con.prepareCall("{" + sqlForHeader + "}"); stmt = cs; if (dialect.hasProcedureWithCursor()) { cs.registerOutParameter(1, dialect.getCursorSqlType()); } rs = cs.executeQuery(); if (dialect.hasProcedureWithCursor()) { rs = (ResultSet) (cs.getObject(1)); } } else { stmt = con.createStatement(); stmt.setMaxRows(1); rs = stmt.executeQuery(sqlForHeader); } ResultSetMetaData rsmd = rs.getMetaData(); int columnCount = rsmd.getColumnCount(); List<NameType> columnNames = new ArrayList<NameType>(); for (int i = 0; i < columnCount; i++) { columnNames.add(new NameType(rsmd.getColumnLabel(i + 1), dialect.getJavaType( rsmd.getColumnTypeName(i + 1), rsmd.getPrecision(i + 1), rsmd.getScale(i + 1)))); // rsmd.getColumnClassName(i + 1))); } // t = System.currentTimeMillis() - t; // System.out.println("execute query for column names in " + t + // "ms"); return columnNames; } finally { ConnectionUtil.closeResultSet(rs); ConnectionUtil.closeStatement(stmt); } }
From source file:org.apache.sqoop.connector.jdbc.oracle.util.OracleQueries.java
public static List<Column> getColDataTypes(Connection connection, OracleTable table, List<String> colNames) throws SQLException { List<Column> result = new ArrayList<Column>(); StringBuilder sb = new StringBuilder(); sb.append("SELECT "); for (int idx = 0; idx < colNames.size(); idx++) { if (idx > 0) { sb.append(","); }/* w ww . j a va 2s . co m*/ sb.append(colNames.get(idx)); } sb.append(String.format(" FROM %s WHERE 0=1", table.toString())); String sql = sb.toString(); PreparedStatement statement = connection.prepareStatement(sql); try { ResultSetMetaData metadata = statement.getMetaData(); int numCols = metadata.getColumnCount(); for (int i = 1; i < numCols + 1; i++) { String colName = metadata.getColumnName(i); Column oracleColumn = OracleSqlTypesUtils.sqlTypeToSchemaType(metadata.getColumnType(i), colName, metadata.getPrecision(i), metadata.getScale(i)); result.add(oracleColumn); } } finally { statement.close(); } return result; }
From source file:org.apache.sqoop.connector.hbase.HbaseToInitializer.java
@Override public Schema getSchema(InitializerContext context, LinkConfiguration linkConfig, ToJobConfiguration toJobConfig) { executor = new HbaseExecutor(linkConfig.linkConfig); String schemaName = toJobConfig.toJobConfig.tableName; if (schemaName == null) { throw new SqoopException(GenericJdbcConnectorError.GENERIC_JDBC_CONNECTOR_0019, "Table name extraction not supported yet."); }/*from w w w. j a v a 2 s. c o m*/ if (toJobConfig.toJobConfig.schemaName != null) { schemaName = toJobConfig.toJobConfig.schemaName + "." + schemaName; } Schema schema = new Schema(schemaName); ResultSet rs = null; ResultSetMetaData rsmt = null; try { rs = executor.executeQuery("SELECT * FROM " + schemaName + " WHERE 1 = 0"); rsmt = rs.getMetaData(); for (int i = 1; i <= rsmt.getColumnCount(); i++) { String columnName = rsmt.getColumnName(i); if (StringUtils.isEmpty(columnName)) { columnName = rsmt.getColumnLabel(i); if (StringUtils.isEmpty(columnName)) { columnName = "Column " + i; } } Column column = SqlTypesUtils.sqlTypeToSchemaType(rsmt.getColumnType(i), columnName, rsmt.getPrecision(i), rsmt.getScale(i)); schema.addColumn(column); } return schema; } catch (SQLException e) { throw new SqoopException(GenericJdbcConnectorError.GENERIC_JDBC_CONNECTOR_0016, e); } finally { if (rs != null) { try { rs.close(); } catch (SQLException e) { LOG.info("Ignoring exception while closing ResultSet", e); } } } }
From source file:org.apache.kylin.query.adhoc.PushDownRunnerJdbcImpl.java
@Override public void executeQuery(String query, List<List<String>> results, List<SelectedColumnMeta> columnMetas) throws Exception { Statement statement = null;//from w ww . ja va2s .co m Connection connection = this.getConnection(); ResultSet resultSet = null; //extract column metadata ResultSetMetaData metaData = null; int columnCount = 0; try { statement = connection.createStatement(); resultSet = statement.executeQuery(query); extractResults(resultSet, results); metaData = resultSet.getMetaData(); columnCount = metaData.getColumnCount(); // fill in selected column meta for (int i = 1; i <= columnCount; ++i) { columnMetas.add(new SelectedColumnMeta(metaData.isAutoIncrement(i), metaData.isCaseSensitive(i), false, metaData.isCurrency(i), metaData.isNullable(i), false, metaData.getColumnDisplaySize(i), metaData.getColumnLabel(i), metaData.getColumnName(i), null, null, null, metaData.getPrecision(i), metaData.getScale(i), metaData.getColumnType(i), metaData.getColumnTypeName(i), metaData.isReadOnly(i), false, false)); } } catch (SQLException sqlException) { throw sqlException; } finally { DBUtils.closeQuietly(resultSet); DBUtils.closeQuietly(statement); closeConnection(connection); } }
From source file:edu.ku.brc.specify.toycode.mexconabio.MexConvToSQLNew.java
public void convert(final String tableName, final String fileName) { String str = ""; int fldLen = 0; int inx = 0;//from w w w.j av a2s .co m Connection conn = null; Statement stmt = null; try { conn = DriverManager.getConnection( "jdbc:mysql://localhost/mex?characterEncoding=UTF-8&autoReconnect=true", "root", "root"); stmt = conn.createStatement(); int[] fieldLengths = null; BasicSQLUtils.deleteAllRecordsFromTable(conn, tableName, SERVERTYPE.MySQL); Vector<Integer> types = new Vector<Integer>(); Vector<String> names = new Vector<String>(); String selectStr = null; String prepareStr = null; try { prepareStr = FileUtils.readFileToString(new File("prepare_stmt.txt")); selectStr = FileUtils.readFileToString(new File("select.txt")); } catch (IOException e) { e.printStackTrace(); } int idInx = selectStr.indexOf("ID,"); if (idInx == 0) { selectStr = selectStr.substring(3); } File file = new File("/Users/rods/Documents/" + fileName); SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy"); //SimpleDateFormat stf = new SimpleDateFormat("k:mm:ss"); int rowCnt = 0; try { System.out.println(prepareStr); PreparedStatement pStmt = conn.prepareStatement(prepareStr); BufferedReader in = new BufferedReader(new InputStreamReader(new FileInputStream(file), "UTF8")); str = in.readLine(); String[] fieldNames = StringUtils.split(str, ","); //String[] fieldNamesDB = StringUtils.split(selectStr, ","); String sql = "SELECT " + selectStr + " FROM " + tableName; System.out.println(sql); ResultSet rs = stmt.executeQuery(sql); ResultSetMetaData rsmd = rs.getMetaData(); fieldLengths = new int[rsmd.getColumnCount()]; for (int i = 1; i <= rsmd.getColumnCount(); i++) { fieldLengths[i - 1] = rsmd.getPrecision(i); types.add(rsmd.getColumnType(i)); names.add(rsmd.getColumnName(i)); System.out.println((i > 1 ? fieldNames[i - 2] : "ID") + " / " + rsmd.getColumnName(i) + " - " + rsmd.getPrecision(i)); } int numCols = rsmd.getColumnCount(); rs.close(); System.out.println("Number of Fields: " + numCols); str = in.readLine(); while (str != null) { //System.err.println(str); str = StringUtils.replace(str.substring(1, str.length() - 1), "\",\"", "|"); Vector<String> fields = split(str); if (fields.size() != numCols) { System.out.println("numCols: " + numCols + " != " + fields.size() + "fields.size()"); continue; } int col = 1; inx = 0; for (String fld : fields) { String field = fld.trim(); //if (field.length() > 1) //{ // field = field.substring(1, field.length()-1); //} //if (inx > 204) break; fldLen = field.length(); pStmt.setObject(col, null); switch (types.get(inx)) { case java.sql.Types.LONGVARCHAR: case java.sql.Types.VARCHAR: case java.sql.Types.LONGNVARCHAR: { if (field.length() > 0) { if (field.length() <= fieldLengths[inx]) { pStmt.setString(col, field); } else { System.err.println(String.format("The data for `%s` (%d) is too big %d f[%s]", names.get(inx), fieldLengths[inx], field.length(), field)); pStmt.setString(col, null); } } else { pStmt.setString(col, null); } } break; case java.sql.Types.DOUBLE: case java.sql.Types.FLOAT: { if (StringUtils.isNotEmpty(field)) { if (StringUtils.isNumeric(field)) { pStmt.setDouble(col, field.length() > 0 ? Double.parseDouble(field) : null); } else { System.err.println(col + " Bad Number[" + field + "] "); pStmt.setDate(col, null); } } else { pStmt.setDate(col, null); } } break; case java.sql.Types.INTEGER: { if (StringUtils.isNotEmpty(field)) { if (StringUtils.isNumeric(field)) { pStmt.setInt(col, field.length() > 0 ? Integer.parseInt(field) : null); } else { System.err.println(col + " Bad Number[" + field + "] "); pStmt.setDate(col, null); } } else { pStmt.setDate(col, null); } } break; case java.sql.Types.TIME: { Time time = null; try { time = Time.valueOf(field); } catch (Exception ex) { } pStmt.setTime(col, time); } break; case java.sql.Types.DATE: { try { if (StringUtils.isNotEmpty(field)) { if (StringUtils.contains(field, "/")) { field = StringUtils.replace(field, "/", "-"); } else if (StringUtils.contains(field, " ")) { field = StringUtils.replace(field, " ", "-"); } pStmt.setDate(col, field.length() > 0 ? new java.sql.Date(sdf.parse(field).getTime()) : null); } else { pStmt.setDate(col, null); } } catch (Exception ex) { System.err.println(col + " Bad Date[" + field + "]\n" + str); pStmt.setDate(col, null); } } break; default: { System.err.println("Error - " + types.get(inx)); } } inx++; col++; } pStmt.execute(); str = in.readLine(); rowCnt++; } in.close(); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } catch (Exception e) { System.err.println("Row: " + rowCnt); System.err.println(str); System.err.println(inx + " " + fieldLengths[inx] + " - Field Len: " + fldLen); e.printStackTrace(); } /*BufferedInputStream bis = new BufferedInputStream(new FileInputStream(file)); while (bis.available() > 0) { int bytesRead = bis.read(bytes); if (bytesRead > 0) { System.arraycopy(bytes, bytesRead, buffer, bufEndInx, bytesRead); bufEndInx += bytesRead; int inx = 0; while (inx < bufEndInx) { if (buffer[inx] != '\n') { String line = } inx++; } } }*/ } catch (Exception ex) { ex.printStackTrace(); } finally { try { stmt.close(); conn.close(); } catch (Exception ex) { ex.printStackTrace(); } } }
From source file:com.xpfriend.fixture.cast.temp.Database.java
private DynaClass getDynaClass(ResultSet resultSet) throws SQLException { ResultSetMetaData md = resultSet.getMetaData(); int count = md.getColumnCount(); DynaProperty[] properties = new DynaProperty[count]; for (int i = 0; i < properties.length; i++) { int column = i + 1; Class<?> type = TypeConverter.getJavaType(md.getColumnType(column), md.getColumnTypeName(column), md.getPrecision(column), md.getScale(column)); String name = getColumnLabel(md, column); properties[i] = new DynaProperty(name, type); }//from ww w .j a v a 2s . c o m return new BasicDynaClass(null, null, properties); }
From source file:at.ac.univie.isc.asio.engine.sql.WebRowSetWriter.java
private void columnDefinition(final int idx, final ResultSetMetaData context) throws XMLStreamException, SQLException { // @formatter:off xml.writeStartElement(WRS, "column-definition"); tag("column-index", idx); tag("auto-increment", context.isAutoIncrement(idx)); tag("case-sensitive", context.isCaseSensitive(idx)); tag("currency", context.isCurrency(idx)); tag("nullable", context.isNullable(idx)); tag("signed", context.isSigned(idx)); tag("searchable", context.isSearchable(idx)); tag("column-display-size", context.getColumnDisplaySize(idx)); tag("column-label", context.getColumnLabel(idx)); tag("column-name", context.getColumnName(idx)); tag("schema-name", context.getSchemaName(idx)); tag("column-precision", context.getPrecision(idx)); tag("column-scale", context.getScale(idx)); tag("table-name", context.getTableName(idx)); tag("catalog-name", context.getCatalogName(idx)); tag("column-type", context.getColumnType(idx)); tag("column-type-name", context.getColumnTypeName(idx)); xml.writeEndElement();/*from ww w . j a v a 2s . c o m*/ // @formatter:on }
From source file:edu.ku.brc.specify.conversion.MSULichensFixer.java
private void convertTaxonRecords() { IdMapperMgr.getInstance().setDBs(oldDBConn, newDBConn); txMapper = IdMapperMgr.getInstance().addTableMapper("taxonname", "TaxonNameID", false); txTypMapper = IdMapperMgr.getInstance().addTableMapper("TaxonomyType", "TaxonomyTypeID", false); txUnitTypMapper = IdMapperMgr.getInstance().addTableMapper("TaxonomicUnitType", "TaxonomicUnitTypeID", false);//from w w w .j a v a2 s . c om mappers = new IdMapperIFace[] { txMapper, txMapper, txTypMapper, txMapper, txUnitTypMapper }; newToOldColMap.put("TaxonID", "TaxonNameID"); newToOldColMap.put("ParentID", "ParentTaxonNameID"); newToOldColMap.put("TaxonTreeDefID", "TaxonomyTypeID"); newToOldColMap.put("TaxonTreeDefItemID", "TaxonomicUnitTypeID"); newToOldColMap.put("Name", "TaxonName"); newToOldColMap.put("FullName", "FullTaxonName"); newToOldColMap.put("IsAccepted", "Accepted"); oldToNewColMap.put("TaxonNameID", "TaxonID"); oldToNewColMap.put("ParentTaxonNameID", "ParentID"); oldToNewColMap.put("TaxonomyTypeID", "TaxonTreeDefID"); oldToNewColMap.put("TaxonomicUnitTypeID", "TaxonTreeDefItemID"); oldToNewColMap.put("TaxonName", "Name"); oldToNewColMap.put("FullTaxonName", "FullName"); oldToNewColMap.put("Accepted", "IsAccepted"); BasicSQLUtils.setDBConnection(newDBConn); StringBuilder newSB = new StringBuilder(); StringBuilder vl = new StringBuilder(); for (int i = 0; i < cols.length; i++) { fieldToColHash.put(cols[i], i + 1); colToFieldHash.put(i + 1, cols[i]); if (newSB.length() > 0) newSB.append(", "); newSB.append(cols[i]); if (vl.length() > 0) vl.append(','); vl.append('?'); } StringBuilder oldSB = new StringBuilder(); for (int i = 0; i < oldCols.length; i++) { oldFieldToColHash.put(oldCols[i], i + 1); if (oldSB.length() > 0) oldSB.append(", "); oldSB.append("ttx."); oldSB.append(oldCols[i]); } rankIdOldDBInx = oldFieldToColHash.get("RankID"); String sqlStr = String.format("SELECT %s FROM taxon ", newSB.toString()); log.debug(sqlStr); String fromClause = " FROM taxonname ttx LEFT JOIN msu_lichens.taxonname_TaxonNameID ON OldID = ttx.TaxonNameID LEFT JOIN msu_lichens_6.taxon AS ntx ON NewID = ntx.TaxonID WHERE ntx.TaxonID IS NULL"; String sql = String.format("SELECT %s %s", oldSB.toString(), fromClause); log.debug(sql); String cntSQL = String.format("SELECT COUNT(*) %s", fromClause); log.debug(cntSQL); int txCnt = BasicSQLUtils.getCountAsInt(oldDBConn, cntSQL); if (frame != null) { frame.setProcess(0, txCnt); } log.debug(txCnt); String pStr = String.format("INSERT INTO taxon (%s) VALUES (%s)", newSB.toString(), vl.toString()); log.debug(pStr); try { stmtTx = newDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet rs1 = stmtTx.executeQuery(sqlStr); ResultSetMetaData rsmd1 = rs1.getMetaData(); colTypes = new int[rsmd1.getColumnCount()]; colSizes = new int[rsmd1.getColumnCount()]; for (int i = 0; i < colTypes.length; i++) { colTypes[i] = rsmd1.getColumnType(i + 1); colSizes[i] = rsmd1.getPrecision(i + 1); } rs1.close(); stmtTx.close(); missingParentTaxonCount = 0; lastEditedByInx = oldFieldToColHash.get("LastEditedBy"); modifiedByAgentInx = fieldToColHash.get("ModifiedByAgentID"); stmtTx = oldDBConn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); pStmtTx = newDBConn.prepareStatement(pStr); int cnt = 0; ResultSet rs = stmtTx.executeQuery(sql); ResultSetMetaData rsmd = rs.getMetaData(); while (rs.next()) { processRow(rs, rsmd, null); cnt++; if (cnt % 1000 == 0) { log.debug(cnt); if (frame != null) { frame.setProcess(cnt); } } } rs.close(); if (frame != null) { frame.setProcess(txCnt, txCnt); } String msg = String.format("Stranded Taxon (no parent): %d", missingParentTaxonCount); tblWriter.log(msg); log.debug(msg); if (missingParentTaxonCount > 0) { if (frame != null) frame.setDesc("Renumbering the tree nodes, this may take a while..."); HashSet<Integer> ttdHash = new HashSet<Integer>(); for (CollectionInfo colInfo : CollectionInfo.getFilteredCollectionInfoList()) { if (!ttdHash.contains(colInfo.getTaxonTreeDef().getId())) { DataProviderSessionIFace session = null; try { session = DataProviderFactory.getInstance().createSession(); TaxonTreeDef taxonTreeDef = colInfo.getTaxonTreeDef(); taxonTreeDef = (TaxonTreeDef) session .getData("FROM TaxonTreeDef WHERE id = " + taxonTreeDef.getId()); sql = "SELECT TaxonID FROM taxon WHERE RankID = 0 AND TaxonTreeDefID = " + taxonTreeDef.getId(); log.debug(sql); Integer txRootId = BasicSQLUtils.getCount(sql); Taxon txRoot = (Taxon) session.getData("FROM Taxon WHERE id = " + txRootId); NodeNumberer<Taxon, TaxonTreeDef, TaxonTreeDefItem> nodeNumberer = new NodeNumberer<Taxon, TaxonTreeDef, TaxonTreeDefItem>( txRoot.getDefinition()); nodeNumberer.doInBackground(); } catch (Exception ex) { //session.rollback(); ex.printStackTrace(); } finally { if (session != null) { session.close(); } } ttdHash.add(colInfo.getTaxonTreeDef().getId()); } } if (frame != null) frame.setDesc("Renumbering done."); } missingParentTaxonCount = 0; } catch (SQLException ex) { ex.printStackTrace(); } finally { try { stmtTx.close(); pStmtTx.close(); } catch (Exception ex) { } } System.out.println("Done."); }