Powered by SmartDoc
<< Relaxer Tutorial/A JDBC Example >>

A JDBC Example

Relaxer can also generate Java Database Connectivity (JDBC) code. This code allows you to connect to a database engine using the java.sql package. In the following example, I use a locally installed version of MySQL, a popular, free open-source database engine. (It is also available commercially.) To get this example to work exactly as shown, you will need to download MySQL. Otherwise, you can use your own database engine, with a few modifications in the commands. I use version 3.23.54 in the example, running as a service. In order to get JDBC to work with MySQL, you also need to download MySQL Connector/J and place the JAR file in your classpath, as you will see. (That JAR file has a long name; I have renamed it to mysql.jar for brevity).

Following is a RELAX NG schema that represents a data model for a simple table in a database. It is called data.rng and is found in the jdbc directory of the example file archive.

data.rng
<?xml version="1.0" encoding="UTF-8"?>
<grammar xmlns="http://relaxng.org/ns/structure/1.0"
datatypeLibrary="http://www.w3.org/2001/XMLSchema-datatypes"
xmlns:sql="http://www.relaxer.org/xmlns/relaxer/sql">
  <start>
    <ref name="data"/>
  </start>
  <define name="data">
    <element name="data">
<attribute name="language" sql:notNull="false" sql:dataType="CHAR(2)">
        <data type="token"/>
      </attribute>
      <ref name="name"/>
      <element name="email" sql:notNull="false" sql:dataType="VARCHAR(64)">
        <data type="token"/>
      </element>
    </element>
  </define>
  <define name="name">
    <element name="name">
      <element name="family" sql:dataType="VARCHAR(64)">
        <data type="token"/>
      </element>
      <element name="given" sql:dataType="VARCHAR(64)">
        <data type="token"/>
      </element>
    </element>
  </define>
</grammar>

A similar Relax Core schema is found in data.rxm:

data.rxm
<?xml version="1.0"?>
<!DOCTYPE module SYSTEM "relaxCore.dtd" [
 <!ATTLIST attribute sql:dataType CDATA #IMPLIED>
 <!ATTLIST element sql:dataType CDATA #IMPLIED>
 <!ATTLIST element sql:notNull CDATA #IMPLIED>
]>

<module relaxCoreVersion="1.0"
  xmlns:sql="http://www.relaxer.org/xmlns/relaxer/sql">

  <interface>
    <export label="data"/>
  </interface>

  <elementRule label="data">
   <tag name="data">
<attribute name="language" type="token" sql:notNull="false" \
    sql:dataType="CHAR(2)"/>
   </tag>
   <sequence>
    <ref label="name"/>
<element name="email" type="string" occurs="?" sql:notNull="false" \
    sql:dataType="VARCHAR(64)"/>
   </sequence>
  </elementRule>

  <elementRule label="name">
   <tag name="name"/>
   <sequence>
    <element name="family" type="string" sql:dataType="VARCHAR(64)"/>
    <element name="given" type="string" sql:dataType="VARCHAR(64)"/>
   </sequence>
  </elementRule>
   
</module>

You probably noticed that these schemas add the attribute sql:dataType and sql:notNull from the http://www.relaxer.org/xmlns/relaxer/sql namespace. These attributes allow you to have additional control over datatypes and column values that will be used by SQL, instead of letting Relaxer assign them.

The document data.xml is an instance of either schema:

data.xml
<?xml version="1.0" encoding="UTF-8"?>

<data language="en">
 <name>
  <family>Lincoln</family>
  <given>Abraham</given>
 </name>
 <email>president@whitehouse.gov</email>
</data>

To create the JDBC code to work with MySQL, issue the following command:

C:\Relaxer\jdbc>relaxer -verbose -jdbc -jdbc.dataConfig:mysql \
    -jdbc.quote.id: -jdbc.quote.idInWhere: data.rng

The -jdbc option lets Relaxer know that you want to generate JDBC code. The options -jdbc.dataConfig:mysql, -jdbc.quote.id:, and -jdbc.quote.idInWhere: help Relaxer deal with MySQL's peculiarities (an SQL-92 incompatibility regarding quotes). You may be able to avoid using these options if you are using a different database.

This command generates the following files:

As mentioned in earlier examples, to examine this code easily, you can run Javadoc on all the Java files in the jdbc directory:

C:\Relaxer\patterns>javadoc -d doc *.java

Then bring up doc/index.html in a browser to examine the code.

The Data Definition Language or DDL file data.ddl contains an SQL command for creating a table:

data.ddl
CREATE TABLE data (
	language CHAR(2),
	name_family VARCHAR(64) NOT NULL,
	name_given VARCHAR(64) NOT NULL,
	email VARCHAR(64)
)

If you compare data.ddl with data.xml, you can see how the columns of the database table align with the XML structures. The table takes the same name as the document element data. The attribute language is assigned a column, as well as each element that has content, that is, <family>, <given>, and <email>. Because <family> and <given> are children of <name>, the column names are prepended with name_. You can also see how the sql:dataType and sql:notNull attributes control the SQL datatypes or values held in the columns.

To compile all the code in jdbc, enter the following:

C:\Relaxer\jdbc>javac -deprecation DataTable.java

Relaxer uses an interface and a class from SAX1 that are deprecated in SAX2, but this shouldn't cause you problems. If you look at the source code in DataTable.java, you will see methods that use classes from java.sql and perform SQL commands. Following is a fragment from DataTable.java:

Fragment from DataTable.java
/**
 * Creates a table.
 *
 * @exception SQLException
 */
public void createTable() throws SQLException {
    Statement st = conn_.createStatement();
    try {
        st.execute(
"CREATE TABLE " + getQuoteId() + tableName_ + getQuoteId() + " (" + \
    getQuoteId() +
"language" + getQuoteId() + " CHAR(2), " + getQuoteId() + "name_family" + \
    getQuoteId() + " VARCHA
R(64) NOT NULL, " + getQuoteId() + "name_given" + getQuoteId() + " \
    VARCHAR(64) NOT NULL, " + getQu
oteId() + "email" + getQuoteId() + " VARCHAR(64))"
        );
    } finally {
        st.close();
    }
}

As you can see, the createTable() method in DataTable.java executes an SQL statement to create a table, based on the content of the schema data.rng. The statement uses the quote character specified in the earlier Relaxer command (the one that includes a command-line option to specify a quote character, that is, -jdbc.quote.id:). Actually, the quote character I used was just empty, which MySQL will accept.

Before moving on, check to see if MySQL is running on your system as you expect. Though there are other ways to do this, you can invoke the MySQL monitor program mysql.exe in another window to query the status of MySQL. The mysql.exe program exists in the bin directory that is installed with MySQL. After the program is running, issue the status command to mysql.exe to check the status of the engine:

Using mysql
C:\mysql\bin>mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 3.23.54-max-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> status
--------------
mysql  Ver 11.18 Distrib 3.23.54, for Win95/Win98 (i32)

Connection id:          1
Current database:       test
Current user:           ODBC@localhost
Server version:         3.23.54-max-nt
Protocol version:       10
Connection:             localhost via TCP/IP
Client characterset:    latin1
Server characterset:    latin1
TCP port:               3306
Uptime:                 7 hours 19 min 8 sec

Threads: 1 Questions: 62 Slow queries: 0 Opens: 12 Flush tables: 1 Open \
    tables: 0 Queries per
 second avg: 0.002
--------------

It appears that MySQL is up and working as expected. (This assumes that your database system is set up like mine, as a service. If you run MySQL as a service—you can read about this in the manual that comes with MySQL—the engine starts up when you boot your computer.) Now check the default databases that MySQL sets up, mysql and test, by giving the show command:

Show databases
mysql> show databases;
+----------+
| Database |
+----------+
| mysql    |
| test     |
+----------+
2 rows in set (0.00 sec)

mysql>

Now you are ready to issue a command that creates the table in the test database of MySQL:

C:\Relaxer\jdbc>java -cp .;relaxer.jar;mysql.jar DataTable create \
    jdbc:mysql://localhost/test data \
    -driver:com.mysql.jdbc.Driver

This instruction uses the java interpreter rather than using Relaxer directly. It also insures that the JAR files relaxer.jar and mysql.jar (from Connector/J) are in the classpath by naming them specifically. It invokes the DataTable class directly with the create argument for creating a table. It gives the URL for MySQL's test database (mysql://localhost/test), names the table data, and identifies the driver class com.mysql.jdbc.Driver.

Upon success, you can query the database to see whether the table was actually created, using the following commands to mysql.exe:

Show tables
mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| data           |
+----------------+
1 row in set (0.00 sec)

mysql>

The table is now in the database data. Now you can insert the information in the file data.xml using this request:

C:\Relaxer\jdbc>java -cp .;relaxer.jar;mysql.jar DataTable insert \
    jdbc:mysql://localhost/test data data.xml \
    -driver:com.mysql.jdbc.Driver

Notice that instead of create, this command uses insert, and that the filename data.xml is included in the line. After this command, use the SQL instruction select in mysql.exe to check the results:

Select contents in table
mysql> select * from data;
+----------+-------------+------------+--------------------------+
| language | name_family | name_given | email                    |
+----------+-------------+------------+--------------------------+
| en       | Lincoln     | Abraham    | president@whitehouse.gov |
+----------+-------------+------------+--------------------------+
1 row in set (0.00 sec)

mysql>

You can see that the insert command was successful at placing data from data.xml into the data table in MySQL. As an exercise, you can create one or more data files, using the exact same format as data.xml, and then submit them to the database by listing the filenames in the same place as data.xml in the insert command. For example, after you create the files mydata1.xml and mydata2.xml (or similar files):

mydata1.xml
<?xml version="1.0" encoding="UTF-8"?>

<data language="en">
 <name>
  <family>Skywalker</family>
  <given>Luke</given>
 </name>
 <email>luke@jedi.tatooine</email>
</data>
mydata2.xml
<?xml version="1.0" encoding="UTF-8"?>

<data language="en">
 <name>
  <family>Baggins</family>
  <given>Bilbo</given>
 </name>
 <email>bilbo@hobbit.middle-earth</email>
</data>

You can then insert them in the table like this:

C:\Relaxer\jdbc>java -cp .;relaxer.jar;mysql.jar DataTable insert \
    jdbc:mysql://localhost/test data mydata1.xml mydata2.xml \
    -driver:com.mysql.jdbc.Driver

Then check the table to see if the data got inserted as you expected:

Check added data with select
mysql> select * from data;
+----------+-------------+------------+---------------------------+
| language | name_family | name_given | email                     |
+----------+-------------+------------+---------------------------+
| en       | Lincoln     | Abraham    | president@whitehouse.gov  |
| en       | Skywalker   | Luke       | luke@jedi.tatooine        |
| en       | Baggins     | Bilbo      | bilbo@hobbit.middle-earth |
+----------+-------------+------------+---------------------------+
3 rows in set (0.00 sec)

mysql>

Finally, you can dump the table as XML to a file using the dump instruction:

C:\Relaxer\jdbc>java -classpath .;relaxer.jar;\lib\mysql.jar \
    DataTable dump jdbc:mysql://localhost/test data \
    -driver:com.mysql.jdbc.Driver > data.out

As a result, you should see the following XML dumped to the file data.out:

data.out
<data \
    language="en"><name><family>Lincoln</family><given>Abraham</given></name><email>president@whitehouse.gov</email></data> \
<data \
    language="en"><name><family>Skywalker</family><given>Luke</given></name><email>luke@jedi.tatooine</email></data> \
<data \
    language="en"><name><family>Baggins</family><given>Bilbo</given></name><email>bilbo@hobbit.world</email></data> \

The file data.out is not well-formed XML, but you get the idea of how Relaxer can dump table data back out as XML.

This concludes the tutorial. For additional JDBC examples, see the sample/jdbc, sample/minutes, sample/taglib and sample/taglibWar directories.

<< Relaxer Tutorial/A JDBC Example >>