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 }