Java XML MYSQL

gobicse
gobicse used Ask the Experts™
on
hi there

 i wrote a small java program which reads an xml file and prints the data in the console. now i want to save this data in a mysql table.

can anyone tell how to do it or any references?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
a_b
Top Expert 2009

Commented:
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?
Bruce SmithSoftware Engineer II

Commented:
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);
        }
    }

Open in new window

Author

Commented:
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();
  }
 }
}

Open in new window

dom2.xml
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

a_b
Top Expert 2009

Commented:

Author

Commented:
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.
Bruce SmithSoftware Engineer II

Commented:
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?
Bruce SmithSoftware Engineer II

Commented:
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 + "');";

Open in new window

Author

Commented:
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.
Bruce SmithSoftware Engineer II

Commented:
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();
        }
    }

Open in new window

Author

Commented:
hi patsmitty...

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

Author

Commented:


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();
        }
    }
    }

Open in new window

Software Engineer II
Commented:
In your code that you supplied above you already have the main method. I've just given you other methods to use. Simply copy and paste them into your original class(XMLReader) and call the methods from your main method. See attached...

XMLReader.java

Author

Commented:
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 ???
Bruce SmithSoftware Engineer II

Commented:
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.  

Author

Commented:
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...

Author

Commented:
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...
Bruce SmithSoftware Engineer II

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial