I need to get data from an XML file and store it into a MySQL Database. I am thinking of using a SAX Parser for parsing the data but I am not sure of how to store data efficiently into database, I am thinking of few technologies like JDBC and Hibernate but I wanted to ask about what would be the efficient way of doing it?
Note: Here programming language is Java.
I would suggest using JAXB for reading in the XML to Java objects and JPA for writing them into the database. You can create a single data model using Java classes that have both annotations for XML binding using JAXB and database persistence annotations using JPA.
@Entity
@Table(name="Foo")
@XmlRootElement
public class Foo {
// ...
}
Information on JAXB annotations. Information on JPA.
You could use Castor witch is an open source data binding framework for moving data from XML to Java programming language objects and from Java to databases.
I found also article series in IBM developerWorks that describe using Castor suited to your needs.
You may store XML into mySQL directly using blob... if you want efficient indexing and high performance, VTD-XML has built-in ability to index/query/update XML document, making it a better alternative than SAX and DOM, here is a link to a related article
It depends on many factors. If your XML is too large ( > 1GB or comparable with your total memory), then you should use SAX and I don't think there would be other solutions. If it's small (say smaller than 100MB), simply load the whole XML into a Document object using JAXP:
DocumentBuilderFactory documentBuilderFactory = DocumentBuilderFactory.newInstance();
DocumentBuilder parser = documentBuilderFactory.newDocumentBuilder();
Document doc = parser.parse(source);
You probably have elements or attributes mappied to columns on DB. You can then query elements/attrs using XPath for simplicity and write them to DB. It this is a one-time conversion, I recommend using simple JDBC. Don't think for JPA or Hibernate, as it just increases your development time for a routine data conversion scenario.
