DYNAMIC RESULT SETS 1
BEGIN
DECLARE temp_cursor1 CURSOR WITH RETURN TO CLIENT FOR
SELECT * FROM test where age < piAge;
OPEN temp_cursor1;
END;
CREATE procedure selectAllUsers(IN piAge INTEGER)
DYNAMIC RESULT SETS 1
BEGIN
DECLARE temp_cursor1 CURSOR WITH RETURN TO CLIENT FOR
SELECT * FROM test where age < piAge;
OPEN temp_cursor1;
END;
这个过程中最后一行直接打开了一个游标,也就是返回了一个结果集。调用存储过程的方法应该看看 org.springframework.jdbc.core.JdbcTemplate 的各个 execute() 方法,具体点就是带了 CallableStatementCallback<T> 参数的那两个 execute(),究底的话又归结为其中之一。看下这两个 execute() 方法,摘自代码 Spring 3.0.5 的 org.springframework.jdbc.core.JdbcTemplate:
public <T> T execute(CallableStatementCreator csc, CallableStatementCallback<T> action) throws DataAccessException
public <T> T execute(String callString, CallableStatementCallback<T> action) throws DataAccessException {
return execute(new SimpleCallableStatementCreator(callString), action);
}
继续倾注于简单方法 public <T> T execute(String callString, CallableStatementCallback<T> action) 的使用。
在我们自己的 JdbcDao 的访问方法中,可以这么写:
@SuppressWarnings("unchecked")
public List<String> getUserList(final int maxAge) {
String procedure = "{call selectAllUsers(?)}";
List<String> users = getJdbcTemplate().execute(procedure, new CallableStatementCallback() {
@Override
public Object doInCallableStatement(CallableStatement cs)
throws SQLException, DataAccessException {
cs.setInt(1,maxAge); //设置参数
//如果有出口参数就得注册一下,这样后面才能取到
//比如第二个出口参数是个整数
//cs.registerOutParameter(2, Types.VARCHAR);
cs.execute(); //执行存储过程
/**********返回结果的取什方式,用 cs.getResultSet**********/
//如果存储过程最后打开的是游标就直接得到 ResultSet
ResultSet rs = cs.getResultSet();
List<String> userList = new ArrayList<String>();
while(rs.next()){
System.out.println(rs.getString(1));
//.......................
}
return userList;
/*****************THE END*******************************/
/***********以下是注册了出口参数的取值方法,cs.getXxx()
*需要前面执行 cs.registerOutParameter() 来注册出口参数类型
*为了能让方法合法,这里也假设返回 List<String>
List<String> userList = new ArrayList<String>();
userList.add(cs.getString(2));
return userList;
*****************THE END******************************/
}
});
return users;
}
以上代码同时描述了如何应对取出口参数和返回结果集的方式。
结合实际:
SqlServer 2008存储过程
CREATE PROCEDURE [dbo].[P_Base_GetCode]
(
@codeName varchar(12)=''
)
AS
BEGIN
DECLARE @code varchar(15)
declare @currentCode int
declare @prefix varchar(5)=''
declare @lastDate datetime
declare @codeLength int
declare @HaveDateStr bit
declare @dateStr varchar(12)=''
declare @format varchar(12)
--检查流水号是否存在
IF NOT EXISTS(SELECT * FROM Base_SeqCode(NOLOCK) WHERE [CodeName]=@codeName )
BEGIN
INSERT INTO Base_SeqCode
(CodeName,CodePrefix,CodeLength,CurrentCode,[DateFormat],HaveDateStr,UpdateDate)
VALUES (@codeName,'', 5, 1,'YYYYMMDD',0,GETDATE())
END
select @prefix=CodePrefix,@lastDate = UpdateDate,@codeLength =CodeLength,@currentCode = CurrentCode,@format=[DateFormat]
,@HaveDateStr=HaveDateStr from Base_SeqCode (nolock) where CodeName = @codeName
if @HaveDateStr=1
begin
if @format='YYYYMMDD'
set @dateStr=convert(varchar(8),GETDATE(),112)
if @format='YYMMDD'
set @dateStr=convert(varchar(8),GETDATE(),12)
IF(DATEDIFF(day,@lastDate,getdate())!=0)
BEGIN
UPDATE Base_Seqcode SET UpdateDate=GETDATE(), CurrentCode=0 from Base_Seqcode where CodeName=@codeName
set @currentCode=0
END
end
begin TRAN
--取出流水号
UPDATE Base_SeqCode SET CurrentCode = @currentCode+1,UpdateDate=GETDATE() WHERE CodeName=@codeName
COMMIT TRAN
set @code =@PREFIX +@dateStr +RIGHT('000000'+CONVERT(varchar, @currentCode+1),@codeLength)
select @code as code
end
java dao调用以上存储过程
/**
* 调用存储过程 得到唯一流水号
* @param codeName
* @return
* @throws Exception
*/
@SuppressWarnings("unchecked")
public String callGetCode(String codeName) throws Exception
{
String procedure = "{call dbo.P_CustAndOrder_GetCode(?,?)}";
final String name = codeName;
String code = super.getJdbcTemplate().execute(procedure, new CallableStatementCallback() {
public Object doInCallableStatement(CallableStatement cs)
throws SQLException, DataAccessException {
cs.setString(1,name);
cs.setString(2,null);
cs.registerOutParameter(2, java.sql.Types.VARCHAR);
cs.execute();
return cs.getObject(2).toString();
}
});
return code;
}