hjam73dh

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