Examples with CallableStatement java.sql.CallableStatement used on opensource projects

Example 36 with CallableStatement

use of java.sql.CallableStatement in project hibernate-orm by hibernate.

the class SQLServerStoredProcedureTest method testStoredProcedureReturnValue.

@Test
public void testStoredProcedureReturnValue() {
    EntityManager entityManager = createEntityManager();
    entityManager.getTransaction().begin();
    try {
        Session session = entityManager.unwrap(Session.class);
        session.doWork(new Work() {

            @Override
            public void execute(Connection connection) throws SQLException {
                CallableStatement function = null;
                try {
                    function = connection.prepareCall("{ ? = call fn_count_phones(?) }");
                    function.registerOutParameter(1, Types.INTEGER);
                    function.setInt(2, 1);
                    function.execute();
                    int phoneCount = function.getInt(1);
                    assertEquals(2, phoneCount);
                } finally {
                    if (function != null) {
                        function.close();
                    }
                }
            }
        });
    } finally {
        entityManager.getTransaction().rollback();
        entityManager.close();
    }
}

Example 37 with CallableStatement

use of java.sql.CallableStatement in project hibernate-orm by hibernate.

the class MySQLStoredProcedureTest method testFunctionWithJDBC.

@Test
public void testFunctionWithJDBC() {
    EntityManager entityManager = createEntityManager();
    entityManager.getTransaction().begin();
    try {
        final AtomicReference<Integer> phoneCount = new AtomicReference<>();
        Session session = entityManager.unwrap(Session.class);
        session.doWork(connection -> {
            try (CallableStatement function = connection.prepareCall("{ ? = call fn_count_phones(?) }")) {
                function.registerOutParameter(1, Types.INTEGER);
                function.setInt(2, 1);
                function.execute();
                phoneCount.set(function.getInt(1));
            }
        });
        assertEquals(Integer.valueOf(2), phoneCount.get());
    } finally {
        entityManager.getTransaction().rollback();
        entityManager.close();
    }
}

Example 38 with CallableStatement

use of java.sql.CallableStatement in project hibernate-orm by hibernate.

the class OracleStoredProcedureTest method testNamedNativeQueryStoredProcedureRefCursorWithJDBC.

@Test
public void testNamedNativeQueryStoredProcedureRefCursorWithJDBC() {
    EntityManager entityManager = createEntityManager();
    entityManager.getTransaction().begin();
    try {
        Session session = entityManager.unwrap(Session.class);
        session.doWork(connection -> {
            try (CallableStatement function = connection.prepareCall("{ ? = call fn_person_and_phones( ? ) }")) {
                try {
                    function.registerOutParameter(1, Types.REF_CURSOR);
                } catch (SQLException e) {
                    function.registerOutParameter(1, -10);
                }
                function.setInt(2, 1);
                function.execute();
                try (ResultSet resultSet = (ResultSet) function.getObject(1)) {
                    while (resultSet.next()) {
                        Long postCommentId = resultSet.getLong(1);
                        String review = resultSet.getString(2);
                    }
                }
            }
        });
    } finally {
        entityManager.getTransaction().rollback();
        entityManager.close();
    }
}

Example 39 with CallableStatement

use of java.sql.CallableStatement in project hibernate-orm by hibernate.

the class Loader method prepareQueryStatement.

/**
	 * Obtain a <tt>PreparedStatement</tt> with all parameters pre-bound.
	 * Bind JDBC-style <tt>?</tt> parameters, named parameters, and
	 * limit parameters.
	 */
protected final PreparedStatement prepareQueryStatement(String sql, final QueryParameters queryParameters, final LimitHandler limitHandler, final boolean scroll, final SharedSessionContractImplementor session) throws SQLException, HibernateException {
    final Dialect dialect = getFactory().getDialect();
    final RowSelection selection = queryParameters.getRowSelection();
    final boolean useLimit = LimitHelper.useLimit(limitHandler, selection);
    final boolean hasFirstRow = LimitHelper.hasFirstRow(selection);
    final boolean useLimitOffset = hasFirstRow && useLimit && limitHandler.supportsLimitOffset();
    final boolean callable = queryParameters.isCallable();
    final ScrollMode scrollMode = getScrollMode(scroll, hasFirstRow, useLimitOffset, queryParameters);
    PreparedStatement st = session.getJdbcCoordinator().getStatementPreparer().prepareQueryStatement(sql, callable, scrollMode);
    try {
        int col = 1;
        //TODO: can we limit stored procedures ?!
        col += limitHandler.bindLimitParametersAtStartOfQuery(selection, st, col);
        if (callable) {
            col = dialect.registerResultSetOutParameter((CallableStatement) st, col);
        }
        col += bindParameterValues(st, queryParameters, col, session);
        col += limitHandler.bindLimitParametersAtEndOfQuery(selection, st, col);
        limitHandler.setMaxRows(selection, st);
        if (selection != null) {
            if (selection.getTimeout() != null) {
                st.setQueryTimeout(selection.getTimeout());
            }
            if (selection.getFetchSize() != null) {
                st.setFetchSize(selection.getFetchSize());
            }
        }
        // handle lock timeout...
        LockOptions lockOptions = queryParameters.getLockOptions();
        if (lockOptions != null) {
            if (lockOptions.getTimeOut() != LockOptions.WAIT_FOREVER) {
                if (!dialect.supportsLockTimeouts()) {
                    if (LOG.isDebugEnabled()) {
                        LOG.debugf("Lock timeout [%s] requested but dialect reported to not support lock timeouts", lockOptions.getTimeOut());
                    }
                } else if (dialect.isLockTimeoutParameterized()) {
                    st.setInt(col++, lockOptions.getTimeOut());
                }
            }
        }
        if (LOG.isTraceEnabled()) {
            LOG.tracev("Bound [{0}] parameters total", col);
        }
    } catch (SQLException | HibernateException e) {
        session.getJdbcCoordinator().getLogicalConnection().getResourceRegistry().release(st);
        session.getJdbcCoordinator().afterStatementExecution();
        throw e;
    }
    return st;
}

Example 40 with CallableStatement

use of java.sql.CallableStatement in project hibernate-orm by hibernate.

the class ProcedureCallImpl method buildOutputs.

private ProcedureOutputsImpl buildOutputs() {
    // todo : going to need a very specialized Loader for this.
    // or, might be a good time to look at splitting Loader up into:
    //		1) building statement objects
    //		2) executing statement objects
    //		3) processing result sets
    // for now assume there are no resultClasses nor mappings defined..
    // 	TOTAL PROOF-OF-CONCEPT!!!!!!
    // todo : how to identify calls which should be in the form `{? = call procName...}` ??? (note leading param marker)
    // 		more than likely this will need to be a method on the native API.  I can see this as a trigger to
    //		both: (1) add the `? = ` part and also (2) register a REFCURSOR parameter for DBs (Oracle, PGSQL) that
    //		need it.
    final String call = getProducer().getJdbcServices().getJdbcEnvironment().getDialect().getCallableStatementSupport().renderCallableStatement(procedureName, parameterStrategy, registeredParameters, getProducer());
    try {
        LOG.debugf("Preparing procedure call : %s", call);
        final CallableStatement statement = (CallableStatement) getSession().getJdbcCoordinator().getStatementPreparer().prepareStatement(call, true);
        // prepare parameters
        int i = 1;
        for (ParameterRegistrationImplementor parameter : registeredParameters) {
            parameter.prepare(statement, i);
            if (parameter.getMode() == ParameterMode.REF_CURSOR) {
                i++;
            } else {
                i += parameter.getSqlTypes().length;
            }
        }
        return new ProcedureOutputsImpl(this, statement);
    } catch (SQLException e) {
        throw getSession().getJdbcServices().getSqlExceptionHelper().convert(e, "Error preparing CallableStatement", getProcedureName());
    }
}