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

Example 16 with StoredProcedureQuery

use of javax.persistence.StoredProcedureQuery in project microservices by pwillhan.

the class CallStoredProcedures method callReturningRefCursor.

@Test(groups = "POSTGRESQL")
public void callReturningRefCursor() throws Exception {
    storeTestData();
    UserTransaction tx = TM.getUserTransaction();
    try {
        tx.begin();
        EntityManager em = JPA.createEntityManager();
        /*
            em.unwrap(Session.class).doWork(
                new CallProcedureAndPrintResult() {
                    @Override
                    protected CallableStatement prepare(Connection connection) throws SQLException {
                        CallableStatement statement = connection.prepareCall("{? = call FIND_ITEMS()}");
                        statement.registerOutParameter(1, Types.OTHER);
                        return statement;
                    }

                    @Override
                    protected ResultSet execute(CallableStatement statement) throws SQLException {
                        statement.execute();
                        return (ResultSet)statement.getObject(1);
                    }
                }
            );
            */
        StoredProcedureQuery query = em.createStoredProcedureQuery("FIND_ITEMS", Item.class);
        query.registerStoredProcedureParameter(1, void.class, ParameterMode.REF_CURSOR);
        List<Item> result = query.getResultList();
        for (Item item : result) {
        // ...
        }
        assertEquals(result.size(), 3);
        tx.commit();
        em.close();
    } finally {
        TM.rollback();
    }
}

Example 17 with StoredProcedureQuery

use of javax.persistence.StoredProcedureQuery in project microservices by pwillhan.

the class CallStoredProcedures method callWithInOutParameters.

@Test(groups = "MYSQL")
public void callWithInOutParameters() throws Exception {
    TestDataCategoriesItems testDataCategoriesItems = storeTestData();
    final Long ITEM_ID = testDataCategoriesItems.items.getFirstId();
    UserTransaction tx = TM.getUserTransaction();
    try {
        tx.begin();
        EntityManager em = JPA.createEntityManager();
        /*
            em.unwrap(Session.class).doWork(
                new CallProcedureAndPrintResult() {
                    @Override
                    protected CallableStatement prepare(Connection connection) throws SQLException {
                        CallableStatement statement = connection.prepareCall("{call FIND_ITEM_TOTAL(?, ?)}");
                        statement.setLong(1, ITEM_ID);
                        statement.registerOutParameter(2, Types.VARCHAR);
                        return statement;
                    }

                    @Override
                    protected ResultSet execute(CallableStatement statement) throws SQLException {
                        statement.execute();
                        System.out.println("### TOTAL NR. OF ITEMS: " + statement.getLong(2));
                        return statement.getResultSet();
                    }
                }
            );
            */
        StoredProcedureQuery query = em.createStoredProcedureQuery("FIND_ITEM_TOTAL", Item.class);
        /* 
               Register all parameters by position (starting at 1) and their type.
             */
        query.registerStoredProcedureParameter(1, Long.class, ParameterMode.IN);
        query.registerStoredProcedureParameter(2, Long.class, ParameterMode.OUT);
        /* 
               Bind values to the input parameters.
             */
        query.setParameter(1, ITEM_ID);
        /* 
               Retrieve the result set returned by the procedure.
             */
        List<Item> result = query.getResultList();
        for (Item item : result) {
            // ...
            assertEquals(item.getId(), ITEM_ID);
        }
        /* 
               After you've retrieved the result sets, you can access the output parameter values.
             */
        Long totalNumberOfItems = (Long) query.getOutputParameterValue(2);
        assertEquals(result.size(), 1);
        assertEquals(totalNumberOfItems, new Long(3));
        tx.commit();
        em.close();
    } finally {
        TM.rollback();
    }
}

Example 18 with StoredProcedureQuery

use of javax.persistence.StoredProcedureQuery in project microservices by pwillhan.

the class CallStoredProcedures method callReturningResultSet.

@Test(groups = "MYSQL")
public void callReturningResultSet() throws Exception {
    storeTestData();
    UserTransaction tx = TM.getUserTransaction();
    try {
        tx.begin();
        EntityManager em = JPA.createEntityManager();
        /*
            em.unwrap(Session.class).doWork(
                new CallProcedureAndPrintResult() {
                    @Override
                    protected CallableStatement prepare(Connection connection) throws SQLException {
                        return connection.prepareCall("{call FIND_ITEMS()}");
                    }
                }
            );
            */
        StoredProcedureQuery query = em.createStoredProcedureQuery("FIND_ITEMS", // Or name of result set mapping
        Item.class);
        List<Item> result = query.getResultList();
        for (Item item : result) {
        // ...
        }
        assertEquals(result.size(), 3);
        tx.commit();
        em.close();
    } finally {
        TM.rollback();
    }
}

Example 19 with StoredProcedureQuery

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

the class HSQLStoreProcedureTest method testGetPositionalParameters.

@Test
@TestForIssue(jiraKey = "HHH-10915")
public void testGetPositionalParameters() {
    EntityManager em = entityManagerFactory.createEntityManager();
    try {
        StoredProcedureQuery query = em.createNamedStoredProcedureQuery("User.inoutproc");
        final Set<Parameter<?>> parameters = query.getParameters();
        assertThat(parameters.size(), is(2));
        try {
            query.getParameter(1);
            fail("An IllegalArgumentException is expected, The stored procedure has named parameters not positional");
        } catch (IllegalArgumentException iae) {
        // expected
        }
        try {
            query.getParameter(1, String.class);
            fail("An IllegalArgumentException is expected, The stored procedure has named parameters not positional");
        } catch (IllegalArgumentException iae) {
        // expected
        }
    } finally {
        em.close();
    }
}

Example 20 with StoredProcedureQuery

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

the class H2StoreProcedureTest method testStoreProcedureGetParameterByPosition.

@Test
public void testStoreProcedureGetParameterByPosition() {
    final EntityManager entityManager = getOrCreateEntityManager();
    try {
        StoredProcedureQuery query = entityManager.createStoredProcedureQuery("by_Id", MyEntity.class);
        query.registerStoredProcedureParameter(1, Long.class, ParameterMode.IN);
        query.setParameter(1, 1L);
        final List resultList = query.getResultList();
        assertThat(resultList.size(), is(1));
        final Set<Parameter<?>> parameters = query.getParameters();
        assertThat(parameters.size(), is(1));
        final Parameter<?> parameter = query.getParameter(1);
        assertThat(parameter, not(nullValue()));
        try {
            query.getParameter(2);
            fail("IllegalArgumentException expected, parameter at position 2 does not exist");
        } catch (IllegalArgumentException iae) {
        // expected
        }
    } finally {
        entityManager.close();
    }
}