CFW93Xyp

1package SQL;
2
3import Beans.Category;
4
5import java.util.HashMap;
6import java.util.Map;
7
8public class DateBaseManager {
9
10    //Data base connection details:
11    public static final String URL = "jdbc:mysql://localhost:3306?createDatabaseIfNotExist=FALSE";
12    public static final String USER_NAME = "root";
13    public static final String PASSWORD = "12345678";
14
15    private static final String CREATE_DB = "CREATE SCHEMA if not exists Coupons";
16    private static final String ADD_CATEGORY = "INSERT INTO `Coupons`.`categories` (`name`) VALUES (?)";
17
18    private static final String CREATE_TABLE_COMPANIES = "CREATE TABLE if not exists `Coupons`.`companies` " +
19            "(`id` INT NOT NULL AUTO_INCREMENT," +
20            "`name` VARCHAR(20) NOT NULL," +
21            "`email` VARCHAR(50) NOT NULL," +
22            "`password` VARCHAR(20) NOT NULL," +
23            "`PRIMARY KEY (`id`));" ;
24
25    private static final String CREATE_TABLE_CUSTOMERS = "CREATE TABLE if not exists `Coupons`.`customers`" +
26            "(`id` INT NOT NULL AUTO_INCREMENT," +
27            "`first_name` VARCHAR(20) NOT NULL," +
28            "`last_name` VARCHAR(20) NOT NULL," +
29            "`email` VARCHAR(50) NOT NULL," +
30            "`password` VARCHAR(20) NOT NULL," +
31            "`PRIMARY KEY (`id`));" ;
32
33    private static final String CREATE_TABLE_CATEGORIES = "CREATE TABLE if not exists `Coupons`.`categories`" +
34            "(`id` INT NOT NULL AUTO_INCREMENT," +
35            "`name` VARCHAR(20) NOT NULL," +
36            "`PRIMARY KEY (`id`));" ;
37
38    private static final String CREATE_TABLE_COUPONS = "CREATE TABLE if not exists `Coupons`.`coupons` " +
39            "(`id` INT NOT NULL AUTO_INCREMENT," +
40            "`company_id` INT NOT NULL," +
41            "`category_id` INT NOT NULL," +
42            "`title` VARCHAR(50) NOT NULL," +
43            "`description` VARCHAR(200) NOT NULL," +
44            "`start_date` DATE NOT NULL," +
45            "`end_date` DATE NOT NULL," +
46            "`amount` INT NOT NULL," +
47            "`price` DOUBLE NOT NULL," +
48            "`image` VARCHAR(150) NOT NULL," +
49            "PRIMARY KEY (`id`)," +
50            "FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE," +
51            "FOREIGN KEY(category_id) REFERENCES categories(id) ON DELETE CASCADE);";
52
53    private static final String CREATE_TABLE_CUSTOMERS_VS_COUPONS = "CREATE TABLE if not exists `Coupons`.`categories_vs_coupons` " +
54            "(`customer_id` INT NOT NULL," +
55            "`coupon_id` INT NOT NULL," +
56            "PRIMARY KEY (`customer_id`)," +
57            "PRIMARY KEY (`coupon_id`)," +
58            "FOREIGN KEY(customer_id) REFERENCES customers(id) ON DELETE CASCADE," +
59            "FOREIGN KEY(coupon_id) REFERENCES coupons(id) ON DELETE CASCADE);";
60
61    public static void createDataBase(){
62        DbUtils.runQuery(CREATE_DB);
63    }
64
65    public static void createTables(){
66        DbUtils.runQuery(CREATE_TABLE_COMPANIES);
67        DbUtils.runQuery(CREATE_TABLE_CUSTOMERS);
68        DbUtils.runQuery(CREATE_TABLE_CATEGORIES);
69        DbUtils.runQuery(CREATE_TABLE_COUPONS);
70        DbUtils.runQuery(CREATE_TABLE_CUSTOMERS_VS_COUPONS);
71    }
72
73    public static void fillCategoriesTable(){
74        Category[] categories  = Category.values();
75        Map<Integer, Object> categoriesMap = new HashMap<>();
76        for (int index = 0; index < categories.length; index += 1){
77            categoriesMap.put(index + 1, String.valueOf(categories[index]));
78        }
79        DbUtils.runQuery(ADD_CATEGORY, categoriesMap);
80    }
81}
82