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.

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.add(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 (!=)
• 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.add(AuthorPeer.AUTHOR_ID, 5, Criteria.LESS_THAN);
List authors = AuthorPeer.doSelect(criteria);

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 complexObjectModel is not 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 setting the author_id column of the author to the id of the corresponding author.

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.

Note that by default, the getters query the database for the corresponding objects if they hev 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.silentDbFetch to false.

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.add(AuthorPeer.LAST_NAME, "Stevens");
List authorAndBooks = AuthorPeer.doSelectJoinBooks(criteria);

Criteria criteria = new Criteria();
criteria.add(AuthorPeer.LAST_NAME, "Stevens");
criteria.add(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);

Using DISTINCT with Criteria

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 will appear for every book that is attached to it. If an author has published 10 books, the author will appear in the returned List 10 times. To avoid this problem so that our returned List only returns one author once despite the number of times it appears, we can 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(String columnname) and addDescendingOrderByColumn(String columnname) 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 accout for equal titles), use the following code:

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

List books = BookPeer.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.add(AuthorPeer.LAST_NAME, "Stevens");
Criteria.add(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.'

To exlicitly specify which operator should be used to link the constraints in a Criteria, use the methods Criteria.Criterion.and() and Criteria.Criterion.or().

For example, the criterion 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();
Criteria.Criterion a1 = crit.getNewCriterion(ABC.A, 1, Criteria.LESS_THAN);
Criteria.Criterion b2 = crit.getNewCriterion(ABC.B, 2, Criteria.GREATER_THAN);
Criteria.Criterion a5 = crit.getNewCriterion(ABC.A, 5, Criteria.GREATER_THAN);
Criteria.Criterion b3 = crit.getNewCriterion(ABC.B, 3, Criteria.LESS_THAN);

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

Note that the tables used in the last example are not defined in the bookstore schema in the tutorial.

Case insensitivity

String comparisons is are usually case sensitive (unless the underlying database only provides case sensitive LIKE clauses - e.g. MySQL). To get a case insensitive comparison, you need to tell the criteria that it should ignore the case thus:

Criteria criteria = new Criteria();
criteria.add(InvoicePeer.TABLE_NAME, searchField,
        (Object) ("%" + searchCriteria + "%"), Criteria.LIKE);
criteria.getCriterion(InvoicePeer.TABLE_NAME, searchField).setIgnoreCase(true);

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 the CUSTOM modifier to use custom SQL

Although Criteria provide for the most common SQL queries, some queries can not be created using standard Criteria methods. With the CUSTOM modifier, the specified SQL is directly used in the where clause. For internal reasons, you must still specify a column on which the query part is acting.

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

Criteria criteria = new Criteria();
criteria.add(AuthorPeer.AUTHOR_ID, (Object)"AUTHOR_ID=5", Criteria.CUSTOM);
List authors = AuthorPeer.doSelect(criteria);

Note that in this specific example, there was no need for the CUSTOM modifier. The same query could have been created by:

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

It is recommended not to use the CUSTOM modifier whenever you can avoid it. If you use the CUSTOM modifier, 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.

Using Criterion to use a Column twice in a Criteria

The Criteria Object extends the functionality of a Hashtable and as such suffers from the Hashtable limitation of the key having to be unique in the Hashtable. When a Criteria is set to use the same column twice, it overwrites the previous key. The way around this is to use the Criterion Object. The Criterion is a final inner class of Criteria. Because it is a member class the Criterion can "look" into Criteria's instance fields and methods including ones declared private. The Criterion also carries the default package visibility which means it can be used in a sub-class of Criteria.

For example, we may want to search for authors which Author Ids are within a certain Range, such as 5 and 10. For this we would need to use the Criterion:

Criteria criteria = new Criteria();
criteria.add(AuthorPeer.AUTHOR_ID, 5, Criteria.GREATER_EQUAL);

Criteria.Criterion criterion = criteria.getCriterion(AuthorPeer.AUTHOR_ID);
criterion.and(
        criteria.getNewCriterion(
                 criterion.getTable(),
                 criterion.getColumn(),
                 new Integer(10),
                 Criteria.LESS_EQUAL)
             );

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 tonne 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 accesss 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 approaces 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.add(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.add(AuthorPeer.FIRST_NAME, firstName);
        }
        if (lastName != null)
        {
            result.add(AuthorPeer.LAST_NAME, lastName);
        }
        return criteria;
    }
}

//Turbine
import org.apache.torque.util.Criteria;

/**
 * SimpleCriteria is a simple case of the more powerful Criteria
 * Object.
 */

public class SimpleCriteria extends Criteria
{
    /** currently used as DEFAULT_CAPACITY in Criteria is private */
    private static final int DEFAULT_CAPACITY = 10;

    /*
     * Constructor
     */
    public SimpleCriteria()
    {
        super(DEFAULT_CAPACITY);
    }

    /*
     * 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 SimpleCriteria greaterThan(String columnname, int columnvalue)
    {
        super.add(columnname, columnvalue, Criteria.GREATER_THAN);
        return this;
    }

   /*
     * Represents the Between in the WHERE
     * clause of an SQL Statement
     *
     * @param columnname the column name
     */
    public SimpleCriteria isBetween(String columnname, int min, int max)
    {
        super.add(columnname, min, Criteria.GREATER_THAN);
        super.Criterion criterion = criteria.getCriterion(columnname);
        criterion.and(
                   super.getNewCriterion(
                              criterion.getTable(),
                              criterion.getColumn(),
                              new Integer(max),
                              Criteria.LESS_EQUAL )
                    );
        return this;
    }

}

SimpleCriteria criteria = new SimpleCriteria();
criteria.isBetween(AuthorPeer.AUTHOR_ID, 5, 10);

List authors = AuthorPeer.doSelect(criteria);

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. To see the queries being processed by your application 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

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