Eclipse Community Forums: EclipseLink

Help

Home

Home » Eclipse Projects » EclipseLink » createnativequery

Show: Today's Messages :: Show Polls :: Message Navigator

createnativequery [message #1864520]

Thu, 21 March 2024 10:15

Jayaram Subramanian

Messages: 3
Registered: March 2024

Junior Member

In our JPA implementation using eclipselink we are having oracleucp as datasource provider and we use the following the convention to invoke plsql pacakage calls in the database

try (CallableStatement cs = getThreadLocalEntityManager().getEntityManager().unwrap(Connection.class).
prepareCall(functionCall)
...
...
cs.execute()

what we noticed was the above lines of code execution was behaving with autocommit as true and any insert/update dml operation in package was getting auto committed as a part of cs.execute command.

But if the above plsql package call was preceeded with the createnativequery call for example
em.createNativeQuery("select 1 from dual").getSingleResult();

and then if we have the plsql package call after that we noticed was
1) the autocommit behavior during cs.execute was not happening
2) the subsequent createnative select calls to fetch records inserted via plsql package was fetching the records
the above 2 points is what we need as a part of our application requirements,

Just wanted to confirm the above and any other side effects of this behavior

Report message to a moderator

Re: createnativequery [message #1864562 is a reply to message #1864520]

Mon, 25 March 2024 17:33

Chris Delahunt

Messages: 54
Registered: December 2021

Member

What is the purpose of building a JDBC statement/call and executing it directly on the connection instead of going through your JPA set of API? JPA is required to manage the connection when it executes calls for you, but doesn't do anything at all when you just obtain the (or a) connection that it is holding if it has one already. Since EclipseLink lazily loads most everything, it may not have a connection associated unless you are in a transaction and have done something that requires the connection (such as your native query). Any reason you cannot create/execute a call via EclipseLink api? (see https://wiki.eclipse.org/EclipseLink/Examples/JPA/StoredProcedures for something similar to what you might be doing).

Best Regards,
Chris

Report message to a moderator

Re: createnativequery [message #1864565 is a reply to message #1864562]

Tue, 26 March 2024 04:45

Jayaram Subramanian

Messages: 3
Registered: March 2024

Junior Member

Thanks Chris, our product has a long history wherein
1) It was inclient server architecture and evolved over a period of 15-20 years with lot of db package calls. and each logged in user having a dedicated connection available wherein there can be multiple package calls on various app actions (button clicls, checkbox actions etc) and uses had the luxury of doing a final commit or cancel at the end.
2) Now we are in process of refactoring to the cloud architecture and one thought is to reuse the packages as much as possible with minimal changes as it may take year(s) to build/test. So found out a feature in eclipse link entity manager (https://eclipse.dev/eclipselink/documentation/2.5/jpa/extensions/p_jdbc_exclusiveconnection_mode.htm#:~:text=EclipseLink%20keeps%20the%20connection%20exclusive%20for%20the%20lifetime%20of%20the,read%20through%20the%20exclusive%20connection.) which kind of mimicked the dedicated db connection behavior and trying to execute the package calls . So the idea here is to have ems mapped to each user based on session cookie with code like below and have package calls executed under the em context

private static Map<String,EntityManager> emMap=new HashMap<String,EntityManager>();
..
..
..
Map<String, String> propertiesMap=new HashMap<>();
private String fetchJessionCookie() {
Map headerCookies=restFlowContext.getHeaders().getCookies();
return ((Cookie)headerCookies.get("JSESSIONID")).getValue();
}

propertiesMap.put(PersistenceUnitProperties.EXCLUSIVE_CONNECTION_MODE, "Always");
em =emf.createEntityManager(propertiesMap);
emMap.put(fetchJessionCookie(), em );

Report message to a moderator

Previous Topic:	Eclipselink slow on merge - Register the existing object
Next Topic:	Concurrently open statements:

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 27 16:39:44 GMT 2024

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter