db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John English <john.fore...@gmail.com>
Subject Re: Getting transitive closure?
Date Mon, 15 Oct 2012 10:02:24 GMT
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