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.

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

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

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

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

List bookAuthors = AuthorPeer.doSelect(criteria);

SELECT BOOK.* FROM BOOK INNER JOIN AUTHOR ON BOOK.AUTHOR_ID=AUTHOR.AUTHOR_ID

SELECT BOOK.* FROM BOOK,AUTHOR WHERE BOOK.AUTHOR_ID=AUTHOR.AUTHOR_ID

criteria.addJoin(AuthorPeer.AUTHOR_ID, BookPeer.AUTHOR_ID);

criteria.addJoin(AuthorPeer.AUTHOR_ID, BookPeer.AUTHOR_ID);
criteria.addJoin(BookPeer.PUBLISHER_ID, PublisherPeer.PUBLISHER_ID, Criteria.INNER_JOIN);

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

List bookAuthors = AuthorPeer.doSelect(criteria);

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

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);

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

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);

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

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

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.

/**
 *  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;
}

/**
 *  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;
    }
}

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

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);