zR3P45Z8

1package DB;
2
3import Beans.Category;
4
5import java.util.HashMap;
6import java.util.Map;
7
8public class DatabaseManager {
9    public static String url = "jdbc:mysql://localhost:3306?createDatabaseIfNotExist=FALSE&useTimezone=TRUE&serverTimezone=GMT"; //Asia/Jerusalem
10    public static String username = "root";
11    public static String password = "";
12
13    private static final String CREATE_DB = "CREATE DATABASE coupon_master";
14    private static final String DROP_DB = "DROP DATABASE coupon_master";
15
16    public static final String CREATE_TABLE_CATEGORIES = "CREATE TABLE IF NOT EXISTS `coupon_master`.`categories` (`ID` int(11) NOT NULL AUTO_INCREMENT,`NAME` varchar(50) NOT NULL, PRIMARY KEY (`ID`))";
17    private static final String ADD_CATEGORY = "INSERT INTO `project`.`categories`(`NAME`) VALUES (?)";
18    public static final String DROP_TABLE_CATEGORIES = "DROP TABLE `coupon_master`.`categories` IF EXISTS";
19
20    public static final String CREATE_TABLE_COMPANIES = "CREATE TABLE IF NOT EXISTS `coupon_master`.`companies` (`ID` int(11) NOT NULL AUTO_INCREMENT, `NAME` varchar(50) NOT NULL, `EMAIL` varchar(50) NOT NULL, " +
21            "`PASSWORD` varchar(50) NOT NULL, PRIMARY KEY (`ID`))";
22    public static final String DROP_TABLE_COMPANIES = "DROP TABLE `coupon_master`.`companies` IF EXISTS";
23
24    public static final String CREATE_TABLE_COUPONS = "CREATE TABLE IF NOT EXISTS `coupon_master`.`coupons` (`ID` int(11) NOT NULL AUTO_INCREMENT, `COMPANY_ID` int(11) DEFAULT NULL, `CATEGORY_ID` int(11) NOT NULL, " +
25            "`TITLE` varchar(50) NOT NULL, `DESCRIPTION` varchar(200) NOT NULL, `START_DATE` date NOT NULL, `END_DATE` date NOT NULL, " +
26            "`AMOUNT` int(11) NOT NULL,`PRICE` double NOT NULL, `IMAGE` varchar(200) NOT NULL, PRIMARY KEY (`ID`), " +
27            " FOREIGN KEY (`COMPANY_ID`) REFERENCES `companies` (`ID`), FOREIGN KEY (`CATEGORY_ID`) REFERENCES `categories` (`ID`))";
28    public static final String DROP_TABLE_COUPONS = "DROP TABLE `coupon_master`.`coupons` IF EXISTS";
29
30    public static final String CREATE_TABLE_CUSTOMERS = "CREATE TABLE IF NOT EXISTS  `coupon_master`.`customers` (`ID` int(11) NOT NULL AUTO_INCREMENT, `FIRST_NAME` varchar(50) NOT NULL,`LAST_NAME` varchar(50) NOT NULL, " +
31            "`EMAIL` varchar(50) NOT NULL, `PASSWORD` varchar(50) NOT NULL, PRIMARY KEY (`ID`))";
32    public static final String DROP_TABLE_CUSTOMERS = "DROP TABLE `coupon_master`.`customers` IF EXISTS";
33
34    public static final String CREATE_TABLE_CUSTOMERS_VS_COUPONS = "CREATE TABLE IF NOT EXISTS `coupon_master`.`customers_vs_coupons` (`CUSTOMER_ID` int(11) NOT NULL, `COUPON_ID` int(11) NOT NULL, " +
35            "PRIMARY KEY (`CUSTOMER_ID`,`COUPON_ID`), FOREIGN KEY (`CUSTOMER_ID`) REFERENCES `customers` (`ID`), FOREIGN KEY (`COUPON_ID`) REFERENCES `coupons` (`ID`))";
36    public static final String DROP_TABLE_CUSTOMERS_VS_COUPONS = "DROP TABLE `coupon_master`.`customers_vs_coupons` IF EXISTS";
37
38
39    public static boolean createDataBase() {
40        return DBUtilities.runQuery(CREATE_DB);
41    }
42
43    public static boolean dropDataBase() {
44        return DBUtilities.runQuery(DROP_DB);
45    }
46
47
48    public static boolean createTableCategories() {
49        boolean check = DBUtilities.runQuery(CREATE_TABLE_CATEGORIES);
50        Map<Integer, Object> parameters = new HashMap<>();
51        for (int counter = 0; counter < Category.categories.length; counter += 1) {
52            parameters.put(1, Category.categories[counter]);
53            DBUtilities.runQueryWithMap(ADD_CATEGORY, parameters);
54        }
55        return check;
56    }
57
58    public static boolean dropTableCategories() {
59        return DBUtilities.runQuery(DROP_TABLE_CATEGORIES);
60    }
61
62
63    public static boolean createTableCompanies() {
64        return DBUtilities.runQuery(CREATE_TABLE_COMPANIES);
65    }
66
67    public static boolean dropTableCompanies() {
68        return DBUtilities.runQuery(DROP_TABLE_COMPANIES);
69    }
70
71
72    public static boolean createTableCoupons() {
73        return DBUtilities.runQuery(CREATE_TABLE_COUPONS);
74    }
75
76    public static boolean dropTableCoupons() {
77        return DBUtilities.runQuery(DROP_TABLE_COUPONS);
78    }
79
80
81    public static boolean createTableCustomers() {
82        return DBUtilities.runQuery(CREATE_TABLE_CUSTOMERS);
83    }
84
85    public static boolean dropTableCustomers() {
86        return DBUtilities.runQuery(DROP_TABLE_CUSTOMERS);
87    }
88
89
90    public static boolean createTableCustomersVsCoupons() {
91        return DBUtilities.runQuery(CREATE_TABLE_CUSTOMERS_VS_COUPONS);
92    }
93
94    public static boolean dropTableCustomersVsCoupons() {
95        return DBUtilities.runQuery(DROP_TABLE_CUSTOMERS_VS_COUPONS);
96    }
97}