Reading from the Database

To read data from the database, you need to specify which datasets you want to read from which tables. In Torque, this is done by constructing a Criteria object, which is a Java representation of a SQL Query. After you have done that, pass the Criteria object to a Peer class, which will query the database and convert the retrieved table rows into Data Objects.

Note that there are two criteria classes in the runtime: org.apache.torque.criteria.Criteria and org.apache.torque.util.Criteria. The former is recommended for new projects, the latter is deprecated but can be used for legacy projects. Apart from obvious API differences, there are a few subtle semantics differences between the two classes (Check the migration guide for details). All examples in this document use the new criteria.Criteria object.

All following examples are using generated classes (AuthorPeer, author, BookPeer, Book,...)from the ORM tutorial. Please generate those classes following the tutorial if you want to try these examples.

Reading all Objects

To read all Datasets from a table in the datasets, create an empty Criteria object and pass it to the doSelect Method of the Peer class for the table. In SQL, this would be equivalent to the command "SELECT * FROM SOME_TABLE"

For example, the following code can be used to read all Authors from the Author table.

Criteria criteria = new Criteria();
List authors = AuthorPeer.doSelect(critieria);

Specifying which objects should be read

To read only the datasets which fulfil certain conditions, add the conditions to the criteria object before you pass the criteria to the select method. In SQL, this would correspond to adding a "WHERE"-clause to the select command.

As an example, to read all Authors which have the LAST_NAME field filled with "Stevens", the following code can be used:

Criteria criteria = new Criteria();
criteria.where(AuthorPeer.LAST_NAME, "Stevens");
List authors = AuthorPeer.doSelect(criteria);

To use another comparison operator than EQUAL (=), you need to specify which operator should be used. Valid comparison operators are:

  • Criteria.EQUAL (Default)
  • Criteria.NOT_EQUAL (<>)
  • Criteria.ALT_NOT_EQUAL (!=) (not needed, in the suported dbs use Criteria.NOT_EQUAL)
  • Criteria.EQUALS (Default)
  • Criteria.GREATER_THAN
  • Criteria.LESS_THAN
  • Criteria.GREATER_EQUAL
  • Criteria.LESS_EQUAL
  • Criteria.IN
  • Criteria.NOT_IN
  • Criteria.LIKE
  • Criteria.NOT_LIKE

For example, to read all authors with an id less than 5, use

Criteria criteria = new Criteria();
criteria.where(AuthorPeer.AUTHOR_ID, 5, Criteria.LESS_THAN);
List authors = AuthorPeer.doSelect(criteria);

AND and OR operators

If you add multiple constraints to a Criteria, they are linked by default by a logical "AND" operator. For example, the code

Criteria criteria = new Criteria();
Criteria.where(AuthorPeer.LAST_NAME, "Stevens");
Criteria.where(AuthorPeer.FIRST_NAME, "W.");
List authors = AuthorPeer.doSelect(criteria);

results in the following SQL query:

SELECT ... from AUTHOR where LAST_NAME='Stevens' AND FIRST_NAME='W.'

However, the code above is not very easy to understand if you do not know how the where conditions are connected by default. So it is better to specify explicitly which operator should be used to link the constraints in a Criteria. This can be done by using the methods Criteria.and() and Criteria.or(). The former and's a condition with all the other conditions already in the criteria, and the latter or's a condition with all the other conditions already in the criteria (Note: this is different for the deprecated util.Criteria).

For example, to produce the following SQL query:

select * from abc where (a < 1 and b > 2) or (a > 5)

you can use the code

Criteria crit = new Criteria()
    .where(ABC.A, 1, Criteria.LESS_THAN)
    .and(ABC.B, 2, Criteria.GREATER_THAN)
    .or(ABC.A, 5, Criteria.GREATER_THAN);

For more complex queries, use the org.apache.torque.criteria.Criterion object, and the methods Criterion.and() and Criterion.or() to combine them.

For example, the Criteria which corresponds to the SQL query

select * from abc where (a < 1 and b > 2) or (a > 5 and b < 3)

is

Criteria crit = new Criteria();
Criterion a1 = new Criterion(ABC.A, 1, Criteria.LESS_THAN);
Criterion b2 = new Criterion(ABC.B, 2, Criteria.GREATER_THAN);
Criterion a5 = new Criterion(ABC.A, 5, Criteria.GREATER_THAN);
Criterion b3 = new Criterion(ABC.B, 3, Criteria.LESS_THAN);

crit.where(a1.and(b2).or(a5.and(b3)));

(Note that the tables used in the last examples are not defined in the bookstore schema in the tutorial).

Foreign keys

Foreign keys define a link between rows in different tables. They are defined in the database schema on generation time. If the generator option torque.om.complexObjectModel is not explicitly set to false, getters and setters are generated for linked objects.

For example, in the Bookstore schema, a foreign key is defined which creates a link between the book and the author tables:

<database ...>

  <table name="author">
    <column
      name="author_id"
      required="true"
      primaryKey="true"
      type="INTEGER"
      description="Author Id"/>
    ...
  </table>
  <table name="book" description="Book Table">
    ...
    <column
      name="author_id"
      required="true"
      type="INTEGER"
      description="Foreign Key Author"/>
    ...
    <foreign-key foreignTable="author">
      <reference
        local="author_id"
        foreign="author_id"/>
    </foreign-key>
  </table>
</database>

The foreign key defines that a book is associated to an author by connecting the author_id column of the book table to the primary key of the corresponding author table.

In the Book Object, the Torque Generator generates the methods getAuthor() and setAuthor(Author author) which can be used to access and set the corresponding author object. In the Author object, the methods getBooks() and addBook() are generated. These methods can also be used to get the connected book objects or to create a connection, respectively.

By default, the getters for collections query the database for the corresponding objects if they have not been read already. For example, the method Author.getBooks() silently queries the database for the books for the author, if they have not been read before. If you do not like this behaviour (e.g. if you want to make sure that all reads occur within one database transaction), set the generator option torque.om.silentDbFetch to false.

It is recommended to create an index of the referencing side of the association (in the above example the book table), if you want to read associated objects from the referenced side (in the above example the author side) and if the referencing column is not also the primary key. In the above example:

<database ...>

  <table name="book" ...>
    ...
    <index name="book_author_id">
      <index-column name="author_id"/>
    </index>
  </table>
</database>

You can also use more than one column in the foreign key. This is done by having more than one reference XML element inside the foreign-key element.

Also, the referenced column does not need to be a primary key (although this is unusual and not recommended for the tyspical cases). If you want to use a non-primary key as referenced key, simply use the desired referenced column as foreign column in the reference element. Note that most databases require a unique index on the referenced column if it is not a primary key. This is usually not created automatically, so you need to specify the unique constraint in the definition of the referenced column.

The book-author example above defines a 1:n association between authors and books (1 because the foreign key column is required, i.e not-nullable, and n because many books can point to one author, so that one author can be connected to many books). To define a 0..1:n association, the attribute required of the foreign key column book.author_id should be set to false (or not set at all, because the default value is false). A 1:0..1 association is a special case of a 1:n association. It is defined by adding a unique index to the n side of the association (in the above case, to the author_id column of the book table). A n:m association between two tables can be created by defining an association table which links the two tables. E.g. for creating an n:m association between authors and books, the following association table can be used:

<database ...>

  <table name="book_author">
    <column
      name="author_id"
      required="true"
      primaryKey="true"
      type="INTEGER"
      description="Link to Author Id"/>
    <column
      name="book_id"
      required="true"
      primaryKey="true"
      type="INTEGER"
      description="Link to Book Id"/>

    <foreign-key foreignTable="author">
      <reference
        local="author_id"
        foreign="author_id"/>
    </foreign-key>
    <foreign-key foreignTable="book">
      <reference
        local="book_id"
        foreign="book_id"/>
    </foreign-key>
  </table>
</database>

Of course, the foreign key column in the book column must be removed for the n:m association.

Joins

In the database, Joins can be used for two purposes. First, they can be used to read corresponding rows in other tables along with the original row. Second, Joins can be used to qualify which objects in one table should be read, or how the objects in one table should be ordered, depending on the content of another table. In Torque, these two uses of joins are treated differently.

Joins to read corresponding rows

To read corresponding rows, doSelectJoin<tablename> methods are generated for the foreign keys in the Peer classes. To keep the public API of the Peer classes reasonable, these methods are generated as protected methods. To make them visible publicly, override them in the Peer class with a public access modifier.

For example, to read all Authors and their corresponding Books, override the doSelectJoinBooks() method in the Author peer with the following code:

public List doSelectJoinBooks(Criteria criteria) throws TorqueException
{
    return super.doSelectJoinBooks(criteria);
}

Then, you can read the author with the last Name "Stevens" and all his books using the following code:

Criteria criteria = new Criteria();
criteria.where(AuthorPeer.LAST_NAME, "Stevens");
List authorAndBooks = AuthorPeer.doSelectJoinBooks(criteria);

Note that an inner join is used for reading the datasets, so no authors are returned if no book is found.

You can also put constraints on the related books. For example, to read only the related book with the title "TCP/IP Illustrated, Volume 1":

Criteria criteria = new Criteria();
criteria.where(AuthorPeer.LAST_NAME, "Stevens");
criteria.and(BookPeer.NAME, "TCP/IP Illustrated, Volume 1");
List authorAndBooks = AuthorPeer.doSelectJoinBooks(criteria);

The doSelectJoin<tablename> only read one related table at once. If there are more than 2 foreign keys in one table, also doSelectJoinAllExcept<tablename> methods are generated, which read all related objects except the one in the method names.

At the moment, there is no method which can read nested data which are more than one foreign key relation apart. For example, if a table A has a foreign key reference to a table B, which has a foreign key reference to a table C, there is no easy way to read the data in table A and the related datasets in table B and table C in one single select. However, you can look at the generated code of the doSelectJoin<tablename> methods and adopt it for the special combination of tables you need to join. Also, you can use the filler methods (see below) for reading associated data with good performance.

Inner joins for qualifying

In the following subsections, the joins are not used for reading additional data, but for qualifying which data should be read in one table by looking at the content of other tables.

For example, we want to know all authors which have published at least one book. This can be achieved using the following code:

Criteria criteria = new Criteria();
criteria.addJoin(AuthorPeer.AUTHOR_ID, BookPeer.AUTHOR_ID, Criteria.INNER_JOIN);

List bookAuthors = AuthorPeer.doSelect(criteria);

This method creates a so called "inner join", i.e. only author entries which have a corresponding book entry are selected. Note that the author would appear for every book that is attached to it. If an author has published 10 books, the author would appear in the returned List 10 times (see the section "Using DISTINCT" below for a solution).

Details
In SQL, there are two different ways to state an inner join. The first way is a statement like
SELECT BOOK.* FROM BOOK INNER JOIN AUTHOR ON BOOK.AUTHOR_ID=AUTHOR.AUTHOR_ID
SQL like this is produced if the join type is stated explicitly (as above).
The second way to create an inner join is
SELECT BOOK.* FROM BOOK,AUTHOR WHERE BOOK.AUTHOR_ID=AUTHOR.AUTHOR_ID
A SQL statement like this is created by not supplying the join type, for example in
criteria.addJoin(AuthorPeer.AUTHOR_ID, BookPeer.AUTHOR_ID);
Note that both SQL statements return the same result set.
It is not possible to combine both ways of creating a join in most databases, i.e. code like
criteria.addJoin(AuthorPeer.AUTHOR_ID, BookPeer.AUTHOR_ID);
criteria.addJoin(BookPeer.PUBLISHER_ID, PublisherPeer.PUBLISHER_ID, Criteria.INNER_JOIN);
produces an SQL error in most databases. In most circumstances, it is recommended to state t the join type explicitly. However, if the "INNER JOIN" syntax is not supported by your database, try not to state the join type explicitly.

Outer joins for qualifying

To discuss the difference between inner joins and outer joins, consider the following code fragments. Joins will be used there just for discussing the effects of the different join types, but for no other reason.

First, let us reconsider the inner join code:

Criteria criteria = new Criteria();
criteria.addJoin(AuthorPeer.AUTHOR_ID, BookPeer.AUTHOR_ID, Criteria.INNER_JOIN);

List bookAuthors = AuthorPeer.doSelect(criteria);

For an inner join, the database only returns an entry in one table if there is a corresponding entry in the other table. In the example above, this means that only authors with a book are read. In contrast, with an outer join, an entry in one table is also returned even if there is no corresponding entry in the other table:

Criteria criteria = new Criteria();
criteria.addJoin(AuthorPeer.AUTHOR_ID, BookPeer.AUTHOR_ID, Criteria.LEFT_JOIN);
List authors = AuthorPeer.doSelect(criteria);

The above code uses a left (outer) join. There, authors which have no books also appear in the list of authors. There are two kinds of outer joins: left joins and right joins. In a left join, entries in the first table are returned even if no entry in the second table is found, whereas in a right join, entries in the second table are returned even if there are no entries in the first table. Both kinds are supported in Torque via the constants Criteria.LEFT_JOIN and Criteria.RIGHT_JOIN.

Usually, outer joins are used for reading in several tables at once (not ?yet? supported by Torque out of the box), or for ordering the contents of one table by the contents of another table.

Fillers

A very efficient way of reading corresponding objects for a set of data objects is to query all corresponding objects in one query using IN. E.g. if you have three authors with the ids 1,3 and 7 and want to read the related books, you can use the SQL statement SELECT * FROM BOOK WHERE AUTHOR_ID IN(1,3,7). This is in nearly all cases faster than the standard lazy-loading approach by calling the java method author.getBook() for each author which will silently issue one select statement for each author if the corresponding books have not been read.

Torque can generate filler methods which use the IN approach. To generate these methods, the generation option torque.om.complexObjectModel.generateFillers needs to be set to true for the generation process. Then, for the author-book example, Torque will generate a method fillAuthors(List<Book>) in the BookPeer class and a method fillBooks(List<Author>) in the AuthorPeer class which will fill all authors and books in the passed lists, respectively, using the IN approach. Once the corresponding objects have been filled, you can use the author.getBooks() and book.getAuthor() methods and Torque will not query the database again.

The filler methods use chunk querying for large lists (i.e. there is a maximum number of ids they will put into a single IN clause; if necessary several SQL statements will be issued). The chunk size can be modified by overriding the getFillerChunkSize() method.

The filler approach has the advantage over joins that it minimizes the amount of transferred data. Consider data where each author has written 10 books. If the author/book data is written by joins, the data of one author will be transferred ten times (once for each corresponding book). This is not the case when using fillers; the filler approach will transfer each row data only once.

Using DISTINCT

All of the examples in the section "joins" can return multiples of the desired table rows, as one row may be linked to multiple rows in the joined table.

In the first example in the section "Inner joins for qualifying", the author would appear for every book that is attached to it. If an author has published 10 books, the author would appear 10 times in the returned list. To avoid this problem, and return each author only once, use the setDistinct() method in Criteria. For instance:

Criteria criteria = new Criteria();
criteria.addJoin(AuthorPeer.AUTHOR_ID, BookPeer.AUTHOR_ID, Criteria.INNER_JOIN);
criteria.setDistinct();

List bookAuthors = AuthorPeer.doSelect(criteria);

Note that distinct only has an effect if there are entries where all column values are the same. It is not possible to specify DISTINCT for single columns only.

Ordering the Results

One of the common clauses in an SQL Query is the ORDER BY clause. With the criteria object, the results can be ordered via the addAscendingOrderByColumn(Column) and addDescendingOrderByColumn(Column) methods. As an example, consider the book table from the tutorial. To get all books ordered by their title and ISBN (where the ISBN should only be taken into account for equal titles), use the following code:

Criteria criteria = new Criteria();
criteria.addAscendingOrderByColumn(BookPeer.TITLE);
criteria.addAscendingOrderByColumn(BookPeer.ISBN);

List books = BookPeer.doSelect(criteria);

Case insensitivity

String comparisons are usually case sensitive (unless the underlying database compare strings in a case-insensitive way - e.g. MySQL in the default configuration). To get a case insensitive comparison, you need to tell the criteria that it should ignore the case thus:

Criteria criteria = new Criteria();
Criterion criterion = new Criterion(
    searchColumn,
    (%" + searchString + "%",
    Criteria.LIKE);
criterion.setIgnoreCase(true);
criteria.where(criterion);

List invoices = InvoicePeer.doSelect(criteria);

For PostgreSQL this will use ILIKE, for other databases it will use the SQL upper() function on the column and search string (for Oracle you may want to define a function index to make this efficient).

You can also use Criteria.setIgnoreCase(true) to make all Criterions in the Criteria object case insensitive.

Using custom SQL

Although Criteria provide for the most common SQL queries, some queries can not be created using standard Criteria methods. However, the Criteria object has the following methods which allow to add custom SQL: andVerbatimSql(), orVerbatimSql(), and whereVerbatimSql().

For demonstration purposes, let us construct a query which retrieves the author with the AUTHOR_ID of 5:

Criteria criteria = new Criteria();
criteria.whereVerbatimSql("AUTHOR_ID=?", new Object[] {5}, AuthorPeer.AUTHOR_ID, null);
List authors = AuthorPeer.doSelect(criteria);

The first argument specifies the custom SQL, the second argument the prepared statement replacements in the SQL, the third argument and the fourth arguments can be used to specify columns accessed in the Custom SQL (this is needed to calculate the FROM clause of the SQL). Note that in the specific example above, there was no need for using custom SQL. The same query could have been created by:

Criteria criteria = new Criteria();
criteria.where(AuthorPeer.AUTHOR_ID, 5);
List authors = AuthorPeer.doSelect(criteria);

It is recommended not to use verbatim SQL whenever you can avoid it. If you use verbatim SQL, your code will be less portable to other databases, and the compiler will not alert you if you change your data model and access a column which no longer exists.

The old way of specifying custom SQL using Criteria.CUSTOM still exists, however this does not allow to use Prepared Statement replacements.

Subselects in the WHERE clause

To use a subselect in the WHERE clause, simply put a criteria which represents the subselect at the place where you would normally specify the column value(s) explicitly.

Do not forget to specify the selectColumns in the Criteria representing the subselect (usually, the Peer classes do this for you, but not in the subselect).

As an example, assume we want to read the author with the largest authorId from the database. This can be done as follows:

Criteria subquery = new Criteria();
subquery.addSelectColumn(new Max(AuthorPeer.AUTHOR_ID));

Criteria criteria = new Criteria();
criteria.where(AuthorPeer.AUTHOR_ID, subquery);

List authors = AuthorPeer.doSelect(criteria);

Referencing the outer select from subselects

In subselects, columns from the outer query can be referenced. For example, for selecting the authors which have written exactly one book with a given title, one could use the SQL statement

SELECT author.author_id, author.name FROM author WHERE (SELECT COUNT(*) FROM book WHERE (book.author_id=author.author_id AND book.title=?))=?

Note that in the subselect, a column from the outer select (author.author_id) is referenced but the table author does not appear in the FROM clause of the subselect (so that the database knows the outer select is referenced instead of creating an inner join in the subselect). As Torque will automatically remove tables from the outer select from the FrOM clause of the inner select, the above query can be crated by

Criteria subquery = new Criteria();
subquery.where(BookPeer.AUTHOR_ID, AuthorPeer.AUTHOR_ID);
subquery.and(BookPeer.TITLE, "SomeTitle");
subquery.addSelectColumn(new Count("*"));

Criteria criteria = new Criteria();
criteria.where(subquery, 1);

If the automatical removal of tables from the FROM clause is not desired, the FROM clause in the subselect can be specified manually using the method subquery.addFrom(...).

Subselects in the FROM clause

There are two ways to create a subselect in the FROM clause. The first way is to use the addFrom methods in the Criteria. This is not recommended as you will have to specify the FROM clause manually, which will not be as portable as the second method and also the compiler won't warn you if a column you use in the FROM clause disappears from your database model. Still, this method may be needed at times.

The second way is by specifying an alias which contains the subselect. This is the preferred way to add a subselect to the FROM clause. For example, a very complicated way to retrieve the names of all authors would be

Criteria subquery = new Criteria();
subquery.addSelectColumn(AuthorPeer.NAME);

Criteria criteria = new Criteria();
criteria.addAlias("A", subquery);
criteria.addSelectColumn(new ColumnImpl("A" + AuthorPeer.NAME.getColumnName()));

List<String> authorNames = BasePeer.doSelect(
    criteria,
    new StringMapper(),
    AuthorPeer.getTableMap());

This code will result in the following SQL:

  SELECT A.name from (SELECT name from AUTHOR) A;

Partial selects

In a partial select, only a part of the attributes of an object is filled. This is often done for performance reasons, especially if one of the columns is a BLOB or CLOB column. A partial select is constructed by using the addSelectColumn method in the Criteria object, and then executing the criteria using one of the standard SomePeer.doSelect() methods. For example, to read all books but fill only the bookId and title attributes, use the following Criteria:

Criteria criteria = new Criteria()
    .addSelectColumn(BookPeer.BOOK_ID)
    .addSelectColumn(BookPeer.TITLE);
List<Book> books = BookPeer.doSelect(criteria);

which corresponds to the expected SQL query

SELECT BOOK.BOOK_ID, BOOK.TITLE FROM BOOK;

Two final notes:

  • The primary key of the objects should always be filled, otherwise the equals and hashCode methods will not work properly.
  • Beware when saving partially filled objects to the database. You might empty the columns you have not read in the database.

Set operations (UNION, EXCEPT, INTERSECT)

Torque supports the set operations UNION, EXCEPT and INTERSECT. MINUS is the Oracle variant of EXCEPT and is also implicitly supported; Torque knows that for oracle, EXCEPT must be converted to MINUS in SQL.

Queries using the set operators can be created using the methods union, unionAll, except, exceptAll, intersect and intersectAll in Criteria. For example:

Criteria criteria = new Criteria()
    .where(BookPeer.AUTHOR_ID, authorList.get(0).getAuthorId())
    .addSelectColumn(BookPeer.BOOK_ID);
Criteria otherCriteria = new Criteria()
    .where(BookPeer.AUTHOR_ID, authorList.get(1).getAuthorId(), Criteria.LESS_EQUAL)
    .addSelectColumn(BookPeer.BOOK_ID);
criteria.union(otherCriteria);

List<Integer> result = AuthorPeer.doSelect(criteria, new IntegerMapper());

Note that the resulting Criteria from a set operation results in a so-called composite Criteria, for which some operations (e.g. group by) cannot be performed (because the resulting SQL would make no sense). Instead, these operations must be performed on the parts of which the Criteria consists.

Locking

Pessimistically lock table rows within a transaction

Usually, the database will write lock a table row if it is updated or inserted. The lock persists until the transaction is committed. This means that other transactions which want to update the same row need to wait until the first transaction is ended.

The same effect can be achieved by doing a "select for update" query. This will also acquire a write lock for the selected table rows. For example, to write lock the book with id 5, do

Criteria criteria = new Criteria()
    .where(BookPeer.BOOK_ID, 5)
    .forUpdate();
BookPeer.doSelect(criteria);

Optimistically lock table rows between transactions

Optimistic locking means that a version column is used in an update to check that a table row was not updated since the last read. In each update, the version column is set to a new value. Also it is checked that the database contains the same value for the version column as the object to save, which means that the row was not updated since the last read.

Torque supports optimistic locking using an integer version column. Torque will automatically increase the version column by one in each update, and check in an update that the version value in memory is still the same as the value in the database.

There are two optimistic locking modes. The first mode, selectForUpdate, does a select for update using the primary key in the where clause. It then checks that a row was selected and that the version column in the database contain the same values as in the update values. If all is ok, the version column is increased in memory and an update using the increased version column is performed. If no row is found, this could mean that the row was deleted in the meantime and a NoRowsException is thrown. (Note that another reason could be that the row with the given primary key has never existed in the database.) If the version column has a different value, another process has modified the same row in the meantime and an OptimisticLockingFailedException is thrown.

The second mode, simpleSelect, does an update using an increased version column as update value, and the version column and the primary key in the where clause. It then checks whether a row was updated. If yes, all is ok, because the version value in memory matched the version column in the database for the chosen row. In this case, the version number in memory is also increased. If no row was updated, an OptimisticLockingFailedException is thrown by Torque. This could be due to the row being updated in the meantime by another process, or the row was deleted, or it never existed.

The mode simpleSelect can be chosen by setting the template option torque.om.optimisticLocking.mode to selectForUpdate when generating the om classes.

Using a Column twice in a Criteria

In Torque 3.x and in the deprecated util.Criteria object, the add methods replaced a condition which already existed in a Criteria for the same column name. This is no longer the case with the new criteria.Criterion object, so no special treatment is needed to use a column twice. The Criteria

org.apache.torque.criteria.Criteria criteria = new Criteria();
criteria.where(AuthorPeer.AUTHOR_ID, 5, Criteria.GREATER_EQUAL);
criteria.and(AuthorPeer.AUTHOR_ID, 10, Criteria.GREATER_EQUAL);

corresponds to the expected SQL query

SELECT ... FROM ... WHERE AUTHOR.AUTHOR_ID >= 5 AND AUTHOR.AUTHOR_ID <= 10;

Encapsulating and Re-using Criteria code

The Criteria Object can be verbose to use directly in your code. Often in an application the 80:20 rule applies when dealing with queries. The same 20% of queries are used 80% of the time. While Criteria and Criterion offer a lot of flexibility, often having something simple to use is easier.

Also, it is often desirable in an application to separate code which is used to access the database from the other application code. For example, the application may need to retrieve an author by his last name. One can, of course, construct a Criteria in the application code and use AuthorPeer's doSelect() method, but from an encapsulation point of view, this would better be done in the database layer of the application.

So there are basically two approaches to this. The first one is adding additional methods to the Peer classes, the second one is writing custom filter classes, and the third one is inheriting from the Criteria object. Usually, the first approach is preferable because it offers better encapsulation. The second approach is good if you want more flexibility but still want to retain encapsulation of the database layer, and the third is good if you want all flexibility of the Criteria object but still want to re-use code.

Additional methods in Peers

To achieve encapsulation and code reuse for queries, you can extend the Peer classes. For example, if you need to select authors by last name, you could extend the AuthorPeer class in the following way:

/**
 *  Returns all the authors with a last name equal to lastName.
 *  @param lastName the last name of the authors to select.
 */
public static List doSelectByLastName(String lastName)
{
    Criteria criteria = new Criteria();
    criteria.where(AuthorPeer.LAST_NAME, lastName);
    List result = AuthorPeer.doSelect(criteria);
    return result;
}

Custom filter classes

Custom filter classes are an encapsulation of the data one searches for. They can create a criteria from their data. Custom filter classes are often used if you present a "search mask" to the user, where you want to retain the information the user has entered anyway. For example, if you have a search mask for looking for authors using their first and last name, you would use the following code:

/**
 *  Contains the data needed to find authors using their names.
 *  Can create a criteria object which can be used to find the desired 
 *  authors in the database.
 */
public class AuthorFilter
{
    /** the first name to look for. */
    private String firstName = null;
    
    /** the last name to look for. */
    private String LastName = null;
    
    /**
     *  Creates a AuthorFilter looking for the firstName and lastName of the 
     *  author.
     *  @param firstName the first name of the authors to look for, 
     *         or null for all first names
     *  @param lastName the last name of the authors to look for,
     *         or null for all last names 
     */
    public AuthorFilter(String firstName, String lastName)
    {
        this.firstName = firstName;
        this.lastName = lastName;
    }
    
    /**
     *  returns the Criteria for the data in the filter.
     *  @return the Criteria which returns the specified authors
     */
    public Criteria getCriteria()
    {
        Criteria result = new Criteria();
        if (firstName != null)
        {
            result.where(AuthorPeer.FIRST_NAME, firstName);
        }
        if (lastName != null)
        {
            result.where(AuthorPeer.LAST_NAME, lastName);
        }
        return criteria;
    }
}

In reality, you would also add getters and setters for first and last name, and allow for wildcards and case-insensitive search, but this example should show the basic idea.

Inheriting from Criteria

In some special cases, it might make sense to create a class that extends Criteria and add convenience methods. For example:

import org.apache.torque.criteria.Criteria;

public class CompareCriteria extends Criteria
{
    /*
     * Represents the Greater Than in the WHERE
     * clause of an SQL Statement
     *
     * @param columnname the column name
     * @param columnvalue the column value to be compared against
     */
    public CompareCriteria greaterThan(Column column, int columnvalue)
    {
        super.where(column, columnvalue, Criteria.GREATER_THAN);
        return this;
    }

   /*
     * Represents the Between in the WHERE
     * clause of an SQL Statement
     *
     * @param columnname the column name
     */
    public CompareCriteria isBetween(Column column, int min, int max)
    {
        super.where(column, min, Criteria.GREATER_THAN);
        super.and(column, max, Criteria.LESS_THAN);
        return this;
    }

}

Debugging Criteria's

Criteria contains a toString() method which will output a representation of the Criteria as a String. A Criteria to a large extent represents the statements in a WHERE clause of SQL. However, there might be details which are hidden when invoking the Criteria.toString() method. In this case, you can configure the logging system to capture the SQL by adding the following to your log4j.properties file:

log4j.logger.org.apache.torque.util = DEBUG

Accessing Multiple Databases

By default, when querying a table using a Peer class, the Peer class will use the name of the database schema file as database handle to establish a database connection. However, any database handle can be used by calling the method Criteria.setDbName(String) with the database handle key as argument. Changing the database handle is e.g. useful in a master-slave environment where tables in different databases have the same structure and thus can be queried using the same peer class.

log4j.logger.org.apache.torque.util = DEBUG

Examples

Putting JOIN, DISTINCT and ORDER BY together

To put all the above together, we can query the bookstore database for all authors which have published a book, and order them by the author's name. This requires a join between the book table and the author table.

Criteria criteria = new Criteria();
criteria.addJoin(AuthorPeer.AUTHOR_ID, BookPeer.AUTHOR_ID, Criteria.INNER_JOIN);
criteria.setDistinct();
criteria.addAscendingOrderByColumn(AuthorPeer.NAME);

List bookAuthors = AuthorPeer.doSelect(criteria);