I have been working on this piece of code to parse my XML file into a MySQL database. Every tutorial I have seen online parses individually like this code I've been working on. From here I would like to loop it in a way which instead of adding each field individually, if iterate it through the XML file and define how many fields need to be created inside of the table. Does anyone have any such tutorials they are aware of or possibly a short example how I could get started for this? Example I would not like to re-edit my program each time i decide to add another characteristic for a customer.
package xSQLTEST;
import java.io.*;
import java.sql.*;
import org.w3c.dom.*;
import javax.xml.parsers.*;
public class InsertXMLData {
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/brandonDB",
"root",
"Absolute!");
Statement st = con.createStatement();
DocumentBuilderFactory docBuilderFactory = DocumentBuilderFactory.newInstance();
DocumentBuilder docBuilder = docBuilderFactory.newDocumentBuilder();
Document doc = docBuilder.parse(new File("C:\\testXML.xml"));
doc.getDocumentElement().normalize();
System.out.println("Root element of the doc is "
+ doc.getDocumentElement().getNodeName());
NodeList listOfPersons = doc.getElementsByTagName("row");
for (int s = 0; s < listOfPersons.getLength(); s++) {
Node firstPersonNode = listOfPersons.item(s);
if (firstPersonNode.getNodeType() == Node.ELEMENT_NODE) {
Element firstPersonElement = (Element) firstPersonNode;
// -------------------------------------------------------------------------------------
NodeList CustIdList = firstPersonElement.getElementsByTagName("CustId");
Element CustIdElement = (Element) CustIdList.item(0);
NodeList textIDList = CustIdElement.getChildNodes();
String CustId = ((Node) textIDList.item(0)).getNodeValue()
.trim();
NodeList NameList = firstPersonElement.getElementsByTagName("Name");
Element NameElement = (Element) NameList.item(0);
NodeList textNMList = NameElement.getChildNodes();
String Name = ((Node) textNMList.item(0)).getNodeValue().trim();
NodeList CityList = firstPersonElement.getElementsByTagName("City");
Element CityElement = (Element) CityList.item(0);
NodeList textCTList = CityElement.getChildNodes();
String City = ((Node) textCTList.item(0)).getNodeValue().trim();
int i = st.executeUpdate("insert into customer(CustId,Name,City) values('"
+ CustId
+ "','"
+ Name
+ "','"
+ City
+ "')");
}
}
System.out.println("Data is successfully inserted!");
}
catch (Exception err) {
System.out.println(" " + err.getMessage());
}
}
}
XML FILE that I am using but would like to possibly add more details about the customer in the future.
<Customers>
<row>
<CustId>1</CustId>
<Name>Woodworks</Name>
<City>Baltimore</City>
</row>
<row>
<CustId>2</CustId>
<Name>Software Solutions</Name>
<City>Boston</City>
</row>
<row>
<CustId>3</CustId>
<Name>Food Fantasies</Name>
<City>New York</City>
</row>
</Customers>
BUFFER READER INSERTED AND WORKING:
BufferedReader br = new BufferedReader(new FileReader(new File("c:\\testXML.xml")));
String line;
StringBuilder sb = new StringBuilder();
while((line=br.readLine())!= null){
sb.append(line.trim());
}
String strFileContent = sb.toString();
Using Jsoup, you can do this:
private static void parseXml(String xml) {
Document doc = Jsoup.parse(xml);
StringBuilder queryBuilder;
StringBuilder columnNames;
StringBuilder values;
for (Element row : doc.select("row")) {
// Start the query
queryBuilder = new StringBuilder("insert into customer(");
columnNames = new StringBuilder();
values = new StringBuilder();
for (int x = 0; x < row.children().size(); x++) {
// Append the column name and it's value
columnNames.append(row.children().get(x).tagName());
values.append(row.children().get(x).text());
if (x != row.children().size() - 1) {
// If this is not the last item, append a comma
columnNames.append(",");
values.append(",");
}
else {
// Otherwise, add the closing paranthesis
columnNames.append(")");
values.append(")");
}
}
// Add the column names and values to the query
queryBuilder.append(columnNames);
queryBuilder.append(" values(");
queryBuilder.append(values);
// Print the query
System.out.println(queryBuilder);
}
}
Output:
insert into customer(custid,name,city) values(1,Woodworks,Baltimore)
insert into customer(custid,name,city) values(2,Software Solutions,Boston)
insert into customer(custid,name,city) values(3,Food Fantasies,New York)
This allows us to add as many new columns or rows as we wish. For example, I added a few bits to your original XML, and without modification to the java code, the output was:
insert into customer(custid,name,city,age) values(1,Woodworks,Baltimore,21)
insert into customer(custid,name,city,age) values(2,Software Solutions,Boston,21)
insert into customer(custid,name,city,age) values(3,Food Fantasies,New York,21)
insert into customer(custid,name,city,age) values(4,Crazy Hat Store,London,21)
EDIT :
To import the XML from file, the method would look like this:
private static String loadXml(String filePath) {
try {
StringBuilder xml = new StringBuilder();
Scanner scanner = new Scanner(new File(filePath)); // i.e. C:\\testXML.xml
while (scanner.hasNextLine()) {
xml.append(scanner.nextLine());
}
return xml.toString();
}
catch (FileNotFoundException e) {
e.printStackTrace();
}
return null;
}
And then my main method looked like this:
public static void main(String[] args) {
parseXml(loadXml(filePath));
}