1  import java.sql.Connection;
  2  import java.sql.PreparedStatement;
  3  import java.sql.ResultSet;
  4  import java.sql.SQLException;
  5  import java.sql.Statement;
  6  import java.io.IOException;
  7  import java.io.File;
  8  import java.util.Scanner;
  9  
 10  /**
 11     Enters an invoice into the database.
 12     Be sure to add Customer.sql, Product.sql, Invoice.sql, and LineItem.sql
 13     to the database before running this program.
 14  */
 15  public class InvoiceEntry
 16  {
 17     public static void main(String args[])         
 18     {   
 19        if (args.length == 0)
 20        {   
 21           System.out.println(
 22                 "Usage: java -classpath driver_class_path"
 23                 + File.pathSeparator 
 24                 + ". InvoiceEntry propertiesFile");
 25           return;
 26        }
 27  
 28        try 
 29        {
 30           SimpleDataSource.init(args[0]);
 31           Connection conn = SimpleDataSource.getConnection();
 32           Scanner in = new Scanner(System.in);
 33  
 34           addInvoice(in, conn);
 35        }
 36        catch (SQLException ex)
 37        {
 38           System.out.println("Database error");
 39           ex.printStackTrace();
 40        }
 41        catch (ClassNotFoundException ex)
 42        {
 43           System.out.println("Error loading database driver");
 44           ex.printStackTrace();
 45        }
 46        catch (IOException ex)
 47        {
 48           System.out.println("Error loading database properties");
 49           ex.printStackTrace();
 50        }
 51     }
 52  
 53     public static void addInvoice(Scanner in, Connection conn)
 54        throws SQLException
 55     {      
 56        try
 57        {
 58           int customerNumber = newCustomer(conn, in);
 59  
 60           int id = getNewId(conn, "Invoice");
 61           PreparedStatement stat = conn.prepareStatement(
 62              "INSERT INTO Invoice VALUES (?, ?, 0)");
 63           stat.setInt(1, id);
 64           stat.setInt(2, customerNumber);
 65           stat.executeUpdate();
 66           stat.close();
 67  
 68           boolean done = false;
 69           while (!done)
 70           {
 71              String productCode = nextLine(in, "Product code (D=Done, L=List)");
 72              if (productCode.equals("D")) { done = true; }
 73              else if (productCode.equals("L")) { listProducts(conn); }               
 74              else if (findProduct(conn, productCode))
 75              {
 76                 int quantity = nextInt(in, "Quantity");
 77                 addLineItem(conn, id, productCode, quantity);
 78              }
 79              else { System.out.println("Invalid product code."); }
 80           }
 81           showInvoice(conn, id);
 82        }
 83        finally
 84        {
 85           conn.close();
 86        }      
 87     }      
 88  
 89     /**
 90        Prompts the user for the customer information and creates a new customer.
 91        @param conn the database connection
 92        @param in the scanner
 93        @return the ID of the new customer
 94     */
 95     private static int newCustomer(Connection conn, Scanner in)
 96        throws SQLException
 97     {
 98        String name = nextLine(in, "Name");
 99        String address = nextLine(in, "Street address");
100        String city = nextLine(in, "City");
101        String state = nextLine(in, "State");
102        String zip = nextLine(in, "Zip");
103        int id = getNewId(conn, "Customer");
104        PreparedStatement stat = conn.prepareStatement(
105           "INSERT INTO Customer VALUES (?, ?, ?, ?, ?, ?)");
106        stat.setInt(1, id);
107        stat.setString(2, name);
108        stat.setString(3, address);
109        stat.setString(4, city);
110        stat.setString(5, state);
111        stat.setString(6, zip);
112        stat.executeUpdate();
113        stat.close();
114        return id;
115     }
116  
117     /**
118        Finds a product in the database.
119        @param conn the database connection
120        @param code the product code to search
121        @return true if there is a product with the given code
122     */
123     private static boolean findProduct(Connection conn, String code)
124        throws SQLException
125     {
126        PreparedStatement stat = conn.prepareStatement(
127           "SELECT * FROM Product WHERE Product_Code = ?");
128        stat.setString(1, code);
129        ResultSet result = stat.executeQuery();
130        boolean found = result.next();
131        stat.close();
132        return found;
133     }
134  
135     /**
136        Adds a line item to the database
137        @param conn the database connection
138        @param id the invoice ID
139        @param code the product code
140        @param quantity the quantity to order
141     */
142     private static void addLineItem(Connection conn, int id,
143        String code, int quantity) throws SQLException
144     {      
145        PreparedStatement stat = conn.prepareStatement(
146           "INSERT INTO LineItem VALUES (?, ?, ?)");
147        stat.setInt(1, id);
148        stat.setString(2, code);
149        stat.setInt(3, quantity);
150        stat.executeUpdate();
151        stat.close();
152     }
153  
154     /**
155        Lists all products in the database.
156        @param conn the database connection
157     */
158     private static void listProducts(Connection conn)
159        throws SQLException
160     {
161        Statement stat = conn.createStatement();
162        ResultSet result = stat.executeQuery(
163           "SELECT Product_Code, Description FROM Product");
164        while (result.next())
165        {
166           String code = result.getString(1);
167           String description = result.getString(2);
168           System.out.println(code + " " + description);
169        }
170        stat.close();
171     }
172     
173     /**
174        Gets a new ID for a table. This method should be called from
175        inside a transaction that also creates the new row with this ID.
176        The ID field should have name table_Number and type INTEGER.
177        @param table the table name
178        @return a new ID that has not yet been used.
179     */
180     private static int getNewId(Connection conn, String table)
181        throws SQLException
182     {
183        Statement stat = conn.createStatement();
184        ResultSet result = stat.executeQuery(
185           "SELECT max(" + table + "_Number) FROM " + table);
186        result.next();
187        int max = result.getInt(1);
188        stat.close();
189        return max + 1;
190     }
191  
192     /**
193        Shows an invoice.
194        @param conn the database connection
195        @param id the invoice ID
196     */
197     private static void showInvoice(Connection conn, int id)
198        throws SQLException
199     {
200        PreparedStatement stat = conn.prepareStatement(
201           "SELECT Customer.Name, Customer.Address, "
202           + "Customer.City, Customer.State, Customer.Zip "
203           + "FROM Customer, Invoice "
204           + "WHERE Customer.Customer_Number = Invoice.Customer_Number "
205           + "AND Invoice.Invoice_Number = ?");
206        stat.setInt(1, id);
207        ResultSet result = stat.executeQuery();
208        result.next();
209        System.out.println(result.getString(1));
210        System.out.println(result.getString(2));        
211        System.out.println(result.getString(3).trim() + ", "
212           + result.getString(4) + " " + result.getString(5));
213        stat.close();
214                   
215        stat = conn.prepareStatement(
216           "SELECT Product.Product_Code, Product.Description, LineItem.Quantity "
217           + "FROM Product, LineItem "
218           + "WHERE Product.Product_Code = LineItem.Product_Code "
219           + "AND LineItem.Invoice_Number = ?");
220        stat.setInt(1, id);
221  
222        result = stat.executeQuery();
223        while (result.next())
224        {  
225           String code = result.getString(1);
226           String description = result.getString(2).trim();
227           int qty = result.getInt(3);
228  
229           System.out.println(qty + " x " + code + " " + description);
230        }
231        stat.close();         
232     }
233  
234     /**
235        Prompts the user and reads a line from a scanner.
236        @param in the scanner
237        @param prompt the prompt
238        @return the string that the user entered
239     */
240     private static String nextLine(Scanner in, String prompt)
241     {
242        System.out.print(prompt + ": ");
243        return in.nextLine();
244     }
245  
246     /**
247        Prompts the user and reads an integer from a scanner.
248        @param in the scanner
249        @param prompt the prompt
250        @return the integer that the user entered
251     */
252     private static int nextInt(Scanner in, String prompt)
253     {
254        System.out.print(prompt + ": ");
255        int result = in.nextInt();
256        in.nextLine(); // Consume newline
257        return result;
258     }
259  }