July 25, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

Installing and Using the Torque Object Mapper

  • September 5, 2002
  • By James M. Turner
  • Send Email »
  • More Articles »

Each table definition begins by specifying the name of the table. Next, each column is defined. Note that PITCHER_ID is flagged as both autoIncrement and primaryKey. This means that when a new row is created, the database will automatically increment this value. It also means that the developer can't write out a new value with this column specified; it will always be generated automatically.

After the columns are defined, any foreign keys are listed. Although this may not be required from the perspective of the database (MySQL doesn't support foreign keys, for example), it is also a flag to Torque to create relationships between the two objects, as you will see later.

<table name="TEAM" idMethod="none">
<column name="TEAM_ID" required="true" primaryKey="true"
        type="INTEGER"/>
<column name="TEAM_NAME" required="true" size="30" type="VARCHAR"/>
</table>

In the case of the TEAM table, we need to specify idMethod="none" because the teams are loaded in by hand, and the team ID numbers are explicitly specified. The "none" value tells Torque that although there are columns that are primary keys, Torque should not attempt to manage them.

<table name="GAME" idMethod="none">
<column name="PITCHER_ID" required="true" type="INTEGER"/>
<column name="PLAYING_FOR" required="true" type="INTEGER"/>
<column name="AGAINST_TEAM" required="true" type="INTEGER"/>
<column name="OUTS_RECORDED" required="true" type="INTEGER"/>
<column name="HITS" type="INTEGER"/>
<column name="RUNS" type="INTEGER"/>
<column name="WALKS" type="INTEGER"/>
<column name="STRIKE_OUTS" type="INTEGER"/>
<foreign-key foreignTable="PITCHER">
<reference local="PITCHER_ID" foreign="PITCHER_ID"/>
</foreign-key>
<foreign-key foreignTable="TEAM">
<reference local="PLAYING_FOR" foreign="TEAM_ID"/>
</foreign-key>
<foreign-key foreignTable="TEAM">
<reference local="AGAINST_TEAM" foreign="TEAM_ID"/>
</foreign-key>
</table>

Finally, the table that holds the actual game statistics. In this case, we still have to specify idMethod="none", this time because there are no primary keys at all, and Torque gets confused if you don't explicitly tell it not to manage such a table. This table also has three separate foreign keys.

Once the schema is written, you use Ant to have Torque generate both the Java and the SQL files for the application.

D:\gamelan\torque>ant -f build-torque.xml
Buildfile: build-torque.xml

main:

project-sql:
[echo] +------------------------------------------+
[echo] |                                          |
[echo] | Generating SQL for YOUR Turbine project! |
[echo] | Woo hoo!                                 |
[echo] |                                          |
[echo] +------------------------------------------+
[torque-sql] Using contextProperties file:
             D:\gamelan\torque\build.properties
[torque-sql] Generating to file
 D:\gamelan\torque\src\sql\report.pitchers.sql.generation
[torque-sql] Resolver: used database.dtd from
 org.apache.torque.engine.database.transform package
[torque-sql] Resolver: used database.dtd from
 org.apache.torque.engine.database.transform package
[torque-sql] Resolver: used database.dtd from
 org.apache.torque.engine.database.transform package
[torque-sql] Resolver: used database.dtd from
 org.apache.torque.engine.database.transform package

project-om:
[echo] +------------------------------------------+
[echo] |                                          |
[echo] | Generating Peer-based Object Model for   |
[echo] | YOUR Turbine project! Woo hoo!           |
[echo] |                                          |
[echo] +------------------------------------------+
[torque-om] Using contextProperties file:
 D:\gamelan\torque\build.properties
[torque-om] Generating to file
 D:\gamelan\torque\src\java\report.pitchers.om.generation
[torque-om] Resolver: used database.dtd from 
 org.apache.torque.engine.database.transform package
[torque-om] Resolver: used database.dtd from 
 org.apache.torque.engine.database.transform package

BUILD SUCCESSFUL

Total time: 2 seconds

You have to enjoy any build process that involves the word Woo hoo!

With the schema written, you need to create the user in the database, and give it privileges to create the database and tables. Assuming that MySQL is already installed, you'd do it like this:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 3.23.45-nt

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

mysql> grant all privileges on *.* to
       pedro@localhost identified by 'ace';
Query OK, 0 rows affected (0.00 sec)

mysql> quit

Now you can let Torque create the database and populate the tables, using ant again.

D:\gamelan\torque>ant project-create-db
Buildfile: build.xml

project-create-db:
[torque-create-db] Generating to file
 D:\gamelan\torque\src\sql\create-db.sql
[torque-create-db] Resolver: used database.dtd from 
 org.apache.torque.engine.database.transform package
[torque-create-db] Resolver: used database.dtd from 
 org.apache.torque.engine.database.transform package
[torque-create-db] Resolver: used database.dtd from 
 org.apache.torque.engine.database.transform package
[torque-create-db] Resolver: used database.dtd from 
 org.apache.torque.engine.database.transform package
[sql] Executing file: D:\gamelan\torque\src\sql\create-db.sql
[sql] 2 of 2 SQL statements executed successfully

BUILD SUCCESSFUL

Total time: 1 second
D:\gamelan\torque>ant project-insert-sql
Buildfile: build.xml

project-insert-sql:
[torque-insert-sql] Our new url -> jdbc:mysql://127.0.0.1/pitchers
[torque-insert-sql] Executing file:
                    D:\gamelan\torque\src\sql\id-table-schema.sql
[torque-insert-sql] 6 of 6 SQL statements executed successfully
[torque-insert-sql] Our new url -> jdbc:mysql://127.0.0.1/pitchers
[torque-insert-sql] Executing file:
                    D:\gamelan\torque\src\sql\project-schema.sql

[torque-insert-sql] 18 of 18 SQL statements executed successfully

BUILD SUCCESSFUL

Total time: 0 seconds

With the database built and populated, we can talk about what's happening on the Java side. If you look in the src subdirectory, you'll see two directories inside it. One, "sql", contains the automatically generated SQL used to create the database. The other, "java", is the head of a source tree that contains the object mapping implementation. Here's the layout:

src
├───java
│    └───pitchers
│           └───torque
│                  └───map
└───sql

All the files that we care about live in the torque directory. For each table, there are four classes defined in this directory. For example, the TEAM table has four corresponding classes: BaseTeam, BaseTeamPeer, Team, and TeamPeer.

The Base files contain the actual implementation details of the table, and are re-created each time you do an ant build. The Peer class is something like a static method; it's used to deal with the table as a whole. The non-Peer class is the implementation of a row from this table. This will become clearer when you see them in use.

The non-Base classes start out empty. For example, here is TeamPeer.java:

package pitchers.torque;

import java.util.*;
import com.workingdogs.village.*;
import org.apache.torque.map.*;
import org.apache.torque.pool.DBConnection;

// Local classes
import pitchers.torque.map.*;

/** 
* The skeleton for this class was autogenerated by Torque on:
*
* [Thu Aug 01 23:35:05 EDT 2002]
*
* You should add additional methods to this class to meet the
* application requirements. This class will only be generated as
* long as it does not already exist in the output directory.
*/
public class TeamPeer
extends pitchers.torque.BaseTeamPeer
{
}

As you can see, all it does is extend the Base class. The important thing to know about these two files is that they are not overwritten on rebuilds, so you can put extensions and business logic in them.

The first extension you could add is a helper function for the Team class to allow you to look up a team by name (remembering that the unique identifier for teams is not the team name but an integer sequence, so that teams can easier change names if they are bought or sold). Here's the source for Team.java:

package pitchers.torque;

import org.apache.torque.util.*;
import org.apache.torque.om.Persistent;
import java.util.List;

/** 
* The skeleton for this class was autogenerated by Torque on:
*
* [Thu Aug 01 23:35:05 EDT 2002]
*
* You should add additional methods to this class to meet the
* application requirements. This class will only be generated as
* long as it does not already exist in the output directory.
*/
public class Team 
extends pitchers.torque.BaseTeam
implements Persistent
{

  public static Team findTeamByName(String name) throws Exception{
    Criteria c = new Criteria();
    c.add(TeamPeer.TEAM_NAME, name);
    List team = TeamPeer.doSelect(c);
    if (team.size() != 1) {
      return null;
    }
    return (Team) team.get(0);
  }
}

This code illustrates several features of Torque. To begin with, it uses the Criteria class. Criteria are equivalent to the WHERE clause in an SQL statement. Each Peer class has static string variables that correspond to the columns of that table. So in this case, after creating a new Criteria, you can add the condition that the team name must equal the name passed in to the method (if you only pass two arguments to the "add" method, the EQUAL test is assembled).

Next, TeamPeer.doSelect is called, with the Criteria as its argument. The doSelect method returns a List with zero or more elements, depending on how many rows in the database matched. Since there should only ever be a single team in the database with a given name, the code returns null if either zero or more than one row is returned; otherwise, it returns the team.

The Team class is basically a bean with getters and setters for all the columns, as well as knowing how to save itself or create new records.

The only other class that you need to extend is the Pitcher class, to add some business logic and helper functions.

package pitchers.torque;
import java.util.Iterator;
import java.util.List;
import org.apache.torque.util.*;
import pitchers.torque.*;


import org.apache.torque.om.Persistent;

/** 
* The skeleton for this class was autogenerated by Torque on:
*
* [Thu Aug 01 23:35:05 EDT 2002]
*
* You should add additional methods to this class to meet the
* application requirements. This class will only be generated as
* long as it does not already exist in the output directory.
*/
public class Pitcher
extends pitchers.torque.BasePitcher
implements Persistent
{

  public static Pitcher findPitcherByName
                (String first, String last)
    throws Exception {
    Criteria c = new Criteria();
    c.add(PitcherPeer.FIRST_NAME, first);
    c.and(PitcherPeer.LAST_NAME, last);
    List pitchers = PitcherPeer.doSelect(c);
    if (pitchers.size() != 1) {
      return null;
    }
    return (Pitcher) pitchers.get(0);
  }

  public double computeERA() throws Exception {
    Iterator c = getAllGames().iterator();
    int outs = 0;
    int runs = 0;

    while (c.hasNext()) {
      Game g = (Game) c.next();
      outs += g.getOutsRecorded();
      runs += g.getRuns();
    }

    // ERA is (earned runs * 9) / innings pitched
    // 3 outs per inning, so it's (earned runs * 9) / (outs / 3)
    // Or, (earned runs * 27) / outs
    return (runs * 27.0) / outs;
  }

  public double computeERAAgainst(Team t) throws Exception {
    Iterator c = getGamesAgainst(t).iterator();
    int outs = 0;
    int runs = 0;

    while (c.hasNext()) {
      Game g = (Game) c.next();
      outs += g.getOutsRecorded();
      runs += g.getRuns();
    }

    // ERA is (earned runs * 9) / innings pitched
    // 3 outs per inning, so it's (earned runs * 9) / (outs / 3)
    // Or, (earned runs * 27) / outs
    return (runs * 27.0) / outs;
  }

  public List getAllGames() throws Exception {
    Criteria c = new Criteria();
    c.add(GamePeer.PITCHER_ID, this.getPitcherId());
    return GamePeer.doSelect(c);
  }

  public List getGamesAgainst(Team t) throws Exception {
    Criteria c = new Criteria();
    c.add(GamePeer.PITCHER_ID, this.getPitcherId());
    c.and(GamePeer.AGAINST_TEAM, t.getTeamId());
    return GamePeer.doSelect(c);
  }
}

The findPitcherByName method is essentially the same as the findTeamByName method you've already seen, except that it shows an example of a compound Criteria. After adding the first condition using the Criteria.add method, all subsequent conditions should either use Criteria.and or Criteria.or, to specify how the WHERE clause should be constructed.





Page 2 of 3



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Sitemap | Contact Us

Rocket Fuel