Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » ReadAllQuery not working for in-memory db(ReadAllQuery not working for in-memory db)
ReadAllQuery not working for in-memory db [message #504753] Tue, 22 December 2009 13:10 Go to next message
Max WF Poon is currently offline Max WF Poon
Messages: 16
Registered: July 2009
Junior Member
I tried EclipseLink 1.2 & 2.0 with in-memory DB such as HSQLDB 1.8.1 & Derby 10.5.3.0 and found that

1. The in-memory DB contents are only retained and visible during the entityManager session, i.e. between
- em = emf.createEntityManager( )
and
- em.close( )
instead of across multiple em session in an application.

Question: Is this expected behavior? Why?

2 After in-memory db population :

- ReadObjectQuery - e.g. from em.find(Manufacturer.class, <primaryKey>) - is working fine

showing the in-memory db table (e.g. for Manufacturer) was really populated, but

- ReadAllQuery - e.g. from em.createQuery("select object(o) from Manufacturer as o")).getResultList() - always returns null

The following are within the same EntityManager session (no em.close( ) in-between) :-
....
FINEST: PERSIST operation called on: sample.entities.Manufacturer[manufacturerId=19987296].
FINEST: Execute query ReadObjectQuery(referenceClass=Manufacturer sql="SELECT MANUFACTURER_ID, ADDRESSLINE2, ZIP, PHONE, ADDRESSLINE1, FAX, EMAIL, NAME, STATE, CITY, REP FROM MANUFACTURER WHERE (MANUFACTURER_ID = ?)")
INFO: em.find( ) found Manufacturer[19987296]=SimpleJpaToplinkApp.entities.Manufact urer[manufacturerId=19987296]
...
FINEST: Execute query ReadAllQuery(referenceClass=Manufacturer sql="SELECT MANUFACTURER_ID, ADDRESSLINE2, ZIP, PHONE, ADDRESSLINE1, FAX, EMAIL, NAME, STATE, CITY, REP FROM MANUFACTURER
FINEST: reconnecting to external connection pool
FINE: SELECT MANUFACTURER_ID, ADDRESSLINE2, ZIP, PHONE, ADDRESSLINE1, FAX, EMAIL, NAME, STATE, CITY, REP FROM MANUFACTURER
INFO: showAllManufacturers( ): manufacturers=null

This seems to be related to:

org.eclipse.persistence.internal.jpa.EJBQueryImpl.java

protected Object executeReadQuery() {
List parameterValues = processParameters();
// TODO: the following performFlush() call is a temporary workaround for
// bug 4752493:
// CTS: INMEMORY QUERYING IN EJBQUERY BROKEN DUE TO CHANGE TO USE
// REPORTQUERY.

Above not working even if I do
em.setFlushMode(FlushMode.COMMIT)
to avoid flushing, before the ReadAllQuery (2nd one above).

Question: Any way / roadmap EclipseLink JPA Query will work with in-memory ?

Thanks.
icon9.gif  Re: ReadAllQuery not working for in-memory db [message #505799 is a reply to message #504753] Tue, 05 January 2010 00:37 Go to previous messageGo to next message
Max WF Poon is currently offline Max WF Poon
Messages: 16
Registered: July 2009
Junior Member
Any advice/input from anybody?
Re: ReadAllQuery not working for in-memory db [message #505918 is a reply to message #504753] Tue, 05 January 2010 10:01 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

By default EclipseLink uses a connection pool (pool of several JDBC connections). If with your in-memory option each connection is maintaining its own concept of the data, then the connection will not see each others data.

You could try to limit the pool to a single connection. Or you could use a file database so their can be multiple connections.

How are you configuring your connection pool?

Try,
"eclipselink.jdbc.connections.min"="1"
"eclipselink.jdbc.connections.max"="1"



James : Wiki : Book : Blog : Twitter
icon9.gif  Re: ReadAllQuery not working for in-memory db [message #506013 is a reply to message #505918] Tue, 05 January 2010 18:49 Go to previous messageGo to next message
Max WF Poon is currently offline Max WF Poon
Messages: 16
Registered: July 2009
Junior Member
Sorry. No. I added to persistence.xml the following but it does not help.
<property name="eclipselink.jdbc.read-connections.min" value="1"/>
<property name="eclipselink.jdbc.read-connections.max" value="1"/>
<property name="eclipselink.jdbc.write-connections.min" value="1"/>
<property name="eclipselink.jdbc.write-connections.max" value="1"/>

Actually, I found that em.persist( ) seems to create the related schema on the in-memory DB only, after which I need to insert the rows into in-memory DB via JDBC :
- executeUpdate(<insert statement>)
then I can retrieve the rows either via
- em.find(...), or JPA-QL i.e.
- em.createQuery(queryStr).getResultList( )

Tracing included as following FYI :

Case I : em.persist( ) followed by em.createQuery(queryStr).getResultList( ) in showAllManufacturers( ) gives the following trace :-

FINEST: PERSIST operation called on: persistenceApp.entities.Manufacturer@130f50e.
.........
FINEST: PERSIST operation called on: persistenceApp.entities.Manufacturer@130fb60.
FINEST: Execute query ReadAllQuery(referenceClass=Manufacturer sql="SELECT MANUFACTURER_ID, ADDRESSLINE2, ZIP, PHONE, ADDRESSLINE1, FAX, EMAIL, NAME, STATE, CITY, REP FROM MANUFACTURER")
FINEST: reconnecting to external connection pool
FINE: SELECT MANUFACTURER_ID, ADDRESSLINE2, ZIP, PHONE, ADDRESSLINE1, FAX, EMAIL, NAME, STATE, CITY, REP FROM MANUFACTURER
FINE: showAllManufacturers(): manufacturers=null

Case II : em.persist( ) followed by JDBC executeUpdate("INSERT IGNORE INTO ...."), followed by em.createQuery(queryStr).getResultList( ) in showAllManufacturers( ) gives the following trace :-

FINEST: PERSIST operation called on: persistenceApp.entities.Manufacturer@130f50e.
...........
FINEST: PERSIST operation called on: persistenceApp.entities.Manufacturer@130fb60.
FINEST: Execute query ReadAllQuery(referenceClass=Manufacturer sql="SELECT MANUFACTURER_ID, ADDRESSLINE2, ZIP, PHONE, ADDRESSLINE1, FAX, EMAIL, NAME, STATE, CITY, REP FROM MANUFACTURER")
FINEST: reconnecting to external connection pool
FINE: SELECT MANUFACTURER_ID, ADDRESSLINE2, ZIP, PHONE, ADDRESSLINE1, FAX, EMAIL, NAME, STATE, CITY, REP FROM MANUFACTURER
FINEST: Register the existing object persistenceApp.entities.Manufacturer@130f50e
...............
FINEST: Register the existing object persistenceApp.entities.Manufacturer@130fb60
FINE: showAllManufacturers(): manufacturers=[persistenceApp.entities.Manufacturer@130f50e,...., persistenceApp.entities.Manufacturer@130fb60]

Again (tested using JavaEE container JDBC connection pool) while JPA-QL seems to work only after explicitly JDBC INSERTs, I can use em.find(....) to retrieve the entity objects in both cases. In summary,

em.persist(...) => can get entities by em.find(...) and within same em session (before em.close( )).

JDBC stmt.executeUpdate("INSERT....") => can get entities by
- em.createQuery(...).getResultList( ), or
- stmt.executeQuery(queryStr)
and across multiple em session, i.e. after em.close( ) and em = emf.createEntityManager( ) again.

[Updated on: Wed, 06 January 2010 04:12]

Report message to a moderator

Re: ReadAllQuery not working for in-memory db [message #506507 is a reply to message #504753] Thu, 07 January 2010 18:40 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member


Try,
"eclipselink.jdbc.connections.min"="1"
"eclipselink.jdbc.connections.max"="1"

don't use,
<property name="eclipselink.jdbc.read-connections.min" value="1"/>
<property name="eclipselink.jdbc.read-connections.max" value="1"/>
<property name="eclipselink.jdbc.write-connections.min" value="1"/>
<property name="eclipselink.jdbc.write-connections.max" value="1"/>

this will configure 2 connections, you need to use 1.

(you need to be on at least EclipseLink 1.1)


James : Wiki : Book : Blog : Twitter
icon9.gif  Re: ReadAllQuery not working for in-memory db [message #506509 is a reply to message #506507] Thu, 07 January 2010 13:51 Go to previous messageGo to next message
Max WF Poon is currently offline Max WF Poon
Messages: 16
Registered: July 2009
Junior Member
Thx for your response. Just tried your suggestion, but same results as before...

Btw, it seems that I can only find documentation of
- <property name="eclipselink.jdbc.read-connections.min" value="1"/>
- <property name="eclipselink.jdbc.read-connections.max" value="1"/>
- <property name="eclipselink.jdbc.write-connections.min" value="1"/>
- <property name="eclipselink.jdbc.write-connections.max" value="1"/>
on http://wiki.eclipse.org/Using_EclipseLink_JPA_Extensions_(ELUG)

Where can I find those for
- <property name="eclipselink.jdbc.connections.min" value="1"/>
- <property name="eclipselink.jdbc.connections.max" value="1"/>
?

[Updated on: Fri, 08 January 2010 00:37]

Report message to a moderator

Re: ReadAllQuery not working for in-memory db [message #506967 is a reply to message #504753] Mon, 11 January 2010 14:58 Go to previous messageGo to next message
James Sutherland is currently offline James Sutherland
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

Yes, the docs are out of date, we are working on new 2.0 docs.

See,
http://www.eclipse.org/eclipselink/api/1.2/org/eclipse/persi stence/config/PersistenceUnitProperties.html#JDBC_CONNECTION S_MIN

Please include your persistence.xml, and an EclipseLink SQL log of what you are doing on finest.

You seem to say you are using a JavaEE DataSource? Then the EclipseLink connection pool settings are not relevant as you are using your server's DataSource. You need to configure you connection pool in your server.


James : Wiki : Book : Blog : Twitter
icon5.gif  Re: ReadAllQuery not working for in-memory db [message #507083 is a reply to message #506967] Tue, 12 January 2010 02:04 Go to previous messageGo to next message
Max WF Poon is currently offline Max WF Poon
Messages: 16
Registered: July 2009
Junior Member
Thanks. I did separate testing and found the following:

I added in
- inMemoryDbEntityMgr.flush( )
after each
- inMemoryDbEntityMgr.persist( entityInstance )
which was looped to persist all relevant entity instances, and then enclosed all the above between
- userTransaction.begin( ) and userTransaction.commit( )

Now persist( ) is working and I can retrieve rows persisted in in-Memory DB via JDBC and JPQL as expected.

Not including inMemoryDbEntityMgr.flush( ) resulted in the following when doing userTransaction.commit( ) after looping of all the persist( ) (I'm using Glassfish v3 and container JDBC DataSource which is referred by both EclipseLink and JDBC) :-

javax.enterprise.resource.resourceadapter.com.sun.enterprise .resource.rm..:Unexpected exception in resource pooling

java.lang.IllegalStateException: Local transaction already has 1 non-XA Resource: cannot add more resources.

Any advice?

Persistence.xml is as follows:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="1.0" 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_1_0.xsd">
<persistence-unit name="persistenceAppPU" transaction-type="JTA">
<provider>org.eclipse.persistence.jpa.PersistenceProvider</provider >
<jta-data-source>jdbc/sample</jta-data-source>
<class>persistenceApp.entities.Manufacturer</class>
<class>persistenceApp.entities.ProductCode</class>
<class>persistenceApp.entities.Product</class>
<exclude-unlisted-classes>true</exclude-unlisted-classes>
<properties>
<property name="eclipselink.cache.type.Manufacturer" value="Full"/>
<property name="eclipselink.cache.type.ProductCode" value="Full"/>
<property name="eclipselink.cache.type.Product" value="Full"/>
<property name="eclipselink.logging.level" value="FINE"/>
</properties>
</persistence-unit>

<persistence-unit name="memoryDB_AppPU" transaction-type="JTA">
<provider>org.eclipse.persistence.jpa.PersistenceProvider</provider >
<jta-data-source>jdbc/derbyInMemory</jta-data-source>
<class>persistenceApp.entities.Manufacturer</class>
<class>persistenceApp.entities.Product</class>
<class>persistenceApp.entities.ProductCode</class>
<exclude-unlisted-classes>false</exclude-unlisted-classes>
<properties>
<property name="javax.persistence.jdbc.user" value="app"/>
<property name="javax.persistence.jdbc.password" value="app"/>
<property name="eclipselink.jdbc.connections.min" value="1"/>
<property name="eclipselink.jdbc.connections.max" value="1"/>
<property name="eclipselink.target-database" value="Derby"/>
<property name="eclipselink.target-server" value="SunAS9"/>
<!-- Logging -->
<property name="eclipselink.logging.level" value="FINEST"/>
<property name="eclipselink.logging.session" value="false"/>
<property name="eclipselink.logging.thread" value="false"/>
<property name="eclipselink.logging.timestamp" value="false"/>
<!-- DDL Generation -->
<property name="eclipselink.ddl-generation" value="drop-and-create-tables"/>
<property name="eclipselink.ddl-generation.output-mode" value="both"/>
<property name="eclipselink.application-location" value="C:/temp/"/>
<property name="eclipselink.create-ddl-jdbc-file-name" value="create.sql"/>
<property name="eclipselink.drop-ddl-jdbc-file-name" value="drop.sql"/>
</properties>
</persistence-unit>
</persistence>
Re: ReadAllQuery not working for in-memory db [message #507508 is a reply to message #507083] Wed, 13 January 2010 17:49 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1034
Registered: July 2009
Senior Member
Hello,

EntityManager are transactionally issolated, which is why you won't see changes in different EntityManagers unless you commit the transaction. Because you are using JTA, the transactions are controled by the container and will need to be started/stoped by the container. You will need to see the docs for the server to determine how this should work with your application. You could use a resource local context instead of JTA and then begin/commit transactions yourself, but you will need to be sure to use a non-JTA datasource.

EclipseLink has a demo app and configuration for different servers available that you can use as a reference at:
http://wiki.eclipse.org/EclipseLink/Examples/JPA

Best Regards,
Chris
icon5.gif  Re: ReadAllQuery not working for in-memory db [message #507602 is a reply to message #507508] Thu, 14 January 2010 03:08 Go to previous message
Max WF Poon is currently offline Max WF Poon
Messages: 16
Registered: July 2009
Junior Member
Thanks. Btw, I found that the database column order is not preserved on the after persist( ) to inMemoryDB this way. How can I enforce the db column order according to the Entity class definition?
Previous Topic:2.0.0: application hangs in acquire connection
Next Topic:Cascade.Persist problems
Goto Forum:
  


Current Time: Thu Oct 23 10:20:23 GMT 2014

Powered by FUDForum. Page generated in 0.01848 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software