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

/*
 * Copyright (c) 2005, 2020 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.sql.Date;
import java.sql.SQLException;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
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.Project;
import org.eclipse.persistence.testing.models.wdf.jpa1.employee.Review;
import org.eclipse.persistence.testing.tests.wdf.jpa1.JPA1Base;
import org.junit.Assert;
import org.junit.Test;

@SuppressWarnings("unchecked")
public class TestRelationships extends JPA1Base {

    private final Set<Department> ALL_DEPARTMENTS = new HashSet<Department>();
    private final Department dep10 = new Department(10, "ten");
    private final Department dep20 = new Department(20, "twenty");

    private void init() throws SQLException {
        clearAllTables();
        ALL_DEPARTMENTS.add(dep10);
        ALL_DEPARTMENTS.add(dep20);
        JPAEnvironment env = getEnvironment();
        EntityManager em = env.getEntityManager();
        try {
            env.beginTransaction(em);
            em.persist(dep10);
            em.persist(dep20);
            env.commitTransactionAndClear(em);
        } finally {
            closeEntityManager(em);
        }
    }

    @Test
    public void testRelationFieldInWhere() throws SQLException {
        init();
        JPAEnvironment env = getEnvironment();
        EntityManager em = env.getEntityManager();
        try {
            env.beginTransaction(em);
            Employee emp = new Employee(15, "first", "last", dep10);
            em.persist(emp);
            env.commitTransactionAndClear(em);
            Query query = em.createQuery("select e from Employee e where e.department.name = 'ten'");
            List result = query.getResultList();
            verify(result.size() == 1, "wrong resultcount");
            Iterator iter = result.iterator();
            while (iter.hasNext()) {
                Employee employee = (Employee) iter.next();
                verify(employee.getId() == 15, "wrong employee");
            }
        } finally {
            closeEntityManager(em);
        }
    }

    @Test
    public void testRelationFieldInSelectList() throws SQLException {
        init();
        JPAEnvironment env = getEnvironment();
        EntityManager em = env.getEntityManager();
        try {
            env.beginTransaction(em);
            em.persist(new Employee(15, "Hans", "M\u00fcller", dep10));
            em.persist(new Employee(16, "Fred", "Maier", dep10));
            env.commitTransactionAndClear(em);
            Query query = em.createQuery("select e.firstname from Employee e where e.lastname = 'Maier'");
            List result = query.getResultList();
            verify(result.size() == 1, "wrong resultcount");
            for (Object object : result) {
                verify("Fred".equals(object), "wrong employee");
            }
        } finally {
            closeEntityManager(em);
        }
    }

    @Test
    public void testEmpty() throws SQLException {
        init();
        JPAEnvironment env = getEnvironment();
        EntityManager em = env.getEntityManager();
        try {
            env.beginTransaction(em);
            em.persist(new Employee(15, "Hans", "M\u00fcller", dep10));
            Employee fred = new Employee(16, "Fred", "Maier", dep10);
            Review review = new Review(1, Date.valueOf("2006-01-02"), "b");
            em.persist(review);
            fred.addReview(review);
            em.persist(fred);
            env.commitTransactionAndClear(em);
            Query query = em.createQuery("select e.firstname from Employee e where e.reviews is not empty");
            List result = query.getResultList();
            verify(result.size() == 1, "wrong resultcount");
            for (Object object : result) {
                verify("Fred".equals(object), "wrong department name");
            }
            query = em.createQuery("select e.firstname from Employee e where e.reviews is empty");
            result = query.getResultList();
            verify(result.size() == 1, "wrong resultcount");
            for (Object object : result) {
                verify("Hans".equals(object), "wrong department name");
            }
        } finally {
            closeEntityManager(em);
        }
    }

    @Test
    public void testInJoin() throws SQLException {
        init();
        JPAEnvironment env = getEnvironment();
        EntityManager em = env.getEntityManager();
        try {
            env.beginTransaction(em);
            em.persist(new Employee(15, "Hans", "M\u00fcller", dep10));
            Employee fred = new Employee(16, "Fred", "Maier", dep10);
            Review fredsReview = new Review(1, Date.valueOf("2006-01-02"), "first");
            em.persist(fredsReview);
            fred.addReview(fredsReview);
            em.persist(fred);
            env.commitTransactionAndClear(em);
            Query query = em.createQuery("select e.firstname from Employee e, in (e.reviews) r where r.id = 1");
            List result = query.getResultList();
            verify(result.size() == 1, "wrong resultcount");
            for (Object object : result) {
                verify("Fred".equals(object), "wrong department name");
            }
            query = em.createQuery("select e.firstname from Employee e, in (e.reviews) r where r.id = 2");
            result = query.getResultList();
            verify(result.size() == 0, "wrong resultcount");
            query = em.createQuery("select e.firstname from Employee e, in (e.reviews) r");
            result = query.getResultList();
            verify(result.size() == 1, "wrong resultcount");
        } finally {
            closeEntityManager(em);
        }
    }

    @Test
    public void testInnerJoin() throws SQLException {
        init();
        JPAEnvironment env = getEnvironment();
        EntityManager em = env.getEntityManager();
        try {
            env.beginTransaction(em);
            em.persist(new Employee(15, "Hans", "M\u00fcller", dep10));
            Employee fred = new Employee(16, "Fred", "Maier", dep10);
            Review fredsReview = new Review(1, Date.valueOf("2006-01-02"), "first");
            em.persist(fredsReview);
            fred.addReview(fredsReview);
            em.persist(fred);
            env.commitTransactionAndClear(em);
            Query query = em.createQuery("select e.firstname from Employee e inner join e.reviews r where r.id = 1");
            List result = query.getResultList();
            verify(result.size() == 1, "wrong resultcount");
            for (Object object : result) {
                verify("Fred".equals(object), "wrong department name");
            }
            query = em.createQuery("select e.firstname from Employee e inner join e.reviews r where r.id = 2");
            result = query.getResultList();
            verify(result.size() == 0, "wrong resultcount");
            query = em.createQuery("select e.firstname from Employee e inner join e.reviews r");
            result = query.getResultList();
            verify(result.size() == 1, "wrong resultcount");
        } finally {
            closeEntityManager(em);
        }
    }

    @Test
    // @TestProperties(unsupportedDatabaseVendors = { DatabaseVendor.SAPDB, DatabaseVendor.OPEN_SQL })
    public void testLeftJoin() throws SQLException {
        init();
        JPAEnvironment env = getEnvironment();
        EntityManager em = env.getEntityManager();
        try {
            env.beginTransaction(em);
            em.persist(new Employee(15, "Hans", "M\u00fcller", dep10));
            Employee fred = new Employee(16, "Fred", "Maier", dep10);
            Review fredsReview = new Review(1, Date.valueOf("2006-01-02"), "first");
            em.persist(fredsReview);
            fred.addReview(fredsReview);
            em.persist(fred);
            env.commitTransactionAndClear(em);
            Query query = em.createQuery("select e from Employee e left join e.reviews r");
            List result = query.getResultList();
            verify(result.size() == 2, "wrong resultcount");
        } finally {
            closeEntityManager(em);
        }
    }

    @Test
    public void testInList() throws SQLException {
        init();
        JPAEnvironment env = getEnvironment();
        EntityManager em = env.getEntityManager();
        try {
            env.beginTransaction(em);
            em.persist(new Employee(15, "Hans", "M\u00fcller", dep10));
            Employee fred = new Employee(16, "Fred", "Maier", dep10);
            Review fredsReview = new Review(1, Date.valueOf("2006-01-02"), "first");
            em.persist(fredsReview);
            fred.addReview(fredsReview);
            em.persist(fred);
            env.commitTransactionAndClear(em);
            Query query = em.createQuery("select e.firstname from Employee e where e.lastname in ('Maier')");
            List result = query.getResultList();
            verify(result.size() == 1, "wrong resultcount");
            for (Object object : result) {
                verify("Fred".equals(object), "wrong department name");
            }
            query = em.createQuery("select e.firstname from Employee e where e.lastname in ('M\u00fcller', 'V\u00f6ller')");
            result = query.getResultList();
            Assert.assertEquals("wrong resultcount", 1, result.size(), 0);
        } finally {
            closeEntityManager(em);
        }
    }

    private static void persistAll(EntityManager em, Object... objects) {
        for (Object object : objects) {
            em.persist(object);
        }
    }

    @Test
    @ToBeInvestigated
    public void testEdmScenario() throws SQLException {
        clearAllTables();
        JPAEnvironment env = getEnvironment();
        EntityManager em = env.getEntityManager();
        try {
            env.beginTransaction(em);
            Employee hugo = new Employee(1, "Hugo", null, null);
            Employee emil = new Employee(2, "Emil", null, null);
            Employee paul = new Employee(3, "Paul", null, null);
            Employee knut = new Employee(4, "Knut", null, null);
            Project a = new Project("A");
            Project b = new Project("B");
            Project c = new Project("C");
            Project d = new Project("D");
            Bicycle bike1 = new Bicycle();
            Bicycle bike2 = new Bicycle();
            Bicycle bike3 = new Bicycle();
            Bicycle bike4 = new Bicycle();
            persistAll(em, hugo, emil, paul, knut, a, b, c, d, bike1, bike2, bike3, bike4);
            a.addEmployee(hugo);
            b.addEmployee(hugo);
            b.addEmployee(emil);
            c.addEmployee(emil);
            emil.addBicycle(bike1);
            paul.addBicycle(bike1);
            paul.addBicycle(bike2);
            knut.addBicycle(bike2);
            hugo.addBicycle(bike3);
            env.commitTransactionAndClear(em);
            // with subquery
            Query query = em.createQuery("select distinct b from Bicycle b join b.riders r "
                    + "where r.id in (select e.id from Project p join p.employees e where p.id = :id)");
            query.setParameter("id", a.getId()); // only hugo
            List result = query.getResultList();
            verify(result.size() == 1, "wrong size: " + result.size());
            Bicycle bike = (Bicycle) result.get(0);
            verify(bike.getId().equals(bike3.getId()), "bike has wrong id: " + bike.getId() + " expected bike with id "
                    + bike3.getId());
            query.setParameter("id", b.getId()); // hugo and emil
            result = query.getResultList();
            verify(result.size() == 2, "wrong size: " + result.size());
            for (Object possiblyBicycle : result) {
                final Bicycle bicycle = (Bicycle) possiblyBicycle;
                verify(bicycle.getId().equals(bike1.getId()) || bicycle.getId().equals(bike3.getId()), "unexpected id: "
                        + bicycle.getId() + "; exptected bike with id " + bike1.getId() + " or " + bike3.getId());
            }
            // with member of
            query = em.createQuery("select distinct b from Bicycle b join b.riders r, Project p "
                    + "where r member of p.employees and p.id = :id");
            query.setParameter("id", a.getId()); // only hugo
            result = query.getResultList();
            verify(result.size() == 1, "wrong size: " + result.size());
            bike = (Bicycle) result.get(0);
            verify(bike.getId().equals(bike3.getId()), "bike has wrong id: " + bike.getId() + "; expected bike with id "
                    + bike3.getId());
            query.setParameter("id", b.getId()); // hugo and emil
            result = query.getResultList();
            verify(result.size() == 2, "wrong size: " + result.size());
            for (Object possiblyBicycle : result) {
                final Bicycle bicycle = (Bicycle) possiblyBicycle;
                verify(bicycle.getId().equals(bike1.getId()) || bicycle.getId().equals(bike3.getId()), "unexpecetd id: "
                        + bicycle.getId() + "; expected bike with id " + bike3.getId() + " or " + bike1.getId());
            }
        } finally {
            closeEntityManager(em);
        }
    }
}