Step 2: Defining the Torque Database Schema

The second file that you must edit to configure Torque is the database schema. The database schema is an XML file that represents the structure of your SQL database in Torque. This is where you define all of your tables, column names and types, as well as the keys used to index these tables.

If you already hava a database set up, you can generate a base schema with the following command in your top-level project directory:

ant jdbc

This will by default generate a schema.xml file. By default or if you do not define defaultOutputDir it will be located in target/generated-schema. Hint: Copy the generated schema to src/main/schema directory and rename it to bookstore-schema.xml, then add appropriate attributes e.g.

 <database xmlns="http://db.apache.org/torque/5.0/templates/database"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://db.apache.org/torque/5.0/templates/database
        http://db.apache.org/torque/torque-5.0/documentation/orm-reference/database-5-0.xsd"
  name="default"
  defaultIdMethod="none" defaultJavaType="object">
 

Your database schema file is typically located in the src/main/schema directory under the base of your project. In this directory, you will create the file bookstore-schema.xml which defines your database schema. The name of your database schema file must end with schema.xml because Torque will only generate object models for files ending with that pattern.

In this tutorial, we will use a simple database that might be used to support a bookstore application. The database will contain three tables: author, publisher, and book. The first table will contain publisher information (name). The second table will contain author information (first and last name). And the third table will contain book information (title, and ISBN). The author id and publisher id will be foreign keys in the book table. The schema representation for this database is stored in the file bookstore-schema.xml, which should be created in the src/main/schema directory and contain the following:

<?xml version="1.0" encoding="ISO-8859-1" ?>
<database xmlns="http://db.apache.org/torque/5.0/templates/database"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://db.apache.org/torque/5.0/templates/database
        http://db.apache.org/torque/torque-5.0/documentation/orm-reference/database-5-0-strict.xsd"
  name="bookstore"
  defaultIdMethod="native">

  <table name="publisher" description="Publisher Table">
    <column
      name="publisher_id"
      required="true"
      primaryKey="true"
      type="INTEGER"
      description="Publisher Id"/>
    <column
      name="name"
      required="true"
      type="VARCHAR"
      size="128"
      description="Publisher Name"/>
  </table>
  <table name="author" description="Author Table">
    <column
      name="author_id"
      required="true"
      primaryKey="true"
      type="INTEGER"
      description="Author Id"/>
    <column
      name="first_name"
      required="true"
      type="VARCHAR"
      size="128"
      description="First Name"/>
    <column
      name="last_name"
      required="true"
      type="VARCHAR"
      size="128"
      description="Last Name"/>
  </table>
  <table name="book" description="Book Table">
    <column
      name="book_id"
      required="true"
      primaryKey="true"
      type="INTEGER"
      description="Book Id"/>
    <column
      name="title"
      required="true"
      type="VARCHAR"
      size="255"
      description="Book Title"/>
    <column
      name="isbn"
      required="true"
      type="VARCHAR"
      size="24"
      javaName="ISBN"
      description="ISBN Number"/>
    <column
      name="publisher_id"
      required="true"
      type="INTEGER"
      description="Foreign Key Publisher"/>
    <column
      name="author_id"
      required="true"
      type="INTEGER"
      description="Foreign Key Author"/>
    <foreign-key foreignTable="publisher">
      <reference
        local="publisher_id"
        foreign="publisher_id"/>
    </foreign-key>
    <foreign-key foreignTable="author">
      <reference
        local="author_id"
        foreign="author_id"/>
    </foreign-key>
  </table>
</database>

There are several items of importance to note. The database element's name attribute must be the same as the database's' name in the runtime property file torque.properties described later in this tutorial. Failure to do so will prevent your object model from working properly.

Another item of importance is the database element's defaultIdMethod attribute. This attribute specifies the default method that Torque will use to generate IDs for primary keys (columns with the primaryKey attribute set to true: book_id, publisher_id, and author_id) in your database tables. There are several possible values:

The defaultIdMethod selected will be used for all tables in your schema unless an individual table element contains the idMethod attribute, in which case, its value will override the defaultIdMethod. idMethod takes the same values as defaultIdMethod.

Another common mistake is to forget that XML is case-sensitive. All of the elements and attributes must be specified according to either the

For additional information on the XML elements and attributes, please refer to the Schema XSD or 5.0 Schema XSD.

Where to next

That completes the configuration of the Torque generator. You are now ready to start building your object model and creating your database.

Maven users should look at Invoking the Torque Maven plugin, wheras ant users should go to Invoking the ant build containing Torque.

User Comments