@Test public void testAddInvoiceProcWithMetaDataUsingArrayParams() throws Exception { initializeAddInvoiceWithMetaData(false); SimpleJdbcCall adder = new SimpleJdbcCall(dataSource).withProcedureName("add_invoice"); Number newId = adder.executeObject(Number.class, 1103, 3); assertEquals(4, newId.intValue()); verifyAddInvoiceWithMetaData(false); verify(connection, atLeastOnce()).close(); }
@Test public void testUnnamedParameterHandling() throws Exception { final String MY_PROC = "my_proc"; SimpleJdbcCall sproc = new SimpleJdbcCall(dataSource).withProcedureName(MY_PROC); // Shouldn't succeed in adding unnamed parameter thrown.expect(InvalidDataAccessApiUsageException.class); sproc.addDeclaredParameter(new SqlParameter(1)); }
@Test public void testCorrectProcedureStatementNamed() throws Exception { initializeAddInvoiceWithMetaData(false); SimpleJdbcCall adder = new SimpleJdbcCall(dataSource).withNamedBinding().withProcedureName("add_invoice"); adder.compile(); verifyStatement(adder, "{call ADD_INVOICE(AMOUNT => ?, CUSTID => ?, NEWID => ?)}"); }
@Test public void testAddInvoiceProcWithMetaDataUsingMapParamSource() throws Exception { initializeAddInvoiceWithMetaData(false); SimpleJdbcCall adder = new SimpleJdbcCall(dataSource).withProcedureName("add_invoice"); Number newId = adder.executeObject(Number.class, new MapSqlParameterSource() .addValue("amount", 1103) .addValue("custid", 3)); assertEquals(4, newId.intValue()); verifyAddInvoiceWithMetaData(false); verify(connection, atLeastOnce()).close(); }
@Test public void testAddInvoiceProcWithoutMetaDataUsingArrayParams() throws Exception { initializeAddInvoiceWithoutMetaData(false); SimpleJdbcCall adder = new SimpleJdbcCall(dataSource).withProcedureName("add_invoice"); adder.declareParameters( new SqlParameter("amount", Types.INTEGER), new SqlParameter("custid", Types.INTEGER), new SqlOutParameter("newid", Types.INTEGER)); Number newId = adder.executeObject(Number.class, 1103, 3); assertEquals(4, newId.intValue()); verifyAddInvoiceWithoutMetaData(false); verify(connection, atLeastOnce()).close(); }
@Test public void testAddInvoiceProcWithoutMetaDataUsingMapParamSource() throws Exception { initializeAddInvoiceWithoutMetaData(false); SimpleJdbcCall adder = new SimpleJdbcCall(dataSource).withProcedureName("add_invoice"); adder.declareParameters( new SqlParameter("amount", Types.INTEGER), new SqlParameter("custid", Types.INTEGER), new SqlOutParameter("newid", Types.INTEGER)); Number newId = adder.executeObject(Number.class, new MapSqlParameterSource(). addValue("amount", 1103). addValue("custid", 3)); assertEquals(4, newId.intValue()); verifyAddInvoiceWithoutMetaData(false); verify(connection, atLeastOnce()).close(); }
@Test public void testNoSuchStoredProcedure() throws Exception { final String NO_SUCH_PROC = "x"; SQLException sqlException = new SQLException("Syntax error or access violation exception", "42000"); given(databaseMetaData.getDatabaseProductName()).willReturn("MyDB"); given(databaseMetaData.getDatabaseProductName()).willReturn("MyDB"); given(databaseMetaData.getUserName()).willReturn("me"); given(databaseMetaData.storesLowerCaseIdentifiers()).willReturn(true); given(callableStatement.execute()).willThrow(sqlException); given(connection.prepareCall("{call " + NO_SUCH_PROC + "()}")).willReturn(callableStatement); SimpleJdbcCall sproc = new SimpleJdbcCall(dataSource).withProcedureName(NO_SUCH_PROC); thrown.expect(BadSqlGrammarException.class); thrown.expect(exceptionCause(sameInstance(sqlException))); try { sproc.execute(); } finally { verify(callableStatement).close(); verify(connection, atLeastOnce()).close(); } }
private SimpleJdbcCall createSimpleJdbcCall(String storedProcedureName) { final SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(this.dataSource); if (this.isFunction) { simpleJdbcCall.withFunctionName(storedProcedureName); } else { simpleJdbcCall.withProcedureName(storedProcedureName); } if (this.ignoreColumnMetaData) { simpleJdbcCall.withoutProcedureColumnMetaDataAccess(); } simpleJdbcCall.declareParameters(this.sqlParameters.toArray(new SqlParameter[this.sqlParameters.size()])); if (!this.returningResultSetRowMappers.isEmpty()) { for (Entry<String, RowMapper<?>> mapEntry : this.returningResultSetRowMappers.entrySet()) { simpleJdbcCall.returningResultSet(mapEntry.getKey(), mapEntry.getValue()); } } if (this.returnValueRequired) { simpleJdbcCall.withReturnValue(); } simpleJdbcCall.getJdbcTemplate().setSkipUndeclaredResults(this.skipUndeclaredResults); return simpleJdbcCall; }
@Override public void callProcedure(String procedureName, Map<String, Object> inParameters) { jdbcCall = jdbcCall.withProcedureName(procedureName); logger.info("-- SQL参数:[{}]", inParameters); jdbcCall.execute(inParameters); }
@Override public void callProcedure(String procedureName) { jdbcCall = jdbcCall.withProcedureName(procedureName); jdbcCall.execute(); }
@Override public Map<String, Object> callProcedureQueryOut(String procedureName, Map<String, Object> inParameters) { jdbcCall = jdbcCall.withProcedureName(procedureName); logger.info("-- SQL参数:[{}]", inParameters); return jdbcCall.execute(inParameters); }
CallCreator createCallForModification(final String procedureName) { return () -> new SimpleJdbcCall(jdbcTemplate).withProcedureName(procedureName); }
@Override public List callProcedureQueryListBeans(String procedureName, Map<String, Object> inParameters, Class<?> outBeansType) { jdbcCall = jdbcCall.withProcedureName(procedureName); if (outBeansType != null) { jdbcCall = jdbcCall.returningResultSet("list_beans", generateRowMapper(outBeansType)); } List list = (List) jdbcCall.execute(inParameters).get("list_beans"); logger.info("-- SQL参数:[{}]", inParameters); logger.info("-- 响应条目:[{}]", list.size()); return list; }
/*** * CheckDBConnection calls a simple "select 1" SP to verify DB is up & running. * * @return True if DB is up & running. */ public boolean checkDBConnection() { return new SimpleJdbcCall(jdbcTemplate).withProcedureName("CheckDBConnection").execute() != null; }
/** * This call will populate a translation table of OS Ids to they're name.<br/> * The translation table shall be in use by DWH. * * @param osIdToName * OS id to OS Name map */ @Override public void populateDwhOsInfo(Map<Integer, String> osIdToName) { // first clear the table simpleJdbcCall.withProcedureName("clear_osinfo").execute(); // batch populate List<MapSqlParameterSource> executions = osIdToName.entrySet() .stream() .map(e -> sqlParameterSourceProvider.get() .addValue("os_id", e.getKey()) .addValue("os_name", e.getValue())) .collect(Collectors.toList()); callsHandler.executeStoredProcAsBatch("insert_osinfo", executions); }
SimpleJdbcCall simpleJdbcCall = new SimpleJdbcCall(dataSource); simpleJdbcCall.withCatalogName("dbo"); simpleJdbcCall.withProcedureName("procedureA "); simpleJdbcCall.setAccessCallParameterMetaData(false); simpleJdbcCall.declareParameters(new new SqlOutParameter("a",Types.NUMERIC)); simpleJdbcCall.execute();
@Override public void updateLastAdminCheckStatus(Guid... userIds) { MapSqlParameterSource parameterSource = getCustomMapSqlParameterSource().addValue("userIds", StringUtils.join(userIds, ",")); new SimpleJdbcCall(getJdbcTemplate()).withProcedureName("UpdateLastAdminCheckStatus").execute(parameterSource); }
@Override public void save(DbUser user) { setIdIfNeeded(user); new SimpleJdbcCall(getJdbcTemplate()).withProcedureName("InsertUser") .execute(new DbUserMapSqlParameterSource(user)); }
@Override public void saveOrUpdate(DbUser user) { setIdIfNeeded(user); new SimpleJdbcCall(getJdbcTemplate()).withProcedureName("InsertOrUpdateUser") .execute(new DbUserMapSqlParameterSource(user)); }
@Override public void save(VdsStatic vds) { Guid id = vds.getId(); if (Guid.isNullOrEmpty(id)) { id = Guid.newGuid(); vds.setId(id); } new SimpleJdbcCall(getJdbcTemplate()).withProcedureName("InsertVdsStatic") .execute(getInsertOrUpdateParams(vds)); }