NoobNe0 NoobNe0 - 1 year ago 75
Java Question

Delete in JSP does not work (table does not exist)

I've a simple JSP which does a select on a table of my simple database and shows the results:


<%@page import="javax.swing.JOptionPane"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.Statement"%>
<%@page import="java.sql.Connection"%>
<%@page contentType="text/html" pageEncoding="UTF-8"

<%! public void deleteUser(int id) {

Connection c1 = null;
PreparedStatement ps = null;
try {
c1 = DriverManager
"postgres", "admin");
System.out.println("Opened database successfully" + "\n");

System.out.println("PROVA PROVA PROVA" + "\n");

ps = c1.prepareStatement("DELETE FROM users WHERE user_id = ? ");
ps.setInt(1, id);
ResultSet rs1 = ps.executeQuery();


JOptionPane.showInputDialog("Player Deleted");

} catch (Exception e) {
System.err.println(e.getClass().getName() + ": " + e.getMessage());

<!DOCTYPE html>

<link rel="stylesheet" type="text/css" href="layout.css">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Registered users</title>
<input Type="BUTTON" Value="Home Page" onclick="location.href = 'index.html'">
<h1>Registered users</h1>
Connection c = null;
Statement stmt = null;
try {
c = DriverManager
"postgres", "admin");

System.out.println("Opened database successfully" + "\n");

stmt = c.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM users;");

while ( {
int id = rs.getInt("user_id");
String firstname = rs.getString("firstname");
String lastname = rs.getString("lastname");
String country = rs.getString("country");

User u = (User) request.getAttribute("user"); %>

First Name: <% out.print(firstname); %> <br/>
Last Name: <% out.print(lastname); %> <br/>
Country: <% out.print(country); %> <br/>

<input Type="BUTTON" Value="Delete" onclick=<%deleteUser(id);%>>

} catch (Exception e) {
System.err.println(e.getClass().getName() + ": " + e.getMessage());

Each record has a "delete" button underneath which should allow to delete the corresponding record.

However, when the method deleteUser is called, the prepared statement fails and I get the following message in the console: table "users" does not ecists.

Why is that? It's because I call the deleteUser method and open another connection to the DB while another connection to the same DB is already opened (hence it fails to find the table)?

How can I fix this?

Also I'd like to return to my simple web application main menu (index.html) after the delete is done. Can you help me also with this?

Many thanks!

Answer Source

There are few issues in your code:

  1. The connection string in deleteUser is different than in the main section of the JSP. That is why the Users table cannot be found.
  2. Javacode is executed on the server; before the browsers receives the page. By consequence, you cannot call Java code on a onClick as you intended. In your code you would actually be deleting records as soon as you'd display it.
  3. As others mentioned JOptionPane.showInputDialog("Player Deleted"); will not work because it is a call to a Swing object, which is used in desktop GUI.
  4. Don't put SQL in your JSP (or Java scriplets either). (Note that I understand you are currently learning and you need to progress step by step, so this is acceptable).
  5. As Joop mentioned, use ps.executeUpdate(); for your delete.
  6. As joop mentioned too, remove the <!-- at the top of your code
  7. Remove also the <input> in your <head> section. It has nothing to do there.

The solution that is the closest to your code would be to check if there is an id parameter in your request and delete the corresponding record, if present. The onClick would just redirect to the same page with the id parameter correctly set.

A better approach would consist of putting all your DB code in a servlet and to make AJAX calls to delete the record and even get your data. You can find tutorials about simple ajax calls or about displaying records with ajax calls. There is also a nice answer on SO. Note they all use JQuery to make the Ajax calls.

When you feel comfortable with that, you can go a step further and use JAX-RS instead of a simple serlvet.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download