jdbc分页-CSDN博客

前端：

    //当前页数
   var curPage = 1;
   var rowTotal,pageSize,total; //总记录数，每页显示数，总页数

//获取分页条
 function getPageBar(){
 //页码大于最大页数
 if(curPage>total) curPage=total;
 //页码小于1
 if(curPage<1) curPage=1;
 pageStr = "共"+rowTotal+"条数据 "+curPage+"/"+total+"页";

 //如果是第一页
 if(curPage==1){
 pageStr += " 首页上一页";
 }else{
 pageStr += "<a href='javascript:void(0)' rel='1'> 首页</a><a href='javascript:void(0)' rel='"+(curPage-1)+"'>上一页</a>";
 }

 //如果是最后页
 if(curPage>=total){
 pageStr += " 下一页尾页";
 }else{
 pageStr += "<a href='javascript:void(0)' rel='"+(parseInt(curPage)+1)+"'> 下一页</a><a href='javascript:void(0)' rel='"+total+"'>尾页</a>";
 }
 $("#pagecount").append(pageStr);

}

后端：

public class Page

{
 private int rowTotal;// 总记录数

 private int pageSize = 10;// 每页记录数

 private int count;// 当前页码

 private int total;// 总页数

 private int beginIndex;// 起始记录下标

 private int endIndex;// 截止记录下标

 /**
 * 使用总记录数、当前页码构造
 *
 * @param rowTotal
 * @param count 页码，从1开始
 */
 public Page(int totalRow, int count)
 {
 this.rowTotal = totalRow;
 this.count = count;
 calculate();
 }

 /**
 * 使用总记录数、当前页码和每页记录数构造
 *
 * @param rowTotal
 * @param count 页码，从1开始
 * @param pageSize 默认10条
 */
 public Page(int totalRow, int count, int pageSize)
 {
 this.rowTotal = totalRow;
 this.count = count;
 this.pageSize = pageSize;
 calculate();
 }

 private void calculate()
 {
 total = rowTotal / pageSize + ((rowTotal % pageSize) > 0 ? 1 : 0);

 if (count > total)
 {
 count = total;
 }
 else if (count < 1)
 {
 count = 1;
 }

 beginIndex = (count - 1) * pageSize;
 endIndex = beginIndex + pageSize;
 if (endIndex > rowTotal)
 {
 endIndex = rowTotal;
 }
 }

 public int getCount()
 {
 return count;
 }

 public int getTotal()
 {
 return total;
 }

 public int getTotalRow()
 {
 return rowTotal;
 }

 public int getPageSize()
 {
 return pageSize;
 }

 public int getBeginIndex()
 {
 return beginIndex;
 }

 public int getEndIndex()
 {
 return endIndex;
 }

}

@RequestMapping(value = "/queryTable", method = RequestMethod.POST)
 public LocalContext queryTable(LocalContext localContext)
 {
 String tableName = localContext.getRequstParam("tableName");
 String whereString = localContext.getRequstParam("whereString");
 //从页面取得页码
 int pageCount = Integer.parseInt(localContext.getRequstParam("pageIndex"));

 //取得总记录数，创建Page对象
 int totalRow = operationSqlService.dataCount(tableName,whereString);
 Page page = new Page(totalRow, pageCount);

 List<Map<String,Object>> result = operationSqlService.queryTable(tableName,whereString,page);
 localContext.getContent().put("result", result);
 localContext.getContent().put("pageSize", page.getPageSize());//每页大小
 localContext.getContent().put("total", page.getTotal());//总页数
 localContext.getContent().put("rowTotal", page.getTotalRow());//总记录数
 return localContext;
 }

public class OperationSqlServiceImpl implements OperationSqlService
{
 @Autowired
 @Qualifier("COMConfig")
 private IConfigService configService;
 private ILogger logger = SimpleXLogger.getLogger(OperationSqlServiceImpl.class);
 private Connection conn = null;
 private Statement stmt = null;
 private ResultSet rs = null;
 private ResultSetMetaData rsmd = null;

 @Override
 public List<Map<String,Object>> queryTable(String tableName, String whereString,Page page)
 {
 List<Map<String,Object>> resultParam = new ArrayList<Map<String,Object>>();
 List<String> titleList = new ArrayList<String>();
 Map<String,Object> titleMap = new HashMap<String, Object>();
 String sql = "select * from " + tableName + " where 1=1 and " + whereString ;

 try
 {
 conn = this.getConnection();
 stmt = conn.createStatement();
 stmt.setMaxRows(page.getEndIndex());
 rs = stmt.executeQuery(sql);
 rsmd = rs.getMetaData();//获取字段名
 for (int i = 0; i < rsmd.getColumnCount(); i++) {
 // rsmd数据下标从1开始
 String columnName = rsmd.getColumnName(i + 1);
 titleList.add(columnName);
 }
 titleMap.put("title",titleList);
 resultParam.add(titleMap);

 for(int i = 0; i < page.getBeginIndex(); i++) {
 rs.next();//关键代码，直接移动游标为当前页起始记录处
 }
 // 获取数据
 while (rs.next()) {
 Map<String,Object> dataMap = new HashMap<String, Object>();
 for (int i = 0; i < rsmd.getColumnCount(); i++) {
 //rs数据下标从1开始
 dataMap.put(rsmd.getColumnName(i + 1), rs.getString(i + 1));
 }
 resultParam.add(dataMap);
 }
 }
 catch (SQLException e)
 {
 e.printStackTrace();
 } finally
 {
 close();
 }
 return resultParam;
 }

 @Override
 public Integer dataCount(String tableName, String whereString)
 {
 conn = this.getConnection();
 String sql = "select count(*) from " + tableName + " where 1=1 and " + whereString ;
 rs = executeQuery(sql);
 Integer totalCount = null;
 try
 {
 while (rs.next())
 {
 totalCount = rs.getInt(1);
 }
 }
 catch (SQLException e)
 {
 e.printStackTrace();
 }finally
 {
 close();
 }
 return totalCount;
 }

 /**
 * 获取连接
 * @return
 */
 private Connection getConnection(){
 String driver = configService.getUniqueValueAsString("driver");
 String strUrl = configService.getUniqueValueAsString("jdbcUrl");
 String USERNAME = configService.getUniqueValueAsString("username");
 String PASSWORD = configService.getUniqueValueAsString("password");
 try {
 Class.forName(driver);
 conn=DriverManager.getConnection(strUrl,USERNAME,PASSWORD);
 }catch (Exception e) {
 logger.error("获取连接失败！");
 e.printStackTrace();
 }
 return conn;
 }

 /**
 * 执行查询操作
 * @param sql
 * @return 返回值是一个结果集
 */
 private ResultSet executeQuery(String sql){
 try {
 conn = this.getConnection();
 stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
 rs = stmt.executeQuery(sql);
 } catch (SQLException e) {
 logger.error("执行查询操作失败！");
 e.printStackTrace();
 }
 return rs;
 }

 /**
 * 关闭数据库连接
 */
 private void close(){
 if(rs!=null){
 try {
 rs.close();
 } catch (SQLException e) {
 e.printStackTrace();
 }
 }
 if(stmt!=null){
 try {
 stmt.close();
 } catch (SQLException e) {
 e.printStackTrace();
 }
 }
 if(conn!=null){
 try {
 conn.close();
 } catch (SQLException e) {
 e.printStackTrace();
 }
 }
 }
}