API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Sep 20th, 2019
102
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Java 16.54 KB | None | 0 0
raw download clone embed print report
  1. package main;
  2.  
  3. import java.io.File;
  4. import java.sql.Connection;
  5. import java.sql.DriverManager;
  6. import java.sql.PreparedStatement;
  7. import java.util.ArrayList;
  8. import java.sql.SQLException;
  9. import java.sql.Statement;
  10. import java.sql.ResultSet;
  11.  
  12. //This class handles input and output for DB management
  13. public class Storage {
  14.    
  15.     //public Connection
  16.    
  17.     private final String DB_NAME = "BISDB";         //Database
  18.     private final String S_TABLE = "SUPPLIERS";     //Suppliers Array
  19.     private final String P_TABLE = "PRODUCTS";      //Products Array
  20.     private final String E_TABLE = "EMPLOYEES";     //Employee Array
  21.     private final String C_TABLE = "CUSTOMERS";     //Customer Array
  22.     private final String SW_TABLE = "SWIPECARD";    //SwipeCard Array
  23.     private final String T_TABLE = "TRANSACTIONS";  //Transaction Array
  24.     private final String I_TABLE = "ITEMS";         //ItemLine Array
  25.     private final String O_TABLE = "ORDERS";        //Order Array
  26.    
  27.     //Prepared Statements
  28.     private final String supStmt = "INSERT INTO " + S_TABLE
  29.                     + "(supplier_id, name, address, phone_no)"
  30.                     + "VALUES(?, ?, ? , ?)";
  31.     private final String ordStmt = "INSERT INTO " + O_TABLE
  32.                     + "(order_id, item, quantity, cost, date, fk_supplier_id)"
  33.                     + "VALUES(?, ? ,? ,? ,? ,?)";
  34.     private final String proStmt = "INSERT INTO " + P_TABLE
  35.                     + "(product_id, product_name, category, price, bulk_sales_amt, discount_percent, "
  36.                     + "stock_level, replenish_level, reorder_quantity, wholesale_cost, fk_supplier_id)"
  37.                     + "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
  38.     private final String empStmt = "INSERT INTO " + E_TABLE
  39.                     + "(employee_id, name, password, staff_type)"
  40.                     + "VALUES(?, ?, ?, ?)";
  41.     private final String cusStmt = "INSERT INTO " + C_TABLE
  42.                     + "(customer_id, name, post_code)"
  43.                     + "VALUES(?, ?, ?)";
  44.     private final String swiStmt = "INSERT INTO " + SW_TABLE
  45.                     + "(swipe_card_id, loyalty_points, credit, fk_customer_id)"
  46.                     + "VALUES(?, ?, ?, ?)";
  47.     private final String traStmt = "INSERT INTO " + T_TABLE
  48.                     + "(transaction_id, transaction_date, total_price, discount_price,"
  49.                     + " purchase_points, fk_customer_id)"
  50.                     + "VALUES(?, ?, ?, ?, ?, ?)";
  51.     private final String iteStmt = "INSERT INTO " + I_TABLE
  52.                     + "(line_id, item, quantity, cost, fk_transaction_id)"
  53.                     + "VALUES(?, ?, ?, ?, ?)";
  54.  
  55.     //Method for getting connection to database
  56.     public Connection getConnection(String dbName)
  57.             throws SQLException, ClassNotFoundException {
  58.         //Registering the HSQLDB JDBC driver
  59.         Class.forName("org.hsqldb.jdbc.JDBCDriver");
  60.    
  61.         /* Database files will be created in the "database"
  62.          * folder in the project. If no username or password is
  63.          * specified, the default SA user and an empty password are used */
  64.         Connection con = DriverManager.getConnection
  65.         ("jdbc:hsqldb:file:database/" + dbName, "SA", "");
  66.         return con;
  67.     }
  68.    
  69.     //Method for checking if DB exists
  70.     public void dbExists() {
  71.         File f = new File("database\\BISDB.log");
  72.         if (f.exists() == false) {
  73.             createDB();
  74.         }
  75.     }
  76.    
  77.     //Method for creation of tables in DB
  78.     //If you need to add or remove variables (or change their type)
  79.     //Modify the appropriate table query
  80.     //Modify the toString of whatever class you edited
  81.     //Check the fillArray method and modify appropriately there too.
  82.     public void createDB() {
  83.         try (Connection con = getConnection(DB_NAME);
  84.                 Statement stmt = con.createStatement()) {
  85.             //Suppliers table
  86.             stmt.executeUpdate("CREATE TABLE "+S_TABLE + "("
  87.                     + "supplier_id VARCHAR(20) NOT NULL, "
  88.                     + "name VARCHAR(40) NOT NULL, "
  89.                     + "address VARCHAR(40) NOT NULL, "
  90.                     + "phone_no VARCHAR(8) NOT NULL, "
  91.                     + "PRIMARY KEY (supplier_id))");
  92.             //Order table
  93.             stmt.executeUpdate("CREATE TABLE " +O_TABLE + "("
  94.                     + "order_id VARCHAR(20) NOT NULL,"
  95.                     + "item VARCHAR(20) NOT NULL, "
  96.                     + "quantity INT NOT NULL, "
  97.                     + "cost DOUBLE NOT NULL, "
  98.                     + "date VARCHAR(6) NOT NULL, "
  99.                     + "fk_supplier_id VARCHAR(15) NOT NULL, "
  100.                     + "FOREIGN KEY (fk_supplier_id) REFERENCES suppliers(supplier_id),"
  101.                     + "PRIMARY KEY (order_id))");
  102.             //Product table
  103.             stmt.executeUpdate("CREATE TABLE "+P_TABLE + "("
  104.                     + "product_id VARCHAR(20) NOT NULL, "
  105.                     + "product_name VARCHAR(10) NOT NULL, "
  106.                     + "category VARCHAR(15) NOT NULL,"
  107.                     + "price DOUBLE NOT NULL, "
  108.                     + "bulk_sales_amt INT NOT NULL, "
  109.                     + "discount_percent INT NOT NULL, "
  110.                     + "stock_level INT NOT NULL, "
  111.                     + "replenish_level INT NOT NULL, "
  112.                     + "reorder_quantity INT NOT NULL, "
  113.                     + "wholesale_cost DOUBLE NOT NULL,"
  114.                     + "fk_supplier_id VARCHAR(15) NOT NULL, "
  115.                     + "FOREIGN KEY (fk_supplier_id) REFERENCES suppliers(supplier_id), "
  116.                     + "PRIMARY KEY (product_id))");
  117.             //Employee table
  118.             stmt.executeUpdate("CREATE TABLE "+E_TABLE + "("
  119.                     + "employee_id VARCHAR(20) NOT NULL, "
  120.                     + "name VARCHAR(20) NOT NULL, "
  121.                     + "password VARCHAR(20) NOT NULL, "
  122.                     + "staff_type VARCHAR(20) NOT NULL, "
  123.                     + "PRIMARY KEY (employee_id))");
  124.             //Customer table
  125.             stmt.executeUpdate("CREATE TABLE "+C_TABLE + "("
  126.                     + "customer_id VARCHAR(20) NOT NULL, "
  127.                     + "name VARCHAR(20) NOT NULL, "
  128.                     + "post_code VARCHAR(4) NOT NULL, "
  129.                     + "PRIMARY KEY (customer_id))");
  130.             //SwipeCard table
  131.             stmt.executeUpdate("CREATE TABLE "+SW_TABLE + "("
  132.                     + "swipe_card_id VARCHAR(20) NOT NULL, "
  133.                     + "loyalty_points INT NOT NULL, "
  134.                     + "credit INT NOT NULL, "
  135.                     + "fk_customer_id VARCHAR(5) NOT NULL, "
  136.                     + "FOREIGN KEY (fk_customer_id) REFERENCES customers(customer_id), "
  137.                     + "PRIMARY KEY (swipe_card_id))"); //CHECK
  138.             //Transactions table
  139.             stmt.executeUpdate("CREATE TABLE "+T_TABLE + "("
  140.                     + "transaction_id VARCHAR(20) NOT NULL, "
  141.                     + "transaction_date VARCHAR(20) NOT NULL, "
  142.                     + "total_price DOUBLE NOT NULL, "
  143.                     + "discount_price DOUBLE NOT NULL, "
  144.                     + "purchase_points INT NOT NULL, "
  145.                     + "fk_customer_id VARCHAR(5) NOT NULL, "
  146.                     + "FOREIGN KEY (fk_customer_id) REFERENCES customers(customer_id), "
  147.                     + "PRIMARY KEY (transaction_id))");
  148.             //LineItem table
  149.             stmt.executeUpdate("CREATE TABLE "+I_TABLE + "("
  150.                     + "line_id VARCHAR(20) NOT NULL, "
  151.                     + "item VARCHAR(10) NOT NULL, "
  152.                     + "quantity INT NOT NULL, "
  153.                     + "cost DOUBLE NOT NULL, "
  154.                     + "fk_transaction_id VARCHAR(5) NOT NULL, "
  155.                     + "FOREIGN KEY (fk_transaction_id) REFERENCES transactions(transaction_id), "
  156.                     + "PRIMARY KEY (line_id))");
  157.             //administrator manager account
  158.             stmt.executeUpdate("INSERT INTO "+ E_TABLE
  159.                                 +" VALUES('1', 'admin', 'pass', 'Manager')");
  160.             System.out.println("Database Initialized");
  161.         } catch (Exception e) {
  162.             System.out.println(e.getMessage());
  163.             e.printStackTrace();
  164.         }
  165.     }
  166.    
  167.     //Method for clearing all rows from all tables
  168.     public void clearDB() {
  169.         try (Connection con = getConnection(DB_NAME);
  170.                 Statement stmt = con.createStatement()) {
  171.         stmt.executeUpdate("DELETE FROM " +P_TABLE);
  172.         stmt.executeUpdate("DELETE FROM " +I_TABLE);
  173.         stmt.executeUpdate("DELETE FROM " +T_TABLE);
  174.         stmt.executeUpdate("DELETE FROM " +SW_TABLE);
  175.         stmt.executeUpdate("DELETE FROM " +C_TABLE);
  176.         stmt.executeUpdate("DELETE FROM " +E_TABLE);
  177.         stmt.executeUpdate("DELETE FROM " +O_TABLE);
  178.         stmt.executeUpdate("DELETE FROM " +S_TABLE);
  179.         } catch (Exception e) {
  180.             System.out.println(e.getMessage());
  181.         }
  182.     }
  183.    
  184.     //Method for updating DB with all Arrays
  185.     //DB empties all rows in all tables with clearDB()
  186.     //Then calls for updates in the order below.
  187.     //Order is important, don't change it unless you know what you are doing.
  188.     public void updateDBAll(ArrayList <Customer> cArray, ArrayList <Employee> eArray,
  189.                             ArrayList <Supplier> sArray, ArrayList <Product> pArray) {
  190.         clearDB();
  191.         updateDBSupplier(sArray);
  192.         updateDBProduct(pArray);
  193.         updateDBCustomer(cArray);
  194.         updateDBEmployee(eArray);
  195.     }
  196.     //The following four methods are for inserting objects into tables.
  197.     //Method for updating DB with customer objects
  198.     public void updateDBCustomer(ArrayList <Customer> cArray) {
  199.         try (Connection con = getConnection(DB_NAME);
  200.                 Statement stmt = con.createStatement()) {
  201.             //Customer
  202.             for (Customer customer:cArray) {
  203.                 String customerSplit[] = customer.toString().split(":");
  204.                 PreparedStatement cStmt = con.prepareStatement(cusStmt);
  205.                 for(int i = 0; i < customerSplit.length; i++) {
  206.                     cStmt.setString(i+1, customerSplit[i]);
  207.                 }
  208.                 cStmt.executeUpdate();
  209.                 //SwipeCard
  210.                 String swipecardSplit[] = customer.getSwipeCard().toString().split(":");
  211.                 PreparedStatement swStmt = con.prepareStatement(swiStmt);
  212.                 for(int i = 0; i < swipecardSplit.length; i++) {
  213.                     swStmt.setString(i+1, swipecardSplit[i]);
  214.                 }
  215.                 //Customer Foreign Key
  216.                 swStmt.setString(4, customer.getCustomerID());
  217.                 swStmt.executeUpdate();
  218.                 //Transactions
  219.                 for (Transaction transaction:customer.getTransactions()) {
  220.                     String transactionSplit[] = transaction.toString().split(":");
  221.                     PreparedStatement tStmt = con.prepareStatement(traStmt);
  222.                     for(int i = 0; i < transactionSplit.length; i++) {
  223.                         tStmt.setString(i+1, transactionSplit[i]);
  224.                     }
  225.                     //Customer Foreign Key
  226.                     tStmt.setString(6, customer.getCustomerID());
  227.                     tStmt.executeUpdate();
  228.                    
  229.                     //Items
  230.                     for (LineItem item:transaction.getItemsBought()) {
  231.                         String itemSplit[] = item.toString().split(":");
  232.                         PreparedStatement iStmt = con.prepareStatement(iteStmt);
  233.                         for(int i = 0; i < itemSplit.length; i++) {
  234.                             iStmt.setString(i+1, itemSplit[i]);
  235.                         }
  236.                         //Transaction Foreign key
  237.                         iStmt.setString(5, transaction.getTransactionID());
  238.                         iStmt.executeUpdate();
  239.                                
  240.                     }
  241.                 }
  242.             }
  243.            
  244.         } catch (Exception e) {
  245.             System.out.println(e.getMessage());
  246.             e.printStackTrace();
  247.         }
  248.     }
  249.    
  250.     //Method for updating DB with employee objects
  251.     public void updateDBEmployee(ArrayList <Employee> eArray) {
  252.         try (Connection con = getConnection(DB_NAME);
  253.                 Statement stmt = con.createStatement()) {
  254.             for (Employee employee:eArray) {
  255.                 String stringSplit[] = employee.toString().split(":");
  256.                 PreparedStatement eStmt = con.prepareStatement(empStmt);
  257.                 for(int i = 0; i < stringSplit.length; i++) {
  258.                     eStmt.setString(i+1, stringSplit[i]);
  259.                 }
  260.                 eStmt.executeUpdate();
  261.             }
  262.            
  263.         } catch (Exception e) {
  264.             System.out.println(e.getMessage());
  265.         }
  266.     }
  267.    
  268.     //Method for updating DB with supplier objects
  269.     public void updateDBSupplier(ArrayList <Supplier> sArray) {
  270.         try (Connection con = getConnection(DB_NAME);
  271.                 Statement stmt = con.createStatement()) {
  272.             for (Supplier supplier: sArray) {
  273.                 String stringSplit[] = supplier.toString().split(":");
  274.                 PreparedStatement sStmt = con.prepareStatement(supStmt);
  275.                 for(int i = 0; i < stringSplit.length; i++) {
  276.                     sStmt.setString(i+1, stringSplit[i]);
  277.                 }
  278.                 sStmt.executeUpdate();
  279.                 //Orders
  280.                 for(Order order: supplier.getoArray()) {
  281.                     String orderSplit[] = order.toString().split(":");
  282.                     PreparedStatement oStmt = con.prepareStatement(ordStmt);
  283.                     for(int i = 0; i < orderSplit.length; i++) {
  284.                         oStmt.setString(i+1, orderSplit[i]);
  285.                     }
  286.                     oStmt.setString(6, supplier.getSupplierID());
  287.                     oStmt.executeUpdate();
  288.                 }
  289.             }
  290.            
  291.         } catch (Exception e) {
  292.             System.out.println(e.getMessage());
  293.         }
  294.     }
  295.    
  296.     //Method for updating DB with product objects
  297.     public void updateDBProduct(ArrayList <Product> pArray) {
  298.         try (Connection con = getConnection(DB_NAME);
  299.                 Statement stmt = con.createStatement()) {
  300.             for (Product product: pArray) {
  301.             String stringSplit[] = product.toString().split(":");
  302.             PreparedStatement pStmt = con.prepareStatement(proStmt);
  303.             for (int i = 0; i < stringSplit.length; i++) {
  304.                 pStmt.setString(i+1, stringSplit[i]);
  305.             }
  306.             pStmt.executeUpdate();
  307.             }
  308.         } catch (Exception e) {
  309.             System.out.println(e.getMessage());
  310.         }
  311.     }
  312.    
  313.     //The following method is what draws information from the Database
  314.     //It inserts objects into the appropriate arrays
  315.     //Need to edit this if you change variables in the system
  316.    
  317.     //Method for filling arrays with DB content
  318.     public void fillArray(ArrayList <Customer> cArray, ArrayList <Employee> eArray,
  319.                             ArrayList <Supplier> sArray, ArrayList <Product> pArray) {
  320.         try (Connection con = getConnection(DB_NAME);
  321.                 Statement stmt = con.createStatement()) {
  322.             String sQuery, pQuery, swQuery, iQuery, eQuery, cQuery, tQuery, oQuery;
  323.             //Supplier Array
  324.             sQuery = "SELECT * FROM " +S_TABLE;
  325.             try (ResultSet resultSet = stmt.executeQuery(sQuery)) {
  326.                 while(resultSet.next()) {
  327.                     Supplier s = new Supplier(resultSet.getString("supplier_id"), resultSet.getString("name"),
  328.                                               resultSet.getString("address"), resultSet.getString("phone_no"));
  329.                     oQuery = "SELECT * FROM " +O_TABLE +" WHERE fk_supplier_id LIKE '" +resultSet.getString("supplier_id")+"'";
  330.                     try(ResultSet oSet = stmt.executeQuery(oQuery)){
  331.                         while (oSet.next()) {
  332.                         Order o = new Order(oSet.getString("order_id"), oSet.getString("item"),
  333.                                             oSet.getInt("quantity"), oSet.getDouble("cost"), oSet.getString("date"));
  334.                         s.getoArray().add(o);
  335.                         }
  336.                     sArray.add(s);
  337.                 }
  338.             }
  339.             }
  340.             //Product Array
  341.             pQuery = "SELECT * FROM " +P_TABLE;
  342.             try (ResultSet resultSet = stmt.executeQuery(pQuery)){
  343.                 while (resultSet.next()) {
  344.                     Product p = new Product(resultSet.getString("product_id"), resultSet.getString("product_name"),
  345.                             resultSet.getString("category"),resultSet.getDouble("price"), resultSet.getInt("bulk_sales_amt"),
  346.                             resultSet.getInt("discount_percent"), resultSet.getInt("stock_level"),
  347.                             resultSet.getInt("replenish_level"), resultSet.getInt("reorder_quantity"),
  348.                             resultSet.getDouble("wholesale_cost"),
  349.                             returnSupplier(sArray, resultSet.getString("fk_supplier_id")));
  350.                     pArray.add(p);
  351.                 }
  352.             }
  353.             //Customer Array
  354.             cQuery = "SELECT * FROM " +C_TABLE;
  355.             try(ResultSet resultSet = stmt.executeQuery(cQuery)) {
  356.                 Customer c;
  357.                 SwipeCard sw = null;
  358.                 while(resultSet.next()) {
  359.                     String customerID = resultSet.getString("customer_id");
  360.                     String customerName = resultSet.getString("name");
  361.                     String customerPostCode = resultSet.getString("post_code");
  362.                     swQuery = "SELECT * FROM " +SW_TABLE +" WHERE fk_customer_id LIKE '" +resultSet.getString("customer_id")+"'";
  363.                     try(ResultSet swSet = stmt.executeQuery(swQuery)){
  364.                         while (swSet.next()) {
  365.                         sw = new SwipeCard(swSet.getString("swipe_card_id"), swSet.getInt("loyalty_points"),
  366.                                                     swSet.getInt("credit"));
  367.                         }
  368.                     }
  369.                     c = new Customer (customerID, customerName, customerPostCode, sw);
  370.                     tQuery = "SELECT * FROM " +T_TABLE +" WHERE fk_customer_ID LIKE '" +resultSet.getString("customer_id")+"'";
  371.                     try(ResultSet tSet = stmt.executeQuery(tQuery)){
  372.                         while(tSet.next()) {
  373.                             Transaction t = new Transaction(tSet.getString("transaction_id"), tSet.getString("transaction_date"),
  374.                                     tSet.getDouble("total_price"), tSet.getDouble("discount_price"),
  375.                                     tSet.getInt("purchase_points"));
  376.                             iQuery = "SELECT * FROM " +I_TABLE +" WHERE fk_transaction_ID LIKE '" +tSet.getString("transaction_id")+"'";
  377.                             try (ResultSet iSet = stmt.executeQuery(iQuery)){
  378.                                 while(iSet.next()) {
  379.                                     t.getItemsBought().add(new LineItem(iSet.getString("line_id"), iSet.getString("item"),
  380.                                                             iSet.getInt("quantity"), iSet.getDouble("cost")));
  381.                                 }
  382.                             }
  383.                             c.getTransactions().add(t);
  384.                         }
  385.                     }
  386.                     cArray.add(c);
  387.                 }
  388.             }
  389.             //Employee Array
  390.             eQuery = "SELECT * FROM " +E_TABLE;
  391.             try(ResultSet resultSet = stmt.executeQuery(eQuery)){
  392.                 while (resultSet.next()) {
  393.                     if(resultSet.getString("staff_type").equals("Manager")) {
  394.                         Manager m = new Manager(resultSet.getString("employee_id"), resultSet.getString("name"), resultSet.getString("password"));
  395.                         eArray.add(m);
  396.                     }
  397.                     else if(resultSet.getString("staff_type").equals("Sales Staff")) {
  398.                         SalesStaff s = new SalesStaff(resultSet.getString("employee_id"), resultSet.getString("name"), resultSet.getString("password"));
  399.                         eArray.add(s);
  400.                     }
  401.                     else if (resultSet.getString("staff_type").equals("Warehouse Staff")) {
  402.                         WarehouseStaff w = new WarehouseStaff(resultSet.getString("employee_id"), resultSet.getString("name"), resultSet.getString("password"));
  403.                         eArray.add(w);
  404.                     }
  405.                 }
  406.             }
  407.            
  408.         }   catch (Exception e) {
  409.             System.out.println(e.getMessage());
  410.             e.printStackTrace();
  411.         }
  412.     }
  413.    
  414.     private Supplier returnSupplier(ArrayList <Supplier> sArray, String name) {
  415.         for (Supplier supplier:sArray) {
  416.             if (supplier.getSupplierID().equals(name)) {
  417.                 return supplier;
  418.             }
  419.         }
  420.         return null;
  421.     }
  422.            
  423. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!