Updating the inventory and printing an invoice
 
import java.io.*;
import java.sql.*;
import javax.servlet.*;
import java.util.*;
import java.text.*;
import javax.servlet.http.*;
public class PlaceOrder extends HttpServlet {
String order_names[]={"Widget","Wodget","Shovel","Rake"};
int order_numbers[]=new int[4];
String order_results[]=new String[4];
Statement stmt = null;
 
 public void init() throws ServletException {
 Random r=new Random();
 //generate random order quantities
 for(int i=0;i<4;i++)order_numbers[i]=r.nextInt(100);
 }
 
public void doGet(HttpServletRequest req, HttpServletResponse res)
 throws ServletException, IOException
 {doPost(req,res);}
 
 public void doPost(HttpServletRequest req, HttpServletResponse res)
 throws ServletException, IOException {
 Connection con = null;
 ResultSet rs;
 res.setContentType("text/plain");
 PrintWriter out = res.getWriter();
 for(int j=0;j<4;j++)out.println("ordering..."+order_names[j]+"..in quantity="+order_numbers[j]);
 
 
 try {
 
 Class.forName("org.gjt.mm.mysql.Driver");
  // Get a Connection to the database
 con = DriverManager.getConnection( "jdbc:mysql://localhost/business", "root", "");
  out.println("got connection");
  con.setAutoCommit(false);
  for(int k=0;k<4;k++)
 
  {
  
  PreparedStatement pstmt = con.prepareStatement("SELECT quantity,price,product_id FROM products where name=?");
 
 pstmt.setString(1,order_names[k]);
  rs = pstmt.executeQuery();
  
  rs.first();//first row of resultset
 int pi=Integer.parseInt(rs.getString("product_id"));//for use later product id
  double price=Double.parseDouble(rs.getString("price"));
 
  int q=rs.getInt("quantity");
  out.println("completed query available="+q+"at price ="+price);//trace line
  price*=order_numbers[k];//billing info
  q-=order_numbers[k];//new quantity??
  if(q<0)
 order_results[k]="cant have any of" + order_names[k]+"...not enough";
  else{//there is enough
 order_results[k]="Sufficient quant in stock..."+order_names[k]+"has been ordered. Quantity is" + order_numbers[k]+" Cost is "+price;
 
  PreparedStatement pstmt2 = con.prepareStatement("update products set quantity =? where product_id=?");
  pstmt2.setInt(2,pi);
  pstmt2.setInt(1,q);
  pstmt2.executeUpdate();
  }//else
  out.println(order_results[k]);//this could be invoice line
  }//for
  con.commit();
 }
 catch (Exception e) {
 // Any error is grounds for rollback
 try {con.rollback(); }
 catch (Exception ignored) { }
  out.println("Order failed. Please contact technical support.");
  } 
 finally {
 try { 
 if (con != null) con.close(); }
  catch (SQLException ignored) { } 
 } //finally
 }//method
 
 
 
 
 
}//class
 
Here is my database/table showing original quantities
 

 
 
Here is servlet output
ordering...Widget..in quantity=59
ordering...Wodget..in quantity=21
ordering...Shovel..in quantity=94
ordering...Rake..in quantity=22
got connection
completed query available=34at price =67.9
cant have any ofWidget...not enough
completed query available=60at price =5.5
Sufficient quant in stock...Wodgethas been ordered. Quantity is 21 Cost is 115.5
completed query available=112at price =20.5
Sufficient quant in stock...Shovelhas been ordered. Quantity is 94 Cost is 1927.0
completed query available=22at price =22.2
Sufficient quant in stock...Rakehas been ordered. Quantity is 22 Cost is 488.4

Inventory after ordering