· 4 years ago · Mar 22, 2021, 01:34 PM
1package DB;
2
3import Beans.Category;
4import Beans.Customer;
5import Configurations.Config;
6
7import java.sql.SQLException;
8import java.util.HashMap;
9import java.util.Map;
10
11public class DatabaseManager {
12 //mysql:mysql-connector-java:8.0.23 : file->project structure->Libreries->+->maven-> link
13 //conection string for connection to the mysql/mariadb server
14 public static String url = "jdbc:mysql://localhost:3306?createDatabaseIfNotExist=FALSE&useTimezone=TRUE&serverTimezone=GMT"; //Asia/Jerusalem
15 public static String username = "root";
16 public static String password = "";
17
18 private static String CREATE_DB = "CREATE DATABASE couponsdb";
19 private static String DROP_DB = "DROP DATABASE couponsdb";
20
21 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)";
22 private static final String ADD_CATEGORIES = "INSERT INTO `couponsdb`.`categories` (`ID`,`NAME`) VALUES (?,?)";
23 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)";
24 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), FOREIGN KEY (CATEGORY_ID) REFERENCES categories(ID))";
25 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)";
26 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), FOREIGN KEY (COUPON_ID) REFERENCES coupons(ID))";
27
28 private static final String DROP_TABLE_CATEGORIES_FOREIGN_KEY = "ALTER TABLE `couponsdb`.`coupons` DROP FOREIGN KEY `coupons_ibfk_2`";
29 private static final String DROP_TABLE_CATEGORIES = "DROP TABLE `couponsdb`.`categories`";
30 private static final String DROP_TABLE_COMPANIES_FOREIGN_KEY = "ALTER TABLE `couponsdb`.`coupons` DROP FOREIGN KEY `coupons_ibfk_1`;";
31 private static final String DROP_TABLE_COMPANIES = "DROP TABLE `couponsdb`.`companies`";
32 private static final String DROP_TABLE_COUPONS_FOREIGN_KEYS = "ALTER TABLE `couponsdb`.`customers_vs_coupons` DROP FOREIGN KEY `customers_vs_coupons_ibfk_2`;";
33 private static final String DROP_TABLE_COUPONS = " DROP TABLE `couponsdb`.`coupons`;";
34 private static final String DROP_TABLE_CUSTOMERS_FOREIGN_KEYS ="ALTER TABLE `couponsdb`.`customers_vs_coupons` DROP FOREIGN KEY `customers_vs_coupons_ibfk_1`;";
35 private static final String DROP_TABLE_CUSTOMERS = "DROP TABLE `couponsdb`.`customers`";
36 private static final String DROP_TABLE_CUSTOMERS_VS_COUPONS = "DROP TABLE `couponsdb`.`customers_vs_coupons`";
37
38
39 public static void getConfiguration() {
40 Config config = Config.readConfig();
41 //"jdbc:mysql://localhost:3306?createDatabaseIfNotExist=FALSE&useTimezone=TRUE&serverTimezone=Asia/Jerusalem"
42 url = "jdbc:mysql://"+config.getSqlConnectionString()+
43 "?createDatabaseIfNotExist"+(config.isCreateIfNotExists()?"TRUE":"FALSE")+
44 "&useTimezone="+(config.isUseTimeZone()?"TRUE":"FALSE")+
45 "&serverTimezone="+config.getServerTimeZone();
46 username = config.getUserName();
47 password = config.getUserPassword();
48 CREATE_DB = "CREATE DATABASE "+config.getDBname();
49 DROP_DB = "DROP DATABASE "+config.getDBname();
50 }
51
52 public static void createDataBase() throws SQLException {
53 DBUtils.runQuery(CREATE_DB);
54 }
55
56 public static void createDBWithTables() throws SQLException{
57 DBUtils.runQuery(CREATE_DB);
58 DBUtils.runQuery(CREATE_TABLE_CATEGORIES);
59 addCategories();
60 DBUtils.runQuery(CREATE_TABLE_COMPANIES);
61 DBUtils.runQuery(CREATE_TABLE_COUPONS);
62 DBUtils.runQuery(CREATE_TABLE_CUSTOMERS);
63 DBUtils.runQuery(CREATE_TABLE_CUSTOMERS_VS_COUPONS);
64 }
65
66 public static void dropDataBase() throws SQLException {
67 DBUtils.runQuery(DROP_DB);
68 }
69
70
71
72 public static void createTableCategories() throws SQLException {
73 DBUtils.runQuery(CREATE_TABLE_CATEGORIES);
74 addCategories();
75
76
77
78 }
79 public static void addCategories() throws SQLException {
80 //create an empty map. Int as key, object as value.
81 //בצורה הזאת אנחנו לא תלויים בבסיס הנתונים. כלומר אם הוא יוחלף זה לא יפריע לנו
82 //אנו שואפים שב-DBDAO נתעסק רק בדברים שקשורים לתוכנה שלנו, ולא בבסיס הנתונים
83 //יש לנו את מחלקת DBUtils, שהיא מתעסקת לנו עם בסיס הנתונים והתפקיד שלה זה לפתוח קונקשיין ולהכין את הסטייטמנט
84 //Loose-coupling
85 Map<Integer, Object> params1 = new HashMap<>();
86 params1.put(1, Category.ELECTRICITY.getCategoryId());
87 params1.put(2, Category.ELECTRICITY);
88 DBUtils.runQuery(ADD_CATEGORIES, params1);
89 Map<Integer, Object> params2 = new HashMap<>();
90 params2.put(1, Category.FOOD.getCategoryId());
91 params2.put(2, Category.FOOD);
92 DBUtils.runQuery(ADD_CATEGORIES, params2);
93 Map<Integer, Object> params3 = new HashMap<>();
94 params3.put(1, Category.RESTAURANT.getCategoryId());
95 params3.put(2, Category.RESTAURANT);
96 DBUtils.runQuery(ADD_CATEGORIES, params3);
97 Map<Integer, Object> params4 = new HashMap<>();
98 params4.put(1, Category.VACATION.getCategoryId());
99 params4.put(2, Category.VACATION);
100 DBUtils.runQuery(ADD_CATEGORIES, params4);
101 }
102
103 public static void createTableCompanies() throws SQLException {
104 DBUtils.runQuery(CREATE_TABLE_COMPANIES);
105 }
106
107 public static void createTableCoupons() throws SQLException {
108 DBUtils.runQuery(CREATE_TABLE_COUPONS);
109 }
110
111 public static void createTableCustomers() throws SQLException {
112 DBUtils.runQuery(CREATE_TABLE_CUSTOMERS);
113 }
114
115 public static void createTableCustomersVsCoupons() throws SQLException {
116 DBUtils.runQuery(CREATE_TABLE_CUSTOMERS_VS_COUPONS);
117 }
118
119
120
121 public static void dropTableCategories() throws SQLException {
122 DBUtils.runQuery(DROP_TABLE_CATEGORIES_FOREIGN_KEY);
123 DBUtils.runQuery(DROP_TABLE_CATEGORIES);
124 }
125
126 public static void dropTableCompanies() throws SQLException {
127 DBUtils.runQuery(DROP_TABLE_COMPANIES_FOREIGN_KEY);
128 DBUtils.runQuery(DROP_TABLE_COMPANIES);
129 }
130
131 public static void dropTableCoupons() throws SQLException {
132 DBUtils.runQuery(DROP_TABLE_COUPONS_FOREIGN_KEYS);
133 DBUtils.runQuery(DROP_TABLE_COUPONS);
134 }
135
136 public static void dropTableCustomers() throws SQLException {
137 DBUtils.runQuery(DROP_TABLE_CUSTOMERS_FOREIGN_KEYS);
138 DBUtils.runQuery(DROP_TABLE_CUSTOMERS);
139 }
140
141 public static void dropTableCustomersVsCoupons() throws SQLException {
142 DBUtils.runQuery(DROP_TABLE_CUSTOMERS_VS_COUPONS);
143 }
144
145}
146