zSWiEHw3

1package DB;
2
3
4
5import Beans.Config;
6
7import java.sql.SQLException;
8
9public class DatabaseManager {
10    //mysql:mysql-connector-java:8.0.23 : file->project structure->Libreries->+->maven-> link
11    //conection string for connection to the mysql/mariadb server
12    public static String url = "jdbc:mysql://localhost:3306?createDatabaseIfNotExist=FALSE&useTimezone=TRUE&serverTimezone=GMT"; //Asia/Jerusalem
13    public static String username = "root";
14    public static String password = "";
15
16    private static String CREATE_DB = "CREATE DATABASE couponsdb";
17    private static String DROP_DB = "DROP DATABASE couponsdb";
18
19    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)";
20    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)";
21    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))";
22    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` INT(30) NOT NULL, LAST_NAME INT(30) NOT NULL, `EMAIL` VARCHAR(30) NOT NULL, `PASSWORD` VARCHAR(30) NOT NULL)";
23    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))";
24
25    private static final String DROP_TABLE_CATEGORIES_FOREIGN_KEY = "ALTER TABLE `couponsdb`.`coupons` DROP FOREIGN KEY `coupons_ibfk_2`";
26    private static final String DROP_TABLE_CATEGORIES =  "DROP TABLE `couponsdb`.`categories`";
27    private static final String DROP_TABLE_COMPANIES = "DROP TABLE `couponsdb`.`companies`";
28    private static final String DROP_TABLE_COUPONS_FOREIGN_KEYS = "ALTER TABLE `couponsdb`.`customers_vs_coupons` DROP FOREIGN KEY `customers_vs_coupons_ibfk_2`;";
29    private static final String DROP_TABLE_COUPONS = " DROP TABLE `couponsdb`.`coupons`;";
30    private static final String DROP_TABLE_CUSTOMERS = "DROP TABLE `couponsdb`.`customers`";
31    private static final String DROP_TABLE_CUSTOMERS_VS_COUPONS = "DROP TABLE `couponsdb`.`customers_vs_coupons`";
32
33
34    public static void getConfiguration() {
35        Config config = Config.readConfig();
36        //"jdbc:mysql://localhost:3306?createDatabaseIfNotExist=FALSE&useTimezone=TRUE&serverTimezone=Asia/Jerusalem"
37        url = "jdbc:mysql://"+config.getSqlConnectionString()+
38                "?createDatabaseIfNotExist"+(config.isCreateIfNotExists()?"TRUE":"FALSE")+
39                "&useTimezone="+(config.isUseTimeZone()?"TRUE":"FALSE")+
40                "&serverTimezone="+config.getServerTimeZone();
41        username = config.getUserName();
42        password = config.getUserPassword();
43        CREATE_DB = "CREATE DATABASE "+config.getDBname();
44        DROP_DB = "DROP DATABASE "+config.getDBname();
45    }
46
47    public static void createDataBase() throws SQLException {
48        DBUtils.runQuery(CREATE_DB);
49    }
50
51    public static void createDBWithTables() throws SQLException{
52        DBUtils.runQuery(CREATE_DB);
53        DBUtils.runQuery(CREATE_TABLE_CATEGORIES);
54        DBUtils.runQuery(CREATE_TABLE_COMPANIES);
55        DBUtils.runQuery(CREATE_TABLE_COUPONS);
56        DBUtils.runQuery(CREATE_TABLE_CUSTOMERS);
57        DBUtils.runQuery(CREATE_TABLE_CUSTOMERS_VS_COUPONS);
58    }
59
60    public static void dropDataBase() throws SQLException {
61        DBUtils.runQuery(DROP_DB);
62    }
63
64
65
66    public static void createTableCategories() throws SQLException {
67        DBUtils.runQuery(CREATE_TABLE_CATEGORIES);
68    }
69
70    public static void createTableCompanies() throws SQLException {
71        DBUtils.runQuery(CREATE_TABLE_COMPANIES);
72    }
73
74    public static void createTableCoupons() throws SQLException {
75        DBUtils.runQuery(CREATE_TABLE_COUPONS);
76    }
77
78    public static void createTableCustomers() throws SQLException {
79        DBUtils.runQuery(CREATE_TABLE_CUSTOMERS);
80    }
81
82    public static void createTableCustomersVsCoupons() throws SQLException {
83        DBUtils.runQuery(CREATE_TABLE_CUSTOMERS_VS_COUPONS);
84    }
85
86
87
88    public static void dropTableCategories() throws SQLException {
89        DBUtils.runQuery(DROP_TABLE_CATEGORIES_FOREIGN_KEY);
90        DBUtils.runQuery(DROP_TABLE_CATEGORIES);
91    }
92
93    public static void dropTableCompanies() throws SQLException {
94        DBUtils.runQuery(DROP_TABLE_COMPANIES);
95    }
96
97    public static void dropTableCoupons() throws SQLException {
98        DBUtils.runQuery(DROP_TABLE_COUPONS_FOREIGN_KEYS);
99        DBUtils.runQuery(DROP_TABLE_COUPONS);
100    }
101
102    public static void dropTableCustomers() throws SQLException {
103        DBUtils.runQuery(DROP_TABLE_CUSTOMERS);
104    }
105
106    public static void dropTableCustomersVsCoupons() throws SQLException {
107        DBUtils.runQuery(DROP_TABLE_CUSTOMERS_VS_COUPONS);
108    }
109
110}