· 8 years ago · Dec 20, 2017, 06:40 PM
1import java.sql.*;
2/**
3This program shows how to create a new database *
4using Java DB. *
5*/
6
7public class BuildEntertainmentDB
8{
9 public static void main(String[] args)throws Exception
10 {
11 final String DB_URL = "jdbc:derby:EntertainmentDB;create=true";
12 try
13 {
14 // Create a connection to the database.
15 Connection conn = DriverManager.getConnection(DB_URL);
16
17 // Create a Statement object.
18 Statement stmt = conn.createStatement();
19
20 // Create the Dvd table.
21 System.out.println("Creating the Dvd table...");
22 stmt.execute("CREATE TABLE Dvd (" +
23 "Title CHAR(25), " +
24 "Minutes INTEGER, " +
25 "Price DOUBLE)");
26
27 // Close the resources.
28 stmt.close();
29 conn.close();
30 System.out.println("Done");
31 }catch(Exception ex)
32 System.out.println("ERROR: " + ex.getMessage());
33 }
34}
35
36
37
38
39
40
41
42import java.sql.*;
43
44/**
45The CoffeeDBManager class performs operations on
46the CoffeeDB database.
47*/
48public class CoffeeDBManager
49{
50 // Constant for database URL.
51 public final String DB_URL = "jdbc:derby:CoffeeDB";
52 // Field for the database connection
53 private Connection conn;
54 /**
55 Constructor
56 */
57 public CoffeeDBManager() throws SQLException
58 {
59 // Create a connection to the database.
60 conn = DriverManager.getConnection(DB_URL);
61 }
62 /**
63 The getCoffeeNames method returns an array
64 of Strings containing all the coffee names.
65 */
66
67 public String[] getCoffeeNames()throws SQLException
68 {
69 // Create a Statement object for the query.
70 Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
71 // Execute the query.
72 ResultSet resultSet = stmt.executeQuery("SELECT Description FROM Coffee");
73 // Get the number of rows
74 resultSet.last(); // Move to last row
75 int numRows = resultSet.getRow(); // Get row number
76 resultSet.first(); // Move to first row
77 // Create an array for the coffee names.
78 String[] listData = new String[numRows];
79 // Populate the array with coffee names.
80 for (int index = 0; index < numRows; index++)
81 {
82 // Store the coffee name in the array.
83 listData[index] = resultSet.getString(1);
84 // Go to the next row in the result set.
85 resultSet.next();
86 }
87 // Close the connection and statement objects.
88 conn.close();
89 stmt.close();
90 // Return the listData array.
91 return listData;
92 }
93
94 /**
95 The getProdNum method returns a specific
96 coffee's product number.
97 @param coffeeName The specified coffee.
98 */
99
100 public String getProdNum(String coffeeName)throws SQLException
101 {
102 String prodNum = ""; // Product number
103 // Create a connection to the database.
104 conn = DriverManager.getConnection(DB_URL);
105 // Create a Statement object for the query.
106 Statement stmt = conn.createStatement();
107 // Execute the query.
108 ResultSet resultSet = stmt.executeQuery(
109 "SELECT ProdNum " +
110 "FROM Coffee " +
111 "WHERE Description = '" +
112 coffeeName + "'");
113 // If the result set has a row, go to it
114 // and retrieve the product number.
115 if (resultSet.next())
116 prodNum = resultSet.getString(1);
117 // Close the Connection and Statement objects.
118 conn.close();
119 stmt.close();
120 // Return the product number.
121 return prodNum;
122 }
123
124 /**
125 The getCoffeePrice method returns the price
126 of a coffee.
127 @param prodNum The specified product number.
128 */
129
130 public double getCoffeePrice(String prodNum)throws SQLException
131 {
132 double price = 0.0; // Coffee price
133
134 // Create a connection to the database.
135 conn = DriverManager.getConnection(DB_URL);
136 // Create a Statement object for the query.
137 Statement stmt = conn.createStatement();
138 // Execute the query.
139 ResultSet resultSet = stmt.executeQuery(
140 "SELECT Price " +
141 "FROM Coffee " +
142 "WHERE ProdNum = '" +
143 prodNum + "'");
144 // If the result set has a row, go to it
145 // and retrieve the price.
146 if (resultSet.next())
147 price = resultSet.getDouble(1);
148 // Close the connection and statement objects.
149 conn.close();
150 stmt.close();
151 // Return the price.
152 return price;
153 }
154
155 /**
156 The getCustomerNames method returns an array
157 of Strings containing all the customer names.
158 */
159
160 public String[] getCustomerNames() throws SQLException
161 {
162 // Create a connection to the database.
163 conn = DriverManager.getConnection(DB_URL);
164 // Create a Statement object for the query.
165 Statement stmt =
166 conn.createStatement(
167 ResultSet.TYPE_SCROLL_SENSITIVE,
168 ResultSet.CONCUR_READ_ONLY);
169 // Execute the query.
170 ResultSet resultSet =
171 stmt.executeQuery("SELECT Name FROM Customer");
172 // Get the number of rows
173 resultSet.last(); // Move last row
174 int numRows = resultSet.getRow(); // Get row number
175 resultSet.first(); // Move to first row
176 // Create an array for the customer names.
177 String[] listData = new String[numRows];
178 // Populate the array with customer names.
179 for (int index = 0; index < numRows; index++)
180 {
181 // Store the customer name in the array.
182 listData[index] = resultSet.getString(1);
183 // Go to the next row in the result set.
184 resultSet.next();
185 }
186 // Close the connection and statement objects.
187 conn.close();
188 stmt.close();
189 // Return the listData array.
190 return listData;
191 }
192
193 /**
194 The getCustomerNum method returns a specific
195 customer's number.
196 @param name The specified customer's name.
197 */
198
199 public String getCustomerNum(String name)throws SQLException
200 {
201 String customerNumber = "";
202 // Create a connection to the database.
203 conn = DriverManager.getConnection(DB_URL);
204 // Create a Statement object for the query.
205 Statement stmt = conn.createStatement();
206 // Execute the query.
207 ResultSet resultSet =
208 stmt.executeQuery("SELECT CustomerNumber " +
209 "FROM Customer " +
210 "WHERE Name = '" + name + "'");
211 if (resultSet.next())
212 customerNumber = resultSet.getString(1);
213 // Close the connection and statement objects.
214 conn.close();
215 stmt.close();
216 // Return the customer number.
217 return customerNumber;
218 }
219
220/**
221 The submitOrder method submits an order to
222 the UnpaidOrder table in the CoffeeDB database.
223 @param custNum The customer number.
224 @param prodNum The product number.
225 @param quantity The quantity ordered.
226 @param price The price.
227 @param orderDate The order date.
228*/
229
230 public void submitOrder(String custNum, String prodNum,int quantity, double price,String orderDate) throws SQLException
231 {
232 // Calculate the cost of the order.
233 double cost = quantity * price;
234 // Create a connection to the database.
235 conn = DriverManager.getConnection(DB_URL);
236 // Create a Statement object for the query.
237 Statement stmt = conn.createStatement();
238 // Execute the query.
239 stmt.executeUpdate("INSERT INTO UnpaidOrder VALUES('" +
240 custNum + "', '" +
241 prodNum + "', '" + orderDate + "', " +
242 quantity + ", " + cost + ")");
243 // Close the connection and statement objects.
244 conn.close();
245 stmt.close();
246 }
247}
248
249
250
251
252import java.sql.*;
253
254/**
255This class executes queries on the CoffeeDB database
256and provides the results in arrays.
257*/
258public class CoffeeDBQuery
259{
260 // Database URL Constant
261 public final String DB_URL = "jdbc:derby:CoffeeDB";
262 private Connection conn; // Database connection
263 private String[][] tableData; // Table data
264 private String[] colNames; // Column names
265 /**
266 Constructor
267 */
268 public CoffeeDBQuery(String query)
269 {
270 // Get a connection to the database.
271 getDatabaseConnection();
272 try
273 {
274 // Create a Statement object for the query.
275 Statement stmt =
276 conn.createStatement(
277 ResultSet.TYPE_SCROLL_INSENSITIVE,
278 ResultSet.CONCUR_READ_ONLY);
279 // Execute the query.
280 ResultSet resultSet =
281 stmt.executeQuery(query);
282 // Get the number of rows.
283 resultSet.last(); // Move to last row
284 int numRows = resultSet.getRow(); // Get row number
285 resultSet.first(); // Move to first row
286 // Get a metadata object for the result set.
287 ResultSetMetaData meta = resultSet.getMetaData();
288 // Create an array of Strings for the column names.
289 colNames = new String[meta.getColumnCount()];
290 // Store the column names in the colNames array.
291 for (int i = 0; i < meta.getColumnCount(); i++)
292 {
293 // Get a column name.
294 colNames[i] = meta.getColumnLabel(i+1);
295 }
296 // Create a 2D String array for the table data.
297 tableData = new String[numRows][meta.getColumnCount()];
298
299 // Store the columns in the tableData array.
300 for (int row = 0; row < numRows; row++)
301 {
302 for (int col = 0; col < meta.getColumnCount(); col++)
303 {
304 tableData[row][col] = resultSet.getString(col + 1);
305 }
306 // Go to the next row in the ResultSet.
307 resultSet.next();
308 }
309
310 // Close the statement and connection objects.
311 stmt.close();
312 conn.close();
313 }
314 catch (SQLException ex)
315 {
316 ex.printStackTrace();
317 }
318 }
319
320 /**
321 The getDatabaseConnection method loads the JDBC
322 and gets a connection to the database.
323 */
324 private void getDatabaseConnection()
325 {
326 try
327 {
328 // Create a connection to the database.
329 conn = DriverManager.getConnection(DB_URL);
330 }
331 catch (Exception ex)
332 {
333 ex.printStackTrace();
334 System.exit(0);
335 }
336 }
337
338 /**
339 The getColumnNames method returns the column names.
340 */
341 public String[] getColumnNames()
342 {
343 return colNames;
344 }
345
346 /**
347 The getTableData method returns the table data.
348 */
349 public String[][] getTableData()
350 {
351 return tableData;
352 }
353}
354
355
356
357
358
359
360
361import java.sql.*;
362import java.util.Scaner;
363
364/**
365This program lets the user delete a coffee
366from the CoffeeDB database's Coffee table.
367*/
368public class CoffeeDeleter
369{
370 public static void main(String[] args)
371 {
372 String prodNum; // To hold the product number
373 String sure; // To make sure the user wants to delete
374 // Create a named constant for the URL.
375 // NOTE: This value is specific for Java DB.
376 final String DB_URL = "jdbc:derby:CoffeeDB";
377 // Create a Scanner object for keyboard input.
378 Scanner keyboard = new Scanner(System.in);
379 try
380 {
381 // Create a connection to the database.
382 Connection conn = DriverManager.getConnection(DB_URL);
383 // Create a Statement object.
384 Statement stmt = conn.createStatement();
385 // Get the product number for the desired coffee.
386 System.out.print("Enter the product number: ");
387 prodNum = keyboard.nextLine();
388 // Display the coffee's current data.
389 if (findAndDisplayProduct(stmt, prodNum))
390 {
391 // Make sure the user wants to delete this product.
392 System.out.print("Are you sure you want to delete " +
393 "this item? (y/n): ");
394 sure = keyboard.nextLine();
395 if (Character.toUpperCase(sure.charAt(0)) == 'Y')
396 {
397 // Delete the specified coffee.
398 deleteCoffee(stmt, prodNum);
399 }
400 }
401
402 else
403 {
404 System.out.println("The item was not deleted.");
405 }
406
407 else
408 {
409 // The specified product number was not found.
410 System.out.println("That product was not found.");
411 }
412 // Close the connection.
413 conn.close();
414 }
415
416 catch(Exception ex)
417 {
418 System.out.println("ERROR: " + ex.getMessage());
419 }
420 }
421
422 /**
423 The findAndDisplayProduct method finds a specified coffee's
424 data and displays it.
425 @param stmt A Statement object for the database.
426 @param prodNum The product number for the desired coffee.
427 @return true or false to indicate whether the product was found.
428 */
429
430 public static boolean findAndDisplayProduct(Statement stmt,String prodNum)throws SQLException
431 {
432 boolean productFound; // Flag
433 // Create a SELECT statement to get the specified
434 // row from the Coffee table.
435 String sqlStatement =
436 "SELECT * FROM Coffee WHERE ProdNum = '" +
437 prodNum + "'";
438 // Send the SELECT statement to the DBMS.
439 ResultSet result = stmt.executeQuery(sqlStatement);
440 // Display the contents of the result set.
441 if (result.next())
442 {
443 // Display the product.
444 System.out.println("Description: " +
445 result.getString("Description"));
446 System.out.println("Product Number: " +
447 result.getString("ProdNum"));
448 System.out.println("Price: $" +
449 result.getDouble("Price"));
450 // Set the flag to indicate the product was found.
451 productFound = true;
452 }
453 else
454 {
455 // Indicate the product was not found.
456 productFound = false;
457 }
458 return productFound;
459 }
460
461 /**
462 The deleteCoffee method deletes a specified coffee.
463 @param stmt A Statement object for the database.
464 @param prodNum The product number for the desired coffee.
465 */
466
467 public static void deleteCoffee(Statement stmt, String prodNum)throws SQLException
468 {
469 // Create a DELETE statement to delete the
470 // specified product number.
471 String sqlStatement = "DELETE FROM Coffee " +
472 "WHERE ProdNum = '" + prodNum + "'";
473 // Send the DELETE statement to the DBMS.
474 int rows = stmt.executeUpdate(sqlStatement);
475 // Display the results.
476 System.out.println(rows + " row(s) deleted.");
477 }
478}
479
480
481
482
483
484
485
486
487
488
489import java.util.Scanner;
490import java.sql.*;
491
492/**
493This program lets the user insert a row into the
494CoffeeDB database's Coffee table.
495*/
496
497public class CoffeeInserter
498{
499 public static void main(String[] args)
500 {
501 String description; // To hold the coffee description
502 String prodNum; // To hold the product number
503 double price; // To hold the price
504
505 // Create a named constant for the URL.
506 // NOTE: This value is specific for Java DB.
507 final String DB_URL = "jdbc:derby:CoffeeDB";
508
509 // Create a Scanner object for keyboard input.
510 Scanner keyboard = new Scanner(System.in);
511
512 try
513 {
514 // Create a connection to the database.
515 Connection conn = DriverManager.getConnection(DB_URL);
516
517 // Get the data for the new coffee.
518 System.out.print("Enter the coffee description: ");
519 description = keyboard.nextLine();
520 System.out.print("Enter the product number: ");
521 prodNum = keyboard.nextLine();
522 System.out.print("Enter the price: ");
523 price = keyboard.nextDouble();
524
525 // Create a Statement object.
526 Statement stmt = conn.createStatement();
527
528 // Create a string with an INSERT statement.
529 String sqlStatement = "INSERT INTO Coffee " +
530 "(ProdNum, Price, Description) " +
531 "VALUES ('" +
532 prodNum + "', " +
533 price + ", '" +
534 description + "')";
535
536 // Send the statement to the DBMS.
537 int rows = stmt.executeUpdate(sqlStatement);
538
539 // Display the results.
540 System.out.println(rows + " row(s) added to the table.");
541
542 // Close the connection.
543 conn.close();
544 }
545 catch(Exception ex)
546 {
547 System.out.println("ERROR: " + ex.getMessage());
548 }
549 }
550}
551
552
553
554
555
556import java.sql.*;
557
558/**
559This program demonstrates some of the SQL math functions.
560*/
561public class CoffeeMath
562{
563 public static void main(String[] args)
564 {
565 // Variables to hold the lowest, highest, and
566 // average prices of coffee.
567 double lowest = 0.0,
568 highest = 0.0,
569 average = 0.0;
570 // Create a named constant for the URL.
571 // NOTE: This value is specific for Java DB.
572 final String DB_URL = "jdbc:derby:CoffeeDB";
573 try
574 {
575 // Create a connection to the database.
576 Connection conn = DriverManager.getConnection(DB_URL);
577 // Create a Statement object.
578 Statement stmt = conn.createStatement();
579 // Create SELECT statements to get the lowest, highest,
580 // and average prices from the Coffee table.
581 String minStatement = "SELECT MIN(Price) FROM Coffee";
582 String maxStatement = "SELECT MAX(Price) FROM Coffee";
583 String avgStatement = "SELECT AVG(Price) FROM Coffee";
584 // Get the lowest price.
585 ResultSet minResult = stmt.executeQuery(minStatement);
586 if (minResult.next())
587 lowest = minResult.getDouble(1);
588 // Get the highest price.
589 ResultSet maxResult = stmt.executeQuery(maxStatement);
590 if (maxResult.next())
591 highest = maxResult.getDouble(1);
592 // Get the average price.
593 ResultSet avgResult = stmt.executeQuery(avgStatement);
594 if (avgResult.next())
595 average = avgResult.getDouble(1);
596 // Display the results.
597 System.out.printf("Lowest price: $%.2f\n", lowest);
598 System.out.printf("Highest price: $%.2f\n", highest);
599 System.out.printf("Average price: $%.2f\n", average);
600 // Close the connection.
601 conn.close();
602 }
603 catch(Exception ex)
604 {
605 System.out.println("ERROR: " + ex.getMessage());
606 }
607 }
608}
609
610
611
612
613
614import java.util.Scanner;
615import java.sql.*;
616/**
617This program lets the user search for coffees
618priced at a minimum value.
619*/
620public class CoffeeMinPrice
621{
622 public static void main(String[] args)
623 {
624 double minPrice; // To hold the minimum price
625 int coffeeCount = 0; // To count coffees that qualify
626 // Create a named constant for the URL.
627 // NOTE: This value is specific for Java DB.
628 final String DB_URL = "jdbc:derby:CoffeeDB";
629 // Create a Scanner object for keyboard input.
630 Scanner keyboard = new Scanner(System.in);
631 // Get the minimum price from the user.
632 System.out.print("Enter the minimum price: ");
633 minPrice = keyboard.nextDouble();
634 try
635 {
636 // Create a connection to the database.
637 Connection conn = DriverManager.getConnection(DB_URL);
638 // Create a Statement object.
639 Statement stmt = conn.createStatement();
640 // Create a string containing a SELECT statement.
641 // Note that we are incorporating the user's input
642 // into the string.
643 String sqlStatement =
644 "SELECT * FROM Coffee WHERE Price >= " +
645 Double.toString(minPrice);
646 // Send the statement to the DBMS.
647 ResultSet result = stmt.executeQuery(sqlStatement);
648 // Display the contents of the result set.
649 // The result set will have three columns.
650 while (result.next())
651 {
652 // Display a row from the result set.
653 System.out.printf("%25s %10s %5.2f\n",
654 result.getString("Description"),
655 result.getString("ProdNum"),
656 result.getDouble("Price"));
657 // Increment the counter.
658 coffeeCount++;
659 }
660 // Display the number of qualifying coffees.
661 System.out.println(coffeeCount + " coffees found.");
662 // Close the connection.
663 conn.close();
664 }
665 catch(Exception ex)
666 {
667 System.out.println("ERROR: " + ex.getMessage());
668 }
669 }
670}
671
672
673
674
675
676
677
678
679
680import java.sql.*;
681import javax.swing.*;
682
683/**
684The CoffeePanel class is a custom JPanel that
685shows a list of coffees in a JList.
686*/
687public class CoffeePanel extends JPanel
688{
689 private final int NUM_ROWS = 5; // Number of rows to display
690 private JList coffeeList; // A list for coffee descriptions
691 String[] coffeeNames; // To hold coffee names
692
693 /**
694 Constructor
695 */
696 public CoffeePanel()
697 {
698 try
699 {
700 // Create a CoffeeDBManager object.
701 CoffeeDBManager dbManager = new CoffeeDBManager();
702
703 // Get a list of coffee names as a String array.
704 coffeeNames = dbManager.getCoffeeNames();
705
706 // Create a JList object to hold the coffee names.
707 coffeeList = new JList(coffeeNames);
708
709 // Set the number of visible rows.
710 coffeeList.setVisibleRowCount(NUM_ROWS);
711
712 // Put the JList object in a scroll pane.
713 JScrollPane scrollPane = new JScrollPane(coffeeList);
714
715 // Add the scroll pane to the panel.
716 add(scrollPane);
717
718 // Add a titled border to the panel.
719 setBorder(BorderFactory.createTitledBorder(
720 "Select a Coffee"));
721 }
722 catch (SQLException ex)
723 {
724 ex.printStackTrace();
725 System.exit(0);
726 }
727 }
728 /**
729 The getCoffee method returns the coffee
730 description selected by the user.
731 */
732
733 public String getCoffee()
734 {
735 // The JList class's getSelectedValue method returns
736 // an Object reference, so we will cast it to a String.
737 return (String) coffeeList.getSelectedValue();
738 }
739}
740
741
742
743
744
745import java.sql.*; // Needed for JDBC classes
746/**
747This program displays the coffee descriptions
748and their prices.
749*/
750public class ShowDescriptionsAndPrices
751{
752 public static void main(String[] args)
753 {
754 // Create a named constant for the URL.
755 // NOTE: This value is specific for Java DB.
756 final String DB_URL = "jdbc:derby:CoffeeDB";
757
758 try
759 {
760 // Create a connection to the database.
761 Connection conn = DriverManager.getConnection(DB_URL);
762
763 // Create a Statement object.
764 Statement stmt = conn.createStatement();
765
766 // Create a string with a SELECT statement.
767 String sqlStatement =
768 "SELECT Description, Price FROM Coffee";
769
770 // Send the statement to the DBMS.
771 ResultSet result = stmt.executeQuery(sqlStatement);
772
773 // Display the contents of the result set.
774 // The result set will have three columns.
775 while (result.next())
776 {
777 System.out.printf("%25s %.2f\n",
778 result.getString("Description"),
779 result.getDouble("Price"));
780 }
781
782 // Close the connection.
783 conn.close();
784 }
785 catch(Exception ex)
786 {
787 System.out.println("ERROR: " + ex.getMessage());
788 }
789 }
790}
791
792
793
794
795
796
797
798import java.sql.*;
799/**
800This program displays all of the columns in the
801Coffee table of the CoffeeDB database.
802*/
803public class ShowCoffeeData
804{
805 public static void main(String[] args)
806 {
807 // Create a named constant for the URL.
808 // NOTE: This value is specific for Java DB.
809 final String DB_URL = "jdbc:derby:CoffeeDB";
810
811 try
812 {
813 // Create a connection to the database.
814 Connection conn = DriverManager.getConnection(DB_URL);
815
816 // Create a Statement object.
817 Statement stmt = conn.createStatement();
818
819 // Create a string with a SELECT statement.
820 String sqlStatement = "SELECT * FROM Coffee";
821
822 // Send the statement to the DBMS.
823 ResultSet result = stmt.executeQuery(sqlStatement);
824
825 // Display the contents of the result set.
826 // The result set will have three columns.
827 while (result.next())
828 {
829 System.out.printf("%25s %10s %5.2f\n",
830 result.getString("Description"),
831 result.getString("ProdNum"),
832 result.getDouble("Price"));
833 }
834
835 // Close the connection.
836 conn.close();
837 }
838 catch(Exception ex)
839 {
840 System.out.println("ERROR: " + ex.getMessage());
841 }
842 }
843}
844
845
846
847
848
849
850
851
852import java.sql.*;
853
854/**
855
856 This program creates the CoffeeDB database.
857*/
858public class CreateCoffeeDB
859{
860 public static void main(String[] args)
861 {
862 // Create a named constant for the URL.
863 // NOTE: This value is specific for Java DB.
864 final String DB_URL = "jdbc:derby:CoffeeDB;create=true";
865
866 try
867 {
868 // Create a connection to the database.
869 Connection conn =
870 DriverManager.getConnection(DB_URL);
871
872 // If the DB already exists, drop the tables.
873 dropTables(conn);
874
875 // Build the Coffee table.
876 buildCoffeeTable(conn);
877
878 // Build the Customer table.
879 buildCustomerTable(conn);
880
881 // Build the UnpaidInvoice table.
882 buildUnpaidOrderTable(conn);
883 // Close the connection.
884 conn.close();
885 }
886 catch (Exception ex)
887 {
888 System.out.println("ERROR: " + ex.getMessage());
889 }
890 }
891
892 /**
893 * The dropTables method drops any existing
894 * in case the database already exists.
895 */
896 public static void dropTables(Connection conn)
897 {
898 System.out.println("Checking for existing tables.");
899
900 try
901 {
902 // Get a Statement object.
903 Statement stmt = conn.createStatement();;
904 try
905 {
906 // Drop the UnpaidOrder table.
907 stmt.execute("DROP TABLE Unpaidorder");
908 System.out.println("UnpaidOrder table dropped.");
909 }
910 catch(SQLException ex){}
911
912 try
913 {
914 // Drop the Customer table.
915 stmt.execute("DROP TABLE Customer");
916 System.out.println("Customer table dropped.");
917 }
918 catch(SQLException ex){}
919
920 try
921 {
922 // Drop the Coffee table.
923 stmt.execute("DROP TABLE Coffee");
924 System.out.println("Coffee table dropped.");
925 }
926 catch(SQLException ex){}
927 }
928 catch(SQLException ex)
929 {
930 System.out.println("ERROR: " + ex.getMessage());
931 ex.printStackTrace();
932 }
933 }
934
935 /**
936 * The buildCoffeeTable method creates the
937 * Coffee table and adds some rows to it.
938 */
939 public static void buildCoffeeTable(Connection conn)
940 {
941 try
942 {
943 // Get a Statement object.
944 Statement stmt = conn.createStatement();
945
946 // Create the table.
947 stmt.execute("CREATE TABLE Coffee (" +
948 "Description CHAR(25), " +
949 "ProdNum CHAR(10) NOT NULL PRIMARY KEY, " +
950 "Price DOUBLE " +
951 ")");
952
953 // Insert row #1.
954 stmt.execute("INSERT INTO Coffee VALUES ( " +
955 "'Bolivian Medium', " +
956 "'14-002', " +
957 "8.95 )");
958 // Insert row #2.
959 stmt.execute("INSERT INTO Coffee VALUES ( " +
960 "'Brazilian Dark', " +
961 "'15-001', " +
962 "7.95 )");
963 // Insert row #3.
964 stmt.execute("INSERT INTO Coffee VALUES ( " +
965 "'Brazilian Medium', " +
966 "'15-002', " +
967 "7.95 )");
968 // Insert row #4.
969 stmt.execute("INSERT INTO Coffee VALUES ( " +
970 "'Brazilian Decaf', " +
971 "'15-003', " +
972 "8.55 )" );
973 // Insert row #5.
974 stmt.execute("INSERT INTO Coffee VALUES ( " +
975 "'Central American Dark', " +
976 "'16-001', " +
977 "9.95 )");
978 // Insert row #6.
979 stmt.execute("INSERT INTO Coffee VALUES ( " +
980 "'Central American Medium', " +
981 "'16-002', " +
982 "9.95 )");
983 // Insert row #1.
984 stmt.execute("INSERT INTO Coffee VALUES ( " +
985 "'Sumatra Dark', " +
986 "'17-001', " +
987 "7.95 )");
988 // Insert row #7.
989 stmt.execute("INSERT INTO Coffee VALUES ( " +
990 "'Sumatra Decaf', " +
991 "'17-002', " +
992 "8.95 )");
993 // Insert row #8.
994 stmt.execute("INSERT INTO Coffee VALUES ( " +
995 "'Sumatra Medium', " +
996 "'17-003', " +
997 "7.95 )");
998 // Insert row #9.
999 stmt.execute("INSERT INTO Coffee VALUES ( " +
1000 "'Sumatra Organic Dark', " +
1001 "'17-004', " +
1002 "11.95 )");
1003 // Insert row #10.
1004 stmt.execute("INSERT INTO Coffee VALUES ( " +
1005 "'Kona Medium', " +
1006 "'18-001', " +
1007 "18.45 )");
1008 // Insert row #11.
1009 stmt.execute("INSERT INTO Coffee VALUES ( " +
1010 "'Kona Dark', " +
1011 "'18-002', " +
1012 "18.45 )");
1013 // Insert row #12.
1014 stmt.execute("INSERT INTO Coffee VALUES ( " +
1015 "'French Roast Dark', " +
1016 "'19-001', " +
1017 "9.65 )");
1018 // Insert row #13.
1019 stmt.execute("INSERT INTO Coffee VALUES ( " +
1020 "'Galapagos Medium', " +
1021 "'20-001', " +
1022 "6.85 )");
1023 // Insert row #14.
1024 stmt.execute("INSERT INTO Coffee VALUES ( " +
1025 "'Guatemalan Dark', " +
1026 "'21-001', " +
1027 "9.95 )");
1028 // Insert row #15.
1029 stmt.execute("INSERT INTO Coffee VALUES ( " +
1030 "'Guatemalan Decaf', " +
1031 "'21-002', " +
1032 "10.45 )");
1033 // Insert row #16.
1034 stmt.execute("INSERT INTO Coffee VALUES ( " +
1035 "'Guatemalan Medium', " +
1036 "'21-003', " +
1037 "9.95 )");
1038
1039 System.out.println("Coffee table created.");
1040 }
1041 catch (SQLException ex)
1042 {
1043 System.out.println("ERROR: " + ex.getMessage());
1044 }
1045 }
1046 /**
1047 * The buildCustomerTable method creates the
1048 * Customer table and adds some rows to it.
1049 */
1050 public static void buildCustomerTable(Connection conn)
1051 {
1052 try
1053 {
1054 // Get a Statement object.
1055 Statement stmt = conn.createStatement();
1056
1057 // Create the table.
1058 stmt.execute("CREATE TABLE Customer" +
1059 "( CustomerNumber CHAR(10) NOT NULL PRIMARY KEY, " +
1060 " Name CHAR(25)," +
1061 " Address CHAR(25)," +
1062 " City CHAR(12)," +
1063 " State CHAR(2)," +
1064 " Zip CHAR(5) )");
1065 // Add some rows to the new table.
1066 stmt.executeUpdate("INSERT INTO Customer VALUES" +
1067 "('101', 'Downtown Cafe', '17 N. Main Street'," +
1068 " 'Asheville', 'NC', '55515')");
1069
1070 stmt.executeUpdate("INSERT INTO Customer VALUES" +
1071 "('102', 'Main Street Grocery'," +
1072 " '110 E. Main Street'," +
1073 " 'Canton', 'NC', '55555')");
1074 stmt.executeUpdate("INSERT INTO Customer VALUES" +
1075 "('103', 'The Coffee Place', '101 Center Plaza'," +
1076 " 'Waynesville', 'NC', '55516')");
1077
1078 System.out.println("Customer table created.");
1079 }
1080 catch (SQLException ex)
1081 {
1082 System.out.println("ERROR: " + ex.getMessage());
1083 }
1084 }
1085 /**
1086 * The buildUnpaidOrderTable method creates
1087 * the UnpaidOrder table.
1088 */
1089 public static void buildUnpaidOrderTable(Connection conn)
1090 {
1091 try
1092 {
1093 // Get a Statement object.
1094 Statement stmt = conn.createStatement();
1095
1096 // Create the table.
1097 stmt.execute("CREATE TABLE UnpaidOrder " +
1098 "( CustomerNumber CHAR(10) NOT NULL REFERENCES Customer(CustomerNumber), "+
1099 " ProdNum CHAR(10) NOT NULL REFERENCES Coffee(ProdNum),"+
1100 " OrderDate CHAR(10),"+
1101 " Quantity DOUBLE,"+
1102 " Cost DOUBLE )");
1103
1104 System.out.println("UnpaidOrder table created.")
1105 }
1106 catch (SQLException ex)
1107 {
1108 System.out.println("ERROR: " + ex.getMessage());
1109 }
1110 }
1111}
1112
1113
1114
1115import java.sql.*;
1116
1117/**
1118This program creates a Customer
1119table in the CoffeeDB database.
1120*/
1121public class CreateCustomerTable
1122{
1123 public static void main(String[] args)
1124 {
1125 // Create a named constant for the URL.
1126 // NOTE: This value is specific for Java DB.
1127 final String DB_URL = "jdbc:derby:CoffeeDB";
1128 try
1129 {
1130 // Create a connection to the database.
1131 Connection conn = DriverManager.getConnection(DB_URL);
1132 // Get a Statement object.
1133 Statement stmt = conn.createStatement();
1134 // Make an SQL statement to create the table.
1135 String sql = "CREATE TABLE Customer" +
1136 "( CustomerNumber CHAR(10) NOT NULL PRIMARY KEY, " +
1137 " Name CHAR(25)," +
1138 " Address CHAR(25)," +
1139 " City CHAR(12)," +
1140 " State CHAR(2)," +
1141 " Zip CHAR(5) )";
1142 // Execute the statement.
1143 stmt.execute(sql);
1144 // Add some rows to the new table.
1145 sql = "INSERT INTO Customer VALUES" +
1146 "('101', 'Downtown Cafe', '17 N. Main Street'," +
1147 " 'Asheville', 'NC', '55515')";
1148 stmt.executeUpdate(sql);
1149 sql = "INSERT INTO Customer VALUES" +
1150 "('102', 'Main Street Grocery'," +
1151 " '110 E. Main Street'," +
1152 " 'Canton', 'NC', '55555')";
1153 stmt.executeUpdate(sql);
1154 sql = "INSERT INTO Customer VALUES" +
1155 "('103', 'The Coffee Place', '101 Center Plaza'," +
1156 " 'Waynesville', 'NC', '55516')";
1157 stmt.executeUpdate(sql);
1158 // Close the connection.
1159 conn.close();
1160 }
1161 catch (Exception ex)
1162 {
1163 System.out.println("ERROR: " + ex.getMessage());
1164 }
1165 }
1166}
1167
1168
1169
1170
1171import java.sql.*; // Needed for JDBC classes
1172/**
1173This program creates an UnpaidOrder
1174
1175table in the CoffeeDB database.
1176*/
1177public class CreateUnpaidOrderTable
1178{
1179 public static void main(String[] args)
1180 {
1181 // Create a named constant for the URL.
1182 // NOTE: This value is specific for Java DB.
1183 final String DB_URL = "jdbc:derby:CoffeeDB";
1184 try
1185 {
1186 // Create a connection to the database.
1187 Connection conn = DriverManager.getConnection(DB_URL);
1188 // Get a Statement object.
1189 Statement stmt = conn.createStatement();
1190 // Make an SQL statement to create the table.
1191 String sql = "CREATE TABLE UnpaidOrder " +
1192 "( CustomerNumber CHAR(10) NOT NULL REFERENCES Customer(CustomerNumber), "+
1193 " ProdNum CHAR(10) NOT NULL REFERENCES Coffee(ProdNum),"+
1194 " OrderDate CHAR(10),"+
1195 " Quantity DOUBLE,"+
1196 " Cost DOUBLE )";
1197 // Execute the statement.
1198 stmt.execute(sql);
1199 // Add some rows to the new table.
1200 sql = "INSERT INTO UnpaidOrder VALUES" +
1201 "('101', '16-001', '3/15/2006', 5, 49.75)";
1202 stmt.executeUpdate(sql);
1203
1204 sql = "INSERT INTO UnpaidOrder VALUES" +
1205 "('101', '14-001', '3/17/2006', 7, 62.65)";
1206 stmt.executeUpdate(sql);
1207 sql = "INSERT INTO UnpaidOrder VALUES" +
1208 "('102', '18-002', '3/20/2006', 10, 184.50)";
1209 stmt.executeUpdate(sql);
1210 sql = "INSERT INTO UnpaidOrder VALUES" +
1211 "('103', '17-004', '3/21/2006', 3, 35.85)";
1212 stmt.executeUpdate(sql);
1213 sql = "INSERT INTO UnpaidOrder VALUES" +
1214 "('103', '16-002', '3/22/2006', 6, 59.70)";
1215 stmt.executeUpdate(sql);
1216 // Close the connection.
1217 conn.close();
1218 }
1219 catch (Exception ex)
1220 {
1221 System.out.println("ERROR: " + ex.getMessage());
1222 }
1223 }
1224}
1225
1226
1227
1228
1229import java.sql.*; // Needed for JDBC classes
1230/**
1231This program demonstrates how to connect to
1232a Java DB database using JDBC.
1233*/
1234
1235public class TestConnection
1236{
1237 public static void main(String[] args)
1238 {
1239 // Create a named constant for the URL.
1240 // NOTE: This value is specific for Java DB.
1241 final String DB_URL = "jdbc:derby:CoffeeDB";
1242
1243 try
1244 {
1245 // Create a connection to the database.
1246 Connection conn = DriverManager.getConnection(DB_URL);
1247 System.out.println("Connection to CoffeeDB created.");
1248
1249 // Close the connection.
1250 conn.close();
1251 System.out.println("Connection closed.");
1252 }
1253 catch(Exception ex)
1254 {
1255 System.out.println("ERROR: " + ex.getMessage());
1256 }
1257 }
1258}