java调用mssql存储过程后，记录集的返回为null
存储过程：
create proc proc_pageList
(
@tabName varchar(100),
@columnName varchar(200),
@idName varchar(100),
@pageSize int,
@pageIndex int,
@pageCounts int output,
@rowCounts int output
)
as
select @rowCounts = count(*) from buymessageinfo--@tabName
set @pageCounts = Ceiling(@rowCounts *1.0 / @pageSize)
Declare @sql nvarchar(200)
if @pageIndex < @pageCounts
set @sql = 'Select '+@columnName+ ' from (select top '+ str(@pageSize) + ' * from (select top '+ str(@pageIndex * @pageSize) + ' * from buymessageinfo) as tab1 order by '+@idName+ ' desc) as tab2 order by '+@idName
else
set @sql = 'Select '+@columnName+ ' from (select top '+ str(@rowCounts-(@pageIndex-1)*@pageSize) + ' * from (select * from buymessageinfo) as tab1 order by '+@idName+ ' desc) as ta2 order by '+@idName
exec (@sql)
java:
public static ArrayList execProcPageList(String procSql,String tableName,String colunm,String IdName,int pageSize, int pageIndex)
{
ArrayList al = new ArrayList();
try {
getConn();
cstmt = conn.prepareCall(procSql);
cstmt.setString(1,tableName);
cstmt.setString(2,colunm);
cstmt.setString(3, IdName);
cstmt.setInt(4, pageSize);
cstmt.setInt(5,pageIndex);
cstmt.registerOutParameter(6, java.sql.Types.INTEGER);
cstmt.registerOutParameter(7, java.sql.Types.INTEGER);
//cstmt.execute();
rs = cstmt.executeQuery();
rs = cstmt.getResultSet();
int pageCounts = cstmt.getInt(6);
int rowCounts = cstmt.getInt(7);
System.out.println(rs.next());
System.out.println(pageCounts);
System.out.println(rowCounts);
} catch (Exception e) {e.printStackTrace();}
finally
{
try {
if(!conn.isClosed())
{
cstmt.close();
rs.close();
conn.close();
}
} catch (SQLException e) {e.printStackTrace();}
}
return al;
}
打印出来的结果：
false
4
14
0
为什么rs.next()这false的呢？基本上其它的方法都试过了。
[解决办法]
你的方法用错了,首先要用registerOutParameter方法注册你要返回的参数,给你个例子吧
public String getSQL(String message_id){
String result = " ";
String sql = "{call dbo.usp_generate_filter_query(?,?)} ";
Connection conn = null;
CallableStatement cstmt = null;
try{
conn = ConnectionManager.getConn(GlobalSettings.CLIENT_ADMIN_DB);
cstmt = conn.prepareCall(sql);
cstmt.setInt(1,Integer.parseInt(message_id));
cstmt.registerOutParameter(2,Types.VARCHAR);
cstmt.execute();
result = cstmt.getString(2);
cstmt.close();
ConnectionManager.close(conn);
}catch(Exception e){
logger.debug( "Exception in getSQL in SqlFunc ");
ConnectionManager.close(conn);
e.printStackTrace();
}
return result;
}
[解决办法]
给你个oracle存储分页功能的：
create or replace procedure Show_Veh_pagination
(p_pageSize in int,
p_pageNo in int,
p_SqlSelect in varchar2,
p_SqlCountSelect in varchar2,
p_outCount out int,
p_outCursor out sys_refcursor)
is
v_sql varchar2(3000);
v_count int;
v_heiRowcount int;
v_lowRowcount int;
begin
p_outCount := 0 ;
if length(p_SqlCountSelect) > 0 then
execute immediate p_SqlCountSelect into v_count;
p_outCount := v_count;
end if;
v_heiRowcount := p_pageSize * p_pageNo;
v_lowRowcount := v_heiRowcount – p_pageSize + 1;
v_sql := ‘select t.* from
(select v.*,rownum as r from (‘||p_SqlSelect ||’) v
where rownum <= ‘|| v_heiRowcount ||’) t
where t.r > = ‘|| v_lowRowcount ||’’;
open p_outCursor for v_sql;
end Show_Veh_pagination;
//------在页面中的调用，因为我使用的是c#.net,所以，只能给你这个了
Oracleconnection Cn = new Oracleconnection(…..);
Cn.open();
DataTable dt = GetData(10,1,”select * from TableName order by column [ desc | asc ]”,Cn);
DataGrid.DataSouce = dt;
DataGrid.DataBind();
Private DataTable GetData (int pageSize,int pageNo,string sql,Oracleconnection cn)
{
Oraclecommand Comm. = new Oraclecommand();
Comm.connection = cn;
Comm..commandText = “Show_page”;
Comm..commandType = commandType. StoredProcedure;
Comm.Parameters.add(“p_pageSize”,Oracle.Float,50);
Comm.Parameters[“p_pageSize”].Direction = ParameterDirection.Intput;
Comm.Parameters[“p_pageSize”].Value = pageSize ;
Comm.Parameters.add(“p_pageNo”.OracleType.Float,50);
Comm.Parameters[“p_pageNo”].Direction = ParameterDirection.Intput;
Comm.Parameters[“p_pageNo”].Value = pageNo;
Comm.Parameters.add(“p_SqlSelect”,OracleType.VarChar,3000);
Comm.Parameters[“p_SqlSelect”].Direction = ParameterDirection.Intput;
Comm.Parameters[“p_SqlSelect”].Value = sql;
Comm.Parameters.add(“p_outRecordcount”,OraclType.Float);
Comm.Parameters[“p_outRecordcount”].Direction = ParameterDirection.Output;
Comm.Parameters[“p_outRecordcount”].Value = 0;
Comm.Parameters.add(“p_outReturnCursor”,OracleType.Cursor);
Comm.Parameters[“p_outReturnCursor”].Direction = ParameterDirection.Output;
OracleDataAdapter oDa = new OracleDataAdapter(Comm);
DataSet ds = new DataSet();
ODa.Fill(ds);
Lable.text = “共有记录 ”+int.parse(comm.Parameters[“p_outRecordcount”].Value.ToString())+” 条”;
Return ds.Table[0];
}
[解决办法]
只是单一的使用调用储存分页功能，别的都去掉了，需要你自己完善！
