一、Java中调用带Cursor的存储过程：
存储过程定义：
PROCEDURE GET_CURSOR_RESULT2(CR OUT CURSOR_RESULT) IS
BEGIN
OPEN CR FOR
select t1.id, t1.account_id, t2.account, t1.key, t1.total, t1.status
from table t1;
END GET_CURSOR_RESULT2;
调用GET_CURSOR_RESULT2存储过程：
jdbcTemplate.execute(new CallableStatementCreator() {
public CallableStatement createCallableStatement(Connection con) throws SQLException {
CallableStatement cs = con.prepareCall("{call TRAIL_GETCURSOR.GET_CURSOR_RESULT2(?)}");
cs.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
return cs;
}
}, new CallableStatementCallback<List<Account>>() {
public List<Account> doInCallableStatement(CallableStatement cs) {
List<Account> accounts = null;
ResultSet rs = null;
cs.execute();
rs = (ResultSet) cs.getObject(1);
while(rs.next()) {
long id = rs.getLong("id");
String accountName = rs.getString("account");
long accountID = rs.getLong("account_id");
long key = rs.getLong("key");
long total = rs.getLong("total");
accounts.set...
}
return accounts;
});
jdbcTemplate.execute()有两个参数：CallableStatement和CallableStatementCallback
二、Java中保证调用存储过程的事务
transTemplate.execute(new TransactionCallback() {
public Object doInTransaction(TransactionStatus status) {
try{
success=jdbcTemplate.execute(...);
if(!success) {
status.setRollbackOnly();
}
} catch(e) {
status.setRollbackOnly();
}
);
在jdbcTemplate外面包上一个transTemplate，通过判断jdbcTemplate执行结果决定是否回退 status.setRollbackOnly()
