Larder Management

JSP

This application, LarderInventory, is intended to help with shopping. Just about everybody goes to the grocery store. Most people probably write up a list first, but, by and large, grocery shopping is still a bit haphazard. This application may or may not help.

First of all, we need a place to keep our records, so we will create a table to hold related data on a database. Here is some example SQL to accomplish that. -- DROP TABLE larder ; CREATE TABLE larder ( 	id            INTEGER, -- unique id 	description   VARCHAR(255), -- descriptive label for item, or product name 	plu           VARCHAR(255), -- price look-up code 	upc           VARCHAR(255), -- universal product code 	qoh           INTEGER, -- quantity on hand 	rop           INTEGER, -- re-order point 	pq            INTEGER, -- (default) purchase quantity 	pup           NUMERIC(11,2), -- purchase unit price 	pb            VARCHAR(255), -- preferred brand(s) 	psl           VARCHAR(255) -- preferred grocery store (with location) ) ; Here is a screen shot of the main page. It's got the basic functionality (add, change and delete record) as well as the option to focus only on shopping.



Here is the code. &lt;%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.text.*" import="java.io.*" import="java.sql.*" import="java.util.*" errorPage="" %> &lt;%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions" %> &lt;% /* * LarderInventory.jsp * * Copyright (c) 2013 Alfred J Wheeler * All rights reserved. * * This software is the proprietary * information of Alfred J Wheeler * */ /**  * The purpose of this program is to provide larder inventory * management to help with grocery shopping. * * USAGE *  LarderInventory.jsp * * @author      10/12/2013 created by Alfred J Wheeler. * * @version     0.1 * * @see         LarderItemAdd, LarderShoppingList, LarderItemEdit, LarderItemRemove * */ /*  * Database : demonstration, Table : larder *  * field name                        data type * ---  ---  * id                                integer * description                      character varying(255) * plu                              character varying(255) * upc                              character varying(255) * qoh                              integer * rop                              integer * pq                               integer * pup                              numeric * pb                               character varying(255) * psl                              character varying(255) */ /* * Initialize some variables */ String message = ""; long dt = 0; String title = null; Connection conn = null; DatabaseMetaData dbmd = null; Statement st = null; ResultSet rs = null; ResultSetMetaData rsmd = null; String sql = ""; Integer columns = 0; /* * Get a date time stamp. Set a page title, if needed... */ dt = System.currentTimeMillis; title = "Larder Inventory ( Groceries )"; /* * Get the database connection values, etc... from the properties file * ( This eventually will be externalized to reduce code clutter... ) */ String p = application.getRealPath("/") + "properties/Demo.properties"; InputStream propsFile; Properties appProp = new Properties; try { propsFile = new FileInputStream(p); appProp.load(propsFile); propsFile.close; } catch (IOException ioe) { out.println("&lt;PRE>"); out.println("I/O Exception."); ioe.printStackTrace; out.println("&lt;/PRE>"); } String dbDriver = "" + appProp.getProperty("dbDriver"); String dbUrl   = "" + appProp.getProperty("dbUrl"); String dbUid   = "" + appProp.getProperty("dbUid"); String dbUpw   = "" + appProp.getProperty("dbUpw"); String css     = "" + appProp.getProperty("css"); //out.println("&lt;PRE>"); //out.println(dbDriver); //out.println(dbUrl); //out.println(dbUid); //out.println(dbUpw); //out.println("&lt;/PRE>"); /* * Open the database */ try { Class.forName(dbDriver); conn = DriverManager.getConnection(dbUrl, dbUid, dbUpw); st = conn.createStatement; %> &lt;!DOCTYPE html> &lt;HTML> &lt;HEAD> &lt;link rel="stylesheet" type="text/css" href="&lt;%=css%>"> &lt;TITLE>&lt;%=title%>&lt;/TITLE> &lt;SCRIPT LANGUAGE="JavaScript" SRC="angular.js">&lt;/SCRIPT> &lt;SCRIPT LANGUAGE="JavaScript" SRC="jquery.js">&lt;/SCRIPT> &lt;/HEAD> &lt;BODY> &lt;H1>&lt;%=title%>&lt;/H1> &lt;br> &lt;div class="centered"> &lt;TABLE class="ivory" border="0"> &lt;tr> &lt;td> &lt;nobr> &lt;form action="LarderItemAdd.jsp" name=f1> &lt;input name=btnG type=submit value=" Add New Grocery Item "> &lt;/form> &lt;/nobr> &lt;/td> &lt;td> &lt;nobr> &lt;form action="LarderShoppingList.jsp" name=f2> &lt;input name=btnG type=submit value=" Shopping List "> &lt;/form> &lt;/nobr> &lt;/td> &lt;/tr> &lt;/TABLE> &lt;/div> &lt;br> &lt;TABLE border="1" cellspacing="0"> &lt;TBODY> &lt;th style="padding:14px;text-align:left;" colspan="2">Items on hand&lt;/th> &lt;% sql = "SELECT " + "id, " + // internal item id 				"description, " + // description or product name "plu, " + // price look up 				"upc, " + // universal product code "qoh, " + // quantity on hand "rop, " + // re-order point "pq, " + // purchase quantity "pb, " + // preferred brand(s) "psl " + // preferred grocery store (with location) "FROM larder " + "ORDER BY UPPER( description )"; //		out.print("sql = " + sql + " \n"); rs = st.executeQuery(sql); rsmd = rs.getMetaData; columns = rsmd.getColumnCount; // if there are not columns, then there are not rows... if ( columns > 0 ) { while(rs.next) { out.println( "&lt;tr>" ); out.println( "&lt;td width=\"250\">" ); out.println( "&lt;table width=\"100%\" border=\"0\" cellpadding=\"0\" cellspacing=\"0\">" ); out.println( "&lt;tr>" ); out.println( "&lt;td width=\"80%\">" + rs.getString("description") + "&lt;/td>" ); out.println( "&lt;td align=\"right\">" + rs.getString("qoh") + "&lt;/td>" ); out.println( "&lt;/tr>" ); out.println( "&lt;/table>" ); out.println( "&lt;/td>" ); out.println( "&lt;td>" ); out.println( "&lt;table border=\"0\" cellpadding=\"0\" cellspacing=\"0\">" ); out.println( "&lt;tr>" ); out.println( "&lt;td>" ); out.println( "&lt;form action=\"LarderItemEdit.jsp\" name=\"edit\">" ); out.println( "&lt;input name=\"id\" type=\"hidden\" value=\"" + rs.getString("id") + "\">" ); out.println( "&lt;input name=\"btnG\" type=\"submit\" value=\"Edit Item\">" ); out.println( "&lt;/form>" ); out.println( "&lt;/td>" ); out.println( "&lt;td>" ); out.println( "&lt;form action=\"LarderItemRemove.jsp\" name=\"remove\">" ); out.println( "&lt;input name=\"id\" type=\"hidden\" value=\"" + rs.getString("id") + "\">" ); out.println( "&lt;input name=\"btnG\" type=\"submit\" value=\"Remove Item\">" ); out.println( "&lt;/form>" ); out.println( "&lt;/td>" ); out.println( "&lt;/tr>" ); out.println( "&lt;/table>" ); out.println( "&lt;/td>" ); out.println( "&lt;/tr>" ); } 		} %> &lt;/tbody>&lt;/table> &lt;% } // end try catch ( SQLException ex ) { out.println ( "&lt;pre>" ); out.println ( "---" ); out.println ( "--                      FATAL ERROR IN SQL" ); out.println ( "---" ); out.println ( "-- " ); while ( ex != null ) { out.println ("-- SQLState: " + ex.getSQLState ); out.println ("-- Message: " + ex.getMessage ); out.println ("-- Vendor:  " + ex.getErrorCode ); ex = ex.getNextException ; out.println ( "-- " ); } 		out.println ( "---" ); } // end catch catch ( java.lang.Exception ex ) { /* 		 * Got some other type of exception. Dump it. */ 		out.println ( "&lt;pre>" ); out.println ( "---" ); ex.printStackTrace ; out.println("exception: " + ex.getMessage ); } // end catch finally { // No action for now } // end finally %> &lt;br>&lt;br>&lt;br>&lt;br> &lt;/body> &lt;/html>

Here is a screen shot of the “Add New Grocery Item” (to the larder) page.



Here is the code. &lt;%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.io.*" import="java.sql.*" import="java.util.*" import="java.util.Properties" errorPage="" %> &lt;% /* * LarderItemAdd.jsp * * Copyright (c) 2013 Alfred J Wheeler * All rights reserved. * * This software is the proprietary * information of Alfred J Wheeler * */ /**  * The purpose of this application is to add a record to table Larder * * USAGE *  LarderItemAdd.jsp * * @author      10/12/2013 created by Alfred J Wheeler. * * @version     0.1 * * @see         LarderInventory, LarderShoppingList, LarderItemEdit, LarderItemRemove * */ /*  * Database : demonstration, Table : larder *  * field name                        data type * ---  ---  * id                                integer * description                      character varying(255) * plu                              character varying(255) * upc                              character varying(255) * qoh                              integer * rop                              integer * pq                               integer * pup                              numeric * pb                               character varying(255) * psl                              character varying(255) */ /* * Initialize some variables */ long dt = 0; String title = null; Connection conn = null; Statement st = null; ResultSet rs = null; String sql = ""; String posted = "false"; boolean populated = true; boolean duplicate = false; int records = 0; String message = ""; int errors = 0; //String tableName = null; String larderDescription = ""; String larderPLU = ""; String larderUPC = ""; String larderQOH = ""; String larderROP = ""; String larderPQ = ""; String larderPUP = ""; String larderPB = ""; String larderPSL = ""; /* * Get a date time stamp. Set a page title, if needed... */ dt = System.currentTimeMillis; title = "Larder - Add New Item"; /* * Get the database connection values, etc... from the properties file * ( This eventually will be externalized to reduce code clutter... ) */ String p = application.getRealPath("/") + "properties/Demo.properties"; InputStream propsFile; Properties appProp = new Properties; try { propsFile = new FileInputStream(p); appProp.load(propsFile); propsFile.close; } catch (IOException ioe) { out.println("&lt;PRE>"); out.println("I/O Exception."); ioe.printStackTrace; out.println("&lt;/PRE>"); } String dbDriver = "" + appProp.getProperty("dbDriver"); String dbUrl   = "" + appProp.getProperty("dbUrl"); String dbUid   = "" + appProp.getProperty("dbUid"); String dbUpw   = "" + appProp.getProperty("dbUpw"); String css     = "" + appProp.getProperty("css"); //out.println("&lt;PRE>"); //out.println(dbDriver); //out.println(dbUrl); //out.println(dbUid); //out.println(dbUpw); //out.println("&lt;/PRE>"); /* * Open the database */ try { Class.forName(dbDriver); conn = DriverManager.getConnection(dbUrl, dbUid, dbUpw); st = conn.createStatement; /* 	 * Get the request values */ 	if ( request.getParameter( "posted" ) == null ) { posted = "false"; } 	else { posted = request.getParameter( "posted" ); } 	if ( posted.equals("true") ) { /* 		 * Are the fields populated? */ 		larderDescription = request.getParameter( "larder_description" ); populated = true; if ( (request.getParameter( "larder_description" )).equals( "" ) ) { populated = false; message = "Description is required. Please enter something."; errors++; } 		/* 		 * Does record exist already? */ 		duplicate = false; if ( populated ) { sql = "SELECT COUNT(*) AS records " + "FROM " + "larder" + " " + // It may make sense to use a variable here at some point... "WHERE description = '" + request.getParameter( "larder_description" ) + "' " ; //			out.print("sql = " + sql + " \n"); rs = st.executeQuery(sql); if (rs.next) { records = Integer.parseInt( rs.getString("records") ); if ( records > 0 ) { duplicate = true; message = "Record already exists. Duplicate records not allowed. Please check your entries."; errors++; } 			} 		} 		/* 		 * If no error, save data, clear fields, display form */ 		/* 		 * Save data... */ 		if ( request.getParameter( "larder_qoh" ).equals("") ) { larderQOH = "0"; } 		else{ larderQOH = request.getParameter( "larder_qoh" ); } 		if ( request.getParameter( "larder_rop" ).equals("") ) { larderROP = "0"; } 		else{ larderROP = request.getParameter( "larder_rop" ); } 		if ( request.getParameter( "larder_pq" ).equals("") ) { larderPQ = "1"; } 		else{ larderPQ = request.getParameter( "larder_pq" ); } 		if ( request.getParameter( "larder_pup" ).equals("") ) { larderPUP = "0"; } 		else{ larderPUP = request.getParameter( "larder_pup" ); } 		if ( errors == 0 ) { sql = "INSERT INTO " + "larder" + " " + "SELECT " + "(SELECT COALESCE(MAX(id), 0) + 1 FROM larder) AS id, " + "'" + request.getParameter( "larder_description" ) + "' AS description, " + "'" + request.getParameter( "larder_plu" ) + "' AS plu, " + "'" + request.getParameter( "larder_upc" ) + "' AS upc, " + "" + larderQOH + " AS qoh, " + "" + larderROP + " AS rop, " + "" + larderPQ + " AS pq, " + "" + larderPUP + " AS pup, " + "'" + request.getParameter( "larder_pb" ) + "' AS pb, " + "'" + request.getParameter( "larder_psl" ) + "' AS psl " ; //			out.print("sql = " + sql + " \n"); st.execute(sql); } 		/* 		 * Clear fields... */ 		larderDescription = ""; larderPLU = ""; larderUPC = ""; larderQOH = ""; larderROP = ""; larderPQ = ""; larderPUP = ""; larderPB = ""; larderPSL = ""; /* 		 * If error, display form as is with the message produced by the preceding code... 		 */ } %> &lt;!DOCTYPE html> &lt;HTML> &lt;HEAD> &lt;link rel="stylesheet" type="text/css" href="&lt;%=css%>"> &lt;TITLE>&lt;%=title%>&lt;/TITLE> &lt;SCRIPT LANGUAGE="JavaScript" SRC="angular.js">&lt;/SCRIPT> &lt;SCRIPT LANGUAGE="JavaScript" SRC="jquery.js">&lt;/SCRIPT> &lt;/HEAD> &lt;BODY> &lt;H1>&lt;%=title%>&lt;/H1> &lt;br> &lt;div align="center"> &lt;nobr> &lt;div class="centered"> &lt;nobr> &lt;form action="LarderInventory.jsp" name=f> &lt;input name=btnG type=submit value=" Return to Larder Inventory "> &lt;/form> &lt;/nobr> &lt;/div> &lt;br> &lt;p class="centered">&lt;%=message%>&lt;/p> &lt;FORM NAME="form1" METHOD="POST"> &lt;!--Get time and pass as hidden variable in query_string to force browser to get page from server instead of cache... --> &lt;% dt = System.currentTimeMillis; %> &lt;input type="hidden" name="dt&lt;%= dt %>" value="&lt;%= dt %>"> &lt;input type="hidden" name="posted" value="true"> &lt;input type="hidden" name="tableName" value="Larder"> &lt;table class="ivory" align="center" xwidth="80%" border="0" cellspacing="0" cellpadding="16"> &lt;TBODY> &lt;tr> &lt;td>&lt;b>Item description:&lt;/B>&lt;/td>&lt;td>&lt;input type="text" name="larder_description" size="127" value="">&lt;/td> &lt;/tr> &lt;tr> &lt;td>&lt;b>PLU:&lt;/B>&lt;/td>&lt;td>&lt;input type="text" name="larder_plu" size="31" value="">&lt;/td> &lt;/tr> &lt;tr> &lt;td>&lt;b>UPC:&lt;/B>&lt;/td>&lt;td>&lt;input type="text" name="larder_upc" size="63" value="">&lt;/td> &lt;/tr> &lt;tr> &lt;td>&lt;b>Quantity on hand:&lt;/B>&lt;/td>&lt;td>&lt;input type="text" name="larder_qoh" size="31" value="">&lt;/td> &lt;/tr> &lt;tr> &lt;td>&lt;b>Restock point:&lt;/B>&lt;/td>&lt;td>&lt;input type="text" name="larder_rop" size="31" value="">&lt;/td> &lt;/tr> &lt;tr> &lt;td>&lt;b>Restock quantity:&lt;/B>&lt;/td>&lt;td>&lt;input type="text" name="larder_pq" size="31" value="">&lt;/td> &lt;/tr> &lt;tr> &lt;td>&lt;b>Purchase unit price:&lt;/B>&lt;/td>&lt;td>&lt;input type="text" name="larder_pup" size="31" value="">&lt;/td> &lt;/tr> &lt;tr> &lt;td>&lt;b>Preferred brand(s):&lt;/B>&lt;/td>&lt;td>&lt;input type="text" name="larder_pb" size="127" value="">&lt;/td> &lt;/tr> &lt;tr> &lt;td>&lt;b>Preferred store(s):&lt;/B>&lt;/td>&lt;td>&lt;input type="text" name="larder_psl" size="127" value="">&lt;/td> &lt;/tr> &lt;tr> &lt;td> &lt;/td>&lt;td> &lt;div > &lt;input type="submit" name="submit" value="Submit"> &lt;input type="reset" name="clear" value="Reset"> &lt;/div> &lt;/td> &lt;/tr> &lt;/table> &lt;br>&lt;br> &lt;/form> &lt;/nobr> &lt;/div> &lt;% } // end try catch ( SQLException ex ) { out.println ( "&lt;pre>" ); out.println ( "---" ); out.println ( "--                      FATAL ERROR IN SQL" ); out.println ( "---" ); out.println ( "-- " ); while ( ex != null ) { out.println ("-- SQLState: " + ex.getSQLState ); out.println ("-- Message: " + ex.getMessage ); out.println ("-- Vendor:  " + ex.getErrorCode ); ex = ex.getNextException ; out.println ( "-- " ); } 	out.println ( "---" ); } // end catch catch ( java.lang.Exception ex ) { /* 	 * Got some other type of exception. Dump it. */ 	out.println ( "&lt;pre>" ); out.println ( "---" ); ex.printStackTrace ; out.println("exception: " + ex.getMessage ); } // end catch finally { // No action for now } // end finally %> &lt;/BODY> &lt;/HTML>

When we notice we entered a description or quantity wrong, we naturally want to fix our mistake. Here is a screen shot of the "Edit Item" page.



Here is the code. &lt;%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.io.*" import="java.sql.*" import="java.util.*" import="java.util.Properties" errorPage="" %> &lt;%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions" %> &lt;% /* * LarderItemEdit.jsp * * Copyright (c) 2013 Alfred Wheeler * All rights reserved. * * This software is the proprietary * information of Alfred Wheeler * */ /**  * The purpose of this application is to edit a record of table "larder" * * USAGE *  LarderItemEdit.jsp * * @author      10/12/2013 created by Alfred Wheeler. * * @version     0.1 * * @see         LarderInventory, LarderItemAdd, LarderShoppingList, LarderItemRemove * */ /*  * Database : demonstration, Table : larder *  * field name                        data type * ---  ---  * id                                integer * description                      character varying(255) * plu                              character varying(255) * upc                              character varying(255) * qoh                              integer * rop                              integer * pq                               integer * pup                              numeric * pb                               character varying(255) * psl                              character varying(255) */ /* * Initialize some variables */ String message = ""; long dt = 0; String title = null; Connection conn = null; DatabaseMetaData dbmd = null; Statement st = null; ResultSet rs = null; ResultSetMetaData rsmd = null; String sql = ""; Integer columns = 0; String posted = "false"; boolean populated = true; boolean recordExists = true; boolean duplicate = false; boolean updated = false; int records = 0; int errors = 0; //String tableName = null; String larderId = ""; String editLarderDescription = ""; String editLarderPLU = ""; String editLarderUPC = ""; String editLarderQOH = ""; String editLarderROP = ""; String editLarderPQ = ""; String editLarderPUP = ""; String editLarderPB = ""; String editLarderPSL = ""; String s = null; // miscellaneous utility variable /* * Get a date time stamp. Set a page title, if needed... */ dt = System.currentTimeMillis; title = "Larder - Edit Item"; /* * Get the database connection values, etc... from the properties file * ( This should be externalized to reduce code clutter... ) */ String p = application.getRealPath("/") + "properties/Demo.properties"; InputStream propsFile; Properties appProp = new Properties; try { propsFile = new FileInputStream(p); appProp.load(propsFile); propsFile.close; } catch (IOException ioe) { out.println("&lt;PRE>"); out.println("I/O Exception."); ioe.printStackTrace; out.println("&lt;/PRE>"); } String dbDriver = "" + appProp.getProperty("dbDriver"); String dbUrl   = "" + appProp.getProperty("dbUrl"); String dbUid   = "" + appProp.getProperty("dbUid"); String dbUpw   = "" + appProp.getProperty("dbUpw"); String css     = "" + appProp.getProperty("css"); //out.println("&lt;PRE>"); //out.println(dbDriver); //out.println(dbUrl); //out.println(dbUid); //out.println(dbUpw); //out.println("&lt;/PRE>"); /* * Open the database */ try { Class.forName(dbDriver); conn = DriverManager.getConnection(dbUrl, dbUid, dbUpw); st = conn.createStatement; /* 	 * Get the request values */ 	if ( request.getParameter( "posted" ) == null ) { posted = "false"; } 	else { posted = request.getParameter( "posted" ); } 	if ( posted.equals("true") ) { /* 		 * 1. Check that the entries are not all blanked out (we don't want to erase any records...) * 2. Check that the entries are valid for their kind (a moot point, for this particular application...) * 3. Check that the significant (primary key) changes do not duplicate any other record... * 3. Make sure the record exists and, ideally, lock it, during the update * 4. Apply the change(s) * 5. Return to list */ 		/* 		 * Are the required fields populated? */ 		larderId = request.getParameter( "larder_id" ); editLarderDescription = request.getParameter( "editLarderDescription" ); editLarderPLU = request.getParameter( "editLarderPLU" ); editLarderUPC = request.getParameter( "editLarderUPC" ); editLarderQOH = request.getParameter( "editLarderQOH" ); editLarderROP = request.getParameter( "editLarderROP" ); editLarderPQ = request.getParameter( "editLarderPQ" ); editLarderPUP = request.getParameter( "editLarderPUP" ); editLarderPB = request.getParameter( "editLarderPB" ); editLarderPSL = request.getParameter( "editLarderPSL" ); populated = true; if ( (request.getParameter( "editLarderDescription" )).equals( "" ) ) { message = "Description is a required field. Please enter something."; errors++; } 		/* 		 * Does record "still" exist? */ 		recordExists = true; if ( populated ) { sql = "SELECT COUNT(*) AS records " + "FROM " + "larder" + " " + // It may make sense to use a variable for table_name at some point... "WHERE id = " + request.getParameter( "larder_id" ) ; //			out.print("sql = " + sql + " \n"); rs = st.executeQuery(sql); if (rs.next) { records = Integer.parseInt( rs.getString("records") ); if ( records &lt; 1 ) { recordExists = false; message = "Record does not exist. You may have removed by some other means or another page."; errors++; } 			} 		} 		/* 		 * Would the change cause an "exact" duplicate? */ 		duplicate = false; if ( populated ) { sql = "SELECT COUNT(*) AS records " + "FROM " + "larder" + " " + // It may make sense to use a variable here at some point... "WHERE description = '" + request.getParameter( "editLarderDescription" ) + "' " + "AND id != " + request.getParameter( "larder_id" ) ; //			out.print("sql = " + sql + " \n"); rs = st.executeQuery(sql); if (rs.next) { records = Integer.parseInt( rs.getString("records") ); if ( records > 0 ) { duplicate = true; message = "Record already exists. Duplicate records not allowed. Please check your entries."; errors++; } 			} 		} 		/* 		 * If no error, save data, clear fields, display form */ 		/* 		 * Save data... */ 		if ( errors == 0 ) { // This should be a call to a stored procedure... sql = "UPDATE larder SET " + "description = '" + request.getParameter( "editLarderDescription" ) + "', " + "plu = '" + request.getParameter( "editLarderPLU" ) + "', " + "upc = '" + request.getParameter( "editLarderUPC" ) + "', " + "qoh = " + request.getParameter( "editLarderQOH" ) + ", " + "rop = " + request.getParameter( "editLarderROP" ) + ", " + "pq = " + request.getParameter( "editLarderPQ" ) + ", " + "pup = " + request.getParameter( "editLarderPUP" ) + ", " + "pb = '" + request.getParameter( "editLarderPB" ) + "', " + "psl = '" + request.getParameter( "editLarderPSL" ) + "' " + "WHERE id = '" + request.getParameter( "larder_id" ) + "' "; //			out.print("sql = " + sql + " \n"); st.execute(sql); /* 			 * Return to list.... */ %> &lt;HTML> &lt;HEAD>&lt;TITLE>&lt;/TITLE>&lt;/HEAD> &lt;BODY onload="document.form1.submit"> &lt;div> &lt;form name="form1" method="POST" action="LarderInventory.jsp?&lt;%=System.currentTimeMillis%>"> &lt;input type="image" src="images/dots/clear.gif" alt="submit" value="Continue"> &lt;/form> &lt;/div> &lt;% updated = true; } 	} 	else { // This is the first time here, so get the values from the database table /* 		 * Note: Must work out automating getting (or just editing) only the * fields that are relevant, e.g. the timestamps are generally not that * relevant to data entry persons */ 		sql = "SELECT " + "id AS larder_id, " + "description AS larder_description, " + "plu AS larder_plu, " + "upc AS larder_upc, " + "qoh AS larder_qoh, " + "rop AS larder_rop, " + "pq AS larder_pq, " + "pup AS larder_pup, " + "pb AS larder_pb, " + "psl AS larder_psl " + "FROM larder " + "WHERE id = " + request.getParameter( "id" ) + " "; //		out.print("sql = " + sql + " \n"); rs = st.executeQuery(sql); if (rs.next) { larderId = rs.getString("larder_id"); editLarderDescription = rs.getString("larder_description"); editLarderPLU = rs.getString("larder_plu"); editLarderUPC = rs.getString("larder_upc"); editLarderQOH = rs.getString("larder_qoh"); editLarderROP = rs.getString("larder_rop"); editLarderPQ = rs.getString("larder_pq"); editLarderPUP = rs.getString("larder_pup"); editLarderPB = rs.getString("larder_pb"); editLarderPSL = rs.getString("larder_psl"); } 	} 	if ( updated == false ) { %> &lt;!DOCTYPE html> &lt;HTML> &lt;HEAD> &lt;link rel="stylesheet" type="text/css" href="&lt;%=css%>"> &lt;TITLE>&lt;%=title%>&lt;/TITLE> &lt;SCRIPT LANGUAGE="JavaScript" SRC="angular.js">&lt;/SCRIPT> &lt;SCRIPT LANGUAGE="JavaScript" SRC="jquery.js">&lt;/SCRIPT> &lt;/HEAD> &lt;BODY> &lt;H1>&lt;%=title%>&lt;/H1> &lt;br>&lt;br>&lt;br>&lt;br> &lt;div class="centered"> &lt;nobr> &lt;form action="LarderInventory.jsp" name=f> &lt;input name=btnG type=submit value=" Return to Larder Inventory "> &lt;/form> &lt;/nobr> &lt;/div> &lt;br> &lt;p class="centered">&lt;%=message%>&lt;/p> &lt;div class="centered"> &lt;FORM NAME="form1" METHOD="POST"> &lt;!--Get time and pass as hidden variable in query_string to force browser to get page from server instead of cache... --> &lt;% dt = System.currentTimeMillis; %> &lt;input type="hidden" name="dt&lt;%= dt %>" value="&lt;%= dt %>"> &lt;input type="hidden" name="posted" value="true"> &lt;input type="hidden" name="tableName" value="larder"> &lt;input type="hidden" name="larder_id" value="&lt;%=larderId%>"> &lt;table border="1"> &lt;TBODY> &lt;tr align="left"> &lt;td>&lt;b>Item description:&lt;/B>&lt;/td>&lt;td>&lt;input type="text" name="editLarderDescription" size="127" value="&lt;%= editLarderDescription%>">&lt;/td> &lt;/tr> &lt;tr align="left"> &lt;td>&lt;b>PLU:&lt;/B>&lt;/td>&lt;td>&lt;input type="text" name="editLarderPLU" size="31" value="&lt;%= editLarderPLU%>">&lt;/td> &lt;/tr> &lt;tr align="left"> &lt;td>&lt;b>UPC:&lt;/B>&lt;/td>&lt;td>&lt;input type="text" name="editLarderUPC" size="63" value="&lt;%= editLarderUPC%>">&lt;/td> &lt;/tr> &lt;tr align="left"> &lt;td>&lt;b>Quantity on hand:&lt;/B>&lt;/td>&lt;td>&lt;input type="text" name="editLarderQOH" size="31" value="&lt;%= editLarderQOH%>">&lt;/td> &lt;/tr> &lt;tr align="left"> &lt;td>&lt;b>Restock point:&lt;/B>&lt;/td>&lt;td>&lt;input type="text" name="editLarderROP" size="31" value="&lt;%= editLarderROP%>">&lt;/td> &lt;/tr> &lt;tr align="left"> &lt;td>&lt;b>Restock quantity:&lt;/B>&lt;/td>&lt;td>&lt;input type="text" name="editLarderPQ" size="31" value="&lt;%= editLarderPQ%>">&lt;/td> &lt;/tr> &lt;tr align="left"> &lt;td>&lt;b>Purchase unit price:&lt;/B>&lt;/td>&lt;td>&lt;input type="text" name="editLarderPUP" size="31" value="&lt;%= editLarderPUP%>">&lt;/td> &lt;/tr> &lt;tr align="left"> &lt;td>&lt;b>Preferred brand(s):&lt;/B>&lt;/td>&lt;td>&lt;input type="text" name="editLarderPB" size="127" value="&lt;%= editLarderPB%>">&lt;/td> &lt;/tr> &lt;tr align="left"> &lt;td>&lt;b>Preferred store(s):&lt;/B>&lt;/td>&lt;td>&lt;input type="text" name="editLarderPSL" size="127" value="&lt;%= editLarderPSL%>">&lt;/td> &lt;/tr> &lt;tr align="left"> &lt;td> &lt;/td>&lt;td> &lt;div > &lt;input type="submit" name="submit" value="Submit"> &lt;input type="reset" name="clear" value="Reset"> &lt;/div> &lt;/td> &lt;/tr> &lt;/table> &lt;br>&lt;br> &lt;/form> &lt;/div> &lt;% } } // end try catch ( SQLException ex ) { out.println ( "&lt;pre>" ); out.println ( "---" ); out.println ( "--                      FATAL ERROR IN SQL" ); out.println ( "---" ); out.println ( "-- " ); while ( ex != null ) { out.println ("-- SQLState: " + ex.getSQLState ); out.println ("-- Message: " + ex.getMessage ); out.println ("-- Vendor:  " + ex.getErrorCode ); ex = ex.getNextException ; out.println ( "-- " ); } 	out.println ( "---" ); } // end catch catch ( java.lang.Exception ex ) { /* 	 * Got some other type of exception. Dump it. */ 	out.println ( "&lt;pre>" ); out.println ( "---" ); ex.printStackTrace ; out.println("exception: " + ex.getMessage ); } // end catch finally { // No action for now } // end finally %> &lt;/body> &lt;/html>

Over time our tastes change. Sometime we feel the need to remove an item from our usual fare. What better way than to forget it ever existed? Here is a screen shot of the "Remove Item" (from our larder) page.



Here is the code. &lt;%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.io.*" import="java.sql.*" import="java.util.*" import="java.util.Properties" errorPage="" %> &lt;%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions" %> &lt;% /* * LarderItemRemove.jsp * * Copyright (c) 2013 Alfred Wheeler * All rights reserved. * * This software is the proprietary * information of Alfred Wheeler * */ /**  * The purpose of this application is to remove a record from table "larder" * * USAGE *  LarderItemRemove.jsp * * @author      10/9/2013 created by Alfred Wheeler. * * @version     0.1 * * @see         LarderInventory, LarderItemAdd, LarderShoppingList, LarderItemEdit * */ /*  * Database : demonstration, Table : larder *  * field name                        data type * ---  ---  * id                                integer * description                      character varying(255) * plu                              character varying(255) * upc                              character varying(255) * qoh                              integer * rop                              integer * pq                               integer * pup                              numeric * pb                               character varying(255) * psl                              character varying(255) */ /* * Initialize some variables */ String message = ""; long dt = 0; String title = null; Connection conn = null; DatabaseMetaData dbmd = null; Statement st = null; ResultSet rs = null; ResultSetMetaData rsmd = null; String sql = ""; Integer columns = 0; int column = 0; String fields = null; String posted = "false"; boolean populated = true; boolean recordExists = true; boolean duplicate = false; boolean updated = false; int records = 0; int errors = 0; //String tableName = null; //String larderId = ""; //String editLarderDescription = ""; //String editLarderPLU = ""; //String editLarderUPC = ""; //String editLarderQOH = ""; //String editLarderROP = ""; //String editLarderPQ = ""; //String editLarderPUP = ""; //String editLarderPB = ""; //String editLarderPSL = ""; //String s = null; // miscellaneous utility variable /* * Get a date time stamp. Set a page title, if needed... */ dt = System.currentTimeMillis; title = "Larder - Remove Item"; /* * Get the database connection values, etc... from the properties file * ( This eventually will be externalized to reduce code clutter... ) */ String p = application.getRealPath("/") + "properties/Demo.properties"; InputStream propsFile; Properties appProp = new Properties; try { propsFile = new FileInputStream(p); appProp.load(propsFile); propsFile.close; } catch (IOException ioe) { out.println("&lt;PRE>"); out.println("I/O Exception."); ioe.printStackTrace; out.println("&lt;/PRE>"); } String dbDriver = "" + appProp.getProperty("dbDriver"); String dbUrl   = "" + appProp.getProperty("dbUrl"); String dbUid   = "" + appProp.getProperty("dbUid"); String dbUpw   = "" + appProp.getProperty("dbUpw"); String css     = "" + appProp.getProperty("css"); /* * Open the database */ try { Class.forName(dbDriver); conn = DriverManager.getConnection(dbUrl, dbUid, dbUpw); st = conn.createStatement; /* 	 * Get the request values */ 	if ( request.getParameter( "posted" ) == null ) { posted = "false"; } 	else { posted = request.getParameter( "posted" ); } 	if ( posted.equals("true") ) { /* 		 * 1. Make sure the record exists and, ideally, lock it, during the removal * 2. Remove the record * 3. Return to list */ 		/* 		 * Does record "still" exist? */ 		recordExists = true; if ( populated ) { sql = "SELECT COUNT(*) AS records " + "FROM " + "larder" + " " + // It may make sense to use a variable for table_name at some point... "WHERE id = " + request.getParameter( "id" ) ; //			out.print("sql = " + sql + " \n"); rs = st.executeQuery(sql); if (rs.next) { records = Integer.parseInt( rs.getString("records") ); if ( records &lt; 1 ) { recordExists = false; message = "Record does not exist. You may have removed by some other means or another page."; errors++; } 			} 		} 		/* 		 * If no error, delete the record and then return to list */ 		if ( errors == 0 ) { sql = "DELETE FROM larder " + "WHERE id = " + request.getParameter( "id" ) + " "; //out.println( "sql = " + sql); st.execute( sql ); %> &lt;BODY onload="document.form1.submit"> &lt;div> &lt;form name="form1" method="POST" action="LarderInventory.jsp?&lt;%=dt%>"> &lt;input type="image" src="images/dots/clear.gif" alt="submit" value="Continue"> &lt;/form> &lt;/div> &lt;% } 	} 	/* 	 * If we got this far, then either we arrived from another page or there was some error... */ %> &lt;!DOCTYPE html> &lt;HTML> &lt;HEAD> &lt;link rel="stylesheet" type="text/css" href="&lt;%=css%>"> &lt;TITLE>&lt;%=title%>&lt;/TITLE> &lt;SCRIPT LANGUAGE="JavaScript" SRC="angular.js">&lt;/SCRIPT> &lt;SCRIPT LANGUAGE="JavaScript" SRC="jquery.js">&lt;/SCRIPT> &lt;/HEAD> &lt;BODY> &lt;H1>&lt;%=title%>&lt;/H1> &lt;br>&lt;br>&lt;br>&lt;br> &lt;div class="centered"> &lt;nobr> &lt;form action="LarderInventory.jsp" name=f> &lt;input name=btnG type=submit value=" Return to Larder Inventory "> &lt;/form> &lt;/nobr> &lt;/div> &lt;br> &lt;p class="centered">&lt;%=message%>&lt;/p> &lt;% fields = "*"; // will implement this later... sql = "SELECT " + fields + " FROM " + "larder" + " " + "WHERE id = " + request.getParameter( "id" ) + " "; //out.print("\n\nsql = " + sql + "\n\n"); rs = st.executeQuery(sql); rsmd = rs.getMetaData; columns = rsmd.getColumnCount; rs = st.executeQuery(sql); if (rs.next) { %> &lt;div class="centered"> &lt;FORM NAME="form1" METHOD="POST"> &lt;!--Get time and pass as hidden variable in query_string to force browser to get page from server instead of cache... --> &lt;% dt = System.currentTimeMillis; %> &lt;input type="hidden" name="dt&lt;%= dt %>" value="&lt;%= dt %>"> &lt;input type="hidden" name="posted" value="true"> &lt;input type="hidden" name="tableName" value="larder"> &lt;input type="hidden" name="larder_id" value="&lt;%=request.getParameter( "larder_id" )%>"> &lt;table class="ivory" border="0" cellspacing="0"> &lt;TBODY> &lt;tr>&lt;th>Field&lt;/th>&lt;th>Value&lt;/th>&lt;/tr> &lt;% for( column=1; column&lt;=columns ; column++ ) { out.println ( "&lt;tr align=\"left\">" ); out.println ( "&lt;td>&lt;b>" + rsmd.getColumnName( column ) + ":&lt;/B>&lt;/td>" ); out.println ( "&lt;td>" + rs.getString( rsmd.getColumnName( column ) ) + "&lt;/td>" ); out.println ( "&lt;/tr>" ); } %> &lt;tr align="left"> &lt;td> &lt;/td>&lt;td> &lt;div > &lt;input type="submit" name="submit" value=" Confirm "> &lt;/div> &lt;/td> &lt;/tr> &lt;/table> &lt;br>&lt;br> &lt;/form> &lt;/div> &lt;% } // end if for result set handling } // end try catch ( SQLException ex ) { out.println ( "&lt;pre>" ); out.println ( "---" ); out.println ( "--                      FATAL ERROR IN SQL" ); out.println ( "---" ); out.println ( "-- " ); while ( ex != null ) { out.println ("-- SQLState: " + ex.getSQLState ); out.println ("-- Message: " + ex.getMessage ); out.println ("-- Vendor:  " + ex.getErrorCode ); ex = ex.getNextException ; out.println ( "-- " ); } 	out.println ( "---" ); } // end catch catch ( java.lang.Exception ex ) { /* 	 * Got some other type of exception. Dump it. */ 	out.println ( "&lt;pre>" ); out.println ( "---" ); ex.printStackTrace ; out.println("exception: " + ex.getMessage ); } // end catch finally { // No action for now } // end finally %> &lt;/body> &lt;/html>

That's the basic functionality. We could stop at this point and the application would be useful. The remaining pages of the application are the "Grocery Shopping List", "Add New Grocery Shopping Item" and "Edit Item" (on the shopping list).

Here is a screen shot of the shopping list page.



Here is the code. &lt;%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.text.*" import="java.io.*" import="java.sql.*" import="java.util.*" errorPage="" %> &lt;%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions" %> &lt;% /* * LarderShoppingList.jsp * * Copyright (c) 2013 Alfred J Wheeler * All rights reserved. * * This software is the proprietary * information of Alfred J Wheeler * */ /**  * The purpose of this program is to provide larder inventory * management to help with grocery shopping. * * USAGE *  LarderShoppingList.jsp * * @author      10/12/2013 created by Alfred J Wheeler. * * @version     0.1 * * @see         LarderInventory, LarderItemAdd, LarderItemEdit, LarderItemRemove * */ /*  * Database : demonstration, Table : larder *  * field name                        data type * ---  ---  * id                                integer * description                      character varying(255) * plu                              character varying(255) * upc                              character varying(255) * qoh                              integer * rop                              integer * pup                              numeric * pb                               character varying(255) * psl                              character varying(255) */ /* * Initialize some variables */ String message = ""; long dt = 0; String title = null; Connection conn = null; DatabaseMetaData dbmd = null; Statement st = null; ResultSet rs = null; ResultSetMetaData rsmd = null; String sql = ""; Integer columns = 0; String posted = "false"; boolean populated = true; boolean recordExists = true; boolean duplicate = false; boolean updated = false; int records = 0; int errors = 0; //String tableName = null; String larderId = ""; String editLarderDescription = ""; String editLarderPLU = ""; String editLarderUPC = ""; String editLarderQOH = ""; String editLarderROP = ""; String editLarderPUP = ""; String editLarderPB = ""; String editLarderPSL = ""; String s = null; // miscellaneous utility variable /* * Get a date time stamp. Set a page title, if needed... */ dt = System.currentTimeMillis; title = "Grocery Shopping List"; /* * Get the database connection values, etc... from the properties file * ( This eventually will be externalized to reduce code clutter... ) */ String p = application.getRealPath("/") + "properties/Demo.properties"; InputStream propsFile; Properties appProp = new Properties; try { propsFile = new FileInputStream(p); appProp.load(propsFile); propsFile.close; } catch (IOException ioe) { out.println("&lt;PRE>"); out.println("I/O Exception."); ioe.printStackTrace; out.println("&lt;/PRE>"); } String dbDriver = "" + appProp.getProperty("dbDriver"); String dbUrl   = "" + appProp.getProperty("dbUrl"); String dbUid   = "" + appProp.getProperty("dbUid"); String dbUpw   = "" + appProp.getProperty("dbUpw"); String css     = "" + appProp.getProperty("css"); //out.println("&lt;PRE>"); //out.println(dbDriver); //out.println(dbUrl); //out.println(dbUid); //out.println(dbUpw); //out.println("&lt;/PRE>"); /* * Open the database */ try { Class.forName(dbDriver); conn = DriverManager.getConnection(dbUrl, dbUid, dbUpw); st = conn.createStatement; %> &lt;!DOCTYPE html> &lt;HTML> &lt;HEAD> &lt;link rel="stylesheet" type="text/css" href="&lt;%=css%>"> &lt;TITLE>&lt;%=title%>&lt;/TITLE> &lt;SCRIPT LANGUAGE="JavaScript" SRC="angular.js">&lt;/SCRIPT> &lt;SCRIPT LANGUAGE="JavaScript" SRC="jquery.js">&lt;/SCRIPT> &lt;/HEAD> &lt;BODY> &lt;H1>&lt;%=title%>&lt;/H1> &lt;br> &lt;div class="centered"> &lt;nobr> &lt;form action="LarderInventory.jsp" name=f> &lt;input name=btnG type=submit value=" Return to Larder Inventory "> &lt;/form> &lt;/nobr> &lt;/div> &lt;br> &lt;div class="centered"> &lt;TABLE class="ivory" border="0"> &lt;tr> &lt;td> &lt;nobr> &lt;form action="LarderShoppingItemAdd.jsp" name=f1> &lt;input name=btnG type=submit value=" Add New Grocery Shopping Item "> &lt;/form> &lt;/nobr> &lt;/td> &lt;/tr> &lt;/TABLE> &lt;/div> &lt;br> &lt;p class="centered">&lt;%=message%>&lt;/p> &lt;TABLE border="1" cellspacing="0"> &lt;TBODY> &lt;th style="padding:14px;text-align:left;" colspan="2">Items and estimated costs&lt;/th> &lt;% sql = "SELECT " + "id, " + // internal item id 				"description, " + // description or product name "plu, " + // price look up 				"pq, " + // (default/minimum) purchase quantity "pup, " + // purchase unit price "(pq * pup) AS ep, " + // extended price "pb, " + // preferred brand(s) "psl " + // preferred grocery store (with location) "FROM larder " + "WHERE ( ( qoh - rop ) &lt;= 0 ) " + "ORDER BY UPPER( description )" ; //		out.print("sql = " + sql + " \n"); rs = st.executeQuery(sql); rsmd = rs.getMetaData; columns = rsmd.getColumnCount; // if there are not columns, then there are not rows... if ( columns > 0 ) { while(rs.next) { out.println( "&lt;tr>" ); out.println( "&lt;td width=\"450\">" ); out.println( "&lt;table width=\"100%\" border=\"0\" cellpadding=\"0\" cellspacing=\"0\">" ); out.println( "&lt;tr>" ); out.println( "&lt;td>" + rs.getString("description") + "&lt;/td>" ); out.println( "&lt;td align=\"right\">( " + rs.getString("pq") + " * " ); 				out.println( " " + rs.getString("pup") + " ) = " ); out.println( " " + rs.getString("ep") + "&lt;/td>" ); out.println( "&lt;/tr>" ); out.println( "&lt;/table>" ); out.println( "&lt;/td>" ); out.println( "&lt;td>" ); out.println( "&lt;table border=\"0\" cellpadding=\"0\" cellspacing=\"0\">" ); out.println( "&lt;tr>" ); out.println( "&lt;td>" ); out.println( "&lt;form action=\"LarderShoppingItemEdit.jsp\" name=\"edit\">" ); out.println( "&lt;input name=\"id\" type=\"hidden\" value=\"" + rs.getString("id") + "\">" ); out.println( "&lt;input name=\"btnG\" type=\"submit\" value=\"Edit Item\">" ); out.println( "&lt;/form>" ); out.println( "&lt;/td>" ); //				out.println( "&lt;td>" ); //				out.println( "&lt;form action=\"LarderItemRemove.jsp\" name=\"remove\">" ); //				out.println( "&lt;input name=\"id\" type=\"hidden\" value=\"" + rs.getString("id") + "\">" ); //				out.println( "&lt;input name=\"btnG\" type=\"submit\" value=\"Remove Item\">" ); //				out.println( "&lt;/form>" ); //				out.println( "&lt;/td>" ); out.println( "&lt;/tr>" ); out.println( "&lt;/table>" ); out.println( "&lt;/td>" ); out.println( "&lt;/tr>" ); } 		} %> &lt;/tbody>&lt;/table> &lt;% } // end try catch ( SQLException ex ) { out.println ( "&lt;pre>" ); out.println ( "---" ); out.println ( "--                      FATAL ERROR IN SQL" ); out.println ( "---" ); out.println ( "-- " ); while ( ex != null ) { out.println ("-- SQLState: " + ex.getSQLState ); out.println ("-- Message: " + ex.getMessage ); out.println ("-- Vendor:  " + ex.getErrorCode ); ex = ex.getNextException ; out.println ( "-- " ); } 		out.println ( "---" ); } // end catch catch ( java.lang.Exception ex ) { /* 		 * Got some other type of exception. Dump it. */ 		out.println ( "&lt;pre>" ); out.println ( "---" ); ex.printStackTrace ; out.println("exception: " + ex.getMessage ); } // end catch finally { // No action for now } // end finally %> &lt;br>&lt;br>&lt;br>&lt;br> &lt;/body> &lt;/html>

So, now, we are at the supermarket and we spot a new item on the shelf! And we reeeealy reeeealy want it! But it's not on our shopping list! Goodness sakes! What to do? Easy! Just add it to the list. Here is a screen shot of the "Add New Shopping List Item" page.



Here is the code. &lt;%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.io.*" import="java.sql.*" import="java.util.*" import="java.util.Properties" errorPage="" %> &lt;% /* * LarderShoppingItemAdd.jsp * * Copyright (c) 2013 Alfred J Wheeler * All rights reserved. * * This software is the proprietary * information of Alfred J Wheeler * */ /**  * The purpose of this application is to add a record to table Larder * * USAGE *  LarderShoppingItemAdd.jsp * * @author      10/12/2013 created by Alfred J Wheeler. * * @version     0.1 * * @see         LarderInventory, LarderShoppingList, LarderItemEdit, LarderItemRemove * */ /*  * Database : demonstration, Table : larder *  * field name                        data type * ---  ---  * id                                integer * description                      character varying(255) * plu                              character varying(255) * upc                              character varying(255) * qoh                              integer * rop                              integer * pq                               integer * pup                              numeric * pb                               character varying(255) * psl                              character varying(255) */ /* * Initialize some variables */ long dt = 0; String title = null; Connection conn = null; Statement st = null; ResultSet rs = null; String sql = ""; String posted = "false"; boolean populated = true; boolean duplicate = false; int records = 0; String message = ""; int errors = 0; //String tableName = null; String larderDescription = ""; String larderPLU = ""; String larderUPC = ""; String larderQOH = ""; String larderROP = ""; String larderPQ = ""; String larderPUP = ""; String larderPB = ""; String larderPSL = ""; /* * Get a date time stamp. Set a page title, if needed... */ dt = System.currentTimeMillis; title = "Larder - Add New Shopping List Item"; /* * Get the database connection values, etc... from the properties file * ( This eventually will be externalized to reduce code clutter... ) */ String p = application.getRealPath("/") + "properties/Demo.properties"; InputStream propsFile; Properties appProp = new Properties; try { propsFile = new FileInputStream(p); appProp.load(propsFile); propsFile.close; } catch (IOException ioe) { out.println("&lt;PRE>"); out.println("I/O Exception."); ioe.printStackTrace; out.println("&lt;/PRE>"); } String dbDriver = "" + appProp.getProperty("dbDriver"); String dbUrl   = "" + appProp.getProperty("dbUrl"); String dbUid   = "" + appProp.getProperty("dbUid"); String dbUpw   = "" + appProp.getProperty("dbUpw"); String css     = "" + appProp.getProperty("css"); //out.println("&lt;PRE>"); //out.println(dbDriver); //out.println(dbUrl); //out.println(dbUid); //out.println(dbUpw); //out.println("&lt;/PRE>"); /* * Open the database */ try { Class.forName(dbDriver); conn = DriverManager.getConnection(dbUrl, dbUid, dbUpw); st = conn.createStatement; /* 	 * Get the request values */ 	if ( request.getParameter( "posted" ) == null ) { posted = "false"; } 	else { posted = request.getParameter( "posted" ); } 	if ( posted.equals("true") ) { /* 		 * Are the fields populated? */ 		larderDescription = request.getParameter( "larder_description" ); populated = true; if ( (request.getParameter( "larder_description" )).equals( "" ) ) { populated = false; message = "Description is required. Please enter something."; errors++; } 		/* 		 * Does record exist already? */ 		duplicate = false; if ( populated ) { sql = "SELECT COUNT(*) AS records " + "FROM " + "larder" + " " + // It may make sense to use a variable here at some point... "WHERE description = '" + request.getParameter( "larder_description" ) + "' " ; //			out.print("sql = " + sql + " \n"); rs = st.executeQuery(sql); if (rs.next) { records = Integer.parseInt( rs.getString("records") ); if ( records > 0 ) { duplicate = true; message = "Record already exists. Duplicate records not allowed. Please check your entries."; errors++; } 			} 		} 		/* 		 * If no error, save data, clear fields, display form */ 		/* 		 * Save data... */ 		if ( request.getParameter( "larder_qoh" ).equals("") ) { larderQOH = "0"; } 		else{ larderQOH = request.getParameter( "larder_qoh" ); } 		if ( request.getParameter( "larder_rop" ).equals("") ) { larderROP = "0"; } 		else{ larderROP = request.getParameter( "larder_rop" ); } 		if ( request.getParameter( "larder_pq" ).equals("") ) { larderPQ = "1"; } 		else{ larderPQ = request.getParameter( "larder_pq" ); } 		if ( request.getParameter( "larder_pup" ).equals("") ) { larderPUP = "0"; } 		else{ larderPUP = request.getParameter( "larder_pup" ); } 		if ( errors == 0 ) { sql = "INSERT INTO " + "larder" + " " + "SELECT " + "(SELECT COALESCE(MAX(id), 0) + 1 FROM larder) AS id, " + "'" + request.getParameter( "larder_description" ) + "' AS description, " + "'" + request.getParameter( "larder_plu" ) + "' AS plu, " + "'" + request.getParameter( "larder_upc" ) + "' AS upc, " + "" + larderQOH + " AS qoh, " + "" + larderROP + " AS rop, " + "" + larderPQ + " AS pq, " + "" + larderPUP + " AS pup, " + "'" + request.getParameter( "larder_pb" ) + "' AS pb, " + "'" + request.getParameter( "larder_psl" ) + "' AS psl " ; //			out.print("sql = " + sql + " \n"); st.execute(sql); } 		/* 		 * Clear fields... */ 		larderDescription = ""; larderPLU = ""; larderUPC = ""; larderQOH = ""; larderROP = ""; larderPQ = ""; larderPUP = ""; larderPB = ""; larderPSL = ""; /* 		 * If error, display form as is with the message produced by the preceding code... 		 */ } %> &lt;!DOCTYPE html> &lt;HTML> &lt;HEAD> &lt;link rel="stylesheet" type="text/css" href="&lt;%=css%>"> &lt;TITLE>&lt;%=title%>&lt;/TITLE> &lt;SCRIPT LANGUAGE="JavaScript" SRC="angular.js">&lt;/SCRIPT> &lt;SCRIPT LANGUAGE="JavaScript" SRC="jquery.js">&lt;/SCRIPT> &lt;/HEAD> &lt;BODY> &lt;H1>&lt;%=title%>&lt;/H1> &lt;br> &lt;div align="center"> &lt;nobr> &lt;div class="centered"> &lt;nobr> &lt;form action="LarderShoppingList.jsp" name=f> &lt;input name=btnG type=submit value=" Return to Shopping List "> &lt;/form> &lt;/nobr> &lt;/div> &lt;br> &lt;p class="centered">&lt;%=message%>&lt;/p> &lt;FORM NAME="form1" METHOD="POST"> &lt;!--Get time and pass as hidden variable in query_string to force browser to get page from server instead of cache... --> &lt;% dt = System.currentTimeMillis; %> &lt;input type="hidden" name="dt&lt;%= dt %>" value="&lt;%= dt %>"> &lt;input type="hidden" name="posted" value="true"> &lt;input type="hidden" name="tableName" value="Larder"> &lt;table class="ivory" align="center" xwidth="80%" border="0" cellspacing="0" cellpadding="16"> &lt;TBODY> &lt;tr> &lt;td>&lt;b>Item description:&lt;/B>&lt;/td>&lt;td>&lt;input type="text" name="larder_description" size="127" value="">&lt;/td> &lt;/tr> &lt;tr> &lt;td>&lt;b>PLU:&lt;/B>&lt;/td>&lt;td>&lt;input type="text" name="larder_plu" size="31" value="">&lt;/td> &lt;/tr> &lt;tr> &lt;td>&lt;b>UPC:&lt;/B>&lt;/td>&lt;td>&lt;input type="text" name="larder_upc" size="63" value="">&lt;/td> &lt;/tr> &lt;input type="hidden" name="larder_qoh" value="0"> &lt;input type="hidden" name="larder_rop" value="0"> &lt;tr> &lt;td>&lt;b>Restock quantity:&lt;/B>&lt;/td>&lt;td>&lt;input type="text" name="larder_pq" size="31" value="">&lt;/td> &lt;/tr> &lt;tr> &lt;td>&lt;b>Purchase unit price:&lt;/B>&lt;/td>&lt;td>&lt;input type="text" name="larder_pup" size="31" value="">&lt;/td> &lt;/tr> &lt;tr> &lt;td>&lt;b>Preferred brand(s):&lt;/B>&lt;/td>&lt;td>&lt;input type="text" name="larder_pb" size="127" value="">&lt;/td> &lt;/tr> &lt;tr> &lt;td>&lt;b>Preferred store(s):&lt;/B>&lt;/td>&lt;td>&lt;input type="text" name="larder_psl" size="127" value="">&lt;/td> &lt;/tr> &lt;tr> &lt;td> &lt;/td>&lt;td> &lt;div > &lt;input type="submit" name="submit" value="Submit"> &lt;input type="reset" name="clear" value="Reset"> &lt;/div> &lt;/td> &lt;/tr> &lt;/table> &lt;br>&lt;br> &lt;/form> &lt;/nobr> &lt;/div> &lt;% } // end try catch ( SQLException ex ) { out.println ( "&lt;pre>" ); out.println ( "---" ); out.println ( "--                      FATAL ERROR IN SQL" ); out.println ( "---" ); out.println ( "-- " ); while ( ex != null ) { out.println ("-- SQLState: " + ex.getSQLState ); out.println ("-- Message: " + ex.getMessage ); out.println ("-- Vendor:  " + ex.getErrorCode ); ex = ex.getNextException ; out.println ( "-- " ); } 	out.println ( "---" ); } // end catch catch ( java.lang.Exception ex ) { /* 	 * Got some other type of exception. Dump it. */ 	out.println ( "&lt;pre>" ); out.println ( "---" ); ex.printStackTrace ; out.println("exception: " + ex.getMessage ); } // end catch finally { // No action for now } // end finally %> &lt;/BODY> &lt;/HTML>

We are almost done shopping and we suddenly remember auntie Zelda will be visiting this weekend and 5 packages of cream cheese will not be nearly enough, because auntie Zelda dearly loves her cream cheese! Okay, we'll just update the purchase quantity, quickly note the updated extended cost and, "Voila! We're good to go!" Here is a screen shot of the "Edit Shopping List Item" page.



Here is the code. &lt;%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.io.*" import="java.sql.*" import="java.util.*" import="java.util.Properties" errorPage="" %> &lt;%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions" %> &lt;% /* * LarderShoppingItemEdit.jsp * * Copyright (c) 2013 Alfred Wheeler * All rights reserved. * * This software is the proprietary * information of Alfred Wheeler * */ /**  * The purpose of this application is to edit a record of table "larder" * * USAGE *  LarderShoppingItemEdit.jsp * * @author      10/12/2013 created by Alfred Wheeler. * * @version     0.1 * * @see         LarderInventory, LarderItemAdd, LarderShoppingList, LarderItemRemove * */ /*  * Database : demonstration, Table : larder *  * field name                        data type * ---  ---  * id                                integer * description                      character varying(255) * plu                              character varying(255) * upc                              character varying(255) * qoh                              integer * rop                              integer * pq                               integer * pup                              numeric * pb                               character varying(255) * psl                              character varying(255) */ /* * Initialize some variables */ String message = ""; long dt = 0; String title = null; Connection conn = null; DatabaseMetaData dbmd = null; Statement st = null; ResultSet rs = null; ResultSetMetaData rsmd = null; String sql = ""; Integer columns = 0; String posted = "false"; boolean populated = true; boolean recordExists = true; boolean duplicate = false; boolean updated = false; int records = 0; int errors = 0; //String tableName = null; String larderId = ""; String editLarderDescription = ""; String editLarderPLU = ""; String editLarderUPC = ""; String editLarderQOH = ""; String editLarderROP = ""; String editLarderPQ = ""; String editLarderPUP = ""; String editLarderPB = ""; String editLarderPSL = ""; String s = null; // miscellaneous utility variable /* * Get a date time stamp. Set a page title, if needed... */ dt = System.currentTimeMillis; title = "Larder - Edit Shopping List Item"; /* * Get the database connection values, etc... from the properties file * ( This should be externalized to reduce code clutter... ) */ String p = application.getRealPath("/") + "properties/Demo.properties"; InputStream propsFile; Properties appProp = new Properties; try { propsFile = new FileInputStream(p); appProp.load(propsFile); propsFile.close; } catch (IOException ioe) { out.println("&lt;PRE>"); out.println("I/O Exception."); ioe.printStackTrace; out.println("&lt;/PRE>"); } String dbDriver = "" + appProp.getProperty("dbDriver"); String dbUrl   = "" + appProp.getProperty("dbUrl"); String dbUid   = "" + appProp.getProperty("dbUid"); String dbUpw   = "" + appProp.getProperty("dbUpw"); String css     = "" + appProp.getProperty("css"); //out.println("&lt;PRE>"); //out.println(dbDriver); //out.println(dbUrl); //out.println(dbUid); //out.println(dbUpw); //out.println("&lt;/PRE>"); /* * Open the database */ try { Class.forName(dbDriver); conn = DriverManager.getConnection(dbUrl, dbUid, dbUpw); st = conn.createStatement; /* 	 * Get the request values */ 	if ( request.getParameter( "posted" ) == null ) { posted = "false"; } 	else { posted = request.getParameter( "posted" ); } 	if ( posted.equals("true") ) { /* 		 * 1. Check that the entries are not all blanked out (we don't want to erase any records...) * 2. Check that the entries are valid for their kind (a moot point, for this particular application...) * 3. Check that the significant (primary key) changes do not duplicate any other record... * 3. Make sure the record exists and, ideally, lock it, during the update * 4. Apply the change(s) * 5. Return to list */ 		/* 		 * Are the required fields populated? */ 		larderId = request.getParameter( "larder_id" ); editLarderDescription = request.getParameter( "editLarderDescription" ); editLarderPLU = request.getParameter( "editLarderPLU" ); editLarderUPC = request.getParameter( "editLarderUPC" ); editLarderQOH = request.getParameter( "editLarderQOH" ); editLarderROP = request.getParameter( "editLarderROP" ); editLarderPQ = request.getParameter( "editLarderPQ" ); editLarderPUP = request.getParameter( "editLarderPUP" ); editLarderPB = request.getParameter( "editLarderPB" ); editLarderPSL = request.getParameter( "editLarderPSL" ); populated = true; if ( (request.getParameter( "editLarderDescription" )).equals( "" ) ) { message = "Description is a required field. Please enter something."; errors++; } 		/* 		 * Does record "still" exist? */ 		recordExists = true; if ( populated ) { sql = "SELECT COUNT(*) AS records " + "FROM " + "larder" + " " + // It may make sense to use a variable for table_name at some point... "WHERE id = " + request.getParameter( "larder_id" ) ; //			out.print("sql = " + sql + " \n"); rs = st.executeQuery(sql); if (rs.next) { records = Integer.parseInt( rs.getString("records") ); if ( records &lt; 1 ) { recordExists = false; message = "Record does not exist. You may have removed by some other means or another page."; errors++; } 			} 		} 		/* 		 * Would the change cause an "exact" duplicate? */ 		duplicate = false; if ( populated ) { sql = "SELECT COUNT(*) AS records " + "FROM " + "larder" + " " + // It may make sense to use a variable here at some point... "WHERE description = '" + request.getParameter( "editLarderDescription" ) + "' " + "AND id != " + request.getParameter( "larder_id" ) ; //			out.print("sql = " + sql + " \n"); rs = st.executeQuery(sql); if (rs.next) { records = Integer.parseInt( rs.getString("records") ); if ( records > 0 ) { duplicate = true; message = "Record already exists. Duplicate records not allowed. Please check your entries."; errors++; } 			} 		} 		/* 		 * If no error, save data, clear fields, display form */ 		/* 		 * Save data... */ 		if ( errors == 0 ) { // This should be a call to a stored procedure... sql = "UPDATE larder SET " + "description = '" + request.getParameter( "editLarderDescription" ) + "', " + "plu = '" + request.getParameter( "editLarderPLU" ) + "', " + "upc = '" + request.getParameter( "editLarderUPC" ) + "', " + "qoh = " + request.getParameter( "editLarderQOH" ) + ", " + "rop = " + request.getParameter( "editLarderROP" ) + ", " + "pq = " + request.getParameter( "editLarderPQ" ) + ", " + "pup = " + request.getParameter( "editLarderPUP" ) + ", " + "pb = '" + request.getParameter( "editLarderPB" ) + "', " + "psl = '" + request.getParameter( "editLarderPSL" ) + "' " + "WHERE id = '" + request.getParameter( "larder_id" ) + "' "; //			out.print("sql = " + sql + " \n"); st.execute(sql); /* 			 * Return to list.... */ %> &lt;HTML> &lt;HEAD>&lt;TITLE>&lt;/TITLE>&lt;/HEAD> &lt;BODY onload="document.form1.submit"> &lt;div> &lt;form name="form1" method="POST" action="LarderShoppingList.jsp?&lt;%=System.currentTimeMillis%>"> &lt;input type="image" src="images/dots/clear.gif" alt="submit" value="Continue"> &lt;/form> &lt;/div> &lt;% updated = true; } 	} 	else { // This is the first time here, so get the values from the database table /* 		 * Note: Must work out automating getting (or just editing) only the * fields that are relevant, e.g. the timestamps are generally not that * relevant to data entry persons */ 		sql = "SELECT " + "id AS larder_id, " + "description AS larder_description, " + "plu AS larder_plu, " + "upc AS larder_upc, " + "qoh AS larder_qoh, " + "rop AS larder_rop, " + "pq AS larder_pq, " + "pup AS larder_pup, " + "pb AS larder_pb, " + "psl AS larder_psl " + "FROM larder " + "WHERE id = " + request.getParameter( "id" ) + " "; //		out.print("sql = " + sql + " \n"); rs = st.executeQuery(sql); if (rs.next) { larderId = rs.getString("larder_id"); editLarderDescription = rs.getString("larder_description"); editLarderPLU = rs.getString("larder_plu"); editLarderUPC = rs.getString("larder_upc"); editLarderQOH = rs.getString("larder_qoh"); editLarderROP = rs.getString("larder_rop"); editLarderPQ = rs.getString("larder_pq"); editLarderPUP = rs.getString("larder_pup"); editLarderPB = rs.getString("larder_pb"); editLarderPSL = rs.getString("larder_psl"); } 	} 	if ( updated == false ) { %> &lt;!DOCTYPE html> &lt;HTML> &lt;HEAD> &lt;link rel="stylesheet" type="text/css" href="&lt;%=css%>"> &lt;TITLE>&lt;%=title%>&lt;/TITLE> &lt;SCRIPT LANGUAGE="JavaScript" SRC="angular.js">&lt;/SCRIPT> &lt;SCRIPT LANGUAGE="JavaScript" SRC="jquery.js">&lt;/SCRIPT> &lt;/HEAD> &lt;BODY> &lt;H1>&lt;%=title%>&lt;/H1> &lt;br>&lt;br>&lt;br>&lt;br> &lt;div class="centered"> &lt;nobr> &lt;form action="LarderShoppingList.jsp" name=f> &lt;input name=btnG type=submit value=" Return to Larder Shopping List "> &lt;/form> &lt;/nobr> &lt;/div> &lt;br> &lt;p class="centered">&lt;%=message%>&lt;/p> &lt;div class="centered"> &lt;FORM NAME="form1" METHOD="POST"> &lt;!--Get time and pass as hidden variable in query_string to force browser to get page from server instead of cache... --> &lt;% dt = System.currentTimeMillis; %> &lt;input type="hidden" name="dt&lt;%= dt %>" value="&lt;%= dt %>"> &lt;input type="hidden" name="posted" value="true"> &lt;input type="hidden" name="tableName" value="larder"> &lt;input type="hidden" name="larder_id" value="&lt;%=larderId%>"> &lt;table border="1"> &lt;TBODY> &lt;tr align="left"> &lt;td>&lt;b>Item description:&lt;/B>&lt;/td>&lt;td>&lt;input type="text" name="editLarderDescription" size="127" value="&lt;%= editLarderDescription%>">&lt;/td> &lt;/tr> &lt;tr align="left"> &lt;td>&lt;b>PLU:&lt;/B>&lt;/td>&lt;td>&lt;input type="text" name="editLarderPLU" size="31" value="&lt;%= editLarderPLU%>">&lt;/td> &lt;/tr> &lt;tr align="left"> &lt;td>&lt;b>UPC:&lt;/B>&lt;/td>&lt;td>&lt;input type="text" name="editLarderUPC" size="63" value="&lt;%= editLarderUPC%>">&lt;/td> &lt;/tr> &lt;tr align="left"> &lt;td>&lt;b>Quantity on hand:&lt;/B>&lt;/td>&lt;td>&lt;input type="text" name="editLarderQOH" size="31" value="&lt;%= editLarderQOH%>">&lt;/td> &lt;/tr> &lt;tr align="left"> &lt;td>&lt;b>Restock point:&lt;/B>&lt;/td>&lt;td>&lt;input type="text" name="editLarderROP" size="31" value="&lt;%= editLarderROP%>">&lt;/td> &lt;/tr> &lt;tr align="left"> &lt;td>&lt;b>Restock quantity:&lt;/B>&lt;/td>&lt;td>&lt;input type="text" name="editLarderPQ" size="31" value="&lt;%= editLarderPQ%>">&lt;/td> &lt;/tr> &lt;tr align="left"> &lt;td>&lt;b>Purchase unit price:&lt;/B>&lt;/td>&lt;td>&lt;input type="text" name="editLarderPUP" size="31" value="&lt;%= editLarderPUP%>">&lt;/td> &lt;/tr> &lt;tr align="left"> &lt;td>&lt;b>Preferred brand(s):&lt;/B>&lt;/td>&lt;td>&lt;input type="text" name="editLarderPB" size="127" value="&lt;%= editLarderPB%>">&lt;/td> &lt;/tr> &lt;tr align="left"> &lt;td>&lt;b>Preferred store(s):&lt;/B>&lt;/td>&lt;td>&lt;input type="text" name="editLarderPSL" size="127" value="&lt;%= editLarderPSL%>">&lt;/td> &lt;/tr> &lt;tr align="left"> &lt;td> &lt;/td>&lt;td> &lt;div > &lt;input type="submit" name="submit" value="Submit"> &lt;input type="reset" name="clear" value="Reset"> &lt;/div> &lt;/td> &lt;/tr> &lt;/table> &lt;br>&lt;br> &lt;/form> &lt;/div> &lt;% } } // end try catch ( SQLException ex ) { out.println ( "&lt;pre>" ); out.println ( "---" ); out.println ( "--                      FATAL ERROR IN SQL" ); out.println ( "---" ); out.println ( "-- " ); while ( ex != null ) { out.println ("-- SQLState: " + ex.getSQLState ); out.println ("-- Message: " + ex.getMessage ); out.println ("-- Vendor:  " + ex.getErrorCode ); ex = ex.getNextException ; out.println ( "-- " ); } 	out.println ( "---" ); } // end catch catch ( java.lang.Exception ex ) { /* 	 * Got some other type of exception. Dump it. */ 	out.println ( "&lt;pre>" ); out.println ( "---" ); ex.printStackTrace ; out.println("exception: " + ex.getMessage ); } // end catch finally { // No action for now } // end finally %> &lt;/body> &lt;/html>

This concludes my presentation of "Larder Management", for now. I may add bells and whistles, maybe design or efficiency enhancements, but it is, essentially, done. Enjoy!