@Override public ResultSet getResultSet(CallableStatement ps) throws SQLException { boolean isResultSet = ps.execute(); while ( !isResultSet && ps.getUpdateCount() != -1 ) { isResultSet = ps.getMoreResults(); } return ps.getResultSet(); }
@Override public ResultSet getResultSet(CallableStatement ps) throws SQLException { boolean isResultSet = ps.execute(); // This assumes you will want to ignore any update counts while (!isResultSet && ps.getUpdateCount() != -1) { isResultSet = ps.getMoreResults(); } return ps.getResultSet(); } }
@Override public ResultSet getResultSet(CallableStatement cs) throws SQLException { boolean isResultSet = cs.execute(); while ( !isResultSet && cs.getUpdateCount() != -1 ) { isResultSet = cs.getMoreResults(); } return cs.getResultSet(); }
@Test public void testNumericWithScale() throws Exception { given(callableStatement.execute()).willReturn(false); given(callableStatement.getUpdateCount()).willReturn(-1); given(callableStatement.getObject(1)).willReturn(new BigDecimal("12345.6789")); given(connection.prepareCall("{call " + NumericWithScaleStoredProcedure.SQL + "(?)}") ).willReturn(callableStatement); NumericWithScaleStoredProcedure nwssp = new NumericWithScaleStoredProcedure(dataSource); Map<String, Object> out = nwssp.executeTest(); assertEquals(new BigDecimal("12345.6789"), out.get("out")); verify(callableStatement).registerOutParameter(1, Types.DECIMAL, 4); }
@Test public void testStoredProcedureSkippingResultsProcessing() throws Exception { given(callableStatement.execute()).willReturn(true); given(callableStatement.getUpdateCount()).willReturn(-1); given(connection.prepareCall("{call " + StoredProcedureWithResultSetMapped.SQL + "()}") ).willReturn(callableStatement); JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource); jdbcTemplate.setSkipResultsProcessing(true); StoredProcedureWithResultSetMapped sproc = new StoredProcedureWithResultSetMapped( jdbcTemplate); Map<String, Object> res = sproc.execute(); assertEquals("incorrect number of returns", 0, res.size()); }
@Test public void testSqlTypeValue() throws Exception { int[] testVal = new int[] { 1, 2 }; given(callableStatement.execute()).willReturn(false); given(callableStatement.getUpdateCount()).willReturn(-1); given(callableStatement.getObject(2)).willReturn("OK"); given(connection.prepareCall("{call " + SqlTypeValueStoredProcedure.SQL + "(?, ?)}") ).willReturn(callableStatement); SqlTypeValueStoredProcedure stvsp = new SqlTypeValueStoredProcedure(dataSource); Map<String, Object> out = stvsp.executeTest(testVal); assertEquals("OK", out.get("out")); verify(callableStatement).setObject(1, testVal, Types.ARRAY); verify(callableStatement).registerOutParameter(2, Types.VARCHAR); }
@Test public void testExecuteClosed() throws Exception { given(this.resultSet.next()).willReturn(true); given(this.callableStatement.execute()).willReturn(true); given(this.callableStatement.getUpdateCount()).willReturn(-1); SqlParameter param = new SqlReturnResultSet("", (RowCallbackHandler) rs -> { throw new InvalidDataAccessApiUsageException(""); }); this.thrown.expect(InvalidDataAccessApiUsageException.class); try { this.template.call(conn -> conn.prepareCall("my query"), Collections.singletonList(param)); } finally { verify(this.resultSet).close(); verify(this.callableStatement).close(); verify(this.connection).close(); } }
@Test public void testAddInvoicesUsingObjectArray() throws Exception { given(callableStatement.execute()).willReturn(false); given(callableStatement.getUpdateCount()).willReturn(-1); given(callableStatement.getObject(3)).willReturn(5); given(connection.prepareCall("{call " + AddInvoice.SQL + "(?, ?, ?)}") ).willReturn(callableStatement); testAddInvoiceUsingObjectArray(1106, 4); verify(callableStatement).setObject(1, 1106, Types.INTEGER); verify(callableStatement).setObject(2, 4, Types.INTEGER); verify(callableStatement).registerOutParameter(3, Types.INTEGER); }
@Test public void testParameterMapper() throws Exception { given(callableStatement.execute()).willReturn(false); given(callableStatement.getUpdateCount()).willReturn(-1); given(callableStatement.getObject(2)).willReturn("OK"); given(connection.prepareCall("{call " + ParameterMapperStoredProcedure.SQL + "(?, ?)}") ).willReturn(callableStatement); ParameterMapperStoredProcedure pmsp = new ParameterMapperStoredProcedure(dataSource); Map<String, Object> out = pmsp.executeTest(); assertEquals("OK", out.get("out")); verify(callableStatement).setString(eq(1), startsWith("Mock for Connection")); verify(callableStatement).registerOutParameter(2, Types.VARCHAR); }
@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(); }
@Test @SuppressWarnings("unchecked") public void testStoredProcedureSkippingUndeclaredResults() throws Exception { ResultSet resultSet = mock(ResultSet.class); given(resultSet.next()).willReturn(true, true, false); given(resultSet.getString(2)).willReturn("Foo", "Bar"); given(callableStatement.execute()).willReturn(true); given(callableStatement.getUpdateCount()).willReturn(-1); given(callableStatement.getResultSet()).willReturn(resultSet); given(callableStatement.getMoreResults()).willReturn(true, false); given(callableStatement.getUpdateCount()).willReturn(-1, -1); given(connection.prepareCall("{call " + StoredProcedureWithResultSetMapped.SQL + "()}") ).willReturn(callableStatement); JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource); jdbcTemplate.setSkipUndeclaredResults(true); StoredProcedureWithResultSetMapped sproc = new StoredProcedureWithResultSetMapped( jdbcTemplate); Map<String, Object> res = sproc.execute(); assertEquals("incorrect number of returns", 1, res.size()); List<String> rs1 = (List<String>) res.get("rs"); assertEquals(2, rs1.size()); assertEquals("Foo", rs1.get(0)); assertEquals("Bar", rs1.get(1)); verify(resultSet).close(); }
@Test @SuppressWarnings("unchecked") public void testStoredProcedureWithResultSetMapped() throws Exception { ResultSet resultSet = mock(ResultSet.class); given(resultSet.next()).willReturn(true, true, false); given(resultSet.getString(2)).willReturn("Foo", "Bar"); given(callableStatement.execute()).willReturn(true); given(callableStatement.getUpdateCount()).willReturn(-1); given(callableStatement.getResultSet()).willReturn(resultSet); given(callableStatement.getMoreResults()).willReturn(false); given(callableStatement.getUpdateCount()).willReturn(-1); given(connection.prepareCall("{call " + StoredProcedureWithResultSetMapped.SQL + "()}") ).willReturn(callableStatement); StoredProcedureWithResultSetMapped sproc = new StoredProcedureWithResultSetMapped(dataSource); Map<String, Object> res = sproc.execute(); List<String> rs = (List<String>) res.get("rs"); assertEquals(2, rs.size()); assertEquals("Foo", rs.get(0)); assertEquals("Bar", rs.get(1)); verify(resultSet).close(); }
@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(); }
@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); }
/** * Confirm our JdbcTemplate is used * * @throws Exception */ @Test public void testStoredProcedureConfiguredViaJdbcTemplate() throws Exception { given(callableStatement.execute()).willReturn(false); given(callableStatement.getUpdateCount()).willReturn(-1); given(callableStatement.getObject(2)).willReturn(4); given(connection.prepareCall("{call " + StoredProcedureConfiguredViaJdbcTemplate.SQL + "(?, ?)}") ).willReturn(callableStatement); JdbcTemplate t = new JdbcTemplate(); t.setDataSource(dataSource); StoredProcedureConfiguredViaJdbcTemplate sp = new StoredProcedureConfiguredViaJdbcTemplate(t); assertEquals(4, sp.execute(1106)); verify(callableStatement).setObject(1, 1106, Types.INTEGER); verify(callableStatement).registerOutParameter(2, Types.INTEGER); }
@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); }
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); } }
@Test public void testAddInvoices() throws Exception { DefaultListableBeanFactory bf = new DefaultListableBeanFactory(); new XmlBeanDefinitionReader(bf).loadBeanDefinitions( new ClassPathResource("org/springframework/jdbc/object/GenericStoredProcedureTests-context.xml")); Connection connection = mock(Connection.class); DataSource dataSource = mock(DataSource.class); given(dataSource.getConnection()).willReturn(connection); CallableStatement callableStatement = mock(CallableStatement.class); TestDataSourceWrapper testDataSource = (TestDataSourceWrapper) bf.getBean("dataSource"); testDataSource.setTarget(dataSource); given(callableStatement.execute()).willReturn(false); given(callableStatement.getUpdateCount()).willReturn(-1); given(callableStatement.getObject(3)).willReturn(4); given(connection.prepareCall("{call " + "add_invoice" + "(?, ?, ?)}")).willReturn(callableStatement); StoredProcedure adder = (StoredProcedure) bf.getBean("genericProcedure"); Map<String, Object> in = new HashMap<>(2); in.put("amount", 1106); in.put("custid", 3); Map<String, Object> out = adder.execute(in); Integer id = (Integer) out.get("newid"); assertEquals(4, id.intValue()); verify(callableStatement).setObject(1, 1106, Types.INTEGER); verify(callableStatement).setObject(2, 3, Types.INTEGER); verify(callableStatement).registerOutParameter(3, Types.INTEGER); verify(callableStatement).close(); }
@Test public void testAddInvoicesWithinTransaction() 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); TransactionSynchronizationManager.bindResource(dataSource, new ConnectionHolder(connection)); try { testAddInvoice(1106, 3); verify(callableStatement).setObject(1, 1106, Types.INTEGER); verify(callableStatement).setObject(2, 3, Types.INTEGER); verify(callableStatement).registerOutParameter(3, Types.INTEGER); verify(connection, never()).close(); } finally { TransactionSynchronizationManager.unbindResource(dataSource); connection.close(); } }
private void initializeAddInvoiceWithMetaData(boolean isFunction) throws SQLException { ResultSet proceduresResultSet = mock(ResultSet.class); ResultSet procedureColumnsResultSet = mock(ResultSet.class); given(databaseMetaData.getDatabaseProductName()).willReturn("Oracle"); given(databaseMetaData.getUserName()).willReturn("ME"); given(databaseMetaData.storesUpperCaseIdentifiers()).willReturn(true); given(databaseMetaData.getProcedures("", "ME", "ADD_INVOICE")).willReturn(proceduresResultSet); given(databaseMetaData.getProcedureColumns("", "ME", "ADD_INVOICE", null)).willReturn(procedureColumnsResultSet); given(proceduresResultSet.next()).willReturn(true, false); given(proceduresResultSet.getString("PROCEDURE_NAME")).willReturn("add_invoice"); given(procedureColumnsResultSet.next()).willReturn(true, true, true, false); given(procedureColumnsResultSet.getInt("DATA_TYPE")).willReturn(4); if (isFunction) { given(procedureColumnsResultSet.getString("COLUMN_NAME")).willReturn(null,"amount", "custid"); given(procedureColumnsResultSet.getInt("COLUMN_TYPE")).willReturn(5, 1, 1); given(connection.prepareCall("{? = call ADD_INVOICE(?, ?)}")).willReturn(callableStatement); given(callableStatement.getObject(1)).willReturn(4L); } else { given(procedureColumnsResultSet.getString("COLUMN_NAME")).willReturn("amount", "custid", "newid"); given(procedureColumnsResultSet.getInt("COLUMN_TYPE")).willReturn(1, 1, 4); given(connection.prepareCall("{call ADD_INVOICE(?, ?, ?)}")).willReturn(callableStatement); given(callableStatement.getObject(3)).willReturn(4L); } given(callableStatement.getUpdateCount()).willReturn(-1); }