You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@camel.apache.org by James Carman <jc...@carmanconsulting.com> on 2013/02/01 03:14:41 UTC
Re: Does Camel JPA component implement it's own connection pooling?
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 <cw...@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
> <jc...@carmanconsulting.com> wrote:
>> Try using TransactionTemplate.
>>
>> On Jan 31, 2013, at 4:19 PM, Chris Wolf <cw...@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 <cw...@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 <cw...@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
>>>>>>> <ma...@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 <cw...@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 <cw...@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 <wi...@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 <cw...@gmail.com> 写道：
>>>>>>>>>>>>
>>>>>>>>>>>>> On Fri, Jan 25, 2013 at 3:55 PM, Chris Wolf <cw...@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
>>>>>>>>
>>
Re: Does Camel JPA component implement it's own connection pooling?
Posted by Chris Wolf <cw...@gmail.com>.
James,
Thanks for this - I deploy with Spring, but for small POC type stuff,
I usually like the convenience of having all the code in one place,
including wiring the collaborators.
-Chris
On Fri, Feb 1, 2013 at 11:00 AM, James Carman
<jc...@carmanconsulting.com> wrote:
> Chris,
>
> Something like this works for me (had to change it a bit to sanitize it):
>
> <persistence xmlns="http://java.sun.com/xml/ns/persistence"
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
> version="2.0">
> <persistence-unit name="metadata-service" transaction-type="RESOURCE_LOCAL">
> <provider>org.apache.openjpa.persistence.PersistenceProviderImpl</provider>
> <class>com.myco.entity.MyEntity</class>
> <exclude-unlisted-classes>true</exclude-unlisted-classes>
> <properties>
> <property name="openjpa.Log" value="slf4j" />
> </properties>
> </persistence-unit>
> </persistence>
>
> I use Spring to configure it, though:
>
> <bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
> <property name="dataSource" ref="dataSource"/>
> <property name="jpaProperties">
> <props>
> <prop key="openjpa.jdbc.SynchronizeMappings">${db.synchronizeMappings}</prop>
> </props>
> </property>
> </bean>
>
> I have found that just using Spring from the get-go actually saves me so much time, because I don't get these little weird hiccups (I get others ;). This stuff has been tested by lots of folks before me. :)
>
> James
>
> On Feb 1, 2013, at 10:39 AM, Chris Wolf <cw...@gmail.com> wrote:
>
>> James,
>>
>> I just replied to your message from last night - I got it working by
>> using your latest suggestion plus enabling DBCP connection pooling,
>> although I don't know why pooling should matter.
>>
>> I notice that you were able to get OpenJPA logging to work - I'd sure
>> be interested in seeing your persistence.xml settings for that - I
>> have not been able to get any logging from OpenJPA and I tried the
>> default, the log4j and slf4j backends - all to no avail...
>>
>> As for my final implementation, I think this latest code from you is
>> the cleanest (for my purposes). Again, thanks so much for the help.
>>
>> -Chris
>>
>> On Fri, Feb 1, 2013 at 6:32 AM, James Carman
>> <jc...@carmanconsulting.com> wrote:
>>> Chris,
>>>
>>> Here's my complete class:
>>>
>>> public class JpaTest
>>> {
>>> public static void main(String[] args)
>>> {
>>> ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext("classpath:/META-INF/spring-beans.xml");
>>> final EntityManagerFactory factory = ctx.getBean(EntityManagerFactory.class);
>>> final 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);
>>> for (int i = 0; i < 10; ++i)
>>> {
>>> MyEntity entity = new MyEntity();
>>> entity.setName("name");
>>> em.persist(entity);
>>> }
>>> }
>>> });
>>>
>>> }
>>> }
>>>
>>>
>>> Here's the output I see when I turn up OpenJPA's logging:
>>>
>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 1546366616 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 702383716 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 245507253 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 924315284 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 70275790 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 9196680 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 2097261823 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 2030360950 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 761335820 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 1063557914 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>>> 02-01@06:30:54 TRACE (JDBC) - <t 2063246672, conn 936619118> [1 ms] commit
>>> 02-01@06:30:54 TRACE (JDBC) - <t 2063246672, conn 936619118> [0 ms] close
>>>
>>>
>>> It looks like it'll use the same connection object for each insert and commit and close it at the end (I'm using DriverManagerDataSource just to make sure it won't pool).
>>>
>>>
>>> On Jan 31, 2013, at 9:14 PM, James Carman <jc...@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 <cw...@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
>>>>> <jc...@carmanconsulting.com> wrote:
>>>>>> Try using TransactionTemplate.
>>>>>>
>>>>>> On Jan 31, 2013, at 4:19 PM, Chris Wolf <cw...@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 <cw...@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 <cw...@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
>>>>>>>>>>> <ma...@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 <cw...@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 <cw...@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 <wi...@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 <cw...@gmail.com> 写道：
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>> On Fri, Jan 25, 2013 at 3:55 PM, Chris Wolf <cw...@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
>>>>>>>>>>>>
>>>>>>
>>>>
>>>
>
Re: Does Camel JPA component implement it's own connection pooling?
Posted by James Carman <jc...@carmanconsulting.com>.
Chris,
Something like this works for me (had to change it a bit to sanitize it):
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
version="2.0">
<persistence-unit name="metadata-service" transaction-type="RESOURCE_LOCAL">
<provider>org.apache.openjpa.persistence.PersistenceProviderImpl</provider>
<class>com.myco.entity.MyEntity</class>
<exclude-unlisted-classes>true</exclude-unlisted-classes>
<properties>
<property name="openjpa.Log" value="slf4j" />
</properties>
</persistence-unit>
</persistence>
I use Spring to configure it, though:
<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
<property name="dataSource" ref="dataSource"/>
<property name="jpaProperties">
<props>
<prop key="openjpa.jdbc.SynchronizeMappings">${db.synchronizeMappings}</prop>
</props>
</property>
</bean>
I have found that just using Spring from the get-go actually saves me so much time, because I don't get these little weird hiccups (I get others ;). This stuff has been tested by lots of folks before me. :)
James
On Feb 1, 2013, at 10:39 AM, Chris Wolf <cw...@gmail.com> wrote:
> James,
>
> I just replied to your message from last night - I got it working by
> using your latest suggestion plus enabling DBCP connection pooling,
> although I don't know why pooling should matter.
>
> I notice that you were able to get OpenJPA logging to work - I'd sure
> be interested in seeing your persistence.xml settings for that - I
> have not been able to get any logging from OpenJPA and I tried the
> default, the log4j and slf4j backends - all to no avail...
>
> As for my final implementation, I think this latest code from you is
> the cleanest (for my purposes). Again, thanks so much for the help.
>
> -Chris
>
> On Fri, Feb 1, 2013 at 6:32 AM, James Carman
> <jc...@carmanconsulting.com> wrote:
>> Chris,
>>
>> Here's my complete class:
>>
>> public class JpaTest
>> {
>> public static void main(String[] args)
>> {
>> ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext("classpath:/META-INF/spring-beans.xml");
>> final EntityManagerFactory factory = ctx.getBean(EntityManagerFactory.class);
>> final 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);
>> for (int i = 0; i < 10; ++i)
>> {
>> MyEntity entity = new MyEntity();
>> entity.setName("name");
>> em.persist(entity);
>> }
>> }
>> });
>>
>> }
>> }
>>
>>
>> Here's the output I see when I turn up OpenJPA's logging:
>>
>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 1546366616 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 702383716 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 245507253 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 924315284 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 70275790 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 9196680 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 2097261823 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 2030360950 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 761335820 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 1063557914 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>> 02-01@06:30:54 TRACE (JDBC) - <t 2063246672, conn 936619118> [1 ms] commit
>> 02-01@06:30:54 TRACE (JDBC) - <t 2063246672, conn 936619118> [0 ms] close
>>
>>
>> It looks like it'll use the same connection object for each insert and commit and close it at the end (I'm using DriverManagerDataSource just to make sure it won't pool).
>>
>>
>> On Jan 31, 2013, at 9:14 PM, James Carman <jc...@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 <cw...@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
>>>> <jc...@carmanconsulting.com> wrote:
>>>>> Try using TransactionTemplate.
>>>>>
>>>>> On Jan 31, 2013, at 4:19 PM, Chris Wolf <cw...@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 <cw...@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 <cw...@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
>>>>>>>>>> <ma...@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 <cw...@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 <cw...@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 <wi...@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 <cw...@gmail.com> 写道：
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>> On Fri, Jan 25, 2013 at 3:55 PM, Chris Wolf <cw...@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
>>>>>>>>>>>
>>>>>
>>>
>>
Re: Does Camel JPA component implement it's own connection pooling?
Posted by Chris Wolf <cw...@gmail.com>.
Ok, right, so even if I got the log4j or slf4j LogFactory to work, the
finest grain category would still map to one of their "channels",
rather then per-class. I wish they hadn't chosen to implement a
log-adapter-adapter...
On Fri, Feb 1, 2013 at 11:02 AM, James Carman
<jc...@carmanconsulting.com> wrote:
> The problem you're facing is that they use a limited set of "channels" for their logging:
>
> http://openjpa.apache.org/builds/1.0.2/apache-openjpa-1.0.2/docs/manual/ref_guide_logging.html#ref_guide_logging_channels
>
> So, you're kind of stuck with those knobs to turn. It is rather verbose, I'll agree. Unfortunately, for a lot of the channels, you have to jack them all the way up to TRACE to get them to give you anything.
>
> On Feb 1, 2013, at 10:51 AM, Chris Wolf <cw...@gmail.com> wrote:
>
>> Just to clarify, for me, these logging settings work:
>>
>> <property name="openjpa.Log" value="openjpa.jdbc.JDBC=TRACE"/>
>>
>>
>> -= or, for SQL =-
>>
>> <property name="openjpa.Log" value="SQL=TRACE"/>
>> <property name="openjpa.ConnectionFactoryProperties"
>> value="PrettyPrint=true, PrettyPrintLineLength=72"/>
>> <property name="openjpa.ConnectionFactoryProperties"
>> value="printParameters=true"/>
>>
>> But the first one (JDBC logging) is too verbose - it's either all or
>> nothing, which is why I wanted to setup the slf4j or log4j backend to
>> be able to have more fine-grained settings. If you've been able to
>> figure that out, that would be great. I've already posted on the
>> openjpa users list about that, but nothing from there yet...
>>
>>
>>
>> On Fri, Feb 1, 2013 at 10:39 AM, Chris Wolf <cw...@gmail.com> wrote:
>>> James,
>>>
>>> I just replied to your message from last night - I got it working by
>>> using your latest suggestion plus enabling DBCP connection pooling,
>>> although I don't know why pooling should matter.
>>>
>>> I notice that you were able to get OpenJPA logging to work - I'd sure
>>> be interested in seeing your persistence.xml settings for that - I
>>> have not been able to get any logging from OpenJPA and I tried the
>>> default, the log4j and slf4j backends - all to no avail...
>>>
>>> As for my final implementation, I think this latest code from you is
>>> the cleanest (for my purposes). Again, thanks so much for the help.
>>>
>>> -Chris
>>>
>>> On Fri, Feb 1, 2013 at 6:32 AM, James Carman
>>> <jc...@carmanconsulting.com> wrote:
>>>> Chris,
>>>>
>>>> Here's my complete class:
>>>>
>>>> public class JpaTest
>>>> {
>>>> public static void main(String[] args)
>>>> {
>>>> ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext("classpath:/META-INF/spring-beans.xml");
>>>> final EntityManagerFactory factory = ctx.getBean(EntityManagerFactory.class);
>>>> final 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);
>>>> for (int i = 0; i < 10; ++i)
>>>> {
>>>> MyEntity entity = new MyEntity();
>>>> entity.setName("name");
>>>> em.persist(entity);
>>>> }
>>>> }
>>>> });
>>>>
>>>> }
>>>> }
>>>>
>>>>
>>>> Here's the output I see when I turn up OpenJPA's logging:
>>>>
>>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 1546366616 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 702383716 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 245507253 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 924315284 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 70275790 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 9196680 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 2097261823 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 2030360950 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 761335820 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 1063557914 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>>>> 02-01@06:30:54 TRACE (JDBC) - <t 2063246672, conn 936619118> [1 ms] commit
>>>> 02-01@06:30:54 TRACE (JDBC) - <t 2063246672, conn 936619118> [0 ms] close
>>>>
>>>>
>>>> It looks like it'll use the same connection object for each insert and commit and close it at the end (I'm using DriverManagerDataSource just to make sure it won't pool).
>>>>
>>>>
>>>> On Jan 31, 2013, at 9:14 PM, James Carman <jc...@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 <cw...@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
>>>>>> <jc...@carmanconsulting.com> wrote:
>>>>>>> Try using TransactionTemplate.
>>>>>>>
>>>>>>> On Jan 31, 2013, at 4:19 PM, Chris Wolf <cw...@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 <cw...@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 <cw...@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
>>>>>>>>>>>> <ma...@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 <cw...@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 <cw...@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 <wi...@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 <cw...@gmail.com> 写道：
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>> On Fri, Jan 25, 2013 at 3:55 PM, Chris Wolf <cw...@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
>>>>>>>>>>>>>
>>>>>>>
>>>>>
>>>>
>
Re: Does Camel JPA component implement it's own connection pooling?
Posted by James Carman <jc...@carmanconsulting.com>.
The problem you're facing is that they use a limited set of "channels" for their logging:
http://openjpa.apache.org/builds/1.0.2/apache-openjpa-1.0.2/docs/manual/ref_guide_logging.html#ref_guide_logging_channels
So, you're kind of stuck with those knobs to turn. It is rather verbose, I'll agree. Unfortunately, for a lot of the channels, you have to jack them all the way up to TRACE to get them to give you anything.
On Feb 1, 2013, at 10:51 AM, Chris Wolf <cw...@gmail.com> wrote:
> Just to clarify, for me, these logging settings work:
>
> <property name="openjpa.Log" value="openjpa.jdbc.JDBC=TRACE"/>
>
>
> -= or, for SQL =-
>
> <property name="openjpa.Log" value="SQL=TRACE"/>
> <property name="openjpa.ConnectionFactoryProperties"
> value="PrettyPrint=true, PrettyPrintLineLength=72"/>
> <property name="openjpa.ConnectionFactoryProperties"
> value="printParameters=true"/>
>
> But the first one (JDBC logging) is too verbose - it's either all or
> nothing, which is why I wanted to setup the slf4j or log4j backend to
> be able to have more fine-grained settings. If you've been able to
> figure that out, that would be great. I've already posted on the
> openjpa users list about that, but nothing from there yet...
>
>
>
> On Fri, Feb 1, 2013 at 10:39 AM, Chris Wolf <cw...@gmail.com> wrote:
>> James,
>>
>> I just replied to your message from last night - I got it working by
>> using your latest suggestion plus enabling DBCP connection pooling,
>> although I don't know why pooling should matter.
>>
>> I notice that you were able to get OpenJPA logging to work - I'd sure
>> be interested in seeing your persistence.xml settings for that - I
>> have not been able to get any logging from OpenJPA and I tried the
>> default, the log4j and slf4j backends - all to no avail...
>>
>> As for my final implementation, I think this latest code from you is
>> the cleanest (for my purposes). Again, thanks so much for the help.
>>
>> -Chris
>>
>> On Fri, Feb 1, 2013 at 6:32 AM, James Carman
>> <jc...@carmanconsulting.com> wrote:
>>> Chris,
>>>
>>> Here's my complete class:
>>>
>>> public class JpaTest
>>> {
>>> public static void main(String[] args)
>>> {
>>> ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext("classpath:/META-INF/spring-beans.xml");
>>> final EntityManagerFactory factory = ctx.getBean(EntityManagerFactory.class);
>>> final 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);
>>> for (int i = 0; i < 10; ++i)
>>> {
>>> MyEntity entity = new MyEntity();
>>> entity.setName("name");
>>> em.persist(entity);
>>> }
>>> }
>>> });
>>>
>>> }
>>> }
>>>
>>>
>>> Here's the output I see when I turn up OpenJPA's logging:
>>>
>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 1546366616 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 702383716 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 245507253 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 924315284 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 70275790 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 9196680 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 2097261823 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 2030360950 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 761335820 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 1063557914 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>>> 02-01@06:30:54 TRACE (JDBC) - <t 2063246672, conn 936619118> [1 ms] commit
>>> 02-01@06:30:54 TRACE (JDBC) - <t 2063246672, conn 936619118> [0 ms] close
>>>
>>>
>>> It looks like it'll use the same connection object for each insert and commit and close it at the end (I'm using DriverManagerDataSource just to make sure it won't pool).
>>>
>>>
>>> On Jan 31, 2013, at 9:14 PM, James Carman <jc...@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 <cw...@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
>>>>> <jc...@carmanconsulting.com> wrote:
>>>>>> Try using TransactionTemplate.
>>>>>>
>>>>>> On Jan 31, 2013, at 4:19 PM, Chris Wolf <cw...@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 <cw...@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 <cw...@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
>>>>>>>>>>> <ma...@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 <cw...@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 <cw...@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 <wi...@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 <cw...@gmail.com> 写道：
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>> On Fri, Jan 25, 2013 at 3:55 PM, Chris Wolf <cw...@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
>>>>>>>>>>>>
>>>>>>
>>>>
>>>
Re: Does Camel JPA component implement it's own connection pooling?
Posted by Chris Wolf <cw...@gmail.com>.
Just to clarify, for me, these logging settings work:
<property name="openjpa.Log" value="openjpa.jdbc.JDBC=TRACE"/>
-= or, for SQL =-
<property name="openjpa.Log" value="SQL=TRACE"/>
<property name="openjpa.ConnectionFactoryProperties"
value="PrettyPrint=true, PrettyPrintLineLength=72"/>
<property name="openjpa.ConnectionFactoryProperties"
value="printParameters=true"/>
But the first one (JDBC logging) is too verbose - it's either all or
nothing, which is why I wanted to setup the slf4j or log4j backend to
be able to have more fine-grained settings. If you've been able to
figure that out, that would be great. I've already posted on the
openjpa users list about that, but nothing from there yet...
On Fri, Feb 1, 2013 at 10:39 AM, Chris Wolf <cw...@gmail.com> wrote:
> James,
>
> I just replied to your message from last night - I got it working by
> using your latest suggestion plus enabling DBCP connection pooling,
> although I don't know why pooling should matter.
>
> I notice that you were able to get OpenJPA logging to work - I'd sure
> be interested in seeing your persistence.xml settings for that - I
> have not been able to get any logging from OpenJPA and I tried the
> default, the log4j and slf4j backends - all to no avail...
>
> As for my final implementation, I think this latest code from you is
> the cleanest (for my purposes). Again, thanks so much for the help.
>
> -Chris
>
> On Fri, Feb 1, 2013 at 6:32 AM, James Carman
> <jc...@carmanconsulting.com> wrote:
>> Chris,
>>
>> Here's my complete class:
>>
>> public class JpaTest
>> {
>> public static void main(String[] args)
>> {
>> ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext("classpath:/META-INF/spring-beans.xml");
>> final EntityManagerFactory factory = ctx.getBean(EntityManagerFactory.class);
>> final 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);
>> for (int i = 0; i < 10; ++i)
>> {
>> MyEntity entity = new MyEntity();
>> entity.setName("name");
>> em.persist(entity);
>> }
>> }
>> });
>>
>> }
>> }
>>
>>
>> Here's the output I see when I turn up OpenJPA's logging:
>>
>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 1546366616 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 702383716 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 245507253 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 924315284 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 70275790 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 9196680 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 2097261823 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 2030360950 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 761335820 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 1063557914 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
>> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
>> 02-01@06:30:54 TRACE (JDBC) - <t 2063246672, conn 936619118> [1 ms] commit
>> 02-01@06:30:54 TRACE (JDBC) - <t 2063246672, conn 936619118> [0 ms] close
>>
>>
>> It looks like it'll use the same connection object for each insert and commit and close it at the end (I'm using DriverManagerDataSource just to make sure it won't pool).
>>
>>
>> On Jan 31, 2013, at 9:14 PM, James Carman <jc...@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 <cw...@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
>>>> <jc...@carmanconsulting.com> wrote:
>>>>> Try using TransactionTemplate.
>>>>>
>>>>> On Jan 31, 2013, at 4:19 PM, Chris Wolf <cw...@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 <cw...@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 <cw...@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
>>>>>>>>>> <ma...@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 <cw...@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 <cw...@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 <wi...@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 <cw...@gmail.com> 写道：
>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>> On Fri, Jan 25, 2013 at 3:55 PM, Chris Wolf <cw...@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
>>>>>>>>>>>
>>>>>
>>>
>>
Re: Does Camel JPA component implement it's own connection pooling?
Posted by Chris Wolf <cw...@gmail.com>.
James,
I just replied to your message from last night - I got it working by
using your latest suggestion plus enabling DBCP connection pooling,
although I don't know why pooling should matter.
I notice that you were able to get OpenJPA logging to work - I'd sure
be interested in seeing your persistence.xml settings for that - I
have not been able to get any logging from OpenJPA and I tried the
default, the log4j and slf4j backends - all to no avail...
As for my final implementation, I think this latest code from you is
the cleanest (for my purposes). Again, thanks so much for the help.
-Chris
On Fri, Feb 1, 2013 at 6:32 AM, James Carman
<jc...@carmanconsulting.com> wrote:
> Chris,
>
> Here's my complete class:
>
> public class JpaTest
> {
> public static void main(String[] args)
> {
> ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext("classpath:/META-INF/spring-beans.xml");
> final EntityManagerFactory factory = ctx.getBean(EntityManagerFactory.class);
> final 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);
> for (int i = 0; i < 10; ++i)
> {
> MyEntity entity = new MyEntity();
> entity.setName("name");
> em.persist(entity);
> }
> }
> });
>
> }
> }
>
>
> Here's the output I see when I turn up OpenJPA's logging:
>
> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 1546366616 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 702383716 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 245507253 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 924315284 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 70275790 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 9196680 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 2097261823 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 2030360950 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 761335820 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 1063557914 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
> 02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
> 02-01@06:30:54 TRACE (JDBC) - <t 2063246672, conn 936619118> [1 ms] commit
> 02-01@06:30:54 TRACE (JDBC) - <t 2063246672, conn 936619118> [0 ms] close
>
>
> It looks like it'll use the same connection object for each insert and commit and close it at the end (I'm using DriverManagerDataSource just to make sure it won't pool).
>
>
> On Jan 31, 2013, at 9:14 PM, James Carman <jc...@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 <cw...@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
>>> <jc...@carmanconsulting.com> wrote:
>>>> Try using TransactionTemplate.
>>>>
>>>> On Jan 31, 2013, at 4:19 PM, Chris Wolf <cw...@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 <cw...@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 <cw...@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
>>>>>>>>> <ma...@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 <cw...@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 <cw...@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 <wi...@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 <cw...@gmail.com> 写道：
>>>>>>>>>>>>>>
>>>>>>>>>>>>>>> On Fri, Jan 25, 2013 at 3:55 PM, Chris Wolf <cw...@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
>>>>>>>>>>
>>>>
>>
>
Re: Does Camel JPA component implement it's own connection pooling?
Posted by James Carman <jc...@carmanconsulting.com>.
Chris,
Here's my complete class:
public class JpaTest
{
public static void main(String[] args)
{
ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext("classpath:/META-INF/spring-beans.xml");
final EntityManagerFactory factory = ctx.getBean(EntityManagerFactory.class);
final 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);
for (int i = 0; i < 10; ++i)
{
MyEntity entity = new MyEntity();
entity.setName("name");
em.persist(entity);
}
}
});
}
}
Here's the output I see when I turn up OpenJPA's logging:
02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 1546366616 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 702383716 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 245507253 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 924315284 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 70275790 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 9196680 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 2097261823 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 2030360950 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 761335820 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> executing prepstmnt 1063557914 INSERT INTO MyEntity (id, name) VALUES (?, ?) [params=?, ?]
02-01@06:30:54 TRACE (SQL) - <t 2063246672, conn 936619118> [0 ms] spent
02-01@06:30:54 TRACE (JDBC) - <t 2063246672, conn 936619118> [1 ms] commit
02-01@06:30:54 TRACE (JDBC) - <t 2063246672, conn 936619118> [0 ms] close
It looks like it'll use the same connection object for each insert and commit and close it at the end (I'm using DriverManagerDataSource just to make sure it won't pool).
On Jan 31, 2013, at 9:14 PM, James Carman <jc...@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 <cw...@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
>> <jc...@carmanconsulting.com> wrote:
>>> Try using TransactionTemplate.
>>>
>>> On Jan 31, 2013, at 4:19 PM, Chris Wolf <cw...@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 <cw...@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 <cw...@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
>>>>>>>> <ma...@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 <cw...@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 <cw...@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 <wi...@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 <cw...@gmail.com> 写道：
>>>>>>>>>>>>>
>>>>>>>>>>>>>> On Fri, Jan 25, 2013 at 3:55 PM, Chris Wolf <cw...@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
>>>>>>>>>
>>>
>
Re: Does Camel JPA component implement it's own connection pooling?
Posted by Chris Wolf <cw...@gmail.com>.
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
<jc...@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 <cw...@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
>> <jc...@carmanconsulting.com> wrote:
>>> Try using TransactionTemplate.
>>>
>>> On Jan 31, 2013, at 4:19 PM, Chris Wolf <cw...@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 <cw...@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 <cw...@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
>>>>>>>> <ma...@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 <cw...@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 <cw...@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 <wi...@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 <cw...@gmail.com> 写道：
>>>>>>>>>>>>>
>>>>>>>>>>>>>> On Fri, Jan 25, 2013 at 3:55 PM, Chris Wolf <cw...@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
>>>>>>>>>
>>>
>