db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Vrin26 <divi.markapu...@in.ibm.com>
Subject Re: Recursive query on common table
Date Fri, 23 Oct 2009 07:05:29 GMT

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.


Mime
View raw message