jpa/eclipselink.jpa.test/src/it/java/org/eclipse/persistence/testing/tests/jpa21/advanced/XMLNamedStoredProcedureQueryTestSuite.java - eclipselink - Git at Google

 /*
  * Copyright (c) 2013, 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:
 //     01/23/2013-2.5 Guy Pelletier
 //       - 350487: JPA 2.1 Specification defined support for Stored Procedure Calls
 package org.eclipse.persistence.testing.tests.jpa21.advanced;

 import java.util.List;

 import jakarta.persistence.EntityManager;
 import jakarta.persistence.ParameterMode;
 import jakarta.persistence.StoredProcedureQuery;

 import junit.framework.TestSuite;
 import junit.framework.Test;

 import org.eclipse.persistence.testing.framework.junit.JUnitTestCase;

 import org.eclipse.persistence.testing.models.jpa21.advanced.xml.Address;
 import org.eclipse.persistence.testing.models.jpa21.advanced.xml.AdvancedTableCreator;
 import org.eclipse.persistence.testing.models.jpa21.advanced.xml.Employee;
 import org.eclipse.persistence.testing.models.jpa21.advanced.xml.EmployeeDetails;
 import org.eclipse.persistence.testing.models.jpa21.advanced.xml.EmployeePopulator;
 import org.eclipse.persistence.testing.models.jpa21.advanced.xml.LargeProject;
 import org.eclipse.persistence.testing.models.jpa21.advanced.xml.Project;
 import org.eclipse.persistence.testing.models.jpa21.advanced.xml.SmallProject;

 public class XMLNamedStoredProcedureQueryTestSuite extends JUnitTestCase {
     public XMLNamedStoredProcedureQueryTestSuite() {}

     public XMLNamedStoredProcedureQueryTestSuite(String name) {
         super(name);
         setPuName("MulitPU-4");
     }

     /**
      * Return the the persistence unit name for this test suite.
      */
     @Override
     public String getPersistenceUnitName() {
         return "MulitPU-4";
     }

     public static Test suite() {
         TestSuite suite = new TestSuite();
         suite.setName("XMLNamedStoredProcedureQueryTestSuite");

         suite.addTest(new XMLNamedStoredProcedureQueryTestSuite("testSetup"));
         suite.addTest(new XMLNamedStoredProcedureQueryTestSuite("testQueryExecuteUpdateOnSelectQueryWithNoResultClass"));
         suite.addTest(new XMLNamedStoredProcedureQueryTestSuite("testQueryExecuteUpdateOnSelectQueryWithResultClass"));
         suite.addTest(new XMLNamedStoredProcedureQueryTestSuite("testQueryExecuteWithNamedCursors"));
         suite.addTest(new XMLNamedStoredProcedureQueryTestSuite("testQueryExecuteWithUnNamedCursor"));
         suite.addTest(new XMLNamedStoredProcedureQueryTestSuite("testQueryGetResultListWithNamedCursors"));
         suite.addTest(new XMLNamedStoredProcedureQueryTestSuite("testQueryWithMultipleResults"));
         suite.addTest(new XMLNamedStoredProcedureQueryTestSuite("testQueryWithNamedColumnResult"));
         suite.addTest(new XMLNamedStoredProcedureQueryTestSuite("testQueryWithNamedFieldResult"));
         suite.addTest(new XMLNamedStoredProcedureQueryTestSuite("testQueryWithNumberedFieldResult"));
         suite.addTest(new XMLNamedStoredProcedureQueryTestSuite("testQueryWithResultClass"));

         return suite;
     }

     /**
      * The setup is done as a test, both to record its failure, and to allow execution in the server.
      */
     public void testSetup() {
         new AdvancedTableCreator().replaceTables(getPersistenceUnitServerSession());
         EmployeePopulator employeePopulator = new EmployeePopulator();
         employeePopulator.buildExamples();
         employeePopulator.persistExample(getPersistenceUnitServerSession());
         clearCache();
     }

     /**
      * Tests an execute update on a named stored procedure that does a select.
      * NamedStoredProcedure defines a result class.
      */
     public void testQueryExecuteUpdateOnSelectQueryWithNoResultClass() {
         if (supportsStoredProcedures() && getPlatform().isMySQL()) {
             EntityManager em = createEntityManager();

             try {
                 beginTransaction(em);
                 em.createNamedStoredProcedureQuery("XMLReadAllAddressesWithNoResultClass").executeUpdate();
                 commitTransaction(em);
             } catch (IllegalStateException e) {
                 if (isTransactionActive(em)){
                     rollbackTransaction(em);
                 }

                 return;
             } finally {
                 closeEntityManager(em);
             }

             fail("Expected Illegal state exception was not thrown.");
         }
     }

     /**
      * Tests an execute update on a named stored procedure that does a select.
      * NamedStoredProcedure defines a result class.
      */
     public void testQueryExecuteUpdateOnSelectQueryWithResultClass() {
         if (supportsStoredProcedures() && getPlatform().isMySQL()) {
             EntityManager em = createEntityManager();

             try {
                 beginTransaction(em);
                 em.createNamedStoredProcedureQuery("XMLReadAddressWithResultClass").setParameter("address_id_v", 1).executeUpdate();
                 commitTransaction(em);
             } catch (IllegalStateException e) {
                 if (isTransactionActive(em)){
                     rollbackTransaction(em);
                 }

                 return;
             } finally {
                 closeEntityManager(em);
             }

             fail("Expected Illegal state exception was not thrown.");
         }
     }

     /**
      * Tests a StoredProcedureQuery using multiple names cursors.
      */
     public void testQueryExecuteWithNamedCursors() {
         if (supportsStoredProcedures() && getPlatform().isOracle() ) {
             EntityManager em = createEntityManager();

             try {
                 StoredProcedureQuery query = em.createNamedStoredProcedureQuery("XMLReadUsingNamedRefCursors");

                 boolean returnVal = query.execute();

                 List<Employee> employees = (List<Employee>) query.getOutputParameterValue("CUR1");
                 assertFalse("No employees were returned", employees.isEmpty());
                 List<Address> addresses = (List<Address>) query.getOutputParameterValue("CUR2");
                 assertFalse("No addresses were returned", addresses.isEmpty());
             } catch (RuntimeException e) {
                 if (isTransactionActive(em)){
                     rollbackTransaction(em);
                 }

                 throw e;
             } finally {
                 closeEntityManager(em);
             }
         }
     }

     /**
      * Tests a StoredProcedureQuery with an unnamed cursor.
      */
     public void testQueryExecuteWithUnNamedCursor() {
         if (supportsStoredProcedures() && getPlatform().isOracle() ) {
             EntityManager em = createEntityManager();

             try {
                 StoredProcedureQuery query = em.createNamedStoredProcedureQuery("XMLReadUsingUnNamedRefCursor");

                 boolean returnVal = query.execute();

                 List<Employee> employees = (List<Employee>) query.getOutputParameterValue(1);
                 assertFalse("No employees were returned", employees.isEmpty());
             } catch (RuntimeException e) {
                 if (isTransactionActive(em)){
                     rollbackTransaction(em);
                 }

                 throw e;
             } finally {
                 closeEntityManager(em);
             }
         }
     }

     /**
      * Tests a StoredProcedureQuery on Oracle using named ref cursors.
      */
     public void testQueryGetResultListWithNamedCursors() {
         if (supportsStoredProcedures() && getPlatform().isOracle() ) {
             EntityManager em = createEntityManager();

             try {
                 StoredProcedureQuery query = em.createNamedStoredProcedureQuery("XMLReadUsingNamedRefCursors");

                 List<Employee> employees = (List<Employee>) query.getResultList();
                 assertFalse("No employees were returned", employees.isEmpty());

                 List<Address> addresses = (List<Address>) query.getResultList();
                 assertFalse("No addresses were returned", addresses.isEmpty());
             } catch (RuntimeException e) {
                 if (isTransactionActive(em)){
                     rollbackTransaction(em);
                 }

                 throw e;
             } finally {
                 closeEntityManager(em);
             }
         }
     }

     /**
      * Test multiple result sets by setting the SQL results set mapping from annotation.
      */
     public void testQueryWithMultipleResults() throws Exception {
         if (supportsStoredProcedures() && getPlatform().isMySQL()) {
             StoredProcedureQuery multipleResultSetQuery = createEntityManager().createNamedStoredProcedureQuery("XMLReadUsingMultipleResultSetMappings");

             // Verify first result set mapping --> Employee
             List results = multipleResultSetQuery.getResultList();
             assertNotNull("No Employee results returned", results);
             assertTrue("Empty Employee results returned", results.size() > 0);

             // Verify second result set mapping --> Address
             assertTrue("Address results not available", multipleResultSetQuery.hasMoreResults());
             results = multipleResultSetQuery.getResultList();
             assertNotNull("No Address results returned", results);
             assertTrue("Empty Address results returned", results.size() > 0);

             // Verify third result set mapping --> Project
             assertTrue("Projects results not available", multipleResultSetQuery.hasMoreResults());
             results = multipleResultSetQuery.getResultList();
             assertNotNull("No Project results returned", results);
             assertTrue("Empty Project results returned", results.size() > 0);

             for (Object result : results) {
                 Object[] resultElement = (Object[]) result;
                 assertTrue("Failed to Return 3 items", (resultElement.length == 3));
                 // Using Number as Different db/drivers  can return different types
                 // e.g. Oracle with ijdbc14.jar returns BigDecimal where as Derby
                 // with derbyclient.jar returns Double. NOTE: the order of checking
                 // here is valid and as defined by the spec.
                 assertTrue("Failed to return LargeProject", (resultElement[0] instanceof LargeProject) );
                 assertTrue("Failed To Return SmallProject", (resultElement[1] instanceof SmallProject) );
                 assertTrue("Failed to return column",(resultElement[2] instanceof Number) );
                 assertFalse("Returned same data in both result elements",((SmallProject)resultElement[1]).getName().equals(((LargeProject)resultElement[0]).getName()));
             }

             // Verify fourth result set mapping --> Employee Constructor Result
             assertTrue("Employee constructor results not available", multipleResultSetQuery.hasMoreResults());
             results = multipleResultSetQuery.getResultList();
             assertNotNull("No Employee constructor results returned", results);
             assertTrue("Empty Employee constructor results returned", results.size() > 0);

             // Verify there as no more results available
             assertFalse("More results available", multipleResultSetQuery.hasMoreResults());
         }
     }

     /**
      * Tests a NamedStoredProcedureQuery annotation using a result-set mapping.
      */
     public void testQueryWithNamedColumnResult() {
         // TODO: investigate if this test should work on Oracle as written.
         if (supportsStoredProcedures() && getPlatform().isMySQL()) {
             EntityManager em = createEntityManager();

             try {
                 beginTransaction(em);

                 Address address = new Address();
                 address.setCity("Ottawa");
                 address.setPostalCode("K1G 6P3");
                 address.setProvince("ON");
                 address.setStreet("123 Street");
                 address.setCountry("Canada");
                 em.persist(address);
                 commitTransaction(em);

                 // Clear the cache
                 em.clear();
                 clearCache();

                 Object[] values = (Object[]) em.createNamedStoredProcedureQuery("XMLReadAddressMappedNamedColumnResult").setParameter("address_id_v", address.getId()).getSingleResult();
                 assertTrue("Address data not found or returned using stored procedure", ((values != null) && (values.length == 6)));
                 assertNotNull("No results returned from store procedure call", values[1]);
                 assertTrue("Address not found using stored procedure", address.getStreet().equals(values[1]));
             } catch (RuntimeException e) {
                 if (isTransactionActive(em)){
                     rollbackTransaction(em);
                 }

                 throw e;
             } finally {
                 closeEntityManager(em);
             }
         }
     }

     /**
      * Tests a NamedStoredProcedureQuery using a result-set mapping.
      */
     public void testQueryWithNamedFieldResult() {
         // TODO: investigate if this test should work on Oracle as written.
         if (supportsStoredProcedures() && getPlatform().isMySQL()) {
             EntityManager em = createEntityManager();

             try {
                 beginTransaction(em);

                 Address address1 = new Address();
                 address1.setCity("Ottawa");
                 address1.setPostalCode("K1G 6P3");
                 address1.setProvince("ON");
                 address1.setStreet("123 Street");
                 address1.setCountry("Canada");

                 em.persist(address1);
                 commitTransaction(em);

                 // Clear the cache
                 em.clear();
                 clearCache();

                 Address address2 = (Address) em.createNamedStoredProcedureQuery("XMLReadAddressMappedNamedFieldResult").setParameter("address_id_v", address1.getId()).getSingleResult();
                 assertNotNull("Address returned from stored procedure is null", address2);
                 assertTrue("Address not found using stored procedure", (address1.getId() == address2.getId()));
             } catch (RuntimeException e) {
                 if (isTransactionActive(em)){
                     rollbackTransaction(em);
                 }

                 throw e;
             } finally {
                 closeEntityManager(em);
             }
         }
     }

     /**
      * Tests a NamedStoredProcedureQuery using positional paramters.
      */
     public void testQueryWithNumberedFieldResult() {
         if (supportsStoredProcedures() && getPlatform().isMySQL()) {
             EntityManager em = createEntityManager();

             try {
                 beginTransaction(em);

                 Address address1 = new Address();
                 address1.setCity("Ottawa");
                 address1.setPostalCode("K1G 6P3");
                 address1.setProvince("ON");
                 address1.setStreet("123 Street");
                 address1.setCountry("Canada");
                 em.persist(address1);
                 commitTransaction(em);

                 // Clear the cache
                 em.clear();
                 clearCache();

                 Address address2 = (Address) em.createNamedStoredProcedureQuery("XMLReadAddressMappedNumberedFieldResult").setParameter(1, address1.getId()).getSingleResult();
                 assertNotNull("Address returned from stored procedure is null", address2);
                 assertTrue("Address didn't build correctly using stored procedure", (address1.getId() == address2.getId()));
                 assertTrue("Address didn't build correctly using stored procedure", (address1.getStreet().equals(address2.getStreet())));
                 assertTrue("Address didn't build correctly using stored procedure", (address1.getCountry().equals(address2.getCountry())));
                 assertTrue("Address didn't build correctly using stored procedure", (address1.getProvince().equals(address2.getProvince())));
                 assertTrue("Address didn't build correctly using stored procedure", (address1.getPostalCode().equals(address2.getPostalCode())));
             } catch (RuntimeException e) {
                 if (isTransactionActive(em)){
                     rollbackTransaction(em);
                 }

                 throw e;
             } finally {
                 closeEntityManager(em);
             }
         }
     }

     /**
      * Tests a NamedStoredProcedureQuery using a result-class.
      */
     public void testQueryWithResultClass() {
         if (supportsStoredProcedures() && getPlatform().isMySQL()) {
             EntityManager em = createEntityManager();

             try {
                 beginTransaction(em);

                 Address address1 = new Address();
                 address1.setCity("Ottawa");
                 address1.setPostalCode("K1G 6P3");
                 address1.setProvince("ON");
                 address1.setStreet("123 Street");
                 address1.setCountry("Canada");
                 em.persist(address1);
                 commitTransaction(em);

                 // Clear the cache
                 em.clear();
                 clearCache();

                 Address address2 = (Address) em.createNamedStoredProcedureQuery("XMLReadAddressWithResultClass").setParameter("address_id_v", address1.getId()).getSingleResult();
                 assertNotNull("Address returned from stored procedure is null", address2);
                 assertTrue("Address didn't build correctly using stored procedure", (address1.getId() == address2.getId()));
                 assertTrue("Address didn't build correctly using stored procedure", (address1.getStreet().equals(address2.getStreet())));
                 assertTrue("Address didn't build correctly using stored procedure", (address1.getCountry().equals(address2.getCountry())));
                 assertTrue("Address didn't build correctly using stored procedure", (address1.getProvince().equals(address2.getProvince())));
                 assertTrue("Address didn't build correctly using stored procedure", (address1.getPostalCode().equals(address2.getPostalCode())));
             } catch (RuntimeException e) {
                 if (isTransactionActive(em)){
                     rollbackTransaction(em);
                 }

                 throw e;
             } finally {
                 closeEntityManager(em);
             }
         }
     }
 }
	/*
	* Copyright (c) 2013, 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:
	// 01/23/2013-2.5 Guy Pelletier
	// - 350487: JPA 2.1 Specification defined support for Stored Procedure Calls
	package org.eclipse.persistence.testing.tests.jpa21.advanced;

	import java.util.List;

	import jakarta.persistence.EntityManager;
	import jakarta.persistence.ParameterMode;
	import jakarta.persistence.StoredProcedureQuery;

	import junit.framework.TestSuite;
	import junit.framework.Test;

	import org.eclipse.persistence.testing.framework.junit.JUnitTestCase;

	import org.eclipse.persistence.testing.models.jpa21.advanced.xml.Address;
	import org.eclipse.persistence.testing.models.jpa21.advanced.xml.AdvancedTableCreator;
	import org.eclipse.persistence.testing.models.jpa21.advanced.xml.Employee;
	import org.eclipse.persistence.testing.models.jpa21.advanced.xml.EmployeeDetails;
	import org.eclipse.persistence.testing.models.jpa21.advanced.xml.EmployeePopulator;
	import org.eclipse.persistence.testing.models.jpa21.advanced.xml.LargeProject;
	import org.eclipse.persistence.testing.models.jpa21.advanced.xml.Project;
	import org.eclipse.persistence.testing.models.jpa21.advanced.xml.SmallProject;

	public class XMLNamedStoredProcedureQueryTestSuite extends JUnitTestCase {
	public XMLNamedStoredProcedureQueryTestSuite() {}

	public XMLNamedStoredProcedureQueryTestSuite(String name) {
	super(name);
	setPuName("MulitPU-4");
	}

	/**
	* Return the the persistence unit name for this test suite.
	*/
	@Override
	public String getPersistenceUnitName() {
	return "MulitPU-4";
	}

	public static Test suite() {
	TestSuite suite = new TestSuite();
	suite.setName("XMLNamedStoredProcedureQueryTestSuite");

	suite.addTest(new XMLNamedStoredProcedureQueryTestSuite("testSetup"));
	suite.addTest(new XMLNamedStoredProcedureQueryTestSuite("testQueryExecuteUpdateOnSelectQueryWithNoResultClass"));
	suite.addTest(new XMLNamedStoredProcedureQueryTestSuite("testQueryExecuteUpdateOnSelectQueryWithResultClass"));
	suite.addTest(new XMLNamedStoredProcedureQueryTestSuite("testQueryExecuteWithNamedCursors"));
	suite.addTest(new XMLNamedStoredProcedureQueryTestSuite("testQueryExecuteWithUnNamedCursor"));
	suite.addTest(new XMLNamedStoredProcedureQueryTestSuite("testQueryGetResultListWithNamedCursors"));
	suite.addTest(new XMLNamedStoredProcedureQueryTestSuite("testQueryWithMultipleResults"));
	suite.addTest(new XMLNamedStoredProcedureQueryTestSuite("testQueryWithNamedColumnResult"));
	suite.addTest(new XMLNamedStoredProcedureQueryTestSuite("testQueryWithNamedFieldResult"));
	suite.addTest(new XMLNamedStoredProcedureQueryTestSuite("testQueryWithNumberedFieldResult"));
	suite.addTest(new XMLNamedStoredProcedureQueryTestSuite("testQueryWithResultClass"));

	return suite;
	}

	/**
	* The setup is done as a test, both to record its failure, and to allow execution in the server.
	*/
	public void testSetup() {
	new AdvancedTableCreator().replaceTables(getPersistenceUnitServerSession());
	EmployeePopulator employeePopulator = new EmployeePopulator();
	employeePopulator.buildExamples();
	employeePopulator.persistExample(getPersistenceUnitServerSession());
	clearCache();
	}

	/**
	* Tests an execute update on a named stored procedure that does a select.
	* NamedStoredProcedure defines a result class.
	*/
	public void testQueryExecuteUpdateOnSelectQueryWithNoResultClass() {
	if (supportsStoredProcedures() && getPlatform().isMySQL()) {
	EntityManager em = createEntityManager();

	try {
	beginTransaction(em);
	em.createNamedStoredProcedureQuery("XMLReadAllAddressesWithNoResultClass").executeUpdate();
	commitTransaction(em);
	} catch (IllegalStateException e) {
	if (isTransactionActive(em)){
	rollbackTransaction(em);
	}

	return;
	} finally {
	closeEntityManager(em);
	}

	fail("Expected Illegal state exception was not thrown.");
	}
	}

	/**
	* Tests an execute update on a named stored procedure that does a select.
	* NamedStoredProcedure defines a result class.
	*/
	public void testQueryExecuteUpdateOnSelectQueryWithResultClass() {
	if (supportsStoredProcedures() && getPlatform().isMySQL()) {
	EntityManager em = createEntityManager();

	try {
	beginTransaction(em);
	em.createNamedStoredProcedureQuery("XMLReadAddressWithResultClass").setParameter("address_id_v", 1).executeUpdate();
	commitTransaction(em);
	} catch (IllegalStateException e) {
	if (isTransactionActive(em)){
	rollbackTransaction(em);
	}

	return;
	} finally {
	closeEntityManager(em);
	}

	fail("Expected Illegal state exception was not thrown.");
	}
	}

	/**
	* Tests a StoredProcedureQuery using multiple names cursors.
	*/
	public void testQueryExecuteWithNamedCursors() {
	if (supportsStoredProcedures() && getPlatform().isOracle() ) {
	EntityManager em = createEntityManager();

	try {
	StoredProcedureQuery query = em.createNamedStoredProcedureQuery("XMLReadUsingNamedRefCursors");

	boolean returnVal = query.execute();

	List<Employee> employees = (List<Employee>) query.getOutputParameterValue("CUR1");
	assertFalse("No employees were returned", employees.isEmpty());
	List<Address> addresses = (List<Address>) query.getOutputParameterValue("CUR2");
	assertFalse("No addresses were returned", addresses.isEmpty());
	} catch (RuntimeException e) {
	if (isTransactionActive(em)){
	rollbackTransaction(em);
	}

	throw e;
	} finally {
	closeEntityManager(em);
	}
	}
	}

	/**
	* Tests a StoredProcedureQuery with an unnamed cursor.
	*/
	public void testQueryExecuteWithUnNamedCursor() {
	if (supportsStoredProcedures() && getPlatform().isOracle() ) {
	EntityManager em = createEntityManager();

	try {
	StoredProcedureQuery query = em.createNamedStoredProcedureQuery("XMLReadUsingUnNamedRefCursor");

	boolean returnVal = query.execute();

	List<Employee> employees = (List<Employee>) query.getOutputParameterValue(1);
	assertFalse("No employees were returned", employees.isEmpty());
	} catch (RuntimeException e) {
	if (isTransactionActive(em)){
	rollbackTransaction(em);
	}

	throw e;
	} finally {
	closeEntityManager(em);
	}
	}
	}

	/**
	* Tests a StoredProcedureQuery on Oracle using named ref cursors.
	*/
	public void testQueryGetResultListWithNamedCursors() {
	if (supportsStoredProcedures() && getPlatform().isOracle() ) {
	EntityManager em = createEntityManager();

	try {
	StoredProcedureQuery query = em.createNamedStoredProcedureQuery("XMLReadUsingNamedRefCursors");

	List<Employee> employees = (List<Employee>) query.getResultList();
	assertFalse("No employees were returned", employees.isEmpty());

	List<Address> addresses = (List<Address>) query.getResultList();
	assertFalse("No addresses were returned", addresses.isEmpty());
	} catch (RuntimeException e) {
	if (isTransactionActive(em)){
	rollbackTransaction(em);
	}

	throw e;
	} finally {
	closeEntityManager(em);
	}
	}
	}

	/**
	* Test multiple result sets by setting the SQL results set mapping from annotation.
	*/
	public void testQueryWithMultipleResults() throws Exception {
	if (supportsStoredProcedures() && getPlatform().isMySQL()) {
	StoredProcedureQuery multipleResultSetQuery = createEntityManager().createNamedStoredProcedureQuery("XMLReadUsingMultipleResultSetMappings");

	// Verify first result set mapping --> Employee
	List results = multipleResultSetQuery.getResultList();
	assertNotNull("No Employee results returned", results);
	assertTrue("Empty Employee results returned", results.size() > 0);

	// Verify second result set mapping --> Address
	assertTrue("Address results not available", multipleResultSetQuery.hasMoreResults());
	results = multipleResultSetQuery.getResultList();
	assertNotNull("No Address results returned", results);
	assertTrue("Empty Address results returned", results.size() > 0);

	// Verify third result set mapping --> Project
	assertTrue("Projects results not available", multipleResultSetQuery.hasMoreResults());
	results = multipleResultSetQuery.getResultList();
	assertNotNull("No Project results returned", results);
	assertTrue("Empty Project results returned", results.size() > 0);

	for (Object result : results) {
	Object[] resultElement = (Object[]) result;
	assertTrue("Failed to Return 3 items", (resultElement.length == 3));
	// Using Number as Different db/drivers can return different types
	// e.g. Oracle with ijdbc14.jar returns BigDecimal where as Derby
	// with derbyclient.jar returns Double. NOTE: the order of checking
	// here is valid and as defined by the spec.
	assertTrue("Failed to return LargeProject", (resultElement[0] instanceof LargeProject) );
	assertTrue("Failed To Return SmallProject", (resultElement[1] instanceof SmallProject) );
	assertTrue("Failed to return column",(resultElement[2] instanceof Number) );
	assertFalse("Returned same data in both result elements",((SmallProject)resultElement[1]).getName().equals(((LargeProject)resultElement[0]).getName()));
	}

	// Verify fourth result set mapping --> Employee Constructor Result
	assertTrue("Employee constructor results not available", multipleResultSetQuery.hasMoreResults());
	results = multipleResultSetQuery.getResultList();
	assertNotNull("No Employee constructor results returned", results);
	assertTrue("Empty Employee constructor results returned", results.size() > 0);

	// Verify there as no more results available
	assertFalse("More results available", multipleResultSetQuery.hasMoreResults());
	}
	}

	/**
	* Tests a NamedStoredProcedureQuery annotation using a result-set mapping.
	*/
	public void testQueryWithNamedColumnResult() {
	// TODO: investigate if this test should work on Oracle as written.
	if (supportsStoredProcedures() && getPlatform().isMySQL()) {
	EntityManager em = createEntityManager();

	try {
	beginTransaction(em);

	Address address = new Address();
	address.setCity("Ottawa");
	address.setPostalCode("K1G 6P3");
	address.setProvince("ON");
	address.setStreet("123 Street");
	address.setCountry("Canada");
	em.persist(address);
	commitTransaction(em);

	// Clear the cache
	em.clear();
	clearCache();

	Object[] values = (Object[]) em.createNamedStoredProcedureQuery("XMLReadAddressMappedNamedColumnResult").setParameter("address_id_v", address.getId()).getSingleResult();
	assertTrue("Address data not found or returned using stored procedure", ((values != null) && (values.length == 6)));
	assertNotNull("No results returned from store procedure call", values[1]);
	assertTrue("Address not found using stored procedure", address.getStreet().equals(values[1]));
	} catch (RuntimeException e) {
	if (isTransactionActive(em)){
	rollbackTransaction(em);
	}

	throw e;
	} finally {
	closeEntityManager(em);
	}
	}
	}

	/**
	* Tests a NamedStoredProcedureQuery using a result-set mapping.
	*/
	public void testQueryWithNamedFieldResult() {
	// TODO: investigate if this test should work on Oracle as written.
	if (supportsStoredProcedures() && getPlatform().isMySQL()) {
	EntityManager em = createEntityManager();

	try {
	beginTransaction(em);

	Address address1 = new Address();
	address1.setCity("Ottawa");
	address1.setPostalCode("K1G 6P3");
	address1.setProvince("ON");
	address1.setStreet("123 Street");
	address1.setCountry("Canada");

	em.persist(address1);
	commitTransaction(em);

	// Clear the cache
	em.clear();
	clearCache();

	Address address2 = (Address) em.createNamedStoredProcedureQuery("XMLReadAddressMappedNamedFieldResult").setParameter("address_id_v", address1.getId()).getSingleResult();
	assertNotNull("Address returned from stored procedure is null", address2);
	assertTrue("Address not found using stored procedure", (address1.getId() == address2.getId()));
	} catch (RuntimeException e) {
	if (isTransactionActive(em)){
	rollbackTransaction(em);
	}

	throw e;
	} finally {
	closeEntityManager(em);
	}
	}
	}

	/**
	* Tests a NamedStoredProcedureQuery using positional paramters.
	*/
	public void testQueryWithNumberedFieldResult() {
	if (supportsStoredProcedures() && getPlatform().isMySQL()) {
	EntityManager em = createEntityManager();

	try {
	beginTransaction(em);

	Address address1 = new Address();
	address1.setCity("Ottawa");
	address1.setPostalCode("K1G 6P3");
	address1.setProvince("ON");
	address1.setStreet("123 Street");
	address1.setCountry("Canada");
	em.persist(address1);
	commitTransaction(em);

	// Clear the cache
	em.clear();
	clearCache();

	Address address2 = (Address) em.createNamedStoredProcedureQuery("XMLReadAddressMappedNumberedFieldResult").setParameter(1, address1.getId()).getSingleResult();
	assertNotNull("Address returned from stored procedure is null", address2);
	assertTrue("Address didn't build correctly using stored procedure", (address1.getId() == address2.getId()));
	assertTrue("Address didn't build correctly using stored procedure", (address1.getStreet().equals(address2.getStreet())));
	assertTrue("Address didn't build correctly using stored procedure", (address1.getCountry().equals(address2.getCountry())));
	assertTrue("Address didn't build correctly using stored procedure", (address1.getProvince().equals(address2.getProvince())));
	assertTrue("Address didn't build correctly using stored procedure", (address1.getPostalCode().equals(address2.getPostalCode())));
	} catch (RuntimeException e) {
	if (isTransactionActive(em)){
	rollbackTransaction(em);
	}

	throw e;
	} finally {
	closeEntityManager(em);
	}
	}
	}

	/**
	* Tests a NamedStoredProcedureQuery using a result-class.
	*/
	public void testQueryWithResultClass() {
	if (supportsStoredProcedures() && getPlatform().isMySQL()) {
	EntityManager em = createEntityManager();

	try {
	beginTransaction(em);

	Address address1 = new Address();
	address1.setCity("Ottawa");
	address1.setPostalCode("K1G 6P3");
	address1.setProvince("ON");
	address1.setStreet("123 Street");
	address1.setCountry("Canada");
	em.persist(address1);
	commitTransaction(em);

	// Clear the cache
	em.clear();
	clearCache();

	Address address2 = (Address) em.createNamedStoredProcedureQuery("XMLReadAddressWithResultClass").setParameter("address_id_v", address1.getId()).getSingleResult();
	assertNotNull("Address returned from stored procedure is null", address2);
	assertTrue("Address didn't build correctly using stored procedure", (address1.getId() == address2.getId()));
	assertTrue("Address didn't build correctly using stored procedure", (address1.getStreet().equals(address2.getStreet())));
	assertTrue("Address didn't build correctly using stored procedure", (address1.getCountry().equals(address2.getCountry())));
	assertTrue("Address didn't build correctly using stored procedure", (address1.getProvince().equals(address2.getProvince())));
	assertTrue("Address didn't build correctly using stored procedure", (address1.getPostalCode().equals(address2.getPostalCode())));
	} catch (RuntimeException e) {
	if (isTransactionActive(em)){
	rollbackTransaction(em);
	}

	throw e;
	} finally {
	closeEntityManager(em);
	}
	}
	}
	}