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

Question

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"
        import="model.User"%>

<%!  public void deleteUser(int id) {

        Connection c1 = null;
        PreparedStatement ps = null;
        try {
            Class.forName("org.postgresql.Driver");
            c1 = DriverManager
                    .getConnection("jdbc:postgresql://localhost:5432/postgres",
                            "postgres", "admin");
            c1.setAutoCommit(false);
            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();

            rs1.close();
            ps.close();
            c1.close();

            JOptionPane.showInputDialog("Player Deleted");

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

<!DOCTYPE html>


<head>
    <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'"> 
</head>
<body>
    <h1>Registered users</h1>
    <p>
        <%
            Connection c = null;
            Statement stmt = null;
            try {
                Class.forName("org.postgresql.Driver");
                c = DriverManager
                        .getConnection("jdbc:postgresql://localhost:5432/firstapp",
                                "postgres", "admin");

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

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

                while (rs.next()) {
                    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);%>>
    <p/>
    <%
            }
            rs.close();
            stmt.close();
            c.close();

        } catch (Exception e) {
            System.err.println(e.getClass().getName() + ": " + e.getMessage());
            System.exit(0);
        }%>
</p>
</body>

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!

Unrelated, but: Never put SQL directly into a JSP. And absolutely, never, ever put a call to a Swing class into JSP — , May 17 '16 at 16:07

score 2 · Answer 1 · answered May 17 '16 at 16:33

ps.executeQuery();

should be

ps.executeUpdate();

And remove

JOptionPane.showInputDialog("Player Deleted");

You could there fill a String variable to be shown in HTML. (JOptionPane is swing, the GUI for a desktop application.)

Also <%-- probably is an editing left-over - a JSP comment.

score 2 · Accepted Answer · edited May 23 '17 at 12:16

There are few issues in your code:

The connection string in deleteUser is different than in the main section of the JSP. That is why the Users table cannot be found.
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.
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.
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).
As Joop mentioned, use ps.executeUpdate(); for your delete.
As joop mentioned too, remove the <!-- at the top of your code
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.

Yes, I'm currently learning so please bear with my poor code. I'd like to redesign the application similarly to what you suggested, using JSP for viewing data and servlet to do the sql operations. Maybe you can suggest a good reference to learn about how it's done for a newbie? Thanks — NoobNe0, May 17 '16 at 17:58
@NoobNe0 Don't worry, we all need to learn at some point. I will add some links in the answer. — C.Champagne, May 17 '16 at 21:42

score 0 · Answer 3 · answered May 17 '16 at 16:30

0

You've set auto commit to false.

c.setAutoCommit(false);

So I think you need to commit the transaction after execute the delete statement.

c.commit();

answered May 17 '16 at 16:30

Marcel Dias

4,271
1
14
10

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

3 Answers3