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

/*
 * Copyright (c) 2005, 2021 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.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.platform.database.SQLServerPlatform;
import org.eclipse.persistence.testing.framework.wdf.JPAEnvironment;
import org.eclipse.persistence.testing.framework.wdf.Skip;
import org.eclipse.persistence.testing.models.wdf.jpa1.employee.Cubicle;
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.tests.wdf.jpa1.JPA1Base;
import org.junit.Test;

@SuppressWarnings("unchecked")
public class TestNativeQuery 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);
            Employee knut = new Employee(1, "Knut", "Maier", dep10);
            Employee fred = new Employee(2, "Fred", "Schmidt", null);
            Cubicle green = new Cubicle(1, 2, "green", knut);
            knut.setCubicle(green);
            em.persist(dep10);
            em.persist(dep20);
            em.persist(green);
            em.persist(knut);
            em.persist(fred);
            env.commitTransactionAndClear(em);
        } finally {
            closeEntityManager(em);
        }
    }

    @Test
    public void testDefaultMapping() throws SQLException {
        init();
        EntityManager em = getEnvironment().getEntityManager();
        try {
            getEnvironment().beginTransaction(em);
            Query query = em.createNativeQuery("select * from TMP_DEP D where D.ID = 10", Department.class);
            List result = query.getResultList();
            Iterator iter = result.iterator();
            verify(iter.hasNext(), "no results");
            Object object = iter.next();
            verify(object instanceof Department, "wrong instance: " + object.getClass().getName());
            verify(em.contains(object), "object not contained in em");
            Department department = (Department) object;
            verify(department.getId() == 10, "wrong id: " + department.getId());
            verify(!iter.hasNext(), "too many rows");
            getEnvironment().rollbackTransaction(em);
        } finally {
            closeEntityManager(em);
        }
    }

    @Test
    public void testDefaultMappingWithMappingResult() throws SQLException {
        init();
        EntityManager em = getEnvironment().getEntityManager();
        try {
            getEnvironment().beginTransaction(em);
            Query query = em.createNativeQuery("select * from TMP_DEP D where D.ID = 10", "departmentByClass");
            List result = query.getResultList();
            Iterator iter = result.iterator();
            verify(iter.hasNext(), "no results");
            Object object = iter.next();
            verify(object instanceof Department, "wrong instance: " + object.getClass().getName());
            verify(em.contains(object), "object not contained in em");
            Department department = (Department) object;
            verify(department.getId() == 10, "wrong id: " + department.getId());
            verify(!iter.hasNext(), "too many rows");
            getEnvironment().rollbackTransaction(em);
        } finally {
            closeEntityManager(em);
        }
    }

    @Test
    public void testDefaultMappingNamedNativeQuery() throws SQLException {
        init();
        EntityManager em = getEnvironment().getEntityManager();
        try {
            getEnvironment().beginTransaction(em);
            Query query = em.createNamedQuery("getDepartmentWithId10SQL_class");
            List result = query.getResultList();
            Iterator iter = result.iterator();
            verify(iter.hasNext(), "no results");
            Object object = iter.next();
            verify(object instanceof Department, "wrong instance: " + object.getClass().getName());
            verify(em.contains(object), "object not contained in em");
            Department department = (Department) object;
            verify(department.getId() == 10, "wrong id: " + department.getId());
            verify(!iter.hasNext(), "too many rows");
            getEnvironment().rollbackTransaction(em);
        } finally {
            closeEntityManager(em);
        }
    }

    @Test
    public void testResultSetMappingNamedNativeQuery() throws SQLException {
        init();
        EntityManager em = getEnvironment().getEntityManager();
        try {
            getEnvironment().beginTransaction(em);
            Query query = em.createNamedQuery("getDepartmentWithId10SQL_mapping");
            List result = query.getResultList();
            Iterator iter = result.iterator();
            verify(iter.hasNext(), "no results");
            Object object = iter.next();
            verify(object instanceof Department, "wrong instance: " + object.getClass().getName());
            verify(em.contains(object), "object not contained in em");
            Department department = (Department) object;
            verify(department.getId() == 10, "wrong id: " + department.getId());
            verify(!iter.hasNext(), "too many rows");
            getEnvironment().rollbackTransaction(em);
        } finally {
            closeEntityManager(em);
        }
    }

    @Skip(databases=SQLServerPlatform.class)
    @Test
    public void testColumnResult() throws SQLException {
        init();
        EntityManager em = getEnvironment().getEntityManager();
        try {
            getEnvironment().beginTransaction(em);
            Query query = em.createNamedQuery("getDepartmentName");
            List result = query.getResultList();
            Iterator iter = result.iterator();
            verify(iter.hasNext(), "no results");
            Object object = iter.next();
            verify(object != null, "object is null");
            verify("ten".equals(object), "wrong object: " + object);
            verify(!iter.hasNext(), "too many rows");
            getEnvironment().rollbackTransaction(em);
        } finally {
            closeEntityManager(em);
        }
    }

    @Skip(databases=SQLServerPlatform.class)
    @Test
    public void testFieldResult() throws SQLException {
        init();
        EntityManager em = getEnvironment().getEntityManager();
        try {
            getEnvironment().beginTransaction(em);
            Query query = em.createNamedQuery("getDepartmentFieldByField");
            List result = query.getResultList();
            Iterator iter = result.iterator();
            verify(iter.hasNext(), "no results");
            Object object = iter.next();
            verify(object instanceof Department, "wrong instance: " + object.getClass().getName());
            verify(em.contains(object), "object not contained in em");
            Department department = (Department) object;
            verify(department.getId() == 10, "wrong id: " + department.getId());
            verify(!iter.hasNext(), "too many rows");
            getEnvironment().rollbackTransaction(em);
        } finally {
            closeEntityManager(em);
        }
    }

    @Test
    public void testDefaultMappingWithParameter() throws SQLException {
        init();
        EntityManager em = getEnvironment().getEntityManager();
        try {
            getEnvironment().beginTransaction(em);
            Query query = em.createNativeQuery("select * from TMP_DEP D where D.ID = ?", Department.class);
            query.setParameter(1, 10);
            List result = query.getResultList();
            Iterator iter = result.iterator();
            verify(iter.hasNext(), "no results");
            Object object = iter.next();
            verify(object instanceof Department, "wrong instance: " + object.getClass().getName());
            verify(em.contains(object), "object not contained in em");
            Department department = (Department) object;
            verify(department.getId() == 10, "wrong id: " + department.getId());
            verify(!iter.hasNext(), "too many rows");
            getEnvironment().rollbackTransaction(em);
        } finally {
            closeEntityManager(em);
        }
    }

    @Skip(databases=SQLServerPlatform.class)
    @Test
    public void testFieldByField() throws SQLException {
        clearAllTables();
        EntityManager em = getEnvironment().getEntityManager();
        try {
            getEnvironment().beginTransaction(em);
            Department dep1 = new Department(1, "one");
            Department dep2 = new Department(2, "two");
            em.persist(dep1);
            em.persist(dep2);
            getEnvironment().commitTransactionAndClear(em);
            Query query = em.createNamedQuery("getDepartmentFieldByField1");
            List result = query.getResultList();
            Iterator iter = result.iterator();
            verify(iter.hasNext(), "no results");
            Object object = iter.next();
            verify(object instanceof Object[], "wrong instance: " + object.getClass().getName());
            Object[] objects = (Object[]) object;
            verify(objects.length == 2, "wrong length: " + objects.length);
            verify(((Number) objects[0]).intValue() == 1, "wrong id: " + ((Number) objects[0]).intValue());
            verify(!iter.hasNext(), "too many rows");
        } finally {
            closeEntityManager(em);
        }
    }
}