SHARE
TWEET

Untitled

a guest Jun 19th, 2017 56 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. package DB;
  2.  
  3. import Main.Main;
  4. import Model.*;
  5.  
  6. import java.sql.*;
  7. import java.time.LocalDateTime;
  8. import java.util.ArrayList;
  9. import java.util.HashMap;
  10. import java.util.List;
  11. import java.util.Map;
  12.  
  13. import static Main.SingleRequest.Controller.Format;
  14.  
  15. /**
  16.  * Created by eee on 14.05.2017.
  17.  */
  18. public class Database {
  19.     private static final String IP = "Localhost";
  20.     private static final String PORT = "5432";
  21.     private static final String DB = "Delivery_Food";//mary92 //Delivery_Food
  22.  
  23.     private static final String USER = "postgres";
  24.     private static final String PASSWORD = "123"; //qwerty321 //123
  25.  
  26.     private static final String Category_TableName = "Category";
  27.     private static final String CategoryID_ColumnName = "category_id";
  28.     private static final String CategoryName_ColumnName = "category_name";
  29.  
  30.  
  31.     private static final String Client_TableName = "Clients";
  32.     private static final String ClientID_ColumnName = "client_id";
  33.     private static final String ClientName_ColumnName = "client_name";
  34.     private static final String ClientAddress_ColumnName = "address";
  35.     private static final String ClientPhone_ColumnName = "clientt_phone";
  36.     private static final String ClientRequestsCount_ColumnName = "requestcount";
  37.  
  38.  
  39.     private static final String Courier_TableName = "Couriers";
  40.     private static final String CourierID_ColumnName = "courier_id";
  41.     private static final String CourierName_ColumnName = "courier_name";
  42.     private static final String CourierIsWorking_ColumnName = "courier_status";
  43.     private static final String CourierPhone_ColumnName = "courier_phone";
  44.  
  45.  
  46.     private static final String Product_TableName = "Product";
  47.     private static final String ProductID_ColumnName = "product_id";
  48.     private static final String ProductCategory_ColumnName = "category_id";
  49.     private static final String ProductName_ColumnName = "product_name";
  50.     private static final String ProductConsist_ColumnName = "consist";
  51.     private static final String ProductPrice_ColumnName = "price";
  52.     private static final String ProductMeasure_ColumnName = "measure";
  53.  
  54.     private static final String Request_TableName = "Requests";
  55.     private static final String RequestID_ColumnName = "request_id";
  56.     private static final String RequestDate_ColumnName = "datec";
  57.     private static final String RequestClientID_ColumnName = "client_id";
  58.     private static final String RequestCourierID_ColumnName = "courier_id";
  59.     private static final String RequestDeliveryDate_ColumnName = "delivery_date";
  60.     private static final String RequestPrice_ColumnName = "summ";
  61.     private static final String RequestStatus_ColumnName = "status";
  62.     private static final String RequestPriceWithDiscount_ColumnName = "replace it";
  63.  
  64.     private static final String Order_TableName = "Orders";
  65.     private static final String OrderID_ColumnName = "order_id";
  66.     private static final String OrderRequestID_ColumnName = "request_id";
  67.     private static final String OrderProductID_ColumnName = "product_id";
  68.     private static final String OrderCount_ColumnName = "quantity";
  69.  
  70.     static Map<Integer, Order> OrderMap = new HashMap<>();
  71.     static Map<Integer, Request> RequestMap = new HashMap<>();
  72.  
  73.     static Connection connection;
  74.     public static void Connect() throws SQLException {
  75.         connection = DriverManager
  76.                 .getConnection("jdbc:postgresql://" + IP + ":" + PORT + "/" + DB,
  77.                         USER, PASSWORD);
  78.         connection.setAutoCommit(false);
  79.     }
  80.  
  81.     public static List<Category> GetCategories() throws SQLException {
  82.         Statement stmt = connection.createStatement();
  83.         ResultSet rs = stmt.executeQuery( "SELECT * FROM " + Category_TableName + ";" );
  84.  
  85.         List<Category> l = new ArrayList<>();
  86.         while (rs.next()){
  87.             String name = rs.getString(CategoryName_ColumnName);
  88.             int id = rs.getInt(CategoryID_ColumnName);
  89.  
  90.             Category cat = new Category(id);
  91.             cat.setName(name);
  92.             l.add(cat);
  93.         }
  94.         rs.close();
  95.         stmt.close();
  96.         return l;
  97.     }
  98.  
  99.     public static void UpdateCategories(List<Category> categories) throws SQLException {
  100.         Statement stmt = connection.createStatement();
  101.  
  102.         for(Category car : categories){
  103.             stmt.executeUpdate("UPDATE " + Category_TableName + " SET " + CategoryName_ColumnName + " = '" + car.getName() + "' where " + CategoryID_ColumnName + " = " + car.getID());
  104.         }
  105.         connection.commit();
  106.         stmt.close();
  107.     }
  108.  
  109.     public static List<Category> AddCategories(List<Category> categories) throws SQLException {
  110.         Statement stmt = connection.createStatement();
  111.  
  112.         for(Category car : categories){
  113.             stmt.executeUpdate("INSERT INTO " + Category_TableName + " (" + CategoryName_ColumnName + ") VALUES ('" + car.getName() + "');", Statement.RETURN_GENERATED_KEYS);
  114.         }
  115.         ResultSet rs = stmt.getGeneratedKeys();
  116.         int i = 0;
  117.         if (rs != null && rs.next()) {
  118.             categories.get(i).setID(rs.getInt(1));
  119.  
  120.         }
  121.         connection.commit();
  122.         stmt.close();
  123.  
  124.         return categories;
  125.     }
  126.  
  127.     public static void RemoveCategories(List<Category> categories) throws SQLException {
  128.         Statement stmt = connection.createStatement();
  129.  
  130.         for(Category car : categories){
  131.             stmt.executeUpdate("DELETE FROM " + Category_TableName + " where " + CategoryID_ColumnName + " = " + car.getID());
  132.         }
  133.         connection.commit();
  134.         stmt.close();
  135.     }
  136.  
  137.  
  138.     public static List<Client> GetClients() throws SQLException {
  139.         Statement stmt = connection.createStatement();
  140.         ResultSet rs = stmt.executeQuery( "SELECT * FROM " + Client_TableName + ";" );
  141.  
  142.         List<Client> l = new ArrayList<>();
  143.         while (rs.next()){
  144.             String name = rs.getString(ClientName_ColumnName);
  145.             String address = rs.getString(ClientAddress_ColumnName);
  146.             String phone = rs.getString(ClientPhone_ColumnName);
  147.             int id = rs.getInt(ClientID_ColumnName);
  148.  
  149.             Client cat = new Client();
  150.             cat.setID(id);
  151.             cat.setName(name);
  152.             cat.setAddress(address);
  153.             cat.setPhone(phone);
  154.             l.add(cat);
  155.         }
  156.         rs.close();
  157.         stmt.close();
  158.         return l;
  159.     }
  160.  
  161.     public static void UpdateClients(List<Client> categories) throws SQLException {
  162.         Statement stmt = connection.createStatement();
  163.  
  164.         for(Client car : categories){
  165.             stmt.executeUpdate("UPDATE " + Client_TableName + " SET " +
  166.                     ClientRequestsCount_ColumnName + " = '" + car.getRequestsCount() + "', " +
  167.                     ClientName_ColumnName + " = '" + car.getName() + "', "+
  168.                     ClientAddress_ColumnName + "= '" + car.getAddress() + "', " +
  169.                     ClientPhone_ColumnName + "= '" + car.getPhone() + "' where " + ClientID_ColumnName + " = " + car.getID());
  170.         }
  171.         connection.commit();
  172.         stmt.close();
  173.     }
  174.  
  175.     public static List<Client> AddClients(List<Client> categories) throws SQLException {
  176.         Statement stmt = connection.createStatement();
  177.  
  178.         for(Client car : categories){
  179.             stmt.executeUpdate("INSERT INTO " + Client_TableName + " ("+ ClientRequestsCount_ColumnName + ", " + ClientName_ColumnName + ", " + ClientPhone_ColumnName + ", " + ClientAddress_ColumnName + ") VALUES ( " +
  180.                     "'" + car.getRequestsCount() + "', " +
  181.                     "'" + car.getName() + "', " +
  182.                     "'" + car.getPhone() + "',  " +
  183.                     "'" + car.getAddress() + "'" + " );", Statement.RETURN_GENERATED_KEYS);
  184.         }
  185.         ResultSet rs = stmt.getGeneratedKeys();
  186.         int i = 0;
  187.         if (rs != null && rs.next()) {
  188.             categories.get(i).setID(rs.getInt(1));
  189.  
  190.         }
  191.         connection.commit();
  192.         stmt.close();
  193.  
  194.         return categories;
  195.     }
  196.  
  197.     public static void RemoveClients(List<Client> categories) throws SQLException {
  198.         Statement stmt = connection.createStatement();
  199.  
  200.         for(Client car : categories){
  201.             stmt.executeUpdate("DELETE FROM " + Client_TableName + " where " + ClientID_ColumnName + " = " + car.getID());
  202.         }
  203.         connection.commit();
  204.         stmt.close();
  205.     }
  206.  
  207.  
  208.     public static List<Courier> GetCouriers() throws SQLException {
  209.         Statement stmt = connection.createStatement();
  210.         ResultSet rs = stmt.executeQuery( "SELECT * FROM " + Courier_TableName + ";" );
  211.  
  212.         List<Courier> l = new ArrayList<>();
  213.         while (rs.next()){
  214.             String name = rs.getString(CourierName_ColumnName);
  215.             Boolean isWorking = rs.getBoolean(CourierIsWorking_ColumnName);
  216.             String phone = rs.getString(CourierPhone_ColumnName);
  217.             int id = rs.getInt(CourierID_ColumnName);
  218.  
  219.             Courier cat = new Courier();
  220.             cat.setID(id);
  221.             cat.setName(name);
  222.             cat.isWorking = isWorking;
  223.             cat.setPhone(phone);
  224.             l.add(cat);
  225.         }
  226.         rs.close();
  227.         stmt.close();
  228.         return l;
  229.     }
  230.  
  231.     public static void UpdateCouriers(List<Courier> categories) throws SQLException {
  232.         Statement stmt = connection.createStatement();
  233.  
  234.         for(Courier car : categories){
  235.             stmt.executeUpdate("UPDATE " + Courier_TableName + " SET " +
  236.                     CourierName_ColumnName + " = '" + car.getName() + "', "+
  237.                     CourierIsWorking_ColumnName + "= '" + car.isWorking + "', " +
  238.                     CourierPhone_ColumnName + "= '" + car.getPhone() + "' where " + CourierID_ColumnName + " = " + car.getID());
  239.         }
  240.         connection.commit();
  241.         stmt.close();
  242.     }
  243.  
  244.     public static List<Courier> AddCouriers(List<Courier> categories) throws SQLException {
  245.         Statement stmt = connection.createStatement();
  246.  
  247.         for(Courier car : categories){
  248.             stmt.executeUpdate("INSERT INTO " + Courier_TableName + " (" + CourierName_ColumnName + ", " + CourierPhone_ColumnName + ", " + CourierIsWorking_ColumnName + ") VALUES ( " +
  249.                     "'" + car.getName() + "', " +
  250.                     "'" + car.getPhone() + "', " +
  251.                     "'" + car.isWorking + "'" + " );", Statement.RETURN_GENERATED_KEYS);
  252.         }
  253.         ResultSet rs = stmt.getGeneratedKeys();
  254.         int i = 0;
  255.         if (rs != null && rs.next()) {
  256.             categories.get(i).setID(rs.getInt(1));
  257.  
  258.         }
  259.         connection.commit();
  260.         stmt.close();
  261.  
  262.         return categories;
  263.     }
  264.  
  265.     public static void RemoveCouriers(List<Courier> categories) throws SQLException {
  266.         Statement stmt = connection.createStatement();
  267.  
  268.         for(Courier car : categories){
  269.             stmt.executeUpdate("DELETE FROM " + Courier_TableName + " where " + CourierID_ColumnName + " = " + car.getID());
  270.         }
  271.         connection.commit();
  272.         stmt.close();
  273.     }
  274.  
  275.     public static List<Product> GetProducts() throws SQLException {
  276.         Statement stmt = connection.createStatement();
  277.         ResultSet rs = stmt.executeQuery( "SELECT * FROM " + Product_TableName + ";" );
  278.  
  279.         List<Category> categories = GetCategories();
  280.         List<Product> l = new ArrayList<>();
  281.         while (rs.next()){
  282.             String name = rs.getString(ProductName_ColumnName);
  283.             int categoryid = rs.getInt(ProductCategory_ColumnName);
  284.             String consist = rs.getString(ProductConsist_ColumnName);
  285.             String measure = rs.getString(ProductMeasure_ColumnName);
  286.             double price = rs.getDouble(ProductPrice_ColumnName);
  287.             int id = rs.getInt(ProductID_ColumnName);
  288.  
  289.             Product cat = new Product();
  290.             cat.setID(id);
  291.             cat.setName(name);
  292.             cat.setConsist(consist);
  293.             try{
  294.                 cat.setMeasure(Measure.valueOf(measure));
  295.             }catch (Exception e) { }
  296.             cat.setPrice(price);
  297.             for (Category category : categories){
  298.                 if( category.getID() == categoryid ) {
  299.                     cat.setCategory(category);
  300.                     break;
  301.                 }
  302.             }
  303.             l.add(cat);
  304.         }
  305.         rs.close();
  306.         stmt.close();
  307.         return l;
  308.     }
  309.  
  310.     public static void UpdateProducts(List<Product> categories) throws SQLException {
  311.         Statement stmt = connection.createStatement();
  312.  
  313.         for(Product car : categories){
  314.             String additional = "";
  315.             if(car.getCategory() != null){
  316.                 additional += ProductCategory_ColumnName + "= '" + car.getCategory().getID() + "', ";
  317.             }
  318.             if(car.getMeasure() != null){
  319.                 additional += ProductMeasure_ColumnName + "= '" + car.getMeasure().name() + "', ";
  320.             }
  321.             stmt.executeUpdate("UPDATE " + Product_TableName + " SET " +
  322.                     ProductName_ColumnName + " = '" + car.getName() + "', "+
  323.                     ProductConsist_ColumnName + "= '" + car.getConsist() + "', " +
  324.                     additional +
  325.                     ProductPrice_ColumnName + "= '" + car.getPrice() + "'" +
  326.                     " where " + ProductID_ColumnName + " = " + car.getID());
  327.         }
  328.         connection.commit();
  329.         stmt.close();
  330.     }
  331.  
  332.     public static List<Product> AddProducts(List<Product> categories) throws SQLException {
  333.         Statement stmt = connection.createStatement();
  334.  
  335.         for(Product car : categories){
  336.             String additionalName = "";
  337.             String additional = "";
  338.  
  339.             if(car.getCategory() != null){
  340.                 additionalName += ProductCategory_ColumnName + ", ";
  341.                 additional += "'" + car.getCategory().getID() + "', ";
  342.             }
  343.             if(car.getMeasure() != null){
  344.                 additionalName += ProductMeasure_ColumnName + ", ";
  345.                 additional += "'" + car.getMeasure().name() + "', ";
  346.             }
  347.  
  348.             stmt.executeUpdate("INSERT INTO " + Product_TableName + " (" + ProductName_ColumnName + ", " + ProductConsist_ColumnName + ", " + additionalName + ProductPrice_ColumnName + ") VALUES ( " +
  349.                     "'" + car.getName() + "', " +
  350.                     "'" + car.getConsist() + "', " +
  351.                     additional +
  352.                     "'" + car.getPrice() + "'" + " );", Statement.RETURN_GENERATED_KEYS);
  353.         }
  354.         ResultSet rs = stmt.getGeneratedKeys();
  355.         int i = 0;
  356.         if (rs != null && rs.next()) {
  357.             categories.get(i).setID(rs.getInt(1));
  358.  
  359.         }
  360.         connection.commit();
  361.         stmt.close();
  362.  
  363.         return categories;
  364.     }
  365.  
  366.     public static void RemoveProducts(List<Product> categories) throws SQLException {
  367.         Statement stmt = connection.createStatement();
  368.  
  369.         for(Product car : categories){
  370.             stmt.executeUpdate("DELETE FROM " + Product_TableName + " where " + ProductID_ColumnName + " = " + car.getID());
  371.         }
  372.         connection.commit();
  373.         stmt.close();
  374.     }
  375.  
  376.     public static List<Request> GetRequests() throws SQLException { return GetRequests(null, null);}
  377.  
  378.     private static List<Request> GetRequests(List<Client> clientsO, List<Courier> couriersO) throws SQLException {
  379.         Statement stmt = connection.createStatement();
  380.         ResultSet rs = stmt.executeQuery( "SELECT * FROM " + Request_TableName + ";" );
  381.  
  382.         List<Client> clients;
  383.         if(clientsO == null) clients = GetClients();
  384.         else clients = clientsO;
  385.  
  386.         List<Courier> couriers;
  387.         if(couriersO == null) couriers = GetCouriers();
  388.         else couriers = couriersO;
  389.  
  390.         List<Request> l = new ArrayList<>();
  391.         while (rs.next()){
  392.             int id = rs.getInt(RequestID_ColumnName);
  393.  
  394.             Request cat;
  395.             if(!RequestMap.containsKey(id)){
  396.                 RequestMap.put(id, new Request());
  397.             }
  398.             cat = RequestMap.get(id);
  399.  
  400.             LocalDateTime date = LocalDateTime.parse(rs.getString(RequestDate_ColumnName), Format);
  401.             LocalDateTime deliveryDate = LocalDateTime.parse(rs.getString(RequestDeliveryDate_ColumnName), Format);
  402.             int clientID = rs.getInt(RequestClientID_ColumnName);
  403.             int courierID = rs.getInt(RequestCourierID_ColumnName);
  404.             double price = rs.getDouble(RequestPrice_ColumnName);
  405.             String status = rs.getString(RequestStatus_ColumnName);
  406.  
  407.             cat.setID(id);
  408.             cat.setDate(date);
  409.             cat.setDeliveryDate(deliveryDate);
  410.             try{
  411.                 cat.setStatus(RequestStatus.valueOf(status));
  412.             }catch (Exception e) { }
  413.             cat.setPrice(price);
  414.             for (Client client : clients){
  415.                 if( client.getID() == clientID ) {
  416.                     cat.setClient(client);
  417.                     break;
  418.                 }
  419.             }
  420.             for (Courier courier : couriers){
  421.                 if( courier.getID() == courierID ) {
  422.                     cat.setCourier(courier);
  423.                     break;
  424.                 }
  425.             }
  426.             l.add(cat);
  427.         }
  428.         rs.close();
  429.         stmt.close();
  430.         return l;
  431.     }
  432.  
  433.     public static void UpdateRequests(List<Request> categories) throws SQLException {
  434.         Statement stmt = connection.createStatement();
  435.  
  436.         for(Request car : categories){
  437.             String additional = "";
  438.             if(car.getClient() != null){
  439.                 additional += RequestClientID_ColumnName + "= '" + car.getClient().getID() + "', ";
  440.             }
  441.             if(car.getCourier() != null){
  442.                 additional += RequestCourierID_ColumnName + "= '" + car.getCourier().getID() + "', ";
  443.             }
  444.             if(car.getStatus() != null){
  445.                 additional += RequestStatus_ColumnName + "= '" + car.getStatus().name() + "', ";
  446.             }
  447.             additional += RequestPriceWithDiscount_ColumnName + "= '" + car.getPriceWithDiscount + ", ";
  448.             stmt.executeUpdate("UPDATE " + Request_TableName + " SET " +
  449.                     RequestDeliveryDate_ColumnName + " = '" + car.getDeliveryDate().format(Format) + "', "+
  450.                     RequestDate_ColumnName + "= '" + car.getDate().format(Format) + "', " +
  451.                             additional +
  452.                     RequestPrice_ColumnName + "= '" + car.getPrice() + "' " +
  453.                      " where " + RequestID_ColumnName + " = " + car.getID());
  454.         }
  455.         connection.commit();
  456.         stmt.close();
  457.     }
  458.  
  459.     public static List<Request> AddRequests(List<Request> categories) throws SQLException {
  460.         Statement stmt = connection.createStatement();
  461.  
  462.         for(Request car : categories){
  463.             RequestMap.put(car.getID(), car);
  464.             String additionalName = "";
  465.             String additional = "";
  466.  
  467.             if(car.getClient() != null){
  468.                 additionalName += RequestClientID_ColumnName + ", ";
  469.                 additional += "'" + car.getClient().getID() + "', ";
  470.             }
  471.             if(car.getCourier() != null){
  472.                 additionalName += RequestCourierID_ColumnName + ", ";
  473.                 additional += "'" + car.getCourier().getID() + "', ";
  474.             }
  475.             if(car.getStatus() != null){
  476.                 additionalName += RequestStatus_ColumnName + ", ";
  477.                 additional += "'" + car.getStatus().name() + "', ";
  478.             }
  479.            
  480.             additionalName += RequestPriceWithDiscount_ColumnName + ", ";
  481.             additional += "'" + car.getPriceWithDiscount() + "', ";
  482.  
  483.             stmt.executeUpdate("INSERT INTO " + Request_TableName + " (" + RequestDate_ColumnName + ", " + RequestDeliveryDate_ColumnName + ", " + additionalName + RequestPrice_ColumnName + ") VALUES ( " +
  484.                     "'" + car.getDate().format(Format) + "', " +
  485.                     "'" + car.getDeliveryDate().format(Format) + "', " +
  486.                     additional +
  487.                     "'" + car.getPrice() + "'" + " );", Statement.RETURN_GENERATED_KEYS);
  488.         }
  489.         ResultSet rs = stmt.getGeneratedKeys();
  490.         int i = 0;
  491.         if (rs != null && rs.next()) {
  492.             categories.get(i).setID(rs.getInt(1));
  493.  
  494.         }
  495.         connection.commit();
  496.         stmt.close();
  497.  
  498.         return categories;
  499.     }
  500.  
  501.     public static void RemoveRequests(List<Request> categories) throws SQLException {
  502.         Statement stmt = connection.createStatement();
  503.  
  504.         for(Request car : categories){
  505.             stmt.executeUpdate("DELETE FROM " + Request_TableName + " where " + RequestID_ColumnName + " = " + car.getID());
  506.         }
  507.         connection.commit();
  508.         stmt.close();
  509.     }
  510.  
  511.  
  512.  
  513.     public static List<Order> GetOrders() throws SQLException {
  514.         Statement stmt = connection.createStatement();
  515.         ResultSet rs = stmt.executeQuery( "SELECT * FROM " + Order_TableName + ";" );
  516.  
  517.         List<Request> requests = GetRequests();
  518.         List<Product> products = GetProducts();
  519.         List<Order> l = new ArrayList<>();
  520.         while (rs.next()){
  521.             int id = rs.getInt(OrderID_ColumnName);
  522.  
  523.  
  524.             Order cat;
  525.             if(!OrderMap.containsKey(id)){
  526.                 OrderMap.put(id, new Order());
  527.             }
  528.             cat = OrderMap.get(id);
  529.  
  530.             int requestID = rs.getInt(OrderRequestID_ColumnName);
  531.             int productID = rs.getInt(OrderProductID_ColumnName);
  532.             int count = rs.getInt(OrderCount_ColumnName);
  533.  
  534.             cat.setID(id);
  535.             cat.setCount(count);
  536.  
  537.             for (Request request : requests){
  538.                 if( request.getID() == requestID ) {
  539.                     cat.setRequest(request);
  540.                     break;
  541.                 }
  542.             }
  543.             for (Product product : products){
  544.                 if( product.getID() == productID ) {
  545.                     cat.setProduct(product);
  546.                     break;
  547.                 }
  548.             }
  549.             l.add(cat);
  550.         }
  551.         rs.close();
  552.         stmt.close();
  553.         return l;
  554.     }
  555.  
  556.     public static void UpdateOrders(List<Order> categories) throws SQLException {
  557.         Statement stmt = connection.createStatement();
  558.  
  559.         for(Order car : categories){
  560.             String additional = "";
  561.             if(car.getRequest() != null){
  562.                 additional += OrderRequestID_ColumnName + "= '" + car.getRequest().getID() + "', ";
  563.             }
  564.             if(car.getProduct() != null){
  565.                 additional += OrderProductID_ColumnName + "= '" + car.getProduct().getID() + "', ";
  566.             }
  567.             stmt.executeUpdate("UPDATE " + Order_TableName + " SET " +
  568.                     additional +
  569.                     OrderCount_ColumnName + "= '" + car.getCount() + "' where " + OrderID_ColumnName + " = " + car.getID());
  570.         }
  571.         connection.commit();
  572.         stmt.close();
  573.     }
  574.  
  575.     public static List<Order> AddOrders(List<Order> categories) throws SQLException {
  576.         Statement stmt = connection.createStatement();
  577.  
  578.         for(Order car : categories){
  579.             OrderMap.put(car.getID(), car);
  580.             String additionalName = "";
  581.             String additional = "";
  582.  
  583.             if(car.getRequest() != null){
  584.                 additionalName += OrderRequestID_ColumnName + ", ";
  585.                 additional += "'" + car.getRequest().getID() + "', ";
  586.             }
  587.             if(car.getProduct() != null){
  588.                 additionalName += OrderProductID_ColumnName + ", ";
  589.                 additional += "'" + car.getProduct().getID() + "', ";
  590.             }
  591.  
  592.             stmt.executeUpdate("INSERT INTO " + Order_TableName + " (" + additionalName + OrderCount_ColumnName + ") VALUES ( " +
  593.                     additional +
  594.                     "'" + car.getCount() + "'" + " );", Statement.RETURN_GENERATED_KEYS);
  595.         }
  596.         ResultSet rs = stmt.getGeneratedKeys();
  597.         int i = 0;
  598.         if (rs != null && rs.next()) {
  599.             categories.get(i).setID(rs.getInt(1));
  600.  
  601.         }
  602.         connection.commit();
  603.         stmt.close();
  604.  
  605.         return categories;
  606.     }
  607.  
  608.     public static void RemoveOrders(List<Order> categories) throws SQLException {
  609.         Statement stmt = connection.createStatement();
  610.  
  611.         for(Order car : categories){
  612.             stmt.executeUpdate("DELETE FROM " + Order_TableName + " where " + OrderID_ColumnName + " = " + car.getID());
  613.         }
  614.         connection.commit();
  615.         stmt.close();
  616.     }
  617. }
RAW Paste Data
Top