openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Judes Tumuhairwe" <judethecuted...@gmail.com>
Subject Re: Mapping a temporary table
Date Tue, 13 Jan 2009 03:27:43 GMT
Thanks Yann. I understand your situation.
Altering/creating tables on the fly is not a good idea but if it is
generated by another app then I guess you have no option.

The short answer is no, you can't do it with JPA since it assumes that the
table-name and the whole schema is static.
- The best option I would say is to do it thru JDBC (e.g. using the DAO
pattern).
- if performance is the issue, unless we're talking about tens of millions
of records, I doubt that dynamically creating classes & compiling it & then
loading it would be any better. There's a good conversation here [1] about
JPA & dynamic schemas.
You might want to consider nightly batch-jobs do crunch the data (depending
on the SLA)

- The next best option (If you must use JPA) may be to somehow abstract the
table name from the app (i.e. map the @Entity(name="myView")----then you'd
have to dynamically update/replace the view as in
CREATE OR REPLACE VIEW usernameView AS
SELECT * FROM prefix_sessionId

Obviously each user would have their own view. In fact, if you're using MS
SQL Server 2005, it has database-level triggers [2]. You could write one to
say:
if (EVENT_TYPE = 'create_table' AND ObjectType = 'Table '
    && ObjectName starts with 'userName')
then CREATE OR REPLACE VIEW userNameView AS SELECT * FROM ObjectName //the
generated table.

In this case, the view name would never change. I agree, its a hack that
wouldn't scale (since you'd need a new class for every user) so maybe JDBC
is simpler after all :)


[1] http://forums.java.net/jive/thread.jspa?threadID=27035
[2] http://www.developer.com/db/article.php/3552096

I hope that helps. I don't know if anyone else has a better option.

Judes


On Sun, Jan 11, 2009 at 11:59 PM, Yann Andenmatten <
YAndenmatten@odyssey-group.com> wrote:

>
> Hi Judes,
>
>
>
> It is not a temporary table in the sense of a database temporary table.
> It is a table created to hold the result of a complicated query. However
> the table name is not fixed (it will be something like a fixed prefix +
> a session id). The columns are fixed.
>
>
>
> The reason to have a temporary table (outside the fact it is done from
> another application), is for performance reason. This table is used to
> store large result of complicated query. Therefore it would be a
> bottleneck if every user creates data in the same table (while other
> users are reading it). Has the interest of the data is not cross user,
> it makes sense to have a separate temporary table(s).
>
>
>
> To answer directly your c question, it is not related to reporting or
> OLAP as it is real time. In a second phase we will include asynchronous
> calculation for very long query.
>
>
>
> Thanks for your interest.
>
>
>
> Yann
>
>
>
> ________________________________
>
> From: Judes Tumuhairwe (via Nabble)
> [mailto:ml-user+110072-47525984@n2.nabble.com<ml-user%2B110072-47525984@n2.nabble.com>
> ]
> Sent: vendredi, 9. janvier 2009 23:24
> To: Yann Andenmatten
> Subject: Re: Mapping a temporary table
>
>
>
> Hi Yann,
> > We want to read data from an application which generate result in
> temporary
> > table. We know the table name,
>
> I don't know if I understand you right but....you want to read data from
> a
> temporary table?
>
> I have a few questions to try to understand what you're talking about:
> a) Are the columns names constant or do they change depending on
> when/how
> the table is generated? If they're the same everytime, is there a reason
> it
> has to be a temporary table?
> b) I don't know if you can read data from a temporary table via JPA.
> AFAIK,
> in Postgres, MySQL, and MS SQL temporary tables are not accessible
> outside
> the scope of that transaction or session (unless you disable auto-commit
> and
> manage the transactions yourself since they're dropped on-commit). Have
> you
> done this successfully in straight JDBC?
> c) Is it fair to assume it is reporting/OLAP related data?
>
> It might help a bit if you elaborate a little bit on the use case.
>
> thanks,
> Judes
>
>
> On Fri, Jan 9, 2009 at 6:41 AM, Yann Andenmatten <
> YAndenmatten@...
> <http://n2.nabble.com/user/SendEmail.jtp?type=node&node=2135621&i=0> >
> wrote:
>
>
> >
> > Hi,
> >
> > We want to read data from an application which generate result in
> temporary
> > table. We know the table name, but I don't know the best way to do
> this.
> > Generated, building a class with the appropriate annotation is not
> > acceptable. Possibly generate a xml based entity configuration might
> be a
> > way.
> >
> > What would you suggest ? Does anyone faced the same problem ?
> >
> > Thanks
> > Yann
> > --
> > View this message in context:
> > http://n2.nabble.com/Mapping-a-temporary-table-tp2133474p2133474.html
> > Sent from the OpenJPA Users mailing list archive at Nabble.com.
> >
> >
>
>
>
> ________________________________
>
> This email is a reply to your post @
> http://n2.nabble.com/Mapping-a-temporary-table-tp2133474p2135621.html
> You can reply by email or by visting the link above.
>
>
>
>
> ____________________________________________________________
>
> • This email and any files transmitted with it are CONFIDENTIAL and
> intended
>  solely for the use of the individual or entity to which they are
> addressed.
> • Any unauthorized copying, disclosure, or distribution of the material
> within
>  this email is strictly forbidden.
> • Any views or opinions presented within this e-mail are solely those of
> the
>  author and do not necessarily represent those of Odyssey Financial
> Technologies SA unless otherwise specifically stated.
> • An electronic message is not binding on its sender. Any message referring
> to
>  a binding engagement must be confirmed in writing and duly signed.
> • If you have received this email in error, please notify the sender
> immediately
>  and delete the original.
> --
> View this message in context:
> http://n2.nabble.com/Mapping-a-temporary-table-tp2133474p2144637.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message