set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[getRecordByPage]
-- Add the parameters for the stored procedure here
@PageSize int,
@PageNumber int,
@QuerySql varchar(1000),
@KeyField varchar(500)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
Declare @SqlTable AS varchar(1000)
Declare @SqlText AS Varchar(1000)
Set @SqlTable='Select Top '+CAST(@PageNumber*@PageSize AS varchar(30))+' '+@QuerySql
Set @SqlText='Select Top '+Cast(@PageSize AS varchar(30))+' * From '
+'('+@SqlTable+') As TembTbA '
+'Where '+@KeyField+' Not In (Select Top '+CAST((@PageNumber-1)*@PageSize AS varchar(30))+' '+@KeyField+' From '
+'('+@SqlTable+') AS TempTbB)'
Exec(@SqlText)
END
public List getPageList(String pageSize,String page){
String sql="* from utable";
String KeyField="Id";
session=getSession();
Query query = session.getNamedQuery("getPageList");
query.setString(0, pageSize);
query.setString(1, page);
query.setString(2, sql);
query.setString(3, KeyField);
List list=query.list();
session.close();
return list;
}
<sql-query name="getPageList" callable="true">
<return alias="user" class="com.feixun.hibernate.Utable">
<return-property name="id" column="Id"/>
<return-property name="uname" column="uname"/>
<return-property name="upwd" column="upwd" />
</return>
{call getRecordByPage(?,?,?,?)}
</sql-query>