Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Templates for CREATE, INSERT, UPDATE, DELETE queries(Using customized queries for accessing database tables and views)
Templates for CREATE, INSERT, UPDATE, DELETE queries [message #921400] Mon, 24 September 2012 00:47 Go to next message
Askar Missing name is currently offline Askar Missing name
Messages: 3
Registered: September 2012
Junior Member
Hello everybody. After almost a year of development of project that uses JPA/Eclipselink we have to route all our queries to db tables and views via oracle packages. So the question is: is it possible in eclipselink to access to tables using customized query templates?
Instead of "SELECT NAME, SURNAME FROM SCHEME1.USER WHERE ID = ?" generate "SCHEME1.PACKAGENAME1.FUNCTION1(?)" for select and same for other queries - create/update/delete?
I didn't found something alike in raw JPA, maybe it is possible using eclipselink API?

EclipseLink, version: Eclipse Persistence Services - 2.3.1.v20111018-r10243

[Updated on: Mon, 24 September 2012 01:18]

Report message to a moderator

Re: Templates for CREATE, INSERT, UPDATE, DELETE queries [message #921420 is a reply to message #921400] Mon, 24 September 2012 01:18 Go to previous messageGo to next message
Askar Missing name is currently offline Askar Missing name
Messages: 3
Registered: September 2012
Junior Member
(edited topic body)

[Updated on: Mon, 24 September 2012 01:19]

Report message to a moderator

Re: Templates for CREATE, INSERT, UPDATE, DELETE queries [message #923057 is a reply to message #921420] Tue, 25 September 2012 11:11 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

You can override any entity CRUD or mapping operation with your own SQL or stored procedure call.

You will need to use a DescriptorCustomizer to do this and set a StoredProcedureCall object on the descriptor's queryManager.

Please also log a bug to have annotation support for this feature.


James : Wiki : Book : Blog : Twitter
Re: Templates for CREATE, INSERT, UPDATE, DELETE queries [message #924832 is a reply to message #923057] Thu, 27 September 2012 02:53 Go to previous messageGo to next message
Askar Missing name is currently offline Askar Missing name
Messages: 3
Registered: September 2012
Junior Member
James, thank you for your response! I'll give it a try and write here if something goes wrong
Re: Templates for CREATE, INSERT, UPDATE, DELETE queries [message #924965 is a reply to message #921400] Thu, 27 September 2012 05:17 Go to previous messageGo to next message
Neikius Mising name is currently offline Neikius Mising name
Messages: 43
Registered: April 2011
Member
Interesting, the very same thing I was unsuccessfully trying a year ago.

In the end my solution was to directly call procedures and flush cache after every write. Very cumbersome. So if you manage to get it working I'd like to know how. Also I will try again, maybe stuff got fixed and I know a bit more.
Re: Templates for CREATE, INSERT, UPDATE, DELETE queries [message #937881 is a reply to message #924965] Tue, 09 October 2012 06:47 Go to previous messageGo to next message
Neikius Mising name is currently offline Neikius Mising name
Messages: 43
Registered: April 2011
Member
So, fun fact, this still does not work.

ReadAllQuery raq = new ReadAllQuery(SomeClass.class);
StoredProcedureCall call_raq = new StoredProcedureCall();
call_raq.setProcedureName("SomeClass.allrows"); //name of named stored procedure
call_raq.useNamedCursorOutputAsResultSet("cursor");
raq.setCall(call_raq);


Now I can clearly see the default ReadAllQuery is used. Was the same 1 year ago, is the same still.

Might be descriptor cannot get those annotated named procedures? There is never any error, just calling of default sql query on a table.

Can someone point me to a working example of this?

edit: well I guess this is just impossible to do as I imagined. If I write/generate direct procedure calls then maybe, but you can't use named procedures/functions in descriptorCustomizer.

Also another problem is cache refreshing anyway since some inserts might do a little of their own tinkering so everything is redundant anyway. Turn off cache, use wrapped objects to query data and its done.

Still interested in what OP did though.

[Updated on: Tue, 09 October 2012 10:23]

Report message to a moderator

Re: Templates for CREATE, INSERT, UPDATE, DELETE queries [message #939206 is a reply to message #937881] Wed, 10 October 2012 10:24 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

Where are you using this code, what error do you get? To override the default readAll operation you need to set this query in the DescriptorQueryManager. Include your complete DescriptorCustomizer code.

In general, it is normally the insert, update, delete, readObject queries that are override, as these are specific queries. The readAll query would only override a dynamic query for all instances (no where clause, not hints). JPA has some different defaults, so you may need to set the distinctState of the query. For a readAll query it would be easier to just execute a named query that calls the stored procedure.


James : Wiki : Book : Blog : Twitter
Re: Templates for CREATE, INSERT, UPDATE, DELETE queries [message #939263 is a reply to message #939206] Wed, 10 October 2012 11:26 Go to previous message
Neikius Mising name is currently offline Neikius Mising name
Messages: 43
Registered: April 2011
Member
That code is directly in the customizer descriptor. I am guessing its not processing annotated procedures.

The thing works when I call procedures directly, BUT there are some things I am not able to do in that framework. I want some of the power of calling named stored procedures I cannot get there plainly calling em. So I ended up calling procedures directly.

Also funny thing is to use setDefaultReadAllQueryRedirector and create a new readAllQuery inside it. You will get immediate stack overflow (recursion).
Previous Topic:MultiTenancy/Inheritance: Strange NPE
Next Topic:MOXy - Two issues with XML-bindings
Goto Forum:
  


Current Time: Fri Aug 22 05:51:21 EDT 2014

Powered by FUDForum. Page generated in 0.02521 seconds