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: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]