Thanks for the Code. I will try modifying and implement in the way i need.
Vrin Sylvain Leroux wrote: > > 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() }; > } > } > > -- View this message in context: http://www.nabble.com/Recursive-query-on-common-table-tp25819772p26021759.html Sent from the Apache Derby Users mailing list archive at Nabble.com.
