ScvMRTkB

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 = "DROP TABLE `couponsdb`.`categories`";
26    private static final String DROP_TABLE_COMPANIES = "DROP TABLE `couponsdb`.`companies`";
27    private static final String DROP_TABLE_COUPONS = "ALTER TABLE `couponsdb`.`customers_vs_coupons` DROP FOREIGN KEY `customers_vs_coupons_ibfk_2`;ALTER TABLE `couponsdb`.`coupons` DROP FOREIGN KEY `coupons_ibfk_1`;ALTER TABLE `couponsdb`.`coupons` DROP FOREIGN KEY `coupons_ibfk_2`; DROP TABLE `couponsdb`.`coupons`";
28    private static final String DROP_TABLE_CUSTOMERS = "DROP TABLE `couponsdb`.`customers`";
29    private static final String DROP_TABLE_CUSTOMERS_VS_COUPONS = "DROP TABLE `couponsdb`.`customers_vs_coupons`";
30
31
32    public static void getConfiguration() {
33        Config config = Config.readConfig();
34        //"jdbc:mysql://localhost:3306?createDatabaseIfNotExist=FALSE&useTimezone=TRUE&serverTimezone=Asia/Jerusalem"
35        url = "jdbc:mysql://"+config.getSqlConnectionString()+
36                "?createDatabaseIfNotExist"+(config.isCreateIfNotExists()?"TRUE":"FALSE")+
37                "&useTimezone="+(config.isUseTimeZone()?"TRUE":"FALSE")+
38                "&serverTimezone="+config.getServerTimeZone();
39        username = config.getUserName();
40        password = config.getUserPassword();
41        CREATE_DB = "CREATE DATABASE "+config.getDBname();
42        DROP_DB = "DROP DATABASE "+config.getDBname();
43    }
44
45    public static void createDataBase() throws SQLException {
46        DBUtils.runQuery(CREATE_DB);
47    }
48
49    public static void createDBWithTables() throws SQLException{
50        DBUtils.runQuery(CREATE_DB);
51        DBUtils.runQuery(CREATE_TABLE_CATEGORIES);
52        DBUtils.runQuery(CREATE_TABLE_COMPANIES);
53        DBUtils.runQuery(CREATE_TABLE_COUPONS);
54        DBUtils.runQuery(CREATE_TABLE_CUSTOMERS);
55        DBUtils.runQuery(CREATE_TABLE_CUSTOMERS_VS_COUPONS);
56    }
57
58    public static void dropDataBase() throws SQLException {
59        DBUtils.runQuery(DROP_DB);
60    }
61
62
63
64    public static void createTableCategories() throws SQLException {
65        DBUtils.runQuery(CREATE_TABLE_CATEGORIES);
66    }
67
68    public static void createTableCompanies() throws SQLException {
69        DBUtils.runQuery(CREATE_TABLE_COMPANIES);
70    }
71
72    public static void createTableCoupons() throws SQLException {
73        DBUtils.runQuery(CREATE_TABLE_COUPONS);
74    }
75
76    public static void createTableCustomers() throws SQLException {
77        DBUtils.runQuery(CREATE_TABLE_CUSTOMERS);
78    }
79
80    public static void createTableCustomersVsCoupons() throws SQLException {
81        DBUtils.runQuery(CREATE_TABLE_CUSTOMERS_VS_COUPONS);
82    }
83
84
85
86    public static void dropTableCategories() throws SQLException {
87        DBUtils.runQuery(DROP_TABLE_CATEGORIES);
88    }
89
90    public static void dropTableCompanies() throws SQLException {
91        DBUtils.runQuery(DROP_TABLE_COMPANIES);
92    }
93
94    public static void dropTableCoupons() throws SQLException {
95        DBUtils.runQuery(DROP_TABLE_COUPONS);
96    }
97
98    public static void dropTableCustomers() throws SQLException {
99        DBUtils.runQuery(DROP_TABLE_CUSTOMERS);
100    }
101
102    public static void dropTableCustomersVsCoupons() throws SQLException {
103        DBUtils.runQuery(DROP_TABLE_CUSTOMERS_VS_COUPONS);
104    }
105    
106}