multiple join between tables and aliases

[Thomas Fischer <Fi...@seitenbau.net>]

Hi,

I wonder how I should best create a multiple join between two tables.

Consider the following example:
There are two tables, AUTHOR and BOOK. Assuming that each book has two
authors, the BOOK table has two references on the AUTHOR table by two
foreign keys, AUTHOR_ID and SECOND_AUTHOR_ID.
I now want to join the book and author tables on the two foreign keys in
BOOK. The following code does this:

Criteria criteria = new Criteria();
criteria.addJoin(BookPeer.AUTHOR_ID, AuthorPeer.AUTHOR_ID);
criteria.addJoin("b.SECOND_AUTHOR_ID", AuthorPeer.AUTHOR_ID);
criteria.addAlias("b", BookPeer.TABLE_NAME);

which creates an sql like

select from BOOK,AUTHOR,BOOK B where BOOK.AUTHOR_ID=AUTHOR.AUTHOR_ID AND
B.SECOND_AUTHOR_ID=AUTHOR.AUTHOR_ID

Note that the following code does not achieve the same

Criteria criteria = new Criteria();
criteria.addJoin(BookPeer.AUTHOR_ID, AuthorPeer.AUTHOR_ID);
criteria.addJoin(BookPeer.SECOND_AUTHOR_ID, AuthorPeer.AUTHOR_ID);

because it creates the following SQL:

select from BOOK,AUTHOR where BOOK.AUTHOR_ID=AUTHOR.AUTHOR_ID AND
BOOK.SECOND_AUTHOR_ID=AUTHOR.AUTHOR_ID

Now, the "problem" is that in the first code example, I do not like the
line

criteria.addJoin("b.SECOND_AUTHOR_ID", AuthorPeer.AUTHOR_ID);

because the name of the database column is put explicitly into the
statement, which is not pretty (for example,if the DB schema is changed,
the compiler would not complain about the code, although the code would not
work anymore).
Another way to obtain the column name would be to use the constant
BookPeer.SECOND_AUTHOR_ID, (filled with the String
"BOOK.SECOND_AUTHOR_ID"), and strip the table name from it using the
BookPeer.TABLE_NAME constant:

String columnName = BookPeer.SECOND_AUTHOR_ID;
columnName = columnName.substring(BookPeer.TABLE_NAME.length() + 1,
columnName.length());
criteria.addJoin("b." + columnName, AuthorPeer.AUTHOR_ID);

Is there a better (i.e. shorter) way to do this ?

Cheers,

       Thomas


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