-1

Why aren't there any results for this query? Only "Movies!" is printed when i run this servlet.

import java.io.*;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.sql.*;

public class Service extends HttpServlet {
 private static final long serialVersionUID = 1L;

 protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
  response.setContentType("text/html");
  PrintWriter out = response.getWriter();
  out.println("Movies!");

  Connection connection = null;
  Statement statement = null;
  try {
   connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test");   
   statement = connection.createStatement();
   String query = "SELECT * FROM movies";
   ResultSet rs = statement.executeQuery(query);
   while(rs.next()) {
    out.println("result set");
    out.print(rs.getInt(1));
    out.print(rs.getString(2));
    out.print(rs.getInt(3));
    out.print(rs.getInt(4));
   }
  } catch(SQLException e) {
   e.printStackTrace();
  }
 }
}
BalusC
  • 1,082,665
  • 372
  • 3,610
  • 3,555
aLk
  • 19
  • 2
  • 3

5 Answers5

2

Best guess: There are no rows in the table. What's the result when you execute the query manually ?

mysql test # connect to mysql

SELECT * FROM movies;

Community
  • 1
  • 1
b7kich
  • 4,253
  • 3
  • 28
  • 29
2

You're writing exceptions to server log file by e.printStackTrace() instead of throwing it so that it would end up in a nice error page in webbrowser.

Fix it as follows:

throw new ServletException("Querying from DB failed!", e);

Otherwise you've got to dig in the server logs for the exact cause of the problem.

Unrelated to the actual problem, remember to close your resources properly in a finally block. You're leaking them. See also the basic JDBC tutorial.

Update: finally, you got the cause of the problem:

java.sql.SQLException: No suitable driver found

You need to download the JDBC driver, put the JAR file in /WEB-INF/lib folder and load it in your code as follows before you acquire any connection:

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

See also:

Community
  • 1
  • 1
BalusC
  • 1,082,665
  • 372
  • 3,610
  • 3,555
  • "java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/test" Hmm – aLk Dec 24 '10 at 04:45
1

Download MySQL JDBC Driver from

http://dev.mysql.com/downloads/connector/j/

Copy jar into WEB-INF/lib folder

Ratna Dinakar
  • 1,573
  • 13
  • 16
0

hi before compilation you must put the clsses12.jar or other Ojdc14 jar file put in class path then make compile . some sample code here ResultSet res=statement.executeQuery("SELECT FIRST-NAME FROM FOO"); StringBuffer sb=new StringBuffer(); while(rs.next()) { sb.append("\n"+rs.getString(1)); }

Anil Reddaboina
  • 221
  • 1
  • 4
  • 1.) the jar file you mentioned need not be on the classpath during compilation, but only when executing your code. 2.) the jar files you mention are Oracle drivers jars, while the question seems to use a MySQL database. – Joachim Sauer Dec 24 '10 at 07:59
0

Hi Download MYSQL JDBC driver from http://www.mysql.com/products/connector/ under the like JDBC Driver for MYSQL. place jdbc jar file at location - /WEB-INF/lib - in your application.

In your code following statement is missing

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

Place it before your following line of code

connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test");

And restart the server..Make sure your database service is running...

And check..

I hope you would not get the error again...

~ Priyanjan

Priyanjan
  • 329
  • 3
  • 5