Eclipse Community Forums: EclipseLink » DescriptorQueryManager+EntityManager

Home » Eclipse Projects » EclipseLink » DescriptorQueryManager+EntityManager

DescriptorQueryManager+EntityManager [message #734295]

Fri, 07 October 2011 07:38

Eclipse User

Hello,

I have a bit of a special case here. I have to access all my data through stored procedures. I have read lots of stuff about DescriptorCustomizer and DescriptorQueryManager. But do they apply at all if I use EntityManager? I got the feeling they do not.

What I have so far:
* entity
* @NamedStoredProcedureQuery for one selectall procedure that returns a cursor (hopefully this part is ok, but got no errors)
* @Customizer points to the DescriptorCustomizer
* DescriptorCustomizer.customize function sets DescriptorQueryManager.setReadAllQuery() to an instance of namedQuery. I get the named query from @PersistenceContext injected EntityManager.

I am quite sure the named procedure never gets called (I put a where clause to the sql which returns a cursor, but I get the full table).

I am sure something is wrong, but the question here is - should this approach work at all? Am I basically wrong in some assumption and this cannot be made to work anyway or maybe this should work somehow and I am just erring somewhere.

Anyway, I gotta figure this out soon. Or I guess I will be calling sql the usual jdbc way as I did all my life... and writing my own caching and stuff.

Thanks for any information regarding this.

And btw, eclipselink sessions seem quite arcane to me. Also that workbench is strange and illogical. Guess some artifact from Toplink or what? I don't want to have too much xml configs anyway since I like the new way with annotations much better.

Re: DescriptorQueryManager+EntityManager [message #734367 is a reply to message #734295]

Fri, 07 October 2011 10:43

Eclipse User

Hello,

The documentation you seem to be looking at is for the underlying native EclipseLink api, which is a hold over from TopLink. The JPA interface is built on top of it, so you can still use those api to get at the nuts and bolts, but most features are entirely accessible through JPA and JPA like annotations or XML.

Some questions:
1) Is your descriptor customizer being called?
2) What query are you trying to execute and what api are you using to build and execute it?
3) What gets issued to the database for your query? You can turn logging on to see what SQL is issued to the database bu setting the value to FINE or FINER as described at
http://wiki.eclipse.org/EclipseLink/Examples/JPA/Logging
4) What are your requirements query wise - are you able to just use named stored procedure queries for reading, or will you need to use stored proc for writing as well?

As for the setReadAllQuery(), this is used for the default read all query, such as if you peform a basic query with no selection criteria. If you try a find, this might not use your stored procedure - you might want to create a named stored proc to use instead of find, or take a look at the setReadObjectQuery() api.

As for linking the session concepts to JPA - a ServerSession matches up closely to a EntityManagerFactory, while a UnitOfWork is used internally by an EntityManager. Hope that helps

Best Regards,
Chris

Re: DescriptorQueryManager+EntityManager [message #734383 is a reply to message #734295]

Fri, 07 October 2011 10:43

Eclipse User

Re: DescriptorQueryManager+EntityManager [message #734403 is a reply to message #734367]

Fri, 07 October 2011 12:17

Eclipse User

Hi, thanks for the quick answer and helpful suggestions. Lets move this thing, I will update as I go.

Up to this point:
1) Yep
2) I am trying to execute a named procedure, like this:

Query query = entityManager.createNamedQuery("Table.getAll", Table.class);

This does NOT work in the customizer. As I suspected entityManager is null and never gets injected that early... quite obvious once I think about it. I used it 'cause I cannot find any other way to specify named query.

I have to do this:

descriptorQueryManager.setReadAll[Call/Query/SQLString](...);

I guess I have to provide one of these? Guess if I were to provide SQLString that would be ok, but how do I map return parameters then (out cursor)? So I am trying to insert either query or call here. And named query at that. Is that even possible? I do not know. atm I do not have the patience to dig around in the sources. And there is no documents regarding this, just some vague statements that it should be possible which lead me to EclipseLink Sessions in the end. And I don't have a clue about the old-style sessions since I am mostly familiar with classic JPA... dead ends everywhere.

3) I am quite sure nothing goes through, since I've just modified the procedure to write something while it fetches (the fastest way though a bit inflexible).
4) In the end I will want full CRUD through procedures. I'd like to standardise at least the basics though, so I don't have to write queries all the time. Well I can do that without the jpa/eclipselink I guess. But this way it seems nicer when you work.

I was guessing the readAllQuery gets executed on

Query query = em.createQuery("select OBJECT(u) from Table u");
tableList = query.getResultList();

Hope it does. Otherwise my task is already impossible.

Oh and thanks for the reply again. I didn't expect to get one, ever, as I usually don't with my problems Smile

edit: Let me include my named procedure, guess this could be very wrong... no errors though.

@Entity
@Table(name="TABLE")
@NamedStoredProcedureQueries(value={
		@NamedStoredProcedureQuery(name="Table.getAll",
			procedureName="GETALL_TABLE",
			resultClass=Table.class,
			parameters={
				@StoredProcedureParameter(queryParameter="result", name="c_cur", direction=Direction.OUT_CURSOR)
			})
		})
@Customizer(data.table.TableDescriptorCustomizer.class)
public class Table implements Serializable {

edit2: Trying to set call procedure directly in descriptor and ignoring these annotations and the notion of named queries. Was looking nice though, I like the clarity of annotations for stuff like this.

Have some problems on what and how to bind, but I might be able to work this out.

[Updated on: Fri, 07 October 2011 12:36] by Moderator

Re: DescriptorQueryManager+EntityManager [message #734414 is a reply to message #734367]

Fri, 07 October 2011 12:17

Eclipse User

@Override
public void customize(ClassDescriptor descriptor) throws Exception {
	
	descriptor.setReadOnly();
	
	DescriptorQueryManager dqm = descriptor.getDescriptorQueryManager();
	
	StoredProcedureCall spc = new StoredProcedureCall();
	
	spc.setProcedureName("GETALL_TABLE");
	spc.addNamedCursorOutputArgument("c_cur");
	
	ReadAllQuery raq = new ReadAllQuery(Table.class, spc);
	
	dqm.setReadAllCall(spc);
	dqm.setReadAllQuery(raq);
	dqm.setReadAllSQLString(null);
	
	descriptor.setQueryManager(dqm);

}

Set all 3 instances of "readall"-something to my custom ones. Still, select columns from table gets called... I guess EntityManager doesn't care for my customizer. Maybe I should file a bug or is there something major I am still missing? I am currently trying to make sense of eclipselink sources regarding this. If I find an entry point...

[Updated on: Fri, 07 October 2011 14:05] by Moderator

Re: DescriptorQueryManager+EntityManager [message #735320 is a reply to message #734414]

Tue, 11 October 2011 10:29

Eclipse User

Since this seems to be missing functionality that is supposed to work I have filed a bug.

Re: DescriptorQueryManager+EntityManager [message #735392 is a reply to message #734295]

Tue, 11 October 2011 11:14

Eclipse User

Yes, custom queries are used from JPA.

The descriptor defines basic CRUD operations that you can override using stored procedures or custom SQL. You need to set the query to a custom query, not a named query.

The basic CRUD operations only override very specific operations, not any dynamic or JPQL queries. For a specific query, you need to define a NamedStoredProcedureQuery for it and call that specific named query through JPA the same as calling any other named query.

See,
http://wiki.eclipse.org/EclipseLink/Examples/JPA/CRUDStoredProcedures

--
James : http://wiki.eclipse.org/EclipseLink : http://en.wikibooks.org/wiki/Java_Persistence : http://java-persistence-performance.blogspot.com/

Re: DescriptorQueryManager+EntityManager [message #735561 is a reply to message #735392]

Wed, 12 October 2011 04:36

Eclipse User

James wrote on Tue, 11 October 2011 11:14

Yes, custom queries are used from JPA.

The descriptor defines basic CRUD operations that you can override using stored procedures or custom SQL. You need to set the query to a custom query, not a named query.

The basic CRUD operations only override very specific operations, not any dynamic or JPQL queries. For a specific query, you need to define a NamedStoredProcedureQuery for it and call that specific named query through JPA the same as calling any other named query.

See,
http://wiki.eclipse.org/EclipseLink/Examples/JPA/CRUDStoredProcedures

--
James : http://wiki.eclipse.org/EclipseLink : http://en.wikibooks.org/wiki/Java_Persistence : http://java-persistence-performance.blogspot.com/

Indeed I have since come to the conclusion that I won't be able to use named procedures/functions for this task (albeit that would have been logical imo).

But not even specifying it otherwise works for me. Hence my statement. The call I provide is never called.

The examples provided at the link are sadly, outdated and not verbose enough. It doesn't say anywhere whether it is enough to override one instance (setCall), or do I have to override all three (setCall, setReadAllQuery, setSQLQuery). Also the example provided is so outdated that the entire api has changed... there is no setName function on StoredProcedureCall. Also why is there no example for readAll? Also it doesn't say anywhere whether this works with EntityManager or just EclipseLink Sessions proprietary api.

Also as I posted - check one post up - I was trying to do just that (from the link) And the call specified is plainly ignored. At least for readAll operation that is the most important imo. Also there is no specification what triggers readAll operation.

EntityManager em;
Query query = em.createQuery("select OBJECT(u) from Table u");

This should trigger my readAllCall yes?

As it is now user does not have permissions on tables. Eclipselink still tries to read directly from the table.

Also I guess I will never be able to read data if I have complex oracle PLSQL types in there? I was looking at this: http://wiki.eclipse.org/EclipseLink/Examples/JPA/PLSQLStoredFunction

This is even worse... I have to repeat the field 3x and also define a special db object to do the mapping. I cannot reuse the existing entity class, I have to manually specify fields (which are the same in each instance). Well I will try whether this works at all.

Re: DescriptorQueryManager+EntityManager [message #735563 is a reply to message #735392]

Wed, 12 October 2011 04:36

Eclipse User

James wrote on Tue, 11 October 2011 11:14
> Yes, custom queries are used from JPA.
>
> The descriptor defines basic CRUD operations that you can override using stored procedures or custom SQL. You need to set the query to a custom query, not a named query.
>
> The basic CRUD operations only override very specific operations, not any dynamic or JPQL queries. For a specific query, you need to define a NamedStoredProcedureQuery for it and call that specific named query through JPA the same as calling any other named query.
>
> See,
> http://wiki.eclipse.org/EclipseLink/Examples/JPA/CRUDStoredProcedures
>
> --
> James : http://wiki.eclipse.org/EclipseLink : http://en.wikibooks.org/wiki/Java_Persistence : http://java-persistence-performance.blogspot.com/

Indeed I have since come to the conclusion that I won't be able to use named procedures/functions for this task (albeit that would have been logical imo).

But not even specifying it otherwise works for me. Hence my statement. The call I provide is never called.

The examples provided at the link are sadly, outdated and not verbose enough. It doesn't say anywhere whether it is enough to override one instance (setCall), or do I have to override all three (setCall, setReadAllQuery, setSQLQuery). Also the example provided is so outdated that the entire api has changed... there is no setName function on StoredProcedureCall. Also why is there no example for readAll? Also it doesn't say anywhere whether this works with EntityManager or just EclipseLink Sessions proprietary api.

Also as I posted - check one post up - I was trying to do just that (from the link) And the call specified is plainly ignored. At least for readAll operation that is the most important imo. Also there is no specification what triggers readAll operation.

EntityManager em;
Query query = em.createQuery("select OBJECT(u) from Table u");

This should trigger my readAllCall yes?

As it is now user does not have permissions on tables. Eclipselink still tries to read directly from the table.

Also I guess I will never be able to read data if I have complex oracle PLSQL types in there? I was looking at this: http://wiki.eclipse.org/EclipseLink/Examples/JPA/PLSQLStoredFunction

This is even worse... I have to repeat the field 3x and also define a special db object to do the mapping. I cannot reuse the existing entity class, I have to manually specify fields (which are the same in each instance). Well I will try whether this works at all.

Re: DescriptorQueryManager+EntityManager [message #739718 is a reply to message #735563]

Mon, 17 October 2011 15:37

Eclipse User

Re: DescriptorQueryManager+EntityManager [message #739720 is a reply to message #735563]

Mon, 17 October 2011 15:37

Eclipse User

The setName on StoredProcedureCall was just a typo, I fixed the wiki example.

It may be that JPQL will not invoke the custom read-all query. Please log a bug for this.

In general if your database does not allow SQL, then using dynamic JPQL to query it is very odd. You would be much better off using a named query in JPA, not JPQL.

--
James : http://wiki.eclipse.org/EclipseLink : http://en.wikibooks.org/wiki/Java_Persistence : http://java-persistence-performance.blogspot.com/

Re: DescriptorQueryManager+EntityManager [message #743337 is a reply to message #735392]

Fri, 21 October 2011 07:10

Eclipse User

Yep, that link.

It doesn't work. At all. Tried a lot of scenarios and the queries from example never get called when using EntityManager.

I got ReadAllQuery working when using Session. But I don't want to use Sessions, too confusing and proprietary.

I guess I am defaulting to named queries as it seems the only possible alternative. Maybe you should just remove that wiki section since nothing seems to work as suggested? Also I am not filing bugs, don't have time. And I would probably get "not supported" answer anyway.

Something from the log:

FINER: client acquired: 26365814
FINER: TX binding to tx mgr, status=STATUS_ACTIVE
FINER: acquire unit of work: 18985130
FINEST: persist() operation called on: package.DataObject@6767df.
FINER: begin unit of work flush
FINER: TX beginTransaction, status=STATUS_ACTIVE
FINEST: Execute query InsertObjectQuery(package.DataObject@6767df)
FINEST: Execute query InsertObjectQuery(package.DataObject@6767df)
FINER: end unit of work flush
FINER: resume unit of work
FINER: TX beforeCompletion callback, status=STATUS_ACTIVE
FINER: begin unit of work commit
FINER: TX afterCompletion callback, status=COMMITTED
FINER: end unit of work commit
FINER: release unit of work
FINER: client released

Usually you would get sth like this:

FINEST: Execute query ReadAllQuery(name="DataObject.allrows" referenceClass=DataObject )
FINEST: Connection acquired from connection pool [default].
FINEST: reconnecting to external connection pool
FINE: BEGIN PROCNAME(ret_cur=>?); END;
bind => [2 parameters bound]

calling a named query in this case.

Persist as you can see just does nothing. Hence I am just guessing that DescriptorCustomizer is not coupled with jpa part of the implementation at all. I would suppose fixing the docs wouldn't hurt. Might save some poor sods some time.

Re: DescriptorQueryManager+EntityManager [message #748498 is a reply to message #743337]

Mon, 24 October 2011 14:20

Eclipse User

Overriding the insert, update, delete SQL in DescriptorQueryManager does work. I'm not sure what code you used, but it was probably incorrect.

Re: DescriptorQueryManager+EntityManager [message #748499 is a reply to message #743337]

Mon, 24 October 2011 14:20

Eclipse User

Overriding the insert, update, delete SQL in DescriptorQueryManager does work. I'm not sure what code you used, but it was probably incorrect.

--
James : http://wiki.eclipse.org/EclipseLink : http://en.wikibooks.org/wiki/Java_Persistence : http://java-persistence-performance.blogspot.com/

Re: DescriptorQueryManager+EntityManager [message #755446 is a reply to message #748498]

Tue, 08 November 2011 09:49

Eclipse User

You are right. It was obviously wrong code on my end.

BUT

There is no way to figure out the "correct code" since this thing is pretty much undocumented. Right now I am just writing my own sql generators that will run the stuff for me. That ought to work when I can figure out how to call sql stored procedure/function directly. I will post another topic regarding this.

Thanks for your time regarding this issue, though it still doesn't work you saved me some time and calmed me down. At least this venue of thought can be closed now, though I am sad I couldn't get it to work.

[Updated on: Tue, 08 November 2011 09:50] by Moderator

Re: DescriptorQueryManager+EntityManager [message #755447 is a reply to message #748498]

Tue, 08 November 2011 09:49

Eclipse User

Previous Topic:	How to get return code from a stored procedure using EclipseLink
Next Topic:	Calling preparedStatement/Function the JPA way

Goto Forum:

-=] Back to Top [=-

Current Time: Tue Jul 22 11:47:30 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter