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

i have application built on spring. let spring @transactional magic , works fine long operate on entities mapped java objects.

however, when want custom job on table not mapped of java entities, i'm stuck. time ago, found solution execute custom query this:

// em instance of entitymanager em.gettransaction().begin(); statement st = em.unwrap(connection.class).createstatement(); resultset rs = st.executequery("select custom my_data"); em.gettransaction().commit(); 

when try entity manager injected spring @persistencecontext annotation, receive obvious exception:

java.lang.illegalstateexception:  not allowed create transaction on shared entitymanager -  use spring transactions or ejb cmt instead 

i managed extract non-shared entity manager this:

@inject public void mycustomsqlexecutor(entitymanagerfactory emf){     entitymanager em = emf.createentitymanager();     // em.unwrap(...) stuff above works fine here } 

nevertheless, find solution neither comfortable nor elegant. wonder if there other way run custom sql queries in spring-transactional-driven environment?

for curious - problem appeared when tried create user accounts in application , in related forum @ once - did not want forum's users table mapped of java entities.

you can use createnativequery execute arbitrary sql on database.

entitymanager em = emf.createentitymanager(); list<object> results = em.createnativequery("select custom my_data").getresultlist(); 

---

the above answer still holds true edit in additional information may relevant people looking @ question.

while true can use createnativequery method execute native queries through entitymanager; there alternative (arguably better) way of doing if using spring framework.

the alternative method executing queries spring (that behave configured transactions) use jdbctemplate. possible use both jdbctemplate and jpa entitymanager within same application. configuration this:

infrastructureconfig.class:

@configuration @import(appconfig.class) public class infrastructureconfig {      @bean //creates 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 entitymanagerfactory , datasource shared emf , 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 example repository use both jpa , jdbc:

public class jpaandjdbcdomainrepository implements domainrepository{      private jdbctemplate template;     private entitymanager entitymanager;      //inject jdbctemplate (or datasource , construct new jdbctemplate)     public domainrepository(jdbctemplate template){         this.template = template;     }      //inject entitymanager     @persistencecontext     void setentitymanager(entitymanager entitymanager) {         this.entitymanager = entitymanager;     }      //execute jpa query     public domainobject getdomainobject(long id){         return entitymanager.find(id);     }      //execute native sql query     public list<map<string,object>> getdata(){         return template.queryforlist("select custom my_data");     } }