RV2xnf6S

1package DB;
2
3public enum SQL {
4    URL(new StringBuffer("jdbc:mysql://localhost:3306?createDatabaseIfNotExist=FALSE&useTimezone=TRUE&serverTimezone=UTC")),
5    USERNAME(new StringBuffer("root")),
6    PASSWORD(new StringBuffer("")),
7    DB_NAME(new StringBuffer("COUPON_MS")),
8    COMPANIES(new StringBuffer("companies")),
9    CUSTOMERS(new StringBuffer("customers")),
10    CATEGORIES(new StringBuffer("categories")),
11    COUPONS(new StringBuffer("coupons")),
12    CUSTOMERS_VS_COUPONS(new StringBuffer("customers_vs_coupons"));
13    private StringBuffer tableName;
14
15    SQL(StringBuffer tableName) {
16        this.tableName = tableName;
17    }
18
19    public String getText() {
20        return tableName.toString();
21    }
22}
23==================================================================================================
24package DB;
25
26import java.sql.SQLException;
27
28public class DatabaseManager {
29    public static final String url = SQL.URL.getText();
30    public static final String username = SQL.USERNAME.getText();
31    public static final String password = SQL.PASSWORD.getText();
32
33    private static final String CREATE_SCHEMA = "CREATE SCHEMA IF NOT EXISTS " + SQL.DB_NAME.getText();
34
35    private static final String CREATE_TABLE_COMPANIES = "CREATE TABLE IF NOT EXISTS " + SQL.DB_NAME.getText() + "." + SQL.COMPANIES.getText() + " " +
36            "(`ID` INT NOT NULL PRIMARY KEY AUTO_INCREMENT," +
37            " `NAME` VARCHAR(30) NOT NULL," +
38            " `EMAIL` VARCHAR(30) NOT NULL," +
39            " `PASSWORD` VARCHAR(30) NOT NULL)";
40    private static final String CREATE_TABLE_CUSTOMERS = "CREATE TABLE IF NOT EXISTS " + SQL.DB_NAME.getText() + "." + SQL.CUSTOMERS.getText() + " " +
41            "(`ID` INT NOT NULL PRIMARY KEY AUTO_INCREMENT," +
42            " `FIRST_NAME` VARCHAR(30) NOT NULL," +
43            " `LAST_NAME` VARCHAR(30) NOT NULL," +
44            " `EMAIL` VARCHAR(30) NOT NULL," +
45            " `PASSWORD` VARCHAR(30) NOT NULL)";
46    private static final String CREATE_TABLE_CATEGORIES = "CREATE TABLE IF NOT EXISTS " + SQL.DB_NAME.getText() + "." + SQL.CATEGORIES.getText() + " " +
47            "(`ID` INT NOT NULL PRIMARY KEY AUTO_INCREMENT," +
48            " `NAME` VARCHAR(30) NOT NULL)";
49    private static final String CREATE_TABLE_COUPONS = "CREATE TABLE IF NOT EXISTS " + SQL.DB_NAME.getText() + "." + SQL.COUPONS.getText() + " " +
50            "(`ID` INT NOT NULL PRIMARY KEY AUTO_INCREMENT," +
51            " `COMPANY_ID` INT NOT NULL, FOREIGN KEY (COMPANY_ID) REFERENCES " + SQL.COMPANIES.getText() + "(ID)," +
52            " `CATEGORY_ID` INT NOT NULL, FOREIGN KEY (CATEGORY_ID) REFERENCES " + SQL.CATEGORIES.getText() + "(ID)," +
53            " `TITLE` VARCHAR(50) NOT NULL," +
54            " `DESCRIPTION` VARCHAR(200) NOT NULL," +
55            " `START_DATE` DATE NOT NULL," +
56            " `END_DATE` DATE NOT NULL," +
57            " `AMOUNT` INT NOT NULL," +
58            " `PRICE` DOUBLE NOT NULL," +
59            " `IMAGE` VARCHAR(200) NOT NULL)";
60    private static final String CREATE_TABLE_CUSTOMERS_VS_COUPONS = "CREATE TABLE IF NOT EXISTS " + SQL.DB_NAME.getText() + "." + SQL.CUSTOMERS_VS_COUPONS.getText() + " " +
61            "(`CUSTOMER_ID` INT NOT NULL," + // FOREIGN KEY (CUS_ID) REFERENCES " + SQL.CUSTOMERS.getText() + "(ID)," +
62            " `COUPON_ID` INT NOT NULL," + // FOREIGN KEY (CON_ID) REFERENCES " + SQL.COUPONS.getText() + "(ID)," +
63            //" PRIMARY KEY AUTO_INCREMENT (CUS_ID, CON_ID)," +
64            " PRIMARY KEY (CUSTOMER_ID, COUPON_ID)," +
65            " UNIQUE INDEX (CUSTOMER_ID, COUPON_ID)," +
66            " FOREIGN KEY (CUSTOMER_ID) REFERENCES " + SQL.CUSTOMERS.getText() + "(ID)," +
67            " FOREIGN KEY (COUPON_ID) REFERENCES " + SQL.COUPONS.getText() + "(ID))";
68
69    public static void createAll() throws SQLException {
70        DBUtils.runQuery(CREATE_SCHEMA);
71        DBUtils.runQuery(CREATE_TABLE_COMPANIES);
72        DBUtils.runQuery(CREATE_TABLE_CUSTOMERS);
73        DBUtils.runQuery(CREATE_TABLE_CATEGORIES);
74        DBUtils.runQuery(CREATE_TABLE_COUPONS);
75        DBUtils.runQuery(CREATE_TABLE_CUSTOMERS_VS_COUPONS);
76    }
77}