James, This latest suggestion works, but I also configured connection pooling, so I went back to your prior suggestion of using JpaTemplate and the nested anonymous classes and that now works also.
I know what you're thinking - why didn't I use connection pooling to begin with? Well since I had multiple issues to deal with, I considered pooling to be an optimization which could wait. I assumed that since my test scaffold was just a single-process/single-threaded, that it would just use a single connection to synchronously perform each step of persistence, but since I'm using sequence-based key generation, I think there is something in the OpenJPA implementation that assumes pooling is in use. I will let the OpenJPA guys know. Thanks again for your help. -Chris On Thu, Jan 31, 2013 at 9:14 PM, James Carman <jcar...@carmanconsulting.com> wrote: > Try this: > > JpaTransactionManager transactionManager = new > JpaTransactionManager(factory); > > TransactionTemplate template = new > TransactionTemplate(transactionManager); > template.execute(new TransactionCallbackWithoutResult() > { > @Override > protected void doInTransactionWithoutResult(TransactionStatus > status) > { > EntityManager em = > EntityManagerFactoryUtils.getTransactionalEntityManager(factory); > MyEntity entity = new MyEntity(); > em.persist(entity); > } > }); > > On Jan 31, 2013, at 5:49 PM, Chris Wolf <cwolf.a...@gmail.com> wrote: > >> James, >> >> Ok, now using TransactionTemplate, but I'm back to where I was - the >> same apparent connection "leak", only now >> the code is hideous with all those nested anonymous classes and whatnot... ;) >> >> >> -Chris >> >> static void saveToDB(final List<MdBaseData> data) throws Exception { >> EntityManagerFactory emf = Persistence >> .createEntityManagerFactory("marketdata"); >> >> final JpaTemplate jpaTempl = new JpaTemplate(emf); >> jpaTempl.afterPropertiesSet(); >> >> JpaTransactionManager jpaTxMgr = new >> JpaTransactionManager(emf); >> jpaTxMgr.afterPropertiesSet(); >> >> final TransactionTemplate txTmpl = new >> TransactionTemplate(jpaTxMgr); >> txTmpl.afterPropertiesSet(); >> >> TransactionStrategy txStrategy = new TransactionStrategy() { >> @SuppressWarnings("deprecation") >> public Object execute(final JpaCallback<?> callback) { >> return txTmpl.execute(new >> TransactionCallback<Object>() { >> public Object >> doInTransaction(TransactionStatus status) { >> return jpaTempl.execute(new >> JpaCallback<Object>() { >> public Object >> doInJpa(EntityManager entityManager) >> throws >> PersistenceException { >> return >> callback.doInJpa(entityManager); >> } >> }); >> } >> }); >> } >> }; >> >> txStrategy.execute(new JpaCallback<Object>() { >> @Override >> public Object doInJpa(EntityManager em) throws >> PersistenceException { >> for (MdBaseData bd : data) { >> em.persist(bd); >> } >> return null; // writing to db, not reading... >> } >> }); >> } >> >> >> On Thu, Jan 31, 2013 at 4:42 PM, James Carman >> <jcar...@carmanconsulting.com> wrote: >>> Try using TransactionTemplate. >>> >>> On Jan 31, 2013, at 4:19 PM, Chris Wolf <cwolf.a...@gmail.com> wrote: >>> >>>> James, >>>> >>>> As per your suggestion, I let Spring manage the transactions and the >>>> apparent connection "leak" went away and I only see one connection >>>> being opened per session, as expected. >>>> >>>> Unfortunately, nothing got inserted into the database. >>>> >>>> Thanks, >>>> >>>> -Chris >>>> >>>> My modified code looks like: >>>> >>>> >>>> static void saveToDB(List<MdBaseData> data) throws Exception { >>>> EntityManagerFactory emf = Persistence >>>> .createEntityManagerFactory("marketdata"); >>>> JpaTransactionManager jpaTxMgr = new >>>> JpaTransactionManager(emf); >>>> >>>> >>>> EntityManager em = >>>> >>>> jpaTxMgr.getEntityManagerFactory().createEntityManager(); >>>> TransactionStatus txStatus = >>>> jpaTxMgr.getTransaction(new >>>> DefaultTransactionDefinition()); >>>> try { >>>> //em.getTransaction().begin(); >>>> for (MdBaseData bd : data) { >>>> em.persist(bd); >>>> } >>>> //em.getTransaction().commit(); >>>> jpaTxMgr.commit(txStatus); >>>> } catch (Exception e) { >>>> jpaTxMgr.rollback(txStatus); >>>> e.printStackTrace(); >>>> } finally { >>>> //em.close(); >>>> } >>>> } >>>> >>>> On Thu, Jan 31, 2013 at 2:45 PM, James Carman >>>> <ja...@carmanconsulting.com> wrote: >>>>> Well, it looks like you're also managing your own transactions. >>>>> Perhaps just use Spring for the transaction management. >>>>> >>>>> >>>>> On Thu, Jan 31, 2013 at 2:35 PM, Chris Wolf <cwolf.a...@gmail.com> wrote: >>>>>> Right, I looked into that but the Spring docs say that JpaTemplate is >>>>>> deprecated in favor of native JPA APIs. >>>>>> >>>>>> http://static.springsource.org/spring/docs/3.1.x/javadoc-api/org/springframework/orm/jpa/JpaTemplate.html >>>>>> >>>>>> I am using OpenJPA-2.1 >>>>>> >>>>>> Thanks, >>>>>> >>>>>> -Chris >>>>>> >>>>>> On Thu, Jan 31, 2013 at 2:29 PM, James Carman >>>>>> <ja...@carmanconsulting.com> wrote: >>>>>>> Well, you're managing your entity manager yourself, right? You could >>>>>>> have a bug in there that's leaving it open? Can you use Spring or >>>>>>> some other technology to do this for you in a way where you can be >>>>>>> sure everything gets cleaned up? >>>>>>> >>>>>>> >>>>>>> On Thu, Jan 31, 2013 at 2:28 PM, Chris Wolf <cwolf.a...@gmail.com> >>>>>>> wrote: >>>>>>>> Howdy Matthias, >>>>>>>> >>>>>>>> Yes, in an earlier post to this thread, I mentioned that 120 >>>>>>>> connections were being opened (the max). So, yes, the max connections >>>>>>>> limit is being hit. >>>>>>>> >>>>>>>> I observe this by tailing the TNS listener log >>>>>>>> >>>>>>>> c:/app/<userId>/diag/tnslsnr/<userId>/listener/trace/listener.log >>>>>>>> >>>>>>>> (Oracle-11g on Windows7) >>>>>>>> >>>>>>>> >>>>>>>> The question is why? Unless I have a connection leak. >>>>>>>> >>>>>>>> -Chris >>>>>>>> >>>>>>>> >>>>>>>> On Thu, Jan 31, 2013 at 2:21 PM, Matthias Lüneberg >>>>>>>> <matthias.lueneb...@googlemail.com> wrote: >>>>>>>>> Hi, >>>>>>>>> >>>>>>>>> Is it possible that the oracle database has reached its max >>>>>>>>> connections/processes limit? >>>>>>>>> >>>>>>>>> I found an article: >>>>>>>>> http://www.dadbm.com/2011/11/oracle-listener-refused-connection-ora-12519-troubleshooting/ >>>>>>>>> >>>>>>>>> Maybe its a database failure. Can you have a look at this? >>>>>>>>> >>>>>>>>> HTH, Matthias >>>>>>>>> >>>>>>>>> Am 31.01.2013 um 18:48 schrieb Chris Wolf <cwolf.a...@gmail.com>: >>>>>>>>> >>>>>>>>>> James, >>>>>>>>>> >>>>>>>>>> Here is what it looks like. At first, I was using the "jpa://" >>>>>>>>>> endpoint, but then I needed to implement a custom processor to handle >>>>>>>>>> additional logic. Thanks for having a look... >>>>>>>>>> >>>>>>>>>> -Chris >>>>>>>>>> >>>>>>>>>> from("file:/tmp/local/data?filter=#filter&preMove=/tmp/local/holding/${file:onlyname}.${id}&move=/tmp/local/archive&moveFailed=/tmp/local/errors&readLock=changed") >>>>>>>>>> .unmarshal(cpmdfileFormat) >>>>>>>>>> .processRef("dataStore") >>>>>>>>>> //.to("jpa:?persistenceUnit=marketdata") >>>>>>>>>> .beanRef("oneshotPoller", "resumeJpaConsumer"); // signal consumer >>>>>>>>>> route to begin >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> Bean for "dataStore": >>>>>>>>>> >>>>>>>>>> public class CPMDPersister implements Processor { >>>>>>>>>> private static final transient Logger log = >>>>>>>>>> LoggerFactory.getLogger(CPMDPersister.class); >>>>>>>>>> IGenericDAO<MdBaseData> mdBaseDataDAO; >>>>>>>>>> IGenericDAO<MdCurveData> mdCurveDataDAO; >>>>>>>>>> >>>>>>>>>> public CPMDPersister(){} >>>>>>>>>> >>>>>>>>>> public CPMDPersister(IGenericDAO<MdBaseData> mdBaseDataDAO, >>>>>>>>>> IGenericDAO<MdCurveData> mdCurveDataDAO) { >>>>>>>>>> this.mdBaseDataDAO = mdBaseDataDAO; >>>>>>>>>> this.mdCurveDataDAO = mdCurveDataDAO; >>>>>>>>>> } >>>>>>>>>> >>>>>>>>>> @Override >>>>>>>>>> public void process(Exchange exchange) throws Exception { >>>>>>>>>> Message inMsg = exchange.getIn(); >>>>>>>>>> >>>>>>>>>> @SuppressWarnings("unchecked") >>>>>>>>>> List<MdBaseData> mdBaseData = >>>>>>>>>> (ArrayList<MdBaseData>)inMsg.getBody(ArrayList.class); >>>>>>>>>> >>>>>>>>>> IntervalElapsedTimer avg = new IntervalElapsedTimer(); >>>>>>>>>> avg.start(); >>>>>>>>>> mdBaseDataDAO.begin(); >>>>>>>>>> >>>>>>>>>> for (MdBaseData md : mdBaseData) { >>>>>>>>>> setParentReferences(md); >>>>>>>>>> mdBaseDataDAO.save(md); >>>>>>>>>> avg.lap(); >>>>>>>>>> log.debug("JPA Persist MD: {}, {}", >>>>>>>>>> new Object[] {md.toString(), avg.toString()}); >>>>>>>>>> } >>>>>>>>>> >>>>>>>>>> mdBaseDataDAO.commit(); >>>>>>>>>> >>>>>>>>>> log.debug("JPA Persist: {}", avg.toString()); >>>>>>>>>> int a = 0; >>>>>>>>>> } >>>>>>>>>> >>>>>>>>>> void setParentReferences(MdBaseData md) { >>>>>>>>>> for (MdCurveData cd : md.getMdCurveData()) { >>>>>>>>>> if (cd.getMdBaseData() == null) >>>>>>>>>> cd.setMdBaseData(new >>>>>>>>>> ArrayList<MdBaseData>()); >>>>>>>>>> if (!cd.getMdBaseData().contains(md)) >>>>>>>>>> cd.getMdBaseData().add(md); >>>>>>>>>> } >>>>>>>>>> } >>>>>>>>>> } >>>>>>>>>> >>>>>>>>>> The DAOs, are a thin wrapper around EntityManager, like: >>>>>>>>>> >>>>>>>>>> @SuppressWarnings("unchecked") >>>>>>>>>> public List<T> findAll() { >>>>>>>>>> return entityManager.createQuery("from " + >>>>>>>>>> entityType.getName()) >>>>>>>>>> .getResultList(); >>>>>>>>>> } >>>>>>>>>> public void begin() { >>>>>>>>>> this.entityManager.getTransaction().begin(); >>>>>>>>>> } >>>>>>>>>> >>>>>>>>>> public void commit() { >>>>>>>>>> this.entityManager.getTransaction().commit(); >>>>>>>>>> this.entityManager.clear(); >>>>>>>>>> } >>>>>>>>>> >>>>>>>>>> public void rollback() { >>>>>>>>>> this.entityManager.getTransaction().rollback(); >>>>>>>>>> } >>>>>>>>>> >>>>>>>>>> On Thu, Jan 31, 2013 at 12:34 PM, James Carman >>>>>>>>>> <ja...@carmanconsulting.com> wrote: >>>>>>>>>>> What does your route look like? >>>>>>>>>>> >>>>>>>>>>> >>>>>>>>>>> On Thu, Jan 31, 2013 at 11:58 AM, Chris Wolf <cwolf.a...@gmail.com> >>>>>>>>>>> wrote: >>>>>>>>>>>> I have further determined that it's an OpenJPA issue (or user >>>>>>>>>>>> error, >>>>>>>>>>>> but how can that be?) ;) >>>>>>>>>>>> >>>>>>>>>>>> I will report back when I get to the bottom of it. Thanks a lot, >>>>>>>>>>>> >>>>>>>>>>>> Chris >>>>>>>>>>>> >>>>>>>>>>>> On Sat, Jan 26, 2013 at 2:47 AM, Willem Jiang >>>>>>>>>>>> <willem.ji...@gmail.com> wrote: >>>>>>>>>>>>> Can you share us your camel route? It is important for us to find >>>>>>>>>>>>> out the key reason of your issue. >>>>>>>>>>>>> >>>>>>>>>>>>> >>>>>>>>>>>>> 发自我的 iPhone >>>>>>>>>>>>> >>>>>>>>>>>>> 在 2013-1-26,上午5:22,Chris Wolf <cwolf.a...@gmail.com> 写道: >>>>>>>>>>>>> >>>>>>>>>>>>>> On Fri, Jan 25, 2013 at 3:55 PM, Chris Wolf >>>>>>>>>>>>>> <cwolf.a...@gmail.com> wrote: >>>>>>>>>>>>>>> I ask because when I use jpa: in producer mode, I get this >>>>>>>>>>>>>>> error: >>>>>>>>>>>>>>> Caused by: oracle.net.ns.NetException: Listener refused the >>>>>>>>>>>>>>> connection >>>>>>>>>>>>>>> with the following error: >>>>>>>>>>>>>>> ORA-12519, TNS:no appropriate service handler found >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> Strangely, using jpa: in consumer mode, this does not happen. >>>>>>>>>>>>>>> In the >>>>>>>>>>>>>>> tnslsnr.log, I observe 120 connections being opened when >>>>>>>>>>>>>>> running the route with jpa: in producer mode. If I instead use >>>>>>>>>>>>>>> a >>>>>>>>>>>>>>> custom Processor and do my own JPA persistence code, >>>>>>>>>>>>>>> the issue does not occur. >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> Thanks for any advice, >>>>>>>>>>>>>>> >>>>>>>>>>>>>>> Chris >>>>>>>>>>>>>> >>>>>>>>>>>>>> >>>>>>>>>>>>>> I stepped through with the debugger and the problem went away. >>>>>>>>>>>>>> The >>>>>>>>>>>>>> point at which a lot of connections were opened was >>>>>>>>>>>>>> after I stepped over: >>>>>>>>>>>>>> >>>>>>>>>>>>>> return jpaTemplate.execute(new JpaCallback<Object>() >>>>>>>>>>>>>> { >>>>>>>>>>>>>> public Object doInJpa(EntityManager >>>>>>>>>>>>>> entityManager) >>>>>>>>>>>>>> throws PersistenceException { >>>>>>>>>>>>>> return callback.doInJpa(entityManager); >>>>>>>>>>>>>> } >>>>>>>>>>>>>> >>>>>>>>>>>>>> ..and when running in real time (not debugging) it seems to be >>>>>>>>>>>>>> running >>>>>>>>>>>>>> in an AsyncProcessor - I wonder if it's too many worker >>>>>>>>>>>>>> threads eating up the connections? BTW, this is mt first project >>>>>>>>>>>>>> using Camel, so forgive the lack of experience... >>>>>>>>>>>>>> >>>>>>>>>>>>>> >>>>>>>>>>>>>> [ad #1 - file:///tmp/local/data] UnmarshallingContext >>>>>>>>>>>>>> DEBUG >>>>>>>>>>>>>> EOF reached at line 2007 >>>>>>>>>>>>>> [ad #1 - file:///tmp/local/data] SendProcessor >>>>>>>>>>>>>> DEBUG >>>>>>>>>>>>>>>>>> Endpoint[jpa://?persistenceUnit=marketdata] Exchange[null] >>>>>>>>>>>>>> [ad #1 - file:///tmp/local/data] JpaTransactionManager >>>>>>>>>>>>>> DEBUG >>>>>>>>>>>>>> Creating new transaction with name [null]: >>>>>>>>>>>>>> PROPAGATION_REQUIRED,ISOLATION_DEFAULT >>>>>>>>>>>>>> 8505 marketdata INFO [Camel (camel-1) thread #1 - >>>>>>>>>>>>>> file:///tmp/local/data] openjpa.Runtime - Starting OpenJPA 2.2.1 >>>>>>>>>>>>>> 8535 marketdata INFO [Camel (camel-1) thread #1 - >>>>>>>>>>>>>> file:///tmp/local/data] openjpa.jdbc.JDBC - Using dictionary >>>>>>>>>>>>>> class >>>>>>>>>>>>>> "org.apache.openjpa.jdbc.sql.OracleDictionary". >>>>>>>>>>>>>> [ad #1 - file:///tmp/local/data] JpaTransactionManager >>>>>>>>>>>>>> DEBUG >>>>>>>>>>>>>> Opened new EntityManager >>>>>>>>>>>>>> [org.apache.openjpa.persistence.EntityManagerImpl@798b429b] for >>>>>>>>>>>>>> JPA >>>>>>>>>>>>>> transaction >>>>>>>>>>>>>> [ad #1 - file:///tmp/local/data] JpaTransactionManager >>>>>>>>>>>>>> DEBUG >>>>>>>>>>>>>> Initiating transaction commit >>>>>>>>>>>>>> [ad #1 - file:///tmp/local/data] JpaTransactionManager >>>>>>>>>>>>>> DEBUG >>>>>>>>>>>>>> Committing JPA transaction on EntityManager >>>>>>>>>>>>>> [org.apache.openjpa.persistence.EntityManagerImpl@798b429b] >>>>>>>>>>>>>> [ad #1 - file:///tmp/local/data] JpaTransactionManager >>>>>>>>>>>>>> DEBUG >>>>>>>>>>>>>> Closing JPA EntityManager >>>>>>>>>>>>>> [org.apache.openjpa.persistence.EntityManagerImpl@798b429b] after >>>>>>>>>>>>>> transaction >>>>>>>>>>>>>> [ad #1 - file:///tmp/local/data] EntityManagerFactoryUtils >>>>>>>>>>>>>> DEBUG >>>>>>>>>>>>>> Closing JPA EntityManager >>>>>>>>> >>> >