Execute some arbitrary sql in the current transact

Question

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?

Answer 1

One can use the EntityManager.createNativeQuery() methods to execute native SQL queries within the context of the same EntityManager that you are using. There are two three different types of these methods, and they differ in the arguments provided.

The first, createNativeQuery(String sqlString, Class resultClass) expects you to provide the Class object representing the type of the values that will be returned by the query. This is to be used in case you are returning a set of values that can be mapped to the class of another entity definiton in your persistence unit.

The second createNativeQuery(String sqlString, String resultSetMapping) expects you to provide the name of the result set mapping. The result set mapping ought to be defined using the @SqlResultSetMapping annotation.

The last createNativeQuery(String sqlString) is apparently meant to be used in scenarios where no result set will be returned, i.e. in execution of INSERT, UPDATE and DELETE statements.

You can also define native queries using the @NamedNativeQuery annotation or the named-native-query element in your persistence.xml file, but these are better suited for scenarios where you know the structure of the query during development. You can however, create multiple such named native queries to represent all varieties of the SQL statement you intend to execute, and then execute different ones at runtime based on the user inputs. Annotated native queries are executed using the EntityManager.createNamedQuery() methods. One will need to use positional parameters (defined using the ? placeholder) to supply values to the native queries at runtime.