SQL语法错误?

大家帮我看一下这个错误，当在search.html中输入11时，出现下面的错误，　　　谢谢了，org.apache.jasper.JasperException: Exception in JSP: /search/pages.jsp:4845:  con=DriverManager.getConnection("jdbc:mysql://localhost:3306/news","root","admin");
46:  stmt=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
47:  System.out.println(sql);
48:  rs=stmt.executeQuery(sql);
49:  rs.last();
50:  RowCount=rs.getRow();
51:  PageCount=((RowCount%PageSize)==0?(RowCount%PageSize):(RowCount%PageSize)+1);
Stacktrace:
org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:504)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:375)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
root cause javax.servlet.ServletException: 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 '= 1' at line 1
org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:858)
org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:791)
org.apache.jsp.search.pages_jsp._jspService(pages_jsp.java:255)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:332)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
root cause 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 '= 1' at line 1
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
com.mysql.jdbc.Connection.execSQL(Connection.java:3118)
com.mysql.jdbc.Connection.execSQL(Connection.java:3047)
com.mysql.jdbc.Statement.executeQuery(Statement.java:1166)
org.apache.jsp.search.pages_jsp._jspService(pages_jsp.java:95)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:332)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)pages.jsp源码如下
<%@ page language="java" import="java.sql.*" pageEncoding="gb2312"%>
<%
String searchBookID=null;
String searchDate1=null;
String searchDate2=null;
String searchPrice1=null;
String searchPrice2=null;int PageSize=2;
int ShowPage=1;
int RowCount=0;
int PageCount=0;
Connection con=null;
Statement stmt=null;
ResultSet rs=null;
%>
<%
searchBookID=request.getParameter("textBookID");
searchDate1=request.getParameter("textDate1");
searchDate2=request.getParameter("textDate2");
searchPrice1=request.getParameter("textPrice1");
searchPrice2=request.getParameter("textPrice2");String sql="select * from book";
if(!searchBookID.equals(""))
{
sql+="where BookID = '"+searchBookID+"'";
}
else
{
     sql+="where BookID like '%'";
}
if(!searchDate1.equals(""))
{
sql+="and PDate>=#"+searchDate1+"# and PDate<=#"+searchDate2+"#";
}
if(!searchPrice1.equals(""))
{
sql+="and Price>="+searchPrice1+"and Price<="+searchPrice2;
}
 %>
 
 <%
 Class.forName("com.mysql.jdbc.Driver");
 con=DriverManager.getConnection("jdbc:mysql://localhost:3306/news","root","admin");
 stmt=con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
 System.out.println(sql);
 rs=stmt.executeQuery(sql);
 rs.last();
 RowCount=rs.getRow();
 PageCount=((RowCount%PageSize)==0?(RowCount%PageSize):(RowCount%PageSize)+1);
  %>
<html>
  <head>
    
    <title>高级搜索</title>
    
    <style type="text/css">
<!--
.STYLE1 {
color: blue;
font-size: 16px;
}
-->
    </style>
</head>
  
  <body>
    <p align="center" class="STYLE1">高级搜索      </p>
<% 
String ToPage=request.getParameter("ToPage");
if(ToPage!=null)
{
ShowPage=Integer.parseInt(ToPage);
if(ShowPage>PageCount)
{
  ShowPage=PageCount;
}
else if(ShowPage<=0)
{
ShowPage=1;
}
}
rs.absolute((ShowPage-1)*PageSize+1);
 %>
 当前在第<span class="STYLE2"><%= ShowPage%> </span>页 共<span class="STYLE2"><%= PageCount %>
 
 </span></p>
 <%  
for(int i=1;i<PageSize;i++)
{
%>
 <table width="80%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td>书籍编号 </td>
    <td><%= rs.getString("BookID") %></td>
  </tr>
  <tr>
    <td>出版日期</td>
    <td><%= rs.getDate("PDate") %></td>
  </tr>
  <tr>
    <td>总页数</td>
    <td><%= rs.getString("Pages") %></td>
  </tr>
  <tr>
    <td>价格</td>
    <td><%= rs.getInt("Price") %>&nbsp;</td>
  </tr>
  <tr>
    <td>书籍名称</td>
    <td><%= rs.getString("Title") %></td>
  </tr>
  <tr>
    <td>作者</td>
    <td><%= rs.getString("Author") %>&nbsp;</td>
  </tr>
  <tr>
    <td>备注</td>
    <td><%= rs.getString("BookMeno") %></td>
  </tr>
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
</table>
<% 
 if(!rs.next())
 break;
}
 %>
 <table width="80%" border="0" align="center" cellpadding="1" cellspacing="0">
  <tr>
  <% 
  if(ShowPage!=1)
  {
    
   %>
    <td><a href=pages.jsp?ToPage=<%= 1 %>>到第一页</a></td>
    <td><a href=pages.jsp?ToPage=<%= ShowPage -1 %>>到上一页</a></td>
<% } %>
 
 
 <% 
  if(ShowPage!=PageCount)
  {
    
   %>
    <td><a href=pages.jsp?ToPage=<%= ShowPage +1%>>到下一页</a></td>
    <td><a href=pages.jsp?ToPage=<%= PageCount %>>到最后一页</a></td>
<%
rs.close();
stmt.close();
con.close();
 } %>
  </tr>
</table>
 </body>
</html>search.html代码
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<title>高级搜索</title>
<style type="text/css">
<!--
.STYLE1 {
color: #FF0000;
font-size: 24px;
}
-->
</style>
</head><body>
<p align="center" class="STYLE1">高级搜索</p>
<hr />
<form id="form1" name="form1" method="post" action="pages.jsp">
  <p>请输入书籍标号：</p>
  <p>
    <input name="textBookID" type="text" id="textBookID" >
</p>
  <p>请输入出版日期:</p>
  <p>
    <input name="textDate1" type="text" id="textDate1" >　
    至  
    <input name="textDate2" type="text" id="textDate2" >
  </p>
  <p> 日期格式请采用2000/1/1这种格式</p>
  <p>请输入价格 :</p>
  <p>
    <input name="textPrice1" type="text" id="textPrice1" > 
    元至
    <input name="textPrice2" type="text" id="textPrice2" >
</p>
  <p>
    <input type="submit" name="Submit" value="确定" >
    <input type="reset" name="Submit2" value="清除" >
  </p>
</form>
<p align="center" class="STYLE1">&nbsp;</p>
</body>
</html>

解决方案 »

  1.   

    String sql="select * from book";
    if(!searchBookID.equals(""))
    {
    sql+="where BookID = '"+searchBookID+"'";
    }
    else
    {
         sql+="where BookID like '%'";
    }
    if(!searchDate1.equals(""))
    {
    sql+="and PDate>=#"+searchDate1+"# and PDate<=#"+searchDate2+"#";
    }
    if(!searchPrice1.equals(""))
    {
    sql+="and Price>="+searchPrice1+"and Price<="+searchPrice2;
    }估计这句sql到了mysql那里会变成
    select * from bookwhere  BookID = ......
      

  2.   

    where ,and 等条件连接时最好前面多加空格。
    我记得时间段的比较，好像不用#
    String sql="select * from book ";
    if(!searchBookID.equals(""))
    {
    sql+=" where BookID = '"+searchBookID+"'";
    }if(!searchDate1.equals(""))
    {
    sql+=" and PDate>='"+searchDate1+"' and PDate<='"+searchDate2+"'";
    }
    if(!searchPrice1.equals(""))
    {
    sql+=" and Price>="+searchPrice1+" and Price<="+searchPrice2;
    }
      

  3.   

    大哥，麻烦你用PreparedStatement好不好，都是SQL注入错误，补丁上面落补丁
      

  4.   

    # 是 access 里日期的写法