You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-dev@db.apache.org by tf...@apache.org on 2012/10/18 08:53:50 UTC
svn commit: r1399532 -
/db/torque/torque4/trunk/torque-site/src/site/xdoc/documentation/modules/runtime/reference/read-from-db.xml
Author: tfischer
Date: Thu Oct 18 06:53:50 2012
New Revision: 1399532
URL: http://svn.apache.org/viewvc?rev=1399532&view=rev
Log:
- Moved AND and OR operator section to a better suited place
- Add documentation for foreign keys
Modified:
db/torque/torque4/trunk/torque-site/src/site/xdoc/documentation/modules/runtime/reference/read-from-db.xml
Modified: db/torque/torque4/trunk/torque-site/src/site/xdoc/documentation/modules/runtime/reference/read-from-db.xml
URL: http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-site/src/site/xdoc/documentation/modules/runtime/reference/read-from-db.xml?rev=1399532&r1=1399531&r2=1399532&view=diff
==============================================================================
--- db/torque/torque4/trunk/torque-site/src/site/xdoc/documentation/modules/runtime/reference/read-from-db.xml (original)
+++ db/torque/torque4/trunk/torque-site/src/site/xdoc/documentation/modules/runtime/reference/read-from-db.xml Thu Oct 18 06:53:50 2012
@@ -157,7 +157,97 @@ List authors = AuthorPeer.doSelect(crite
</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>
@@ -202,9 +292,9 @@ List authors = AuthorPeer.doSelect(crite
]]></source>
<p>
- The foreign key defines that a book is associated to an author by setting
- the <code>author_id</code> column of the author to the id of the
- corresponding author.
+ 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>
@@ -212,21 +302,111 @@ List authors = AuthorPeer.doSelect(crite
<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.
+ <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>
- Note that by default, the getters query the database for the
+ 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>.
- You can also query the database for corresponding objects for several
- other objects at once, see the sections on Joins and Fillers below.
</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">
@@ -543,93 +723,6 @@ List books = BookPeer.doSelect(criteria)
</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>
- To explicitly specify which operator should be used
- to link the constraints in a Criteria, use 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="Case insensitivity">
<p>
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org