jpa/eclipselink.jpa.wdf.test/src/it/java/org/eclipse/persistence/testing/tests/wdf/jpa1/query/TestExtendedQueries.java

/*
 * Copyright (c) 2005, 2021 Oracle and/or its affiliates. All rights reserved.
 * Copyright (c) 2005, 2015 SAP. All rights reserved.
 *
 * This program and the accompanying materials are made available under the
 * terms of the Eclipse Public License v. 2.0 which is available at
 * http://www.eclipse.org/legal/epl-2.0,
 * or the Eclipse Distribution License v. 1.0 which is available at
 * http://www.eclipse.org/org/documents/edl-v10.php.
 *
 * SPDX-License-Identifier: EPL-2.0 OR BSD-3-Clause
 */

// Contributors:
//     SAP - initial API and implementation

package org.eclipse.persistence.testing.tests.wdf.jpa1.query;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Random;
import java.util.Set;

import jakarta.persistence.EntityManager;
import jakarta.persistence.Query;

import org.eclipse.persistence.testing.framework.wdf.JPAEnvironment;
import org.eclipse.persistence.testing.framework.wdf.ToBeInvestigated;
import org.eclipse.persistence.testing.models.wdf.jpa1.employee.Bicycle;
import org.eclipse.persistence.testing.models.wdf.jpa1.employee.Department;
import org.eclipse.persistence.testing.models.wdf.jpa1.employee.Employee;
import org.eclipse.persistence.testing.models.wdf.jpa1.employee.EmploymentPeriod;
import org.eclipse.persistence.testing.models.wdf.jpa1.employee.Hobby;
import org.eclipse.persistence.testing.models.wdf.jpa1.employee.MotorVehicle;
import org.eclipse.persistence.testing.tests.wdf.jpa1.JPA1Base;
import org.junit.Test;

public class TestExtendedQueries extends JPA1Base {

    // the random generator will use this final seed in order to enable reproduceable testing
    private static final long RANDOM_SEED = 1232133213;
    // should not be changed
    private static final int NUMBER_OF_DEPARTMENTS = 10;
    private static final int NUMBER_OF_EMPLOYEES = 100;
    // number of maximum tries during random distribution
    private static final int MAX_NUMBER_OF_TRIES = 100;
    // since there is a one-to-one relationship for vehicle driver this must at least match the number of employees
    private static final short NUMBER_OF_MOTORVEHICLES = NUMBER_OF_EMPLOYEES;
    private static final short NUMBER_OF_BICYCLES = 5;
    // this maps take note of how much entities are used in relationships
    private static final Map<String, Department> ALL_DEPARTMENTS = new HashMap<String, Department>();
    private static final Map<String, Integer> NUMBER_OF_GIVEN_NAMES_USED = new HashMap<String, Integer>();
    private static final Map<String, Integer> NUMBER_OF_SURNAMES_USED = new HashMap<String, Integer>();
    private static final Map<Short, List<Integer>> BIKES_RIDERS = new HashMap<Short, List<Integer>>();
    private static final Map<Short, Integer> MOTORVEHICLES_DRIVERS = new HashMap<Short, Integer>();
    private static final Set<String> EMP_HOBBIES = new HashSet<String>();
    // an array of given names
    private static final String[] GIVEN_NAMES = { "Hadmar", "Hagen", "Hakan", "Hakon", "Hannes", "Hanns", "Hans", "Hansj\u00f6rg",
            "Hansklaus", "Hanspeter", "Harald", "Harold", "Harro", "Harry", "Hartmut", "Hartwig", "Hartwin", "Harvey", "Hasso",
            "Hauke", "H\u00e4nel", "Heiko", "Heyko", "Heimo", "Hein", "Heiner", "Heinrich", "Heinz", "Helge", "Helmar", "Helmut",
            "Hendrik", "Henning", "Henri", "Henrik", "Henryk", "Herbert", "Heribert", "Hermann", "Hieronymus", "Hilarius",
            "Hilmar", "Hinrich", "Hjalmar", "Holger", "Horst", "Hubert", "Hubertus", "Hugo", "Humphrey", "Hanna", "Hannah",
            "Hannelore", "Harriet", "Hedi", "Hedwig", "Heide", "Heidemarie", "Heidi", "Heike", "Heinke", "Helen", "Helena",
            "Helene", "Helga", "Helgard", "Hella", "Helma", "Henrike", "Herma", "Hermine", "Hertha", "Hieu", "Hilda", "Hilde",
            "Hildegard", "Hilke", "Hulda", "Holde", "Holda", "Hedda", "Heida", "Hazel" };
    // an array of surnames
    private static final String[] SURNAMES = { "Haak", "Haas", "Habermann", "Hache", "Hackmann", "Halbach", "Haller", "Hamann",
            "Hamburger", "Hamilton", "Hampe", "Hanisch", "Hanson", "Hanstein", "Hardenberg", "Harding", "Hardy", "Harnack",
            "Hartenthaler", "Hartfelder", "Hartig", "Hary", "Haus Lancaster", "Hausmann", "Hausner", "Havenstein", "Hawke",
            "Hayek", "Hecht", "Heer", "Hegemann", "Heidegger", "Heider", "Heim", "Hein", "Heinichen", "Heinrich", "Heise",
            "Helbig", "Hellmann", "Helmig", "Helms", "Hemsterhuis", "Hendrix", "Hennig", "Henning", "Henry", "Henschel",
            "Hense", "Hentschel", "Herbst", "Herder", "Hermann", "Hermelink", "Herold", "Herrmann", "Herzog", "Hesse",
            "Hesselbarth", "Hettner", "Heuser", "Hewitt", "Hilbert", "Hildebrand", "Hildebrandt", "Hilgenfeld" };
    // list of hobbienames
    private static final String[] HOBBY_NAMES = { "reading", "cooking", "watching tv", "cleaning the house", "swimming",
            "computers", "stamps" };
    // array of all hobbies
    private static final Hobby[] HOBBIES = new Hobby[HOBBY_NAMES.length];
    // array of all bicycles
    private static final Bicycle[] BICYCLES = new Bicycle[NUMBER_OF_BICYCLES];
    // array of all motorvehicles
    private static final MotorVehicle[] MOTORVEHICLES = new MotorVehicle[NUMBER_OF_MOTORVEHICLES];
    // randomizer for distributing relationships between Employees and other entities
    private static final Random nameRandomizer = new Random(RANDOM_SEED);

    protected void resetData() {
        NUMBER_OF_GIVEN_NAMES_USED.clear();
        NUMBER_OF_SURNAMES_USED.clear();
        EMP_HOBBIES.clear();
        MOTORVEHICLES_DRIVERS.clear();
    }

    protected String getRandomSurname() {
        String tmpName = SURNAMES[nameRandomizer.nextInt(SURNAMES.length)];
        if (!NUMBER_OF_SURNAMES_USED.containsKey(tmpName)) {
            NUMBER_OF_SURNAMES_USED.put(tmpName, 1);
        } else {
            Integer tmpNum = NUMBER_OF_SURNAMES_USED.get(tmpName);
            tmpNum = tmpNum + 1;
            NUMBER_OF_SURNAMES_USED.put(tmpName, tmpNum);
        }
        return tmpName;
    }

    protected String getRandomGivenName() {
        String tmpName = GIVEN_NAMES[nameRandomizer.nextInt(GIVEN_NAMES.length)];
        if (!NUMBER_OF_GIVEN_NAMES_USED.containsKey(tmpName)) {
            NUMBER_OF_GIVEN_NAMES_USED.put(tmpName, 1);
        } else {
            Integer tmpNum = NUMBER_OF_GIVEN_NAMES_USED.get(tmpName);
            tmpNum = tmpNum + 1;
            NUMBER_OF_GIVEN_NAMES_USED.put(tmpName, tmpNum);
        }
        return tmpName;
    }

    /**
     * returns a hobby for given employee, it is not possible to assign a hobby more than once to a employee
     *
     * @param empID
     *            the ID of the employee
     * @return a hobby for the given employee ID
     */
    protected Hobby getRandomHobby(int empID) {
        boolean matched = false;
        int index = 0;
        for (int number_of_tries = 0; !matched; number_of_tries++) {
            index = nameRandomizer.nextInt(HOBBIES.length - 1);
            if (!EMP_HOBBIES.contains(empID + "_" + index)) {
                EMP_HOBBIES.add(empID + "_" + index);
                matched = true;
            }
            if (number_of_tries >= MAX_NUMBER_OF_TRIES) {
                throw new RuntimeException("Can't find anymore randomly distributed hobbies");
            }
        }
        return HOBBIES[index];
    }

    @SuppressWarnings("boxing")
    protected Bicycle getRandomBike(int empID) {
        short index = (short) nameRandomizer.nextInt(BICYCLES.length - 1);
        // take a note about this relation
        if (!BIKES_RIDERS.containsKey(BICYCLES[index].getId())) {
            ArrayList<Integer> tmpAl = new ArrayList<Integer>();
            tmpAl.add(empID);
            BIKES_RIDERS.put(BICYCLES[index].getId(), tmpAl);
        } else {
            List<Integer> tmpAl = BIKES_RIDERS.get(BICYCLES[index].getId());
            tmpAl.add(empID);
        }
        return BICYCLES[index];
    }

    @SuppressWarnings("boxing")
    protected MotorVehicle getRandomMotorVehicle(int empID) {
        // take note about this relation
        boolean matched = false;
        short index = 0;
        for (int number_of_tries = 0; !matched; number_of_tries++) {
            index = (short) nameRandomizer.nextInt(MOTORVEHICLES.length - 1);
            if (!MOTORVEHICLES_DRIVERS.containsKey(MOTORVEHICLES[index].getId())) {
                MOTORVEHICLES_DRIVERS.put(MOTORVEHICLES[index].getId(), empID);
                matched = true;
            }
            if (number_of_tries >= MAX_NUMBER_OF_TRIES) {
                throw new RuntimeException("Can't find anymore randomly distributed motorvehicles");
            }
        }
        return MOTORVEHICLES[index];
    }

    @Override
    protected void setup() {
        resetData();
        JPAEnvironment env = getEnvironment();
        EntityManager em = env.getEntityManager();
        try {
            env.beginTransaction(em);
            // fills the hobby-array
            for (int i = 0; i < HOBBY_NAMES.length; i++) {
                Hobby aHobby = new Hobby(HOBBY_NAMES[i]);
                HOBBIES[i] = aHobby;
                em.persist(aHobby);
            }
            // creates & persists some departments
            for (int i = 0; i < NUMBER_OF_DEPARTMENTS; i++) {
                Department tmpDep = new Department(i, "Department_" + i);
                // ALL_DEPARTMENTS.put(tmpDep.getName(), tmpDep);
                em.persist(tmpDep);
            }
            // creates & persists some motorvehicles
            for (int i = 0; i < NUMBER_OF_MOTORVEHICLES; i++) {
                MotorVehicle tmpMVehicle = new MotorVehicle();
                tmpMVehicle.setBrand("foo-car");
                MOTORVEHICLES[i] = tmpMVehicle;
                em.persist(tmpMVehicle);
            }
            // creates & persists some bicycles
            for (int i = 0; i < NUMBER_OF_BICYCLES; i++) {
                Bicycle tmpBike = new Bicycle();
                tmpBike.setBrand("foo-brand");
                tmpBike.setRiders(new ArrayList<Employee>());
                BICYCLES[i] = tmpBike;
                em.persist(tmpBike);
            }
            // creates & persists some empoyees
            for (int i = 0; i < NUMBER_OF_EMPLOYEES; i++) {
                /*
                 * sets a department tries to set 10 employees to each department example: employees 1 to 10 are related to
                 * department 1. employees 11 to 20 are mapped to department 2 and so on
                 */
                Employee tmpEmp = new Employee(i, this.getRandomGivenName(), this.getRandomSurname(), ALL_DEPARTMENTS
                        .get("Department_" + Math.ceil(i / 100) * 10));
                // add two hobbies
                tmpEmp.addHobby(this.getRandomHobby(tmpEmp.getId()));
                tmpEmp.addHobby(this.getRandomHobby(tmpEmp.getId()));
                tmpEmp.addHobby(this.getRandomHobby(tmpEmp.getId()));
                tmpEmp.addHobby(this.getRandomHobby(tmpEmp.getId()));
                tmpEmp.addHobby(this.getRandomHobby(tmpEmp.getId()));
                // every second employee gets a motorvehicle
                if (i % 2 == 0) {
                    getRandomMotorVehicle(tmpEmp.getId()).setDriver(tmpEmp);
                }
                // the others get a bicycle
                else {
                    getRandomBike(tmpEmp.getId()).getRiders().add(tmpEmp);
                }
                // set the period
                tmpEmp.setEmploymentPeriod(new EmploymentPeriod());
                tmpEmp.getEmploymentPeriod().setStartDate(new Date(System.currentTimeMillis() - 1000000000));
                tmpEmp.getEmploymentPeriod().setEndDate(new Date(System.currentTimeMillis() + 1000000000));
                em.persist(tmpEmp);
            }
            env.commitTransactionAndClear(em);
            // em.clear();
        } finally {
            closeEntityManager(em);
        }
    }

    /*
     * @Test public void testQueryWithMemberOf() throws Exception { EntityManager em = getEnvironment().getEntityManager(); try
     * { Bicycle bike = new Bicycle(); bike.setId((short)12); bike.setBrand("foo"); bike.setColor("brown");
     * bike.setNumberOfGears((short)1); em.persist(bike);
     *
     * //test string-mapping of enums Query query = em.createQuery("Select Distinct Object(emp) FROM Employee emp WHERE :param
     * NOT MEMBER emp.bicycles"); query.setParameter("param", bike); query.getResultList(); } finally { closeEntityManager(em);
     * } }
     */
    // @TestProperties(unsupportedDatabaseVendors = { DatabaseVendor.OPEN_SQL })
    @Test
    public void testQueryWithUpdateAndSet() throws Exception {
        JPAEnvironment env = getEnvironment();
        EntityManager em = env.getEntityManager();
        try {
            env.beginTransaction(em);
            Query query = em
                    .createQuery("UPDATE Employee emp SET emp.salary = emp.salary + :param1 WHERE emp.firstname = SUBSTRING(:string, :int1, :int2)");
            query.setParameter("param1", 13);
            query.setParameter("string", "moo");
            query.setParameter("int1", 1);
            query.setParameter("int2", 2);
            query.executeUpdate();
            env.commitTransaction(em);
        } finally {
            closeEntityManager(em);
        }
    }

    // @TestProperties(unsupportedDatabaseVendors = { DatabaseVendor.OPEN_SQL })
    @Test
    public void testQueryWithBuiltinAbs() throws Exception {
        EntityManager em = getEnvironment().getEntityManager();
        try {
            // test string-mapping of enums
            Query query = em.createQuery("Select DISTINCT Object(emp) From Employee emp WHERE emp.salary > ABS(:dbl)");
            query.setParameter("dbl", 1180D);
            query.getResultList();
        } finally {
            closeEntityManager(em);
        }
    }

    // @TestProperties(unsupportedDatabaseVendors = { DatabaseVendor.OPEN_SQL })
    @Test
    public void testQueryWithMemberOf() throws Exception {
        EntityManager em = getEnvironment().getEntityManager();
        try {
            // test string-mapping of enums
            Query query = em
                    .createQuery("Select Distinct Object(emp) FROM Employee emp WHERE emp.firstname = SUBSTRING(:string, :int1, :int2)");
            query.setParameter("string", "moo");
            query.setParameter("int1", 1);
            query.setParameter("int2", 2);
            query.getResultList();
        } finally {
            closeEntityManager(em);
        }
    }

    @Test
    public void testQueryWithComparisonExpressionAndEnums() throws Exception {
        EntityManager em = getEnvironment().getEntityManager();
        try {
            // test string-mapping of enums
            Query query4 = em
                    .createQuery("SELECT btfa.id FROM BasicTypesFieldAccess btfa WHERE btfa.enumString = org.eclipse.persistence.testing.models.wdf.jpa1.types.UserDefinedEnum.HUGO");
            query4.getResultList();
            // test ordinal-mapping of enums
            Query query5 = em
                    .createQuery("SELECT btfa.id FROM BasicTypesFieldAccess btfa WHERE btfa.enumOrdinal = org.eclipse.persistence.testing.models.wdf.jpa1.types.UserDefinedEnum.HUGO");
            query5.getResultList();
        } finally {
            closeEntityManager(em);
        }
    }

    @ToBeInvestigated
    @Test
    public void testQueryWithInExpressionAndEnums() throws Exception {
        EntityManager em = getEnvironment().getEntityManager();
        try {
            // test string-mapping of enums
            Query query4 = em
                    .createQuery("SELECT btfa.id FROM BasicTypesFieldAccess btfa WHERE btfa.enumString IN (org.eclipse.persistence.testing.models.wdf.jpa1.types.UserDefinedEnum.HUGO, org.eclipse.persistence.testing.models.wdf.jpa1.types.UserDefinedEnum.EMIL)");
            query4.getResultList();
            // test ordinal-mapping of enums
            Query query5 = em
                    .createQuery("SELECT btfa.id FROM BasicTypesFieldAccess btfa WHERE btfa.enumOrdinal IN (com.sap.jpa.example.UserDefinedEnum.HUGO, com.sap.jpa.example.UserDefinedEnum.EMIL)");
            query5.getResultList();
        } finally {
            closeEntityManager(em);
        }
    }

    @Test
    public void testQueryWithAggregateWithHavingExpression() throws Exception {
        EntityManager em = getEnvironment().getEntityManager();
        try {
            // this one tests aggregate expression in having condition
            Query query2 = em
                    .createQuery("SELECT  e.firstname FROM Employee e JOIN e.hobbies h GROUP BY e.firstname HAVING COUNT(DISTINCT e.id) = 1");
            query2.getResultList();
        } finally {
            closeEntityManager(em);
        }
    }

    @Test
    public void testQueryWithInExpressionAndParameters() throws Exception {
        EntityManager em = getEnvironment().getEntityManager();
        try {
            // this one tests for the IN predicate in combination with two parameters
            Query query1 = em.createQuery("select e.id from Employee e where e.id IN (?1, ?2)");
            query1.setParameter(1, 1);
            query1.setParameter(2, 2);
            query1.getResultList();
        } finally {
            closeEntityManager(em);
        }
    }

    @SuppressWarnings("unchecked")
    @Test
    public void testBasicQuery() {
        EntityManager em = getEnvironment().getEntityManager();
        try {
            Query query = em.createQuery("select e from Employee e");
            List result = query.getResultList();
            verify(result.size() == NUMBER_OF_EMPLOYEES, "wrong resultcount for employees");
        } finally {
            closeEntityManager(em);
        }
    }

    /**
     * check for a thrown exception in case of an illegal OrderBy-clause
     */
    @ToBeInvestigated
    @Test
    public void testInvalidOrderBy() {
        boolean passed = false;
        EntityManager em = getEnvironment().getEntityManager();
        try {
            em.createQuery("SELECT e.firstname FROM Employee e ORDER BY e.salary");
        } catch (IllegalArgumentException iAEx) {
            passed = true;
        } finally {
            closeEntityManager(em);
        }
        verify(passed, "missing IllegalArgumentException");
    }

    /**
     * check for a thrown exception in case of an illegal OrderBy-clause
     */
    @ToBeInvestigated
    @Test
    public void testInvalidOrderByWithCmr() {
        boolean passed = false;
        EntityManager em = getEnvironment().getEntityManager();
        try {
            em.createQuery("SELECT e.department.name FROM Employee e ORDER BY e.department.id");
        } catch (IllegalArgumentException iAEx) {
            passed = true;
        } finally {
            closeEntityManager(em);
        }
        verify(passed, "missing IllegalArgumentException");
    }

    @Test
    public void testOrderBy() {
        EntityManager em = getEnvironment().getEntityManager();
        try {
            em.createQuery("SELECT e FROM Employee e ORDER BY e.salary ASC, e.firstname DESC, e.lastname DESC");
        } finally {
            closeEntityManager(em);
        }
    }

    @Test
    public void testOrderByCmr() {
        EntityManager em = getEnvironment().getEntityManager();
        try {
            em.createQuery("SELECT e.department FROM Employee e ORDER BY e.department.id ASC");
        } finally {
            closeEntityManager(em);
        }
    }

    /**
     * check for the right occurence of all names in the result of the query
     */
    @SuppressWarnings("unchecked")
    @Test
    public void testSurnamesOfEmployees() {
        EntityManager em = getEnvironment().getEntityManager();
        try {
            Query query = em.createQuery("SELECT e from Employee e WHERE e.lastname=?1");
            Iterator<String> iter = NUMBER_OF_SURNAMES_USED.keySet().iterator();
            while (iter.hasNext()) {
                String surname = iter.next();
                Integer surnamesUsed = NUMBER_OF_SURNAMES_USED.get(surname);
                query.setParameter(1, surname);
                List result = query.getResultList();
                verify(result.size() == surnamesUsed, "the number of persons with given name " + surname
                        + " in the result does not match the number of names used when the entities where created.");
            }
        } finally {
            closeEntityManager(em);
        }
    }

    @SuppressWarnings("unchecked")
    @Test
    public void testGivenNamesOfEmployees() {
        EntityManager em = getEnvironment().getEntityManager();
        try {
            Query query = em.createQuery("SELECT e from Employee e WHERE e.firstname=?1");
            Iterator<String> iter = NUMBER_OF_GIVEN_NAMES_USED.keySet().iterator();
            while (iter.hasNext()) {
                String givenName = iter.next();
                Integer givenNamesUsed = NUMBER_OF_GIVEN_NAMES_USED.get(givenName);
                query.setParameter(1, givenName);
                List result = query.getResultList();
                verify(result.size() == givenNamesUsed, "the number of persons with given name " + givenName
                        + " in the result does not match the number of names used when the entities where created.");
            }
        } finally {
            closeEntityManager(em);
        }
    }

    @SuppressWarnings("unchecked")
    @Test
    public void testNumberOfDrivers() {
        EntityManager em = getEnvironment().getEntityManager();
        try {
            Query query = em.createQuery("SELECT d.firstname FROM MotorVehicle mv JOIN mv.driver d WHERE mv.id = ?1");
            Iterator<Short> iter = MOTORVEHICLES_DRIVERS.keySet().iterator();
            while (iter.hasNext()) {
                Short vehicleID = iter.next();
                // can only be 1 since the one-to-one relationship for drivers
                int numberOfDrivers = 1;
                query.setParameter(1, vehicleID);
                List result = query.getResultList();
                verify(result.size() == numberOfDrivers, "the number of drivers for motorvehicle does not match "
                        + result.size() + " != " + numberOfDrivers);
            }
        } finally {
            closeEntityManager(em);
        }
    }

    @SuppressWarnings("unchecked")
    @Test
    public void testNumberOfDrivenVehicles() {
        EntityManager em = getEnvironment().getEntityManager();
        try {
            Query query = em.createQuery("SELECT d.firstname FROM MotorVehicle mv JOIN mv.driver d");
            List result = query.getResultList();
            verify(result.size() == MOTORVEHICLES_DRIVERS.size(), "there number of driver-relationships between ");
        } finally {
            closeEntityManager(em);
        }
    }
}