Error loading the MySQL database table from a jsp

Nov 05, 2014 01:53:26

I m new to programming and am working on the college project. I have MySQL 5.6.20, Eclipse Kepler and Apache Tomcat 7.0.54. I m trying to retrieve the whole table from MySQL schema into a jsp. All the possible things i can do, i have already tried, but none of them are helping. The following attachment contains the whole project. I have my sql connection code in Sample.java file, which i want to get working in Fetch.jsp on the Submit button click. But it shows the tomcat error as localhost:8080/Sample HTTP Status 404-/Sample ; type Status report; message /Sample; Description The requested resource is not available.

The help will be very much appreciated ASAP,please!

package testusecase;
import java.io.IOException;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Date;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class Sample extends HttpServlet {
/**
*
*/
private static final long serialVersionUID = 8462790020399479519L;
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
@SuppressWarnings("unused")
Sample instance = new Sample();
final String URL = "jdbc:mysql://localhost:3306/samschema";
final String USER = "root";
final String PASSWORD = "password";
final String DRIVER_CLASS = "com.mysql.jdbc.Driver";
try {
Class.forName(DRIVER_CLASS);
Connection connection = null;
connection = DriverManager.getConnection(URL, USER, PASSWORD);
String query = "SELECT * FROM samschema.sample1";
Statement st = connection.createStatement();
ResultSet rs = st.executeQuery(query);
while (rs.next()) {
int serial_no = rs.getInt("serial_no");
String first_name = rs.getString("first_name");
String middle_name = rs.getString("middle_name");
String last_name = rs.getString("last_name");
String date_of_birth = rs.getString("date_of_birth");
String contact_no = rs.getString("contact_no");
String email_id = rs.getString("email_id");
String residential_address = rs.getString("residential_address");
String city = rs.getString("city");
BigDecimal percentage_x = rs.getBigDecimal("percentage_x");
Date yop_x = rs.getDate("yop_x");
String board_x = rs.getString("board_x");
String percentage_xii = rs.getString("percentage_xii");
String yop_xii = rs.getString("yop_xii");
String board_xii = rs.getString("board_xii");
String btech_stream = rs.getString("btech_stream");
String mtech_stream = rs.getString("mtech_stream");
String other_stream = rs.getString("other_stream");
String percentage_graduation = rs.getString("percentage_graduation");
String mca_percentage = rs.getString("mca_percentage");
String year_of_graduation = rs.getString("year_of_graduation");
String d_to_d = rs.getString("d_to_d");
String mtech_percentage = rs.getString("mtech_percentage");
String yop_diploma = rs.getString("yop_diploma");
String percentage_d_to_d = rs.getString("percentage_d_to_d");
// print the results
System.out.format("%d, %s, %s, %s, %s, %s, %s, %s, %s, %d, %d, %s,
%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s\n", serial_no, first_name,
middle_name,last_name, date_of_birth,contact_no,email_id,residential_address,
city, percentage_x, yop_x, board_x, percentage_xii, yop_xii, board_xii, btech_stream,
mtech_stream, other_stream, percentage_graduation, mca_percentage,
year_of_graduation, d_to_d, mtech_percentage, yop_diploma, percentage_d_to_d);
RequestDispatcher view = req.getRequestDispatcher("Fetch.jsp");
view.forward(req, resp);
}
st.close();
}
catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}

<?xml version="1.0" encoding="ISO-8859-1"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://java.sun.com/xml/ns/javaee"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0">
<display-name>TestUseCase</display-name>
<welcome-file-list>
<welcome-file>index.html</welcome-file>
<welcome-file>index.htm</welcome-file>
<welcome-file>index.jsp</welcome-file>
<welcome-file>default.html</welcome-file>
<welcome-file>default.htm</welcome-file>
<welcome-file>default.jsp</welcome-file>
</welcome-file-list>
<security-constraint>
<web-resource-collection>
<web-resource-name>TestUseCase</web-resource-name>
<url-pattern>/*</url-pattern>
<http-method>GET</http-method>
<http-method>POST</http-method>
</web-resource-collection>
</security-constraint>
<servlet>
<description></description>
<display-name>Login</display-name>
<servlet-name>Login</servlet-name>
<jsp-file>/Login.jsp</jsp-file>
</servlet>
<servlet-mapping>
<servlet-name>Login</servlet-name>
<url-pattern>/Login</url-pattern>
</servlet-mapping>
<servlet>
<description></description>
<display-name>Sample</display-name>
<servlet-name>Sample</servlet-name>
<servlet-class>/Sample</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>Sample</servlet-name>
<url-pattern>/Sample</url-pattern>
</servlet-mapping>
</web-app>

Nov 05, 2014 04:57:42

In your web.xml why the / preceding Sample in servlet-class? remove the / .. and think it should have the fully qualified class name

Also the action property should be preceded by the webapplication context? like /YourWebAppName/Sample

hope this helps..

Nov 05, 2014 05:25:48

Editing worked, i think. Removed / from servlet class and from action property. But this time the error is ClassNotFoundException which is called in the Sample.java class, meaning, the click event is NOW calling the class and then it is generating the exception. Suggest me, what to do NOW?
By the way, all the other fully qualified class names didn't work, just showed the Resource not available error!!
Help me on the exception error!

Nov 05, 2014 05:34:32

Can you post your changes?

Nov 05, 2014 05:46:52

Fetch.jsp

web.xml file

and the error message

HTTP Status 500 - Error instantiating servlet class Sample
type Exception report
message Error instantiating servlet class Sample
description The server encountered an internal error that prevented it from fulfilling this request.
exception
javax.servlet.ServletException: Error instantiating servlet class Sample
org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:610)
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1040)
org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:607)
org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316)
java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
java.lang.Thread.run(Unknown Source)
root cause
java.lang.ClassNotFoundException: Sample
org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1720)
org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1571)
org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:610)
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1040)
org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:607)
org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316)
java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
java.lang.Thread.run(Unknown Source)
note The full stack trace of the root cause is available in the Apache Tomcat/7.0.54 logs.
Apache Tomcat/7.0.54

Nov 05, 2014 06:13:15

The class should be fully qualified.. like this..

<servlet-class>testusecase.Sample</servlet-class>

Nov 05, 2014 09:40:48

Gamini Sirisena wrote:The class should be fully qualified.. like this..

<servlet-class>testusecase.Sample</servlet-class>

Yeah,that helped.After this i m not getting any errors but just a blank page.Should i make another table in jsp form to fill in with the database table column entries?

Nov 06, 2014 07:59:59

Let's take this step by step..

You are doing this..

RequestDispatcher view = req.getRequestDispatcher("Fetch.jsp");
view.forward(req, resp);

comment those lines for now. Browse the web to find how to print stuff to the browser from your servlet. Once you are successful with printing a test message you can try printing all results from your query to the browser.
we'll talk after that..

Nov 06, 2014 10:27:28

I have tried simple plain text like "Hello" and also, i commented the RequestDispatcher as you said to try getting the simple output as text.It is working with the help of PrintWriter object, at both the places, outside and inside the try{}. But the real problem i can't figure out is about displaying the table of the database schema. What exactly is it that i should do now? Sorry to be such a pain, but really need your help with this. It's a small,college project and it's already high time. Please Guide me the changes in the servlet ASAP.

Nov 07, 2014 01:09:32

You want to display results from your database query right?

Now the way you printed the "Hello" you can print each value to the browser.
If you want to display the data in a HTML table then you have to write that to the browser as wel..

like..

Nov 07, 2014 02:13:52

Sorry for the mess and trouble, but just minutes ago, i finally found a way out to print table after your advice to start search from how to print simple text. Now the problem is regarding the empty/Null values present in the database. jsp output isn't showing rest of the records if it finds a Null value in the first row itself.

Example: Scenario 1:Here the first record doesn't contain any value for 12th percentage in the database. So the rest of the records aren't showing up at all.
Scenario 2:The record with Serial no 8 have null value for Middle name into the database and from there the rest of the table show drops off.
This was just to verify that the problem i think is exactly about the null values.
Scenario 2 is after commenting the code. The output contains some personal information, so the snapshots aren't included.Sorry!

package testusecase;
import java.io.IOException;
import java.io.PrintWriter;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Date;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class Sample extends HttpServlet {
/**
*
*/
private static final long serialVersionUID = 8462790020399479519L;
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
@SuppressWarnings("unused")
Sample instance = new Sample();
final String URL = "jdbc:mysql://localhost:3306/samschema";
final String USER = "root";
final String PASSWORD = "password";
final String DRIVER_CLASS = "com.mysql.jdbc.Driver";
try {
Class.forName(DRIVER_CLASS);
Connection connection = null;
connection = DriverManager.getConnection(URL, USER, PASSWORD);
String query = "SELECT * FROM samschema.sample1";
Statement st = connection.createStatement();
ResultSet rs = st.executeQuery(query);
PrintWriter out = resp.getWriter();
out.println("<html>");
out.println("<head><title>DatabaseSample1</title></head>");
out.println("<body>");
out.println("<h1>Student Placement Cell Online</h1>");
out.println("</body></html>");
out.println("<TABLE BORDER=1><TR><TH>Serial_No</TH><TH>First_Name</TH><TH>Middle Name</TH><TH>Last_Name</TH><TH>Date_Of_Birth</TH><TH>Gender</TH><TH>Contact_No</TH><TH>Email_Id</TH><TH>Residentail_Address</TH><TH>City</TH><TH>10th_Percentage</TH><TH>Year_Of_Passing_10th</TH><TH>10th_Board</TH><TH>12th_Percentage</TH><TH>Year_Of_Passing_12th</TH><TH>12th_Board</TH><TH>B.Tech_Stream</TH><TH>M.Tech_Stream</TH></TR>");
while (rs.next()) {
out.println("<TR>");
out.println("<TD>");
out.print((rs.getObject(1)).toString());
out.print("</TD><TD>");
out.print(rs.getObject(2).toString());
out.print("</TD><TD>");
out.print((rs.getObject(3)).toString());
out.print("</TD><TD>");
out.print(rs.getObject(4).toString());
out.print("</TD><TD>");
out.print((rs.getObject(5)).toString());
out.print("</TD><TD>");
out.print(rs.getObject(6).toString());
out.print("</TD><TD>");
out.print((rs.getObject(7)).toString());
out.print("</TD><TD>");
out.print(rs.getObject(8).toString());
out.print("</TD><TD>");
out.print((rs.getObject(9)).toString());
out.print("</TD><TD>");
out.print(rs.getObject(10).toString());
out.print("</TD><TD>");
out.print((rs.getObject(11)).toString());
out.print("</TD><TD>");
out.print(rs.getObject(12).toString());
out.print("</TD><TD>");
/* out.print((rs.getObject(13)).toString());
out.print("</TD><TD>");
out.print(rs.getObject(14).toString());
out.print("</TD><TD>");
out.print((rs.getObject(15)).toString());
out.print("</TD><TD>");
out.print(rs.getObject(16).toString());
out.print("</TD><TD>");
out.print((rs.getObject(17)).toString());
out.print("</TD><TD>");
out.print(rs.getObject(18).toString());
out.print("</TD><TD>");
out.print((rs.getObject(19)).toString());
out.print("</TD><TD>");
out.print(rs.getObject(20).toString());
out.print("</TD><TD>");
out.print((rs.getObject(21)).toString());
out.print("</TD><TD>");
out.print(rs.getObject(22).toString());
out.print("</TD><TD>");
out.print((rs.getObject(23)).toString());
out.print("</TD><TD>");
out.print(rs.getObject(24).toString());
out.print("</TD><TD>");
out.println("</TR>");
*/
}
st.close();
}
catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}

Nov 07, 2014 03:08:57

with this
out.print((rs.getObject(1)).toString());

you are dereferencing g the object returned from the result set. Which would throw a null pointer Exception if it was null. I sugget you do.. for now.. which will handle the nulls.. (but will print null)

out.print((rs.getString(1)+""));

and aren't you seeing a null pointer stack trace in the console?

Nov 07, 2014 03:44:20

Having HTML code inside servlet, is not good.. You should learn JSP, and use JSTL and EL to display the data you want in the view.
Since you are learning now, start learning the good habits..

Nov 09, 2014 09:50:59

@GaminiSirisena, Sir, it sure works!! Thank you a lot.

@Prasad Krishnegowda...Thanks to you too. keeping that advice in mind, i am looking forward to learn it right away.

Nov 10, 2014 03:18:58

Hi there,
There is a way to shorten your code and make it efficient. Try to use JSTL tag libraries. I use it all time and it works well.

Dec 22, 2014 15:55:12

Hi, you have an HTML code inside your servlet, it's a bad practice You should learn JSP (JSP standared tag library) it will hep you to generate HTML code without using java code , also you can use JSTL inside your JSP pages try to learn this technologies, they wil help you .

Aaaaaand ... we're on the march. Stylin. Get with it tiny ad.

a bit of art, as a gift, that will fit in a stocking

https://gardener-gift.com