cocoon-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Matthew Cordes <mcord...@maine.edu>
Subject Re: hierarchical resultsets & xml
Date Tue, 08 Aug 2000 13:13:25 GMT
Steve,

I wrote my own little group of classes that takes a sql query 
and an array of Nodes ( Text, Attribute, Element ).  The array is
arranged in tree order. I run the sql query, get back the resultset
and let each node do its thing to create the xml, in pseudo:

// create doc and root
Document myDoc = DOMImplementationImpl( null, "root", null );
Element rootElem = myDoc.getDocumentElement();

// loop over resultset
while ( myResultSet.next() )
{
	Element tempElem = rootElem;

	// loop over nodes
	for( int x=0; x < nodes.length; x++ )
	{
		tempElem = nodes[x].getXML( myResultSet, tempElem );
	}
}

return tempElem.getOwnerDocument();


and I currently use it like this:

String sqlString = "select category, name, age, "
	+ from myschema.mytable where x=?";

// yes, my class names really suck
DBClass dbClass = new DBClass( m_dbConnection );

// the first node is the root of the xml tree
DBNodes[] nodes = { new NodeDBNode( "root_element", null ),
	new NodeDBNode( "category" ),
	new NodeDBNode( "person", "name", "name" ),
	new AttributeDBNode( "age" ) };

dbClass.load( sqlString, nodes );
dbClass.setString( 1, "some_string" );
dbClass.execute();
Document doc = dbClass.getDocument();



which would produce the following xml:

<root_element>
	<category id="1">
		<person name="bob" age="25"/>
		<person name="joe" age="22"/>
	</category>
	<category id="2">
		<person name="sam" age="27"/>
		<person name="rich" age="55"/>
	</category>
</root_element>	

	


each type of node ( AttributeDBNode, TextDBNode, and NodeDBNode)
has a getXML( ResultSet, Element ) method and thanks to the 
beauty of polymorphism does
different things to the resultset and element it is passed ( in 
the for loop above ).

NodeDBNode( element nodes ) get a string from the database using the 
field name we used to create it and look at all the children of the 
element passed to it for one with an attribute named 'id' ( this 
can be changed, but for simplicity I'll leave it this way for now ).
If one is found it is returned, if not it is created and returned.

TextDBNodes, also get a value from the DB, the add a child by their tag
name ( which can be different than the database field name ) to that
element and return the element they were initially passed.

AttributeDBNodes act very much like textNodes, but add an attribute and 
return the element they were passed.


I hope this gives you some ideas on how to write your own db->xml
classes, if you'd like my source code feel free to email me.  I'd 
really like to make this thing as fast and easy as possible because it 
has already saved some headaches at work.

-matt


On Tue, Aug 08, 2000 at 08:55:05AM +0200, Henning von Bargen wrote:
> > -----Urspr√ľngliche Nachricht-----
> > Von:	Raj Basavaraju [SMTP:raj@bidland.com]
> > Gesendet am:	Dienstag, 8. August 2000 00:43
> > An:	cocoon-users@xml.apache.org
> > Betreff:	Re: hierarchical resultsets & xml
> > 
> > Steve Conover wrote:
> > 
> > > H everyone,
> > >
> > > For the last week I've been trying to solve the problem of how to write
> ONE
> > > sql query, and get back a resultset that can be looped thru and
> translated
> > > into XML.  And I want to use XSP/Cocoon.
> > >
> > > It appears that the root of the problem is that JDBC doesn't do
> hierarchical
> > > recordsets (I don't think it's in jdbc 2.0 either - does anyone know
> this
> > > for sure?).  So I can't directly construct a query that brings back
> > 
> > You are right, even jdbc 2.0 doesn't handle hierarchical recordsets.
> > 
> > > department 1
> > >         Employee 1
> > >         Employee 2
> > > department 2
> > >         Employee 3
> > >         Employee 4
> > > etc...(that wouldn't use standard sql anyway, it'd be oracle-specific)
> > >
> > > So my question is, can all of you who have dealt with this problem
> impart
> > > some of your wisdom?  Do you go thru several xsp steps (run query, get
> > > departments, for-each department, run query to get employees, etc)?
> What's
> > > the most elegant way of doing this?  Any performance gotchas to look out
> > > for?
> > 
> > Can you use hierarchical query something like:
> > 
> >   select rpad(' ',level*3)||surname employee
> >   from employees
> >   start with mgr_id = 0
> >   connect by prior emp_id = mgr_id;
> > 
> > Best regards.
> > 
> > Raj
> 
> Seems not a good idea to me.
> Performance isn't good for this kind of query, 
> and IMHO the intention of the connect by prior syntax is for forest data
> structures
> with trees of arbitrary depth (like in the well-known employee-manager
> example above).
> Joining different tables which have a foreign-key constraint this way seems
> to be mis-use.
> Henning
> 
> > 
> > 
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: cocoon-users-unsubscribe@xml.apache.org
> > For additional commands, e-mail: cocoon-users-help@xml.apache.org
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: cocoon-users-unsubscribe@xml.apache.org
> For additional commands, e-mail: cocoon-users-help@xml.apache.org
> 

Mime
View raw message