1、通过sql获取记录集,从记录集的起始位置,开始计数下移,定位到所要显示的起始位置,插入数据,并记录插入的数量,直到插入数量等于每页所要显示的最大数量,或者记录集的末尾。
CREATE proc dbo.jdbc_showdata
@sqlstr nvarchar(4000), --查询字符串
@pagecount int,--第N页
@pagesize int--每页行数
as
set nocount on
declare @P1 int,--P1是游标的id
@rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
select @rowcount as sumrows,ceiling(1.0*@rowcount/@pagesize) as pagerows,@pagecount as currentpage
set @pagecount=(@pagecount-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@pagecount,@pagesize
exec sp_cursorclose @P1
GO
以下是Database.java的源码注意用红字标出的地方就是调用的方法
package SQLServer;
import java.io.IOException;
import java.sql.*;
//用于连接SQLServer数据库
public class Database {
private String Driver="com.microsoft.jdbc.sqlserver.SQLServerDriver";
private Connection Con=null;
private String url;
private String Host;
private String Port;
private String DBName;
private String SelectMethod;
private String User;
private String Password;
private int rowcount=0;
private int pagecount=0;
private int currpage=0;
Database(String host,String port,
String dbname,String selectmethod,
String username,String password){
Host=host;
Port=port;
DBName=dbname;
SelectMethod=selectmethod;
User=username;
Password=password;
}
Database(){
Host="localhost";
Port="1433";
DBName="master";
SelectMethod="cursor";
User="sa";
Password="";
}
public String GetConnection(){
String Result="";
url="jdbc:microsoft:sqlserver://";
url+=Host+":";
url+=Port+";";
url+="databaseName="+DBName+";";
url+="selectMethod="+SelectMethod+";";
try {
Class.forName(Driver);
Con = DriverManager.getConnection(url, User, Password);
if (Con!=null){
System.out.println("Connection Successful!");
}
}
catch (Exception e) {
e.printStackTrace();
System.out.print(e.getMessage());
Result="连接数据库失败!";
}
return Result;
}
public int getrowcount(){
return rowcount;
}
public int getpagecount(){
return pagecount;
}
public int getcurrpage(){
return currpage;
}
public String GetTableContext(String tablename,int pagerows,int currentpage)
throws IOException {
String result="";
if (Con==null){
result=GetConnection();
if (result==""){
try {
CallableStatement callsta;
String sql="Select * from "+tablename;
String st="{call jdbc_showdata(?,?,?)}";
callsta=Con.prepareCall(st);
callsta.setString(1, sql);
callsta.setInt(2, currentpage);
callsta.setInt(3, pagerows);
ResultSet rs=null;
if(callsta.execute()==true){
rs=callsta.getResultSet();
result+="<table border=1 cellspacing=\"0\" cellpadding=\"0\">";
for (int i=1;i<=rs.getMetaData().getColumnCount();i++){
result+="<th>";
result+=rs.getMetaData().getColumnName(i);
result+="</th>";
}
if (callsta.getMoreResults()){
rs=callsta.getResultSet();
if (rs.next()){
rowcount=rs.getInt(1);
pagecount=rs.getInt(2);
currpage=rs.getInt(3);
}
}
if (callsta.getMoreResults()){
rs=callsta.getResultSet();
while (rs.next()) {
result+="<tr>";
for (int i=1;i<=rs.getMetaData().getColumnCount();i++){
result+="<td><font size=\"2\">";
result+="<div style=\"height:10px;\" vertical-align: middle;>";
if (rs.getString(i).length()==0||rs.getString(i)==null){
result+="<pre> </pre>";
}else{
result+=rs.getString(i);
}
result+="</div>";
result+="</font size></td>";
}
result+="</tr>";
}
}
}
result+="</table>";
}catch (Exception e){
e.printStackTrace();
}
}
}
return result;
}
public String getDriver(){
return Driver;
}
public String getHost(){
return Host;
}
public void setHost(String _Host){
Host=_Host;
}
public String getPort(){
return Port;
}
public void setPort(String _Port){
Port=_Port;
}
public String getDBName(){
return DBName;
}
public void setDBName(String _DBName){
DBName=_DBName;
}
public String getSelectMethod(){
return SelectMethod;
}
public void setSelectMethod(String _SelectMethod){
SelectMethod=_SelectMethod;
}
public String getUser(){
return User;
}
public void setUser(String _User){
User=_User;
}
public String getPassword(){
return Password;
}
public void setPassword(String _Password){
Password=_Password;
}
public Connection getCon(){
return Con;
}
public void setCon(Connection _Con){
Con=_Con;
}
}