package xml;
import connexion.*;
import java.sql.*;
import org.w3c.dom.*;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
public class extract extends Object {
static Connection bdc;
public static void main (String args[]){
//Create the Document object
Document mapDoc = null;
//Define a new Document object
Document dataDoc = null;
//Create the new Document
Document newDoc = null;
try {
//Create the DocumentBuilderFactory
DocumentBuilderFactory dbfactory = DocumentBuilderFactory.newInstance();
//Create the DocumentBuilder
DocumentBuilder docbuilder = dbfactory.newDocumentBuilder();
//Parse the file to create the Document
mapDoc = docbuilder.parse("mapping.xml" );
//Instantiate a new Document object
dataDoc = docbuilder.newDocument();
//Instantiate the new Document
newDoc = docbuilder.newDocument();
} catch (Exception e) {
System.out.println("Problem creating document: "+e.getMessage());
}
Element mapRoot = mapDoc.getDocumentElement();
//Retrieve the (only) data element and cast it to Element
Node dataNode = mapRoot.getElementsByTagName("data" ).item(0);
Element dataElement = (Element)dataNode;
System.out.println(Element.TEXT_NODE);
//Retrieve the sql statement
String sql = dataElement.getAttribute("sql" );
//Output the SQL statement
System.out.println(sql);
ResultSetMetaData resultmetadata = null;
//Create a new element called "data"
Element dataRoot = dataDoc.createElement("data" );
try{
connexion maConnexion = new connexion();
Class.forName("org.hsqldb.jdbcDriver" );
String url = maConnexion.getConnexion();
String user = maConnexion.getUser();
String pass = maConnexion.getPass();
bdc=DriverManager.getConnection(url,user,pass);
Statement monState = bdc.createStatement();
ResultSet mesRes = monState.executeQuery("select * from client" );
resultmetadata = mesRes.getMetaData();
int numCols = resultmetadata.getColumnCount();
while (mesRes.next()) {
//For each row of data
//Create a new element called "row"
Element rowEl = dataDoc.createElement("row" );
for (int i=1; i <= numCols; i++) {
//For each column, retrieve the name and data
String colName = resultmetadata.getColumnName(i);
System.out.println(colName);
String colVal = mesRes.getString(i);
System.out.println(colVal);
//If there was no data, add "and up"
if (mesRes.wasNull()) {
colVal = "and up";
}
//Create a new element with the same name as the column
Element dataEl = dataDoc.createElement(colName);
//Add the data to the new element
dataEl.appendChild(dataDoc.createTextNode(colVal));
//Add the new element to the row
rowEl.appendChild(dataEl);
}
//Add the row to the root element
dataRoot.appendChild(rowEl);
}
}
catch (Exception e) {
System.out.println("SQL Error: "+e.getMessage());
} finally {
System.out.println("Closing connections..." );
try {
bdc.close();
} catch (SQLException e) {
System.out.println("Can't close connection." );
}
}
//Add the root element to the document
dataDoc.appendChild(dataRoot);
System.out.println("Test1" );
//Retrieve the root element (also called "root" )
Element newRootInfo = (Element)mapRoot.getElementsByTagName("root" ).item(0);
System.out.println("Test2" );
//Retrieve the root and row information
String newRootName = newRootInfo.getAttribute("name" );
System.out.println("Test3" );
String newRowName = newRootInfo.getAttribute("rowName" );
System.out.println("Test4" );
//Retrieve information on elements to be built in the new document
NodeList newNodesMap = mapRoot.getElementsByTagName("element" );
System.out.println("Test5" );
//Create the final root element with the name from the mapping file
Element newRootElement = newDoc.createElement(newRootName);
System.out.println("Test6" );
//Retrieve all rows in the old document
NodeList oldRows = dataRoot.getElementsByTagName("row" );
System.out.println("Test7" );
for (int i=0; i < oldRows.getLength(); i++){
//Retrieve each row in turn
Element thisRow = (Element)oldRows.item(i);
//Create the new row
Element newRow = newDoc.createElement(newRowName);
for (int j=0; j < newNodesMap.getLength(); j++) {
//For each node in the new mapping, retrieve the information
//First the new information...
Element thisElement = (Element)newNodesMap.item(j);
System.out.println("Test6" );
String newElementName = thisElement.getAttribute("name" );
//Then the old information
Element oldElement = (Element)thisElement.getElementsByTagName("content" ).item(0);
System.out.println("Test7" );
String oldField = oldElement.getFirstChild().getNodeValue();
//Get the original values based on the mapping information
Element oldValueElement = (Element)thisRow.getElementsByTagName(oldField).item(0);
System.out.println("Test8" );
String oldValue = oldValueElement.getFirstChild().getNodeValue();
System.out.println("Test9" );
//Create the new element
Element newElement = newDoc.createElement(newElementName);
newElement.appendChild(newDoc.createTextNode(oldValue));
//Retrieve list of new elements
NodeList newAttributes = thisElement.getElementsByTagName("attribute" );
System.out.println("Test10" );
for (int k=0; k < newAttributes.getLength(); k++) {
//For each new attribute
//Get the mapping information
Element thisAttribute = (Element)newAttributes.item(k);
String oldAttributeField = thisAttribute.getFirstChild().getNodeValue();
String newAttributeName = thisAttribute.getAttribute("name" );
//Get the original value
oldValueElement = (Element)thisRow.getElementsByTagName(oldAttributeField).item(0);
String oldAttributeValue = oldValueElement.getFirstChild().getNodeValue();
//Create the new attribute
newElement.setAttribute(newAttributeName, oldAttributeValue);
}
System.out.println("Test11" );
//Add the new element to the new row
newRow.appendChild(newElement);
}
//Add the new row to the root
newRootElement.appendChild(newRow);
}
//Add the new root to the document
newDoc.appendChild(newRootElement);
}
}
