Getting sqlexception in servlet

Question

I have this code:

    public class cuemath extends HttpServlet{
   /**
    *
    */
   private static final long serialVersionUID = 1L;
   String Q;
   String DOWNLOAD="";

   ArrayList<String> _DOWNLOAD_ ;



   public void doGet (HttpServletRequest req,
                   HttpServletResponse res)
   throws ServletException, IOException
   {
     PrintWriter out = res.getWriter();

   try{Connection myCon;


   _DOWNLOAD_ =new ArrayList<String>();



   Statement myStmt;

   String userName = "xxxx";
   String password = "xxxxxxxx";
   String url= "jdbc:mysql://localhost:3306/maths?user="
   +userName
   +"&password="
   +password;

   Class.forName ("com.mysql.jdbc.Driver").newInstance ();



   myCon = DriverManager.getConnection (url);


   myStmt = myCon.createStatement();
   ResultSet rs;

   String pm=req.getParameter("LessonId").trim();
   int p=Integer.parseInt(pm);

   String query="select * from lessons where LESSONS_ID="+p;
   rs= myStmt.executeQuery(query);

   out.println("<CUEMATH>");
   out.println("<lessonid>");


           out.println(rs.getString("LESSONS_ID"));

   out.println("</lessonid>");

   out.println("<lessontext>");

           out.println(replaceTags(rs.getString("LESSONS_TEXT")));

   out.println("</lessontext>");
   out.println("</CUEMATH>");

   }
   catch (Exception e) {
        e.printStackTrace();
}
   }
    private  String replaceTags(String Input){

           if(!Input.equals(null)){

                   String one =">";
                   String two ="<";
                   String five ="\n";
                   String seven ="\\t";

                   String three ="&gt;";
                   String four ="&lt;";
                   String six = "backslash";
                   String eight="tabspace";


                   Input = Input.replaceAll(one, three);
                   Input = Input.replaceAll(two, four);
                   Input = Input.replaceAll(five, six);
                   Input = Input.replaceAll(seven, eight);



                   Input=Input.replaceAll(five, six);
                   Input = Input.replaceAll(one, three);
                   Input = Input.replaceAll(two, four);


                   Input=Input.replaceAll(five, six);
                   return Input;


           }
           else
                   return "";


   }

}

I am getting this error log:

               java.sql.SQLException
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
at com.mysql.jdbc.ResultSetImpl.checkRowPos(ResultSetImpl.java:815)
at com.mysql.jdbc.ResultSetImpl.getStringInternal(ResultSetImpl.java:5528)
at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5448)
at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5488)
at cue.math.cuemath.doGet(cuemath.java:75)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:235)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:189)
at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:91)
at org.jboss.web.tomcat.security.SecurityContextEstablishmentValve.invoke(SecurityContextEstablishmentValve.java:92)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.jboss.web.tomcat.service.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:157)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:325)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:828)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:601)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Thread.java:619)

I am generating a xml from database values. I am newbie can anyone help me with this problem?

Answer 1

I think Ajj is right. The specific problem I can see after looking at your code is that LESSONS_ID field is a string and you are appending it to your query without quoting it.

String query="select * from lessons where LESSONS_ID="+p;

if LESSONS_ID had the value "basics" (say), then the query being sent to MySQL is:

select * from lessons where LESSONS_ID=basics

Which is not going to work. If you quote it in your string using either single or double quotes then it would work:

String query="select * from lessons where LESSONS_ID='"+p+"'";

query is now:

select * from lessons where LESSONS_ID='basics'

You could also consider using the PreparedStatement class in preference to Statement as that handles the parameter types reducing such issues.

You can look here for a PreparedStatement example.

Hope that helps.

Answer 2

Before you start retrieving results from the ResultSet object, you should add a call to rs.next() This call returns a boolean to indicate if a further row is available (in this instance, the first row), and moves the internal result set pointer to the next row of results (in this case the first row).

Often, you will see this in code:

if (rs.next())
{
  // retrieve results from rs object
}

or for multiple rows

while (rs.next())
{
  // each iteration through this loop
  // is a separate result row...
}

Answer 3

Better check the value of 'p' which you are appending to the end of the query once.

The problem is with the query creation itself. run the same query in the editor(with the value of p which you are getting). Double check it.