The following code snippet demostrates how Java can be used to read database tables and generate a valid xml file out of it. The example uses a DB2 database but should be ported to other databases with small effort. the data is provied by the sample database provided by DB2. Used techniques are JDBC and DOM Parser.
import java.io.FileOutputStream;
import java.sql.*;
import org.apache.crimson.tree.XmlDocument;
import org.w3c.dom.*;
public class DB2Mitarbeiter {
public static void main(String[] args) {
String url = "jdbc:db2://127.0.0.1:50000/SAMPLE";
String SQLString=null;
String lastDep = null;
Element emp = null;
Node lastname, firstname = null;
XmlDocument document = new XmlDocument();
Element dep = document.createElement("department");
Node company = document.createElement("company");
ResultSet rs = null;
// Load Type4 DB Driver
try {
Class.forName("com.ibm.db2.jcc.DB2Driver");
} catch (ClassNotFoundException e) {
System.out.println("ClassNotFoundException");
e.printStackTrace();
}
System.out.println("DB Type 4 Driver loaded.");
// DB connect with url,login and pass
try {
FileOutputStream fo = new FileOutputStream("test.xml");
Connection con = DriverManager.getConnection(url,"username","password");
Statement stmt = con.createStatement();
System.out.println("DB connection established.");
SQLString = "SELECT * FROM DEPARTMENT,EMPLOYEE WHERE EMPLOYEE.WORKDEPT = DEPARTMENT.DEPTNO order by DEPARTMENT.DEPTNO";
//SQLString = "SELECT * FROM DEPARTMENT LEFT JOIN EMPLOYEE ON EMPLOYEE.WORKDEPT = DEPARTMENT.DEPTNO";
rs = stmt.executeQuery(SQLString);
while(rs.next()){
//System.out.println("rs: " + rs.getString("DEPTNO") + " - lastdep: " + lastDep);
if (lastDep == null) {
dep.setAttribute("depid",rs.getString("DEPTNO"));
lastDep = rs.getString("DEPTNO");
emp = document.createElement("employee");
emp.setAttribute("empid", rs.getString("EMPNO"));
lastname = emp.appendChild(document.createElement("lastname"));
lastname.appendChild(document.createTextNode(rs.getString("LASTNAME")));
firstname = emp.appendChild(document.createElement("firstname"));
firstname.appendChild(document.createTextNode(rs.getString("FIRSTNME")));
dep.appendChild(emp);
}
else if (lastDep.equals(rs.getString("DEPTNO"))) {
emp = document.createElement("employee");
emp.setAttribute("empid", rs.getString("EMPNO"));
lastname = emp.appendChild(document.createElement("lastname"));
lastname.appendChild(document.createTextNode(rs.getString("LASTNAME")));
firstname = emp.appendChild(document.createElement("firstname"));
firstname.appendChild(document.createTextNode(rs.getString("FIRSTNME")));
dep.appendChild(emp);
}
else {
company.appendChild(dep);
dep = document.createElement("department");
dep.setAttribute("depid",rs.getString("DEPTNO"));
emp = document.createElement("employee");
emp.setAttribute("empid", rs.getString("EMPNO"));
lastname = emp.appendChild(document.createElement("lastname"));
lastname.appendChild(document.createTextNode(rs.getString("LASTNAME")));
firstname = emp.appendChild(document.createElement("firstname"));
firstname.appendChild(document.createTextNode(rs.getString("FIRSTNME")));
dep.appendChild(emp);
}
lastDep = rs.getString("DEPTNO");
}
document.appendChild(company);
document.write(fo);
fo.close();
} catch (Exception e1) {
System.out.println("Fehler");
e1.printStackTrace();
}
}
}
