Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Executing arbitrary SQL in the current session
Executing arbitrary SQL in the current session [message #688541] Fri, 24 June 2011 18:01 Go to next message
Kevin Pauli is currently offline Kevin Pauli
Messages: 6
Registered: June 2011
Junior Member
I am new to JPA 2.0/EclipseLink/Glassfish/JEE6, and have kind of a basic question.

I have a DAO in which most of the entities are mapped directly to columns using JPA annotations, so I use the EntityManager, and it works great with no issues.

However there are some tables in which I am constructing the SQL statements myself b/c they use oracle-specific functions (spatial), and I want very fine-grained control of the SQL. So I am building it with string concatenation. I would like to be able to enroll my SQL executions in the current transaction, if there is one already underway.

So naturally I don't want to go directly to the DriverManager and create my own connection, I was looking for some kind of EntityManager.executeArbitrarySQL(String) function that would find the current connection and make my SQL part of the current transaction. Am I off my rocker?

[Updated on: Fri, 24 June 2011 18:02]

Report message to a moderator

Re: Executing arbitrary SQL in the current session [message #689375 is a reply to message #688541] Mon, 27 June 2011 10:29 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 execute any SQL in JPA using a native Query.

i.e.
em.createNativeQuery(mySQL).executeUpdate(); (or getResultList())

There are also result set mappings, and EclipseLink has specialize support for stored procedure.

Note that EclipseLink also has support for Oracle spatial objects.

If you need raw JDBC access, you can get the JDBC connection from the EntityManager,

See,
http://wiki.eclipse.org/EclipseLink/Examples/JPA/EMAPI#Getting_a_JDBC_Connection_from_an_EntityManager

http://en.wikibooks.org/wiki/Java_Persistence/Querying#Native_SQL_Queries

http://wiki.eclipse.org/EclipseLink/FAQ/JPA#Are_stored_procedures_and_triggers_supported.3F

http://dev.eclipse.org/svnroot/rt/org.eclipse.persistence/trunk/examples/org.eclipse.persistence.example.jpa.spatial/


James : Wiki : Book : Blog : Twitter
(no subject) [message #689393 is a reply to message #688541] Mon, 27 June 2011 10:29 Go to previous message
James is currently offline James
Messages: 272
Registered: July 2009
Senior Member
You can execute any SQL in JPA using a native Query.

i.e.
em.createNativeQuery(mySQL).executeUpdate(); (or getResultList())

There are also result set mappings, and EclipseLink has specialize support for stored procedure.

Note that EclipseLink also has support for Oracle spatial objects.

If you need raw JDBC access, you can get the JDBC connection from the EntityManager,

See,
http://wiki.eclipse.org/EclipseLink/Examples/JPA/EMAPI#Getting_a_JDBC_Connection_from_an_EntityManager

http://en.wikibooks.org/wiki/Java_Persistence/Querying#Native_SQL_Queries

http://wiki.eclipse.org/EclipseLink/FAQ/JPA#Are_stored_procedures_and_triggers_supported.3F

http://dev.eclipse.org/svnroot/rt/org.eclipse.persistence/trunk/examples/org.eclipse.persistence.example.jpa.spatial/
--
James : http://wiki.eclipse.org/EclipseLink : http://en.wikibooks.org/wiki/Java_Persistence : http://java-persistence-performance.blogspot.com/
Previous Topic:Glassfish 3.0.1 + bundled EclipseLink (2.0.1) Lazy loading OneToOne
Next Topic:eclipselink.join-fetch not work at all
Goto Forum:
  


Current Time: Fri Aug 29 16:38:55 EDT 2014

Powered by FUDForum. Page generated in 0.01724 seconds