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.

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/4.0/templates/database"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://db.apache.org/torque/4.0/templates/database
        http://db.apache.org/torque/4.0/templates/database-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:

Property Description
native Instructs Torque to use the underlying database's mechanism to generate IDs (varies per database e.g. autoincrement for mysql and schemata for postgresql).
idbroker Instructs Torque to use its IDBroker service to generate IDs in a database agnostic manner. An extra table is used for this, which needs to be created separately.
none Instructs Torque to not generate IDs. Use this setting if you want to define the primary key yourself.

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 strict Schema XSD or the Schema XSD for the database schema. In addition, you should include the XML declaration and XML schema specification in your database schema file. This helps to detect errors in the schema file easily.

For additional information on the XML elements and attributes, please refer to the 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

User comments for this step