| /* |
| * 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.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, Integer.valueOf(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, Integer.valueOf(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, Integer.valueOf(1)); |
| query.executeUpdate(); |
| getEnvironment().commitTransactionAndClear(em); |
| Department found = em.find(Department.class, Integer.valueOf(1)); |
| verify("emil".equals(found.getName()), "wrong name: " + found.getName()); |
| } finally { |
| closeEntityManager(em); |
| } |
| } |
| } |