Hello,
I've been trying to convert one of our web-applications to JPA. We are using
GlassFish with TopLink, although there is nothing that forces us use
TopLink, so we are ready to switch to something else if it can solve our
problems.
One thing I should note before asking the questions: we only read the data
in our application and never modify, add or delete the data, so we don't
care about persisting the data back. Moreover, we've only got the views of
the actual tables and there is no way we can change the bad DB design, or
even get better-structured views.
Here are serveral questions/problems we've got:
1. Suppose we've got entity ChangeRequest (CR) which data is contained
in table CHANGE_REQUESTS. For each CR there may be 0..n keywords which are
associated to it. Keywords are simple strings, they reside in the table
KEYWORDS and the columns of these table are (CHANGE_REQUEST_EXTRA_ID,
KEYWORD, ...). CHANGE_REQUEST_EXTRA_ID is not a PK in CHANGE_REQUESTS table,
but it is unique for each entity there. There are actually quite a bunch of
other fields in KEYWORDS table (like DATE_CREATED, DATE_MODIFIED, etc.), but
they are completely unnecessary. The only thing we are interested in is the
collection of the keywords (Strings) associated to the CR. Is it possible to
do without an extra entity for a keyword, so that I could have a
"Collection<String> keywords;" in the CR class, instead of
"Collection<Keyword> keywords;"? Is there any way I can get a Collection of
basic types (like String) from the EntityManager query?
2. Is it possible to make the KEYWORDS table as a SecondaryTable for
CRs (remember that we have one-to-many relation)? Would we be able to have a
"Collection<String> keywords"-kind of field in our CR entity then? If so,
would these keywords be fetched in a separate query from the query which
gets the CR information, or would there be a single query with a JOIN and
thus a lot of rows would be returned with only the KEYWORD column being
different?
3. Suppose we've got the many-to one relation between CRs and CRs: for
each CR there may exist a main CR, and one main CR may have many CRs
associated with it. We need to do the following query (in terms of SQL):
> SELECT cr2.cr_number FROM change_requests cr1
> JOIN change_requests cr2
> ON (cr1.mr_number != 0 AND cr1.mr_number = cr2.mr_number)
> OR cr1.cr_number = cr2.cr_number
> WHERE cr1.cr_number = ? AND cr2.release = ?
cr_number is a PK, mr_number is a column with the id of the main CR if
it exists, or 0 if it doesn't. First, I tried to express this query JPQL,
but I failed to express the join-criteria. I would love to see how that can
be done, if it is possible.
If I use a NativeNamedQuery query like above, and try to get the
result as ChangeRequest entity, I can't do that either: it seems that
TopLink cannot work with the aliased column names and I get the following
exception:
> Exception [TOPLINK-6044] (Oracle TopLink Essentials - 2.0 (Build
> b58g-fcs (09/07/2007))):
> oracle.toplink.essentials.exceptions.QueryException
> Exception Description: The primary key read from the row
> [DatabaseRecord(
> => 1234567)] during the execution of the query was detected to
> be null. Primary keys must not contain null.
> Query: ReadAllQuery(foo.bar.ChangeRequest)
Is it possible to do at all? I tried to do the JOIN without aliasing
the second table to cr2, but that gave me the Oracle DB error "column
ambiguosly defined". Selecting cr2.* doesn't help too (in that case we get
DatabaseRecord( => 1234567, => asdfasdf, ...)] in the exception message.
Thanks,
Ivan