Database help.

[stanger89]

I'm sure there are some database gurus out there so I pose this question to you.

I want to be able to search an XML (DVD Profiler) on any field and get back a list of entries that contain that field.

Say there's this format:

<DVD>
  <Title>The Matrix</Title>
  <Actors>
    <Actor>
      <FirstName>Laurence</FirstName>
      <LastName>Fishburn</LastName>
      <Role>Morpheus</Role>
    </Actor>
  </Actors>
</DVD>

I want to be able to query for Actor == Laurence Fishburn and get back a list of DVDs (titles) containing Actor.

I'm looking for the easiest way, I know I don't need all that something like MySQL can deliver, but I also don't want to spend all my time coding something that's done already.

[GKusnick]

In XPath this would be something like "DVD[Actors/Actor/(FirstName = 'Laurence' and LastName = 'Fishburn')]". So in standard DOM syntax you'd say something like:

nodelist = doc.selectNodes("DVD[Actors/Actor/(FirstName = 'Laurence' 
  and LastName = 'Fishburn')]");

This would return a list of all DVD nodes such that DVD/Actors/Actor/FirstName = 'Laurence' and DVD/Actors/Actor/LastName = 'Fishburn'. Then you just iterate through the list.

However I'm not real sure off the top of my head how you do DOM calls in Java. Maybe someone else can help with that.

[dflachbart]

Originally Posted by GKusnick

In XPath this would be something like "DVD[Actors/Actor/(FirstName = 'Laurence' and LastName = 'Fishburn')]". So in standard DOM syntax you'd say something like:

nodelist = doc.selectNodes("DVD[Actors/Actor/(FirstName = 'Laurence' 
  and LastName = 'Fishburn')]");

This would return a list of all DVD nodes such that DVD/Actors/Actor/FirstName = 'Laurence' and DVD/Actors/Actor/LastName = 'Fishburn'. Then you just iterate through the list.

However I'm not real sure off the top of my head how you do DOM calls in Java. Maybe someone else can help with that.

I'm not familiar with the full XPath syntax, but if you want to return the titles shouldn't the XPath be "/DVD[Actors/Actor/(FirstName = 'Laurence' and LastName = 'Fishburn')]/Title" ?

Edit: I just realized that Gregs Xpath is also correct, you'll get back a list of DVD nodes and just need to pull out the title info manually...


To use XPath lookups in Java, you can use the Xalan package from Apache, it offers a full XPath implementation. There might also be something more lightweight out there which I don't know about. If you need help with using xalan, I could look this up, we are using it in a project at work ...


Dirk

[stanger89]

Looks like I have to break down and figure out XPath

Thanks for the suggestions guys.

[stanger89]

Well, FYI, Java 5 (1.5.x) includes java.xml.xpath, which I'd kind of like to stick with since it minimizes the amount of stuff that must be installed.

So anyway, be gentile No laughing, at least not too much.

Trying to start easy, I've got a function, getTitles, which will return a list of the Titles stored in the XML. Originally it looked like this:

        for(int i = 0; i < numTitles; i++)
		{
			titles.add(getTitle(i));
			safeTitles.add(getSafeTitle(i));
		}

        public String getTitle(int n){
		Node node = DVDs.item(n);
		node.normalize();
		Node Property = node.getFirstChild();
		String value = "";
		
		while(Property.getNodeName() != "Title" && Property.getNextSibling() != null) {
			Property = Property.getNextSibling();
		}
		
		value = Property.getFirstChild().getNodeValue();
		return value;
	}

        public Vector getTitles()
	{
		return titles;
	}

How would I convert that to use XPath?

[GKusnick]

I would do it roughly along the following lines:

XPathFactory factory = XPathFactory.newInstance();
XPath xpath = factory.newXPath();
NodeList nodes = xpath.evaluate(
  "//DVD[Actors/Actor/(FirstName = 'Laurence' and LastName = 'Fishburn')]/Title", 
  DVDs, NODESET);

for (int i = 0; i < nodes.getLength(); i++)
    {
    titles.add(nodes.item(i).getTextContent());
    safeTitles.add(getSafeTitle(i));
    }

Please note that I'm looking at java.xml.xpath for the first time here, and have not made any attempt to compile or test this code. But from my reading of the javadocs, this is more or less how it should go.

That's a fairly complex XPath expression that may not be exactly right, so my advice would be to start with a much simpler expression such as "//Title" (which simply retrieves all the Title nodes anywhere in the XML tree), get your code working with that, and then build up the more complex expression step by step. So for instance, once you get "//Title" working, try "//DVD/Title" (i.e. all Title nodes that are direct children of DVD nodes), then "//DVD[Actors]/Title" (all Title nodes that are direct children of DVD nodes that have Actors), and so on. That way when you screw it up, you'll know exactly where the screwup happened.

[stanger89]

Cool, thanks. I've got the getTitles() working with XPath
The code for anyone interested:

    public Vector getTitles()
    {    
        NodeList nodes = null;
        Vector result = new Vector();
        try {
            nodes =  (NodeList) xpath.evaluate("//DVD/Title", document, XPathConstants.NODESET);
        } catch (XPathExpressionException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        for (int i = 0; i < nodes.getLength(); i++)
        {
            result.add(nodes.item(i).getTextContent());
        }
        return result;
    }

Time to work on figuring out XPath and what all it can do

[stanger89]

FYI,

I found a really cool interactive XPath evaluator:
http://www.develop.com/technology/re...0-795f9aebcebf

You can load your own XMLs into it and all