Mapping between tables and XML documents

We describe the above by using an example. Let's assume we have a table "httpSession" in which we store the sessions of an HTTP servers current users. Each user has choosen a language in which he wants his HTML pages to be displayed. Thus we have the following tables:

      CREATE TABLE language (
        id INT NOT NULL IDENTITY,
        language VARCHAR(10) NOT NULL,
        country VARCHAR(10) NOT NULL,
        PRIMARY KEY (id),
        UNIQUE (language, country)
      );
      CREATE TABLE httpSession (
        id INT NOT NULL IDENTITY,
        ipaddress VARCHAR(15) NOT NULL,
        logintime TIMESTAMP NOT NULL,
        lastaction TIMESTAMP NOT NULL,
        expiretime SMALLINT NOT NULL,
        cookie VARCHAR(25) NOT NULL,
        languageid INT NOT NULL,
        PRIMARY KEY (id),
        UNIQUE (cookie),
        FOREIGN KEY (languageid) REFERENCES language (id)
      );
    

A Schema for JDBC mapping

This situation is just what JaxMe was made for: The secondary table "language" will typically be subject to manual modifications only. The "httpSession" table, however, is updated all the time.

To access these tables, we again create some kind of XML schema:

      <?xml version="1.0" encoding="UTF-8"?>
      <xs:schema
          xmlns:xs="http://www.w3.org/2001/XMLSchema"
          targetNamespace="http://jaxme.ispsoft.de/namespaces/examples/Session"
          xmlns:jm="http://ispsoft.de/namespaces/jaxme/schema"
          xmlns:jdbcm="http://ispsoft.de/namespaces/jaxme/jdbc-mapping">
        <xs:annotation>
          <xs:appinfo>
            <jm:defaults package="com.mycompany.session"/>
            <jdbcm:defaults driver="org.hsqldb.jdbcDriver"
                url="jdbc:hsqldb:build/db/db"
                user="sa" password=""/>
          </xs:appinfo>
        </xs:annotation>

        <xs:element name="Session">
          <xs:annotation>
            <xs:appinfo>
              <jdbcm:select
                tables="httpSession JOIN language ON httpSession.languageid=language.id"
                />
              <jdbcm:table name="httpSession"/>
            </xs:appinfo>
          </xs:annotation>
          <xs:complexType>
            <xs:sequence>
              <xs:element name="Language">
                <xs:annotation>
                  <xs:appinfo>
                    <jdbcm:table name="language"/>
                  </xs:appinfo>
                </xs:annotation>
                <xs:complexType>
                  <xs:sequence/>
                </xs:complexType>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:schema>
    

Now suggest the following row in the "httpSession" table:

The corresponding "language" row might be:

JaxMe will map this schema to an XML document like the following:

      <Session xmlns="http://jaxme.ispsoft.de/namespaces/examples/Session">
        <ID>126776</ID>
        <IPADDRESS>134.23.1.7</IPADDRESS>
        <LOGINTIME>2001-12-21 13:45:01</LOGINTIME>
        <LASTACTION>2001-12-21 13:47:37</LASTACTION>
        <EXPIRETIME>900</EXPIRETIME>
        <COOKIE>g3dFs2Uj8</COOKIE>
        <LANGUAGEID>3</LANGUAGEID>
        <Language>
          <ID>3</ID>
          <LANGUAGE>de</LANGUAGE>
          <COUNTRY>DE</COUNTRY>
        </Language>
      </Session>
    

But now, how do we get this? We'll use some examples to demonstrate working with the Session document.

Creating classes for JDBC

Using the source generator is not so different. In short, you have to change the SchemaReader and SourceGenerator:

      java de.ispsoft.jaxme.generator.Main Session.xsd
        --schemaReader=de.ispsoft.jaxme.generator.XsdJdbcSchemaReader
        --sourceWriter=de.ispsoft.jaxme.generator.JdbcJavaSourceWriter
    

As before, you will find classes "ClsSession", "ClsSessionHandler", "ClsLanguage" and "ClsLanguageHandler" being generated. But this time, there is an additional class "ClsSessionManager". It is responsible for access to the database.

Note: If you are using an Oracle database, you might need an additional argument

        --option=jdbc.dbmode=Oracle
      

Reading data from a relational database

First of all, we have to specify database driver, JDBC URL, user name and password, like in any other JDBC application. This is done in an XML file:

      <?xml version="1.0" encoding="UTF-8" ?>
      <JMManagerConfiguration
          xmlns="http://jaxme.ispsoft.de/namespaces/JMManagerConfiguration">
        <Defaults>
          <JMJdbcManager>
            <Driver>org.hsqldb.jdbcDriver</Driver>
            <Url>jdbc:hsqldb:build/db/db</Url>
            <User>sa</User>
            <Password/>
          </JMJdbcManager>
        </Defaults>
        <Configuration
            localName="Session"
            namespaceURI="http://jaxme.ispsoft.de/namespaces/examples/Session">
          <ManagerClass>com.mycompany.session.ClsSessionManager</ManagerClass>
          <ElementClass>com.mycompany.session.ClsSession</ElementClass>
          <HandlerClass>com.mycompany.session.ClsSessionHandler</HandlerClass>
        </Configuration>
      </JMManagerConfiguration>
   

Note that the given values are just defaults and can be overwritten per document type by adding subelements Driver, Url, and so on to the respective Configuration section. For example, to use the same settings for the Session Document, but a different user name, you would use

        <Configuration
            localName="Session"
            namespaceURI="http://jaxme.ispsoft.de/namespaces/examples/Session">
          <ManagerClass>com.mycompany.session.ClsSessionManager</ManagerClass>
          <ElementClass>com.mycompany.session.ClsSession</ElementClass>
          <HandlerClass>com.mycompany.session.ClsSessionHandler</HandlerClass>
          <User>otheruser</User>
        </Configuration>
    

This file is best inserted into your classpath somewhere, for example, as a part of a JAR file. Assuming, that your configuration file is in com/mycompany/Configuration.xml, you could write the following program, that writes all current HTTP sessions to System.out, much like the "AddressPrinter":

import com.mycompany.session.ClsSession;
import de.ispsoft.jaxme.*;

public class SessionPrinter {
  public static void main(String[] args) throws Exception {
    System.setProperty(JMFactory.URI_PROPERTY,
                       "resource:com/mycompany/Configuration.xml");
    JMManagerFactory factory = JMFactory.getJMManagerFactory();
    JMManager manager = factory.getManager(ClsSession.NAMESPACE_URI,
                                           ClsSession.LOCAL_NAME);
    String query = "LASTACTION + EXPIRETIME > NOW()";  // A WHERE clause
    for (java.util.Iterator iter = manager.select(query);
         iter.hasNext();  ) {
      ClsSession session = (ClsSession) iter.next();
      System.out.println(session.toXML());
    }
  }
}
    

You could also read the configuration from a file or URL by changing the property value to

      file://c:\temp\Configuration.xml
    

      http://configurations.company.com/Configuration.xml
    

Writing data to a relational database

Now an example that creates a new HTTP session. It uses the same property file as before:

import com.mycompany.session.ClsSession;
import de.ispsoft.jaxme.*;

public class SessionAdd {
  public static void main(String[] args) throws Exception {
    System.setProperty(JMFactory.URI_PROPERTY,
                       "resource:com/mycompany/Configuration.xml");
    JMManagerFactory factory = JMFactory.getJMManagerFactory();
    JMManager manager = factory.getManager(ClsSession.NAMESPACE_URI,
                                           ClsSession.LOCAL_NAME);
    ClsSession session = (ClsSession) manager.create();
    session.eID = new Integer(126776);
    session.eIPADDRESS = "134.23.1.7";
    session.eLOGINTIME = new java.sql.Timestamp(System.currentTimeMillis());
    session.eLASTACTION = session.eLOGINTIME;
    session.eEXPIRETIME = new Short((short) (15*60));
    session.eCOOKIE = "jH63fGdx";
    session.eLANGUAGEID = new Integer(3);
    manager.insert(session);
  }
}
    

A similar example, the update of a row:

import com.mycompany.session.ClsSession;
import de.ispsoft.jaxme.*;

public class SessionUpdate {
  public static void main(String[] args) throws Exception {
    System.setProperty(JmFactory.URI_PROPERTY,
                       "resource:com/mycompany/Configuration.xml");
    JMManagerFactory factory = JMFactory.getJMManagerFactory();
    JMManager manager = factory.getManager(ClsSession.NAMESPACE_URI,
                                           ClsSession.LOCAL_NAME);
    java.util.Iterator iter = manager.select("COOKIE='jH63fGdx'");
    if (iter.hasNext()) {
      ClsSession session = (ClsSession) iter.next();
      session.eLASTACTION = new java.sql.Timestamp(System.currentTimeMillis());
      manager.update(session);
    }
  }
}
   

And finally, as you have probably guessed, an example for deleting a row:

import com.mycompany.session.ClsSession;
import de.ispsoft.jaxme.*;

public class SessionDelete {
  public static void main(String[] args) throws Exception {
    System.setProperty(JMFactory.URI_PROPERTY,
                       "resource:com/mycompany/Configuration.xml");
    JMManagerFactory factory = JMFactory.getJMManagerFactory();
    JMManager manager = factory.getManager(ClsSession.NAMESPACE_URI,
                                           ClsSession.LOCAL_NAME);
    java.util.Iterator iter = manager.select("COOKIE='jH63fGdx'");
    if (iter.hasNext()) {
      ClsSession session = (ClsSession) iter.next();
      manager.delete(session);
    }
  }
}
   

Creating primary keys

Most databases support the generation of primary keys. So does JaxMe, in two different ways. Which way to choose, depends on the following considerations:

1. If you need the generated value for later work, the INSERT operation has to be split into different queries: One for the key generation (typically a SELECT) and one for the actual INSERT.
2. If you just want to INSERT and don't care for the generated value, you can combine both operations into the INSERT statement. The advantage is that you are faster, because only one query is performed.

Creating a primary key for immediate use

If you want to create a primary key that should be used immediately after the INSERT statement, then you have to specify an additional SELECT statement. For example:

     <jdm:table name="httpSession" keys="ID">
       <jdm:createKeyQuery>
         SELECT httpSessionSequence.nextval()
       </jdm:createKeyQuery>
     </jdm:table>
   

In some cases the order of INSERT and SELECT has to be reversed. For example the MySQL database has a nice feature called autoincrement column. With autoincrement columns the above example might look like

     <jdm:table name="httpSession" keys="ID">
       <jdm:createKeyQuery postInsert="true">
         SELECT LAST_INSERT_ID()
       </jdm:createKeyQuery>
     </jdm:table>
  

For combined keys the example could work quite the same. The only difference would be, that the SELECT statement had to return multiple values, one for each column.

TODO: Provide an example for createKeyQuery.

Creating a primary key without accessing the value

If you don't need to access the generated key, you might use the autoSql type, for example like this:

    <xs:element name="Session">
      <xs:annotation>
        <xs:appinfo>
          <jdm:table name="httpSession"/>
        </xs:appinfo>
      </xs:annotation>
      <xs:complexType>
        <xs:sequence>
          <xs:element name="ID" type="jdm:autoSql">
            <xs:annotation>
              <xs:appinfo>
                <jdm:autoSql insert="SELECT httpSessionSequence.nextval()">
              </xs:appinfo>
            </xs:annotation>
          </xs:element>
        </xs:sequence>
      </xs:complexType>
    </xs:element>
  

Find methods

In most cases you would like to specify certain fixed queries, that are performed frequently. This is possible by specifying find methods:

       <jdbcm:select
         tables="httpSession JOIN language ON httpSession.languageid=language.id"
         />
         <jdbcm:findMethod name="findByIpAddress" query="IPADDRESS = ?">
           <jdbcm:param name="pIpAddress" type="xs:string"/>
         </jdbcm:findMethod>
         <jdbcm:findMethod name="findByLanguageId" query="LANGUAGEID = ?"
           maxResultSize="1">
           <jdbcm:param name="pLanguage" type="xs:integer"/>
         </jdbcm:findMethod>
       <jdbcm:table name="httpSession"/>
     

       java.util.List findByIpAddress(String pIpAddress);
       ClsSession findByLanguage(Integer pLanguage);
     

       java.util.List select("IPADDRESS = ?",
                             new Object[]{JMManager.VARCHAR, pIpAddress});
       java.util.List select("LANGUAGE = ?",
                             new Object[]{JMManager.INTEGER, pLanguage});
     

Find methods are particularly useful in conjunction with the BeanWriter: The generated entity beans have the same find methods, that the manager has.

As in the case of the views, find methods may have a dynamic part:

         <jdbcm:findMethod name="findByMatchingColumn"
           query="{MatchColumn} LIKE ?">
           <jdbcm:pattern name="pMatchColumn" sample="SomeColumn"
             match="{MatchColumn}/>
           <jdbcm:param name="pValue" type="xs:integer"/>
         </jdbcm:findMethod>
       

         <jdbcm:findMethod name="findByWhatever"
           query="{Whatever}">
           <jdbcm:pattern name="pWhatever" sample="1=1"
             match="{Whatever}/>
           <jdbcm:placeholders name="pValues"/>
         </jdbcm:findMethod>
       

Mapping between column names and elements or attributes

By default the generator maps table columns to elements or attributes with the same name. If a matching element or attribute cannot be found, it creates one. This is convenient, but in most cases not what you actually want.

The main problem is that the generator sees column names in all uppercase letters. (This is how most RDBMS supply their metadata.) But you don't want your attribute be called IPADDRESS, you want IpAddress. This can be fixed by explicitly specifying a certain spelling:

        <xs:element name="Session">
          <xs:annotation>
            <xs:appinfo>
              <jdbcm:table name="httpSession"/>
            </xs:appinfo>
          </xs:annotation>
          <xs:complexType>
            <xs:sequence>
	      <element name="Id"/>
	      <element name="Language"/>
	      <element name="Country"/>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:schema>
    

This example will generate a class ClsSession with elements Id, Language and Country, mapped to the columns with the same names. Originally the elements have been named ID, LANGUAGE and COUNTRY, so that looks much better now.

However, we are still not satisfied. What it we want the element Country to have the name CountryCode? This can be done by using an jdm:column clause:

        <xs:element name="Session">
          <xs:annotation>
            <xs:appinfo>
              <jdbcm:table name="httpSession"/>
            </xs:appinfo>
          </xs:annotation>
          <xs:complexType>
            <xs:sequence>
	      <element name="Id"/>
	      <element name="Language"/>
	      <element name="CountryCode">
	        <xs:annotation>
		  <xs:appinfo>
		    <jdbcm:column name="Country">
		  </xs:appinfo>
		</xs:annotation>
	      </element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:schema>
    

Mapping of table names