<?xml version="1.0"?>
<!--
 Licensed to the Apache Software Foundation (ASF) under one
 or more contributor license agreements.  See the NOTICE file
 distributed with this work for additional information
 regarding copyright ownership.  The ASF licenses this file
 to you under the Apache License, Version 2.0 (the
 "License"); you may not use this file except in compliance
 with the License.  You may obtain a copy of the License at

   http://www.apache.org/licenses/LICENSE-2.0

 Unless required by applicable law or agreed to in writing,
 software distributed under the License is distributed on an
 "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 KIND, either express or implied.  See the License for the
 specific language governing permissions and limitations
 under the License.
-->

<document>
 <properties>
   <title>Torque ORM Reference - Reading from the database</title>
   <author email="leon@opticode.co.za">Leon Messerschmidt</author>
   <author email="jvanzyl@periapt.com">Jason van Zyl</author>
   <author email="seade@backstagetech.com.au">Scott Eade</author>
   <author email="fischer@seitenbau.de">Thomas Fischer</author>
 </properties>

 <body>

  <section name="Reading from the Database">
    <p>
      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.
    </p>
    <p>
      Note that there are two criteria classes in the runtime:
      <code>org.apache.torque.criteria.Criteria</code> and 
      <code>org.apache.torque.util.Criteria</code>.
      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 
      <a href="migration-from-torque-3.html">migration guide</a>
      for details).
      All examples in this document use the new criteria.Criteria object.
    </p>
    <p>
      All following examples are using generated classes 
      (AuthorPeer, author, BookPeer, Book,...)from the
      <a href="../tutorial/orm/index.html">ORM tutorial</a>.
      Please generate those classes following the tutorial if you want to try
      these examples.
    </p>

  </section>

  <section name="Reading all Objects">

    <p>
      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"
    </p>
  
    <p>  
      For example, the following code can be used to read all 
      Authors from the Author table.
    </p>

<source>
Criteria criteria = new Criteria();
List authors = AuthorPeer.doSelect(critieria);
</source>

  </section>
  
  <section name="Specifying which objects should be read">

    <p>
      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.
    </p>
    
    <p> 
      As an example, to read all Authors which have the LAST_NAME field filled 
      with "Stevens", the following code can be used: 
    </p>

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

    <p>
      To use another comparison operator than EQUAL (=), you need to specify 
      which operator should be used. Valid comparison operators are:
      <ul>
        <li>
          <code>Criteria.EQUAL</code> (Default)
        </li>
        <li>
          <code>Criteria.NOT_EQUAL</code> (&lt;&gt;)
        </li>
        <li>
          <code>Criteria.ALT_NOT_EQUAL</code> (!=) (not needed, 
          in the suported dbs use <code>Criteria.NOT_EQUAL</code>)
        </li>
        <li>
          <code>Criteria.EQUALS</code> (Default)
        </li>
        <li>
          <code>Criteria.GREATER_THAN</code>
        </li>
        <li>
          <code>Criteria.LESS_THAN</code>
        </li>
        <li>
          <code>Criteria.GREATER_EQUAL</code>
        </li>
        <li>
          <code>Criteria.LESS_EQUAL</code>
        </li>
        <li>
          <code>Criteria.IN</code>
        </li>
        <li>
          <code>Criteria.NOT_IN</code>
        </li>
        <li>
          <code>Criteria.LIKE</code>
        </li>
        <li>
          <code>Criteria.NOT_LIKE</code>
        </li>
      </ul>
    </p>
    
    <p>
      For example, to read all authors with an id less than 5, use
    </p>

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

  </section>

  <section name="AND and OR operators">
  
    <p>
      If you add multiple constraints to a Criteria, they are linked by default 
      by a logical "AND" operator. For example, the code
    </p>
    
<source>
Criteria criteria = new Criteria();
Criteria.where(AuthorPeer.LAST_NAME, "Stevens");
Criteria.where(AuthorPeer.FIRST_NAME, "W.");
List authors = AuthorPeer.doSelect(criteria);
</source>
      
    <p>
      results in the following SQL query:
    </p>
    
<source>
SELECT ... from AUTHOR where LAST_NAME='Stevens' AND FIRST_NAME='W.'
</source>

    <p>
      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 
      <code>Criteria.and()</code> and 
      <code>Criteria.or()</code>.
      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).
    </p>
    <p>
      For example, to produce the following SQL query:
    </p>
<source><![CDATA[
select * from abc where (a < 1 and b > 2) or (a > 5)
]]></source>

    <p>
      you can use the code
    </p>
    
<source><![CDATA[
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);
]]></source>

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

    <p>
      For example, the Criteria which corresponds to the SQL query
    </p>

<source><![CDATA[
select * from abc where (a < 1 and b > 2) or (a > 5 and b < 3)
]]></source>

    <p>
      is
    </p>

<source><![CDATA[
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)));
]]></source>

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

  </section>

  <section name="Foreign keys">

    <p>
      Foreign keys define a link between rows in different tables.  They are
      defined in the database schema on generation time.  If the generator
      option <code>torque.om.complexObjectModel</code> is not explicitly
      set to false, getters and setters are generated for linked objects.
    </p>
    
    <p>
      For example, in the Bookstore schema, a foreign key is defined which
      creates a link between the book and the author tables:
    </p>

<source><![CDATA[
<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>
]]></source>

    <p>
      The foreign key defines that a book is associated to an author 
      by connecting the <code>author_id</code> column of the book table
      to the primary key of the corresponding author table.
    </p>
    
    <p>
      In the Book Object, the Torque Generator generates the methods
      <code>getAuthor()</code> and <code>setAuthor(Author author)</code> 
      which can be used to access and set the corresponding author object.
      In the Author object, the methods <code>getBooks()</code> and 
      <code>addBook()</code> are generated. These methods can also be used
      to get the connected book objects or to create a connection, respectively.
    </p>
    
    <p>
      By default, the getters for collections query the database for the 
      corresponding objects if they have not been read already.  For example,
      the method <code>Author.getBooks()</code> 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
      <code>torque.om.silentDbFetch</code> to <code>false</code>.
    </p>

    <p>
      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:
    </p>

<source><![CDATA[
<database ...>

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

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

    <p>
      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 <code>required</code> 
      of the foreign key column <code>book.author_id</code> 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 <code>author_id</code> 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:
    </p>
    
<source><![CDATA[
<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>
]]></source>
    <p>
      Of course, the foreign key column in the book column must be removed
      for the n:m association.
    </p>
    
  </section>

  <section name="Joins">

    <p>
      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.
    </p>
    
    <subsection name="Joins to read corresponding rows" >

      <p>
        To read corresponding rows, <code>doSelectJoin&lt;tablename&gt;</code>
        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.
      </p>
    
      <p>
        For example, to read all Authors and their corresponding Books,
        override the <code>doSelectJoinBooks()</code> method in the 
        Author peer with the following code:
      </p>

<source>
public List doSelectJoinBooks(Criteria criteria) throws TorqueException
{
    return super.doSelectJoinBooks(criteria);
}
</source>

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

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

      <p>
        Note that an inner join is used for reading the datasets, so no authors
        are returned if no book is found.
      </p>
    
      <p>
        You can also put constraints on the related books.  For example, to read 
        only the related book with the title 
        &quot;TCP/IP Illustrated, Volume 1&quot;:
      </p>

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

      <p>
        The <code>doSelectJoin&lt;tablename&gt;</code> only read one related
        table at once.  If there are more than 2 foreign keys in one table,
        also <code>doSelectJoinAllExcept&lt;tablename&gt;</code> methods 
        are generated, which read all related objects except the one
        in the method names.
      </p>
    
      <p>  
        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
        <em>and</em> table C in one single select.
        However, you can look at the generated code of the
        <code>doSelectJoin&lt;tablename&gt;</code> 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.
      </p>
      
    </subsection>
    
    <subsection name="Inner joins for qualifying">
    
      <p>
        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.
      </p>
    
      <p>
        For example, we want to know all authors which have published at least 
        one book.  This can be achieved using the following code:
      </p>

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

List bookAuthors = AuthorPeer.doSelect(criteria);</source>

      <p>
        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 &quot;Using DISTINCT&quot; below for a solution).
      </p>

      <table>
        <tr>
          <th>Details</th>
        </tr>
        <tr>
          <td>
            In SQL, there are two different ways to state an inner join.
            The first way is a statement like
<source>
SELECT BOOK.* FROM BOOK INNER JOIN AUTHOR ON BOOK.AUTHOR_ID=AUTHOR.AUTHOR_ID
</source>
            SQL like this is produced if the join type is stated explicitly
            (as above).<br />
            The second way to create an inner join is
<source>
SELECT BOOK.* FROM BOOK,AUTHOR WHERE BOOK.AUTHOR_ID=AUTHOR.AUTHOR_ID
</source>
            A SQL statement like this is created by not supplying the join type,
            for example in
<source>
criteria.addJoin(AuthorPeer.AUTHOR_ID, BookPeer.AUTHOR_ID);</source>
            Note that both SQL statements return the same result set.<br/>
            It is not possible to combine both ways of creating a join
            in most databases, i.e. code like
<source>
criteria.addJoin(AuthorPeer.AUTHOR_ID, BookPeer.AUTHOR_ID);
criteria.addJoin(BookPeer.PUBLISHER_ID, PublisherPeer.PUBLISHER_ID, Criteria.INNER_JOIN);
</source>
            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.
          </td>
        </tr>
      </table>
    </subsection>

    <subsection name="Outer joins for qualifying">
      <p>
        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.
      </p>

      <p>
        First, let us reconsider the inner join code:
      </p>

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

List bookAuthors = AuthorPeer.doSelect(criteria);
</source>

      <p>
        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:
      </p>

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

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

      <p>
        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.
      </p>
    </subsection>

  </section>

  <section name="Fillers">

    <p>
      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
      <code>SELECT * FROM BOOK WHERE AUTHOR_ID IN(1,3,7)</code>.
      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.
    </p>

    <p>
      Torque can generate filler methods which use the IN approach.
      To generate these methods, the generation option 
      <code>torque.om.complexObjectModel.generateFillers</code>
      needs to be set to true for the generation process.
      Then, for the author-book example, Torque will generate a method
      fillAuthors(List&lt;Book&gt;) in the BookPeer class and a method
      fillBooks(List&lt;Author&gt;) 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.
    </p>

    <p>
      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.
    </p>
    <p>
      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.
    </p>
  </section>

  <section name="Using DISTINCT">

    <p>
      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.
    </p>
    
    <p>
      In the first example in the section 
      &quot;Inner joins for qualifying&quot;, 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:
    </p>

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

List bookAuthors = AuthorPeer.doSelect(criteria);
</source>

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

  </section>

  <section name="Ordering the Results">

   <p>
     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:
   </p>

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

List books = BookPeer.doSelect(criteria);</source>

  </section>

  <section name="Case insensitivity">

    <p>
      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:
    </p>

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

List invoices = InvoicePeer.doSelect(criteria);
</source>

    <p>
      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).
    </p>
    
    <p>
      You can also use <code>Criteria.setIgnoreCase(true)</code> to make all 
      Criterions in the Criteria object case insensitive.
    </p>
    
  </section>

  <section name="Using custom SQL">

    <p>
      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: <code>andVerbatimSql()</code>,
      <code>orVerbatimSql()</code>, and <code>whereVerbatimSql()</code>. 
    </p>
    <p>
      For demonstration purposes, let us construct a query which retrieves
      the author with the AUTHOR_ID of 5: 
    </p>

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

    <p>
      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:
    </p>

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

    <p>
      It is recommended <em>not</em> 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.
    </p>
    
    <p>
      The old way of specifying custom SQL using Criteria.CUSTOM still exists,
      however this does not allow to use Prepared Statement replacements.
    </p>
    
  </section>

  <section name="Subselects in the WHERE clause">
    <p>
      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.
    </p>
    
    <p>
      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).
    </p>
    
    <p>
      As an example, assume we want to read the author with the largest authorId
      from the database. This can be done as follows:
    </p>
    
<source>
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);
</source>

    <subsection name="Referencing the outer select from subselects">
      <p>
        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
      </p>
    
<source>
SELECT author.author_id, author.name FROM author WHERE (SELECT COUNT(*) FROM book WHERE (book.author_id=author.author_id AND book.title=?))=?
</source>

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

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

      <p>
        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
        <code>subquery.addFrom(...)</code>.
      </p>
    </subsection>
  </section>

  <section name="Subselects in the FROM clause">
    <p>
      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.
    </p>
    
    <p>
      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
    </p>
    
<source><![CDATA[
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());
]]></source>
    <p>
      This code will result in the following SQL:
    </p>
<source>
  SELECT A.name from (SELECT name from AUTHOR) A;
</source>
  </section>

  <section name="Partial selects">

    <p>
      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:
   </p>

<source>
Criteria criteria = new Criteria()
    .addSelectColumn(BookPeer.BOOK_ID)
    .addSelectColumn(BookPeer.TITLE);
List&lt;Book&gt; books = BookPeer.doSelect(criteria);
</source>

    <p>
      which corresponds to the expected SQL query
   </p>
   
<source>
SELECT BOOK.BOOK_ID, BOOK.TITLE FROM BOOK;
</source>

    <p>
      Two final notes:
   </p>
   
   <ul>
     <li>
       The primary key of the objects should always be filled,
       otherwise the equals and hashCode methods will not work properly.
     </li>
     <li>
       Beware when saving partially filled objects to the database.
       You might empty the columns you have not read in the database.
     </li>
   </ul>
    
  </section>

  <section name="Set operations (UNION, EXCEPT, INTERSECT)">
    <p>
      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.
    </p>
    
    <p>
      Queries using the set operators can be created using the methods 
      union, unionAll, except, exceptAll, intersect and intersectAll
      in Criteria.
      For example:
    </p>
    
<source><![CDATA[
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());

]]></source>
    <p>
      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.
      
    </p>
  </section>

  <section name="Locking">
    <subsection name="Pessimistically lock table rows within a transaction">
      <p>
        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.
      </p>
      <p>
        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
      </p>
<source>
Criteria criteria = new Criteria()
    .where(BookPeer.BOOK_ID, 5)
    .forUpdate();
BookPeer.doSelect(criteria);
</source>
    </subsection>
    <subsection name="Optimistically lock table rows between transactions">
      <p>
        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.
      </p>
      <p>
        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.
      </p>
      <p>
        There are two optimistic locking modes. 
        The first mode, <code>selectForUpdate</code>,
        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 <code>NoRowsException</code> 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 
        <code>OptimisticLockingFailedException</code> is thrown.
      </p>
      <p>
        The second mode, <code>simpleSelect</code>,
        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 <code>OptimisticLockingFailedException</code>
        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.
      </p>
      <p>
        The mode <code>simpleSelect</code> can be chosen by setting the
        template option <code>torque.om.optimisticLocking.mode</code>
        to <code>selectForUpdate</code> when generating the om classes.
      </p>
    </subsection>
  </section>

  <section name="Using a Column twice in a Criteria">

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

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

    <p>
      corresponds to the expected SQL query
   </p>
   
<source>
SELECT ... FROM ... WHERE AUTHOR.AUTHOR_ID &gt;= 5 AND AUTHOR.AUTHOR_ID &lt;= 10;
</source>

  </section>

  <section name="Encapsulating and Re-using Criteria code">

    <p>
      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.
    </p>
    
    <p>
      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 <code>AuthorPeer</code>'s <code>doSelect()</code>
      method, but from an encapsulation point of view, this would better be done
      in the database layer of the application.
    </p>
    
    <p>
      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.
    </p>
    
    <subsection name="Additional methods in Peers">
      
      <p>
        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:
      </p>
      
<source>
/**
 *  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;
}
</source>

    </subsection>

    <subsection name="Custom filter classes">
      
      <p>
        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: 
      </p>
      
<source>
/**
 *  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;
    }
}
</source>

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

    </subsection>

    <subsection name="Inheriting from Criteria">
    
      <p>
        In some special cases, it might make sense to create a class
        that extends Criteria and add convenience methods.
        For example:
      </p>

<source>
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;
    }

}
</source>
    </subsection>
  </section>

  <section name="Debugging Criteria's">

    <p>
      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 <code>log4j.properties</code>
      file:
    </p>

<source>
log4j.logger.org.apache.torque.util = DEBUG
</source>

  </section>

  <section name="Accessing Multiple Databases">

    <p>
      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
      <code>Criteria.setDbName(String)</code> 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.
    </p>

<source>
log4j.logger.org.apache.torque.util = DEBUG
</source>

  </section>

  <section name="Examples">

    <subsection name="Putting JOIN, DISTINCT and ORDER BY together">
      <p>
        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.
      </p>

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

    </subsection>

  </section>

 </body>
</document>
