What is the best way to import xml to a database (using java)

Question

I have just written a program for importing data from an xml file to a database using dom4j. It endend up as a series of xpath statements wrapped in java, - and it works, but I strongly feel that there is a better way of doing this.

What is the preferred way to import data from xml into a db?

Answer 1

It really depends on the format of your XML file. Can you elaborate further?

In my case, I have an application that a simple Key->Value pair syntax stored in XML, in which I use DOM to iterate over the nodes and generate SQL to insert the data.

using XPath to do this would be a perfectly fine solution

Answer 2

I often had the same problem, and wish I had the same tools 10 years ago that I do now.

If you need to read existing XML into normal Java classes, I personally believe that apache digester is much more elegant and easier to use than dom4j, since you just specify the mapping.

Then, use Hibernate to write the materials down to the database.

Answer 3

There are several questions you should ask yourself before trying anything else:

• is it worth cleaning? If that app is not going to be used more than infrequently, "don't fix what ain't broken".
• is it going to get changed often? In that case, easy maintenance and readability are paramount, and dom4j is probably not bad on that.
• is performance of any importance? If it does not make any difference your app runs in half the time, go back to the previous bullet: "better" will only mean neater in that case.

Answer 4

Depending on what kind of XML you have, JAXB (which is a standard part of Java SE since 1.6) might be the most straightforward way to deal with it. Just make a class similar to your XML structure, put in the setters and getters for the fields and a @XmlRootElement annotation to the top of the class. Then something like

Unmarshaller um = JAXBContext.newInstance(YourClass.class).createUnmarshaller();
YourClass input = (YourClass) um.unmarshal(new FileReader("file.xml"));

magically converts your XML file to a Java object. Then it should be easy to write to the DB of your choice.

Answer 5

I have found Simple very easy to use for creating Java objects from XML. We use this to setup default configuration that we can then store using Hibernate.

Answer 6

It certainly depends on exactly what you are doing. I assume it is you wanting to extract data from within XML documents. If so, xpath may work ok, but I personally still like data binding more: read XML, bind to java objects, get data via regular Bean/POJO accessors. Or better yet, if using ORM, bind to/from objects ORM (like hibernate) can use.

For datd binding I'd vote for JAXB, or XStream. I would ony use any DOM alternative if xpath was used.