-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathlistorder.jsp
73 lines (64 loc) · 2.45 KB
/
listorder.jsp
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
<%@ page import="java.sql.*,java.util.Locale" %>
<%@ page import="java.text.NumberFormat" %>
<%@ page contentType="text/html; charset=UTF-8" pageEncoding="UTF8"%>
<!DOCTYPE html>
<html>
<head>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
<link href="style.css" rel="stylesheet" type="text/css">
<title>Ray's Grocery Order List</title>
</head>
<body>
<h1>Order List</h1>
<%
String sql = "SELECT orderId, O.CustomerId, totalAmount, firstName+' '+lastName, orderDate FROM OrderSummary O, Customer C WHERE "
+ "O.customerId = C.customerId";
String url = "jdbc:sqlserver://db:1433;DatabaseName=tempdb;";
String uid = "SA";
String pw = "YourStrong@Passw0rd";
NumberFormat currFormat = NumberFormat.getCurrencyInstance(Locale.US);
try
{ // Load driver class
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
}
catch (java.lang.ClassNotFoundException e)
{
out.println("ClassNotFoundException: " +e);
}
try ( Connection con = DriverManager.getConnection(url, uid, pw);
Statement stmt = con.createStatement();)
{
ResultSet rst = stmt.executeQuery(sql);
out.print("<table border=\"1\"><tr><th>Order Id</th><th>Order Date</th><th>Customer Id</th><th>Customer Name</th><th>Total Amount</th></tr>");
// Use a PreparedStatement as will execute many times
sql = "SELECT productId, quantity, price FROM OrderProduct WHERE orderId=?";
PreparedStatement pstmt = con.prepareStatement(sql);
while (rst.next())
{ int orderId = rst.getInt(1);
out.print("<tr><td>"+orderId+"</td>");
out.print("<td>"+rst.getString(5)+"</td>");
out.print("<td>"+rst.getInt(2)+"</td>");
out.print("<td>"+rst.getString(4)+"</td>");
out.print("<td>"+currFormat.format(rst.getDouble(3))+"</td>");
out.println("</tr>");
// Retrieve all the items for an order
pstmt.setInt(1, orderId);
ResultSet rst2 = pstmt.executeQuery();
out.println("<tr align=\"right\"><td colspan=\"4\"><table border=\"1\">");
out.println("<th>Product Id</th> <th>Quantity</th> <th>Price</th></tr>");
while (rst2.next())
{
out.print("<tr><td>"+rst2.getInt(1)+"</td>");
out.print("<td>"+rst2.getInt(2)+"</td>");
out.println("<td>"+currFormat.format(rst2.getDouble(3))+"</td></tr>");
}
out.println("</table></td></tr>");
}
out.println("</table>");
}
catch (SQLException ex)
{ out.println(ex);
}
%>
</body>
</html>