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

Question

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.

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

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
}

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?

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.

Answer 1

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.

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.

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:

@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:

@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:

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