| 2 same queries => different results? [message #387364] |
Sat, 18 April 2009 06:50  |
Eclipse User |
|
|
|
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 #387381 is a reply to message #387371] |
Sat, 25 April 2009 05:04  |
| Eclipse User |
|
|
|
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 11:28 |
| Eclipse User |
|
|
|
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
|
|
|
Powered by
FUDForum. Page generated in 0.03686 seconds
Home