Delete mysql multiple records by checkbox and submit button

Question

I can't manage to work this part of my app. I have to delete some records (correctly loaded from DB) in mysql database from a jsp page, checking checkboxes and clicking submit button. Even if data is correctly displayed, nothing is been deleted from DB Here's the code:

Here's the class

/* ArticoliManager.java */
public class ArticoliManager {

public void cancellaArticolo(String chboxArticoliDaCancellare[]) throws SQLException{
Connection con = DBConnectionPool.getConnection();
PreparedStatement ps = null;
try {
    for(String deleteThem:chboxArticoliDaCancellare){
    String query = "DELETE * FROM articoli WHERE id='"+deleteThem+"'";
    ps = con.prepareStatement(query);
    ps.executeUpdate();
    con.commit();
}
}
finally {
    if (ps != null) {
        try {
            ps.close();
        }
        catch (SQLException ignored) {
        }
    }
    try {
        con.close();
    }
    catch (SQLException ignored) {
    }
}

}
}

Here's the servlet

/* CancellaArticolo.java
*/
public class CancellaArticoloServlet extends HttpServlet {

protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException, SQLException {
    response.setContentType("text/html;charset=UTF-8");
    PrintWriter out = response.getWriter();
    HttpSession session = request.getSession();
    int idArticoloDaCancellare = 0;
    try {
        ArticoliManager am = new ArticoliManager();
        String chboxArticoliDaCancellare[] = request.getParameterValues("chbox");
        am.cancellaArticolo(chboxArticoliDaCancellare);
        request.getRequestDispatcher("gestione_admin.jsp").forward(request, response);
    } finally {            
        out.close();
    }
}

@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    try {
        processRequest(request, response);
    } catch (SQLException ex) {
        Logger.getLogger(CancellaArticoloServlet.class.getName()).log(Level.SEVERE, null, ex);
    }
}

/** 
 * Handles the HTTP <code>POST</code> method.
 * @param request servlet request
 * @param response servlet response
 * @throws ServletException if a servlet-specific error occurs
 * @throws IOException if an I/O error occurs
 */
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    try {
        processRequest(request, response);
    } catch (SQLException ex) {
        Logger.getLogger(CancellaArticoloServlet.class.getName()).log(Level.SEVERE, null, ex);
    }
}

/** 
 * Returns a short description of the servlet.
 * @return a String containing servlet description
 */
@Override
public String getServletInfo() {
    return "Short description";
}// </editor-fold>

Here's part of the jsp page

/* gestione_admin.jsp */
<%    
                            for (int i=0; i<al.size(); i++){
                            out.println("<table>");
                            out.println("<tr>");
                            out.println("<td>");
                            %>
                            <form action="CancellaArticolo">
                            <input type="checkbox" name="chbox" value="<%=+al.get(i).getId()%>"/>
                            <%
                            out.println("<b>Autore: </b>"+al.get(i).getAutore()+"                    <b>Articolo: </b>"+al.get(i).getTitolo()+"</td>");
                            out.println("</tr>");
                            out.println("</table>");
                            %>
                            </form>
                            <%
                            }
                            %>
                            <input type="submit" value="Cancella Articoli Selezionati"></input>
                            </form>

it seems almost allright... what's the problem?

Answer 1

The checkbox value must be the ID of the item. Something like this:

<input type="checkbox" name="chbox" value="<%=al.get(i).getId()%>"/>

You should already have discovered this when you bothered to debug the chboxArticoliDaCancellare values. As you had it, they are all "chkbox".

You also need to ensure that the input elements are all inside the same <form> as the submit button which is supposed to send the desired data along. So, basically:

<form action="yourServletURL" method="post">
    ...
    <input type="checkbox" ... />
    ...
    <input type="checkbox" ... />
    ...
    <input type="checkbox" ... />
    ...
    <input type="submit" ... />
    ...
</form>

---

Unrelated to the concrete problem, you're not using PreparedStatement correctly. You still have a SQL injection hole there because you concatenated the user-controlled request parameter value inside the SQL string instead of using a placeholder ? with a PreparedStatement#setXxx() call. Also, consider looking at JSTL/EL, it will make your presentation code cleaner.