Java XML MYSQL

Do you want to save the xml as a blob? or do you want to create a db schema from the xml file and insert data?

Okay so you want to take the XML data and store it into a database. Assuming you already have a database set up, you can use the following two methods to connect to your database and insert data to it.

The connect() method simply creates the connection to your database that will allow you interact with it. You need to modify the connection string to match your database.

The saveToDB(xmlData) method takes the data from your XML as a parameter and inserts it into the database that you've connected to.

Cheers

private Connection con = null;
private Statement stmt = null;

    public void connect() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = (Connection) DriverManager.getConnection("jdbc:mysql://server.url.com:3306/dbName", "userName", "password");
        } catch (Exception ex) {
            Logger.getLogger(WriteToDB.class.getName()).log(Level.SEVERE, null, ex);
        }
    }



    public void saveToDB(Object xmlData) {
        try {
            stmt = (Statement) con.createStatement();
            String sql = "INSERT INTO tableName(field1) VALUES('" + xmlData + "');";
            stmt.execute(sql);
        } catch (SQLException ex) {
            Logger.getLogger(WriteToDB.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

Select allOpen in new window

hi a_b & patsmitty

i have attached my code and xml file. here is my output.

"Root element company
Information of all employees
First Name : Tom
Last Name : Cruise
First Name : Paul
Last Name : Enderson
First Name : George
Last Name : Bush
First Name : Gobinath
Last Name : Palan"

i want to create a table in mysql with two fields such as "first name" & "lastname" and insert the above values in it.
i hope i m clear in my question. now a_b i really dont know tat i have to use blob for this. if i m wrong.. correct me...

i m going to try patsmitty code and get back to u if i have any errors. thanks for both ur responses.

public class XMLReader {

 public static void main(String argv[]) {

  try {
  File file = new File("/home/workspace/dom2.xml");
  DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
  DocumentBuilder db = dbf.newDocumentBuilder();
  Document doc = db.parse(file);
  doc.getDocumentElement().normalize();
  System.out.println("Root element " + doc.getDocumentElement().getNodeName());
  NodeList nodeLst = doc.getElementsByTagName("employee");
  System.out.println("Information of all employees");

  for (int s = 0; s < nodeLst.getLength(); s++) {

    Node fstNode = nodeLst.item(s);
    
    if (fstNode.getNodeType() == Node.ELEMENT_NODE) {
  
           Element fstElmnt = (Element) fstNode;
      NodeList fstNmElmntLst = fstElmnt.getElementsByTagName("firstname");
      Element fstNmElmnt = (Element) fstNmElmntLst.item(0);
      NodeList fstNm = fstNmElmnt.getChildNodes();
      System.out.println("First Name : "  + ((Node) fstNm.item(0)).getNodeValue());
      NodeList lstNmElmntLst = fstElmnt.getElementsByTagName("lastname");
      Element lstNmElmnt = (Element) lstNmElmntLst.item(0);
      NodeList lstNm = lstNmElmnt.getChildNodes();
      System.out.println("Last Name : " + ((Node) lstNm.item(0)).getNodeValue());
    }

  }
  } catch (Exception e) {
    e.printStackTrace();
  }
 }
}

Select allOpen in new window

dom2.xml

Read - http://www.roseindia.net/java/example/java/io/flat-file-xml.shtml

now i have modified the above code given by patsmitty and i created a table in mysql with 2 fields.

u have mentioned " public void saveToDB(Object xmlData) " in ur code. how do i pass my output to xmlData which in turn will insert those outputs in the mysql table.

Well is your output a String? If so you'll want to modify the method to look like this:  
saveToDB(String output).....
Does this make sense?

Wait, sorry you said your table had two fields so you'd change the method constructor to something like:

saveToDB(String field1, String field2).....

...and change the sql to something like the following code:

String sql = "INSERT INTO tableName(field1, field2) VALUES('" + field1 + "', '" + field2 + "');";

Select allOpen in new window

Dear a_b

 i tried the code which u gave me. i m getting the following error

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
e : com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
You have Successfully created XML and data insert into the database.

Okay so I've taken your code and inserted some changes and assuming that your connection string is correct, this should be really close to working. See the code and note the Node values are being stored into Strings and then passed into the database.

private Connection con = null;
    private Statement stmt = null;

    public void connect() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = (Connection) DriverManager.getConnection("jdbc:mysql://server.url.com:3306/dbName", "userName", "password");
        } catch (Exception ex) {
            Logger.getLogger(WriteToDB.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void saveToDB(String value1, String value2) {
        try {
            stmt = (Statement) con.createStatement();
            String sql = "INSERT INTO tableName(field1, field2) VALUES('" + value1 + "', '" + value2 + "');";
            stmt.execute(sql);
        } catch (SQLException ex) {
            Logger.getLogger(WriteToDB.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void xmlReader() {
        try {
            File file = new File("/home/workspace/dom2.xml");
            DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
            DocumentBuilder db = dbf.newDocumentBuilder();
            Document doc = db.parse(file);
            doc.getDocumentElement().normalize();
            System.out.println("Root element " + doc.getDocumentElement().getNodeName());
            NodeList nodeLst = doc.getElementsByTagName("employee");
            System.out.println("Information of all employees");
            connect();
            for (int s = 0; s < nodeLst.getLength(); s++) {
                String fname, lname = null;
                Node fstNode = nodeLst.item(s);

                if (fstNode.getNodeType() == Node.ELEMENT_NODE) {

                    Element fstElmnt = (Element) fstNode;
                    NodeList fstNmElmntLst = fstElmnt.getElementsByTagName("firstname");
                    Element fstNmElmnt = (Element) fstNmElmntLst.item(0);
                    NodeList fstNm = fstNmElmnt.getChildNodes();
                    fname = ((Node) fstNm.item(0)).getNodeValue().toString();
                    NodeList lstNmElmntLst = fstElmnt.getElementsByTagName("lastname");
                    Element lstNmElmnt = (Element) lstNmElmntLst.item(0);
                    NodeList lstNm = lstNmElmnt.getChildNodes();
                    lname = ((Node) lstNm.item(0)).getNodeValue().toString();
                    saveToDB(fname, lname);
                    System.out.println(fname + ", " + lname + "\n");
                }

            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

Select allOpen in new window

hi patsmitty...

 thanks for the response. no main method in it???

hi patsmitty

 i figured it out and my code now looks like in the code area. when i run this program i get the following error.
any suggestions??

Root element company
Information of all employees
java.lang.NullPointerException
      at dom2.XMLReader.saveToDB(XMLReader.java:43)
      at dom2.XMLReader.xmlReader(XMLReader.java:77)
      at dom2.XMLReader.main(XMLReader.java:25)

public class XMLReader {
    Connection con = null;
    Statement stmt = null;
    
	public static void main(String args[]) 
	throws Exception{
		try{
		new XMLReader().xmlReader();
		}
		catch(Exception e){
		System.out.println(e.getMessage());
		}
	}
	
   public void connect() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = (Connection) DriverManager.getConnection("jdbc:mysql://localhost/feedback", "xxx", "xxx");
        } catch (Exception ex) {
            //Logger.getLogger(WriteToDB.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void saveToDB(String value1, String value2) {
        try {
            stmt = (Statement) con.createStatement();
            String sql = "INSERT INTO feedback.flname(fname, lname) VALUES('" + value1 + "', '" + value2 + "');";
            stmt.execute(sql);
        } catch (SQLException ex) {
            //Logger.getLogger(WriteToDB.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void xmlReader() {
        try {
            File file = new File("/home/workspace/dom2.xml");
            DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
            DocumentBuilder db = dbf.newDocumentBuilder();
            Document doc = db.parse(file);
            doc.getDocumentElement().normalize();
            System.out.println("Root element " + doc.getDocumentElement().getNodeName());
            NodeList nodeLst = doc.getElementsByTagName("employee");
            System.out.println("Information of all employees");
            connect();
            for (int s = 0; s < nodeLst.getLength(); s++) {
                String fname, lname = null;
                Node fstNode = nodeLst.item(s);

                if (fstNode.getNodeType() == Node.ELEMENT_NODE) {

                    Element fstElmnt = (Element) fstNode;
                    NodeList fstNmElmntLst = fstElmnt.getElementsByTagName("firstname");
                    Element fstNmElmnt = (Element) fstNmElmntLst.item(0);
                    NodeList fstNm = fstNmElmnt.getChildNodes();
                    fname = ((Node) fstNm.item(0)).getNodeValue().toString();
                    NodeList lstNmElmntLst = fstElmnt.getElementsByTagName("lastname");
                    Element lstNmElmnt = (Element) lstNmElmntLst.item(0);
                    NodeList lstNm = lstNmElmnt.getChildNodes();
                    lname = ((Node) lstNm.item(0)).getNodeValue().toString();
                    saveToDB(fname, lname);
                    System.out.println(fname + ", " + lname + "\n");
                }

            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    }

Select allOpen in new window

hi patsmitty

 thanks for ur response.. in the following line i m getting an error.. in the "WriteToDB"....

Logger.getLogger(WriteToDB.class.getName()).log(Level.SEVERE, null, ex);

do i have to create a class called WriteToDB ???

You need to import the mysql jdbc driver as a package to your class. Are you using some IDE like Netbeans to write your code or are you working straight from a command line to compile / run / test your code? Depending on which one you're using to program dictates the way you'll load the driver.  

i am using eclipse IDE and in the build path --> under library i added the external jar file which is nothing but the mysql jdbc driver...

dear patsmitty...

  the code is running perfectly.... only if i disable the following lines

Logger.getLogger(WriteToDB.class.getName()).log(Level.SEVERE, null, ex);

it says "WriteToDB cannot be resolved to a type" if i enable this line.. as it is in the catch block i disabled it.. it would be fine if i know why am i getting an error in this line...

Change
} catch (SQLException ex) {
            //Logger.getLogger(WriteToDB.class.getName()).log(Level.SEVERE, null, ex);
        }

to

} catch (SQLException ex) {
            ex.printStackTrace();
        }


and copy and paste the error message here. This will give you more of an idea of what the exact error is.