Inserting Multiple Records from XML files into MaxDB Database

[Former Member]

Inserting Multiple Records from XML files into MaxDB Database

In my last weblog I explained a simple technique to Inserting Multiple Records into MaxDB Database into MaxDB database server. I always prefer having a variety of options in most cases. In general the data we receive can be in any format and hence we cannot limit ourselves to specific formats. Some time back I posted a weblog for Creating an Extractor for downloading MaxDB Table Data to XML. I felt it will be good option to insert multiple data from XML files into MaxDB.
This will minimize the problems due to the delimiters and XML being widely accepted format things will be very much easier while extraction and upload. I would recommend all readers to have a look at JAXP if you want to understanding the XML parsing which is the main addition when compared to my Inserting Multiple Records into MaxDB Database weblog. And for the below two forum threads one more solution is here,
How to insert multiple rows
😞 Mass Data Upload to table in MaxDB

Hoping things are much simpler except for the piece java code I will get into the solution straight away.

Table Definition

I considered a simple table for the test which had the below definition.



Table Contents

The number of records it contained was only one.



Records to insert

Now the task is to upload a set of records shown below into the database from XML file.



The JDBC Program

The JDBC program for performing the multiple records insert into MaxDB is given below.

/** * Project      :InsertMultipleRecords * Date         :Aug 9, 2005 * Programmed By: Kathirvel Balakrishnan * Company      : Wipro Technologies * Designation  : Project Engineer */package com.MaxDB;import java.io.File;import java.io.IOException;import java.sql.DriverManager;import java.sql.Connection;import java.sql.Statement;import javax.xml.parsers.DocumentBuilder;import javax.xml.parsers.DocumentBuilderFactory;import javax.xml.parsers.ParserConfigurationException;import org.w3c.dom.Document;import org.w3c.dom.Element;import org.w3c.dom.Node;import org.w3c.dom.NodeList;import org.xml.sax.SAXException;import org.xml.sax.SAXParseException;/** * InsertMultipleRecordsFromXML */public class InsertMultipleRecordsFromXML{  String myFileName = "d:/accesscard.xml"; int recordCount = 0; String myRecords[][] = new String[100][100]; /**  * Loading the driver for the MaxDB Database server  */ public void loadDriver() {  try {   Class.forName("com.sap.dbtech.jdbc.DriverSapDB");   System.out.println("The Driver has been loaded successfully!");  } catch (Exception E1) {   System.out.println("Unable to load the Driver!");   System.out.println("Exceptions:" + E1.toString());   System.exit(1);  } } /**  * Parsing the XML file and storing the values into an array for processing  */ public void ReadXMLIntoArray(){  try{     DocumentBuilderFactory myDocBuildFactory = DocumentBuilderFactory.newInstance();   DocumentBuilder myDocBulider = myDocBuildFactory.newDocumentBuilder();   Document myDocument = myDocBulider.parse(new File(myFileName));      myDocument.getDocumentElement().normalize();   NodeList numberOfRows = myDocument.getElementsByTagName("row");   System.out.println("The Number of Records in the given file is: "+ numberOfRows.getLength());   recordCount = numberOfRows.getLength();      for(int i = 0; i < numberOfRows.getLength(); i++){        Node myNode = numberOfRows.item(i);       if(myNode.getNodeType() == Node.ELEMENT_NODE){     Element myElement = (Element)myNode;          NodeList idList = myElement.getElementsByTagName("ID");     Element idElement = (Element)idList.item(0);     NodeList idText = idElement.getChildNodes();     myRecords[i][0] = ((Node)idText.item(0)).getNodeValue().trim();          NodeList nameList = myElement.getElementsByTagName("NAME");     Element nameElement = (Element)nameList.item(0);     NodeList nameText = nameElement.getChildNodes();     myRecords[i][1] =  ((Node)nameText.item(0)).getNodeValue().trim();                   NodeList cardNoList = myElement.getElementsByTagName("CARDNO");     Element cardNoElement = (Element)cardNoList.item(0);     NodeList cardNoText = cardNoElement.getChildNodes();     myRecords[i][2] = ((Node)cardNoText.item(0)).getNodeValue().trim();         }   }     }catch(SAXParseException SE1){   System.out.println("SAXParseException:"+SE1.getMessage());     }catch(SAXException SE2){   System.out.println("SAXException:"+SE2.getMessage());  }catch(IOException IO){   System.out.println("IOException:"+IO.getMessage());  }catch(ParserConfigurationException PE){   System.out.println("ParserConfigurationException:"+PE.getMessage());  } } /**  * Now all the records that have been read are being inserted into MaxDB  */ public void InsertData() {  String serverName = "LOCALHOST";  String dataBase = "TEST";  String userName = "DBADMIN";  String password = "maxdb";  Connection myConnection;  Statement myStatement;  System.out.println("Establishing connection to Server");  try {   //Establishing the connection with the database   myConnection =    DriverManager.getConnection(     "jdbc:sapdb://" + serverName + "/" + dataBase,     userName,     password);   myStatement = myConnection.createStatement();   System.out.println("Connection to Server was Established");   // Executing the SQL Query   for (int i = 0; i < recordCount; i++) {    String TempQuery =     "INSERT INTO ACCESSCARD VALUES('"      + myRecords[i][0]      + "','"      + myRecords[i][1]     + "','"      + myRecords[i][2]      + "')";    myStatement.executeUpdate(TempQuery);    System.out.println("Current Query is:"+TempQuery);        }   System.out.println("Data has successfully inserted into MaxDB<br /> ");   // Closing   myStatement.close();   myConnection.close();  } catch (Exception E2) {   // Prints if any exception is raised   System.out.println(" Exceptions : " + E2.toString());  }  }   public static void main(String[] args) {  // Creating a new instance  InsertMultipleRecordsFromXML In = new InsertMultipleRecordsFromXML();    // Loading the JDBC Driver   In.loadDriver();    // Reading XML Data into array for processing  In.ReadXMLIntoArray();    // Insert the data into MaxDB from the array  In.InsertData(); }}

The Result of the Program

The program did work perfectly and the test run results were displayed.



Verification

The data was successfully inserted into MaxDB. The table entries are shown below.



Hope this was useful.