| /* |
| * Copyright (c) 2011, 2020 Oracle and/or its affiliates. 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: |
| // Oracle - initial API and implementation |
| // |
| package org.eclipse.persistence.jpa.tests.jpql.tools.model; |
| |
| /** |
| * The abstract definition |
| * |
| * @version 2.4 |
| * @since 2.4 |
| * @author Pascal Filion |
| */ |
| @SuppressWarnings("nls") |
| public abstract class AbstractStateObjectTest2_0 extends AbstractStateObjectTest { |
| |
| public static StateObjectTester stateObject_014() { |
| |
| // SELECT ENTRY(addr) FROM Alias a JOIN a.addresses addr |
| |
| return selectStatement( |
| select(entry("addr")), |
| from("Alias", "a", join("a.addresses", "addr")) |
| ); |
| } |
| |
| public static StateObjectTester stateObject_139() { |
| |
| // SELECT p |
| // FROM Employee e JOIN e.projects p |
| // WHERE e.id = :id AND INDEX(p) = 1 |
| |
| return selectStatement( |
| select(variable("p")), |
| from("Employee", "e", join("e.projects", "p")), |
| where(path("e.id").equal(inputParameter(":id")).and(index("p").equal(numeric(1)))) |
| ); |
| } |
| |
| public static StateObjectTester stateObject_205() throws Exception { |
| |
| // UPDATE Employee e |
| // SET e.salary = |
| // CASE WHEN e.rating = 1 THEN e.salary * 1.1 |
| // WHEN e.rating = 2 THEN e.salary * 1.05 |
| // ELSE e.salary * 1.01 |
| // END |
| |
| return updateStatement( |
| update("Employee", "e", set( |
| path("e.salary"), |
| case_( |
| when(path("e.rating").equal(numeric(1)), |
| path("e.salary").multiplication(numeric(1.1)) |
| ), |
| when(path("e.rating").equal(numeric(2)), |
| path("e.salary").multiplication(numeric(1.05)) |
| ), |
| path("e.salary").multiplication(numeric(1.01)) |
| ) |
| )) |
| ); |
| } |
| |
| public static StateObjectTester stateObject_206() throws Exception { |
| |
| // SELECT e.name, |
| // CASE TYPE(e) WHEN Exempt THEN 'Exempt' |
| // WHEN Contractor THEN 'Contractor' |
| // WHEN Intern THEN 'Intern' |
| // ELSE 'NonExempt' |
| // END |
| // FROM Employee e |
| // WHERE e.dept.name = 'Engineering' |
| |
| return selectStatement( |
| select( |
| path("e.name"), |
| case_( |
| type("e"), |
| new StateObjectTester[] { |
| when(entity("Exempt"), string("'Exempt'")), |
| when(entity("Contractor"), string("'Contractor'")), |
| when(entity("Intern"), string("'Intern'")) |
| }, |
| string("'NonExempt'") |
| ) |
| ), |
| from("Employee", "e", "Contractor", "c"), |
| where(path("e.dept.name").equal(string("'Engineering'"))) |
| ); |
| } |
| |
| public static StateObjectTester stateObject_207() throws Exception { |
| |
| // SELECT e.name, |
| // f.name, |
| // CONCAT(CASE WHEN f.annualMiles > 50000 THEN 'Platinum ' |
| // WHEN f.annualMiles > 25000 THEN 'Gold ' |
| // ELSE '' |
| // END, |
| // 'Frequent Flyer') |
| // FROM Employee e JOIN e.frequentFlierPlan f |
| |
| return selectStatement( |
| select( |
| path("e.name"), |
| path("f.name"), |
| concat( |
| case_( |
| when(path("f.annualMiles").greaterThan(numeric(50000)), |
| string("'Platinum '")), |
| when(path("f.annualMiles").greaterThan(numeric(25000)), |
| string("'Gold '")), |
| string("''") |
| ), |
| string("'Frequent Flyer'") |
| ) |
| ), |
| from("Employee", "e", join("e.frequentFlierPlan", "f")) |
| ); |
| } |
| |
| public static StateObjectTester stateObject_208() throws Exception { |
| |
| // SELECT e |
| // FROM Employee e |
| // WHERE TYPE(e) IN (Exempt, Contractor) |
| |
| return selectStatement( |
| select(variable("e")), |
| from("Employee", "e"), |
| where(type("e").in(entity("Exempt"), entity("Contractor"))) |
| ); |
| } |
| |
| public static StateObjectTester stateObject_209() { |
| |
| // SELECT e |
| // FROM Employee e |
| // WHERE TYPE(e) IN (:empType1, :empType2) |
| |
| return selectStatement( |
| select(variable("e")), |
| from("Employee", "e"), |
| where(type("e").in(inputParameter(":empType1"), inputParameter(":empType2"))) |
| ); |
| } |
| |
| public static StateObjectTester stateObject_210() { |
| |
| // SELECT e |
| // FROM Employee e |
| // WHERE TYPE(e) IN :empTypes |
| |
| InExpressionStateObjectTester inExpression = in( |
| type("e"), |
| inputParameter(":empTypes") |
| ); |
| |
| return selectStatement( |
| select(variable("e")), |
| from("Employee", "e"), |
| where(inExpression) |
| ); |
| } |
| |
| public static StateObjectTester stateObject_211() { |
| |
| // SELECT TYPE(employee) |
| // FROM Employee employee |
| // WHERE TYPE(employee) <> Exempt |
| |
| return selectStatement( |
| select(type("employee")), |
| from("Employee", "employee"), |
| where(type("employee").different(variable("Exempt"))) |
| ); |
| } |
| |
| public static StateObjectTester stateObject_212() { |
| |
| // SELECT t |
| // FROM CreditCard c JOIN c.transactionHistory t |
| // WHERE c.holder.name = 'John Doe' AND INDEX(t) BETWEEN 0 AND 9 |
| |
| return selectStatement( |
| select(variable("t")), |
| from("CreditCard", "c", join("c.transactionHistory", "t")), |
| where( |
| path("c.holder.name").equal(string("'John Doe'")) |
| .and( |
| index("t").between(numeric(0), numeric(9)))) |
| ); |
| } |
| |
| public static StateObjectTester stateObject_213() { |
| |
| // SELECT w.name |
| // FROM Course c JOIN c.studentWaitlist w |
| // WHERE c.name = 'Calculus' |
| // AND |
| // INDEX(w) = 0 |
| |
| return selectStatement( |
| select(path("w.name")), |
| from("Course", "c", join("c.studentWaitlist", "w")), |
| where( |
| path("c.name").equal(string("'Calculus'")) |
| .and( |
| index("w").equal(numeric(0)))) |
| ); |
| } |
| |
| public static StateObjectTester stateObject_214() throws Exception { |
| |
| // UPDATE Employee e |
| // SET e.salary = CASE e.rating WHEN 1 THEN e.salary * 1.1 |
| // WHEN 2 THEN e.salary * 1.05 |
| // ELSE e.salary * 1.01 |
| // END |
| |
| return updateStatement( |
| update( |
| "Employee", "e", |
| set("e.salary", case_( |
| path("e.rating"), |
| new StateObjectTester[] { |
| when(numeric(1), path("e.salary").multiplication(numeric(1.1))), |
| when(numeric(2), path("e.salary").multiplication(numeric(1.05))), |
| }, |
| path("e.salary").multiplication(numeric(1.01)) |
| )) |
| ) |
| ); |
| } |
| |
| public static StateObjectTester stateObject_217() throws Exception { |
| |
| // SELECT o.quantity, o.cost*1.08 AS taxedCost, a.zipcode |
| // FROM Customer c JOIN c.orders o JOIN c.address a |
| // WHERE a.state = 'CA' AND a.county = 'Santa Clara' |
| // ORDER BY o.quantity, taxedCost, a.zipcode |
| |
| return selectStatement( |
| select( |
| path("o.quantity"), |
| selectItemAs(path("o.cost").multiplication(numeric(1.08)), "taxedCost"), |
| path("a.zipcode") |
| ), |
| from("Customer", "c", join("c.orders", "o"), join("c.address", "a")), |
| where( |
| path("a.state").equal(string("'CA'")) |
| .and( |
| path("a.county").equal(string("'Santa Clara'")) |
| ) |
| ), |
| nullExpression(), |
| nullExpression(), |
| orderBy( |
| orderByItem("o.quantity"), |
| orderByItem(variable("taxedCost")), |
| orderByItem("a.zipcode") |
| ) |
| ); |
| } |
| |
| public static StateObjectTester stateObject_218() throws Exception { |
| |
| // SELECT AVG(o.quantity) as q, a.zipcode |
| // FROM Customer c JOIN c.orders o JOIN c.address a |
| // WHERE a.state = 'CA' |
| // GROUP BY a.zipcode |
| // ORDER BY q DESC"; |
| |
| return selectStatement( |
| select( |
| selectItemAs(avg("o.quantity"), "q"), |
| path("a.zipcode") |
| ), |
| from("Customer", "c", join("c.orders", "o"), join("c.address", "a")), |
| where(path("a.state").equal(string("'CA'"))), |
| groupBy(path("a.zipcode")), |
| nullExpression(), |
| orderBy(orderByItemDesc(variable("q"))) |
| ); |
| } |
| |
| public static StateObjectTester stateObject_222() throws Exception { |
| |
| // SELECT e.salary / 1000D n |
| // From Employee e |
| |
| return selectStatement( |
| select(selectItem(path("e.salary").division(numeric("1000D")), "n")), |
| from("Employee", "e") |
| ); |
| } |
| |
| public static StateObjectTester stateObject_223() throws Exception { |
| |
| // SELECT MOD(a.id, 2) AS m |
| // FROM Address a JOIN FETCH a.customerList |
| // ORDER BY m, a.zipcode |
| |
| return selectStatement( |
| select(selectItemAs(mod(path("a.id"), numeric(2)), "m")), |
| from("Address", "a", joinFetch("a.customerList")), |
| nullExpression(), |
| nullExpression(), |
| nullExpression(), |
| orderBy( |
| orderByItem(variable("m")), |
| orderByItem(path("a.zipcode")) |
| ) |
| ); |
| } |
| } |