JDBC/SQL Question
I have to use straight JDBC, SQL, not fancy bean mapping tools -- it's not my call. Would be nice, but anyways........
I have several tables which are linked together by one unique key ID. There is a many-to-one relationship. If this were objects it would transaction to one Java Object containing a list of other Java Objects like this
class Catalog{
private List Books;
}
So I have to write queries which will return, the Catalog for instance. There would be one row in the table corresponding to the Catalog and several rows in the Books table.
In this case, should I use a JOIN type of query? If I do this I get back several rows since there are several books. Or should I do a query to get the Catalog first, another query to get the Books?
[910 byte] By [
smiles78a] at [2007-11-27 9:47:28]
# 2
As previously stated, mostly personal preference but with some gotchas.
If a catalog can be empty (no books) then a join may cause you trouble since it won't return anything at all. If you need to be able to read empty Catalogs then separate queries is probably your best bet.
A join approach costs a bit since the data from the Catalog table has to be returned with every row adding to network traffic. If a Catalog row is small or you're only returning a few rows then who cares, but if it's big or there's many rows then it's a consideration. At the same time, two queries is more code, more chance for bugs, and creating a statement and executing a query has some I/O cost as well.
Jemiah
# 3
> If a catalog can be empty (no books) then a join may
> cause you trouble since it won't return anything at
> all.
catalog.catalog_id = books.catalog_id (+)
will return a single row for a catlog with all the book fields being null if no book exists for the catalog.
>
> A join approach costs a bit since the data from the
> Catalog table has to be returned with every row
> adding to network traffic. If a Catalog row is small
> or you're only returning a few rows then who cares,
> but if it's big or there's many rows then it's a
> consideration. At the same time, two queries is more
> code, more chance for bugs, and creating a statement
> and executing a query has some I/O cost as well.
>
You don't have to return the entire row from either of the tables. Also, if she is going to be returning a large number of catalogs, then she will probably be executing a query per catalog to get the books, which, once again minimizes any relative join costs. And, those join costs are not high, unless, as I said, the tables are badly constructed, the query is badly constructed, the indexes on the table to be joined don't match well causing poor optimisation, or you are simply trying to join so many tables that it also cannot be optimised well by the DB.
Edit: In any case, there is going to be a join. The question is only, whether you are going to do the join in your program, or are you going to let the DB do the join.
