1。 首先上两个通用的分页存储过程。一个为查询某一张表下的总记录数,一个为查询分页数据项
create procedure countTable
@tblName varchar(255)
AS
create procedure countTable
@tblName varchar(255)
AS
declare @strSQL varchar(6000)
set @strSQL = 'select count(*) from ' + @tblName
exec (@strSQL)
create procedure pages
@tblName varchar(255),
@fldName varchar(255),
@PageSize int = 10,
@PageIndex int = 1,
@OrderType bit = 0,
@strWhere varchar(2000) = ''
AS
declare @strSQL varchar(6000)
declare @strTmp varchar(1000)
declare @strOrder varchar(500)
if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by [' + @fldName +'] desc'
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by [' + @fldName +'] asc'
end
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
+ @strOrder
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
if @PageIndex = 1
begin
set @strTmp = ''
if @strWhere != ''
set @strTmp = ' where (' + @strWhere + ')'
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + ']' + @strTmp + ' ' + @strOrder
end
exec (@strSQL)
2。 通过以下的JAVA代码,来调用以上的俩个存储过程。
public int testCountTable() {
Sql2000DbHandler sql2000 = new Sql2000DbHandler();
sql2000.dbConnect();
Connection conn = sql2000.getConn();
int number = 0;
PreparedStatement pstmt;
try {
pstmt = conn.prepareStatement("{call countTable(?)}");
pstmt.setString(1, "jobs");
ResultSet rs = pstmt.executeQuery();
while(rs.next()) {
number = rs.getInt(1);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return number;
}
public List<Jobs> testInParamProc(int nowpage) {
Sql2000DbHandler sql2000 = new Sql2000DbHandler();
sql2000.dbConnect();
Connection conn = sql2000.getConn();
PreparedStatement pstmt;
List<Jobs> list = new ArrayList<Jobs>();
try {
pstmt = conn.prepareStatement("{call pages(?,?,?,?,?)}");
pstmt.setString(1, "jobs");
pstmt.setString(2, "job_id");
pstmt.setInt(3, 10);
pstmt.setInt(4, nowpage);
pstmt.setInt(5, 0);
// pstmt.setString(6, "min_lvl>100 and max_lvl>200");
ResultSet rs = pstmt.executeQuery();
Jobs jobs = null;
while(rs.next()) {
jobs = new Jobs();
String jobDesc = rs.getString("JOB_DESC");
jobs.setName(jobDesc);
list.add(jobs);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
3。 以上的方式即为通过JAVA的方式来调用分页存储过程的,其中返回的数据,需要自行组装成
分页的PAGE方式。