java - SQL syntax error in pagination query -
i'm trying make pagination in jsp page. didn't data database. got error "you have error in sql syntax; check manual corresponds mysql server version right syntax use near '-10, 5' @ line 1".
i didn't understand what's wrong. please check code , me solve problem?
booksinfo.java
package com.sreejonee.books; import java.sql.date; import java.sql.timestamp; public class booksinfo { private int book_id; private string bookname; private string filename; private string writername; private string book_details; private timestamp date_time; private int rating; private int parentscat_id; private string parentscat_name; private string thumcoverimag; private string filepath; public int getbook_id() { return book_id; } public void setbook_id(int book_id) { this.book_id = book_id; } public string getbookname() { return bookname; } public void setbookname(string bookname) { this.bookname = bookname; } public string getfilename() { return filename; } public void setfilename(string filename) { this.filename = filename; } public string getwritername() { return writername; } public void setwritername(string writername) { this.writername = writername; } public string getbook_details() { return book_details; } public void setbook_details(string book_details) { this.book_details = book_details; } public timestamp getdate_time() { return date_time; } public void setdate_time(timestamp date_time) { this.date_time = date_time; } public int getrating() { return rating; } public void setrating(int rating) { this.rating = rating; } public int getparentscat_id() { return parentscat_id; } public void setparentscat_id(int parentscat_id) { this.parentscat_id = parentscat_id; } public string getparentscat_name() { return parentscat_name; } public void setparentscat_name(string parentscat_name) { this.parentscat_name = parentscat_name; } public string getthumcoverimag() { return thumcoverimag; } public void setthumcoverimag(string thumcoverimag) { this.thumcoverimag = thumcoverimag; } public string getfilepath() { return filepath; } public void setfilepath(string filepath) { this.filepath = filepath; } } booksinfodao.java
package com.sreejonee.books; import java.sql.*; import java.util.arraylist; import java.util.list; import com.sreejonee.db.connectionfactory; import com.sreejonee.db.dbconnector; public class booksinfodao { connection connection; statement stmt; private int noofrecords; private static connection getconnection() throws sqlexception, classnotfoundexception { connection con = connectionfactory. getinstance().getconnection(); system.out.println("connected!"); return con; } public list<booksinfo> viewallbooksinfo(int offset, int noofrecords) { string query = "select sql_calc_found_rows * library order date_time desc limit"+ offset + ", " + noofrecords; list<booksinfo> bookslist = new arraylist<booksinfo>(); booksinfo books = null; try { connection = getconnection(); stmt = connection.createstatement(); resultset rs = stmt.executequery(query); while (rs.next()) { books = new booksinfo(); books.setbook_id(rs.getint("book_id")); books.setbook_details(rs.getstring("book_details")); books.setbookname(rs.getstring("bookname")); books.setwritername(rs.getstring("writername")); books.setdate_time(rs.gettimestamp("date_time")); books.setparentscat_id(rs.getint("parentscat_id")); books.setparentscat_name(rs.getstring("parentscat_name")); books.setfilename(rs.getstring("filename")); books.setfilepath(rs.getstring("filepath")); books.setrating(rs.getint("rating")); books.setthumcoverimag(rs.getstring("thumcoverimag")); bookslist.add(books); } rs.close(); rs = stmt.executequery("select found_rows()"); if (rs.next()) this.noofrecords = rs.getint(1); } catch (sqlexception e) { e.printstacktrace(); } catch (classnotfoundexception e) { e.printstacktrace(); } { try { if (stmt != null) { stmt.close(); } if (connection != null) { connection.close(); } } catch (sqlexception e2) { e2.printstacktrace(); } } return bookslist; } public int getnoofrecords() { return noofrecords; } } servlet: booksinfoservlet.java
package com.sreejonee.servlet; import java.io.ioexception; import java.util.list; import javax.servlet.requestdispatcher; import javax.servlet.servletexception; import javax.servlet.annotation.webservlet; import javax.servlet.http.httpservlet; import javax.servlet.http.httpservletrequest; import javax.servlet.http.httpservletresponse; import com.sreejonee.books.booksinfo; import com.sreejonee.books.booksinfodao; @webservlet("/booksinfoservlet") public class booksinfoservlet extends httpservlet { private static final long serialversionuid = 1l; /** * @see httpservlet#httpservlet() */ public booksinfoservlet() { super(); } /** * @see httpservlet#doget(httpservletrequest request, httpservletresponse response) */ protected void doget(httpservletrequest request, httpservletresponse response) throws servletexception, ioexception { int page = 1; int recordsperpage = 5; if(request.getparameter("page") != null) page = integer.parseint(request.getparameter("page")); booksinfodao booksinfodao = new booksinfodao(); list<booksinfo> bookslist = booksinfodao.viewallbooksinfo((page-1)*recordsperpage, recordsperpage); int noofrecords = booksinfodao.getnoofrecords(); int noofpages = (int) math.ceil(noofrecords * 1.0 / recordsperpage); request.setattribute("bookslist", bookslist); request.setattribute("noofpages", noofpages); request.setattribute("currentpage", page); requestdispatcher view = request.getrequestdispatcher("user.jsp"); view.forward(request, response); } /** * @see httpservlet#dopost(httpservletrequest request, httpservletresponse response) */ protected void dopost(httpservletrequest request, httpservletresponse response) throws servletexception, ioexception { } } jsp page:
<c:foreach var="books" items="${bookslist}"> <div id="${books.filename}" class="single_midlecontent_component"> <div class="socialicon_on_post"> <h4> <i class="fa fa-facebook-square"></i> </h4> </div> <div class="title_of_post "> <a href="#" class="media-left"> <img class="media-object" src="${books.getfilepath()+file.separator+books.getthumcoverimag()}" alt="..."> </a> <h5 class="media-body "> <span><a href="">${books.getbookname()}</a></span> </h5> <h5 class="media-body "> <span>by <a href="">${books.getwritername()}</a></span> </h5> <p class="date_time media-body">${books.getdate_time()}</p> </div> <div class="body_of_post"> <p>${books.getbook_details()}</p> <img src="" class="img-responsive" alt="responsive image"> </div> <div class="fb-like" data-href="https://developers.facebook.com/docs/plugins/" data-layout="standard" data-action="like" data-show-faces="true" data-share="true"></div> <div class="download_book"> <form action="downloadfileservlet" method="post"> <input type="hidden" name="filename" value="${books.getfilename()}"> <div> <input type="hidden" name="parentscat_name" value="${books.getparentscat_name()}"> </div> <div class="download_button"> <input class="btn btn-default " type="submit" value="download"> </div> </form> </div> <div class="bottom_of_post"></div> </div>
i can see 2 errors here.
first one:
string query = "select sql_calc_found_rows * library order date_time desc limit"+ offset + ", " + noofrecords; there space missing after limit -- way wrote it, generate ... limit10, 5.
second one:
i think caller of viewallbooksinfo() gives wrong offset argument. based on error, assume offset -10 illegal because arguments limit clause in mysql need non-negative.
in updated question, show code:
if(request.getparameter("page") != null) page = integer.parseint(request.getparameter("page")); booksinfodao booksinfodao = new booksinfodao(); list<booksinfo> bookslist = booksinfodao.viewallbooksinfo((page-1)*recordsperpage, recordsperpage); obviously, because recordsperpage 5, , offset -10, page seems -1. have not shown code page calls booksinfoservlet, guess have entered -1 requested page number there.
Comments
Post a Comment