下面列出了org.springframework.jdbc.core.ColumnMapRowMapper#org.springframework.jdbc.core.SqlOutParameter 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
/**
* 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");
}
}
}
/**
* 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();
}
@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);
}
}
@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);
}
}
public StoredProcedureConfiguredViaJdbcTemplate(JdbcTemplate t) {
setJdbcTemplate(t);
setSql(SQL);
declareParameter(new SqlParameter("intIn", Types.INTEGER));
declareParameter(new SqlOutParameter("intOut", 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 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 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();
}
@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"});
}
@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 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<>();
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());
}
@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);
}
}
@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);
}
}
public StoredProcedureConfiguredViaJdbcTemplate(JdbcTemplate t) {
setJdbcTemplate(t);
setSql(SQL);
declareParameter(new SqlParameter("intIn", Types.INTEGER));
declareParameter(new SqlOutParameter("intOut", 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 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 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();
}
@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"});
}
@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 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<>();
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());
}
/**
* 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;
}