You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by tsanidas <ts...@yahoo.com> on 2010/09/27 16:49:59 UTC
OpenJPA with Stored Proc - join possible?
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.
Re: OpenJPA with Stored Proc - join possible?
Posted by Michael Dick <mi...@gmail.com>.
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 <ts...@yahoo.com> 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.
>