Java to convert table data into XML and vice versa. Example using MySql

In this tutorial, let us see how to convert MySQL database table data into XML and vice versa. Let us create XML from a table (product) which has two fields named ProductId, ProductName. In this example, the generated XML will contain the table data including Table Name and Structure.

The sample XML to be created is given below.

<Table>
	<TableName>product</TableName>
	<TableStructure>
		<Column1>
			<ColumnName>productId</ColumnName>
			<ColumnType>VARCHAR</ColumnType>
			<Length>10</Length>
		</Column1>
		<Column2>
			<ColumnName>productName</ColumnName>
			<ColumnType>VARCHAR</ColumnType>
			<Length>100</Length>
		</Column2>
	</TableStructure>
	<TableData>
		<Product1>
			<productId>I1</productId>
			<productName>Samsung Tv</productName>
		</Product1>
		<Product2>
			<productId>I3</productId>
			<productName>SONY LED TV</productName>
		</Product2>
	</TableData>
</Table>

For retriving Table Name and Table Structure, we will use getMetaData() of ResultSetMetaData.

The complete java program is given below for converting Table dats to XML and vice versa.
It has two methods.
generateXML()– converts table data to XML. The XML includes table data and structure of the table. Table structure includes basic details like table name, column name, column type and column precision. You can include more details as per your requirement.

xmlToTable() – converts XML into table data. DDL and DML is generated by parsing the XML to create table structure and insert table data.

 

package net.javaonline.db2xml;

import java.io.StringWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;

public class TableToXML {

	public static void main(String arg[]) {


//Table data to XML 
		Document doc = null;
		try {
			doc = TableToXML.generateXML();
		} catch (TransformerException | ParserConfigurationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}


//XML to table data
		try {
			TableToXML.xmlToTable(doc);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	
	public static Document generateXML() throws TransformerException,
			ParserConfigurationException {

		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		DOMSource domSource = null;

		DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
		DocumentBuilder builder = factory.newDocumentBuilder();
		Document doc = builder.newDocument();
		Element results = doc.createElement("Table");
		doc.appendChild(results);

		try {

			try {
				Class.forName("com.mysql.jdbc.Driver");
				con = DriverManager.getConnection(
						"jdbc:mysql://localhost:3306/test", "root", "*****");
			} catch (Exception e) {
				System.out.println(e);
				System.exit(0);
			}

			pstmt = con
					.prepareStatement("select productid, productname from product");

			rs = pstmt.executeQuery();

			System.out.println("Col count pre ");
			ResultSetMetaData rsmd = rs.getMetaData();//to retrieve table name, column name, column type and column precision, etc..
			int colCount = rsmd.getColumnCount();

			Element tableName = doc.createElement("TableName");
			tableName.appendChild(doc.createTextNode(rsmd.getTableName(1)));
			results.appendChild(tableName);

			Element structure = doc.createElement("TableStructure");
			results.appendChild(structure);

			Element col = null;
			for (int i = 1; i <= colCount; i++) {

				col = doc.createElement("Column" + i);
				results.appendChild(col);
				Element columnNode = doc.createElement("ColumnName");
				columnNode
						.appendChild(doc.createTextNode(rsmd.getColumnName(i)));
				col.appendChild(columnNode);

				Element typeNode = doc.createElement("ColumnType");
				typeNode.appendChild(doc.createTextNode(String.valueOf((rsmd
						.getColumnTypeName(i)))));
				col.appendChild(typeNode);

				Element lengthNode = doc.createElement("Length");
				lengthNode.appendChild(doc.createTextNode(String.valueOf((rsmd
						.getPrecision(i)))));
				col.appendChild(lengthNode);

				structure.appendChild(col);
			}

			System.out.println("Col count = " + colCount);

			Element productList = doc.createElement("TableData");
			results.appendChild(productList);

			int l = 0;
			while (rs.next()) {
				Element row = doc.createElement("Product" + (++l));
				results.appendChild(row);
				for (int i = 1; i <= colCount; i++) {
					String columnName = rsmd.getColumnName(i);
					Object value = rs.getObject(i);
					Element node = doc.createElement(columnName);
					node.appendChild(doc.createTextNode((value != null) ? value
							.toString() : ""));
					row.appendChild(node);
				}
				productList.appendChild(row);
			}

			
			
			domSource = new DOMSource(doc);
			TransformerFactory tf = TransformerFactory.newInstance();
			Transformer transformer = tf.newTransformer();
			transformer.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION,
					"yes");
			transformer.setOutputProperty(OutputKeys.METHOD, "xml");
			transformer.setOutputProperty(OutputKeys.ENCODING, "ISO-8859-1");

			StringWriter sw = new StringWriter();
			StreamResult sr = new StreamResult(sw);
			transformer.transform(domSource, sr);
			
		

			System.out.println("Xml document 1" + sw.toString());

			System.out.println("********************************");

		} catch (SQLException sqlExp) {

			System.out.println("SQLExcp:" + sqlExp.toString());

		} finally {
			try {

				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (SQLException expSQL) {
				System.out
						.println("CourtroomDAO::loadCourtList:SQLExcp:CLOSING:"
								+ expSQL.toString());
			}
		}

		// return sw.toString();

		return doc;

	}

	public static void xmlToTable(Document doc) throws SQLException

	{

		Connection con = null;

		try {
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/test", "root", "*****");
		} catch (Exception e) {
			System.out.println(e);
			System.exit(0);
		}

		System.out.println("Table Name= "
				+ doc.getElementsByTagName("TableName").item(0)
						.getTextContent());

		StringBuffer ddl = new StringBuffer("create table "
				+ doc.getElementsByTagName("TableName").item(0)
						.getTextContent() + "1 (");

		StringBuffer dml = new StringBuffer("insert into  "
				+ doc.getElementsByTagName("TableName").item(0)
						.getTextContent() + "1 (");

		NodeList tableStructure = doc.getElementsByTagName("TableStructure");

		int no_of_columns = tableStructure.item(0).getChildNodes().getLength();

		for (int i = 0; i < no_of_columns; i++) {
			ddl.append(doc.getElementsByTagName("ColumnName").item(i)
					.getTextContent()
					+ " "
					+ doc.getElementsByTagName("ColumnType").item(i)
							.getTextContent()
					+ "("
					+ doc.getElementsByTagName("Length").item(i)
							.getTextContent() + "),");
			dml.append(doc.getElementsByTagName("ColumnName").item(i)
					.getTextContent()
					+ ",");

		}

		System.out.println(" DDL " + ddl.toString());
		System.out.println(" dml " + dml.toString());

		ddl = ddl.replace(ddl.length() - 1, ddl.length(), ")");
		dml = dml.replace(dml.length() - 1, dml.length(), ") values(");

		System.out.println(" DDL " + ddl.toString());

		for (int k = 0; k < no_of_columns; k++)
			dml.append("?,");

		dml = dml.replace(dml.length() - 1, dml.length(), ")");

		System.out.println(" dml " + dml.toString());

		Statement stmt = null;

		try {
			stmt = con.createStatement();
			// to create table One time only;
			stmt.executeUpdate(ddl.toString());

		} catch (Exception e) {
			System.out
					.println("Tables already created, skipping table creation process"
							+ e.toString());
		}

		NodeList tableData = doc.getElementsByTagName("TableData");

		int tdlen = tableData.item(0).getChildNodes().getLength();

		PreparedStatement prepStmt = con.prepareStatement(dml.toString());

		String colName = "";
		for (int i = 0; i < tdlen; i++) {
			System.out.println("Outer" + i);

			for (int j = 0; j < tableStructure.item(0).getChildNodes()
					.getLength(); j++) {

				colName = doc.getElementsByTagName("ColumnName").item(j)
						.getTextContent();
				prepStmt.setString(j + 1, doc.getElementsByTagName(colName)
						.item(i).getTextContent());

				System.out.println("Data  ="
						+ doc.getElementsByTagName(colName).item(i)
								.getTextContent());

			}

			prepStmt.addBatch();

		}

		int[] numUpdates = prepStmt.executeBatch();

		System.out.println(numUpdates + " records inserted");



	}

}

 

For Running the program, create a table product with the fields productId and productName

DDL to create the table

CREATE TABLE product (productId varchar(10),productName varchar(100));

Insert two records

insert into product values(‘I1′,’LCD TV’);
insert into product values(‘I1′,’LED TV’);

 

Running the above program, will create the following

1. XML File

2. copy of product table with data named product1

table to xml and viceversa

 

Leave a Reply