jpa/eclipselink.jpa.wdf.test/src/it/java/org/eclipse/persistence/testing/tests/wdf/jpa1/query/TestDeleteQuery.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.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashSet;
import java.util.Set;

import jakarta.persistence.EntityManager;
import jakarta.persistence.Query;
import jakarta.persistence.TransactionRequiredException;

import org.eclipse.persistence.config.PersistenceUnitProperties;
import org.eclipse.persistence.testing.framework.wdf.JPAEnvironment;
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.tests.wdf.jpa1.JPA1Base;
import org.junit.Test;

public class TestDeleteQuery 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 employee = new Employee(1, "Hugo", "Maier", dep10);
            em.persist(dep10);
            em.persist(dep20);
            em.persist(employee);
            env.commitTransactionAndClear(em);
        } finally {
            closeEntityManager(em);
        }
    }

    private void verifyCountOnDatabase(int expected, String table) throws SQLException {
        Connection conn = getEnvironment().getDataSource().getConnection();
        try {
            PreparedStatement stmt = conn.prepareStatement("select COUNT(*) from " + table);
            try {
                ResultSet rs = stmt.executeQuery();
                try {
                    verify(rs.next(), "no count ?!");
                    int count = rs.getInt(1);
                    verify(count == expected, "unexpected count: " + count + " on table >>" + table + "<<, expected: "
                            + expected);
                } finally {
                    rs.close();
                }
            } finally {
                stmt.close();
            }
        } finally {
            conn.close();
        }
    }

    @Test
    public void testDeleteOutsideTx() {
        EntityManager em = getEnvironment().getEntityManager();
        try {
            Query query = em.createQuery("delete from Department d");
            try {
                query.executeUpdate();
                flop("missing TransactionRequiredException");
            } catch (TransactionRequiredException ex) {
            }
        } finally {
            closeEntityManager(em);
        }
    }

    @Test
    public void testDeleteWithSecondaryTable() {

        Project project1 = new Project();
        project1.setName("foo");
        project1.setPlannedDays(1);
        project1.setUsedDays(53);

        Project project2 = new Project();
        project2.setName("bar");
        project2.setPlannedDays(100);
        project2.setUsedDays(410);

        EntityManager em = getEnvironment().getEntityManager();
        try {
            getEnvironment().beginTransaction(em);
            em.persist(project1);
            em.persist(project2);
            em.flush();
            getEnvironment().commitTransaction(em);
            verifyCountOnDatabase(2, "TMP_PROJECT");
            verifyCountOnDatabase(2, "TMP_PROJECT_DETAILS");

            getEnvironment().beginTransaction(em);
            Query deleteQuery = em.createQuery("DELETE FROM Project p WHERE p.id IS NOT NULL");
            deleteQuery.executeUpdate();
            em.flush();
            getEnvironment().commitTransaction(em);

            verifyCountOnDatabase(0, "TMP_PROJECT");
            verifyCountOnDatabase(0, "TMP_PROJECT_DETAILS");
        } catch (SQLException sqlEx) {
            throw new RuntimeException(sqlEx);
        } finally {
            closeEntityManager(em);
        }

    }

    @Test
    public void testDeleteAllDepartments() throws SQLException {
        EntityManager em = getEnvironment().getEntityManager();
        try {
            init();
            getEnvironment().beginTransaction(em);
            // OS/390 doesn't get the update count right (returns -1), if there is no where clause.
            // Workaround is to supply a dummy where clause.
            Query emplyoeeDelete = em.createQuery("delete from Employee e");
            emplyoeeDelete.executeUpdate(); // del emp before dep caused by FK
            Query query = em.createQuery("delete from Department d where d.id is not null");
            int count = query.executeUpdate();
            if (!"JDBC".equals(getEnvironment().getPropertyValue(em, PersistenceUnitProperties.BATCH_WRITING))) {
                verify(count == 2, "wrong update count: " + count);
            }
            getEnvironment().commitTransaction(em);
            verifyCountOnDatabase(0, "TMP_DEP");
            init();
            getEnvironment().beginTransaction(em);
            emplyoeeDelete = em.createQuery("delete from Employee e");
            emplyoeeDelete.executeUpdate();
            query = em.createQuery("delete from Department d where d.id = 10");
            count = query.executeUpdate();
            if (!"JDBC".equals(getEnvironment().getPropertyValue(em, PersistenceUnitProperties.BATCH_WRITING))) {
                verify(count == 1, "wrong update count: " + count);
            }
            getEnvironment().commitTransaction(em);
            verifyCountOnDatabase(1, "TMP_DEP");
        } finally {
            closeEntityManager(em);
        }
    }

    @Test
    public void testDeleteEmployeeWithGivenDepartment() throws SQLException {
        EntityManager em = getEnvironment().getEntityManager();
        try {
            init();
            Department department = em.find(Department.class, 10);
            verifyDeleteEmployeeWithGivenDepartment(em, department);
            em.clear();
            init();
            verifyDeleteEmployeeWithGivenDepartment(em, dep10);
            em.clear();
        } finally {
            closeEntityManager(em);
        }
    }

    private void verifyDeleteEmployeeWithGivenDepartment(EntityManager em, Department dep) {
        getEnvironment().beginTransaction(em);
        Query query = em.createQuery("delete from Employee e where e.department = ?1");
        query.setParameter(1, dep);
        int count = query.executeUpdate();
        verify(count == 1, "wrong update count: " + count);
        getEnvironment().commitTransaction(em);
        getEnvironment().evict(em, Employee.class);
        verify(null == em.find(Employee.class, 1), "employee found");
    }

    @Test
    public void testDeleteAllDepartmentsNative() throws SQLException {
        EntityManager em = getEnvironment().getEntityManager();
        try {
            init();
            getEnvironment().beginTransaction(em);
            // OS/390 doesn't get the update count right (returns -1), if there is no where clause.
            // Workaround is to supply a dummy where clause.
            Query emplyoeeDelete = em.createQuery("delete from Employee e");
            emplyoeeDelete.executeUpdate(); // del emp before dep caused by FK
            Query query = em.createNativeQuery("delete from TMP_DEP where ID is not null");
            int count = query.executeUpdate();

            if (!"JDBC".equals(getEnvironment().getPropertyValue(em, PersistenceUnitProperties.BATCH_WRITING))) {
                verify(count == 2, "wrong update count: " + count);
            }
            getEnvironment().commitTransaction(em);
            verifyCountOnDatabase(0, "TMP_DEP");
            init();
            getEnvironment().beginTransaction(em);
            emplyoeeDelete = em.createQuery("delete from Employee e");
            emplyoeeDelete.executeUpdate();
            query = em.createNativeQuery("delete from TMP_DEP where TMP_DEP.ID = 10");
            count = query.executeUpdate();
            if (!"JDBC".equals(getEnvironment().getPropertyValue(em, PersistenceUnitProperties.BATCH_WRITING))) {
                verify(count == 1, "wrong update count: " + count);
            }
            getEnvironment().commitTransaction(em);
            verifyCountOnDatabase(1, "TMP_DEP");
        } finally {
            closeEntityManager(em);
        }
    }

    @Test
    public void testExecuteUpdateSelect() {
        EntityManager em = getEnvironment().getEntityManager();
        try {
            getEnvironment().beginTransaction(em);
            Query query = em.createQuery("select d from Department d");
            try {
                query.executeUpdate();
                flop("missing IllegalStateException");
            } catch (IllegalStateException ex) {
            } finally {
                getEnvironment().rollbackTransaction(em);
            }
        } finally {
            closeEntityManager(em);
        }
    }

    @Test
    public void testGetResultListDelete() {
        EntityManager em = getEnvironment().getEntityManager();
        try {
            getEnvironment().beginTransaction(em);
            Query query = em.createQuery("delete from Department d");
            try {
                query.getResultList();
                flop("missing IllegalStateException");
            } catch (IllegalStateException ex) {
            } finally {
                getEnvironment().rollbackTransaction(em);
            }
        } finally {
            closeEntityManager(em);
        }
    }

    @Test
    public void testGetSingleResultDelete() {
        EntityManager em = getEnvironment().getEntityManager();
        try {
            getEnvironment().beginTransaction(em);
            Query query = em.createQuery("delete from Department d");
            try {
                query.getResultList();
                flop("missing IllegalStateException");
            } catch (IllegalStateException ex) {
            } finally {
                getEnvironment().rollbackTransaction(em);
            }
        } finally {
            closeEntityManager(em);
        }
    }

    @Test
    public void testUpdateDepartment() throws SQLException {
        clearAllTables();
        EntityManager em = getEnvironment().getEntityManager();
        try {
            getEnvironment().beginTransaction(em);
            Department department = new Department(1, "hugo");
            em.persist(department);
            getEnvironment().commitTransactionAndClear(em);
            getEnvironment().beginTransaction(em);
            Query query = em.createQuery("update Department d set d.name = ?1 where d.id = ?2");
            query.setParameter(1, "emil");
            query.setParameter(2, 1);
            query.executeUpdate();
            getEnvironment().commitTransactionAndClear(em);
            Department found = em.find(Department.class, 1);
            verify("emil".equals(found.getName()), "wrong name: " + found.getName());
        } finally {
            closeEntityManager(em);
        }
    }
}