Best solution I've been able to come up with is this, which
does N selects where N is the path length from the start
to the furthest leaf. Can anyone suggest anything better?
/**
* Process all descendants of a specified row in a table
* with a recursive key relationship. A method "processRow"
* is called to process each row as it is found in some
* unspecified manner.
*
* @param connection
* the database connection to use.
* @param tableName
* the name of the recursive table.
* @param keyCol
* the name of the primary key column. This is assumed
* to be an integer.
* @param parentCol
* the name of the column identifying the parent row.
* This row should be a foreign key to "keyCol" in
* the same table.
* @param keyCol
* the name of the primary key column.
* @param start
* the number of the row to start the descent from.
*/
public void processDescendants (Connection connection,
String tableName,
String keyCol,
String parentCol,
int start)
throws SQLException {
String parentList = "" + start;
//
// Loop until there are no more results,
// then return from inside the loop
//
while (true) {
try (PreparedStatement stat = connection.prepareStatement(
"SELECT * FROM " + tableName +
" WHERE " + keyCol + " NOT IN (" + parentList + ")" +
" AND " + parentCol + " IN (" + parentList + ")"
)) {
try (ResultSet res = stat.execute()) {
if (res.next()) {
//
// Results found, so process them
//
do {
//
// Process the current row
//
processRow(res);
//
// Add the ID of the current row to
// the list of parent rows
//
parentList += "," + res.getInt(keyCol);
}
while (res.next());
}
else {
//
// No unprocessed rows found, so return
//
return;
}
}
}
}
}
--
John English
