如何使用Spring管理的事务性EntityManager执行自定义SQL查询 [英] How to execute custom SQL query with spring-managed transactional EntityManager

问题描述

我有一个基于Spring构建的应用程序.我让Spring做所有的@Transactional魔术，只要我对映射到Java对象的实体进行操作，一切都可以正常工作.

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

但是，当我想在未映射到我的任何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:

// 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();

当我尝试使用从Spring注入的带有@PersistenceContext批注的实体管理器尝试此操作时，我会收到几乎显而易见的异常:

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

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
}

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

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?

对于那些好奇的人-当我尝试在应用程序和相关论坛中立即创建用户帐户时出现了此问题-我不想将论坛的users表映射到我的任何Java实体.

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.

推荐答案

您可以使用

You can use createNativeQuery to execute any arbitrary SQL on your database.

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.

确实可以使用 createNativeQuery 方法以通过EntityManager执行本机查询；如果您使用的是Spring框架，则有另一种(可能更好)的方法.

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

使用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 and a JPA EntityManager within the same application. The configuration would look something like this:

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;
    }
}

还有一个同时使用JPA和JDBC的示例存储库:

And an example repository that would use both JPA and 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");
    }
}

这篇关于如何使用Spring管理的事务性EntityManager执行自定义SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！