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.
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.
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:
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
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:
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:
Then, you can read the author with the last Name "Stevens" and all his books using the following code:
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":
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:
This method creates a so called "inner join", i.e. only author entries which have a corresponding book entry are selected.
Details |
---|
Also, in SQL, there are two different ways to state an inner join.
The first way is a statement like
The second way to create an inner join is It is not possible to combine both ways of creating a join in most databases, i.e. code like |
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:
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:
The above code uses a left (outer) join. There, authors which have no books also appear in the list of authors. There are two kinds of outer joins: left joins and right joins. In a left join, entries in the first table are returned even if no entry in the second table is found, whereas in a right join, entries in the second table are returned even if there are no entries in the first table. Both kinds are supported in Torque via the constants Criteria.LEFT_JOIN and Criteria.RIGHT_JOIN.
Usually, outer joins are used for reading in several tables at once (not ?yet? supported by Torque), or for ordering the contents of one table by the contents of another table.
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:
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.
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:
If you add multiple constraints to a Criteria, they are linked by default by a logical "AND" operator. For example, the code
results in the following SQL query:
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
is
Note that the tables used in the last example are not defined in the bookstore schema in the tutorial.
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:
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.
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:
Note that in this specific example, there was no need for the CUSTOM modifier. The same query could have been created by:
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.
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:
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:
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:
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.
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
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:
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.