Search in sources :

Example 16 with ProcedureCall

use of org.hibernate.procedure.ProcedureCall in project hibernate-orm by hibernate.

the class MySQLStoredProcedureTest method testHibernateProcedureCallReturnValueParameter.

@Test
public void testHibernateProcedureCallReturnValueParameter() {
    EntityManager entityManager = createEntityManager();
    entityManager.getTransaction().begin();
    try {
        Session session = entityManager.unwrap(Session.class);
        ProcedureCall call = session.createStoredProcedureCall("sp_phones");
        call.registerParameter(1, Long.class, ParameterMode.IN).bindValue(1L);
        Output output = call.getOutputs().getCurrent();
        List<Object[]> personComments = ((ResultSetOutput) output).getResultList();
        assertEquals(2, personComments.size());
    } finally {
        entityManager.getTransaction().rollback();
        entityManager.close();
    }
}
Also used : ResultSetOutput(org.hibernate.result.ResultSetOutput) EntityManager(javax.persistence.EntityManager) ProcedureCall(org.hibernate.procedure.ProcedureCall) Output(org.hibernate.result.Output) ResultSetOutput(org.hibernate.result.ResultSetOutput) Session(org.hibernate.Session) Test(org.junit.Test)

Example 17 with ProcedureCall

use of org.hibernate.procedure.ProcedureCall in project hibernate-orm by hibernate.

the class StoredProcedureResultSetMappingTest method testPartialResults.

@Test
public void testPartialResults() {
    Configuration cfg = new Configuration().addAnnotatedClass(Employee.class).setProperty(AvailableSettings.HBM2DDL_AUTO, "create-drop");
    cfg.addAuxiliaryDatabaseObject(new ProcedureDefinition());
    SessionFactory sf = cfg.buildSessionFactory();
    try {
        Session session = sf.openSession();
        session.beginTransaction();
        ProcedureCall call = session.createStoredProcedureCall("allEmployeeNames", "id-fname-lname");
        ProcedureOutputs outputs = call.getOutputs();
        ResultSetOutput output = assertTyping(ResultSetOutput.class, outputs.getCurrent());
        assertEquals(3, output.getResultList().size());
        assertTyping(Employee.class, output.getResultList().get(0));
        session.getTransaction().commit();
        session.close();
    } finally {
        sf.close();
    }
}
Also used : SessionFactory(org.hibernate.SessionFactory) ResultSetOutput(org.hibernate.result.ResultSetOutput) ProcedureCall(org.hibernate.procedure.ProcedureCall) Configuration(org.hibernate.cfg.Configuration) ProcedureOutputs(org.hibernate.procedure.ProcedureOutputs) Session(org.hibernate.Session) Test(org.junit.Test)

Example 18 with ProcedureCall

use of org.hibernate.procedure.ProcedureCall in project hibernate-orm by hibernate.

the class StoredProcedureTest method testInParametersNullnessPassingInNamedQueriesViaHints.

@Test
@SuppressWarnings("unchecked")
public void testInParametersNullnessPassingInNamedQueriesViaHints() {
    Session session = openSession();
    session.beginTransaction();
    // similar to #testInParametersNotSet and #testInParametersNotSetPass in terms of testing
    // support for specifying whether to pass NULL argument values or not.  This version tests
    // named procedure support via hints.
    // first a fixture - this execution should fail
    {
        ProcedureCall query = session.getNamedProcedureCall("findUserRangeNoNullPassing");
        query.getParameterRegistration(2).bindValue(2);
        try {
            query.getOutputs();
            fail("Expecting failure due to missing parameter bind");
        } catch (JDBCException ignore) {
        }
    }
    // here we enable NULL passing via hint through a named parameter
    {
        ProcedureCall query = session.getNamedProcedureCall("findUserRangeNamedNullPassing");
        query.getParameterRegistration("secondArg").bindValue(2);
        query.getOutputs();
    }
    // here we enable NULL passing via hint through a named parameter
    {
        ProcedureCall query = session.getNamedProcedureCall("findUserRangeOrdinalNullPassing");
        query.getParameterRegistration(2).bindValue(2);
        query.getOutputs();
    }
    session.getTransaction().commit();
    session.close();
}
Also used : ProcedureCall(org.hibernate.procedure.ProcedureCall) JDBCException(org.hibernate.JDBCException) Session(org.hibernate.Session) Test(org.junit.Test)

Example 19 with ProcedureCall

use of org.hibernate.procedure.ProcedureCall in project api-core by ca-cwds.

the class SsaName3Dao method callStoredProc.

/**
 * Call DB2 stored procedure SPSSANAME3 to insert soundex records for client search. Story
 * #146481759.
 *
 * @param parameterObject stored procedure parameters
 */
public void callStoredProc(SsaName3ParameterObject parameterObject) {
    Session session = sessionFactory.getCurrentSession();
    final String STORED_PROC_NAME = "SPSSANAME3";
    final String schema = (String) session.getSessionFactory().getProperties().get("hibernate.default_schema");
    String strdtts = new SimpleDateFormat("yyyy-MM-dd-HH.mm.ss.SSS").format(parameterObject.getUpdateTimeStamp());
    try {
        ProcedureCall q = session.createStoredProcedureCall(schema + "." + STORED_PROC_NAME);
        q.registerStoredProcedureParameter("TABLENAME", String.class, ParameterMode.IN);
        q.registerStoredProcedureParameter("CRUDFUNCT", String.class, ParameterMode.IN);
        q.registerStoredProcedureParameter("IDENTIFIER", String.class, ParameterMode.IN);
        q.registerStoredProcedureParameter("NAMECODE", String.class, ParameterMode.IN);
        q.registerStoredProcedureParameter("FIRSTNAME", String.class, ParameterMode.IN);
        q.registerStoredProcedureParameter("MIDDLENAME", String.class, ParameterMode.IN);
        q.registerStoredProcedureParameter("LASTNAME", String.class, ParameterMode.IN);
        q.registerStoredProcedureParameter("STREETNUM", String.class, ParameterMode.IN);
        q.registerStoredProcedureParameter("STREETNAME", String.class, ParameterMode.IN);
        q.registerStoredProcedureParameter("GVRENTC", String.class, ParameterMode.IN);
        q.registerStoredProcedureParameter("LASTUPDTM", String.class, ParameterMode.IN);
        q.registerStoredProcedureParameter("LASTUPDID", String.class, ParameterMode.IN);
        q.registerStoredProcedureParameter("RETSTATUS", String.class, ParameterMode.OUT);
        q.registerStoredProcedureParameter("RETMESSAG", String.class, ParameterMode.OUT);
        q.setParameter("TABLENAME", parameterObject.getTableName());
        q.setParameter("CRUDFUNCT", parameterObject.getCrudOper());
        q.setParameter("IDENTIFIER", parameterObject.getIdentifier());
        q.setParameter("NAMECODE", parameterObject.getNameCd());
        q.setParameter("FIRSTNAME", parameterObject.getFirstName());
        q.setParameter("MIDDLENAME", parameterObject.getMiddleName());
        q.setParameter("LASTNAME", parameterObject.getLastName());
        q.setParameter("STREETNUM", parameterObject.getStreettNumber());
        q.setParameter("STREETNAME", parameterObject.getStreetName());
        q.setParameter("GVRENTC", String.valueOf(parameterObject.getGvrEntc()));
        q.setParameter("LASTUPDTM", strdtts);
        q.setParameter("LASTUPDID", parameterObject.getUpdateId());
        if (LOGGER.isInfoEnabled()) {
            LOGGER.info("TABLENAME = {}", parameterObject.getTableName());
            LOGGER.info("CRUDFUNCT = {}", parameterObject.getCrudOper());
            LOGGER.info("IDENTIFIER = {}", parameterObject.getIdentifier());
            LOGGER.info("NAMECODE = {}", parameterObject.getNameCd());
            LOGGER.info("FIRSTNAME = {}", parameterObject.getFirstName());
            LOGGER.info("MIDDLENAME = {}", parameterObject.getMiddleName());
            LOGGER.info("LASTNAME = {}", parameterObject.getLastName());
            LOGGER.info("STREETNUM = {}", parameterObject.getStreettNumber());
            LOGGER.info("STREETNAME = {}", parameterObject.getStreetName());
            LOGGER.info("GVRENTC = {}", String.valueOf(parameterObject.getGvrEntc()));
            LOGGER.info("LASTUPDTM = {}", strdtts);
            LOGGER.info("LASTUPDID = {}", parameterObject.getUpdateId());
        }
        q.execute();
        final String returnStatus = (String) q.getOutputParameterValue("RETSTATUS");
        final String returnMessage = (String) q.getOutputParameterValue("RETMESSAG");
        int returnCode = Integer.parseInt(returnStatus);
        LOGGER.info("storeProcReturnStatus: {}, storeProcreturnMessage: {}", returnStatus, returnMessage);
        /*
       * return code: 0=successful, 1=keys not generated, 2=Invalid parameters sent to stored
       * procedure 3=SQL failed, 4=Call to SSANAME3 DLL failed
       */
        if (returnCode != 0 && returnCode != 1) {
            LOGGER.error("Stored Procedure return message - {}", returnMessage);
            throw new DaoException("Stored Procedure returned with ERROR - {}" + returnMessage);
        }
    } catch (DaoException h) {
        throw new DaoException("Call to Stored Procedure failed - " + h, h);
    }
}
Also used : ProcedureCall(org.hibernate.procedure.ProcedureCall) DaoException(gov.ca.cwds.data.DaoException) SimpleDateFormat(java.text.SimpleDateFormat) Session(org.hibernate.Session)

Example 20 with ProcedureCall

use of org.hibernate.procedure.ProcedureCall in project hibernate-orm by hibernate.

the class MySQLStoredProcedureTest method testHibernateProcedureCallReturnValueParameter.

@Test
public void testHibernateProcedureCallReturnValueParameter() {
    doInJPA(this::entityManagerFactory, entityManager -> {
        // tag::sql-hibernate-call-sp-no-out-mysql-example[]
        Session session = entityManager.unwrap(Session.class);
        ProcedureCall call = session.createStoredProcedureCall("sp_phones");
        call.registerParameter(1, Long.class, ParameterMode.IN).bindValue(1L);
        Output output = call.getOutputs().getCurrent();
        List<Object[]> personComments = ((ResultSetOutput) output).getResultList();
        // end::sql-hibernate-call-sp-no-out-mysql-example[]
        assertEquals(2, personComments.size());
    });
}
Also used : ResultSetOutput(org.hibernate.result.ResultSetOutput) ProcedureCall(org.hibernate.procedure.ProcedureCall) Output(org.hibernate.result.Output) ResultSetOutput(org.hibernate.result.ResultSetOutput) Session(org.hibernate.Session) Test(org.junit.Test)

Aggregations

ProcedureCall (org.hibernate.procedure.ProcedureCall)28 Test (org.junit.Test)24 Session (org.hibernate.Session)18 ResultSetOutput (org.hibernate.result.ResultSetOutput)16 Output (org.hibernate.result.Output)14 ProcedureOutputs (org.hibernate.procedure.ProcedureOutputs)11 EntityManager (javax.persistence.EntityManager)4 List (java.util.List)3 StoredProcedureResultSetMappingTest (org.hibernate.test.sql.storedproc.StoredProcedureResultSetMappingTest)3 JDBCException (org.hibernate.JDBCException)2 ProcedureCallImpl (org.hibernate.procedure.internal.ProcedureCallImpl)2 DaoException (gov.ca.cwds.data.DaoException)1 SimpleDateFormat (java.text.SimpleDateFormat)1 Date (java.util.Date)1 Parameter (javax.persistence.Parameter)1 SessionFactory (org.hibernate.SessionFactory)1 Configuration (org.hibernate.cfg.Configuration)1 ProcedureCallMemento (org.hibernate.procedure.ProcedureCallMemento)1 QueryParameter (org.hibernate.query.QueryParameter)1 TestForIssue (org.hibernate.testing.TestForIssue)1