TYBcs Java Slip 3-2 | IProgramX

Q. Consider the following entities and their relationships
BillMaster(billno, custname, billdate)
BillDetails(itemname, qty, rate)
BillMaster and BillDetails are related with one-to-many relationship. Create a ______ 3NF using Postgresql for the above and solve the following Design HTML page that accept the bill number from user and print the corresponding bill in the following format using servlet programming.

Sql Table:

create table BillMaster(billno serial primary key, custname text, billdate date);
create table BillDetails(itemname text, qty int, rate float, billno int references BillMaster(billno));

insert into BillMaster(custname,billdate) values('Ram Gore','2015-01-01'),('Seeta Kale','2015-03-01');

insert into BillDetails values('Pen',2,30,1),('Pencil',10,10,1)('Eraser',5,5,1),('Santoor Soap',3,45,2),('Kissan Jam',2,110,2),('Pravin Mango Pickel',4,45,2);

Html:

<form method='post' action='http://localhost:8080/ty/bill'>
<b>Bill No:</b><input type='text' name='bno'><br>
<input type='submit'><input type='reset'>
</form>

XML: web.html

    <servlet>
        <servlet-name>Bill</servlet-name>
        <servlet-class>BillServlet</servlet-class>
    </servlet>

    <servlet-mapping>
        <servlet-name>Bill</servlet-name>
        <url-pattern>/bill</url-pattern>
    </servlet-mapping>

Program: BillServlet.java

import java.sql.*;
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class BillServlet extends HttpServlet{
 private Connection con;
   private PreparedStatement ps;
   private ResultSet rs;

 public void init() 
   throws ServletException{
      try{
         Class.forName("org.postgresql.Driver");
         con = DriverManager.getConnection("jdbc:postgresql:bill",
       "postgres","");
      }
      catch(Exception e){}
   }

   public void doPost(HttpServletRequest request, HttpServletResponse response)
   throws IOException,ServletException{
      response.setContentType("text/html");

      PrintWriter out = response.getWriter();

      int bno = Integer.parseInt(request.getParameter("bno"));
 
  try{
   ps = con.prepareStatement("select * from BillMaster where billno=?");
   ps.setInt(1,bno);
   
   rs = ps.executeQuery();

   rs.next();

   out.print("<table border=1>"+
   "<tr><td><b>Bill No:</b></td><td>"+rs.getInt(1)+"</td>"+ 
   "<td><b>Bill Date:</b></td><td>"+rs.getString(3)+"</td></tr>"+
   "<tr><td><b>Customer Name:</b></td><td>"+rs.getString(2)+"</td></tr>");
   out.print("<tr><th>Sr. No.</th><th>Item Name</th><th>Qty</th><th>Rate</th><th>Total</th></tr>");

   ps = con.prepareStatement("select * from BillDetails where billno=?");
   ps.setInt(1,bno);

   rs = ps.executeQuery();

   int i=1;
   float tot=0;

   while(rs.next()){
    float amt = rs.getInt(2)*rs.getFloat(3);

    out.print("<tr><td>"+i+"</td><td>"+
     rs.getString(1)+"</td><td>"+
     rs.getInt(2)+"</td><td>"+
     rs.getFloat(3)+"</td><td>"+
     amt+"</td></tr>");

    tot+=amt;
    i++;
   }
   out.print("<tr><td><b>Net Bill:</b></td><td>"+tot+"</td></tr>");
   out.print("</table>");  
  }catch(Exception e){}
 }
}