在JPA 2.0的当前事务中执行一些任意的sql [英] Execute some arbitrary sql in the current transaction in JPA 2.0

问题描述

我是JPA 2.0 / EclipseLink / Glassfish / JEE6的新手，并且有一个基本问题。

我有一个DAO，其中大多数实体都是使用JPA批注直接映射到列，所以我使用EntityManager，它工作良好，没有问题。

然而，有一些表格我自己构建SQL语句，他们使用的是特定于oracle的函数（空间），我想要非常细致的控制的SQL。所以我用字符串连接构建它。我希望能够在当前事务中注册我的SQL执行，如果有一个正在执行。

所以自然我不想直接进入DriverManager并创建自己的连接，我正在寻找某种EntityManager.executeArbitrarySQL（String）函数，它会找到当前连接并使我的SQL成为当前事务的一部分。我可以使用 EntityManager.createNativeQuery（） $ c>方法在您使用的同一个EntityManager的上下文中执行本机SQL查询。

第一种是 createNativeQuery（String sqlString，Class resultClass） 期望您提供表示查询将返回的值的类型的Class对象。这是为了在您返回一组可以映射到持久性单元中另一个实体定义类的值的情况下使用。

第二个 createNativeQuery（String sqlString，String resultSetMapping） 期望您提供结果集映射的名称。结果集映射应该使用 @SqlResultSetMapping 注释。

最后 createNativeQuery（String sqlString） 显然是用于没有返回结果集的场景，例如执行INSERT，UPDATE和DELETE语句。

您也可以使用 @NamedNativeQuery 注释或 persistence.xml 文件中的 named-native-query 元素，但这些更好适用于在开发过程中知道查询结构的场景。但是，您可以创建多个这种命名的本机查询来表示您希望执行的所有类型的SQL语句，然后根据用户输入在运行时执行不同的语句。带注释的本地查询是使用 EntityManager.createNamedQuery（）方法执行的。我们需要使用位置参数（使用？占位符定义）来为运行时的本机查询提供值。

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?

解决方案

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.

这篇关于在JPA 2.0的当前事务中执行一些任意的sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！