/*******************************************************************************
* 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 v1.0 and Eclipse Distribution License v. 1.0
* which accompanies this distribution.
* The Eclipse Public License is available at http://www.eclipse.org/legal/epl-v10.html
* and the Eclipse Distribution License is available at
* http://www.eclipse.org/org/documents/edl-v10.php.
*
* 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 javax.persistence.EntityManager;
import javax.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);
}
}
}