以前的项目用sqlserver做的数据库,现在变到mysql,关于分页的改动请指教?以前sqlserver:
public class PageBean { private int pagesize; //页大小
private int allcount; //总记录数
private int currpage; //当前页码
private int allpage; //总页数
private ArrayList data; //页面数据
public int getAllcount() {
return allcount;
}
public void setAllcount(int allcount) {
this.allcount = allcount;
}
public int getAllpage() { //总页数
return (allcount % pagesize == 0)?
allcount/pagesize : allcount/pagesize+1;
}
public void setAllpage(int allpage) {
this.allpage = allpage;
}
public int getCurrpage() {
return currpage;
}
public void setCurrpage(int currpage) {
this.currpage = currpage;
}
public ArrayList getData() {
return data;
}
public void setData(ArrayList data) {
this.data = data;
}
public int getPagesize() {
return pagesize;
}
public void setPagesize(int pagesize) {
this.pagesize = pagesize;
}
} //分页查找全部商品信息的方法
public PageBean selectgs(int nowPage){
PageBean pb=new PageBean();
ArrayList list=new ArrayList();
try {
conn=new ConnectDB().getConn();
String sql="select count(*) from goodscon";
pstmt=conn.prepareStatement(sql);
rs=pstmt.executeQuery();
if(rs.next()){
pb.setAllcount(rs.getInt(1)); // 总纪录数
}
int pagesize = 6;
String sql1="select top " + pagesize + " * from goodscon where steid not in(select top " + pagesize * (nowPage - 1) +" steid from goodscon)";
pstmt=conn.prepareStatement(sql1);
rs=pstmt.executeQuery();
while(rs.next()){
goodscon gs=new goodscon();
gs.setSteid(rs.getInt("steid"));
gs.setStename(rs.getString("stename"));
gs.setStephoto(rs.getString("stephoto"));
gs.setStevalue(rs.getFloat("stevalue"));
gs.setCpgnjieshao(rs.getString("cpgnjieshao"));
gs.setStedesc(rs.getString("stedesc"));
gs.setStetime(rs.getString("stetime"));
gs.setSteversion(rs.getString("steversion"));
gs.setSteversionjs(rs.getString("steversionjs"));
list.add(gs);
}
pb.setCurrpage(nowPage); // 当前页
pb.setData(list); // 数据
pb.setPagesize(pagesize); // 页大小
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if(rs!=null)
rs.close();
if( pstmt != null )
pstmt.close();
if( conn != null )
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return pb;
}
ACTION里:
public ActionForward selectsp(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response) {
Opergoodscon oper=new Opergoodscon();
// 页码
int pagecode = request.getParameter("page")==null? 1 :
Integer.parseInt(request.getParameter("page"));
PageBean pb=oper.selectgs(pagecode);
request.setAttribute("list", pb);
return mapping.findForward("selectsp");
}请问变到mysql怎么改?
public class PageBean { private int pagesize; //页大小
private int allcount; //总记录数
private int currpage; //当前页码
private int allpage; //总页数
private ArrayList data; //页面数据
public int getAllcount() {
return allcount;
}
public void setAllcount(int allcount) {
this.allcount = allcount;
}
public int getAllpage() { //总页数
return (allcount % pagesize == 0)?
allcount/pagesize : allcount/pagesize+1;
}
public void setAllpage(int allpage) {
this.allpage = allpage;
}
public int getCurrpage() {
return currpage;
}
public void setCurrpage(int currpage) {
this.currpage = currpage;
}
public ArrayList getData() {
return data;
}
public void setData(ArrayList data) {
this.data = data;
}
public int getPagesize() {
return pagesize;
}
public void setPagesize(int pagesize) {
this.pagesize = pagesize;
}
} //分页查找全部商品信息的方法
public PageBean selectgs(int nowPage){
PageBean pb=new PageBean();
ArrayList list=new ArrayList();
try {
conn=new ConnectDB().getConn();
String sql="select count(*) from goodscon";
pstmt=conn.prepareStatement(sql);
rs=pstmt.executeQuery();
if(rs.next()){
pb.setAllcount(rs.getInt(1)); // 总纪录数
}
int pagesize = 6;
String sql1="select top " + pagesize + " * from goodscon where steid not in(select top " + pagesize * (nowPage - 1) +" steid from goodscon)";
pstmt=conn.prepareStatement(sql1);
rs=pstmt.executeQuery();
while(rs.next()){
goodscon gs=new goodscon();
gs.setSteid(rs.getInt("steid"));
gs.setStename(rs.getString("stename"));
gs.setStephoto(rs.getString("stephoto"));
gs.setStevalue(rs.getFloat("stevalue"));
gs.setCpgnjieshao(rs.getString("cpgnjieshao"));
gs.setStedesc(rs.getString("stedesc"));
gs.setStetime(rs.getString("stetime"));
gs.setSteversion(rs.getString("steversion"));
gs.setSteversionjs(rs.getString("steversionjs"));
list.add(gs);
}
pb.setCurrpage(nowPage); // 当前页
pb.setData(list); // 数据
pb.setPagesize(pagesize); // 页大小
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if(rs!=null)
rs.close();
if( pstmt != null )
pstmt.close();
if( conn != null )
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return pb;
}
ACTION里:
public ActionForward selectsp(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response) {
Opergoodscon oper=new Opergoodscon();
// 页码
int pagecode = request.getParameter("page")==null? 1 :
Integer.parseInt(request.getParameter("page"));
PageBean pb=oper.selectgs(pagecode);
request.setAttribute("list", pb);
return mapping.findForward("selectsp");
}请问变到mysql怎么改?
limit 0,15 意思:从第一条开始取15条。
String sql1="select top " + pagesize + " * from goodscon where steid not in(select top " + pagesize * (nowPage - 1) +" steid from goodscon)"; String sql1="select steid from goodscon limit " + pagesize + " where not in(select steid from goodscon limit " + pagesize * (nowPage - 1) +" )";
试试 如果不行的话. 你把你两张表的结构贴出来. 我在我本地试试..
at com.zg.servlet.PageBean.getAllpage(PageBean.java:19)sqlserver:
CREATE TABLE goodscon ( --商品表
steid int IDENTITY (1, 1) NOT NULL , --编号
stename varchar (100) NOT NULL , --商品名
stetime datetime NULL , --上架时间
stephoto varchar (200) NULL , --图片
cpgnjieshao varchar(50) null, --产品功能介绍
stedesc varchar (1000) NULL , --订购说明
stevalue varchar (50) NULL , --价格
steversion varchar(50) NOT NULL , --产品版本
steversionjs varchar(4000) null --产品版本商品介绍
)mysql:
CREATE TABLE goodscon ( --商品表
steid int NOT NULL auto_increment primary key , --编号
stename varchar (100) NOT NULL , --商品名
stetime timestamp NULL , --上架时间
stephoto varchar (200) NULL , --图片
cpgnjieshao varchar(50) null, --产品功能介绍
stedesc varchar (1000) NULL , --订购说明
stevalue varchar (50) NULL , --价格
steversion varchar(50) NOT NULL , --产品版本
steversionjs varchar(4000) null --产品版本商品介绍
)