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>