/*
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License.  You may obtain a copy of the License at
 *
 * http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing,
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied.  See the License for the
 * specific language governing permissions and limitations
 * under the License.
 */
package org.apache.openjpa.persistence.jdbc.sqlcache;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Collections;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.Iterator;
import java.util.List;
import java.util.Properties;

import jakarta.persistence.EntityManager;
import jakarta.persistence.LockModeType;
import jakarta.persistence.Query;
import jakarta.persistence.TypedQuery;

import org.apache.openjpa.kernel.PreparedQuery;
import org.apache.openjpa.kernel.PreparedQueryCache;
import org.apache.openjpa.kernel.QueryHints;
import org.apache.openjpa.kernel.QueryLanguages;
import org.apache.openjpa.kernel.QueryStatistics;
import org.apache.openjpa.kernel.jpql.JPQLParser;
import org.apache.openjpa.lib.jdbc.AbstractJDBCListener;
import org.apache.openjpa.lib.jdbc.JDBCEvent;
import org.apache.openjpa.lib.jdbc.JDBCListener;
import org.apache.openjpa.persistence.ArgumentException;
import org.apache.openjpa.persistence.OpenJPAEntityManager;
import org.apache.openjpa.persistence.OpenJPAEntityManagerFactorySPI;
import org.apache.openjpa.persistence.OpenJPAEntityManagerSPI;
import org.apache.openjpa.persistence.OpenJPAPersistence;
import org.apache.openjpa.persistence.OpenJPAQuery;
import org.apache.openjpa.persistence.jdbc.sqlcache.Employee.Category;
import org.apache.openjpa.persistence.test.AbstractPersistenceTestCase;

/**
 * Tests correctness and performance of queries with and without Prepared Query Cache.
 *
 * This test uses a single EntityManagerFactory initialized with fixed set of entity classes
 * and appropriate configuration parameters for Prepared Query Cache.
 *
 * The entity classes are specified in persistence unit <code>"PreparedQuery"</code> in a
 * <code>META-INF/persistence.xml</code> that must be available to the classpath.
 *
 * Configures the EntityManagerFactory with properties that are relevant to testing PreparedQuery caches.
 * Switches off runtime enhancement to avoid unintended consequences.
 * Provides facilities to run a JPQL query with and without cache and compute the performance delta.
 *
 * Uses a hard-coded data set initialized once for the entire suite.
 *
 * @author Pinaki Poddar
 *
 */
public class TestPreparedQueryCache extends AbstractPersistenceTestCase {

    private static String RESOURCE = "META-INF/persistence.xml";
    private static String UNIT_NAME = "PreparedQuery";

    protected static final int SAMPLE_SIZE = 100;  // no. of observations for performance statistics
    public static final boolean USE_CACHE  = true; // mnemonic for using cache
    private static Object[] NO_PARAMS      = null; // mnemonic for no query parameters
    public static final boolean IS_NAMED_QUERY  = true; // mnemonic for named query

    private static boolean FAIL_IF_PERF_DEGRADE = false;


    private static Company IBM;
    public static final String[] COMPANY_NAMES    = {"IBM", "BEA", "acme.org" };
    public static final int[]    START_YEARS      = {1900, 2000, 2010 };
	public static final String[] DEPARTMENT_NAMES = {"Marketing", "Sales", "Engineering" };
    public static final String[] EMPLOYEE_NAMES   = {"Tom", "Dick", "Harray" };
    public static final Date[] EMPLOYEE_START_DATES = {new GregorianCalendar(2017, Calendar.FEBRUARY, 12).getTime(),
    		new GregorianCalendar(2014, Calendar.JANUARY, 1).getTime(),
    		new GregorianCalendar(2014, Calendar.JANUARY, 1).getTime()};
    public static final Date[] EMPLOYEE_END_DATES = {new GregorianCalendar(2999, Calendar.JANUARY, 1).getTime(),
    		new GregorianCalendar(2999, Calendar.JANUARY, 1).getTime(),
    		new GregorianCalendar(2016, Calendar.DECEMBER, 31).getTime()};
	public static final String[] CITY_NAMES       = {"Tulsa", "Durban", "Harlem"};

    public static final String EXCLUDED_QUERY_1 = "select count(p) from Company p";
    public static final String EXCLUDED_QUERY_2 = "select count(p) from Department p";
	public static final String INCLUDED_QUERY   = "select p from Address p";

    public static final long[] BOOK_IDS     = {1000, 2000, 3000};
    public static final String[] BOOK_NAMES = {"Argumentative Indian", "Tin Drum", "Blink"};
    public static final long[] CD_IDS       = {1001, 2001, 3001};
    public static final String[] CD_LABELS  =  {"Beatles", "Sinatra", "Don't Rock My Boat"};

    protected static OpenJPAEntityManagerFactorySPI emf;
    protected static SQLAuditor auditor;
    protected static int TEST_COUNT = 0;
    private OpenJPAEntityManagerSPI em;

	/**
	 * Sets up the test suite with a statically initialized EntityManagerFactory.
	 * Creates data once for all other tests to use.
	 */
    @Override
	public void setUp() throws Exception {
        super.setUp();
        if (emf == null) {
            Properties config = new Properties();
            config.put("openjpa.jdbc.SynchronizeMappings", "buildSchema(ForeignKeys=true,SchemaAction='drop,add')");
            config.put("openjpa.jdbc.JDBCListeners", new JDBCListener[] { auditor = new SQLAuditor()});
            config.put("openjpa.jdbc.QuerySQLCache", "true(EnableStatistics=true)");
            config.put("openjpa.RuntimeUnenhancedClasses", "unsupported");
            config.put("openjpa.DynamicEnhancementAgent", "false");
            emf = (OpenJPAEntityManagerFactorySPI)OpenJPAPersistence.createEntityManagerFactory(
                    UNIT_NAME, RESOURCE, config);
            em = emf.createEntityManager();
            createTestData();
        } else {
    		em = emf.createEntityManager();
            getPreparedQueryCache().clear();
        }
        TEST_COUNT++;
	}

	/**
	 * Create data for the entire test suite to use.
	 */
	void createTestData() {
	    em.getTransaction().begin();
	    for (int i = 0; i < COMPANY_NAMES.length; i++) {
	        Company company = new Company();
	        if (i == 0)
	            IBM = company;
	        company.setName(COMPANY_NAMES[i]);
	        company.setStartYear(START_YEARS[i]);
	        em.persist(company);
            for (String departmentName : DEPARTMENT_NAMES) {
                Department dept = new Department();
                dept.setName(departmentName);
                company.addDepartment(dept);
                em.persist(dept);
                for (int k = 0; k < EMPLOYEE_NAMES.length; k++) {
                    Employee emp = new Employee();
                    emp.setName(EMPLOYEE_NAMES[k]);
                    emp.setStartDate(EMPLOYEE_START_DATES[k]);
                    emp.setEndDate(EMPLOYEE_END_DATES[k]);

                    Address addr = new Address();
                    addr.setCity(CITY_NAMES[k]);
                    em.persist(emp);
                    em.persist(addr);
                    emp.setAddress(addr);
                    dept.addEmployees(emp);
                }
            }
	    }
        Person p1 = new Person("John", "Doe", (short)45, 1964);
        Person p2 = new Person("John", "Doe", (short)42, 1967);
        Person p3 = new Person("Harry", "Doe", (short)12, 1995);
        Person p4 = new Person("Barry", "Doe", (short)22, 1985);
        em.persist(p1);
        em.persist(p2);
        em.persist(p3);
        em.persist(p4);

        Author a1 = new Author("Author1", "", (short)40, 1960);
        Author a2 = new Author("Author2", "", (short)41, 1961);
        Author a3 = new Author("Author3", "", (short)42, 1962);
        Singer s1 = new Singer("Singer1", "", (short)21, 1991);
        Singer s2 = new Singer("Singer2", "", (short)22, 1992);

        long id = 100;
        Book   b1 = new Book("Book1");
        Book   b2 = new Book("Book2");
        CD     c1 = new CD("CD1");
        CD     c2 = new CD("CD2");

        b1.setId(id++); b1.setTitle("title-1"); b1.setToken("LARGE");
        b2.setId(id++); b2.setTitle("title-2"); b2.setToken("MEDIUM");
        c1.setId(id++);
        c2.setId(id++);
        b1.addAuthor(a1);
        b1.addAuthor(a2);
        b2.addAuthor(a2);
        b2.addAuthor(a3);
        c1.setSinger(s1);
        c2.setSinger(s2);

        em.persist(a1); em.persist(a2);   em.persist(a3);
        em.persist(s1); em.persist(s2);
        em.persist(b1); em.persist(b2);
        em.persist(c1); em.persist(c2);

        id = (int)System.currentTimeMillis();
        OrderJPA o1 = new OrderJPA();
        o1.setOrderId(id++);
        o1.setCustomerId(339);
        o1.setDistrictId(3);
        o1.setWarehouseId(23);

        OrderJPA o2 = new OrderJPA();
        o2.setOrderId(id++);
        o2.setCustomerId(2967);
        o2.setDistrictId(5);
        o2.setWarehouseId(22);

        em.persist(o1);
        em.persist(o2);

        for (int i = 1; i < 10; i++) {
            Parent parent = new Parent();
            parent.setId(i);
            parent.setName(new String("Parent "+i));
            Address addr = new Address();
            addr.setCity("Address "+i+i);
            parent.setAddrId(addr);
            em.persist(addr);
            for (int j = 1; j < 5; j++) {
                Child child = new Child();
                child.setName("Child "+i+j);
                child.setParent(parent);
                parent.add(child);
            }
            em.persist(parent);
        }

        em.getTransaction().commit();
	}

	@Override
	public void tearDown() throws Exception {
	    closeEM(em);
	    em = null;
	    if (TEST_COUNT >= 50) {
	        auditor.clear();
	        auditor = null;
	        closeEMF(emf);
	        emf = null;
	    }
		super.tearDown();
	}

    public void testCollectionValuedParameterOfEntities() {
        OpenJPAEntityManager em = emf.createEntityManager();
        String jpql1 = "select d from Department d where d.name in ('Marketing', 'Sales') order by d.name";
        String jpql2 = "select d from Department d where d.name in ('Engineering', 'Marketing') order by d.name";

        List<Department> param1 = (List<Department>) em.createQuery(jpql1).getResultList();
        List<Department> param2 = (List<Department>) em.createQuery(jpql2).getResultList();
        em.clear();

        String jpql = "select e from Employee e where e.department in :param";

        List<Employee> rs1 = em.createQuery(jpql).setParameter("param", param1).getResultList();

        for (Employee employee2 : rs1) {
            Employee e = (Employee) employee2;
            assertFalse(e.getDepartment().getName().equals("Engineering"));
        }

        List<Employee> rs2 = (List<Employee>) em.createQuery(jpql).setParameter("param", param2).getResultList();
        for (Employee employee1 : rs2) {
            Employee e = (Employee) employee1;
            assertFalse(e.getDepartment().getName().equals("Sales"));
        }

        em.clear();
        String jpql3 = "select e from Employee e where e.department in (:p1, :p2, :p3)";
        Query query = em.createQuery(jpql3);
        query.setParameter("p1", param1.get(0));
        query.setParameter("p2", param1.get(1));
        query.setParameter("p3", param1.get(2));
        List<Employee> rs3 = query.getResultList();
        for (Employee element : rs3) {
            Employee e = (Employee) element;
            assertTrue(e.getDepartment().getName().equals("Marketing"));
        }

        em.clear();
        query = em.createQuery(jpql3);
        query.setParameter("p1", param2.get(0));
        query.setParameter("p2", param2.get(1));
        query.setParameter("p3", param2.get(2));
        List<Employee> rs4 = query.getResultList();
        for (Employee employee : rs4) {
            Employee e = (Employee) employee;
            assertTrue(e.getDepartment().getName().equals("Engineering"));
        }

        em.clear();
        String jpql4 = "select p from Parent p where p.id < 3";
        String jpql5 = "select p from Parent p where p.id > 4";
        List<Parent> parm1 = em.createQuery(jpql4).getResultList();
        List<Parent> parm2 = em.createQuery(jpql5).getResultList();

        assertTrue("Size of two result list " + parm1.size() + " and " + parm2.size() +
            " must not be same", parm1.size() != parm2.size());

        em.clear();
        String jpql6 = "select c from Child c where c.parent in ?1";
        Query qry = em.createQuery(jpql6);
        qry.setParameter(1, parm1);
        List<Child> c1 = qry.getResultList();
        for (Child item : c1) {
            Child child = (Child) item;
            assertTrue(child.getParent().getId() < 3);
        }

        em.clear();
        qry = em.createQuery(jpql6);
        qry.setParameter(1, parm2);
        List<Child> c2 = qry.getResultList();
        for (Child value : c2) {
            Child child = (Child) value;
            assertTrue(child.getParent().getId() > 4);
        }

    }

    public void testCollectionValuedParameterOfEntitiesWithEmptyList() {
        OpenJPAEntityManager em = emf.createEntityManager();
        String jpql1 =
            "select d from Department d where d.name in ('Marketing', 'Sales') order by d.name";
        List<Department> param1 =
            (List<Department>) em.createQuery(jpql1).getResultList();
        em.clear();

        String jpql = "select e from Employee e where e.department in :param";

        List<Employee> rs1 =
            em.createQuery(jpql).setParameter("param", param1).getResultList();

        for (Employee employee : rs1) {
            Employee e = (Employee) employee;
            assertFalse(e.getDepartment().getName().equals("Engineering"));
        }

        // Prior to OPENJPA-2118, the following query would yeild a
        // 'ArithmeticException: divide
        // by zero' exception (see JIRA for details).
        try {
            // Pass an empty list to 'param'.
            em.createQuery(jpql).setParameter("param",
                new ArrayList<Department>()).getResultList();
        } catch (ArgumentException ae) {
            assertEquals(ae.getCause().getMessage(),
                "Input parameter \"param\" is empty.");
        }
    }

    public void testRepeatedParameterInSubqueryInDifferentOrderSubQLast() {
        OpenJPAEntityManager em = emf.createEntityManager();

        String jpql = "SELECT o from OrderJPA o " +
                "WHERE (o.CustomerId = :customerId) " +
                "AND (o.WarehouseId = :warehouseId) " +
                "AND (o.DistrictId = :districtId) " +
                "AND o.OrderId IN (SELECT MAX (o1.OrderId) from OrderJPA o1 " +
                    "WHERE ((o1.CustomerId = :customerId) " +
                    "AND    (o1.DistrictId = :districtId) " +
                    "AND    (o1.WarehouseId = :warehouseId)))";

        em.getTransaction().begin();
        TypedQuery<OrderJPA> q1 = em.createQuery(jpql, OrderJPA.class);
        q1.setParameter("customerId", 339)
          .setParameter("districtId", 3)
          .setParameter("warehouseId", 23);

        assertEquals(JPQLParser.LANG_JPQL, OpenJPAPersistence.cast(q1).getLanguage());
        assertFalse(q1.getResultList().isEmpty());

        TypedQuery<OrderJPA> q2 = em.createQuery(jpql, OrderJPA.class);
        assertEquals(QueryLanguages.LANG_PREPARED_SQL, OpenJPAPersistence.cast(q2).getLanguage());
        q2.setParameter("customerId", 2967)
          .setParameter("districtId", 5)
          .setParameter("warehouseId", 22);

        assertFalse(q2.getResultList().isEmpty());
        em.getTransaction().rollback();

    }

	public void testPreparedQueryCacheIsActiveByDefault() {
		assertNotNull(getPreparedQueryCache());
	}

	public void testPreparedQueryCacheIsPerUnitSingleton() {
		PreparedQueryCache c1 = getPreparedQueryCache();
		PreparedQueryCache c2 = getPreparedQueryCache();
		assertSame(c1, c2);
	}

	public void testPreparedQueryIdentifierIsOriginalJPQLQuery() {
        String jpql = "select p from Company p";
        OpenJPAQuery<?> q1 = em.createQuery(jpql);
        q1.getResultList();
        PreparedQuery pq = getPreparedQueryCache().get(jpql);
        assertNotNull(pq);
        assertEquals(jpql, pq.getIdentifier());
        assertEquals(jpql, pq.getOriginalQuery());
	}

	public void testOriginalJPQLQueryStringIsSetOnPreparedQuery() {
        String jpql = "select p from Company p";
        OpenJPAQuery<?> q1 = em.createQuery(jpql);
        q1.getResultList();
        PreparedQuery pq = getPreparedQueryCache().get(jpql);
        assertNotNull(pq);
        OpenJPAQuery<?> q2 = em.createQuery(jpql);
        assertEquals(jpql,q2.getQueryString());
	}

	public void testOrderByElementsAbsentInProjection() {
	    String jpql = "select c.name from Company c ORDER BY c.startYear";
        OpenJPAQuery<?> q1 = em.createQuery(jpql);
        List l1 = q1.getResultList();

        PreparedQuery pq = getPreparedQueryCache().get(jpql);
        assertNotNull(pq);
        OpenJPAQuery<?> q2 = em.createQuery(jpql);
        List l2 = q2.getResultList();

        assertEquals(l1.size(), l2.size());
        assertEquals(l1.toString(), l2.toString());
	}

	public void testExclusionPattern() {
		OpenJPAQuery<?> q1 = em.createQuery(EXCLUDED_QUERY_1);
		q1.getResultList();
		assertNotCached(EXCLUDED_QUERY_1);

		OpenJPAQuery<?> q2 = em.createQuery(EXCLUDED_QUERY_2);
		q2.getResultList();
		assertNotCached(EXCLUDED_QUERY_2);

		OpenJPAQuery<?> q3 = em.createQuery(INCLUDED_QUERY);
		q3.getResultList();
		assertCached(INCLUDED_QUERY);
	}

	void assertLanguage(OpenJPAQuery<?> q, String lang) {
		assertEquals(lang, q.getLanguage());
	}

	void assertCached(String id) {
		PreparedQuery cached = getPreparedQueryCache().get(id);
		assertNotNull(getPreparedQueryCache() + ": " + getPreparedQueryCache().getMapView() +
				" does not contain " + id, cached);
	}

	void assertNotCached(String id) {
	    PreparedQueryCache cache = getPreparedQueryCache();
		if (cache != null) {
			assertNull(cache.get(id));
		}
	}

	public void testPreparedQueryIsCachedOnExecution() {
		String jpql = "select p from Company p";
		OpenJPAQuery<?> q1 = em.createQuery(jpql);
		assertNotCached(jpql);
		assertLanguage(q1, JPQLParser.LANG_JPQL);

		q1.getResultList();
		assertCached(jpql);
		assertLanguage(q1, JPQLParser.LANG_JPQL);

		PreparedQuery cached = getPreparedQueryCache().get(jpql);
		assertEquals(jpql, cached.getIdentifier());
		assertFalse(jpql.equalsIgnoreCase(cached.getTargetQuery()));
	}

	public void testPreparedQueryIsCachedAcrossExecution() {
		String jpql = "select p from Company p";
		OpenJPAQuery<?> q1 = em.createQuery(jpql);
		assertNotCached(jpql);
		assertLanguage(q1, JPQLParser.LANG_JPQL);


		q1.getResultList();
		assertCached(jpql);
		assertLanguage(q1, JPQLParser.LANG_JPQL);

		// Create a new query with the same JPQL
		// This is not only cached, its language is different too
		OpenJPAQuery<?> q2 = em.createQuery(jpql);
		assertCached(jpql);
		assertLanguage(q2, QueryLanguages.LANG_PREPARED_SQL);
	}

	public void testInvalidatePreparedQueryWithHint() {
		String jpql = "select p from Company p";
		OpenJPAQuery<?> q1 = em.createQuery(jpql);
		assertNotCached(jpql);

		q1.getResultList();
		assertCached(jpql);
		assertLanguage(q1, JPQLParser.LANG_JPQL);

		// Create a new query with the same JPQL
		// This is cached on creation, its language is Prepared SQL
		OpenJPAQuery<?> q2 = em.createQuery(jpql);
		assertCached(jpql);
		assertLanguage(q2, QueryLanguages.LANG_PREPARED_SQL);
		q2.getResultList();

		// Now execute with hints to invalidate.
		q2.setHint(QueryHints.HINT_INVALIDATE_PREPARED_QUERY, true);
		// Immediately it should be removed from the cache
		assertNotCached(jpql);
		assertEquals(JPQLParser.LANG_JPQL, q2.getLanguage());
		q2.getResultList();

		// Create a new query with the same JPQL
		// This is not cached on creation, its language is JPQL
		OpenJPAQuery<?> q3 = em.createQuery(jpql);
		assertNotCached(jpql);
		assertLanguage(q3, JPQLParser.LANG_JPQL);
	}

	public void testIgnorePreparedQueryWithHint() {
		String jpql = "select p from Company p";
		OpenJPAQuery<?> q1 = em.createQuery(jpql);
		assertNotCached(jpql);

		q1.getResultList();
		assertCached(jpql);
		assertLanguage(q1, JPQLParser.LANG_JPQL);

		// Create a new query with the same JPQL
		// This is cached on creation, its language is PREPARED SQL
		OpenJPAQuery<?> q2 = em.createQuery(jpql);
		assertCached(jpql);
		assertLanguage(q2, QueryLanguages.LANG_PREPARED_SQL);
		q2.getResultList();

		// Now execute with hints to ignore.
		q2.setHint(QueryHints.HINT_IGNORE_PREPARED_QUERY, true);
		// It should remain in the cache
		assertCached(jpql);
		// But its language should be JPQL and not PREPARED SQL
		assertEquals(JPQLParser.LANG_JPQL, q2.getLanguage());
		q2.getResultList();

		// Create a new query with the same JPQL
		// This is cached on creation, its language is PREPARED SQL
		OpenJPAQuery<?> q3 = em.createQuery(jpql);
		assertCached(jpql);
		assertLanguage(q3, QueryLanguages.LANG_PREPARED_SQL);
	}

	public void testQueryStatistics() {
        QueryStatistics<String> stats = getPreparedQueryCache().getStatistics();
        stats.reset();

        String jpql1 = "select c from Company c";
        String jpql2 = "select c from Company c where c.name = 'PObject'";
		int N1 = 5;
		int N2 = 8;
		for (int i = 0; i < N1; i++) {
	        OpenJPAQuery<?> q1 = em.createQuery(jpql1);
			q1.getResultList();
		}
		for (int i = 0; i < N2; i++) {
	        OpenJPAQuery<?> q2 = em.createQuery(jpql2);
			q2.getResultList();
		}

		assertEquals(N1,      stats.getExecutionCount(jpql1));
		assertEquals(N2,      stats.getExecutionCount(jpql2));
		assertEquals(N1+N2,   stats.getExecutionCount());
		assertEquals(N1-1,    stats.getHitCount(jpql1));
		assertEquals(N2-1,    stats.getHitCount(jpql2));
		assertEquals(N1+N2-2, stats.getHitCount());

	}

	public void testResetQueryStatistics() {
        QueryStatistics<String> stats = getPreparedQueryCache().getStatistics();
        stats.reset();

		String jpql1 = "select c from Company c";
        String jpql2 = "select c from Company c where c.name = 'PObject'";
		int N10 = 4;
		int N20 = 7;
		for (int i = 0; i < N10; i++) {
	        OpenJPAQuery<?> q1 = em.createQuery(jpql1);
			q1.getResultList();
		}
		for (int i = 0; i < N20; i++) {
	        OpenJPAQuery<?> q2 = em.createQuery(jpql2);
			q2.getResultList();
		}

		assertEquals(N10,       stats.getExecutionCount(jpql1));
		assertEquals(N20,       stats.getExecutionCount(jpql2));
		assertEquals(N10+N20,   stats.getExecutionCount());
		assertEquals(N10-1,     stats.getHitCount(jpql1));
		assertEquals(N20-1,     stats.getHitCount(jpql2));
		assertEquals(N10+N20-2, stats.getHitCount());

		stats.reset();

		int N11 = 7;
		int N21 = 4;
		for (int i = 0; i < N11; i++) {
            OpenJPAQuery<?> q1 = em.createQuery(jpql1);
			q1.getResultList();
		}
		for (int i = 0; i < N21; i++) {
            OpenJPAQuery<?> q2 = em.createQuery(jpql2);
			q2.getResultList();
		}

		assertEquals(N11,     stats.getExecutionCount(jpql1));
		assertEquals(N21,     stats.getExecutionCount(jpql2));
		assertEquals(N11+N21, stats.getExecutionCount());
		assertEquals(N11,     stats.getHitCount(jpql1));
		assertEquals(N21,     stats.getHitCount(jpql2));
		assertEquals(N11+N21, stats.getHitCount());

//		assertEquals(N10+N11,     stats.getTotalExecutionCount(jpql1));
//		assertEquals(N20+N21,     stats.getTotalExecutionCount(jpql2));
//		assertEquals(N10+N11+N20+N21, stats.getTotalExecutionCount());
		assertEquals(N10+N11-1,     stats.getTotalHitCount(jpql1));
		assertEquals(N20+N21-1,     stats.getTotalHitCount(jpql2));
		assertEquals(N10+N11+N20+N21-2, stats.getTotalHitCount());

	}

	public void testQueryWithNoParameter() {
		String jpql = "select p from Company p";
		compare(jpql, !IS_NAMED_QUERY);
	}

	public void testQueryWithLiteral() {
        String jpql = "select p from Company p where p.name = " + literal(COMPANY_NAMES[0]);
		compare(jpql, !IS_NAMED_QUERY);
	}

	public void testQueryWithParameter() {
		String jpql = "select p from Company p where p.name = :param";
		Object[] params = {"param", COMPANY_NAMES[0]};
		compare(jpql, !IS_NAMED_QUERY,  params);
	}

	public void testQueryWithJoinsAndParameters() {
        String jpql = "select e from Employee e " + "where e.name = :emp"
					+ " and e.department.name = :dept"
                    + " and e.department.company.name LIKE  "
                    + literal(COMPANY_NAMES[0])
					+ " and e.address.city = :city";
		Object[] params = { "emp", EMPLOYEE_NAMES[0],
                            "dept", DEPARTMENT_NAMES[0],
							"city", CITY_NAMES[0]};
		compare(jpql, !IS_NAMED_QUERY,  params);
	}

	public void testNamedQueryWithNoParameter() {
		String namedQuery = "Company.PreparedQueryWithNoParameter";
        compare(namedQuery, IS_NAMED_QUERY);
	}

	public void testNamedQueryWithLiteral() {
		String namedQuery = "Company.PreparedQueryWithLiteral";
		compare(namedQuery, IS_NAMED_QUERY);
	}

	public void testNamedQueryWithPositionalParameter() {
        String namedQuery = "Company.PreparedQueryWithPositionalParameter";
		Object[] params = {1, COMPANY_NAMES[0], 2, START_YEARS[0]};
		compare(namedQuery, IS_NAMED_QUERY,  params);
	}

	public void testNamedQueryWithNamedParameter() {
		String namedQuery = "Company.PreparedQueryWithNamedParameter";
        Object[] params = {"name", COMPANY_NAMES[0], "startYear",
                START_YEARS[0]};
		compare(namedQuery, IS_NAMED_QUERY,  params);
	}

	public void testPersistenceCapableParameter() {
        String jpql = "select e from Employee e " +
                "where e.department.company=:company";
	    Object[] params = {"company", IBM};
	    compare(jpql, !IS_NAMED_QUERY,  params);
	}

	/**
	 * Project results are returned with different types of ROP.
	 */
	public void testProjectionResult() {
        String jpql = "select e.name from Employee e " +
                "where e.address.city=:city";
        Object[] params = {"city", CITY_NAMES[0]};
        compare(jpql, !IS_NAMED_QUERY, params);
	}

	public void testCollectionValuedParameters() {
	    String jpql = "select e from Employee e where e.name in :names";
	    Object[] params1 = {"names",
	            Arrays.asList(new String[]{EMPLOYEE_NAMES[0],
	            EMPLOYEE_NAMES[1]})};
        Object[] params2 = {"names",
                Arrays.asList(new String[]{EMPLOYEE_NAMES[2]})};
        Object[] params3 = {"names", Arrays.asList(EMPLOYEE_NAMES)};

        int expectedCount = 2 * COMPANY_NAMES.length * DEPARTMENT_NAMES.length;
        run(jpql, !IS_NAMED_QUERY, params1, expectedCount, USE_CACHE, 1);

        expectedCount = 1 * COMPANY_NAMES.length * DEPARTMENT_NAMES.length;
        run(jpql, !IS_NAMED_QUERY, params2, expectedCount, USE_CACHE, 1);

        expectedCount = EMPLOYEE_NAMES.length * COMPANY_NAMES.length * DEPARTMENT_NAMES.length;
        run(jpql, !IS_NAMED_QUERY, params3, expectedCount, USE_CACHE, 1);
	}

    public void testQueryProjectionNotCandidateClass() {
        String jpql = "select e.department from Employee e";
        compare(jpql, !IS_NAMED_QUERY);
    }

    public void testQueryMultipleProjectionClass() {
        String jpql = "select d, e from Department d, in (d.employees) e";
        compare(jpql, !IS_NAMED_QUERY);
    }

    public void testQueryWithOrderByClause() {
        String jpql = "select e.name from Employee e order by e.id";
        compare(jpql, !IS_NAMED_QUERY);
    }

    public void testQueryCount() {
        String jpql = "select count(e),d from Department d join d.employees e group by d";
        compare(jpql, !IS_NAMED_QUERY);
    }

    public void testProjectRepeatsTerm() {
        String jpql = "select e.name, e.name from Employee e";
        compare(jpql, !IS_NAMED_QUERY);
    }

    public void testProjectEmbedded() {
        String jpql = "select e.address from Employee e";
        compare(jpql, !IS_NAMED_QUERY);
    }

    public void testNeedsTypeConversion() {
        String jpql = "select e.name, e.isManager from Employee e";
        compare(jpql, !IS_NAMED_QUERY);
    }

	String literal(String s) {
	    return "'"+s+"'";
	}

    public void testPositional() {
        String jpql = "select p from Person p where p.firstName=?1" +
                      " and p.lastName='Doe' and p.age > ?2";
        EntityManager em = emf.createEntityManager();

        OpenJPAQuery<?> q1 = OpenJPAPersistence.cast(em.createQuery(jpql));
        assertEquals(JPQLParser.LANG_JPQL, q1.getLanguage());

        List<?> result1 = q1.setParameter(1, "John")
                       .setParameter(2, (short)40)
                       .getResultList();

        assertEquals(2, result1.size());

        OpenJPAQuery<?> q2 = OpenJPAPersistence.cast(em.createQuery(jpql));
        assertEquals(QueryLanguages.LANG_PREPARED_SQL, q2.getLanguage());
        List<?> result2 = q2.setParameter(1, "Harry")
                  .setParameter(2, (short)10)
                  .getResultList();

        assertEquals(1, result2.size());
    }

    public void testNamed() {
        String jpql = "select p from Person p where p.firstName=:first" +
                      " and p.lastName='Doe' and p.age > :age";
        EntityManager em = emf.createEntityManager();

        OpenJPAQuery<?> q1 = OpenJPAPersistence.cast(em.createQuery(jpql));
        assertEquals(JPQLParser.LANG_JPQL, q1.getLanguage());

        List<?> result1 = q1.setParameter("first", "John")
                       .setParameter("age", (short)40)
                       .getResultList();

        assertEquals(2, result1.size());

        OpenJPAQuery<?> q2 = OpenJPAPersistence.cast(em.createQuery(jpql));
        assertEquals(QueryLanguages.LANG_PREPARED_SQL, q2.getLanguage());
        List<?> result2 = q2.setParameter("first", "Barry")
                  .setParameter("age", (short)20)
                  .getResultList();

        assertEquals(1, result2.size());
    }

    public void testWrongParameterValueTypeThrowException() {
        String jpql = "select p from Person p where p.firstName=:first"
                    + " and p.age > :age";
        EntityManager em = emf.createEntityManager();

        OpenJPAQuery<?> q1 = OpenJPAPersistence.cast(em.createQuery(jpql));
        try {
            List<?> result1 = q1.setParameter("first", (short)40)
                             .setParameter("age", "John")
                             .getResultList();
            fail("Expected to fail with wrong parameter value");
        } catch (IllegalArgumentException e) {
            // good
        }
    }

    public void testNullParameterValueForPrimitiveTypeThrowsException() {
        String jpql = "select p from Person p where p.firstName=:first"
                    + " and p.age > :age";
        EntityManager em = emf.createEntityManager();

        OpenJPAQuery<?> q1 = OpenJPAPersistence.cast(em.createQuery(jpql));
        try {
            List<?> result1 = q1.setParameter("first", "John")
                             .setParameter("age", null)
                             .getResultList();
            fail("Expected to fail with null parameter value for primitives");
        } catch (RuntimeException e) {
            // good
        }
    }
    public void testQueryWithLazyRelationIsCached() {
        // Author is lazily related to Book
        String jpql = "select p from Author p";
        EntityManager em = emf.createEntityManager();

        Query q1 = em.createQuery(jpql);
        assertEquals(OpenJPAPersistence.cast(q1).getLanguage(),
                JPQLParser.LANG_JPQL);
        List<Author> authors1 = q1.getResultList();
        assertFalse(authors1.isEmpty());
        Author author1 = authors1.iterator().next();
        em.close(); // nothing will be loaded by chance

        assertNull(author1.getBooks());

        // do the same thing again, this time query should be cached
        em = emf.createEntityManager();
        Query q2 = em.createQuery(jpql);
        assertEquals(OpenJPAPersistence.cast(q2).getLanguage(),
                QueryLanguages.LANG_PREPARED_SQL);
        List<Author> authors2 = q2.getResultList();
        assertFalse(authors2.isEmpty());
        Author author2 = authors2.iterator().next();
        em.close();

        assertNull(author2.getBooks());
    }

    public void testQueryWithEagerRelationIsNotCached() {
        // Book is eagerly related to Author
        String jpql = "select b from Book b";
        EntityManager em = emf.createEntityManager();

        Query q1 = em.createQuery(jpql);
        assertEquals(OpenJPAPersistence.cast(q1).getLanguage(),
                JPQLParser.LANG_JPQL);
        List<Book> books = q1.getResultList();
        assertFalse(books.isEmpty());
        Book book1 = books.iterator().next();
        em.close(); // nothing will be loaded by chance

        assertNotNull(book1.getAuthors());
        assertFalse(book1.getAuthors().isEmpty());

        // do the same thing again, this time query should not be cached
        // because it requires multiple selects
        em = emf.createEntityManager();
        Query q2 = em.createQuery(jpql);
        assertEquals(OpenJPAPersistence.cast(q2).getLanguage(),
                JPQLParser.LANG_JPQL);
        List<Book> books2 = q2.getResultList();
        assertFalse(books2.isEmpty());
        Book book2 = books2.iterator().next();
        em.close();

        assertNotNull(book2.getAuthors());
        assertFalse(book2.getAuthors().isEmpty());
    }

    public void testQueryWithUserDefinedAndInternalParamtersInSubquery() {
        String jpql = "Select a From Address a Where Not Exists ("
            + "     Select s.id From Singer As s Where "
            + "        s.address = a  And "
            + "        Not ("
            + "              (s.firstName = :firstName) "
            + "              Or "
            + "              ("
            + "                  ("
            + "                      exists (select c.id from CD c where c.singer = s and c.status = 1) And "
            + "                      s.lastName = :lastName"
            + "                  ) "
            + "                  Or "
            + "                  ("
            + "                      not exists (Select c.id from CD c where c.singer = s and c.status = 2)"
            + "                  )"
            + "              )"
            + "            )"
            + "     )";

        Query jQ = em.createQuery(jpql);
        jQ.setParameter("lastName", "LastName");
        jQ.setParameter("firstName", "FirstName");
        List jList = jQ.getResultList();

        Query jQ1 = em.createQuery(jpql);
        jQ1.setParameter("lastName", "LastName1");
        jQ1.setParameter("firstName", "FirstName1");
        try {
            List jList1 = jQ1.getResultList();
        } catch (Exception e) {
            System.err.println(jQ1.getParameters());
            e.printStackTrace();
            fail("Fail to execute again - Parameters are messed up:" + e.getMessage());
        }
    }

    public void testPreparedQueryIgnoredWhenLockModeIsSet() {
        String jpql = "select p from Author p";
        EntityManager em = emf.createEntityManager();

        Query q1 = em.createQuery(jpql);
        assertEquals(JPQLParser.LANG_JPQL, OpenJPAPersistence.cast(q1).getLanguage());
        List<Author> authors1 = q1.getResultList();

        // do the same thing again, this time query should be cached
        em.getTransaction().begin();
        Query q2 = em.createQuery(jpql);
        assertEquals(QueryLanguages.LANG_PREPARED_SQL, OpenJPAPersistence.cast(q2).getLanguage());
        LockModeType lmode1 = q2.getLockMode();
        q2.setLockMode(LockModeType.OPTIMISTIC);
        LockModeType lmode2 = q2.getLockMode();
        assertEquals(JPQLParser.LANG_JPQL, OpenJPAPersistence.cast(q2).getLanguage());
        assertFalse(lmode1.equals(lmode2));
        List<Author> authors2 = q2.getResultList();
        em.getTransaction().rollback();
    }

    public void testEnumParameter() {
        String jpql = "select e from Employee e where e.status=:current and e.hireStatus=:hire";
        EntityManager em = emf.createEntityManager();

        TypedQuery<Employee> q1 = em.createQuery(jpql, Employee.class);
        assertEquals(JPQLParser.LANG_JPQL, OpenJPAPersistence.cast(q1).getLanguage());
        List<Employee> emps = q1.setParameter("current", Category.PERMANENT)
                                .setParameter("hire", Category.CONTRACTOR).getResultList();

        // do the same thing again, this time query should be cached
        em.getTransaction().begin();
        TypedQuery<Employee> q2 = em.createQuery(jpql, Employee.class);
        assertEquals(QueryLanguages.LANG_PREPARED_SQL, OpenJPAPersistence.cast(q2).getLanguage());
        List<Employee> emps2 = q2.setParameter("current", Category.PERMANENT)
                            .setParameter("hire", Category.CONTRACTOR).getResultList();
        em.getTransaction().rollback();
    }

    public void testMultithreadedAccess() {
        OpenJPAEntityManager em1 = emf.createEntityManager();
        String jpql = "select p from Author p where p.name=:name";
        int N = 5;
        Thread[] threads =  new Thread[N];
        QueryThread[] qts = new QueryThread[N];
        for (int i = 0; i < N; i++) {
            OpenJPAEntityManager emt = emf.createEntityManager();
            qts[i] = new QueryThread(emt, jpql);
            threads[i] = new Thread(qts[i]);
            threads[i].setDaemon(true);
        }
        for (Thread t : threads) {
            t.start();
        }
        for (int i = 0; i < N; i++) {
            try {
              threads[i].join();
              assertFalse(qts[i].isFailed());
            } catch (InterruptedException e) {
                e.printStackTrace();
                fail();
            }
        }
    }

    public void testParameterOnExternalizedFieldIsExcluded() {
        String jpql = "select b from Book b where b.title=:title and b.token=:token";
        Query q1 = em.createQuery(jpql)
          .setParameter("title", "title-1")
          .setParameter("token", "LARGE");
        // default fetches authors eagerly and thus creates multiple SQL and hence not caches anyway
        OpenJPAPersistence.cast(q1).getFetchPlan().removeFetchGroup("default");
        assertFalse(q1.getResultList().isEmpty());
        assertNotCached(jpql);
        Query q2 = em.createQuery(jpql)
                     .setParameter("title", "title-2")
                     .setParameter("token", "MEDIUM");
       assertFalse(q2.getResultList().isEmpty());
    }

    public void testNoParameterOnExternalizedFieldIsIncluded() {
        String jpql = "select b from Book b where b.title=:title";
        Query q1 = em.createQuery(jpql)
          .setParameter("title", "title-1");
        // default fetches authors eagerly and thus creates multiple SQL and hence not caches anyway
        OpenJPAPersistence.cast(q1).getFetchPlan().removeFetchGroup("default");
        assertFalse(q1.getResultList().isEmpty());
        assertCached(jpql);
        Query q2 = em.createQuery(jpql)
                     .setParameter("title", "title-2");
       assertFalse(q2.getResultList().isEmpty());
    }

    public void testSubqueryParameters() {
        EntityManager em = emf.createEntityManager();
        String query = "select e from Employee e "
            + "inner join e.department d "
            + "inner join d.company c "
            + "where mod(c.startYear, 100) = 0 "
            + "and exists (select e2 from Employee e2 "
                + "inner join e2.department d2 "
                + "inner join d2.company c2 "
                + "where e2.address.city = e.address.city "
                + "and e2.isManager = false "
                + "and d2.name = d.name "
                + "and c2.name = :companyName) "
            + "and d.name = :departmentName";

        em.getTransaction().begin();
        TypedQuery<Employee> q1 = em.createQuery(query, Employee.class);
        q1.setParameter("companyName", "acme.org");
        q1.setParameter("departmentName", "Engineering");
        assertEquals(q1.getResultList().size(), 6);

        TypedQuery<Employee> q2 = em.createQuery(query, Employee.class);
        q2.setParameter("companyName", "acme.org");
        q2.setParameter("departmentName", "Engineering");
        assertEquals(q2.getResultList().size(), 6);
        em.getTransaction().rollback();
    }

    public void testRepeatedParameterInSubqueryInDifferentOrder() {
        OpenJPAEntityManager em = emf.createEntityManager();
        String jpql =  "select o from OrderJPA o "
                             + "where o.OrderId in (select max(o1.OrderId) from OrderJPA o1 "
                                   +  "where ((o1.CustomerId = :customerId) "
                                   +  "and   (o1.DistrictId = :districtId) "
                                   +  "and   (o1.WarehouseId = :warehouseId))) "
                    +  "and (o.CustomerId = :customerId) "
                    +  "and (o.WarehouseId = :warehouseId) "
                    +  "and (o.DistrictId = :districtId)";

        em.getTransaction().begin();
        TypedQuery<OrderJPA> q1 = em.createQuery(jpql, OrderJPA.class);
        q1.setParameter("customerId", 339)
          .setParameter("districtId", 3)
          .setParameter("warehouseId", 23);

        assertEquals(JPQLParser.LANG_JPQL, OpenJPAPersistence.cast(q1).getLanguage());
        assertFalse(q1.getResultList().isEmpty());


        TypedQuery<OrderJPA> q2 = em.createQuery(jpql, OrderJPA.class);
        assertEquals(QueryLanguages.LANG_PREPARED_SQL, OpenJPAPersistence.cast(q2).getLanguage());
        q2.setParameter("customerId", 2967)
          .setParameter("districtId", 5)
          .setParameter("warehouseId", 22);

        assertFalse(q2.getResultList().isEmpty());
        em.getTransaction().rollback();
    }

    public void testRepeatedParameterInSubqueryInSameOrder() {
        OpenJPAEntityManager em = emf.createEntityManager();
        String jpql =  "select o from OrderJPA o "
                             + "where o.OrderId in (select max(o1.OrderId) from OrderJPA o1 "
                             +  "where ((o1.CustomerId = :customerId) "
                             +  "and   (o1.DistrictId = :districtId) "
                             +  "and   (o1.WarehouseId = :warehouseId))) "
                        +  "and (o.CustomerId = :customerId) "
                        +  "and (o.DistrictId = :districtId) "
                        +  "and (o.WarehouseId = :warehouseId)";

        em.getTransaction().begin();
        TypedQuery<OrderJPA> q1 = em.createQuery(jpql, OrderJPA.class);
        q1.setParameter("customerId", 339)
          .setParameter("districtId", 3)
          .setParameter("warehouseId", 23);

        assertEquals(JPQLParser.LANG_JPQL, OpenJPAPersistence.cast(q1).getLanguage());
        assertFalse(q1.getResultList().isEmpty());


        TypedQuery<OrderJPA> q2 = em.createQuery(jpql, OrderJPA.class);
        assertEquals(QueryLanguages.LANG_PREPARED_SQL, OpenJPAPersistence.cast(q2).getLanguage());
        q2.setParameter("customerId", 2967)
          .setParameter("districtId", 5)
          .setParameter("warehouseId", 22);

        assertFalse(q2.getResultList().isEmpty());
        em.getTransaction().rollback();
    }

    public void testPartiallyRepeatedParameterInSubquery() {
        OpenJPAEntityManager em = emf.createEntityManager();
        String jpql =  "select o from OrderJPA o "
                             + "where o.OrderId in (select max(o1.OrderId) from OrderJPA o1 "
                             +  "where ((o1.CustomerId = :customerId) "
                             +  "and   (o1.WarehouseId = :warehouseId))) "
                        +  "and (o.CustomerId = :customerId) "
                        +  "and (o.DistrictId = :districtId) "
                        +  "and (o.WarehouseId = :warehouseId)";

        em.getTransaction().begin();
        TypedQuery<OrderJPA> q1 = em.createQuery(jpql, OrderJPA.class);
        q1.setParameter("customerId", 339)
          .setParameter("districtId", 3)
          .setParameter("warehouseId", 23);

        assertEquals(JPQLParser.LANG_JPQL, OpenJPAPersistence.cast(q1).getLanguage());
        assertFalse(q1.getResultList().isEmpty());


        TypedQuery<OrderJPA> q2 = em.createQuery(jpql, OrderJPA.class);
        assertEquals(QueryLanguages.LANG_PREPARED_SQL, OpenJPAPersistence.cast(q2).getLanguage());
        q2.setParameter("customerId", 2967)
          .setParameter("districtId", 5)
          .setParameter("warehouseId", 22);

        assertFalse(q2.getResultList().isEmpty());
        em.getTransaction().rollback();
    }

    public void testPartiallyRepeatedParameterInMainquery() {
        OpenJPAEntityManager em = emf.createEntityManager();
        String jpql =  "select o from OrderJPA o "
                             + "where o.OrderId in (select max(o1.OrderId) from OrderJPA o1 "
                             +  "where ((o1.CustomerId = :customerId) "
                             +  "and   (o1.DistrictId = :districtId) "
                             +  "and   (o1.WarehouseId = :warehouseId))) "
                        +  "and (o.CustomerId = :customerId) "
                        +  "and (o.WarehouseId = :warehouseId)";

        em.getTransaction().begin();
        TypedQuery<OrderJPA> q1 = em.createQuery(jpql, OrderJPA.class);
        q1.setParameter("customerId", 339)
          .setParameter("districtId", 3)
          .setParameter("warehouseId", 23);

        assertEquals(JPQLParser.LANG_JPQL, OpenJPAPersistence.cast(q1).getLanguage());
        assertFalse(q1.getResultList().isEmpty());


        TypedQuery<OrderJPA> q2 = em.createQuery(jpql, OrderJPA.class);
        assertEquals(QueryLanguages.LANG_PREPARED_SQL, OpenJPAPersistence.cast(q2).getLanguage());
        q2.setParameter("customerId", 2967)
          .setParameter("districtId", 5)
          .setParameter("warehouseId", 22);

        assertFalse(q2.getResultList().isEmpty());
        em.getTransaction().rollback();
    }

    public void testRangeIsExcluded() {
        List<Company> l = null;

        l = getAllCompaniesPaged(0, 1);
        assertEquals(1, l.size());
        assertEquals(1900, l.get(0).getStartYear());

        l = getAllCompaniesPaged(1, 1);
        assertEquals(1, l.size());
        assertEquals(2000, l.get(0).getStartYear());

        l = getAllCompaniesPaged(2, 1);
        assertEquals(1, l.size());
        assertEquals(2010, l.get(0).getStartYear());
    }

    public List<Company> getAllCompaniesPaged(int start, int max) {
        EntityManager em = emf.createEntityManager();
        Query q = em.createQuery("select p from Company p order by p.startYear");
        q.setFirstResult(start);
        q.setMaxResults(max);
        return (List<Company>) q.getResultList();
    }

    PreparedQueryCache getPreparedQueryCache() {
        return emf.getConfiguration().getQuerySQLCacheInstance();
    }

    /**
     * Compare the result of execution of the non-parameterized query with and without PreparedQuery cache.
     *
     * @param jpql a String representing either a JPQL or the name of a NamedQuery
     * @param isNamedQuery flags if the first input represents a JPQL string or a NamedQuery name
     */
    void compare(String jpql, boolean isNamed) {
        compare(jpql, isNamed, -1, NO_PARAMS);
    }


    /**
     * Compare the result of execution of the non-parameterized query with and without PreparedQuery cache.
     *
     * @param jpql a String representing either a JPQL or the name of a NamedQuery
     * @param isNamedQuery flags if the first input represents a JPQL string or a NamedQuery name
     * @param expectedCount expected number of results
     */
    void compare(String jpql, boolean isNamed, int expectedCount) {
        compare(jpql, isNamed, expectedCount, NO_PARAMS);
    }

    /**
     * Compare the result of execution of the query with and without PreparedQuery cache.
     *
     *
     * @param jpql a String representing either a JPQL or the name of a NamedQuery
     * @param isNamedQuery flags if the first input represents a JPQL string or a NamedQuery name
     * @param params a even sized array whose even-indexed elements are keys and odd-indexed elements are corresponding
     * parameter value for the query. A null array denotes the query is not parameterized.
     */
    void compare(String jpql, boolean isNamed, Object... params) {
        compare(jpql, isNamed, -1, params);
    }
    /**
     * Compare the result of execution of the query with and without PreparedQuery cache.
     *
     *
     * @param jpql a String representing either a JPQL or the name of a NamedQuery
     * @param isNamedQuery flags if the first input represents a JPQL string or a NamedQuery name
     * @param params a even sized array whose even-indexed elements are keys and odd-indexed elements are corresponding
     * parameter value for the query. A null array denotes the query is not parameterized.
     * @param expectedCount expected number of results. Supply a negative number to ignore.
     */
    void compare(String query, boolean isNamed, int expectedCount, Object... params) {
        run(query, isNamed, params, expectedCount, !USE_CACHE, 1); // run the query once for warming up

        // run N times without cache
        auditor.clear();
        long without = run(query, isNamed, params, expectedCount, !USE_CACHE, SAMPLE_SIZE);
        List<String> originalSQLs = auditor.getSQLs();

        // run N times with cache
        auditor.clear();
        long with = run(query, isNamed, params, expectedCount, USE_CACHE, SAMPLE_SIZE);
        List<String> cachedSQLs = auditor.getSQLs();

        compareSQLs(originalSQLs, cachedSQLs);
        long delta = (without == 0) ? 0 : (without - with) * 100 / without;

        String jpql = getJPQLString(query, isNamed);
        System.err.println((delta < 0 ? "***WARN " : "") + Math.abs(delta) + "% " +
                (delta < 0 ? "degradtion" : "improvement") + " for ["+ jpql + "]");
        assertTrue(Math.abs(delta) + "% degradtion for ["+ jpql + "]", !FAIL_IF_PERF_DEGRADE || delta > 0);
    }

    void compareSQLs(List<String> a, List<String> b) {
        assertEquals(a.size(), b.size());
        for (int i = 0; i < a.size(); i++) {
            assertEquals(a.get(i), b.get(i));
        }
    }

    /**
     * Create a query from the given string and execute it for given number of times.
     *
     * @param jpql a String representing either a JPQL or the name of a NamedQuery
     * @param isNamedQuery flags if the first input represents a JPQL string or a NamedQuery name
     * @param params a even sized array whose even-indexed elements are keys and odd-indexed elements are corresponding
     * parameter value for the query. A null array denotes the query is not parameterized.
     * @param expectedCount expected number of results. Supply a negative number to ignore.
     * @param useCache flags if the PreparedQuery cache is to be activated.
     * @param N number of times the query is to be executed to calculate reasonable statistics.
     *
     * @return median time to execute a query and iterate through its results
     */
    long run(String jpql, boolean isNamedQuery, Object[] params, int expectedCount, boolean useCache, int N) {
        List<Long> stats = new ArrayList<>();
        String cacheKey = getJPQLString(jpql, isNamedQuery);
        QueryStatistics<String> cacheStats = getPreparedQueryCache().getStatistics();
        getPreparedQueryCache().clear();
        assertEquals(0, cacheStats.getExecutionCount(cacheKey));
        assertEquals(0, cacheStats.getHitCount(cacheKey));

        for (int i = 0; i < N; i++) {
            OpenJPAEntityManagerSPI em = (OpenJPAEntityManagerSPI)emf.createEntityManager();
            em.setQuerySQLCache(useCache);
            assertEquals(useCache, em.getQuerySQLCache());

            // measure time
            long start = System.nanoTime();
            OpenJPAQuery<?> q = isNamedQuery ? em.createNamedQuery(jpql) : em.createQuery(jpql);
            parameterize(q, params);
            List<?> list = q.getResultList();
            if (expectedCount >= 0)
                assertEquals(expectedCount, list.size());
            else
                assertFalse(list.isEmpty());
            iterate(list);
            long end = System.nanoTime();

            assertEquals(useCache ? i+1 : 0, cacheStats.getExecutionCount(cacheKey));
            assertEquals(useCache ? i : 0,   cacheStats.getHitCount(cacheKey));

            q.closeAll();
            stats.add(end - start);
            em.close();
        }
        assertEquals("Execution Count [" + cacheKey + "]", useCache ? N : 0, cacheStats.getTotalExecutionCount());
        assertEquals("Hit Count [" + cacheKey + "]", useCache ? N-1 : 0, cacheStats.getTotalHitCount());

        Collections.sort(stats);
        return stats.get(N/2);
    }

    public void testRepeatedQueryInBetweenParameters() {
    	OpenJPAEntityManager em = emf.createEntityManager();
    	em.clear();

    	String jpql1 = "SELECT e FROM Employee e";
    	int employeeSize = em.createQuery(jpql1, Employee.class).getResultList().size();

    	String jpql2 = "SELECT e FROM Employee e WHERE :baseDate between e.startDate AND e.endDate";
    	TypedQuery<Employee> q1 = em.createQuery(jpql2, Employee.class);
    	q1.setParameter("baseDate", new GregorianCalendar(2016, Calendar.JUNE, 1).getTime());
    	int count2016 = (int)(employeeSize * ((double)2/3));

		assertEquals(count2016, q1.getResultList().size());

		TypedQuery<Employee> q2 = em.createQuery(jpql2, Employee.class);
    	q2.setParameter("baseDate", new GregorianCalendar(2017, Calendar.JUNE, 1).getTime());
    	int count2017 = (int)(employeeSize * ((double)2/3));

		assertEquals(count2017, q2.getResultList().size());
    }

    void parameterize(Query q, Object[] params) {
        if (params == null)
            return;
        for (int j = 0; params != null && j < params.length - 1; j += 2) {
            Object key = params[j];
            Object val = params[j + 1];
            if (key instanceof Integer)
                q.setParameter(((Number)key).intValue(), val);
            else if (key instanceof String)
                q.setParameter(key.toString(), val);
            else
                fail("key " + key + " is neither Number nor String");
        }
    }

    void iterate(List<?> list) {
        Iterator<?> i = list.iterator();
        while (i.hasNext())
            i.next();
    }

    /**
     * Gets the JPQL String of a NamedQuery of the given name.
     */
    String getJPQLString(String name, boolean isNamedQuery) {
        if (!isNamedQuery)
            return name;
        return emf.getConfiguration()
                  .getMetaDataRepositoryInstance()
                  .getQueryMetaData(null, name, null, true)
                  .getQueryString();
    }


    /**
     * A JDBC Listener to audit target SQL executed per JPQL query.
     *
     */
    public class SQLAuditor extends AbstractJDBCListener {
        private List<String> sqls = new ArrayList<>();

        @Override
        public void beforeExecuteStatement(JDBCEvent event) {
            if (event.getSQL() != null && sqls != null) {
               sqls.add(event.getSQL());
            }
        }

        void clear() {
            sqls.clear();
        }

        List<String> getSQLs() {
            return new ArrayList<>(sqls);
        }
    }

    public static class QueryThread implements Runnable {
        private final OpenJPAEntityManager em;
        private final String jpql;
        private boolean failed = false;
        public QueryThread(OpenJPAEntityManager em, String jpql) {
            super();
            this.em = em;
            this.jpql = jpql;
        }

        @Override
        public void run() {
            try {
                for (int i = 0; i < 10 && !failed; i++) {
                    OpenJPAQuery q = em.createQuery(jpql);
                    q.setParameter("name", "Author-"+i);
                    q.getResultList();
                    if (i > 1)
                        assertEquals(QueryLanguages.LANG_PREPARED_SQL, q.getLanguage());
                }
            } catch (Exception e) {
                e.printStackTrace();
                failed = true;
            }
        }

        public boolean isFailed() {
            return failed;
        }

    }

}
