Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » 2 same queries => different results?
2 same queries => different results? [message #387364] Sat, 18 April 2009 10:50 Go to next message
andiqo  is currently offline andiqo Friend
Messages: 32
Registered: July 2009
Member
Hello,

I try to invoke the same query twice and I didn't get same results?!
What could be wrong (see source code and output below)?

I tried to disable Query Cache but still different results...

I am using EclipseLink 2.0.0 Milestone Build.

Thanks a lot for your help.

andiqo

//-- source code
Query query1 = em.createQuery("SELECT o FROM Node o WHERE o.name = :name");
query1.setParameter("name", "w1");
List<?> results1 = query1.getResultList();
System.out.println("** results1.size is: " + results1.size());

Query query2 = em.createQuery("SELECT o FROM Node o WHERE o.name = :name");
query2.setParameter("name", "w1");
List<?> results2 = query2.getResultList();
System.out.println("** results2.size is: " + results2.size());

//-- output
[EL Fine]: 2009-04-18
12:42:02.684--ServerSession(485367518)--Connection(148503708 9)--Thread(Thread[main,5,main])--SELECT
ID, VERSION, NODETYPE_ID FROM NODE WHERE (NAME = w1)
** results1.size is: 1
[EL Fine]: 2009-04-18
12:42:04.329--ServerSession(485367518)--Connection(20566293) --Thread(Thread[main,5,main])--SELECT
ID, VERSION, NODETYPE_ID FROM NODE WHERE (NAME = w1)
** results2.size is: 0
Re: 2 same queries => different results? [message #387368 is a reply to message #387364] Mon, 20 April 2009 14:09 Go to previous messageGo to next message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

That is very odd, I cannot see how this is possible.

Your log shows the same SQL being generated. Is another user/thread
deleting the row from the database? Try doing the same through JDBC,
perhaps something odd is occurring with your driver or database. What
database and driver are you using? Is it just this query, or do other
queries have the same effect?

---
James
http://www.nabble.com/EclipseLink---Users-f26658.html


James : Wiki : Book : Blog : Twitter
Re: 2 same queries => different results? [message #387371 is a reply to message #387364] Mon, 20 April 2009 15:04 Go to previous messageGo to next message
Gordon Yorke is currently offline Gordon YorkeFriend
Messages: 77
Registered: July 2009
Member
Is this your entire log? Do you have any postLoad events that change the
state of the Entities? What properties have you set when creating the
EntityManager?
Based on the log the Entity Manager is joining the active transaction and
using the transactional connection for the second query which results in
different data.
--Gordon
Re: 2 same queries => different results? [message #387381 is a reply to message #387371] Sat, 25 April 2009 09:04 Go to previous messageGo to next message
andiqo  is currently offline andiqo Friend
Messages: 32
Registered: July 2009
Member
Hello,

I could investigate a little more this morning. Below is my test class and
my properties. I am using PostgreSQL 8.3.7 on x86_64-pc-linux-gnu with
jdbc driver: 8.3-604.jdbc4.

In fact, I could have my test pass changing my setupClass() method as
follow:

@BeforeClass
public static void setupClass() throws Exception {
em = emf.createEntityManager();
Assert.assertTrue(em.isOpen());

Connection conn = JpaSpiActions4Test.getInstance().getConnection(em);
conn.setTransactionIsolation(Connection.TRANSACTION_REPEATAB LE_READ);
conn.setAutoCommit(false);

createSchema(em);
populate();
}
=> failure

@BeforeClass
public static void setupClass() throws Exception {
em = emf.createEntityManager();
Assert.assertTrue(em.isOpen());

Connection conn = JpaSpiActions4Test.getInstance().getConnection(em);
conn.setTransactionIsolation(Connection.TRANSACTION_READ_COM MITTED);
conn.setAutoCommit(false);

createSchema(em);
populate();
}
=> success!

@BeforeClass
public static void setupClass() throws Exception {
em = emf.createEntityManager();
Assert.assertTrue(em.isOpen());

Connection conn = JpaSpiActions4Test.getInstance().getConnection(em);
conn.setTransactionIsolation(Connection.TRANSACTION_REPEATAB LE_READ);
conn.setAutoCommit(false);

createSchema(em);

em.close();
emf.close();
emf = Persistence.createEntityManagerFactory("JenmoPU");
em = emf.createEntityManager();
Assert.assertTrue(em.isOpen());

populate();
}
=> success!

It still looks strange for me...

Thanks a lot for your help!

andiqo

// ======================================

public class TestDbEclipseLink {
protected static EntityManagerFactory emf =
Persistence.createEntityManagerFactory("JenmoPU");

private static EntityManager em;

protected static void createSchema(EntityManager em) throws
SQLException {
Connection conn = JpaSpiActions4Test.getInstance().getConnection(em);

boolean previous = conn.getAutoCommit();
conn.setAutoCommit(true);

IProcedure1<Connection> createSchema =
CreateSchemaAction.getAction();
createSchema.execute(conn);

conn.setAutoCommit(previous);
}

protected static void populateData(final IPopulator populator, boolean
closeEm)
throws DbUseCaseException {
DbUseCase dbUc = new DbUseCase() {
@Override
protected void reallyExecute(EntityManager em) throws
DbUseCaseException {
try {
// txBegin before call to reallyExecute in DbUseCase
populator.execute();
// txCommit after call to reallyExecute in DbUseCase
} catch (ProcedureException e) {
throw new DbUseCaseException(e);
}
}
};
dbUc.execute(populator.getEm(), closeEm);
}

protected static void runPopulator(IPopulator populator) throws
Exception {
if (populator != null) {
MyTimer timer = new MyTimer();
populateData(populator, false);
timer.end("End Populating data");
}
}

protected static void txBegin(EntityManager em) {
if (em.getTransaction().isActive() == true) {
em.getTransaction().commit();
}
em.getTransaction().begin();
}

protected static void txCommit(EntityManager em) {
if (em.getTransaction().isActive() == true) {
em.getTransaction().commit();
}
em.getTransaction().begin();
}

protected static void cleanUpTables(EntityManager em) {
txBegin(em);

// Should read table names from output.sql
em.createNativeQuery("delete from EDGE").executeUpdate();
em.createNativeQuery("delete from NODE").executeUpdate();
em.createNativeQuery("delete from NODEFIELD").executeUpdate();
em.createNativeQuery("delete from NODEPROPERTY").executeUpdate();
em.createNativeQuery("delete from NODEREVISION").executeUpdate();
em.createNativeQuery("delete from NODETYPE").executeUpdate();
em.createNativeQuery("delete from PROPERTY").executeUpdate();
em.createNativeQuery("delete from
PROPERTY_NODETYPE").executeUpdate();
em.createNativeQuery("delete from SPLITBLOB").executeUpdate();
em.createNativeQuery("delete from SPLITBLOBPART").executeUpdate();

txCommit(em);
}

protected static void populate() throws Exception {
runPopulator(new IPopulator() {

@Override
public EntityManager getEm() {
return em;
}

@Override
public boolean execute() throws ProcedureException {
NodeType type = NodeType.newInstance("node-type");
Node root = Node.newRoot(type, "root");
em.persist(root);
return true;
}

});
}

@BeforeClass
public static void setupClass() throws Exception {
em = emf.createEntityManager();
Assert.assertTrue(em.isOpen());

Connection conn = JpaSpiActions4Test.getInstance().getConnection(em);
conn.setTransactionIsolation(Connection.TRANSACTION_REPEATAB LE_READ);
conn.setAutoCommit(false);

createSchema(em);
populate();
}

@AfterClass
public static void teardownClass() {
if (em != null) {
txCommit(em);
em.close();
em = null;
}
}

@Before
public void setupMethod() {
txBegin(em);
}

@After
public void teardownMethod() {
txCommit(em);
}

@Test
public void testJpql() throws Exception {
Query query1 = em.createQuery("SELECT o FROM Node o WHERE o.name =
:name");
query1.setParameter("name", "root");
List<?> results1 = query1.getResultList();
int s1 = results1.size();

Query query2 = em.createQuery("SELECT o FROM Node o WHERE o.name =
:name");
query2.setParameter("name", "root");
List<?> results2 = query2.getResultList();
int s2 = results2.size();

Assert.assertEquals(s1, s2);
System.out.println("** results1.size is: " + results1.size());
System.out.println("** results2.size is: " + results2.size());
}
}

<property name="javax.persistence.jdbc.url"
value="jdbc:postgresql://localhost:5432/jenmo?compatible=7.1" />
<property name="javax.persistence.jdbc.driver"
value="org.postgresql.Driver" />
<property name="javax.persistence.jdbc.user" value="postgres" />
<property name="javax.persistence.jdbc.password" value="dbadmin" />

<property name="eclipselink.jdbc.batch-writing" value="JDBC" />
<property name="eclipselink.jdbc.batch-writing.size" value="1000" />
<property name="eclipselink.jdbc.cache-statements" value="true" />
<property name="eclipselink.jdbc.cache-statements.size"value="1000" />

<property name="eclipselink.target-database"
value="org.jenmo.core.domain.MyPostgreSQLPlatform" />
<property name="eclipselink.target-server" value="None" />

<property name="eclipselink.cache.shared.default" value="true" />

<property name="eclipselink.query-results-cache" value="true" />
<property name="eclipselink.query-results-cache.expiry" value="100000" />
<property name="eclipselink.query-results-cache.size" value="1000" />
Re: 2 same queries => different results? [message #387426 is a reply to message #387381] Tue, 28 April 2009 15:28 Go to previous message
Gordon Yorke is currently offline Gordon YorkeFriend
Messages: 77
Registered: July 2009
Member
How are you getting the connection from the Entity Manager? It seems that
the isolation level setting before populate interferes with the read
queries. Have you attempted to use straight JDBC to test this isolation
level with your database? Start transaction by setting autocommit to
false, insert data and then execute 2 queries. It is possible, although
unexpected, that the isolation level is causing the second query to not
see the transactional updates.
--Gordon
Previous Topic:How to use @BasicMap for a HashMap whose key values are entities
Next Topic:EclipseLink+Spring+ComboPooledDataSource+MySQL ORM not persisting
Goto Forum:
  


Current Time: Fri Dec 19 12:19:59 GMT 2014

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

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