JDBC, DOM und XSLT Utilities aus alten Zeiten...

DataDomBuilder.java

/*
 * Created on 26.05.2003
 *
 * To change the template for this generated file go to
 * Window>Preferences>Java>Code Generation>Code and Comments
 * $Id: DataDomBuilder.java,v 1.21 2004/02/05 09:27:55 rschumm Exp $
 */
package ch.zhwin.jdbc2xml;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.TransformerException;
import org.w3c.dom.Document;
import org.w3c.dom.Element;

/**
 * Fabriziert aus einer Datenbankabfrage ein DOM <p>
 * 
 * Benützung der Klasse: <br>
 * <blockquote><pre>
 * DataDomBuilder builder = new DataDomBuilder(...);
 * builder.executeQuery("select..."); 
 * Document dom = builder.getDataDom(); 
 * String xml = builder.getDataDomAsString();  
 * </pre></blockquote>
 * 
 * @author rschumm
 *
 * jdbc2xml, $Date: 2004/02/05 09:27:55 $
 * (c) 2003 ZHW
 */
public class DataDomBuilder {
	private Connection conn;
	private Document dataDoc = null;

	private ResultSet res;
	private String dbUrl;
	private String user;
	private String password;
	private DocumentBuilder builder;
	private String driver;

	/**
	 * Konstruktor für Benutzung mit einer allgemeinen Datenbank.  
	 * @param driver Der fully qualified Classname des Datenank JDBC-Treibers. 
	 * @param dbUrl Die JDBC-URL. 
	 * @param user Der Datenbankuser bzw. leer
	 * @param password Das dazugehörige Passwort bzw. leer 
	 * @throws ParserConfigurationException
	 */
	public DataDomBuilder(String driver, String dbUrl, String user, String password)
		throws ParserConfigurationException {
		
		this.dbUrl = dbUrl;
		this.user = user;
		this.password = password;
		this.driver = driver;
		
		//DocumentBuilderFactory liefert einen neuen DocumentBuilder: 
		//BEGINN Studenten-Code
		DocumentBuilderFactory fact = DocumentBuilderFactory.newInstance();
		builder = fact.newDocumentBuilder();
//		ENDE Studenten-Code
	}
	
	/**
	 * Konstruktor für Benutzung mit der Beispiels-Access-Datenbank.  
	 */
	public DataDomBuilder() throws ParserConfigurationException {
		this("sun.jdbc.odbc.JdbcOdbcDriver", "jdbc:odbc:Studenten", "", "");
	}
	
	
	/**
	 * Führt die Abfrage durch. 
	 * @param query Die SQL Abfrage. 
	 * @throws ClassNotFoundException Wenn die JDCB-Treiber nicht gefunden wurden. 
	 * @throws SQLException
	 */
	public void executeQuery(String query) throws SQLException, ClassNotFoundException{
		
		//stellt das dataDoc her:
		//BEGINN Studenten-Code
		dataDoc = builder.newDocument();
		Element dataRoot = dataDoc.createElement("rowset");
//		ENDE Studenten-Code

		//Lädt den JDBC-Treiber:
		Class.forName(driver);
		
		try {
			//Holt die Daten aus der Bank und verarbeitet sie:

			conn = DriverManager.getConnection(dbUrl, user, password);
			Statement stat = conn.createStatement();
			
//			BEGINN Studenten-Code
			res = stat.executeQuery(query);

			//Beginn der Datenverarbeitung: 
			ResultSetMetaData meta = res.getMetaData();
			int spalten = meta.getColumnCount();
			int rownr = 0;
			String s = null;
			while (res.next()) {
				//Fuer jede Zeile: 
				Element row = dataDoc.createElement("row");
				rownr++;
				row.setAttribute("no", "" + rownr);

				for (int i = 1; i <= spalten; i++) {
					//Fuer jede Spalte: 
					Element datenspalte =
						dataDoc.createElement(
							meta.getColumnName(i).toLowerCase());
					//Hack fuer Bug in Access-ODBC-JDBC
					//liefert bei leeren Results null. 
					s = res.getString(i);
					if (s == null)
						s = "";
					datenspalte.appendChild(dataDoc.createTextNode(s));
					row.appendChild(datenspalte);
				}

				dataRoot.appendChild(row);
			}

			dataDoc.appendChild(dataRoot);
			
//			ENDE Studenten-Code
			
		} catch (SQLException e) {
			throw new SQLException("Problem mit JDBC: " + e.toString());

		} finally {
			try {
				if (conn != null) conn.close();
			} catch (Exception e2) {
				throw new SQLException(
					"Problem beim Schliessen der SQL-Verbindung: "
						+ e2.toString());
			}
		}
		
	

	}


	/**
	 * @return Liefert das Ergebnis der Abfrage als DOM. 
	 */
	public Document getDataDom(){
		return dataDoc;
	}

	/**
	 * @return Liefert das Ergebnis der Abfrage als (XML-)String. 
	 * @throws TransformerException
	 */
	public String getDataDomAsString() throws TransformerException {
		return XSLTHelper.domToString(dataDoc);
	}

	/**
	 * Zum Testen. 
	 * @param args
	 * @throws Exception
	 */
	public static void main(String[] args) throws Exception {
		DataDomBuilder b =
			new DataDomBuilder();
		b.executeQuery("select * from studenten");
		
		System.out.println(b.getDataDomAsString());
		
	}


}

XSLTHelper.java

/*
 * Projekt svg
 * Erstellt: 30.05.2003
 * Autor: rschumm
 *
 * (c) ZHW, 2003
 * $Id: XSLTHelper.java,v 1.4 2004/02/09 14:00:21 rschumm Exp $
 */
package ch.zhwin.jdbc2xml;

import java.io.File;
import java.io.IOException;
import java.io.StringWriter;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Source;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerConfigurationException;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMResult;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import javax.xml.transform.stream.StreamSource;

import org.w3c.dom.Document;
import org.xml.sax.SAXException;

/**
 * 
 * XSLTDomBuilder <br>
 * Hilfsklasse für XSLT-Sachen, im Prinzip ein Wrapper
 * zur einfacheren Bedienung des XSLT-Transformers. <p>
 * 
 * Benützung der Klasse: <br>
 * <blockquote><pre>
 * XSLTHelper helper = new XSLTHelper(); 
 * helper.initTransformer(xsltfile); 
 * helper.setParamter(key, value); 
 * String result1 = helper.transformToString(dom);
 * String result2 = helper.transformToString(xmlfile);  
 * </pre></blockquote>
 * 
 * @author rschumm
 * jdbc2xml, 30.05.2003
 * (c) 2003 ZHW
 */
public class XSLTHelper {

	private String output;

	private TransformerFactory tFactory;
	private Transformer transformer;
	private StringWriter stringWriter = null;
	private StreamResult result;
	
	public XSLTHelper() {	
		tFactory = TransformerFactory.newInstance();
	}
	
	/**
	 * Initialisiert den Transformer mit dem gewünschte XSLT File.
	 * @param xslt Das XSLT File fuer die Transformation
	 */
	public void initTransformer(File xslt) throws TransformerConfigurationException{
		
		//BEGINN Studenten-Code
		
		//Setzt das XSLT-File fuer den Transformer. 
		Source xsltSource = new StreamSource(xslt); 

		// Create a string writer
		stringWriter = new StringWriter();

		// Create the result stream for the transform
		result = new StreamResult(stringWriter);

		// Create a Transformer to serialize the document
		transformer = tFactory.newTransformer(xsltSource);
		
		//ENDE Studenten-Code
		
	}

	
	/**
	 * Setzt Parameter fuer die Transformation, die an das Stylesheet uebergeben werden.<br>
	 * Muss vor <code>transformToString(...)</code> aufgerufen werden. 
	 * @param key Der Name des Parameters im Stylesheet
	 * @param value Das Objekt fuer die Uebergabe, ueblicherweise ein String. 
	 */
	public void setParameter(String key, Object value){
		transformer.setParameter(key, value); 
	}


	/**
	 * Führt die Transformation auf das DOM aus.
	 * 
	 * @param dom Das zu transformierende DOM
	 * @return Das Resultat als String. 
	 * @throws TransformerException
	 */
	public String transformToString(Document dom) throws TransformerException {
		
		//BEGINN Studenten-Code
		
		//Create dom source for the document
		DOMSource domSource = new DOMSource(dom);
		 
		transformer.setOutputProperty("indent", "yes");
		
		//Hier wird transformiert:
		// Transform the document to the result stream
		transformer.transform(domSource, result);
		
		//ENDE Studenten-Code
		
		return stringWriter.toString();
	}
	
	/**
	 * Führt die Transformation auf das XML File aus.
	 * 
	 * @param xml Das zu transformierende XML File
	 * @return Das Resultat als String. 
	 * @throws TransformerException
	 */
	public String transformToString(File xml) throws TransformerException, SAXException, IOException, ParserConfigurationException{
		DocumentBuilderFactory fact = DocumentBuilderFactory.newInstance();
		DocumentBuilder docbuild = fact.newDocumentBuilder();
		return transformToString(docbuild.parse(xml));
	}

	

	
	/**
	 * Static Method that converts the DOM to string for display
	 * @param doc org.w3c.dom.Document
	 * @return String
	 */
	public static String domToString(Document doc)
	throws TransformerException {

		// Create dom source for the document
		DOMSource domSource = new DOMSource(doc);

		// Create a string writer
		StringWriter stringWriter = new StringWriter();

		// Create the result stream for the transform
		StreamResult result = new StreamResult(stringWriter);

		// Create a Transformer to serialize the document
		TransformerFactory tFactory = TransformerFactory.newInstance();
		Transformer transformer = tFactory.newTransformer();
		transformer.setOutputProperty(OutputKeys.METHOD, "xml");
		transformer.setOutputProperty(OutputKeys.INDENT, "yes");
		

		// Transform the document to the result stream
		transformer.transform(domSource, result);
		return stringWriter.toString();
	}
	
	/**
	 * Static Method that saves the DOM into a File
	 * @param doc org.w3c.dom.Document
	 * @param outfile the File to save into
	 * @return String
	 */
	public static void domToFile(Document doc, File outfile)
	throws TransformerException {

		// Create dom source for the document
		DOMSource domSource = new DOMSource(doc);



		// Create the result stream for the transform
		StreamResult result = new StreamResult(outfile);

		// Create a Transformer to serialize the document
		TransformerFactory tFactory = TransformerFactory.newInstance();
		Transformer transformer = tFactory.newTransformer();
		transformer.setOutputProperty(OutputKeys.METHOD, "xml");
		transformer.setOutputProperty(OutputKeys.INDENT, "yes");
		

		// Transform the document to the result stream
		transformer.transform(domSource, result);

	}
	
	/**
	 * Führt die Transformation auf das DOM aus.
	 * 
	 * @param dom Das zu transformierende DOM
	 * @param file Das File für das Resultat 
	 * @throws TransformerException
	 */
	public void transformToFile(Document dom, File file) throws TransformerException {
		//Create dom source for the document
		DOMSource domSource = new DOMSource(dom);
		
		// Create the result stream for the transform
		result = new StreamResult(file); 
		
		
		transformer.setOutputProperty("indent", "yes");
		
		//Hier wird transformiert:
		// Transform the document to the result stream
		transformer.transform(domSource, result);

	}
	
	/**
	 * Führt die Transformation auf das DOM aus.
	 * 
	 * @param indom Das zu transformierende DOM
	 * @param outdom Das DOM für das Resultat 
	 * @throws TransformerException
	 */
	public void transformToDOM(Document dom, Document outdom) throws TransformerException {
		transformer.transform(new DOMSource(dom),  new DOMResult(outdom));
	}
	
	/**
	 * Führt die Transformation auf das XML File aus.
	 * 
	 * @param xml Das zu transformierende XML File
	 * @param outfile Das File für das Resultat 
	 * @throws TransformerException
	 */
	public void transformToFile(File xml, File outfile) throws TransformerException, SAXException, IOException, ParserConfigurationException{
		DocumentBuilderFactory fact = DocumentBuilderFactory.newInstance();
		DocumentBuilder docbuild = fact.newDocumentBuilder();
		transformToFile(docbuild.parse(xml), outfile);
	}
	
	
	/**
	 * Zum Testen. 
	 * @param args
	 * @throws Exception
	 */
	public static void main(String[] args) throws Exception {
		DataDomBuilder b =
			new DataDomBuilder();
		b.executeQuery("select * from studenten");
		
		System.out.println(b.getDataDomAsString());

		File xslfile =new File(b.getClass().getResource("mapping.xsl").getFile());
		
		XSLTHelper x = new XSLTHelper();
		x.initTransformer(xslfile);
		
		System.out.println(x.transformToString(b.getDataDom()));
	}

}