Hi John,
Well I would prefer a structure, that I could put into a
Nasi-Schneidermann-Diagramm.
So instead of using while (true) and fleeing out of it like hell with a break
or a return I would do something like this:
PreparedStatement stat;
try
{
stat = connection.prepareStatement(
"SELECT * FROM " + tableName +
" WHERE " + keyCol + " NOT IN (" + parentList + ")" +
" AND " + parentCol + " IN (" + parentList + ")"
while(res.next())
{
//
// Process the current row
//
processRow(res);
//
// Add the ID of the current row to
// the list of parent rows
//
parentList += "," + res.getInt(keyCol);
}
catch (exception ex)
{
...
}
finally
{
stat.close();
}
Best regards,
Malte
-----Ursprüngliche Nachricht-----
Von: John English [mailto:john.fore...@gmail.com]
Gesendet: Montag, 15. Oktober 2012 12:02
An: derby-user@db.apache.org
Betreff: Re: Getting transitive closure?
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
