Examples with StoredProcedureQuery javax.persistence.StoredProcedureQuery used on opensource projects

Example 31 with StoredProcedureQuery

use of javax.persistence.StoredProcedureQuery in project tests by datanucleus.

the class StoredProcedureTest method testExecuteOutputParam.

public void testExecuteOutputParam() {
    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_OUTPUTPARAM";
    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 + "(OUT PARAM1 INT) BEGIN " + "SELECT COUNT(*) INTO PARAM1 FROM JPA_AN_PERSON; 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");
            Long count = (Long) q.getSingleResult();
            // Execute stored proc and compare
            StoredProcedureQuery spq = em.createStoredProcedureQuery(procName);
            spq.registerStoredProcedureParameter("PARAM1", Integer.class, ParameterMode.OUT);
            boolean val = spq.execute();
            assertFalse("Flag for result set returned true but should have been false", val);
            Object paramVal = spq.getOutputParameterValue("PARAM1");
            assertEquals("Output parameter is incorrect", new Integer(count.intValue()), paramVal);
            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);
    }
}

Example 32 with StoredProcedureQuery

use of javax.persistence.StoredProcedureQuery in project dataverse by IQSS.

the class DatasetServiceBean method generateIdentifierAsSequentialNumber.

private String generateIdentifierAsSequentialNumber(Dataset dataset, IdServiceBean idServiceBean) {
    String identifier;
    do {
        StoredProcedureQuery query = this.em.createNamedStoredProcedureQuery("Dataset.generateIdentifierAsSequentialNumber");
        query.execute();
        Integer identifierNumeric = (Integer) query.getOutputParameterValue(1);
        // because the stored procedure hasn't been created in the database?
        if (identifierNumeric == null) {
            return null;
        }
        identifier = identifierNumeric.toString();
    } while (!isIdentifierUniqueInDatabase(identifier, dataset, idServiceBean));
    return identifier;
}

Example 33 with StoredProcedureQuery

use of javax.persistence.StoredProcedureQuery in project hibernate-orm by hibernate.

the class MySQLStoredProcedureTest method testStoredProcedureOutParameter.

@Test
public void testStoredProcedureOutParameter() {
    EntityManager entityManager = createEntityManager();
    entityManager.getTransaction().begin();
    try {
        StoredProcedureQuery query = entityManager.createStoredProcedureQuery("sp_count_phones");
        query.registerStoredProcedureParameter("personId", Long.class, ParameterMode.IN);
        query.registerStoredProcedureParameter("phoneCount", Long.class, ParameterMode.OUT);
        query.setParameter("personId", 1L);
        query.execute();
        Long phoneCount = (Long) query.getOutputParameterValue("phoneCount");
        assertEquals(Long.valueOf(2), phoneCount);
    } finally {
        entityManager.getTransaction().rollback();
        entityManager.close();
    }
}

Example 34 with StoredProcedureQuery

use of javax.persistence.StoredProcedureQuery in project hibernate-orm by hibernate.

the class MySQLStoredProcedureTest method testStoredProcedureRefCursor.

@Test
public void testStoredProcedureRefCursor() {
    EntityManager entityManager = createEntityManager();
    entityManager.getTransaction().begin();
    try {
        StoredProcedureQuery query = entityManager.createStoredProcedureQuery("sp_phones");
        query.registerStoredProcedureParameter(1, void.class, ParameterMode.REF_CURSOR);
        query.registerStoredProcedureParameter(2, Long.class, ParameterMode.IN);
        query.setParameter(2, 1L);
        List<Object[]> personComments = query.getResultList();
        assertEquals(2, personComments.size());
    } catch (Exception e) {
        assertTrue(Pattern.compile("Dialect .*? not known to support REF_CURSOR parameters").matcher(e.getCause().getMessage()).matches());
    } finally {
        entityManager.getTransaction().rollback();
        entityManager.close();
    }
}

Example 35 with StoredProcedureQuery

use of javax.persistence.StoredProcedureQuery in project hibernate-orm by hibernate.

the class JpaTckUsageTest method testBasicScalarResults.

@Test
public void testBasicScalarResults() {
    EntityManager em = entityManagerFactory.createEntityManager();
    em.getTransaction().begin();
    try {
        StoredProcedureQuery query = em.createStoredProcedureQuery("findOneUser");
        boolean isResult = query.execute();
        assertTrue(isResult);
        int updateCount = query.getUpdateCount();
        boolean results = false;
        do {
            List list = query.getResultList();
            assertEquals(1, list.size());
            results = query.hasMoreResults();
        // and it only sets the updateCount once lol
        } while (results || updateCount != -1);
    } finally {
        em.getTransaction().commit();
        em.close();
    }
}