use of org.eclipse.persistence.testing.models.jpa.advanced.Project in project eclipselink by eclipse-ee4j.
the class AdvancedCriteriaQueryTestSuite method testSubqueryExistsNested.
public void testSubqueryExistsNested() {
EntityManager em = createEntityManager();
List<Employee> jpqlEmployees;
List<Employee> criteriaEmployees;
beginTransaction(em);
try {
jpqlEmployees = em.createQuery("SELECT e FROM Employee e join e.projects ep WHERE EXISTS (SELECT p FROM Project p WHERE ep = p AND EXISTS (SELECT t FROM Employee t WHERE p.teamLeader = t))").getResultList();
em.clear();
CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Employee> mainQuery = builder.createQuery(Employee.class);
Subquery<Object> subQuery1 = mainQuery.subquery(Object.class);
Subquery<Object> subQuery2 = subQuery1.subquery(Object.class);
Root<Employee> mainEmployee = mainQuery.from(Employee.class);
mainQuery.select(mainEmployee);
Root<Project> sub1Project = subQuery1.from(Project.class);
Join<Employee, Project> mainEmployeeProjects = mainEmployee.join("projects");
Root<Employee> sub2Employee = subQuery2.from(Employee.class);
Join<Employee, Employee> sub1ProjectTeamLeader = sub1Project.join("teamLeader");
subQuery2.where(builder.equal(sub2Employee, sub1ProjectTeamLeader));
subQuery1.where(builder.and(builder.exists(subQuery2), builder.equal(sub1Project, mainEmployeeProjects)));
mainQuery.where(builder.exists(subQuery1));
TypedQuery<Employee> tquery = em.createQuery(mainQuery);
criteriaEmployees = tquery.getResultList();
} finally {
rollbackTransaction(em);
closeEntityManager(em);
}
compareIds(jpqlEmployees, criteriaEmployees);
for (Employee emp : criteriaEmployees) {
assertTrue("Found someone without projects", !emp.getProjects().isEmpty());
boolean atLeastOneProjectHasLeader = false;
for (Project proj : emp.getProjects()) {
if (proj.getTeamLeader() != null) {
atLeastOneProjectHasLeader = true;
break;
}
}
assertTrue("None of employee's projects has a leader", atLeastOneProjectHasLeader);
}
}
use of org.eclipse.persistence.testing.models.jpa.advanced.Project in project eclipselink by eclipse-ee4j.
the class AdvancedCriteriaQueryTestSuite method testJoinDistinct.
public void testJoinDistinct() {
EntityManager em = createEntityManager();
beginTransaction(em);
try {
CriteriaBuilder qbuilder = em.getCriteriaBuilder();
CriteriaQuery<Employee> cquery = qbuilder.createQuery(Employee.class);
Root<Employee> customer = cquery.from(Employee.class);
Fetch<Employee, Project> o = customer.fetch("phoneNumbers", JoinType.LEFT);
cquery.where(customer.get("address").get("city").in("Ottawa", "Halifax"));
cquery.select(customer).distinct(true);
TypedQuery<Employee> tquery = em.createQuery(cquery);
if (usesSOP() && getServerSession().getPlatform().isOracle()) {
// distinct is incompatible with blob in selection clause on Oracle
tquery.setHint(QueryHints.SERIALIZED_OBJECT, "false");
}
List<Employee> result = tquery.getResultList();
assertFalse("No results found", result.isEmpty());
Long count = (Long) em.createQuery("Select count(e) from Employee e where e.address.city in ('Ottawa', 'Halifax')").getSingleResult();
assertTrue("Incorrect number of results returned", result.size() == count);
} finally {
rollbackTransaction(em);
closeEntityManager(em);
}
}
use of org.eclipse.persistence.testing.models.jpa.advanced.Project in project eclipselink by eclipse-ee4j.
the class AdvancedCriteriaQueryTestSuite method testSubqueryExistsNestedUnusedRoot.
public void testSubqueryExistsNestedUnusedRoot() {
EntityManager em = createEntityManager();
List<Employee> jpqlEmployees;
List<Employee> criteriaEmployees;
beginTransaction(em);
try {
jpqlEmployees = em.createQuery("SELECT e FROM Employee e join e.projects ep WHERE EXISTS (SELECT p FROM Project p WHERE ep = p AND EXISTS (SELECT t FROM Employee t WHERE p.teamLeader = t))").getResultList();
em.clear();
CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Employee> mainQuery = builder.createQuery(Employee.class);
Subquery<Object> subQuery1 = mainQuery.subquery(Object.class);
Subquery<Object> subQuery2 = subQuery1.subquery(Object.class);
// Add an unused Root
mainQuery.from(Dealer.class);
Root<Employee> mainEmployee = mainQuery.from(Employee.class);
// Add another unused Root
mainQuery.from(Address.class);
mainQuery.select(mainEmployee);
Root<Project> sub1Project = subQuery1.from(Project.class);
Join<Employee, Project> mainEmployeeProjects = mainEmployee.join("projects");
Root<Employee> sub2Employee = subQuery2.from(Employee.class);
Join<Employee, Employee> sub1ProjectTeamLeader = sub1Project.join("teamLeader");
subQuery2.where(builder.equal(sub2Employee, sub1ProjectTeamLeader));
subQuery1.where(builder.and(builder.exists(subQuery2), builder.equal(sub1Project, mainEmployeeProjects)));
mainQuery.where(builder.exists(subQuery1));
TypedQuery<Employee> tquery = em.createQuery(mainQuery);
criteriaEmployees = tquery.getResultList();
} finally {
rollbackTransaction(em);
closeEntityManager(em);
}
compareIds(jpqlEmployees, criteriaEmployees);
for (Employee emp : criteriaEmployees) {
assertTrue("Found someone without projects", !emp.getProjects().isEmpty());
boolean atLeastOneProjectHasLeader = false;
for (Project proj : emp.getProjects()) {
if (proj.getTeamLeader() != null) {
atLeastOneProjectHasLeader = true;
break;
}
}
assertTrue("None of employee's projects has a leader", atLeastOneProjectHasLeader);
}
}
use of org.eclipse.persistence.testing.models.jpa.advanced.Project in project eclipselink by eclipse-ee4j.
the class AdvancedCriteriaQueryTestSuite method testSubqueryExists.
public void testSubqueryExists() {
EntityManager em = createEntityManager();
List<Employee> jpqlEmployees;
List<Employee> criteriaEmployees;
beginTransaction(em);
try {
jpqlEmployees = em.createQuery("SELECT e FROM Employee e WHERE EXISTS (SELECT p FROM e.projects p)").getResultList();
em.clear();
CriteriaBuilder qbuilder = em.getCriteriaBuilder();
CriteriaQuery<Employee> cquery = qbuilder.createQuery(Employee.class);
Root<Employee> customer = cquery.from(Employee.class);
// create correlated subquery
Subquery<Project> sq = cquery.subquery(Project.class);
Root<Employee> sqc = sq.correlate(customer);
Path<Project> sqo = sqc.join("projects");
sq.select(sqo);
cquery.where(qbuilder.exists(sq));
TypedQuery<Employee> tquery = em.createQuery(cquery);
criteriaEmployees = tquery.getResultList();
} finally {
rollbackTransaction(em);
closeEntityManager(em);
}
compareIds(jpqlEmployees, criteriaEmployees);
for (Employee emp : criteriaEmployees) {
assertTrue("Found someone without projects", !emp.getProjects().isEmpty());
}
}
use of org.eclipse.persistence.testing.models.jpa.advanced.Project in project eclipselink by eclipse-ee4j.
the class JUnitJPQLComplexTestSuite method complexExistsTest.
public void complexExistsTest() {
EntityManager em = createEntityManager();
Collection allEmps = getServerSession().readAllObjects(Employee.class);
List expectedResult = new ArrayList();
// find an employees with projects
for (Iterator i = allEmps.iterator(); i.hasNext(); ) {
Employee e = (Employee) i.next();
Collection<Project> projects = e.getProjects();
if ((projects != null) && (projects.size() > 0)) {
expectedResult.add(e.getId());
}
}
String ejbqlString = "SELECT e.id FROM Employee e WHERE EXISTS (SELECT p FROM e.projects p)";
List result = em.createQuery(ejbqlString).getResultList();
Assert.assertTrue("Complex Not Exists test failed", comparer.compareObjects(result, expectedResult));
}
Aggregations