Examples with JPAEntityManager org.datanucleus.api.jpa.JPAEntityManager used on opensource projects

Example 6 with JPAEntityManager

use of org.datanucleus.api.jpa.JPAEntityManager in project tests by datanucleus.

the class StoredProcedureTest method testNamedProcWithParamReturningResultSet.

public void testNamedProcWithParamReturningResultSet() {
    if (vendorID == null) {
        return;
    }
    if (storeMgr instanceof RDBMSStoreManager) {
        DatastoreAdapter dba = ((RDBMSStoreManager) storeMgr).getDatastoreAdapter();
        if (!dba.supportsOption(DatastoreAdapter.STORED_PROCEDURES)) {
            LOG.warn("Database doesnt support stored procedures so ignoring the test");
            return;
        }
    }
    String procName = "DN_PROC_NAMED_RS";
    RDBMSStoreManager rdbmsMgr = (RDBMSStoreManager) storeMgr;
    ManagedConnection mc = rdbmsMgr.getConnectionManager().getConnection(-1);
    try {
        Connection conn = (Connection) mc.getConnection();
        Statement stmt = conn.createStatement();
        // Drop it first
        String dropStmt = "DROP PROCEDURE IF EXISTS " + procName;
        stmt.execute(dropStmt);
        // Create it
        String createStmt = "CREATE PROCEDURE " + procName + "(IN PARAM1 VARCHAR(255)) BEGIN " + "SELECT COUNT(*) FROM JPA_AN_PERSON WHERE FIRSTNAME = PARAM1; END";
        stmt.execute(createStmt);
    } catch (SQLException sqle) {
        fail("Exception in drop-create of stored procedure : " + sqle.getMessage());
    } finally {
        mc.close();
    }
    try {
        JPAEntityManager em = (JPAEntityManager) getEM();
        EntityTransaction tx = em.getTransaction();
        try {
            tx.begin();
            Person p = new Person(101, "Fred", "Flintstone", "fred.flintstone@warnerbros.com");
            em.persist(p);
            tx.commit();
        } catch (Exception e) {
            LOG.error("Exception in test", e);
            fail("Exception in test : " + e.getMessage());
        } finally {
            if (tx.isActive()) {
                tx.rollback();
            }
            em.close();
        }
        em = (JPAEntityManager) getEM();
        tx = em.getTransaction();
        try {
            tx.begin();
            // Get value to compare against
            Query q = em.createQuery("SELECT COUNT(p) FROM " + Person.class.getName() + " p WHERE p.firstName='Fred'");
            Long count = (Long) q.getSingleResult();
            // Execute stored proc and compare
            StoredProcedureQuery spq = em.createNamedStoredProcedureQuery("myNamedSP");
            spq.setParameter("PARAM1", "Fred");
            boolean val = spq.execute();
            assertTrue("Return from execute should have been true", val);
            List results = spq.getResultList();
            assertNotNull("ResultSet was null!", results);
            assertEquals("Number of results was wrong", 1, results.size());
            assertEquals("Result set result was wrong", count, results.get(0));
            assertFalse("More results present but should be the end", spq.hasMoreResults());
            tx.commit();
        } catch (Exception e) {
            LOG.error("Exception in test", e);
            fail("Exception in test : " + e.getMessage());
        } finally {
            if (tx.isActive()) {
                tx.rollback();
            }
            em.close();
        }
    } finally {
        // Cleanup data
        clean(Person.class);
    }
}