Java 如何使用 spring 管理的事务 EntityManager 执行自定义 SQL 查询

How to execute custom SQL query with spring-managed transactional EntityManager

提问by fracz

I have an application built on Spring. I let the Spring do the all @Transactionalmagic and everything works fine as long as I operate on my entities that are mapped to Java objects.

我有一个基于 Spring 构建的应用程序。我让 Spring 完成所有@Transactional魔法，只要我对映射到 Java 对象的实体进行操作，一切都会正常运行。

However, when I want to do some custom job on a table that is not mapped to any of my Java entities, I'm stuck. Some time ago, I found a solution to execute a custom query like this:

但是，当我想在未映射到我的任何 Java 实体的表上执行一些自定义作业时，我被卡住了。前段时间，我找到了一个解决方案来执行这样的自定义查询：

// em is instance of EntityManager
em.getTransaction().begin();
Statement st = em.unwrap(Connection.class).createStatement();
ResultSet rs = st.executeQuery("SELECT custom FROM my_data");
em.getTransaction().commit();

When I try this with the entity manager injected from Spring with the @PersistenceContextannotation, I receive almost obvious exception:

当我尝试使用从 Spring 注入的带有@PersistenceContext注释的实体管理器时，我收到几乎明显的异常：

java.lang.IllegalStateException: 
Not allowed to create transaction on shared EntityManager - 
use Spring transactions or EJB CMT instead

I finally managed to extract non-shared Entity Manager like this:

我终于设法像这样提取非共享实体管理器：

@Inject
public void myCustomSqlExecutor(EntityManagerFactory emf){
    EntityManager em = emf.createEntityManager();
    // the em.unwrap(...) stuff from above works fine here
}

Nevertheless, I find this solution neither comfortable nor elegant. I just wonder if there is any other way to run custom SQL queries in this Spring-transactional-driven environment?

尽管如此，我发现这个解决方案既不舒适也不优雅。我只是想知道在这个 Spring 事务驱动的环境中是否还有其他方法可以运行自定义 SQL 查询？

For those who are curious - this problem appeared when I tried to create user accounts in my application and in the related forum at once - I did not want the forum's users table to be mapped to any of my Java entities.

对于那些好奇的人——当我试图同时在我的应用程序和相关论坛中创建用户帐户时出现这个问题——我不希望论坛的用户表被映射到我的任何 Java 实体。

采纳答案by FGreg

You can use createNativeQueryto execute any arbitrary SQL on your database.

您可以使用createNativeQuery在您的数据库上执行任意 SQL。

EntityManager em = emf.createEntityManager();
List<Object> results = em.createNativeQuery("SELECT custom FROM my_data").getResultList();

---

---

The above answer still holds true but I would like to edit in some additional information that may also be relevant to people looking at this question.

上面的答案仍然适用，但我想编辑一些可能与查看此问题的人相关的其他信息。

While it is true that you can use the createNativeQuerymethod to execute native queries through an EntityManager; there is an alternative (arguably better) way of doing it if you are using the Spring Framework.

虽然确实可以使用createNativeQuery方法通过 EntityManager 执行本机查询；如果您使用的是 Spring 框架，则有一种替代（可以说是更好）的方法。

The alternative method for executing queries with Spring (that will behave with the configured transactions) is to use the JDBCTemplate. It is possible to use both the JDBCTemplate anda JPA EntityManager within the same application. The configuration would look something like this:

使用 Spring 执行查询的替代方法（将与配置的事务一起工作）是使用JDBCTemplate。可以在同一应用程序中同时使用 JDBCTemplate和JPA EntityManager。配置看起来像这样：

InfrastructureConfig.class:

InfrastructureConfig.class：

@Configuration
@Import(AppConfig.class)
public class InfrastructureConfig {

    @Bean //Creates an in-memory database.
    public DataSource dataSource(){
        return new EmbeddedDatabaseBuilder().build(); 
    }   

    @Bean //Creates our EntityManagerFactory
    public AbstractEntityManagerFactoryBean entityManagerFactory(DataSource dataSource){
        LocalContainerEntityManagerFactoryBean emf = new LocalContainerEntityManagerFactoryBean();
        emf.setDataSource(dataSource);
        emf.setJpaVendorAdapter(new HibernateJpaVendorAdapter());

        return emf;
    }

    @Bean //Creates our PlatformTransactionManager. Registering both the EntityManagerFactory and the DataSource to be shared by the EMF and JDBCTemplate
    public PlatformTransactionManager transactionManager(EntityManagerFactory emf, DataSource dataSource){
        JpaTransactionManager tm = new JpaTransactionManager(emf);
        tm.setDataSource(dataSource);
        return tm;
    }

}

AppConfig.class:

AppConfig.class：

@Configuration
@EnableTransactionManagement
public class AppConfig {

    @Bean
    public MyService myTransactionalService(DomainRepository domainRepository) {
        return new MyServiceImpl(domainRepository);
    }

    @Bean
    public DomainRepository domainRepository(JdbcTemplate template){
        return new JpaAndJdbcDomainRepository(template);
    }

    @Bean
    public JdbcTemplate jdbcTemplate(DataSource dataSource){
        JdbcTemplate template = new JdbcTemplate(dataSource);
        return template;
    }
}

And an example repository that would use both JPA and JDBC:

以及一个同时使用 JPA 和 JDBC 的示例存储库：

public class JpaAndJdbcDomainRepository implements DomainRepository{

    private JdbcTemplate template;
    private EntityManager entityManager;

    //Inject the JdbcTemplate (or the DataSource and construct a new JdbcTemplate)
    public DomainRepository(JdbcTemplate template){
        this.template = template;
    }

    //Inject the EntityManager
    @PersistenceContext
    void setEntityManager(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    //Execute a JPA query
    public DomainObject getDomainObject(Long id){
        return entityManager.find(id);
    }

    //Execute a native SQL Query
    public List<Map<String,Object>> getData(){
        return template.queryForList("select custom from my_data");
    }
}

回答by Luca Basso Ricci

You can use EntityManager.createNativeQuery(String sql)to use direct sql code or use EntityManager.createNamedQuery(String name)to execute precompiled query. You still use spring-managed Entity manager, but working on non managed objects

您可以使用EntityManager.createNativeQuery(String sql)使用直接 sql 代码或使用EntityManager.createNamedQuery(String name)执行预编译查询。您仍然使用 spring 管理的实体管理器，但在非托管对象上工作