prepareStatement in servlet is not working

Get Started
1

i have created a delete.jsp page which checks for datatype entered in the fields and when user enters the student id will delete it from database.i am succesfull while following insert.jsp but i am geting proublem while updating and deleting. i have linked insertiion,updation and deletion jsp files to 1 home.html page which run in tomcat server. When i click delete or update it shows me fields to modify but when i click submit nothing happens. i have a feeling something about preparestatement not working. plz help

my database is as below

SQL> desc student_info;
Name Null? Type

SID NUMBER(9)
SNAME VARCHAR2(8)
SADD VARCHAR2(16)

and my delete.jsp page is as below

<html>
<script language='javascript'>
function isValid()
{
	if(f.stu_id.value=="")
	{
		alert(f.stu_id.name+" "+"field missing");
		f.stu_id.focus();
		return false;
	}
	if(isNaN(f.stu_id.value))
	{
		alert("stu_id should be numeric value");
		f.stu_id.focus();
		return false;
	}
	return true;
}
</script>
<body>
<form name=f action='./delete' method='post' onSubmit='return isValid()'>
<table border=1 cellpadding =7 cellspacing=7 align=center>
<caption><i><font size=4><u>Delete records</caption>
<tr>
<th>student id<th><input type="text" name="stu_id"></tr>
<tr>
<th>click<th><input type="submit" value="delete"></tr>
</table>
</form>
</body>
</html>

and my deleterecords.java file

import java.sql.*;
import java.io.*;
public class  deleterecords
{
	public deleterecords() throws Exception
	{
		Connection con=null;
		PreparedStatement ps=null;
		int rs=0;
		BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
		int sid=Integer.parseInt(br.readLine());
		System.out.println("enter the record number to delete:");
		Class.forName("oracle.jdbc.driver.OracleDriver");
		con=DriverManager.getConnection("jdbc:odbc:thin:@localhost:8080:ravi","system","tiger");
		
		ps=con.prepareStatement("delete from student_info where stu_id=?");
		ps.setInt(1,sid);
		rs=ps.executeUpdate();


	}
	public static void main(String args[]) throws Exception
	{
		new deleterecords();
	}
}
2

You will need to add your oracle jdbc driver jar file to your classpath. Which is typically your …/WEB-INF/lib directory.

3

Spring DB is awesome. I guess my favorite feature would be Annotation based to do Transaction propogation. It also handles JTA very well and many other features like “pooling” mechanism that’s not tied to the web server.

4

#2 there is no exception. after pressing the delete button at delete.jsp nothing is displayed in the web browser.

5

sorry placed wrong delete.java code. it seems i can not edit the post after 30 min from the time of post
here is the right one deletedemo.java


import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.sql.*;
import java.sql.PreparedStatement;

public class deletedemo extends HttpServlet
{
	public void doPost(HttpServletRequest req,HttpServletResponse res) 
	{
		Connection con=null;
		PreparedStatement ps=null;
		int rs=0;
		try
		{
			PrintWriter pw=res.getWriter();
			res.setContentType("text/html");
			pw.println("<html><form target='display'>");
			int sid=Integer.parseInt(req.getParameter("stu_id"));
			Class.forName("oracle.jdbc.driver.OracleDriver");
			con=DriverManager.getConnection("jdbc:odbc:thin:@localhost:8080:ravi","system","tiger");
			ps=con.prepareStatement("delete from student_info where stu_id=?");
			ps.setInt(1,sid);
			rs=ps.executeUpdate();
			if(rs!=1)
				pw.println("<h2>student id is proublem</h2>");
			else
				pw.println("<h2 style='position:absolute;left:50;top:50'> one record deleted </h2>");
			ps.close();
			con.close();
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	}
	public void doGet(HttpServletRequest req,HttpServletResponse res)
	{
		try
		{
			doPost(req,res);
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}
	}
}
6

#1 You really should not create jdbc connection on each HTTP requests. You can use the init() method or use constructor to set the jdbc connection. You can reuse the same connection. Remember that all HTTP requests share the same Servlets.

#2 copy/paste the Exception otherwise we have no clue.

Just FYI, using straight up jdbc is kind of like using Windows 95. If you want to use Windows 7 version of accessing the db. Try this.

http://static.springsource.org/spring/docs/3.0.x/spring-framework-reference/html/jdbc.html#jdbc-JdbcTemplate

This might be slightly easier though it’s slightly older version of doing this
http://www.vogella.de/articles/SpringJDBC/article.html

7

You’re doing an e.printStackTrace in your code, so if there is an exception (there probably is), you’ll need to check the servers stderr stream.

Otherwise I agree with sg, accessing a database this way is not recommended. I don’t know much about Spring JDBC but if you’re using Tomcat, (most other servers support this as well), you can use built in connection pooling to manage your database connections.

8

@rozner thank you for your help. i have checked stdrr stream log file and here it says class not found exception plz help


java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver
	at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1387)
	at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1233)
	at java.lang.ClassLoader.loadClassInternal(Unknown Source)
	at java.lang.Class.forName0(Native Method)
	at java.lang.Class.forName(Unknown Source)
	at deletedemo.doPost(deletedemo.java:19)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
	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.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
	at org.apache.coyote.http11.Http11AprProcessor.process(Http11AprProcessor.java:859)
	at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:574)
	at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:1527)
	at java.lang.Thread.run(Unknown Source)
java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver
	at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1387)
	at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1233)
	at java.lang.ClassLoader.loadClassInternal(Unknown Source)
	at java.lang.Class.forName0(Native Method)
	at java.lang.Class.forName(Unknown Source)
	at deletedemo.doPost(deletedemo.java:19)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
	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.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
	at org.apache.coyote.http11.Http11AprProcessor.process(Http11AprProcessor.java:859)
	at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:574)
	at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:1527)
	at java.lang.Thread.run(Unknown Source)
java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver
	at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1387)
	at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1233)
	at java.lang.ClassLoader.loadClassInternal(Unknown Source)
	at java.lang.Class.forName0(Native Method)
	at java.lang.Class.forName(Unknown Source)
	at deletedemo.doPost(deletedemo.java:19)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
	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.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
	at org.apache.coyote.http11.Http11AprProcessor.process(Http11AprProcessor.java:859)
	at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:574)
	at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:1527)
	at java.lang.Thread.run(Unknown Source)