Hi,
Here is a quick and dirty class I wrote yesterday in order to experiment with
the (very useful) table function wrappers provided by Rick.
This function will return the list of all children from a given node (through
the relation Zones.ref <--> Zones.parent).
The table name and the column that serves as reference to the parent node are
all hard coded. But I think you might be found that useful.
Please note that in it current implementation, it could be used both for trees
and graphs containing cycles.
Hope this helps,
Sylvain.
Vrin26 a écrit :
Hi,
Can any one help me with an idea, how I can do a recursion for this issue.
Thanks.
Rick Hillegas-2 wrote:
Vrin26 wrote:
Hi,
I need to do a recursive query on a single derby database table.
In Derby database documents it is mentioned that this derby doesn't
support
recursive queries.
Is there any other work around to handle this scenario?
Thanks
You can push the recursion into a table function or into a database
procedure which returns a result set. The table function documentation
talks about wrapping external data sources, but you can also use table
functions to wrap complicated processing inside Derby itself. See
http://db.apache.org/derby/docs/10.5/devguide/devguide-single.html#cdevspecialtabfuncs
The CREATE PROCEDURE statement is documented here:
http://db.apache.org/derby/docs/10.5/ref/ref-single.html#crefsqlj95081
Hope this helps,
-Rick
--
Website: http://www.chicoree.fr
package fr.chicoree.derby;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashSet;
import java.util.Set;
import sun.javadb.vti.core.EnumeratorTableFunction;
/*
Works on this table:
CREATE TABLE Zones (
-- Unique ID
id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
-- Reference to the parent
parent INT REFERENCES Zones(id),
-- désignation
designation CHAR(80) NOT NULL
);
Usage:
CREATE FUNCTION AllChildren(rootNode INT) RETURNS TABLE (Children INT)
LANGUAGE java
PARAMETER STYLE DERBY_JDBC_RESULT_SET
READS SQL DATA
EXTERNAL NAME 'fr.chicoree.derby.AllChildren.allChildren'
;
-- Get all children of node 2:
SELECT * FROM TABLE (AllChildren(2) ) AS T;
*/
public class AllChildren extends EnumeratorTableFunction {
static final String[] columns = { "Children" };
public AllChildren(int rootNode) throws SQLException {
super(columns);
Set<Integer> subTree = new HashSet<Integer>();
Set<Integer> parentSet = new HashSet<Integer>();
parentSet.add(rootNode);
Connection conn = DriverManager.getConnection(
"jdbc:default:connection" );
Statement stmt = conn.createStatement();
final String query = "SELECT Children.id " +
" FROM Zones AS
Children " +
" INNER JOIN Zones AS
Parent " +
" ON Parent.id =
Children.parent" +
" WHERE Parent.id in
(%s)";
int subTreeSize = subTree.size();
int subTreePrevSize;
do {
StringBuilder args = new StringBuilder();
String glue = "";
for(Integer node : parentSet) {
args.append(glue);
args.append(node);
glue = ",";
}
// System.err.println(String.format(query,
args.toString()));
ResultSet rs = stmt.executeQuery(String.format(query,
args.toString()));
parentSet = new HashSet<Integer>();
while(rs.next()) {
Integer child = rs.getInt(1);
subTree.add(child);
parentSet.add(child);
}
subTreePrevSize = subTreeSize;
subTreeSize = subTree.size();
} while(subTreePrevSize < subTreeSize);
setEnumeration(subTree);
}
public static ResultSet allChildren(int rootNode) throws SQLException
{ return new
AllChildren(rootNode); }
@Override
public String[] makeRow(Object node) throws SQLException {
return new String[] { node.toString() };
}
}
