1.) Create an Oracle Stored Package/Procedure
CREATE OR REPLACE PACKAGE AddPkg AS
PROCEDURE addLongs(i_one IN NUMBER, i_two IN NUMBER, o_three OUT NUMBER);
END AddPkg;
/
2.) Create Repository class that wraps the JdbcTemplate logic.
@Repository
public class AdditionRepository {
private static final String ADD_PKG_PROC = "{call AddPkg.addLongs(?,?,?)}";
private JdbcTemplate jdbcTemplate;
public void AdditionRepository(DataSource dataSource) {
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
public Long add(final Long n1, final Long n2) {
Long retVal = this.jdbcTemplate.execute(new CallableStatementCreator() {
CallableStatement cs = con.prepareCall(ADD_PKG_PROC);
cs.setLong(1, n1);
cs.setLong(2, n2);
cs.registerOutParameter(3, java.sql.Types.NUMERIC);
return cs;
}, new CallableStatementCallback
cs.execute();
// Note that the position corresponds to the position in the stored procedure
Long result = cs.getLong(3);
return result;
});
}
return retVal;
}
While this is a simple example is shows the basics of calling a stored procedure and function using the Spring JdbcTemplate. I realize there are other ways to perform this operation as well, which I might also document in future, but for know keeping it simple.
So go forth and add numbers....
