Creating the Database Connection

A database connection is required for an XSQL query. To create a database connection in JDeveloper, select the Connections>Database node in the Connections Navigator as shown in Figure 2.

A Connection Wizard is then displayed, as shown in Figure 3.

In the Connection Wizard specify a Connection Name and a Connection Type and click the Next button. In the next frame displayed, specify a Username and Password and click the Next button.

In the subsequent Connection frame specify a JDBC Driver, Host Name, JDBC Port, and database SID and click the Next button as shown in Figure 4.

In the subsequent Test frame, click the Test Connection button to test the JDBC connection (see Figure 5). Click the Finish button to configure the database connection.

As Figure 6 shows, you'll see that a connection node has been added to the Connections>Database node in the Applications Navigator.

A connection with a database may also be created by modifying the <JDeveloper>/jdev/system9.0.5.1.1605/XSQLConfig.xml file. <JDeveloper> is the directory in which JDeveloper 10g is installed; the XSQLConfig.xml file may be set to have read privileges by the user running the application server. In the <connectiondefs> element in XSQLConfig.xml add a <connection> element specifying the connection to be created. Create a connection with the SCOTT schema.

<connection name="dbConnection">
      <username>SCOTT</username>
      <password></password>
      <dburl>jdbc:oracle:thin:@<HOST>:<PORT>:<SID></dburl>
      <driver>oracle.jdbc.driver.OracleDriver</driver>
</connection>

• Attribute name is the connection name
• Element username is the username used to log in to the database
• Element password is the password used to log in to the database
• Element dburl is the URL of the database
• Element driver is the JDBC driver used to connect to the database.
• <HOST> is the Oracle 10g Production database host.
• <PORT> is the database port.
• <SID> is the database SID.

Next, copy XSQLConfig.xml into the project source directory in JDeveloper. We will use the connection dbConnection configured in the XSQLConfig.xml file to specify the value of the connection attribute in the <page> tag of an XSQL page.

Next we'll add <xsql:query></xsql:query> tags to the XSQL page to process our XSQL queries. The application of XSQL queries may require the use of bind variables, the variables in a SQL statement. Bind variables can be set using the values of URL parameters, session variables, cookie values, or page parameters. In our example, we'll set XSQL query bind variables with page parameters.

Bind variables, specified with the bind-params attribute in the <xsql:query> tag, are used in the SQL statement within the <xsql:query> tag. The bind variables are represented with '?' in a SQL statement. The value of the bind parameters is specified with the <xsql:set-page-params/> tag.

<page xmlns:xsql="urn:oracle-xsql" connection="dbConnection">
<xsql:set-page-param name="JOURNAL" value="Oracle Magazine"/>
<xsql:set-page-param name="PUBLISHER" value="Oracle Publishing"/>
<xsql:query xmlns:xsql="urn:oracle-xsql"
      bind-params="JOURNAL  PUBLISHER">
          select JOURNAL, PUBLISHER, EDITION, 
               TITLE, AUTHOR from OracleCatalog WHERE JOURNAL=? AND PUBLISHER=?
</xsql:query>
</page>
 

The <xsql:query></xsql:query> tags may be added from the XSQL Component Pallete, as illustrated in Figure 8.

The attribute connection in the page tag specifies the connection used to query the database. Our XSQL page, queryDb.xsql, is shown in Figure 9.

To run queryDb.xsql, right-click the queryDb.xsql node in the Applications Navigator and select Run, as shown in Figure 10.

The result of the XSQL query is an XML document; the XSQL page queryDb.xsql displays the XML output of the XSQL query in the default browser. The result of our XSQL query is illustrated below.

- <page>
 - <ROWSET>
  - <ROW num="1">
    <JOURNAL>Oracle Magazine</JOURNAL>
    <PUBLISHER>Oracle Publishing</PUBLISHER>
    <EDITION>November-December 2003</EDITION>
    <TITLE>Servlets and JSP Step Up</Title>
    <AUTHOR>Budi Kurniawan</AUTHOR>
    </ROW>

 - <ROW num="2">
    <JOURNAL>Oracle Magazine</JOURNAL>
    <PUBLISHER>Oracle Publishing</PUBLISHER>
    <EDITION>September-October 2003</EDITION>
    <TITLE>Parsing XML Efficiently</Title>
    <AUTHOR>Jilua Basu</AUTHOR>
    </ROW>
 </ROWSET>
</page>

Next we'll examine the different <xsql:query> tag attributes used in a XSQL query.

Setting XSQL Query Attributes

The <xsql:query> tag provides XSQL attributes not only to modify the data retrieved with the XSQL query but to modify the XML document produced with the XSQL query. The different XSQL attributes and their applications are listed in Table 1.

As described earlier, a <xsql:query> tag with the attributes set may be added to the queryDb.xsql with the Query component in the XSQL Component Palette. First, select the Query Component in the XSQL Component Palette. A Query frame is displayed, as illustrated in Figure 11. Next, specify values for the different <xsql:query> attributes.

Now add an SQL query in the <xsql:query/> tag. The queryDb.xsql XSQL page with the query attributes set is illustrated in Figure 12.

Run queryDb.xsql by right-clicking the queryDb.xsql node in the Applications Navigator frame and selecting Run. The result set of the XSQL query is displayed in your default browser as illustrated below.

- <page>
 - <Catalog>
  - <Journal-Edition Id="2">
    <JOURNAL>Oracle Magazine</JOURNAL>
    <PUBLISHER>Oracle Publishing</PUBLISHER>
    <EDITION>September-October 2003</EDITION>
    <TITLE>Parsing XML Efficiently</Title>
    <AUTHOR>Julie Basu</AUTHOR>
   </Journal-Edition>
  </Catalog>
</page>

In the following section, we'll discuss XSLT transformation of the output from an XSQL query.

Transforming Output with XSQL

Now we'll create an XSLT file to transform the XSQL output in JDeveloper. Select File>New and select Categories:General>XML in the Categories frame. Select XSLT Stylesheet from the items listed as shown in Figure 13.

Specify Directory Name and an XSLT File Name in the frame displayed. Now, modify the XSLT file in JDeveloper. The XSLT used to generate an HTML table from the XSQL query XML output is illustrated below.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output encoding="ISO-8859-1"  method="text/html" />
<xsl:template match="/technology/page">
<html>
  <head>
    <title>Oracle Catalog</title>
  </head>
  <body>
    <table border="1" cellspacing="0">
        <tr>
         <th>Journal</th>
         <th>Publisher</th>
         <th>Edition</th>
         <th>Title</th>
         <th>Author</th>
        </tr>
      <xsl:for-each select="ROWSET/ROW">
        <tr>
         <td><xsl:value-of select="JOURNAL"/></td>
         <td><xsl:value-of select="PUBLISHER"/></td>
         <td><xsl:value-of select="EDITION"/></td>
         <td><xsl:value-of select="TITLE"/></td>
         <td><xsl:value-of select="AUTHOR"/></td>
        </tr>
      </xsl:for-each>
    </table>
  </body>
</html>
</xsl:template>
</xsl:stylesheet>

The XSLT file itself, OracleCatalog.xsl, is illustrated in Figure 14.

Now add the following reference to specify that OracleCatalog.xsl will transform the XSQL query output in the XSQL file (queryDb.xsql) used to query the database:

<?xml-stylesheet type="text/xsl" href="OracleCatalog.xsl"?>

The resulting XSQL file is illustrated in Fig 15.

Finally, run queryDb.xsql in JDeveloper as illustrated in Figure 16.

The output of the XSQL query with the XSLT transformation is displayed in an HTML table in the default browser, as illustrated below:

Journal	Publisher	Edition	Title	Author
Oracle Magazine	Oracle Publishing	November-December 2003	Servlets and JSP Step Up	Budi Kurniawan
Oracle Magazine	Oracle Publishing	September-October 2003	Parsing XML Efficiently	Julie Basu

You have just used the XSQL Servlet tool in JDeveloper 10g to query a database table and then transform the resulting XML output with an XSLT stylesheet.