Hi, Unfortunately support for joins in native sql queries is limited to single-valued relationships (see section 3.8.15 of the spec - right at the end).
I don't believe OpenJPA currently provides a workaround for this restriction (other than to use JPQL). I've looked into similar things though, and I might be able to help you get started if you're interested / able to add this support to OpenJPA? -mike On Mon, Sep 27, 2010 at 9:49 AM, tsanidas <[email protected]> wrote: > > All, > I have 2 tables that are provided by 3rd party software that I'm trying > to query from. There are issues with the way the data is organized, so we > wrapped them in a Pl/SQL function that returns a result set. > I've mapped the 2 tables as a one-to-many, since that's the relationship > between these two tables. I have no problem with calling the function via > a > Native Query, but it seems to want to query the second table to get the > associated "many" rows with a query executed per row. I know that if I > were > doing this all via OpenJPA and not a proc/function, I could use the " join > fetch " and get around the query-per-row issue. My question to the gurus > is, if my procedure returns all of the columns that would normally have > been > in the " join fetch" query, can I get OpenJPA to treat this as a join and > handle the one-to-many relationship without performing a query per row? > > Details: OpenJPA 2.0.1, Oracle 11g > Query: Query qry = em.createNativeQuery("select * from table( > FINDBYCRITERIA2(?))", Article.class); > Function: FINDBYCRITERIA2() Currently, returns a join of the ARTICLE > and ARTICLE_DETAIL tables > > Tables: > > ARTICLE > ------------------------------------ > ARTICLE_KEY (pk) > LANG_CODE (pk) > TITLE > (etc etc etc) > > ARTICLE_DETAIL > ------------------------------------ > ID (pk) > ARTICLE_KEY > LANG_CODE > KEYWORD > AUTHOR > (etc etc etc) > > > -- > View this message in context: > http://openjpa.208410.n2.nabble.com/OpenJPA-with-Stored-Proc-join-possible-tp5575342p5575342.html > Sent from the OpenJPA Users mailing list archive at Nabble.com. >
