Paging functionality is implemented in SQL Server and MySQL separately

Source: Internet
Author: User
Tags rowcount

Mysql

In MySQL, you can use Limit to query the records of column m to nth column,

Example: SELECT * FROM tablename limit m, n

Sql= "SELECT * from the users order by UserId limit" + (pageNow-1) *pagesize+ "," +pagesize;

Get the data for each page after pagination:

Pagination Display user Information public ArrayList getusersbyfenye (int pagesize,int pagenow) {String sql= "SELECT * from Users order by UserId Lim It "+ (pageNow-1) *pagesize+", "+pagesize; SqlHelper sqlhelper=new SqlHelper (); ArrayList al=new ArrayList (); ResultSet rs=sqlhelper.executequery (SQL, NULL), try {while (Rs.next ()) {User user=new User (); User.setuserid (Rs.getint ( 1); User.setusername (Rs.getstring (2)); User.setpassword (Rs.getstring (3)); User.setphone (Rs.getstring (4)); User.setgrade (Rs.getint (5)); Al.add (user);}} catch (SQLException e) {//TODO auto-generated catch Blocke.printstacktrace ();} Return al;}

  

Get Total Pages:

public int Getpagecount (int. pageSize) {String sql= "SELECT COUNT (*) from users"; int pagecount=0; SqlHelper sqlhelper=new SqlHelper (); ResultSet rs=sqlhelper.executequery (SQL, NULL), try {if (Rs.next ()) {int Rowcount=0;rowcount=rs.getint (1);p agecount= ( rowCount-1)/pagesize+1;}} catch (SQLException e) {//TODO auto-generated catch Blocke.printstacktrace ();} return PageCount;}

SQL Server

However, in SQL Server, the LIMIT statement is not supported. What do we do?
Solution:
Although SQL Server does not support Limit, it supports TOP.

Ps=ct.preparestatement ("Select Top" +pagesize+ "* from the users where userId not in (select Top" +pagesize* (pageNow-1) + "user Id from users) ");

Paging functionality is implemented in SQL Server and MySQL separately

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.