public static String executeWithReturnValue(String url, String serviceName, String json) { try (Connection conn = DriverManager.getConnection(url); CallableStatement stmt = conn.prepareCall(sqlWithReturnValue)) { stmt.setString(2, serviceName); stmt.setString(3, json); stmt.registerOutParameter(1, java.sql.Types.VARCHAR); if (stmt.execute()) { return stmt.getString(1); } } catch (SQLException e) { throw new RuntimeException("Failed to execute service: " + serviceName, e); } return null; }
@Nullable private static String lookupDefaultSchema(DatabaseMetaData databaseMetaData) { try { CallableStatement cstmt = null; try { Connection con = databaseMetaData.getConnection(); if (con == null) { logger.debug("Cannot check default schema - no Connection from DatabaseMetaData"); return null; } cstmt = con.prepareCall("{? = call sys_context('USERENV', 'CURRENT_SCHEMA')}"); cstmt.registerOutParameter(1, Types.VARCHAR); cstmt.execute(); return cstmt.getString(1); } finally { if (cstmt != null) { cstmt.close(); } } } catch (SQLException ex) { logger.debug("Exception encountered during default schema lookup", ex); return null; } }
@Test public void testAddInvoices() throws Exception { given(callableStatement.execute()).willReturn(false); given(callableStatement.getUpdateCount()).willReturn(-1); given(callableStatement.getObject(3)).willReturn(4); given(connection.prepareCall("{call " + AddInvoice.SQL + "(?, ?, ?)}") ).willReturn(callableStatement); testAddInvoice(1106, 3); verify(callableStatement).setObject(1, 1106, Types.INTEGER); verify(callableStatement).setObject(2, 3, Types.INTEGER); verify(callableStatement).registerOutParameter(3, Types.INTEGER); }
@Test public void testStoredProcedureReturnValue() { doInJPA( this::entityManagerFactory, entityManager -> { Session session = entityManager.unwrap( Session.class ); session.doWork( connection -> { 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(); } } } ); } ); } }
callableStatement.registerOutParameter(1, returnType); callableStatement.setString(2, objectType); callableStatement.setString(3, name); callableStatement.setString(4, schema); callableStatement.executeUpdate(); result = callableStatement.getObject(1);
@Override public String getString(int parameterIndex) throws SQLException { try { return stmt.getString(parameterIndex); } catch (Throwable t) { throw checkException(t); } }
@Test public void testCaseInsensitiveResultsMap() throws Exception { given(this.callableStatement.execute()).willReturn(false); given(this.callableStatement.getUpdateCount()).willReturn(-1); given(this.callableStatement.getObject(1)).willReturn("X"); assertTrue("default should have been NOT case insensitive", !this.template.isResultsMapCaseInsensitive()); this.template.setResultsMapCaseInsensitive(true); assertTrue("now it should have been set to case insensitive", this.template.isResultsMapCaseInsensitive()); Map<String, Object> out = this.template.call( conn -> conn.prepareCall("my query"), Collections.singletonList(new SqlOutParameter("a", 12))); assertThat(out, instanceOf(LinkedCaseInsensitiveMap.class)); assertNotNull("we should have gotten the result with upper case", out.get("A")); assertNotNull("we should have gotten the result with lower case", out.get("a")); verify(this.callableStatement).close(); verify(this.connection).close(); }
@Override public void registerOutParameter(int parameterIndex, int sqlType, String typeName) throws SQLException { try { stmt.registerOutParameter(parameterIndex, sqlType, typeName); } catch (Throwable t) { throw checkException(t); } }
private void verifyAddInvoiceWithoutMetaData(boolean isFunction) throws SQLException { if (isFunction) { verify(callableStatement).registerOutParameter(1, 4); verify(callableStatement).setObject(2, 1103, 4); verify(callableStatement).setObject(3, 3, 4); } else { verify(callableStatement).setObject(1, 1103, 4); verify(callableStatement).setObject(2, 3, 4); verify(callableStatement).registerOutParameter(3, 4); } verify(callableStatement).close(); }
String sql = "BEGIN INSERT INTO mytable(id, content) VALUES (seq_mytable.NEXTVAL(), ?) RETURNING id INTO ?; END;"; Connection connection = null; CallableStatement statement = null; try { connection = database.getConnection(); statement = connection.prepareCall(sql); statement.setString(1, "test"); statement.registerOutParameter(2, Types.NUMERIC); statement.execute(); int id = statement.getInt(2); // ...
private void initializeAddInvoiceWithoutMetaData(boolean isFunction) throws SQLException { given(databaseMetaData.getDatabaseProductName()).willReturn("MyDB"); given(databaseMetaData.getUserName()).willReturn("me"); given(databaseMetaData.storesLowerCaseIdentifiers()).willReturn(true); given(callableStatement.execute()).willReturn(false); given(callableStatement.getUpdateCount()).willReturn(-1); if (isFunction) { given(callableStatement.getObject(1)).willReturn(4L); given(connection.prepareCall("{? = call add_invoice(?, ?)}") ).willReturn(callableStatement); } else { given(callableStatement.getObject(3)).willReturn(4L); given(connection.prepareCall("{call add_invoice(?, ?, ?)}") ).willReturn(callableStatement); } }
@Override public int getInt(int parameterIndex) throws SQLException { try { return stmt.getInt(parameterIndex); } catch (Throwable t) { throw checkException(t); } }
@Override public Timestamp getTimestamp(int parameterIndex, Calendar cal) throws SQLException { return delegate.getTimestamp(parameterIndex, cal); }
public static void executeNoReturnValue(String url, String serviceName, String json) { try (Connection conn = DriverManager.getConnection(url); CallableStatement stmt = conn.prepareCall(sqlNoReturnValue)) { stmt.setString(1, serviceName); stmt.setString(2, json); stmt.execute(); } catch (SQLException e) { throw new RuntimeException("Failed to execute service: " + serviceName, e); } }
@Override public Date getDate(String parameterName) throws SQLException { return delegate.getDate(parameterName); }
@Override public Object getObject(String parameterName) throws SQLException { try { Object obj = stmt.getObject(parameterName); return wrapObject(obj); } catch (Throwable t) { throw checkException(t); } }
@Test public void testNullArg() throws Exception { given(callableStatement.execute()).willReturn(false); given(callableStatement.getUpdateCount()).willReturn(-1); given(connection.prepareCall("{call " + NullArg.SQL + "(?)}")).willReturn(callableStatement); NullArg na = new NullArg(dataSource); na.execute((String) null); callableStatement.setNull(1, Types.VARCHAR); }
@Test public void testStoredProcedureWithResultSet() throws Exception { ResultSet resultSet = mock(ResultSet.class); given(resultSet.next()).willReturn(true, true, false); given(callableStatement.execute()).willReturn(true); given(callableStatement.getUpdateCount()).willReturn(-1); given(callableStatement.getResultSet()).willReturn(resultSet); given(callableStatement.getUpdateCount()).willReturn(-1); given(connection.prepareCall("{call " + StoredProcedureWithResultSet.SQL + "()}") ).willReturn(callableStatement); StoredProcedureWithResultSet sproc = new StoredProcedureWithResultSet(dataSource); sproc.execute(); assertEquals(2, sproc.getCount()); verify(resultSet).close(); }