Created in eclipse(Java EE).
ui.html
<!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>Querry Page</title> </head> <body> <form action="process.jsp" method="POST"> <input type="submit" name=querry value="q1"/> <input type="submit" name=querry value="q2"> <input type="submit" name=querry value="q3"/> <input type="submit" name=querry value="q4"/> <input type="submit" name=querry value="q5"/> <input type="submit" name=querry value="q6"/> </form> <p> q1=List rep and numbers<br> q2=Cust with credit or order greater than 50000<br> q3=Products with no orders<br> q4=Order over 25000<br> q5=Credit limit less sales rep's quota<br> q6=avg sale for each customer<br> <p> </body> </html>
process.jsp
<%@ page import ="java.sql.*" %> <%@ page import ="javax.sql.*" %> <% String querryNo=request.getParameter("querry"); //out.println(userid); //out.println(pwd); try { //Register Driver Class.forName("com.mysql.jdbc.Driver"); // create connection Connection con = DriverManager.getConnection("jdbc:mysql://192.168.4.91:3306/3101db","3101","3101"); //ip,username,pass Statement st= con.createStatement(); if(querryNo.equals("q1")){ ResultSet rs=st.executeQuery("select repid,name from salesresps"); %> <table border="1"> <tr> <th>Repid</th> <th>Name</th> </tr> <%while (rs.next()){%> <tr><td><%out.println(rs.getInt(1)); %></td> <td><%out.println(rs.getString(2)); %></td> </tr> <% }%> </table> <% } else if(querryNo.equals("q2")){ ResultSet rs=st.executeQuery("select custid,company,credit_limit from customers where credit_limit>50000 or custid in(select custid from orders where amt>50000)"); %><table border="1"><tr> <th>Custid</th> <th>Company Name</th> <th>Credit Limit</th> </tr> <%while(rs.next()){%> <tr> <td><%out.println(rs.getInt(1)); %></td> <td><%out.println(rs.getString(2));%></td> <td><%out.println(rs.getInt(3));%></td> </tr> <%}%> </table> <% } else if(querryNo.equals("q3")){ %><% ResultSet rs=st.executeQuery("select productname as product_with_no_orders from orders where custid is null"); %> <table border="1"><tr> <th>Product with no orders</th> </tr> <%while(rs.next()){%> <tr><td><%out.println(rs.getString(1)); %></td></tr> <%}%> </table> <% } else if(querryNo.equals("q4")){ ResultSet rs=st.executeQuery("select orders.orderno, orders.amt,orders.productname,customers.company as customername,salesresps.name as sellername from orders,customers,salesresps where orders.amt>2500 and orders.custid=customers.custid and customers.repid = salesresps.repid"); %> <table border="1"> <tr> <th>order_no</th> <th>order_amt</th> <th>product_name</th> <th>customer_name</th> <th>Seller_name</th> </tr> <%while(rs.next()){%> <tr> <td><% out.println(rs.getInt(1));%></td> <td><% out.println(rs.getInt(2));%></td> <td><% out.println(rs.getString(3));%></td> <td><% out.println(rs.getString(4));%></td> <td><% out.println(rs.getString(5));%></td> </tr> <%}%> </table> <%} else if(querryNo.equals("q5")){ ResultSet rs=st.executeQuery("select customers.company as customername,credit_limit,salesresps.name as sellername,salesresps.quota from customers,salesresps where customers.credit_limit> salesresps.quota"); %> <table border="1"> <tr> <th>Customer Name</th> <th>Credit Limit</th> <th>Seller Name</th> <th>Quota</th> </tr> <%while(rs.next()){%> <tr> <td><% out.println(rs.getString(1));%></td> <td><% out.println(rs.getInt(2));%></td> <td><% out.println(rs.getString(3));%></td> <td><% out.println(rs.getInt(4));%></td> </tr> <%}%> </table> <% } else if(querryNo.equals("q6")){ ResultSet rs=st.executeQuery("select customers.custid,avg(salesresps.sales) from customers inner join salesresps on customers.repid=salesresps.repid group by customers.company"); %> <table border="1"> <tr> <th>Cust_id</th> <th>Average</th> </tr> <%while (rs.next()){%> <tr><td><%out.println(rs.getInt(1)); %></td> <td><%out.println(rs.getInt(2)); %></td> </tr> <% }%> </table> <% } } catch(Exception e) { e.printStackTrace(); } %>
Screenshots
No comments:
Post a Comment