sqlserver 2005数据库 怎么进行分页

Question

用JDBC实现；不用HIBERNATE 框架
知道的留言 ；谢谢
我要真分页；不是假分页
在JSP页面中怎么调用；

Answer 1

分两步实现
一、分页的存储过程如下
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[Pagination]
@tblName varchar(255), -- 表名
@strGetFields varchar(1000) , -- 需要返回的列
@fldName varchar(255), -- 排序的字段名
@PageSize int, -- 页尺寸
@PageIndex int , -- 页码
@doCount bit=0, -- 返回记录总数, 非 0 值则返回
@OrderType bit, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1500) -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
if @doCount != 0
begin
if @strWhere !=''
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere
else
set @strSQL = 'select count(*) as Total from [' + @tblName + ']'
end
--以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况
else
begin
if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by [' + @fldName + '] desc'
end
--如果@OrderType不是0，就执行降序，这句很重要！
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by [' + @fldName + '] asc'
end
if @PageIndex = 1
begin
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @strGetFields + ' from [' + @tblName + '] where '
+ @strWhere + ' ' + @strOrder
else
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @strGetFields
+ ' from [' + @tblName + '] ' + @strOrder
end
--如果是第一页就执行以上代码，这样会加快执行速度
else
begin
--以下代码赋予了@strSQL以真正执行的SQL代码
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @strGetFields + ' 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) + ' ' + @strGetFields + ' from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str( ( @PageIndex - 1 ) * @PageSize ) + ' ['
+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
end
end
exec(@strSQL)

二、页面调用部分代码
Function navindex(ByVal PageIndextemp As Integer, ByVal PageSizetemp As Integer, ByVal countint As Integer, ByVal pagename As String) As String
Dim i As Integer
If countint Mod PageSizetemp = 0 Then
i = countint \ PageSizetemp
Else
i = countint \ PageSizetemp + 1
End If
Dim maxi, mini As Integer
Dim navleft, navright, navstrtemp As String
If i < 10 Then
maxi = i
mini = 1
Else
maxi = pageindex + 3
mini = pageindex - 3
If mini > 1 Then
navleft = "<a href=""" & pagename & "?page=" & (mini - 1) & """ class=""link_nav_btn""><</a> "
Else

mini = 1
maxi = 10
End If
If maxi < i Then
navright = " <a href=""" & pagename & "?page=" & (maxi + 1) & """ class=""link_nav_btn"">></a>"
Else
If i - 10 > 0 Then
mini = i - 10
Else
mini = 1
End If
maxi = i
End If
End If
For n As Integer = mini To maxi
If n = pageindex Then
navstrtemp = navstrtemp & " <a href=""" & pagename & "?page=" & n & """ class=""link_nav_btn_select""><b>" & n & "</b></a>"
Else
navstrtemp = navstrtemp & " <a href=""" & pagename & "?page=" & n & """ class=""link_nav_btn"">" & n & "</a>"
End If
Next
navstrtemp = navleft & navstrtemp & navright
Return navstrtemp
End Function

Sub databinds(ByVal tblnametemp As String, ByVal strGetFieldstemp As String, ByVal fldNametemp As String, ByVal PageSizetemp As Integer, ByVal PageIndextemp As Integer, ByVal OrderTypetemp As Short, ByVal strWheretemp As String)
'tblnametemp表名,strGetFieldstemp需要返回的列,fldNametemp排序的字段名,PageSizetemp页尺寸,PageIndextemp页码,OrderTypetemp设置排序类型,strWheretemp查询条件

'总数
cmdTM = New SqlCommand("select count(*) from " & tblnametemp & " where " & strWheretemp, conPubs)
conPubs.Open()
countint = CInt(cmdTM.ExecuteScalar())
conPubs.Close()
'导航
navstr = navindex(PageIndextemp, PageSizetemp, countint, "newshyxh.aspx")
'分页
cmdTM = New SqlCommand("Pagination", conPubs)
cmdTM.CommandType = CommandType.StoredProcedure
'add input
cmdTM.Parameters.Add("@tblName", SqlDbType.VarChar, 255).Value = tblnametemp
cmdTM.Parameters.Add("@strGetFields", SqlDbType.VarChar, 1000).Value = strGetFieldstemp
cmdTM.Parameters.Add("@fldName", SqlDbType.VarChar, 255).Value = fldNametemp
cmdTM.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PageIndextemp
cmdTM.Parameters.Add("@PageSize", SqlDbType.Int).Value = PageSizetemp
cmdTM.Parameters.Add("@OrderType", SqlDbType.Bit).Value = OrderTypetemp
cmdTM.Parameters.Add("@strWhere", SqlDbType.VarChar, 1500).Value = strWheretemp

conPubs.Open()
newsright.DataSource = cmdTM.ExecuteReader()
newsright.DataBind()
conPubs.Close()
End Sub

Answer 2

方法1:
WITH TMP AS(SELECT ROW_NUMBER(PARTITION BY COL_NAME ORDER BY COL_NAME) AS RN, *
FROM TABLE)
SELECT *
FROM TMP
WHERE RN >= 起始索引 AND RN < 结束索引位置
方法2：
http://topic.csdn.net/u/20081106/10/edea2c7c-0a15-47c1-b9c1-26ddc7ce90db.html