Mysql 分页查询 快照_MySql中查询语句实现分页功能

import java.util.*;

import java.sql.*;

public class FruitDao {

private Connection conn;

private PreparedStatement pre;

private ResultSet rs;

public FruitDao() throws Exception {

conn = DBConnection.aa();

}

//返回总页数

public int yeshu(int meiyegeshu) throws SQLException{

//求有多少行

int yeshu = 0;

String sql = "select count(*)from fruit";

pre = conn.prepareStatement(sql);

rs = pre.executeQuery();

rs.next();

int hangshu = rs.getInt(1);

//求有多少页

yeshu = (int)Math.ceil(1.0*hangshu/meiyegeshu);//取上限值

conn.close();

return yeshu;

}

//返回指定页的数据

public ArrayList selest(int yaozhaoyeshu,int meiyegeshu) throws Exception{

ArrayList list = new ArrayList();

String sql = "select * from fruit limit ?,?";

pre = conn.prepareStatement(sql);

pre.setInt(1, meiyegeshu*(yaozhaoyeshu-1));//要找第几页，从多少行开始

pre.setInt(2, meiyegeshu);//找的页有多少行

rs = pre.executeQuery();

while (rs.next()){

Fruit data = new Fruit();

data.setIds(rs.getString(1));

data.setName(rs.getString(2));

data.setPrice(rs.getDouble(3));

data.setSource(rs.getString(4));

data.setNumbers(rs.getInt(5));

data.setImage(rs.getString(6));

list.add(data);

}

conn.close();

return list;

}

}