db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <Malte.Kem...@de.equens.com>
Subject AW: Getting transitive closure?
Date Mon, 15 Oct 2012 11:21:04 GMT
 

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.foreign@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

Mime
View raw message