Step 2: Configuring Torque

The following section outlines the necessary steps to define your database schema and configure Torque to use your schema. Upon completion, you'll be able to use Torque to create your object model and all of the Java classes that support it. In addition, Torque can generate and execute all of the appropriate SQL commands to create your database, freeing you from doing it yourself.

To accomplish all of the above, you only need to create/edit three files: the Torque build properties, the Torque database schema, and the Torque run-time properties. Each of these files is covered in the following sections.

Torque Build Properties

Torque is a system that literally builds Java source/class files representing your object model, SQL statements for your specific database, and documentation. To accomplish these tasks, it uses Ant to control its build process, and ant uses the build.properties file in the top-level Torque directory to setup your development environment. It is this file that we will now edit.

Keep in mind, this tutorial is going to show you the bare minimum to get your first Torque application up and running. However, the build.properties file is thoroughly commented, so please refer to it if you have a question regarding a part of the file that is not covered here. Make the following changes and edit appropriately for your environment. The properties are described in the table following (note: you'll need to add the torque.database.buildUrl property):

  torque.project = bookstore
  torque.database = mysql
  torque.targetPackage = com.kazmier.om
  torque.database.createUrl = jdbc:mysql://127.0.0.1/mysql
  torque.database.buildUrl = jdbc:mysql://127.0.0.1/bookstore
  torque.database.url = jdbc:mysql://127.0.0.1/bookstore
  torque.database.driver = org.gjt.mm.mysql.Driver
  torque.database.user = adminuser
  torque.database.password = adminpassword
  torque.database.host = 127.0.0.1

For a reference as to what each property, and others, controls, please see the properties reference.

Setting these properties correctly is very important. These enable Torque to generate all of the required sources and SQL for your specific database. If you experience problems later in this tutorial, it would be wise to double-check these values.

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 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.

The database schema file is located in the torque/schema directory. Here you will find two XML files: id-table-schema.xml and project-schema.xml. The id-table-schema.xml file is used internally by Torque's IDBroker service (which is a database independent method for generating unique IDs). project-schema.xml is where you'll define your database schema. Historically, the name of your database schema file was required to be in the format of name-schema.xml where name was the same as the project property defined in build.properties; otherwise, Torque was not able to find your database schema file. This is no longer the case, name is no longer restricted to the project name. However, it must end with ‘-schema.xml’ because Torque will only generate object models for files ending with that pattern.

For 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 author information (first and last name). The second table will contain publisher information (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 as follows:

<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<!DOCTYPE database SYSTEM
 "http://db.apache.org/torque/dtd/database_3_1.dtd">

<database
  name="bookstore"
  defaultIdMethod="idbroker">

  <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>
  <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>
</database>

Edit project-schema.xml to reflect the above database schema. If you would rather create your own schema file, be sure the filename ends in ‘-schema.xml’, and delete project-schema.xml because Torque will generate an object model for that file as well. Do not delete id-table-schema.xml if you plan on using Torque's IDBroker service, which is used in this tutorial.

There are several items of importance to note. The database element's name attribute must be the same as the database name specified by the databaseUrl property in build.properties; likewise, the run-time properties (described in the next section) should also reflect this value. Failure to do so will prevent Torque from creating your database tables (if instructed to do so) or 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.

One common reason that a table might override the defaultIdMethod is when a table is composed only of foreign keys (i.e. a ‘junction entity’ in database-speak). In this case, all columns should be defined as primary keys because they are all needed to declare a row as unique. However, Torque should not generate primary key IDs for objects in this table because the objects that compose the table already have primary key IDs. Thus, the idMethod attribute of the table must be set to none. For example, if the book table defined above did not have any additional attributes other than a publisher_id and author_id, the schema for the book table should be defined as:

  <table name="book" idMethod="none" description="Book Table">
    <column
      name="publisher_id"
      required="true"
      primaryKey="true"
      type="INTEGER"
      description="Foreign Key Publisher"/>
    <column
      name="author_id"
      required="true"
      primaryKey="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>

Another common mistake is to forget that XML is case-sensitive. All of the elements and attributes must be specified according to the DTD for the database schema. In addition, you must include the XML declaration and DTD specification in your database schema file. Failure to do so can result in errors.

Finally, you must also edit (or add if its not present) the name attribute to the database element in id-table-schema.xml. The value should be identical to the value in your database schema file. This will instruct Torque to create id-table in the same database as your schema. Below is the file used in this example:

<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<!DOCTYPE database SYSTEM
 "http://db.apache.org/torque/dtd/database_3_1.dtd">

<database name="bookstore">
  <table name="ID_TABLE" idMethod="idbroker">
    <column
      name="ID_TABLE_ID"
      required="true"
      primaryKey="true"
      type="INTEGER"/>
    <column
      name="TABLE_NAME"
      required="true"
      size="255"
      type="VARCHAR"/>
    <column
      name="NEXT_ID"
      type="INTEGER"/>
    <column
      name="QUANTITY"
      type="INTEGER"/>
    <unique>
      <unique-column name="TABLE_NAME"/>
    </unique>
  </table>
</database>

Torque uses the database schema files to generate your object model and Java classes to support it. In addition, Torque generates SQL that can be used to create your databases and tables from these schemas. In the next section, we will conclude the configuration of Torque by editing the Torque run-time properties. For additional information on the XML elements and attributes, please refer to the Torque Schema Reference.

Torque Run-Time Properties

The last step in the configuration of Torque are the Torque run-time properties. As the name suggests, these properties are used when your application is executing the object model code generated by Torque. The run-time properties control logging and database parameters such as drivers, usernames, and passwords. These properties can be saved in any file because your application must explicitly initialize Torque (as you'll see later in this document).

There is a sample run-time properties file included in the Torque distribution called Torque.properties located in the torque/schema directory. However, for simplicity, we'll just create our own. Again, this tutorial will guide you through the bare minimum to get your application up and running. For more information regarding the Torque run-time properties, refer to the comments in the sample file included in the distribution. Create a new file called Torque.properties in the top-level torque directory (to avoid overwriting the sample property file) and add the following lines to it:

log4j.rootCategory = DEBUG, default
log4j.appender.default = org.apache.log4j.FileAppender
log4j.appender.default.file = ./torque.log
log4j.appender.default.layout = org.apache.log4j.SimpleLayout

torque.database.default = bookstore
torque.database.bookstore.driver = org.gjt.mm.mysql.Driver
torque.database.bookstore.url = jdbc:mysql://127.0.0.1/bookstore
torque.database.bookstore.username = user
torque.database.bookstore.password = password
  

It is worth re-iterating that these run-time properties are not used by Torque when generating your object model and creating your database. They are used only by the application utilizing the Torque-generated object model classes at run-time.