Implementing pagination with Java servlets, MySQL, and Javascript for the front end -

i'm having trouble going through , visualizing how accomplish pagination on large dataset mysql database, java servlets, , using javascript display results.

i working on personal project small dataset try , learn java servlets / mysql / javascript. have movie database movie names , other attributes in mysql:

create table `movies` (     `id` int(11) not null auto increment,     `title` varchar(100) not null default '',     `year` int(4) not null, ) engine=innodb default charset=latin1; 

i have movie java bean:

// movie bean import java.io.serializable; public class movie implements serializable {      // instance variables     private long            id;     private string          title;     private int             year;      public movie() {}      // setters     public void setid    (long id)      { this.id = id;       }     public void settitle (string title) { this.title = title; }     public void setyear  (int year)     { this.year = year;   }      // getters     public long     getid()     { return id;    }     public string   gettitle()  { return title; }     public int      getyear()   { return year;  } } 

a moviedao accesses database , retrieves list of movies (below excerpt, assume connect database):

public class moviedao {     private list<movie> movies;      private static resultset querymovies(string st) throws sqlexception {         resultset res = null;         try {             preparedstatement ps = dbcon.preparestatement(st);             res = ps.executequery();         } catch(sqlexception e) {             system.out.println("error moviedao query");             e.printstacktrace();         }         return res;     }      public list<movie> listmovies() throws sqlexception {         movies = new arraylist<movie>();         resultset res = querymovies("select * movies");         while(res.next()) {             movie mov = new movie();             mov.setid(res.getlong("id"));             mov.settitle(res.getstring("title"));             mov.setyear(res.getint("year"));             movies.add(mov);         }         return movies;     } } 

and movieservlet class displays information jsp variable list:

@webservlet("/movies") public class movieservlet extends httpservlet {      private moviedao moviedao;      @override     protected void doget(httpservletrequest request, httpservletresponse response)         throws ioexception, servletexception {         moviedao = new moviedao();         try {             list<movie> movies = moviedao.listmovies();             request.setattribute("movies", movies); // jsp variable: ${movies}             request.getrequestdispatcher("movies.jsp").forward(request, response);         } catch (sqlexception e) {             throw new servletexception("could not retrieve movies database",e);         }         moviedao.closemoviedao();     } } 

for front end side of things have table populated through jsp , javascript. had list of page numbers generated so:

function showpagenumber() {     var html = '';     for(i = num_of_pages; > 0; i--) { html += "<li class='page'><a>" + + "</a></li>"; }     $('.page').click(function() {         result_min = ($(this).text() - 1) * results_per_page;         result_max = $(this).text() * results_per_page;         show(result_min,result_max);     }); }  function show(min,max) {     var $rows = $('#result_table');     $rows.hide().slice(min, max).show(); } 

inside each row of table id of result_table, had jsp variables. had sort function when user clicked on each header of table, sort header (ie. clicking id sort ascending id). worked great few thousand records of movies , relatively quick.

i inserted 80,000 more records database. no ones surprise, loading of data takes ages. sorting takes longer.

to deal this, want let user access next button, first ten page number buttons, , last button goes last page. figured limiting number of possible movies helps display data faster.

my problem how can sort every record , display correctly? example, if user sorts id list of ascending id's so:

1  - movie1 - 1990 2  - movie2 - 1996 3  - movie3 - 1934 

and if click same header order reverse so:

3  - movie3 - 1934 2  - movie2 - 1996 1  - movie1 - 1990 

but if limit amount of data retrieved, have range of id's 1 - 80,000 , how can re-sort through data , retrieve id values in reverse order now.. without having call select again?