Jsp中MySQL分页查询语句

qq_39208530 2017-11-18 10:02:20

public class GetCon { //得到Connection对象
public GetCon(){

}
public static Connection getCon(){
String url="jdbc:mysql://localhost/userdata";
String user="root";
String password="121609";
Connection con=null;
try{
Class.forName("com.mysql.jdbc.Driver");

}catch(ClassNotFoundException e){
System.out.println("加载驱动器类出现错误");
}
try{
con=DriverManager.getConnection(url, user, password);
}catch(SQLException e){
System.out.println("SQL异常");
}
return con;
}
}
public class splitPage {//实现分页查询
private Connection con=null;
private Statement sta=null;
private ResultSet rs=null;
private ResultSetMetaData rsmd=null;
private String sql=null;
private int pageCount=0;
private int rowCount=0;
private int pageSize=0;
public splitPage(Connection con){
this.con=con;
}
public void initialize(String sql, int pageSize, int apage){
this.pageSize=pageSize;
this.sql=sql;
int arows=pageSize*(apage-1);
try{
sta=this.con.createStatement();
rs=sta.executeQuery(sql);
rsmd=rs.getMetaData();
if(rs!=null){
rs.last();
this.rowCount=rs.getRow();
}
rs.close();
this.pageCount=(this.rowCount-1)/pageSize+1;
this.sql=sql+"limit"+arows+","+pageSize;
}catch(SQLException e){
System.out.println(e.toString());
}
}
public Vector getPage(){
Vector v=new Vector();
try{
sta=this.con.createStatement();
rs=sta.executeQuery(sql);
rsmd=rs.getMetaData();
while(rs.next()){
String s[]=new String[rsmd.getColumnCount()];
for(int i=0; i<s.length; i++){
s[i]=rs.getString(i+1);
}
v.addElement(s);
}
rs.close();
sta.close();
}catch(SQLException e){
System.out.println(e.toString());
}
return v;
}
public int getpageCount(){
return this.pageCount;
}
public int getrowCount(){
return this.rowCount;
}
}
<body> //jsp内容
<%! int pageSize=3; %>
<% int showPage=1;
String sql="select * from student";
String str=request.getParameter("showPage");
splitPage pages=new splitPage(GetCon.getCon());
if(str!=null){
try{
showPage=Integer.parseInt(str);
if(showPage<1){
showPage=1;
}
}catch(ClassCastException e){
showPage=1;
}
}
pages.initialize(sql, pageSize, showPage);
Vector v=pages.getPage();
%>
<table border=1>
<tr>
<th>学号</th><th>姓名</th><th>地址</th><th>出生日期</th></tr>
<% for(int i=0; i<v.size(); i++){
String[] s=(String[])v.get(i);%>
<tr>
<td><%=s[0] %></td><td><%=s[1] %></td><td><%=s[2] %></td><td><%=s[3] %></td>
</tr>
<%}%>
</table>
<table>
<tr>
<td>共<%=pages.getrowCount() %>条</td><td><%=pageSize %>条/页</td><td>第<%=showPage %>页</td><td>共<%=pages.getpageCount() %>页</td>
</tr>
<tr>
<td><a href="MyJsp9.jsp?showPage=1">[首页]</a></td><td><a href="MyJsp9.jsp?showPage=<%=showPage-1%>">上一页</a></td><td><a href="MyJsp9.jsp?showPage=<%=showPage+1%>">下一页</a></td><td><a href="MyJsp9.jsp?showPage=<%=pages.getpageCount()%>">[尾页]</a></td>
</tr>
</table>
<form name=form method=get>
<select name=showPage>
<%for(int i=1; i<=pages.getpageCount(); i++){ %>
<option value=<%=i %>><%=i %></option>
<%} %>
</select>
<input type=submit name=submit value=提交>
</form>
</body>
错误：com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '3' at line 1

...全文