Skip to content

Instantly share code, notes, and snippets.

@rschumm
Created May 4, 2012 22:29
Show Gist options
  • Save rschumm/2598128 to your computer and use it in GitHub Desktop.
Save rschumm/2598128 to your computer and use it in GitHub Desktop.
JDBC, DOM und XSLT Utilities aus alten Zeiten...
/*
* 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());
}
}
/*
* 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()));
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment