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