hOW TO edit and delete records in database in jsp and servlet ???

alanborder 0 Newbie Poster

14 Years Ago

package edu.edusys.demo.jdbc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import edu.edusys.demo.jdbc.bean.Customer;

public class CustomerDAO {

    private Connection connection;

    public CustomerDAO(Connection connection) {
        super();
        this.connection = connection;
    }

    public boolean addCustomer(Customer customer) throws Exception {

        String sql =  "INSERT INTO Customer" + "(name,address,phone)" + "VALUES (?,?,?)";

        PreparedStatement ps = connection.prepareStatement(sql);

        ps.setString(1, customer.getName());

        ps.setString(2, customer.getAddress());

        ps.setString(3, customer.getPhone());


        int n = ps.executeUpdate();
        ps.close();

        return n > 0;

//Manage Customer servlet
        }
    public String  deleteCustomer(String id) throws Exception {

        String sql =  "DELETE FROM Customer WHERE customer_id = ?";

        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1,id);
        System.out.println(ps.toString());
        ResultSet rs = ps.executeQuery();

    int n = ps.executeUpdate();


    ps.close();
    return sql;

    }
    public ArrayList getAllCustomers() throws Exception {

        ArrayList customersList = new ArrayList();
        String sql = "SELECT * FROM Customer " + "ORDER by name";
        Statement s = connection.createStatement();
        ResultSet rs = s.executeQuery(sql);

    while (rs.next()) {

        Customer customer = new Customer();
        customer.setCustomerId(rs.getInt("customer_id"));
        customer.setName(rs.getString("name"));
        customer.setAddress(rs.getString("address"));
        customer.setPhone(rs.getString("phone"));

        customersList.add(customer);

        }


    rs.close();
    s.close();

        return customersList;

    }

}




**Servlet**


    package edu.edusys.demo.jdbc.web;

import java.io.IOException;
import java.sql.Connection;
import java.util.ArrayList;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import edu.edusys.demo.jdbc.bean.Customer;
import edu.edusys.demo.jdbc.dao.CustomerDAO;
import edu.edusys.demo.jdbc.util.DBConnectionManager;

/**
 * Servlet implementation class AddCustomerServlet
 */
public class ManageCustomerServlet extends HttpServlet {
    private Connection connection = null;
    private boolean isSuccessful = true;

    protected void service(HttpServletRequest request,
        HttpServletResponse response) throws ServletException, IOException {

      try{
        performTask(request, response);

    } catch (Exception e) {
        e.printStackTrace();
        isSuccessful = false;

    } finally {

        try {
             if (connection != null) {
            if (isSuccessful)
                connection.commit();
            else
                connection.rollback();

            connection.close();

             }
        } catch (Exception e2) {
            e2.printStackTrace();

        }

        getServletContext().getRequestDispatcher("/ManageCustomers.jsp").forward(request, response);

        }

      }
    private void performTask(HttpServletRequest request, HttpServletResponse response) throws Exception {

        String name = request.getParameter("name");
        String address = request.getParameter("address");
        String phone = request.getParameter("phone");

        Customer customer = new Customer();
        customer.setName(name);
        customer.setAddress(address);
        customer.setPhone(phone);


        connection = DBConnectionManager.openConnection();

        CustomerDAO dao = new CustomerDAO(connection);
        isSuccessful = dao.addCustomer(customer);
        request.setAttribute("status", isSuccessful);



    //end




        CustomerDAO dao1 = new CustomerDAO(connection);
        ArrayList customersList = dao1.getAllCustomers();
        request.setAttribute("customersList", customersList);






    }





    }

** JSP file**

<%@ page import="java.util.*" %>
<%@ page import="java.lang.Object" %> 
<%@ page import="java.util.*" %>
<%@page import="edu.edusys.demo.jdbc.bean.Customer"%>

<html>
<head>


    <%
    ArrayList customersList = (ArrayList) request.getAttribute("customersList");
    Iterator iterator = customersList.iterator();
%>


<title>Insert title here</title>
</head>
<body>
    <h2 align="center">Add Customer</h2>
    <table align="center" width="50%">
        <tr>
            <td align = "center">
                <form action = "<%= request.getContextPath() %>/manageCustomers" method = "post">
                    <table>
                        <tr>
                            <td> Name: </td>
                            <td><input type = "text" name = "name"></td>
                        </tr>


                        <tr>
                            <td> Address: </td>
                            <td><input type = "text" name = "address"></td>
                        </tr>


                        <tr>
                            <td> Phone: </td>
                            <td><input type = "text" name = "phone"></td>
                        </tr>

                        <tr>
                            <td colspan = "2" align="center">
                                <input type = "submit" value = "save">
                            </td>
                        </tr>
                    </table>
                </form>
            </td>
        </tr>                
    </table>



    <br><br>





    <h2 align="center">Customers List</h2>

    <table align="center" width="80%">
        <tr>
            <td>
                <table width ="100%" border="1" style="border-collapse: collapse;">
                    <% if (customersList.size() == 0) { %>
                    <tr>
                        <td align="center">No Customer Found</td>
                    </tr>
                    <% } else { %>
                    <tr>
                        <th>ID</th>
                        <th>Name</th>
                        <th>Address</th>
                        <th>Phone</th>    
                        <th>&nbsp;</th>   
                    </tr>    
                    <% while (iterator.hasNext()) {
                        Customer customer = (Customer) iterator.next();
                    %>

                    <tr>
                        <td><%= customer.getCustomerId() %></td>
                        <td><%= customer.getName() %></td>
                        <td><%= customer.getAddress() %></td>
                        <td><%= customer.getPhone() %></td>    
                        <td>
                        <form action = "<%= request.getContextPath() %>/manageCustomers" method = "post">





                        </td>
                    </tr>
                    <% }
                }%>
                </table>
            </td>        
        </tr>
    </table>












    </body>
</html>



    <script type = "text/javascript">
    <% if (request.getAttribute("status")!=null) {

         boolean status = (Boolean) request.getAttribute("status");
         if (status) {
    %>
        alert("Customer Added Successfully");
    <% } else { %>
        alert("Customer Couldn't Be Added");
    <%}} %>
</script>

java:jsp

Edited 14 Years Ago by alanborder because: w

1 Contributor
0 Replies
222 Views

Dani AI

Generated 7 Months Ago

A concise, practical follow‑up to ’s post: the main problems are an incorrect delete flow, resource leaks, and mixing display/mutation responsibilities in one request. The delete method currently runs the wrong JDBC call and even returns the SQL text instead of a success flag; the servlet is using a catch‑all service flow which makes transaction/redirect logic fragile; the JSP has no concrete edit/delete controls and uses scriptlets (harder to maintain). Suggested fixes below are focused, minimal, and safe.

Use an action parameter from the list view (post for mutations, get for showing forms). Example row controls (JSP using EL/JSTL rather than scriptlets):

Handle actions in the servlet with clear separation: doGet loads and shows the list or the edit form; doPost performs add/update/delete and then redirects (Post/Redirect/Get) to the list. Core pattern (pseudocode):

try (Connection c = DBConnectionManager.openConnection()) {
c.setAutoCommit(false);
if ("delete".equals(action)) dao.deleteById(id);
else if ("update".equals(action)) dao.update(customer);
else if ("add".equals(action)) dao.add(customer);
c.commit();
}
response.sendRedirect(req.getContextPath() + "/manageCustomers"); // PRG

DAO notes and troubleshooting: make delete/update return a boolean or affected‑rows int and call executeUpdate() (not executeQuery()). Close statements/result sets with try‑with‑resources. Validate incoming id types to avoid SQL errors. If the list is sometimes null in the JSP, guard iteration with a null/empty check or switch to JSTL <c:forEach>. For longer term maintainability, replace scriptlets with EL/JSTL and consider a lightweight MVC framework (Spring MVC or similar) when the app grows.

Be the first to reply

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.