org.springframework.jdbc.core.SqlOutParameter Java Examples

/**
 * 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");
		}
	}
}
 

@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();
}
 

/**
 * 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");
		}
	}
}
 

@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();
}
 

@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);
	}
}
 

/**
 * 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) {
	if (this.metaDataProvider.isReturnResultSetSupported()) {
		return new SqlReturnResultSet(parameterName, rowMapper);
	}
	else {
		if (this.metaDataProvider.isRefCursorSupported()) {
			return new SqlOutParameter(parameterName, this.metaDataProvider.getRefCursorSqlType(), rowMapper);
		}
		else {
			throw new InvalidDataAccessApiUsageException("Return of a ResultSet from a stored procedure is not supported.");
		}
	}
}
 

@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);
	}
}
 

@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);
	}
}
 

public ParameterMapperStoredProcedure(DataSource ds) {
	setDataSource(ds);
	setSql(SQL);
	declareParameter(new SqlParameter("in", Types.VARCHAR));
	declareParameter(new SqlOutParameter("out", Types.VARCHAR));
	compile();
}
 

public SqlTypeValueStoredProcedure(DataSource ds) {
	setDataSource(ds);
	setSql(SQL);
	declareParameter(new SqlParameter("in", Types.ARRAY, "NUMBERS"));
	declareParameter(new SqlOutParameter("out", Types.VARCHAR));
	compile();
}
 

public void testValidateInOutParameter() {
	TestRdbmsOperation operation = new TestRdbmsOperation();
	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"});
}
 

@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 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();
}
 

@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();
}
 

@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<SqlParameter>();
	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());
}
 

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();
}
 

@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();
}
 

@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<SqlParameter>();
	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());
}
 

@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 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"});
}
 

public SqlTypeValueStoredProcedure(DataSource ds) {
	setDataSource(ds);
	setSql(SQL);
	declareParameter(new SqlParameter("in", Types.ARRAY, "NUMBERS"));
	declareParameter(new SqlOutParameter("out", Types.VARCHAR));
	compile();
}
 

public ParameterMapperStoredProcedure(DataSource ds) {
	setDataSource(ds);
	setSql(SQL);
	declareParameter(new SqlParameter("in", Types.VARCHAR));
	declareParameter(new SqlOutParameter("out", Types.VARCHAR));
	compile();
}
 

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();
}
 

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();
}
 

public StoredProcedureConfiguredViaJdbcTemplate(JdbcTemplate t) {
	setJdbcTemplate(t);
	setSql(SQL);
	declareParameter(new SqlParameter("intIn", Types.INTEGER));
	declareParameter(new SqlOutParameter("intOut", Types.INTEGER));
	compile();
}
 

/**
 * 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) {
	if (this.metaDataProvider.isReturnResultSetSupported()) {
		return new SqlReturnResultSet(parameterName, rowMapper);
	}
	else {
		if (this.metaDataProvider.isRefCursorSupported()) {
			return new SqlOutParameter(parameterName, this.metaDataProvider.getRefCursorSqlType(), rowMapper);
		}
		else {
			throw new InvalidDataAccessApiUsageException("Return of a ResultSet from a stored procedure is not supported.");
		}
	}
}
 

@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);
	}
}
 

@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);
	}
}
 

/**
 * 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) {
	if (this.metaDataProvider.isReturnResultSetSupported()) {
		return new SqlReturnResultSet(parameterName, rowMapper);
	}
	else {
		if (this.metaDataProvider.isRefCursorSupported()) {
			return new SqlOutParameter(parameterName, this.metaDataProvider.getRefCursorSqlType(), rowMapper);
		}
		else {
			throw new InvalidDataAccessApiUsageException("Return of a ResultSet from a stored procedure is not supported.");
		}
	}
}
 

@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);
	}
}