SQL Help and Servlet help


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 5 of 5

Thread: SQL Help and Servlet help

  1. #1
    Join Date
    Feb 2005
    Posts
    55

    SQL Help and Servlet help

    I created the following servlet, it is meant to access an SQL database and display records of a movieTitle and a review. My code only displays the headings and no records and an error. The sqlException error that is thrown is Column Index out of range, 3 > 2.
    in the database i have a int reviewId, String movieTitle and a String review. Can anyone help?
    Thanks Trixxma

    /*
    * MovieServlet.java
    *
    * Created on 3 September 2005, 21:50
    */

    import java.io.*;
    import java.net.*;
    import java.sql.Connection;
    import java.sql.Statement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import javax.servlet.*;
    import javax.servlet.http.*;

    /**
    *
    * @author Trixxma
    * @version
    */
    public class MovieServlet extends HttpServlet {

    /** Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods.
    * @param request servlet request
    * @param response servlet response
    */
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
    sendMovies(response);
    }

    public void sendMovies(HttpServletResponse response) throws IOException {
    PrintWriter out = response.getWriter();
    out.println("<html>");
    out.println("<head>");
    out.println("<title>Servlet a</title>");
    out.println("<link href='main.css' rel='stylesheet' type='text/css' />");
    out.println("</head>");
    out.println("<body>");
    out.println("<div id='navbar'>");
    out.println("<img src='images/heading.gif'>");
    out.println("<ul>");
    out.println("<li><a href='Index.html '>Home</a></li>");
    out.println("<li><a href='MovieDatabase '>Movie Database</a></li>");
    out.println("<li><a href='Registration.html '>Register</a></li>");
    out.println("<li><a href='Review'>Create a Review</a></li>");
    out.println("<li><a href='Comment'>Comment</a></li>");
    out.println("<li><a href='Login.html '>Login</a></li>");
    out.println("<li><a href='Movie?logout=1' value='logout' name='logout' >Logout</a></li>");
    out.println("</div>");
    out.println("<div id='main'>");

    String query = "SELECT movieTitle, review" + " FROM movie";

    try {
    Connection con = DataManager.getInstance().getConnection();
    ResultSet result;
    Statement stmt = con.createStatement();

    out.println("<TABLE>");
    out.println("<TR>");
    out.println("<TH>Movie Title</TH>");
    out.println("<TH>Review</TH>");
    out.println("<TH></TH>");
    out.println("</TR>");
    result = stmt.executeQuery(query);

    while (result.next()){
    int reviewid = result.getInt(1);
    out.println("<TR>");
    out.println("<TD>" + result.getString(2) + "</TD>");
    out.println("<TD>" + result.getString(3) + "</TD>");
    out.println("<TD><a href=Comment?reviewid =" + reviewid + ">Add a comment</a></TD>");
    out.println("</TR>");
    }
    result.close();
    } catch (SQLException se) {
    out.println("<p>There has been a problem finding results.");
    //out.println("<p>SQL Error executing query=<br>"+query+"<br>");
    out.println(se.getMessage());
    } catch (Exception e){
    out.println(e.getMessage());
    }

    out.println("</TABLE>");
    out.println("</div>");
    out.println("<div id='left'>");
    out.println("<center><P>Search the movie database</P></center>");
    out.println("<center><form action=Search METHOD='POST'>");
    out.println("Search: <input type='text' name='search' size='40'>");
    out.println("<center><input type='submit' value='submit' name='submit'></center>");
    out.println("</form></center>");
    out.println("</div>");
    out.println("</body>");
    out.println("</html>");
    out.close();
    }

    // <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
    /** Handles the HTTP <code>GET</code> method.
    * @param request servlet request
    * @param response servlet response
    */
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
    processRequest(request, response);
    }

    /** Handles the HTTP <code>POST</code> method.
    * @param request servlet request
    * @param response servlet response
    */
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
    processRequest(request, response);
    }

    /** Returns a short description of the servlet.
    */
    public String getServletInfo() {
    return "Short description";
    }
    // </editor-fold>
    }
    Last edited by trixma; 09-08-2005 at 05:17 AM.

  2. #2
    Join Date
    Jul 2005
    Location
    SW MO, USA
    Posts
    299
    Sorry, I don't know SQL.
    I have a recommendation for when you post messages about errors you get.
    Copy ALL of the error message and paste it in your posting. If you compile your program with the -g option, the error message will include the line number of the source program where the error occured. Also be sure to use printStackTrace() in the catch clause. It shows the line number of the error.

    Also some println() statements to show the contents of variables is useful when debugging your code.

  3. #3
    Join Date
    Aug 2004
    Posts
    46
    Your query is "SELECT movieTitle, review FROM movie", which returns two columns. You try to access a non-existent third column with " result.getString(3) ".

  4. #4
    Join Date
    Feb 2005
    Posts
    55
    Thanks that worked, I have another question
    I am trying to search the database and only the titles appear at the top, the code compiles perfectily but no results are displayed, the code is shown below

    /*
    * SearchServlet.java
    *
    * Created on 3 September 2005, 19:55
    */

    import java.io.*;
    import java.net.*;
    import java.util.*;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Connection;
    import java.sql.Statement;
    import java.sql.*;
    import javax.servlet.*;
    import javax.servlet.http.*;

    /**
    *
    * @author Trixxma
    * @version
    */
    public class SearchServlet extends HttpServlet {

    /** Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods.
    * @param request servlet request
    * @param response servlet response
    */
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
    response.setContentType("text/html;charset=UTF-8");
    PrintWriter out = response.getWriter();

    String keyword = request.getParameter("keyword");
    sendSearchResult(request, response);
    }

    public void sendSearchResult(HttpServletRequest request, HttpServletResponse response) throws IOException {
    PrintWriter out = response.getWriter();
    out.println("<html>");
    out.println("<head>");
    out.println("<title>Servlet a</title>");
    out.println("<link href='main.css' rel='stylesheet' type='text/css' />");
    out.println("</head>");
    out.println("<body>");
    out.println("<div id='navbar'>");
    out.println("<img src='images/heading.gif'>");
    out.println("<ul>");
    out.println("<li><a href='Index.html '>Home</a></li>");
    out.println("<li><a href='Movie '>Movie Database</a></li>");
    out.println("<li><a href='Registration.html '>Register</a></li>");
    out.println("<li><a href='Login.html '>Login</a></li>");
    out.println("<li><a href='Movie?logout=1' value='logout' name='logout' >Logout</a></li>");
    out.println("</div>");
    out.println("<div id='main'>");

    try {
    Connection conn = DataManager.getInstance().getConnection();
    Statement stmt = conn.createStatement();

    out.println("<TABLE>");
    out.println("<TR>");
    out.println("<TH>Title</TH>");
    out.println("<TH>Review</TH>");
    out.println("</TR>");
    String query = "SELECT movieTitle, review" + " FROM movie"
    + " WHERE movieTitle LIKE '%" + " +keyword+" +
    "%'";
    ResultSet result = stmt.executeQuery(query);
    while (result.next()){
    out.println("<TR>");
    out.println("<TD>" + result.getString(1) + "</TD>");
    out.println("<TD>" + result.getString(2) + "</TD>");
    out.println("</TR>");
    }
    } catch (SQLException se) {
    out.println("<p>There has been a problem executing your search.</p>");
    // out.println("<p>SQL Error executing query='"+query+"'");
    out.println(se.getMessage());
    } catch (Exception e){
    }
    out.println("</TABLE>");
    out.println("</div>");
    out.println("<div id='left'>");
    out.println("<center><P>Search the movie database</P></center>");
    out.println("<center><form action=Search METHOD='POST'>");
    out.println("Search: <input type='text' name='keyword' size='40'>");
    out.println("<center><input type='submit' value='submit' name='submit'></center>");
    out.println("</form></center>");
    out.println("</div>");
    out.println("</body>");
    out.println("</html>");
    out.close();
    }

    // <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
    /** Handles the HTTP <code>GET</code> method.
    * @param request servlet request
    * @param response servlet response
    */
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
    processRequest(request, response);
    }

    /** Handles the HTTP <code>POST</code> method.
    * @param request servlet request
    * @param response servlet response
    */
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
    processRequest(request, response);
    }

    /** Returns a short description of the servlet.
    */
    public String getServletInfo() {
    return "Short description";
    }
    // </editor-fold>
    }
    Last edited by trixma; 09-08-2005 at 09:12 PM.

  5. #5
    Join Date
    Aug 2004
    Posts
    46
    How many rows are returned when you run the query outside of your Servlet?

    Also, I refrained from mentioning it before, but there are some fairly serious problems with your code beyond not getting the results you're looking for. One should always clean up data resources after using them (i.e., close()). Also, it might be helpful (and indeed, it's good OOP practice) to separate out responsibilities into different classes. Your poor servlet is overworked; delegate display logic to a JSP and data transaction logic to helper classes.

    Have a look at the following:

    The Java™ Tutorial - Trail: JDBC™ Database Access
    Understanding JavaServer Pages Model 2 architecture
    Web-Tier Application Framework Design
    Almost All Java Web Apps Need Model 2

    Hope this helps!
    Last edited by yawmark; 09-09-2005 at 08:14 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center