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();
}
}
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();
}
}
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();
}
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);
}
}
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());
});
}
Aggregations