Java Code Examples for org.springframework.jdbc.core.SqlOutParameter

The following examples show how to use org.springframework.jdbc.core.SqlOutParameter. These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source Project: spring-analysis-note   Source File: CallMetaDataContext.java    License: MIT License 6 votes vote down vote up
/**
 * Create a ReturnResultSetParameter/SqlOutParameter depending on the support provided
 * by the JDBC driver used for the database in use.
 * @param parameterName the name of the parameter (also used as the name of the List returned in the output)
 * @param rowMapper a RowMapper implementation used to map the data returned in the result set
 * @return the appropriate SqlParameter
 */
public SqlParameter createReturnResultSetParameter(String parameterName, RowMapper<?> rowMapper) {
	CallMetaDataProvider provider = obtainMetaDataProvider();
	if (provider.isReturnResultSetSupported()) {
		return new SqlReturnResultSet(parameterName, rowMapper);
	}
	else {
		if (provider.isRefCursorSupported()) {
			return new SqlOutParameter(parameterName, provider.getRefCursorSqlType(), rowMapper);
		}
		else {
			throw new InvalidDataAccessApiUsageException(
					"Return of a ResultSet from a stored procedure is not supported");
		}
	}
}
 
Example 2
Source Project: java-technology-stack   Source File: CallMetaDataContext.java    License: MIT License 6 votes vote down vote up
/**
 * Create a ReturnResultSetParameter/SqlOutParameter depending on the support provided
 * by the JDBC driver used for the database in use.
 * @param parameterName the name of the parameter (also used as the name of the List returned in the output)
 * @param rowMapper a RowMapper implementation used to map the data returned in the result set
 * @return the appropriate SqlParameter
 */
public SqlParameter createReturnResultSetParameter(String parameterName, RowMapper<?> rowMapper) {
	CallMetaDataProvider provider = obtainMetaDataProvider();
	if (provider.isReturnResultSetSupported()) {
		return new SqlReturnResultSet(parameterName, rowMapper);
	}
	else {
		if (provider.isRefCursorSupported()) {
			return new SqlOutParameter(parameterName, provider.getRefCursorSqlType(), rowMapper);
		}
		else {
			throw new InvalidDataAccessApiUsageException(
					"Return of a ResultSet from a stored procedure is not supported");
		}
	}
}
 
Example 3
Source Project: effectivejava   Source File: SimpleJdbcCallTests.java    License: Apache License 2.0 6 votes vote down vote up
@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();
}
 
Example 4
Source Project: spring-analysis-note   Source File: OracleCallMetaDataProvider.java    License: MIT License 5 votes vote down vote up
@Override
public SqlParameter createDefaultOutParameter(String parameterName, CallParameterMetaData meta) {
	if (meta.getSqlType() == Types.OTHER && REF_CURSOR_NAME.equals(meta.getTypeName())) {
		return new SqlOutParameter(parameterName, getRefCursorSqlType(), new ColumnMapRowMapper());
	}
	else {
		return super.createDefaultOutParameter(parameterName, meta);
	}
}
 
Example 5
@Override
public SqlParameter createDefaultOutParameter(String parameterName, CallParameterMetaData meta) {
	if (meta.getSqlType() == Types.OTHER && "refcursor".equals(meta.getTypeName())) {
		return new SqlOutParameter(parameterName, getRefCursorSqlType(), new ColumnMapRowMapper());
	}
	else {
		return super.createDefaultOutParameter(parameterName, meta);
	}
}
 
Example 6
Source Project: spring-analysis-note   Source File: StoredProcedureTests.java    License: MIT License 5 votes vote down vote up
public StoredProcedureConfiguredViaJdbcTemplate(JdbcTemplate t) {
	setJdbcTemplate(t);
	setSql(SQL);
	declareParameter(new SqlParameter("intIn", Types.INTEGER));
	declareParameter(new SqlOutParameter("intOut", Types.INTEGER));
	compile();
}
 
Example 7
Source Project: spring-analysis-note   Source File: StoredProcedureTests.java    License: MIT License 5 votes vote down vote up
public AddInvoice(DataSource ds) {
	setDataSource(ds);
	setSql(SQL);
	declareParameter(new SqlParameter("amount", Types.INTEGER));
	declareParameter(new SqlParameter("custid", Types.INTEGER));
	declareParameter(new SqlOutParameter("newid", Types.INTEGER));
	compile();
}
 
Example 8
Source Project: spring-analysis-note   Source File: StoredProcedureTests.java    License: MIT License 5 votes vote down vote up
public AddInvoiceUsingObjectArray(DataSource ds) {
	setDataSource(ds);
	setSql(SQL);
	declareParameter(new SqlParameter("amount", Types.INTEGER));
	declareParameter(new SqlParameter("custid", Types.INTEGER));
	declareParameter(new SqlOutParameter("newid", Types.INTEGER));
	compile();
}
 
Example 9
Source Project: spring-analysis-note   Source File: StoredProcedureTests.java    License: MIT License 5 votes vote down vote up
public ParameterMapperStoredProcedure(DataSource ds) {
	setDataSource(ds);
	setSql(SQL);
	declareParameter(new SqlParameter("in", Types.VARCHAR));
	declareParameter(new SqlOutParameter("out", Types.VARCHAR));
	compile();
}
 
Example 10
Source Project: spring-analysis-note   Source File: StoredProcedureTests.java    License: MIT License 5 votes vote down vote up
public SqlTypeValueStoredProcedure(DataSource ds) {
	setDataSource(ds);
	setSql(SQL);
	declareParameter(new SqlParameter("in", Types.ARRAY, "NUMBERS"));
	declareParameter(new SqlOutParameter("out", Types.VARCHAR));
	compile();
}
 
Example 11
Source Project: spring-analysis-note   Source File: RdbmsOperationTests.java    License: MIT License 5 votes vote down vote up
@Test
public void validateInOutParameter() {
	operation.setDataSource(new DriverManagerDataSource());
	operation.setSql("DUMMY_PROC");
	operation.declareParameter(new SqlOutParameter("DUMMY_OUT_PARAM", Types.VARCHAR));
	operation.declareParameter(new SqlInOutParameter("DUMMY_IN_OUT_PARAM", Types.VARCHAR));
	operation.validateParameters(new Object[] {"DUMMY_VALUE1", "DUMMY_VALUE2"});
}
 
Example 12
Source Project: spring-analysis-note   Source File: SimpleJdbcCallTests.java    License: MIT License 5 votes vote down vote up
@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();
}
 
Example 13
Source Project: spring-analysis-note   Source File: SimpleJdbcCallTests.java    License: MIT License 5 votes vote down vote up
@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();
}
 
Example 14
Source Project: spring-analysis-note   Source File: SimpleJdbcCallTests.java    License: MIT License 5 votes vote down vote up
@Test
public void testAddInvoiceFuncWithoutMetaDataUsingMapParamSource() throws Exception {
	initializeAddInvoiceWithoutMetaData(true);
	SimpleJdbcCall adder = new SimpleJdbcCall(dataSource).withFunctionName("add_invoice");
	adder.declareParameters(
			new SqlOutParameter("return", Types.INTEGER),
			new SqlParameter("amount", Types.INTEGER),
			new SqlParameter("custid", Types.INTEGER));
	Number newId = adder.executeFunction(Number.class, new MapSqlParameterSource()
			.addValue("amount", 1103)
			.addValue("custid", 3));
	assertEquals(4, newId.intValue());
	verifyAddInvoiceWithoutMetaData(true);
	verify(connection, atLeastOnce()).close();
}
 
Example 15
Source Project: spring-analysis-note   Source File: SimpleJdbcCallTests.java    License: MIT License 5 votes vote down vote up
@Test
public void testAddInvoiceFuncWithoutMetaDataUsingArrayParams() throws Exception {
	initializeAddInvoiceWithoutMetaData(true);
	SimpleJdbcCall adder = new SimpleJdbcCall(dataSource).withFunctionName("add_invoice");
	adder.declareParameters(
			new SqlOutParameter("return", Types.INTEGER),
			new SqlParameter("amount", Types.INTEGER),
			new SqlParameter("custid", Types.INTEGER));
	Number newId = adder.executeFunction(Number.class, 1103, 3);
	assertEquals(4, newId.intValue());
	verifyAddInvoiceWithoutMetaData(true);
	verify(connection, atLeastOnce()).close();
}
 
Example 16
Source Project: spring-analysis-note   Source File: CallMetaDataContextTests.java    License: MIT License 5 votes vote down vote up
@Test
public void testMatchParameterValuesAndSqlInOutParameters() throws Exception {
	final String TABLE = "customers";
	final String USER = "me";
	given(databaseMetaData.getDatabaseProductName()).willReturn("MyDB");
	given(databaseMetaData.getUserName()).willReturn(USER);
	given(databaseMetaData.storesLowerCaseIdentifiers()).willReturn(true);

	List<SqlParameter> parameters = new ArrayList<>();
	parameters.add(new SqlParameter("id", Types.NUMERIC));
	parameters.add(new SqlInOutParameter("name", Types.NUMERIC));
	parameters.add(new SqlOutParameter("customer_no", Types.NUMERIC));

	MapSqlParameterSource parameterSource = new MapSqlParameterSource();
	parameterSource.addValue("id", 1);
	parameterSource.addValue("name", "Sven");
	parameterSource.addValue("customer_no", "12345XYZ");

	context.setProcedureName(TABLE);
	context.initializeMetaData(dataSource);
	context.processParameters(parameters);

	Map<String, Object> inParameters = context.matchInParameterValuesWithCallParameters(parameterSource);
	assertEquals("Wrong number of matched in parameter values", 2, inParameters.size());
	assertTrue("in parameter value missing", inParameters.containsKey("id"));
	assertTrue("in out parameter value missing", inParameters.containsKey("name"));
	assertTrue("out parameter value matched", !inParameters.containsKey("customer_no"));

	List<String> names = context.getOutParameterNames();
	assertEquals("Wrong number of out parameters", 2, names.size());

	List<SqlParameter> callParameters = context.getCallParameters();
	assertEquals("Wrong number of call parameters", 3, callParameters.size());
}
 
Example 17
Source Project: java-technology-stack   Source File: OracleCallMetaDataProvider.java    License: MIT License 5 votes vote down vote up
@Override
public SqlParameter createDefaultOutParameter(String parameterName, CallParameterMetaData meta) {
	if (meta.getSqlType() == Types.OTHER && REF_CURSOR_NAME.equals(meta.getTypeName())) {
		return new SqlOutParameter(parameterName, getRefCursorSqlType(), new ColumnMapRowMapper());
	}
	else {
		return super.createDefaultOutParameter(parameterName, meta);
	}
}
 
Example 18
@Override
public SqlParameter createDefaultOutParameter(String parameterName, CallParameterMetaData meta) {
	if (meta.getSqlType() == Types.OTHER && "refcursor".equals(meta.getTypeName())) {
		return new SqlOutParameter(parameterName, getRefCursorSqlType(), new ColumnMapRowMapper());
	}
	else {
		return super.createDefaultOutParameter(parameterName, meta);
	}
}
 
Example 19
Source Project: java-technology-stack   Source File: StoredProcedureTests.java    License: MIT License 5 votes vote down vote up
public StoredProcedureConfiguredViaJdbcTemplate(JdbcTemplate t) {
	setJdbcTemplate(t);
	setSql(SQL);
	declareParameter(new SqlParameter("intIn", Types.INTEGER));
	declareParameter(new SqlOutParameter("intOut", Types.INTEGER));
	compile();
}
 
Example 20
Source Project: java-technology-stack   Source File: StoredProcedureTests.java    License: MIT License 5 votes vote down vote up
public AddInvoice(DataSource ds) {
	setDataSource(ds);
	setSql(SQL);
	declareParameter(new SqlParameter("amount", Types.INTEGER));
	declareParameter(new SqlParameter("custid", Types.INTEGER));
	declareParameter(new SqlOutParameter("newid", Types.INTEGER));
	compile();
}
 
Example 21
Source Project: java-technology-stack   Source File: StoredProcedureTests.java    License: MIT License 5 votes vote down vote up
public AddInvoiceUsingObjectArray(DataSource ds) {
	setDataSource(ds);
	setSql(SQL);
	declareParameter(new SqlParameter("amount", Types.INTEGER));
	declareParameter(new SqlParameter("custid", Types.INTEGER));
	declareParameter(new SqlOutParameter("newid", Types.INTEGER));
	compile();
}
 
Example 22
Source Project: java-technology-stack   Source File: StoredProcedureTests.java    License: MIT License 5 votes vote down vote up
public ParameterMapperStoredProcedure(DataSource ds) {
	setDataSource(ds);
	setSql(SQL);
	declareParameter(new SqlParameter("in", Types.VARCHAR));
	declareParameter(new SqlOutParameter("out", Types.VARCHAR));
	compile();
}
 
Example 23
Source Project: java-technology-stack   Source File: StoredProcedureTests.java    License: MIT License 5 votes vote down vote up
public SqlTypeValueStoredProcedure(DataSource ds) {
	setDataSource(ds);
	setSql(SQL);
	declareParameter(new SqlParameter("in", Types.ARRAY, "NUMBERS"));
	declareParameter(new SqlOutParameter("out", Types.VARCHAR));
	compile();
}
 
Example 24
Source Project: java-technology-stack   Source File: RdbmsOperationTests.java    License: MIT License 5 votes vote down vote up
@Test
public void validateInOutParameter() {
	operation.setDataSource(new DriverManagerDataSource());
	operation.setSql("DUMMY_PROC");
	operation.declareParameter(new SqlOutParameter("DUMMY_OUT_PARAM", Types.VARCHAR));
	operation.declareParameter(new SqlInOutParameter("DUMMY_IN_OUT_PARAM", Types.VARCHAR));
	operation.validateParameters(new Object[] {"DUMMY_VALUE1", "DUMMY_VALUE2"});
}
 
Example 25
Source Project: java-technology-stack   Source File: SimpleJdbcCallTests.java    License: MIT License 5 votes vote down vote up
@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();
}
 
Example 26
Source Project: java-technology-stack   Source File: SimpleJdbcCallTests.java    License: MIT License 5 votes vote down vote up
@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();
}
 
Example 27
Source Project: java-technology-stack   Source File: SimpleJdbcCallTests.java    License: MIT License 5 votes vote down vote up
@Test
public void testAddInvoiceFuncWithoutMetaDataUsingMapParamSource() throws Exception {
	initializeAddInvoiceWithoutMetaData(true);
	SimpleJdbcCall adder = new SimpleJdbcCall(dataSource).withFunctionName("add_invoice");
	adder.declareParameters(
			new SqlOutParameter("return", Types.INTEGER),
			new SqlParameter("amount", Types.INTEGER),
			new SqlParameter("custid", Types.INTEGER));
	Number newId = adder.executeFunction(Number.class, new MapSqlParameterSource()
			.addValue("amount", 1103)
			.addValue("custid", 3));
	assertEquals(4, newId.intValue());
	verifyAddInvoiceWithoutMetaData(true);
	verify(connection, atLeastOnce()).close();
}
 
Example 28
Source Project: java-technology-stack   Source File: SimpleJdbcCallTests.java    License: MIT License 5 votes vote down vote up
@Test
public void testAddInvoiceFuncWithoutMetaDataUsingArrayParams() throws Exception {
	initializeAddInvoiceWithoutMetaData(true);
	SimpleJdbcCall adder = new SimpleJdbcCall(dataSource).withFunctionName("add_invoice");
	adder.declareParameters(
			new SqlOutParameter("return", Types.INTEGER),
			new SqlParameter("amount", Types.INTEGER),
			new SqlParameter("custid", Types.INTEGER));
	Number newId = adder.executeFunction(Number.class, 1103, 3);
	assertEquals(4, newId.intValue());
	verifyAddInvoiceWithoutMetaData(true);
	verify(connection, atLeastOnce()).close();
}
 
Example 29
Source Project: java-technology-stack   Source File: CallMetaDataContextTests.java    License: MIT License 5 votes vote down vote up
@Test
public void testMatchParameterValuesAndSqlInOutParameters() throws Exception {
	final String TABLE = "customers";
	final String USER = "me";
	given(databaseMetaData.getDatabaseProductName()).willReturn("MyDB");
	given(databaseMetaData.getUserName()).willReturn(USER);
	given(databaseMetaData.storesLowerCaseIdentifiers()).willReturn(true);

	List<SqlParameter> parameters = new ArrayList<>();
	parameters.add(new SqlParameter("id", Types.NUMERIC));
	parameters.add(new SqlInOutParameter("name", Types.NUMERIC));
	parameters.add(new SqlOutParameter("customer_no", Types.NUMERIC));

	MapSqlParameterSource parameterSource = new MapSqlParameterSource();
	parameterSource.addValue("id", 1);
	parameterSource.addValue("name", "Sven");
	parameterSource.addValue("customer_no", "12345XYZ");

	context.setProcedureName(TABLE);
	context.initializeMetaData(dataSource);
	context.processParameters(parameters);

	Map<String, Object> inParameters = context.matchInParameterValuesWithCallParameters(parameterSource);
	assertEquals("Wrong number of matched in parameter values", 2, inParameters.size());
	assertTrue("in parameter value missing", inParameters.containsKey("id"));
	assertTrue("in out parameter value missing", inParameters.containsKey("name"));
	assertTrue("out parameter value matched", !inParameters.containsKey("customer_no"));

	List<String> names = context.getOutParameterNames();
	assertEquals("Wrong number of out parameters", 2, names.size());

	List<SqlParameter> callParameters = context.getCallParameters();
	assertEquals("Wrong number of call parameters", 3, callParameters.size());
}
 
Example 30
Source Project: opscenter   Source File: ProcedureParameterUtils.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * Convert parameter declarations from an SqlParameterSource to a corresponding List of SqlParameters.
 * This is necessary in order to reuse existing methods on JdbcTemplate.
 * The SqlParameter for a named parameter is placed in the correct position in the
 * resulting list based on the parsed SQL statement info.
 * @param parsedSqlBean the parsed SQL statement
 * @param paramSource the source for named parameters
 */
public static List<SqlParameter> buildSqlParameterList(ParsedSqlBean parsedSqlBean, SqlParameterSource paramSource) {
	List<String> paramNames = parsedSqlBean.getParameterNames();
	Map<String, Boolean> outParam = parsedSqlBean.getOutParam();
	List<SqlParameter> params = new ArrayList<>(paramNames.size());
	for (String paramName : paramNames) {
		if(outParam.containsKey(paramName)) {
			params.add(new SqlOutParameter(paramName,Types.VARCHAR));
		}else {
			params.add(new SqlParameter(
					paramName, paramSource.getSqlType(paramName), paramSource.getTypeName(paramName)));
		}
	}
	return params;
}