Criteria Howto

- For a basic description and examples of the Criteria Object with Peers - please view the Peers Howto document. This - document intends to show more advanced techniques using Criteria, such as - comparators and joins. As always, for more information on the methods - available in the Criteria Object, view the javadocs. -

+ 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. +

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

+ 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(); -criteria.addAscendingOrderByColumn(BookPeer.TITLE); -criteria.addAscendingOrderByColumn(BookPeer.ISBN); +List authors = AuthorPeer.doSelect(critieria); + -List books = BookPeer.doSelect(criteria); +

+ +

+ 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 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: +

+ -

+ + + ... +

+ + ... + + ... + + + +

+ +]]> -

- In a relational database, there are two different join types: - inner joins and outer joins. Both types can be generated using Torque - and will be discussed seperately in the following: -

+ 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. +

+ +

+ 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. +

+ + + +

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

- Imagine we want to know all authors which have published at least one book. - This can be achieved using the following code: + Then, you can read the author with the last Name "Stevens" and all his + books using the following code:

- + +Criteria criteria = new Criteria(); +criteria.add(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 witht the title + "TCP/IP Illustrated, Volume 1": +

+ + +Criteria criteria = new Criteria(); +criteria.add(AuthorPeer.LAST_NAME, "Stevens"); +criteria.add(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 way + to read the data in table A and the related datasests in table B + and table C in one single select. +

+ + + + + +

+ 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); @@ -90,72 +317,56 @@ - All joins which are created using Criteria are only used for data selection, - not for reading the related objects. - For example, in the above code, only author objects are read in from the database. - The book objects which are related to the authors are not read, - meaning that - -Author author = (Author) bookAuthors.get(0); -List books = author.getBooks(); - performs a background read in the database to get all the books for one author. - - If you do not like this behaviour, there are also methods which do read the - related objects, see for example BaseBookPeer.doSelectJoinAuthor(). - These methods are protected to keep the public API reasonable. - If you need public access to such a method, simply overwrite the method in - the Peer class and declare the method public. Note that these methods perform - inner joins (this behavour will hopefully be changed in the near future). - - - - - Also, 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 + Also, 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 + +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.
+ 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); +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. + However, if the "INNER JOIN" syntax is not supported by your + database, try not to state the join type explicitly. - - +

- 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. + 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); +List bookAuthors = AuthorPeer.doSelect(criteria); +

For an inner join, the database only returns an entry @@ -166,10 +377,11 @@ 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); +List authors = AuthorPeer.doSelect(criteria); +

The above code uses a left (outer) join. There, authors @@ -184,7 +396,7 @@

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

@@ -193,134 +405,127 @@

- All of the examples in the section "joins" return multiples - of the desired table columns. - In the first example, 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: -

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

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

+ 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.addJoin(AuthorPeer.AUTHOR_ID, BookPeer.AUTHOR_ID, Criteria.INNER_JOIN); -criteria.setDistinct(); -criteria.addAscendingOrderByColumn(AuthorPeer.NAME); +criteria.addAscendingOrderByColumn(BookPeer.TITLE); +criteria.addAscendingOrderByColumn(BookPeer.ISBN); -List bookAuthors = AuthorPeer.doSelect(criteria); +List books = BookPeer.doSelect(criteria);

- To use a subselect, 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: + If you add multiple constraints to a Criteria, they are linked by default + by a logical "AND" operator. For example, the code

-Criteria subquery = new Criteria(); -subquery.addSelectColumn("MAX(" + AuthorPeer.AUTHOR_ID + ")"); - Criteria criteria = new Criteria(); -criteria.add(AuthorPeer.AUTHOR_ID, subquery); - +Criteria.add(AuthorPeer.LAST_NAME, "Stevens"); +Criteria.add(AuthorPeer.FIRST_NAME, "W."); List authors = AuthorPeer.doSelect(criteria); -

- -

- The Criteria Object has the following camparators: -

- + +

+ results in the following SQL query: +

+ -Criteria.ALT_NOT_EQUAL -Criteria.CUSTOM -Criteria.DISTINCT -Criteria.EQUAL -Criteria.GREATER_EQUAL -Criteria.GREATER_THAN -Criteria.IN -Criteria.JOIN -Criteria.LESS_EQUAL -Criteria.LESS_THAN -Criteria.LIKE -Criteria.NOT_EQUAL -Criteria.NOT_IN +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(). +

The comparators can be used to return results that satisfy the chosen - comparisons. As an example, assume we have Invoice OM and Peer Objects that - map to an invoice table in a database. The invoice table contains the - columns, INVOICE_ID, COST, DATE and DESCRIPTION. Where the id is an integer, - the cost a double, the date an mysql DATETIME and the Description a VARCHAR. -

+ For example, the criterion which corresponds to the SQL query +

- In the case of an invoice, we may need to know all the invoices that are - above a certain limit. Where the limit is greater than $1000, this could be - done via: -

+ 2) or (a > 5 and b < 3) +]]> - -Criteria criteria = new Criteria(); -criteria.add(InvoicePeer.COST, 1000, Criteria.GREATER_THAN); +

+ is +

-List invoices = InvoicePeer.doSelect(criteria); - + - This will return a Vector of Invoice OM Objects which have cost values - greater than $1000. The other comparitors work similarly and can be used in - the same manner though many of the comparators are present as methods in - the Criteria Object already, such as the Joins. -

+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. +

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

+ 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(); @@ -331,83 +536,227 @@ 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). -

- +

+ 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. +

- There is currently no conditional operator for NULL and NOT NULL as the - database implementations vary fairly widely. The way to solve it for a - particular application is through the CUSTOM comparitor. Using the Invoice - example again to check if the description is null; -

+ 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(InvoicePeer.DESCRIPTION, (Object)"DESCRIPTION is NULL", Criteria.CUSTOM); +criteria.add(AuthorPeer.AUTHOR_ID, (Object)"AUTHOR_ID=5", Criteria.CUSTOM); +List authors = AuthorPeer.doSelect(criteria); + -List invoices = InvoicePeer.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. +

- 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. +

+ 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.

- Using the Invoice table and OM Object again, we may want to search on the - Invoice Amounts within a certain Range, such as $1000 and $5000. For this - we would need to use the Criterion: -

+ 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(InvoicePeer.COST, 1000, Criteria.GREATER_EQUAL); +criteria.add(AuthorPeer.AUTHOR_ID, 5, Criteria.GREATER_EQUAL); -Criteria.Criterion criterion = criteria.getCriterion(InvoicePeer.COST); +Criteria.Criterion criterion = criteria.getCriterion(AuthorPeer.AUTHOR_ID); criterion.and( - criteria.getNewCriterion( - criterion.getTable(), - criterion.getColumn(), - new Integer(5000), - Criteria.LESS_EQUAL ) - ); + criteria.getNewCriterion( + criterion.getTable(), + criterion.getColumn(), + new Integer(10), + Criteria.LESS_EQUAL) + );

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

+ 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. +

+ + + +

+ 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 author 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.add(AuthorPeer.LAST_NAME, lastName); + List result = AuthorPeer.doSelect(criteria); + return result; +} + -

- One way to achieve this is to create a class that extends Criteria and add - convenience methods for your application or are specific to your database. - In this case the example Object will be the SimpleCriteria with the methods that - allow access to the examples above. -

+ + + + +

+ 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.add(AuthorPeer.FIRST_NAME, firstName); + } + if (lastName != null) + { + result.add(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 serach, + but this example should show the basic idea. +

+ + + + + +

+ Another way to achieve code-reuse is to create a class that extends + Criteria and add convenience methods for your application. + In this case the example Object will be the SimpleCriteria with the + methods that allow access to the examples above. +

//Turbine @@ -445,19 +794,7 @@ } /* - * Represents the Is NULL in the WHERE - * clause of an SQL Statement - * - * @param columnname the column name - */ - public SimpleCriteria isNull(String columnname) - { - super.add(columnname, (columnname + " is NULL"), Criteria.CUSTOM); - return this; - } - - /* - * Represents the Is NULL in the WHERE + * Represents the Between in the WHERE * clause of an SQL Statement * * @param columnname the column name @@ -479,37 +816,65 @@ } -

- This will simplify the code being written in the Business Objects or - Actions and condense all the Criteria knowledge into the SimpleCriteria - Object. The SimpleCriteria Object used in the same manner as Criteria. - For example the initial -

+ This will simplify the code being written in the Business Objects or + Actions and condense all the Criteria knowledge into the SimpleCriteria + Object. The SimpleCriteria Object used in the same manner as Criteria. + For example, the example from the section + "Using Criterion to use a Column twice in a Criteria" + above would be +

SimpleCriteria criteria = new SimpleCriteria(); -criteria.isBetween(InvoicePeer.COST, 1000, 5000); +criteria.isBetween(AuthorPeer.AUTHOR_ID, 5, 10); -List invoices = InvoicePeer.doSelect(criteria); +List authors = AuthorPeer.doSelect(criteria); + +

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

+ 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.BasePeer = DEBUG +log4j.logger.org.apache.torque.util = DEBUG

+ + +

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

+ - + \ No newline at end of file Added: db/torque/runtime/trunk/xdocs/reference/relevant-classes.xml URL: http://svn.apache.org/viewcvs/db/torque/runtime/trunk/xdocs/reference/relevant-classes.xml?rev=371077&view=auto ============================================================================== --- db/torque/runtime/trunk/xdocs/reference/relevant-classes.xml (added) +++ db/torque/runtime/trunk/xdocs/reference/relevant-classes.xml Sat Jan 21 07:46:38 2006 @@ -0,0 +1,243 @@ + + + + + + Torque Runtime Reference - Relevant classes and Resources + Leon Messerschmidt + Jason van Zyl + Scott Eade + + + +

+ Everything in Peers resolve around Peer classes. A Peer class has a + one-to-one mapping to a Database table. You use each table's associated + Peer class to do operations on that table. Peer classes are generated + for you automatically. +

+ +

+ Peer classes have static methods only, so you would never create objects + of Peer classes. It is not necessary to have objects on this level + because of the one-to-one mapping with a table. Peer methods are thread + safe. +

+ +

+ Peer classes are generated for you automatically. For each table, two + Peer classes are generated: Base<table-name>Peer and + <table-name>Peer. The Base<table-name>Peer class contains + all the functionality and should not be changed. The other class is + empty but can be extended to add or change functionality. If you + regenerate with torque only the Base* class changes. This allows you + to change the schema, but still keep your existing code. +

+ +

+ A Data Object holds information about a single row of a specific table. + Data Objects can be generated automatically for you. It takes the form + of Bean properties for each field of the table. +

+ +

+ The Data Object classes also generated automatically. For each table, two + Data Object classes are generated: Base<table-name> and + <table-name>. As with the Peers, the Base<table-name> class + contains all the functionality and should not be changed. The other class + is empty but can be extended to add or change functionality. If you + regenerate the classes, only the Base* classes will be overwritten. +

+ +

+ Data Objects are used almost exclusively with their related Peer classes. + Where peer classes "wrap around" around a database table, a Data Object + "wrap around" individual rows of the table. The two always go together. +

+ +

+ You normally use Data Objects in one of two ways. The most common way + is to extract data after you called a doSelect on a Peer class. The + doSelect method returns a List of Data Objects that holds the data of + the resultset. Secondly you can create Data Objects and call their save + methods to insert or update the related row into the database. +

+ +

+ Criteria is an abstraction of the criteria of an sql query. We use + criteria objects to specify the criteria of a sql statement. The + database adaptor classes contains information on how this Criteria object + will be translated to different flavours of sql. +

+ +

+ Criteria is in effect a map of field names and values that forms the + criteria of a query. By default the comparison is equals (=) but you + can define any comparison operator (<, >, <=, > =, IN, etc.). +

+ +

+ Criteria can also be used to do some other sql function like ORDER BY or + DISTINCT. If Criteria is too limited for your purposes (which should not + happen often) you are still free to use raw sql queries. +

+ +

+ The Peers make use of a DatabaseMap class that holds internal data about + the relational schema. You will seldom, if ever, need to work with the + DatabaseMap class. It is used internally by Peers to discover information + about the database at runtime. +

+ +

+ There is exactly one DatabaseMap for each relational database that you + connect to. You may wish to connect to more than one database in your + application. You should then have one DatabaseMap for each of the + databases. +

+ +

+ DatabaseMaps are constructed by classes called MapBuilders. Torque + generates MapBuilder classes for each of the tables in your schema. + The MapBuilder for a table is called when the Peer class for the table + is loaded. +

+ +

+ All DatabaseMaps are instances of the class + org.apache.torque.map.DatabaseMap. + They are kept in the instance variable TorqueInstance.dbMaps. + The Map for the database with the name key can be retrieved + by the method Torque.getDatabaseMap(key). +

+ +

+ The ID Broker is used to automatically create unique primary keys for + tables. It creates id's from a database table called id_table. +

+ +

+ Of course Torque also supports using the ID generation provided by the + underlying database system - just set the idMethod attributes + as desired in your schema. +

+ +

+ The ID Broker is used in the underlying Peer code. After you have + generated your object model classes you need not worry about it anymore. +

+ +

+ Although all databases supported by Torque understand SQL, there are + differences in the behaviour of the databases which the Torque runtime + needs to know about. For example, the standard (String) format + of a date object in an Oracle9i database is different from a + postgresql database. + The adapter for a database provides the necessary methods to hide such + differences from the user. + For example, the adapter provides a method to create a String in the + database's preferred format from a Date object. +

+ +

+ Adapters are subclasses of the org.apache.torque.adapter.DB + class. + The adapters are stored in the private map TorqueInstance.apdapterMap; + the key of the map is the name of the database (e.g. "bookstore"), + and the value of the map is the adapter. The adapter for a given key + can be retrieved via the method Torque.getDB(key). +

+ +

+ If your database is not yet supported, you can read the + Support for new Databases + docs on how to create a new adapter for your database. +

+ +

+ To access a database, a connection must be made to the database. + A DataSource is an object which can provide Connections to the + database. A DataSourceFactory is used to configure and provide + one DataSource. +

+ +

+ All DataSourceFactories used by Torque must implement the interface + org.apache.torque.dsfactory.DataSourceFactory. + The DataSourceFactories are stored in the private map + TorqueInstance.dsFactoryMap; the key of the map is the + name of the database (e.g. "bookstore"), and the + value of the map is the DataSourceFactory. + The DataSourceFactory for a given key can not be retrieved + by a public method; however, a connection from the DataSource + for the DataSourceFactory for a given key can be obtained + by Torque.getConnection(key); +

+ + +

+ +

+ + + +

+ Torque can be used with several databases at once. + The resources for each database are usually kept in Maps + where the key is the name of the database. To make things easier + for people who use only one database, Torque supports the notion + of a default database. This allows it to provide convenience + methods like Torque.getConnection() where no database name + must be specified. These methods refer to the default database, + in contrast to e.g. Torque.getConnection(String) + where the name of the database must be supplied explicitly. +

+ +

+ The name of the default database can be retrieved by + Torque.getDefaultDB(). +

+ + + +

+ + + Added: db/torque/runtime/trunk/xdocs/reference/write-to-db.xml URL: http://svn.apache.org/viewcvs/db/torque/runtime/trunk/xdocs/reference/write-to-db.xml?rev=371077&view=auto ============================================================================== --- db/torque/runtime/trunk/xdocs/reference/write-to-db.xml (added) +++ db/torque/runtime/trunk/xdocs/reference/write-to-db.xml Sat Jan 21 07:46:38 2006 @@ -0,0 +1,117 @@ + + + + + + Torque Runtime Reference - Writing to the database + Leon Messerschmidt + Jason van Zyl + Scott Eade + + + +

+ +

+ To write an object into the database, call its save() method. Depending + on whether the data object was newly created or was read from the + database, the corresponding row in the database table is inserted or + updated. If the object is new, its primary key(s) is/are generated + automatically if the id method was set to "idbroker" or + "native" in the schema.xml. +

+ +

+ As an example, consider the following code, which creates an Author + object and inserts it into the database. Then the object is loaded again, + modified. and updates the corresponding row in the databse. +

+ + +Author stevens = new Author(); +stevens.setFirstName("W."); +stevens.setLastName("Stevens"); +stevens.save(); + + +

+ If the generator properties "torque.complexObjectModel" and + "torque.objectIsCaching" were not set to false at generation + time, any objects which are referenced in the foreign key Lists of the + object are also saved. +

+ +

+ For example, in the following code, calling + book.save() also saves the author added to the book: +

+ + +Author bloch = new Author(); +bloch.setFirstName("Joshua"); +bloch.setLastName("Bloch"); +Book effective = new Book(); +effective.setTitle("Effective Java"); +effective.setISBN("0-618-12902-2"); +effective.setPublisher(addison); +bloch.addBook(effective); +bloch.save(); //also saves the book "effective" + + +

+ Note that the save is only propagated in the 1->n directions of foreign + keys, not in the n->1 direction. I.e. in the above example, calling + effective.save() would NOT save the corresponding author. +

+ +

+ If the object which save method is called is neither new nor modified, + it is not saved. Internally, this is handled via the isNew + and modifed flags of the object. +

+ +

+ There are alternative ways to insert or update an object in the database: + you can pass the objects to the doInsert() or + doUpdate methods of their corresponding Peers, or you can + create a criteria object which contains the data of the object and pass + the Criteria object to the doInsert() or + doUpdate methods of the Peer class. + Note that if you construct a criteria, the id field is not added to the + Criteria. It is taken care of by underlying database system (or perhaps + the ID BROKER when it is in use). + The object that is returned by doInsert is the id of the newly added row. +

+ +

+ Deletes work much in the same way as a select. If you, for example, + want to delete the author with id = 3 then you simply add it to the + Criteria and call doDelete. +

+ + +Criteria criteria = new Criteria(); +crit.add(AuthorPeer.AUTHOR_ID, 3); +AuthorPeer.doDelete(criteria); + + +

+ + --------------------------------------------------------------------- To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org For additional commands, e-mail: torque-dev-help@db.apache.org