MvsdV1qg

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