dTup3a8f

1package com.kapitsa;
2//Created by Luladik on 2/20/2016.
3
4import java.sql.Connection;
5import java.sql.DriverManager;
6import java.sql.SQLException;
7import java.sql.Statement;
8
9public class DBManager {
10
11    private static final String URL = "jdbc:mysql://localhost:3306/food?autoReconnect=true&useSSL=false";
12    private static final String USERNAME = "root";
13    private static final String PASSWORD = "root";
14    private static final String DRIVER = "com.mysql.jdbc.Driver";
15
16    private static Connection getDBConnection() {
17        Connection dbConnection;
18        try {
19            Class.forName(DRIVER);
20            System.out.println("Driver connected");
21        } catch (ClassNotFoundException e) {
22            System.out.println(e.getMessage());
23        }
24        try {
25            dbConnection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
26            System.out.println("Successfully Connected");
27            return dbConnection;
28        } catch (SQLException e) {
29            System.out.println(e.getMessage());
30        }
31        return null;
32    }
33
34    public static void init() throws SQLException {
35        String createDb = "CREATE DATABASE IF NOT EXISTS shopmanager";
36        String createShops = "CREATE TABLE IF NOT EXISTS Shops (" +
37                                "id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT," +
38                                "Title VARCHAR(30) NOT NULL" +
39                                ");";
40        String createCategories = "CREATE TABLE IF NOT EXISTS Categories (" +
41                                "id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT," +
42                                "shop_id INT UNSIGNED NOT NULL," +
43                                "Title VARCHAR(20) NOT NULL," +
44                                "Description VARCHAR(40) NOT NULL," +
45                                "FOREIGN KEY(shop_id) REFERENCES Shops(id)" +
46                                ");";
47        String createItems = "CREATE TABLE IF NOT EXISTS Items (" +
48                                "id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT," +
49                                "category_id INT UNSIGNED NOT NULL," +
50                                "Title VARCHAR(30) NOT NULL," +
51                                "Price DECIMAL(20) NOT NULL," +
52                                "Status ENUM('Available', 'Absent', 'Expected')," +
53                                "FOREIGN KEY(category_id) REFERENCES Categories(id)" +
54                                ");";
55
56        try(final Statement statement = getDBConnection().createStatement()) {
57            statement.execute(createDb);
58            statement.execute("USE shopmanager;");
59            statement.execute(createShops);
60            statement.execute(createCategories);
61            statement.execute(createItems);
62            System.out.println("Tables created successfully");
63
64        } catch (Exception e) {
65            e.printStackTrace();
66            System.out.println("Error when creating tables");
67        }
68    }
69}