/** * Register a named output parameter * @param name the parameter name * @param sqlType an SQL type constant as defined by {@link java.sql.Types} or by the JDBC vendor. * @return self */ public Call registerOutParameter(String name, int sqlType) { return registerOutParameter(name, sqlType, null); }
@Override void configureReturner(Call c, SqlObjectStatementConfiguration cfg) { cfg.setReturner(() -> { OutParameters ou = c.invoke(); if (returnOutParams) { return ou; } else { return null; } }); } }
/** * Create a call to a stored procedure. * * @param sql the stored procedure sql * * @return the Call */ public Call createCall(String sql) { return new Call(this, sql); }
/** * Invoke the callable statement * @return the output parameters resulting from the invocation. */ public OutParameters invoke() { try { final PreparedStatement stmt = this.internalExecute(); OutParameters out = new OutParameters(); for (OutParamArgument param : params) { Object obj = param.map((CallableStatement) stmt); // convert from JDBC 1-based position to Jdbi's 0-based int index = param.position - 1; out.getMap().put(index, obj); if (param.name != null) { out.getMap().put(param.name, obj); } } return out; } finally { close(); } }
@Test @Ignore // TODO(scs): how do we test out parameters with h2? public void testWithNullReturn() { OutParameters ret = h.createCall("CALL TEST_PROCEDURE(?, ?)") .bind(0, (String) null) .registerOutParameter(1, Types.VARCHAR) .invoke(); String out = ret.getString(1); assertThat(out).isNull(); }
@Test public void testUnusedBindingWithOutParameter() { h.execute("CREATE ALIAS TO_DEGREES FOR \"java.lang.Math.toDegrees\""); Call call = h.createCall("? = CALL TO_DEGREES(?)") .registerOutParameter(0, Types.DOUBLE) .bind(1, 100.0d) .bind(2, "foo"); assertThatThrownBy(call::invoke).isInstanceOf(UnableToCreateStatementException.class); }
/** * Register a positional output parameter * @param position the parameter position (zero-based) * @param sqlType an SQL type constant as defined by {@link java.sql.Types} or by the JDBC vendor. * @param mapper a mapper which converts the {@link CallableStatement} to a desired output type. * @return self */ public Call registerOutParameter(int position, int sqlType, CallableStatementMapper mapper) { getBinding().addPositional(position, new OutParamArgument(sqlType, mapper, null)); return this; }
throw new UnableToExecuteStatementException("Could not get OUT parameter from statement", e, getContext());
@Test @Ignore // TODO(scs): how do we test out parameters with h2? public void testWithNullReturnWithNamedParam() { OutParameters ret = h.createCall("CALL TEST_PROCEDURE(:x, :y)") .bind("x", (String) null) .registerOutParameter("y", Types.VARCHAR) .invoke(); String out = ret.getString("y"); assertThat(out).isNull(); }
@Test public void testPermittedUnusedBindingWithOutParameter() { h.execute("CREATE ALIAS TO_DEGREES FOR \"java.lang.Math.toDegrees\""); Call call = h.configure(SqlStatements.class, stmts -> stmts.setUnusedBindingAllowed(true)) .createCall("? = CALL TO_DEGREES(?)") .registerOutParameter(0, Types.DOUBLE) .bind(1, 100.0d) .bind(2, "foo"); assertThatCode(call::invoke).doesNotThrowAnyException(); } }
/** * Invoke the callable statement * @return the output parameters resulting from the invocation. */ public OutParameters invoke() { try { final PreparedStatement stmt = this.internalExecute(); OutParameters out = new OutParameters(); for ( OutParamArgument param : params ) { Object obj = param.map((CallableStatement)stmt); // convert from JDBC 1-based position to Jdbi's 0-based int index = param.position - 1; out.getMap().put(index, obj); if ( param.name != null ) { out.getMap().put(param.name, obj); } } return out; } finally { close(); } }
/** * Register a named output parameter * @param name the parameter name * @param sqlType an SQL type constant as defined by {@link java.sql.Types} or by the JDBC vendor. * @param mapper a mapper which converts the {@link CallableStatement} to a desired output type. * @return self */ public Call registerOutParameter(String name, int sqlType, CallableStatementMapper mapper) { getBinding().addNamed(name, new OutParamArgument(sqlType, mapper, name)); return this; }
throw new UnableToExecuteStatementException("Could not get OUT parameter from statement", e, getContext());
@Test public void testCall() { Handle handle = db.getHandle(); handle.execute(findSqlOnClasspath("create_stored_proc_add")); // tag::invokeProcedure[] OutParameters result = handle .createCall("{:sum = call add(:a, :b)}") // <1> .bind("a", 13) // <2> .bind("b", 9) // <2> .registerOutParameter("sum", Types.INTEGER) // <3> <4> .invoke(); // <5> // end::invokeProcedure[] // tag::getOutParameters[] int sum = result.getInt("sum"); // end::getOutParameters[] assertThat(sum).isEqualTo(22); } }
/** * Register a positional output parameter * @param position the parameter position (zero-based) * @param sqlType an SQL type constant as defined by {@link java.sql.Types} or by the JDBC vendor. * @return self */ public Call registerOutParameter(int position, int sqlType) { return registerOutParameter(position, sqlType, null); }
/** * Register a positional output parameter * @param position the parameter position (zero-based) * @param sqlType an SQL type constant as defined by {@link java.sql.Types} or by the JDBC vendor. * @param mapper a mapper which converts the {@link CallableStatement} to a desired output type. * @return self */ public Call registerOutParameter(int position, int sqlType, CallableStatementMapper mapper) { getBinding().addPositional(position, new OutParamArgument(sqlType, mapper, null)); return this; }
/** * Create a call to a stored procedure. * * @param sql the stored procedure sql * * @return the Call */ public Call createCall(String sql) { return new Call(this, sql); }
@Test public void testStatementWithNamedParam() { OutParameters ret = h.createCall(":x = CALL TO_DEGREES(:y)") .registerOutParameter("x", Types.DOUBLE) .bind("y", 100.0d) .invoke(); Double expected = Math.toDegrees(100.0d); assertThat(ret.getDouble("x")).isEqualTo(expected, Offset.offset(0.001)); assertThat(ret.getLong("x").longValue()).isEqualTo(expected.longValue()); assertThat(ret.getShort("x").shortValue()).isEqualTo(expected.shortValue()); assertThat(ret.getInt("x").intValue()).isEqualTo(expected.intValue()); assertThat(ret.getFloat("x")).isEqualTo(expected.floatValue()); assertThatExceptionOfType(Exception.class).isThrownBy(() -> ret.getDate("x")); assertThatExceptionOfType(Exception.class).isThrownBy(() -> ret.getDate("y")); }
@Override public SqlStatementCustomizer createForMethod(Annotation annotation, Class<?> sqlObjectType, Method method) { final OutParameter outParam = (OutParameter) annotation; return stmt -> ((Call) stmt).registerOutParameter(outParam.name(), outParam.sqlType()); } }
/** * Register a named output parameter * @param name the parameter name * @param sqlType an SQL type constant as defined by {@link java.sql.Types} or by the JDBC vendor. * @param mapper a mapper which converts the {@link CallableStatement} to a desired output type. * @return self */ public Call registerOutParameter(String name, int sqlType, CallableStatementMapper mapper) { getBinding().addNamed(name, new OutParamArgument(sqlType, mapper, name)); return this; }