MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for July, 2010

MySQL, XSLT & Xalan Queries

without comments

I posted how to connect to an Oracle database from an XSLT library file back in August 2008. It’s an event driven XML approach that can support web page development. One of my students wanted to do the same thing against MySQL. He quickly saw that it was simply a matter of the switching the JDBC library. He’s got the whole idea bundled on his blog here.

XSLT Library File to Query MySQL Database ↓

Unfold this if you’d like to see the XLST code he’s posted on his blog for MySQL. You’ll find that only line #10 (below) required a change. If you’re new to XSLT, you may find Doug Tidwell’s XSLT, 2nd Edition book very helpful. 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:sql="org.apache.xalan.lib.sql.XConnection"
  extension-element-prefixes="sql">
<xsl:output method="html" />
<xsl:template match="/">
<xsl:variable
  name="movies"
  select="sql:new('com.mysql.jdbc.Driver','jdbc:mysql:///storedb','student','student')" />
<xsl:variable name="streaming" select="sql:disableStreamingMode($movies)" />
<xsl:variable
  name="queryResults"
  select="sql:query($movies,'SELECT i.item_title, i.item_asin, i.item_release_date FROM storedb.item i')" />
<html>
<head><title>MySQL Result Set</title></head>
<body style="font-family: sans-serif;">
<table border="1" cellpadding="5">
  <tr>
    <xsl:for-each select="$queryResults/sql/metadata/column-header">
      <th><xsl:value-of select="@column-label" /></th>
    </xsl:for-each>
  </tr>
  <xsl:apply-templates select="$queryResults/sql/row-set/row" />
</table>
</body>
</html>
<xsl:value-of select="sql:close($movies)" />
</xsl:template>
<xsl:template match="row">
  <tr><xsl:apply-templates select="col" /></tr>
</xsl:template>
<xsl:template match="col">
  <td><xsl:value-of select="text()" /></td>
</xsl:template>
</xsl:stylesheet>

Hope this helps those looking for a solution.

Written by maclochlainn

July 9th, 2010 at 10:55 pm

Posted in Java,MySQL,xml,xslt