| /* |
| * 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.Iterator; |
| import java.util.List; |
| |
| import jakarta.persistence.EntityManager; |
| import jakarta.persistence.Query; |
| |
| import org.eclipse.persistence.testing.framework.wdf.JPAEnvironment; |
| import org.eclipse.persistence.testing.models.wdf.jpa1.employee.BrokerageAccount; |
| import org.eclipse.persistence.testing.models.wdf.jpa1.employee.CheckingAccount; |
| import org.eclipse.persistence.testing.models.wdf.jpa1.employee.CreditCardAccount; |
| 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.SavingsAccount; |
| import org.eclipse.persistence.testing.tests.wdf.jpa1.JPA1Base; |
| import org.junit.Test; |
| |
| @SuppressWarnings("unchecked") |
| public class TestJoinedInheritance extends JPA1Base { |
| private final Department dep10 = new Department(10, "ten"); |
| private final Department dep20 = new Department(20, "twenty"); |
| |
| private void init() throws SQLException { |
| clearAllTables(); |
| JPAEnvironment env = getEnvironment(); |
| EntityManager em = env.getEntityManager(); |
| try { |
| // begin transaction |
| env.beginTransaction(em); |
| // persist predefined departments |
| em.persist(dep10); |
| em.persist(dep20); |
| // create 4 Employees: 1 and 2 with dep 10, 3 and 4 with dep 20 |
| Employee[] e = new Employee[4]; |
| e[0] = new Employee(1, "Albert", "Acker", dep10); |
| e[1] = new Employee(2, "Berta", "Baier", dep10); |
| e[2] = new Employee(3, "Christa", "Clemens", dep20); |
| e[3] = new Employee(4, "Dieter", "Deutsch", dep20); |
| // create and persist 4 SavingAccounts used as sample accounts; |
| SavingsAccount[] sa = new SavingsAccount[4]; |
| for (int i = 0; i < sa.length; i++) { |
| sa[i] = new SavingsAccount(); |
| sa[i].setNumber(100 + (i + 1)); |
| sa[i].setOwner("none"); |
| sa[i].setBalance(10 + i); |
| sa[i].setBankId("12345678"); |
| sa[i].setBankName("Volksbank"); |
| sa[i].setInterestRate(20 + 1); |
| em.persist(sa[i]); |
| } |
| // each employee owns one savings account as sample account |
| for (int i = 0; i < sa.length; i++) { |
| e[i].setSampleAccount(sa[i]); |
| } |
| // create and persist 2 BrokerageAccounts |
| BrokerageAccount[] ba = new BrokerageAccount[2]; |
| for (int i = 0; i < ba.length; i++) { |
| ba[i] = new BrokerageAccount(); |
| ba[i].setNumber(200 + (i + 1)); |
| ba[i].setOwner("none"); |
| ba[i].setBalance(10 + i); |
| ba[i].setRiskLevel("super-risky"); |
| em.persist(ba[i]); |
| } |
| // each employee from department 10 owns one brokerage account |
| for (int i = 0; i < ba.length; i++) { |
| e[i].setBrokerageAccount(ba[i]); |
| } |
| // create and persist 4 CreditCardAccounts |
| CreditCardAccount[] cca = new CreditCardAccount[4]; |
| for (int i = 0; i < cca.length; i++) { |
| cca[i] = new CreditCardAccount(); |
| cca[i].setNumber(300 + (i + 1)); |
| cca[i].setOwner("none"); |
| cca[i].setBalance(10 + i); |
| cca[i].setCardNumber(1000000 + i); |
| cca[i].setCardType("VISA"); |
| cca[i].setValidTo(Date.valueOf("2010-01-01")); |
| em.persist(cca[i]); |
| } |
| // each employee from department 20 owns two credit card accounts |
| e[2].addCreditCardAccount(cca[0]); |
| e[2].addCreditCardAccount(cca[1]); |
| e[3].addCreditCardAccount(cca[2]); |
| e[3].addCreditCardAccount(cca[3]); |
| // create and persist one checking account owned by employee 1 |
| CheckingAccount ca = new CheckingAccount(); |
| ca.setNumber(401); |
| ca.setOwner("none"); |
| ca.setBalance(99); |
| ca.setBankId("98765432"); |
| ca.setBankName("Sparkasse"); |
| ca.setCreditLimit(-5000); |
| ca.setClient(e[0]); |
| em.persist(ca); |
| // persist all employees |
| for (int i = 0; i < e.length; i++) { |
| em.persist(e[i]); |
| } |
| // the following data is now available |
| // |
| // e dep sa cac bac cca |
| // --------------------------- |
| // 1 10 sa1 ca1 ba1 --- |
| // 2 10 sa2 --- ba2 --- |
| // 3 20 sa3 --- --- cca1, cca2 |
| // 4 20 sa4 --- --- cca3, cca4 |
| // commit |
| env.commitTransactionAndClear(em); |
| } finally { |
| closeEntityManager(em); |
| } |
| } |
| |
| @Test |
| public void testSelectEmployee() throws SQLException { |
| init(); |
| JPAEnvironment env = getEnvironment(); |
| EntityManager em = env.getEntityManager(); |
| try { |
| Query query = em.createQuery("select e from Employee e where e.id = 1"); |
| List result = query.getResultList(); |
| verify(result.size() == 1, "wrong resultcount " + result.size() + ", 1 is expected"); |
| Iterator iter = result.iterator(); |
| Object obj = iter.next(); |
| verify(obj instanceof Employee, "unexpected object, Employee is expected"); |
| Employee emp = (Employee) obj; |
| verify(emp.getFirstName().equals("Albert"), "wrong first name " + emp.getFirstName() + ", Albert is expected"); |
| } finally { |
| closeEntityManager(em); |
| } |
| } |
| |
| @Test |
| public void testSelectSampleAccounts() throws SQLException { |
| init(); |
| JPAEnvironment env = getEnvironment(); |
| EntityManager em = env.getEntityManager(); |
| try { |
| Query query = em |
| .createQuery("select e.id, e.sampleAccount.number from Employee e where e.id > 0 order by e.id ASC"); |
| List result = query.getResultList(); |
| verify(result.size() == 4, "wrong resultcount " + result.size() + ", 4 is expected"); |
| Iterator iter = result.iterator(); |
| int i = 1; |
| while (iter.hasNext()) { |
| Object obj = iter.next(); |
| verify(obj instanceof Object[], "result is no object array"); |
| Object[] array = (Object[]) obj; |
| verify(array.length == 2, "wrong length " + array.length + ", 2 is expected"); |
| verify(array[1] instanceof Long, "second select item is no Long (wrong class " + array[1].getClass() + ")"); |
| Long accountId = (Long) array[1]; |
| long expectedId = 100 + i; |
| verify(accountId.equals(expectedId), "wrong id of sampleAccount " + accountId + ", " + expectedId |
| + " is expected"); |
| i++; |
| } |
| } finally { |
| closeEntityManager(em); |
| } |
| } |
| |
| @Test |
| public void testSelectCheckingAccount() throws SQLException { |
| init(); |
| JPAEnvironment env = getEnvironment(); |
| EntityManager em = env.getEntityManager(); |
| try { |
| Query query = em.createQuery("select e.checkingAccount.owner from Employee e where e.id = 1"); |
| List result = query.getResultList(); |
| verify(result.size() == 1, "wrong resultcount " + result.size() + ", 1 is expected"); |
| Iterator iter = result.iterator(); |
| Object obj = iter.next(); |
| verify(obj instanceof String, "select item is no String (wrong class " + obj.getClass() + ")"); |
| String owner = (String) obj; |
| verify(owner.equals("none"), "wrong owner " + owner + ", >>none<< is expected"); |
| } finally { |
| closeEntityManager(em); |
| } |
| } |
| |
| @Test |
| public void testSelectBrokerageAccounts() throws SQLException { |
| init(); |
| JPAEnvironment env = getEnvironment(); |
| EntityManager em = env.getEntityManager(); |
| try { |
| Query query = em |
| .createQuery("select e.id, e.brokerageAccount.number from Employee e where e.id > 0 order by e.id ASC"); |
| List result = query.getResultList(); |
| verify(result.size() == 2, "wrong resultcount " + result.size() + ", 2 is expected"); |
| Iterator iter = result.iterator(); |
| int i = 1; |
| while (iter.hasNext()) { |
| Object obj = iter.next(); |
| verify(obj instanceof Object[], "result is no object array"); |
| Object[] array = (Object[]) obj; |
| verify(array.length == 2, "wrong length " + array.length + ", 2 is expected"); |
| verify(array[1] instanceof Long, "second select item is no Long (wrong class " + array[1].getClass() + ")"); |
| Long accountId = (Long) array[1]; |
| long expectedId = 200 + i; |
| verify(accountId.equals(expectedId), "wrong id of brokerageAccount " + accountId + ", " + expectedId |
| + " is expected"); |
| i++; |
| } |
| } finally { |
| closeEntityManager(em); |
| } |
| } |
| |
| @Test |
| public void testSelectCreditCardAccounts() throws SQLException { |
| init(); |
| JPAEnvironment env = getEnvironment(); |
| EntityManager em = env.getEntityManager(); |
| try { |
| Query query = em |
| .createQuery("select cc.number from Employee e join e.creditCardAccounts cc where e.id = 3 order by cc.number"); |
| List result = query.getResultList(); |
| verify(result.size() == 2, "wrong resultcount " + result.size() + ", 2 is expected"); |
| Iterator iter = result.iterator(); |
| int i = 1; |
| while (iter.hasNext()) { |
| Object obj = iter.next(); |
| verify(obj instanceof Long, "select item is no Long (wrong class " + obj.getClass() + ")"); |
| Long accountId = (Long) obj; |
| long expectedId = 300 + i; |
| verify(accountId.equals(expectedId), "wrong id of creditCardAccount " + accountId + ", " + expectedId |
| + " is expected"); |
| i++; |
| } |
| } finally { |
| closeEntityManager(em); |
| } |
| } |
| |
| @Test |
| public void testSelectEmployeeJoinCheckingAccount() throws SQLException { |
| init(); |
| JPAEnvironment env = getEnvironment(); |
| EntityManager em = env.getEntityManager(); |
| try { |
| // "e" could be used instead of "c.client" |
| Query query = em |
| .createQuery("select e.id from Employee e join e.checkingAccount c where c.balance = 99 and c.client.brokerageAccount is not null"); |
| List result = query.getResultList(); |
| verify(result.size() == 1, "wrong resultcount " + result.size() + ", 1 is expected"); |
| Iterator iter = result.iterator(); |
| Object obj = iter.next(); |
| verify(obj instanceof Integer, "select item is no Integer (wrong class " + obj.getClass() + ")"); |
| int id = ((Integer) obj).intValue(); |
| verify(id == 1, "wrong employee id " + id + ", 1 is expected"); |
| query = em |
| .createQuery("select distinct e.id from Employee e join e.checkingAccount c where c.balance = 99 and c.client.creditCardAccounts is not empty"); |
| result = query.getResultList(); |
| verify(result.size() == 0, "wrong resultcount " + result.size() + ", 0 is expected"); |
| } finally { |
| closeEntityManager(em); |
| } |
| } |
| } |