· 4 years ago · Mar 29, 2021, 07:58 AM
1package DB;
2
3
4import Beans.*;
5import Beans.Config;
6import DAO.CouponsDAO;
7import DBDAO.CouponsDBDAO;
8import Facades.AdminFacade;
9import Facades.CompanyFacade;
10
11import java.sql.SQLException;
12import java.util.HashMap;
13import java.util.Map;
14
15public class DatabaseManager {
16 //mysql:mysql-connector-java:8.0.23 : file->project structure->Libreries->+->maven-> link
17 //conection string for connection to the mysql/mariadb server
18 public static String url = "jdbc:mysql://localhost:3306?createDatabaseIfNotExist=FALSE&useTimezone=TRUE&serverTimezone=GMT"; //Asia/Jerusalem
19 public static String username = "root";
20 public static String password = "";
21
22 private static String CREATE_DB = "CREATE DATABASE couponsdb";
23 private static String DROP_DB = "DROP DATABASE couponsdb";
24
25 private static final String CREATE_TABLE_CATEGORIES = "CREATE TABLE IF NOT EXISTS `couponsdb`.`categories` (`ID` INT(25) NOT NULL AUTO_INCREMENT PRIMARY KEY, `NAME` VARCHAR(30) NOT NULL)";
26 private static final String CREATE_TABLE_CATEGORIES_CONTENT = "INSERT INTO `couponsdb`.`categories` (`ID`,`NAME`) VALUES (?,?)";
27 private static final String CREATE_TABLE_COMPANIES = "CREATE TABLE IF NOT EXISTS `couponsdb`.`companies` (`ID` INT(25) NOT NULL AUTO_INCREMENT PRIMARY KEY, `NAME` VARCHAR(30) NOT NULL, `EMAIL` VARCHAR(50) NOT NULL, `PASSWORD` VARCHAR(50) NOT NULL)";
28 private static final String CREATE_TABLE_COUPONS = "CREATE TABLE IF NOT EXISTS `couponsdb`.`coupons` (`ID` INT(25) NOT NULL AUTO_INCREMENT PRIMARY KEY, `COMPANY_ID` INT(25) NOT NULL, `CATEGORY_ID` INT(25) NOT NULL, `TITLE` VARCHAR(30) NOT NULL, `DESCRIPTION` VARCHAR(150) NOT NULL, `START_DATE` DATE NOT NULL, `END_DATE` DATE NOT NULL, `AMOUNT` INT(11) NOT NULL, `PRICE` DOUBLE NOT NULL, `IMAGE` VARCHAR(50) NOT NULL, FOREIGN KEY (COMPANY_ID) REFERENCES companies(ID) ON DELETE CASCADE, FOREIGN KEY (CATEGORY_ID) REFERENCES categories(ID) ON DELETE CASCADE) ";
29 private static final String CREATE_TABLE_CUSTOMERS = "CREATE TABLE IF NOT EXISTS `couponsdb`.`customers` (`ID` int(25) NOT NULL AUTO_INCREMENT PRIMARY KEY, `FIRST_NAME` VARCHAR(30) NOT NULL, LAST_NAME VARCHAR(30) NOT NULL, `EMAIL` VARCHAR(30) NOT NULL, `PASSWORD` VARCHAR(30) NOT NULL)";
30 private static final String CREATE_TABLE_CUSTOMERS_VS_COUPONS = "CREATE TABLE couponsdb.`customers_vs_coupons` (`CUSTOMER_ID` INT(25) NOT NULL, `COUPON_ID` INT(25) NOT NULL , PRIMARY KEY (`CUSTOMER_ID`,`COUPON_ID`), FOREIGN KEY (CUSTOMER_ID) REFERENCES customers(ID) ON DELETE CASCADE, FOREIGN KEY (COUPON_ID) REFERENCES coupons(ID) ON DELETE CASCADE)";
31
32 private static final String DROP_TABLE_CATEGORIES_FOREIGN_KEY = "ALTER TABLE `couponsdb`.`coupons` DROP FOREIGN KEY `coupons_ibfk_2`";
33 private static final String DROP_TABLE_CATEGORIES = "DROP TABLE `couponsdb`.`categories`";
34 private static final String DROP_TABLE_COMPANIES_FOREIGN_KEY = "ALTER TABLE `couponsdb`.`coupons` DROP FOREIGN KEY `coupons_ibfk_1`;";
35 private static final String DROP_TABLE_COMPANIES = "DROP TABLE `couponsdb`.`companies`";
36 private static final String DROP_TABLE_COUPONS_FOREIGN_KEYS = "ALTER TABLE `couponsdb`.`customers_vs_coupons` DROP FOREIGN KEY `customers_vs_coupons_ibfk_2`;";
37 private static final String DROP_TABLE_COUPONS = " DROP TABLE `couponsdb`.`coupons`;";
38 private static final String DROP_TABLE_CUSTOMERS_FOREIGN_KEY = "ALTER TABLE `couponsdb`.`customers_vs_coupons` DROP FOREIGN KEY `customers_vs_coupons_ibfk_1`;";
39 private static final String DROP_TABLE_CUSTOMERS = "DROP TABLE `couponsdb`.`customers`";
40 private static final String DROP_TABLE_CUSTOMERS_VS_COUPONS = "DROP TABLE `couponsdb`.`customers_vs_coupons`";
41
42
43 public static void getConfiguration() {
44 Config config = Config.readConfig();
45 //"jdbc:mysql://localhost:3306?createDatabaseIfNotExist=FALSE&useTimezone=TRUE&serverTimezone=Asia/Jerusalem"
46 url = "jdbc:mysql://" + config.getSqlConnectionString() +
47 "?createDatabaseIfNotExist" + (config.isCreateIfNotExists() ? "TRUE" : "FALSE") +
48 "&useTimezone=" + (config.isUseTimeZone() ? "TRUE" : "FALSE") +
49 "&serverTimezone=" + config.getServerTimeZone();
50 username = config.getUserName();
51 password = config.getUserPassword();
52 CREATE_DB = "CREATE DATABASE " + config.getDBname();
53 DROP_DB = "DROP DATABASE " + config.getDBname();
54 }
55
56 public static void createDataBase() throws SQLException {
57 DBUtils.runQuery(CREATE_DB);
58 }
59
60 public static void dropDataBase() throws SQLException {
61 DBUtils.runQuery(DROP_DB);
62 }
63
64 public static void createTableCategories() throws SQLException {
65 DBUtils.runQuery(CREATE_TABLE_CATEGORIES);
66 Map<Integer, Object> params1 = new HashMap<>();
67 params1.put(1, Category.ELECTRICITY.getCategoryId());
68 params1.put(2, Category.ELECTRICITY);
69 DBUtils.runQuery(CREATE_TABLE_CATEGORIES_CONTENT, params1);
70 Map<Integer, Object> params2 = new HashMap<>();
71 params2.put(1, Category.FOOD.getCategoryId());
72 params2.put(2, Category.FOOD);
73 DBUtils.runQuery(CREATE_TABLE_CATEGORIES_CONTENT, params2);
74 Map<Integer, Object> params3 = new HashMap<>();
75 params3.put(1, Category.RESTAURANT.getCategoryId());
76 params3.put(2, Category.RESTAURANT);
77 DBUtils.runQuery(CREATE_TABLE_CATEGORIES_CONTENT, params3);
78 Map<Integer, Object> params4 = new HashMap<>();
79 params4.put(1, Category.VACATION.getCategoryId());
80 params4.put(2, Category.VACATION);
81 DBUtils.runQuery(CREATE_TABLE_CATEGORIES_CONTENT, params4);
82 }
83
84
85 public static void createTableCompanies() throws SQLException {
86 DBUtils.runQuery(CREATE_TABLE_COMPANIES);
87 }
88
89 public static void createTableCoupons() throws SQLException {
90 DBUtils.runQuery(CREATE_TABLE_COUPONS);
91 }
92
93 public static void createTableCustomers() throws SQLException {
94 DBUtils.runQuery(CREATE_TABLE_CUSTOMERS);
95 }
96
97 public static void createTableCustomersVsCoupons() throws SQLException {
98 DBUtils.runQuery(CREATE_TABLE_CUSTOMERS_VS_COUPONS);
99 }
100
101 public static void dropTableCategories() throws SQLException {
102 DBUtils.runQuery(DROP_TABLE_CATEGORIES_FOREIGN_KEY);
103 DBUtils.runQuery(DROP_TABLE_CATEGORIES);
104 }
105
106 public static void dropTableCompanies() throws SQLException {
107 DBUtils.runQuery(DROP_TABLE_COMPANIES_FOREIGN_KEY);
108 DBUtils.runQuery(DROP_TABLE_COMPANIES);
109 }
110
111 public static void dropTableCoupons() throws SQLException {
112 DBUtils.runQuery(DROP_TABLE_COUPONS_FOREIGN_KEYS);
113 DBUtils.runQuery(DROP_TABLE_COUPONS);
114 }
115
116 public static void dropTableCustomers() throws SQLException {
117 DBUtils.runQuery(DROP_TABLE_CUSTOMERS_FOREIGN_KEY);
118 DBUtils.runQuery(DROP_TABLE_CUSTOMERS);
119 }
120
121 public static void dropTableCustomersVsCoupons() throws SQLException {
122 DBUtils.runQuery(DROP_TABLE_CUSTOMERS_VS_COUPONS);
123 }
124
125 public static void createDBWithTables() throws SQLException {
126 DBUtils.runQuery(CREATE_DB);
127 createTableCategories();
128 DBUtils.runQuery(CREATE_TABLE_COMPANIES);
129 DBUtils.runQuery(CREATE_TABLE_COUPONS);
130 DBUtils.runQuery(CREATE_TABLE_CUSTOMERS);
131 DBUtils.runQuery(CREATE_TABLE_CUSTOMERS_VS_COUPONS);
132 }
133
134 public static void createDBWithContent() throws SQLException, InterruptedException {
135 dropDataBase();
136 createDBWithTables();
137 AdminFacade adminFacade = new AdminFacade();
138 CompanyFacade companyFacade = new CompanyFacade();
139
140 CouponsDAO couponsDAO = new CouponsDBDAO();
141
142 Company company1 = new Company("Microsoft", "microsoft@gmail.com", "1234");
143 Company company2 = new Company("Apple", "apple@gmail.com", "7412");
144 Company company3 = new Company("Amazon", "amazon@gmail.com", "7777");
145 Company company4 = new Company("John bryce", "jb@gmail.com", "1594");
146 Company company5 = new Company("Intel", "intel@gmail.com", "8888");
147 adminFacade.addCompany(company1);
148 adminFacade.addCompany(company2);
149 adminFacade.addCompany(company3);
150 adminFacade.addCompany(company4);
151 adminFacade.addCompany(company5);
152 System.out.println("------------------------------------------------------------");
153 Coupon coupon1 = new Coupon(1, 1, "dfsdf", "dfsdf", couponsDAO.getDate(2020, 4, 4), couponsDAO.getDate(2022, 8, 8), 50, 15.5, "dfsdd");
154 Coupon coupon2 = new Coupon(1, 2, "dgdsfs", "dfsdf", couponsDAO.getDate(2020, 4, 4), couponsDAO.getDate(2022, 8, 8), 50, 12.5, "dfsdd");
155 Coupon coupon3 = new Coupon(1, 3, "dffdbsdf", "dfsdf", couponsDAO.getDate(2020, 4, 4), couponsDAO.getDate(2022, 8, 8), 50, 20.5, "dfsdd");
156 Coupon coupon4 = new Coupon(1, 4, "bvbb", "dfsdf", couponsDAO.getDate(2020, 4, 4), couponsDAO.getDate(2022, 8, 8), 50, 10.5, "dfsdd");
157 Coupon coupon5 = new Coupon(2, 1, "dfsfgdf", "dfsdf", couponsDAO.getDate(2020, 4, 4), couponsDAO.getDate(2022, 8, 8), 50, 13.4, "dfsdd");
158 Coupon coupon6 = new Coupon(2, 2, "dfd", "dfsdf", couponsDAO.getDate(2020, 4, 4), couponsDAO.getDate(2022, 8, 8), 50, 13.7, "dfsdd");
159 Coupon coupon7 = new Coupon(2, 3, "dfdf", "dfsdf", couponsDAO.getDate(2020, 4, 4), couponsDAO.getDate(2022, 8, 8), 50, 14.3, "dfsdd");
160 Coupon coupon8 = new Coupon(2, 4, "www", "dfsdf", couponsDAO.getDate(2020, 4, 4), couponsDAO.getDate(2022, 8, 8), 50, 11.5, "dfsdd");
161 Coupon coupon9 = new Coupon(3, 1, "hghdf", "dfsdf", couponsDAO.getDate(2020, 4, 4), couponsDAO.getDate(2022, 8, 8), 50, 14, "dfsdd");
162 Coupon coupon10 = new Coupon(3, 2, "zdfv", "dfsdf", couponsDAO.getDate(2020, 4, 4), couponsDAO.getDate(2022, 8, 8), 50, 44.6, "dfsdd");
163 Coupon coupon11 = new Coupon(3, 3, "cvdf", "dfsdf", couponsDAO.getDate(2020, 4, 4), couponsDAO.getDate(2022, 8, 8), 50, 28.1, "dfsdd");
164 Coupon coupon12 = new Coupon(3, 4, "the", "dfsdf", couponsDAO.getDate(2020, 4, 4), couponsDAO.getDate(2022, 8, 8), 50, 15.6, "dfsdd");
165 Coupon coupon13 = new Coupon(4, 1, "hfg ", "dfsdf", couponsDAO.getDate(2020, 4, 4), couponsDAO.getDate(2022, 8, 8), 50, 12.2, "dfsdd");
166 Coupon coupon14 = new Coupon(4, 2, "vvvvv", "dfsdf", couponsDAO.getDate(2020, 4, 4), couponsDAO.getDate(2022, 8, 8), 50, 18.8, "dfsdd");
167 Coupon coupon15 = new Coupon(4, 3, "rhfd", "dfsdf", couponsDAO.getDate(2020, 4, 4), couponsDAO.getDate(2022, 8, 8), 50, 8.4, "dfsdd");
168 Coupon coupon16 = new Coupon(4, 4, "jhbng", "dfsdf", couponsDAO.getDate(2020, 4, 4), couponsDAO.getDate(2022, 8, 8), 50, 9.1, "dfsdd");
169 Coupon coupon17 = new Coupon(5, 1, "dfsvzcxvdf", "dfsdf", couponsDAO.getDate(2020, 4, 4), couponsDAO.getDate(2022, 8, 8), 50, 7.6, "dfsdd");
170 Coupon coupon18 = new Coupon(5, 2, "dfsfgfdfgdf", "dfsdf", couponsDAO.getDate(2020, 4, 4), couponsDAO.getDate(2022, 8, 8), 50, 10.7, "dfsdd");
171 Coupon coupon19 = new Coupon(5, 3, "dfqweqsdf", "dfsdf", couponsDAO.getDate(2020, 4, 4), couponsDAO.getDate(2022, 8, 8), 50, 4.5, "dfsdd");
172 Coupon coupon20 = new Coupon(5, 4, "dffbbbbsdf", "dfsdf", couponsDAO.getDate(2020, 4, 4), couponsDAO.getDate(2022, 8, 8), 50, 6.5, "dfsdd");
173
174 companyFacade.addCoupon(coupon1);
175 companyFacade.addCoupon(coupon2);
176 companyFacade.addCoupon(coupon3);
177 companyFacade.addCoupon(coupon4);
178 companyFacade.addCoupon(coupon5);
179 companyFacade.addCoupon(coupon6);
180 companyFacade.addCoupon(coupon7);
181 companyFacade.addCoupon(coupon8);
182 companyFacade.addCoupon(coupon9);
183 companyFacade.addCoupon(coupon10);
184 companyFacade.addCoupon(coupon11);
185 companyFacade.addCoupon(coupon12);
186 companyFacade.addCoupon(coupon13);
187 companyFacade.addCoupon(coupon14);
188 companyFacade.addCoupon(coupon15);
189 companyFacade.addCoupon(coupon16);
190 companyFacade.addCoupon(coupon17);
191 companyFacade.addCoupon(coupon18);
192 companyFacade.addCoupon(coupon19);
193 companyFacade.addCoupon(coupon20);
194
195 System.out.println("------------------------------------------------------------");
196
197 Customer customer1 = new Customer("David", "Birger", "david@gmail.com", "qasw");
198 Customer customer2 = new Customer("Tom", "Hacarmeli", "tom@gmail.com", "tyygy");
199 Customer customer3 = new Customer("Yossi", "Cohen", "yossi@gmail.com", "fgdf");
200 Customer customer4 = new Customer("Avi", "Levi", "avi@gmail.com", "cvv");
201 Customer customer5 = new Customer("Israel", "Israeli", "israel@gmail.com", "3444");
202 Customer customer6 = new Customer("Ron", "Alon", "ron@gmail.com", "k43yy");
203 Customer customer7 = new Customer("Benjamin", "Netanyahu", "benjamin@gmail.com", "mrlve");
204 Customer customer8 = new Customer("Sara", "Netanyahu", "sara@gmail.com", "kjjhkj");
205 Customer customer9 = new Customer("Benny", "Gantz", "benny@gmail.com", "cdfd");
206 Customer customer10 = new Customer("Naftali", "Bennet", "naftali@gmail.com", "yhbd");
207 adminFacade.addCustomer(customer1);
208 adminFacade.addCustomer(customer2);
209 adminFacade.addCustomer(customer3);
210 adminFacade.addCustomer(customer4);
211 adminFacade.addCustomer(customer5);
212 adminFacade.addCustomer(customer6);
213 adminFacade.addCustomer(customer7);
214 adminFacade.addCustomer(customer8);
215 adminFacade.addCustomer(customer9);
216 adminFacade.addCustomer(customer10);
217
218 System.out.println("------------------------------------------------------------");
219
220 couponsDAO.addCouponPurchase(1, 10);
221 couponsDAO.addCouponPurchase(2, 9);
222 couponsDAO.addCouponPurchase(3, 8);
223 couponsDAO.addCouponPurchase(4, 15);
224 couponsDAO.addCouponPurchase(4, 16);
225 couponsDAO.addCouponPurchase(4, 17);
226 couponsDAO.addCouponPurchase(4, 18);
227 couponsDAO.addCouponPurchase(4, 19);
228 couponsDAO.addCouponPurchase(5, 6);
229 couponsDAO.addCouponPurchase(6, 5);
230 couponsDAO.addCouponPurchase(7, 2);
231 couponsDAO.addCouponPurchase(7, 4);
232 couponsDAO.addCouponPurchase(7, 6);
233 couponsDAO.addCouponPurchase(7, 9);
234 couponsDAO.addCouponPurchase(7, 10);
235 couponsDAO.addCouponPurchase(8, 3);
236 couponsDAO.addCouponPurchase(9, 2);
237 couponsDAO.addCouponPurchase(10, 1);
238
239 }
240}