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> </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>