SQL Help and Servlet help

**trixma** · 09-08-2005 12:05 AM

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>
}

**Norm** · 09-08-2005 09:50 AM

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.

**yawmark** · 09-08-2005 11:45 AM

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) ".

**trixma** · 09-08-2005 07:32 PM

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>
}

**yawmark** · 09-09-2005 08:12 AM

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!

Thread: SQL Help and Servlet help

Thread Tools

Display

SQL Help and Servlet help

Bookmarks

Bookmarks

Posting Permissions