[news.eclipse.rt.eclipselink] Re: 2 same queries => different results?

[nocquidant@xxxxxxxxx (andiqo )]

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_REPEATABLE_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_COMMITTED);
  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_REPEATABLE_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_REPEATABLE_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" />