DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Providing Enum Consistency Between Application and Data
  • Non-blocking Database Migrations
  • Enterprise RIA With Spring 3, Flex 4 and GraniteDS
  • The First Annual Recap From JPA Buddy

Trending

  • A Deep Dive Into Firmware Over the Air for IoT Devices
  • How to Configure and Customize the Go SDK for Azure Cosmos DB
  • Transforming AI-Driven Data Analytics with DeepSeek: A New Era of Intelligent Insights
  • Kubeflow: Driving Scalable and Intelligent Machine Learning Systems
  1. DZone
  2. Coding
  3. Languages
  4. Load XML Into MySQL Using Java

Load XML Into MySQL Using Java

If you like XML's handy hierarchical style and the familiarity of Java, you're in luck: You can quickly parse your XML data and put it into a MySQL database with ease.

By 
Jay Sridhar user avatar
Jay Sridhar
·
Feb. 21, 17 · Tutorial
Likes (11)
Comment
Save
Tweet
Share
54.5K Views

Join the DZone community and get the full member experience.

Join For Free

“Never memorize something that you can look up.”
― Albert Einstein

XML provides the ability to represent hierarchical structures with its parent-child relationships. This enables applications to store structured data in XML for export. Importing this XML data into a database is a bit involved as we shall see in this article. You need to write code to manage the database connection. In addition, you need to parse the XML and isolate the data that needs to be imported.

In this article, we show how to import and load XML data into a MySQL database. The application creates the table for storing the data and proceeds to import the data.

We are attempting to import data from books.xml, which looks like this:

<?xml version="1.0"?>
<catalog>
  <book id="bk101">
    <author>Gambardella, Matthew</author>
    <title>XML Developer's Guide</title>
    <genre>Computer</genre>
    <price>44.95</price>
    <publish_date>2000-10-01</publish_date>
    <description>An in-depth look at creating applications
    with XML.</description>
  </book>
  <book id="bk102">
    <author>Ralls, Kim</author>
    <title>Midnight Rain</title>
    <genre>Fantasy</genre>
...


2. Load MySQL JDBC Driver

Download the MySQL Connector/J Driver from here. Unpack the distribution and copy the mysql-connector-java-<version>-bin.jar to your application directory. (Replace <version> with the version of the driver you downloaded.)

The first thing we need to do in the application is to load the MySQL JDBC driver. While we use a static block for that, you can always load the driver while the application is running.

static {
    try { Class.forName("com.mysql.jdbc.Driver"); }
    catch(ClassNotFoundException ex) {
    System.err.println("Driver not found: " + ex.getMessage());
    }
};


3. Connect to MySQL

The format of the JDBC connection string is as shown. This string is used for connecting to a MySQL database running on the localhost — the database name we are using is testing. The username and password are also specified in the connection string.

String dbUrl="jdbc:mysql://localhost/testing?user=uname&password=pwd";


Here is the code to open a connection to the MySQL database using the above connection string.

Connection conn = DriverManager.getConnection(dbUrl);


If you would rather specify the username and password separately instead of including it in the connection string, you can do this instead. (Maybe you are obtaining these values from different locations.)

String dbUrl = "jdbc:mysql://localhost/testing";
String uname = ...;
String pwd = ...;
Connection conn = DriverManager.getConnection(dbUrl, uname, pwd);


4. Create MySQL Table

Let us now create the table with the structure needed for the storage.

conn.createStatement()
    .execute("CREATE TABLE books(\n" +
         " id integer primary key auto_increment,\n" +
         " book_id varchar(25) not null unique,\n" +
         " author varchar(50) not null,\n" +
         " title varchar(250) not null,\n" +
         " genre varchar(25) not null,\n" +
         " price float not null,\n" +
         " publish_date date not null,\n" +
         " description text not null\n" +
         ")");


Note the following:

  • MySQL sets the id field to an auto incremented value when creating the row.
  • We store the XML attribute id in the book_id field.
  • The description field is text since we expect it to be somewhat large but not exceed 65,535 characters.

5. Parse XML

With the database setup code out of the way, let us look into importing the XML data into the application. See here for a tutorial about using an XML parser to parse the XML data.

File file = new File(fileName);
DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
DocumentBuilder builder = factory.newDocumentBuilder();
Document xmlDoc = builder.parse(file);


With this code, we end up with a Document object representing the XML data in memory. We can use this Document object to search for the data we want to insert into the database.

To insert the data, we need to obtain the required data from the XML nodes. The following is a convenience method to extract an attribute value from a Node.

static private String getAttrValue(Node node,String attrName) {
    if ( ! node.hasAttributes() ) return "";
    NamedNodeMap nmap = node.getAttributes();
    if ( nmap == null ) return "";
    Node n = nmap.getNamedItem(attrName);
    if ( n == null ) return "";
    return n.getNodeValue();
}


And we use this convenience method to extract the text content of a named child element.

static private String getTextContent(Node parentNode,String childName) {
    NodeList nlist = parentNode.getChildNodes();
    for (int i = 0 ; i < nlist.getLength() ; i++) {
    Node n = nlist.item(i);
    String name = n.getNodeName();
    if ( name != null && name.equals(childName) )
        return n.getTextContent();
    }
    return "";
}


7. Prepare XML Data

We use XPath to extract the set of nodes whose data we want to insert into the database. Given the structure of the XML, the set of nodes are located at the XPath /catalog/book.

XPath xpath = XPathFactory.newInstance().newXPath();
Object res = xpath.evaluate("/catalog/book",
                 xmlDoc,
                 XPathConstants.NODESET);


The above code returns a NodeList that matched the specified XPath.

8. Insert into MySQL

To insert the data extracted in a loop, we create a PreparedStatement as follows:

PreparedStatement stmt = conn
    .prepareStatement("INSERT INTO books(\n" +
              " book_id, author, title, genre, price,\n" +
                 " publish_date, description)\n" +
                  "VALUES(?, ?, ?, ?, ?,\n" +
                  " str_to_date(?, '%Y-%m-%d'), ?)");


We use the following loop to extract and insert the data from the child elements using the convenience methods defined earlier:

for (int i = 0 ; i < nlist.getLength() ; i++) {
    Node node = nlist.item(i);
    List<String> columns = Arrays
    .asList(getAttrValue(node, "id"),
        getTextContent(node, "author"),
        getTextContent(node, "title"),
        getTextContent(node, "genre"),
        getTextContent(node, "price"),
        getTextContent(node, "publish_date"),
        getTextContent(node, "description"));
    for (int n = 0 ; n < columns.size() ; n++) {
    stmt.setString(n+1, columns.get(n));
    }
    stmt.execute();
}


With that, we have successfully imported data from an XML file into a MySQL database.

Summary

This article covered the details of importing XML into a MySQL database. We parsed the XML using the DOM parser. Then we used XPath to extract just the data we needed. Finally, we inserted it into the database using JDBC.

XML Database MySQL Data (computing) application Java (programming language)

Published at DZone with permission of Jay Sridhar, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • Providing Enum Consistency Between Application and Data
  • Non-blocking Database Migrations
  • Enterprise RIA With Spring 3, Flex 4 and GraniteDS
  • The First Annual Recap From JPA Buddy

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!