GVSSihJm

1package Problem1;
2
3import java.sql.*;
4import java.util.Properties;
5
6public class InitialStep {
7    static final String URL = "jdbc:mysql://localhost:3306/";
8    static final String DB = "minions_db";
9    static final String USER = "root";
10    static final String PASS = "1234";
11
12    public static void main(String[] args) throws SQLException {
13        Properties props = new Properties();
14        props.setProperty("user", USER);
15        props.setProperty("password", PASS);
16
17        Connection conn = DriverManager.getConnection(URL, props);
18        Statement statement = conn.createStatement();
19
20        //Create database
21        String query = "DROP DATABASE IF EXISTS minions_db";
22        statement.executeUpdate(query);
23
24        query = "CREATE DATABASE minions_db";
25        statement.executeUpdate(query);
26
27        conn = DriverManager.getConnection(URL + DB, USER, PASS);
28        statement = conn.createStatement();
29
30        //Create tables
31        query = "CREATE TABLE towns(\n" +
32                "id INT PRIMARY KEY AUTO_INCREMENT,\n" +
33                "name VARCHAR(255) NOT NULL,\n" +
34                "country VARCHAR(100) NOT NULL\n" +
35                ")";
36        statement.executeUpdate(query);
37
38        query = "CREATE TABLE minions (\n" +
39                "id INT PRIMARY KEY AUTO_INCREMENT,\n" +
40                "name VARCHAR(255) NOT NULL,\n" +
41                "age INT,\n" +
42                "town_id INT,\n" +
43                "CONSTRAINT fk_minion_towns\n" +
44                "FOREIGN KEY (town_id)\n" +
45                "REFERENCES towns(id)\n" +
46                ")";
47        statement.executeUpdate(query);
48
49        query = "CREATE TABLE villains (\n" +
50                "id INT PRIMARY KEY AUTO_INCREMENT,\n" +
51                "name VARCHAR(255) NOT NULL,\n" +
52                "evilness_factor ENUM('good', 'bad', 'evil', 'super evil')\n" +
53                ")";
54        statement.executeUpdate(query);
55
56        query = "CREATE TABLE minions_villains(\n" +
57                "minion_id INT,\n" +
58                "villain_id INT,\n" +
59                "CONSTRAINT fk_mv_minions\n" +
60                "FOREIGN KEY (minion_id)\n" +
61                "REFERENCES minions(id),\n" +
62                "CONSTRAINT fk_mv_villains\n" +
63                "FOREIGN KEY (villain_id)\n" +
64                "REFERENCES villains(id),\n" +
65                "PRIMARY KEY (minion_id, villain_id)\n" +
66                ")";
67        statement.executeUpdate(query);
68
69        //Insert data
70        query = "INSERT INTO towns(name, country)\n" +
71                "VALUES('town1', 'country1'),('town2', 'country2'),\n" +
72                "('town3', 'country3'),('town4', 'country4'),\n" +
73                "('town5', 'country5')";
74        statement.executeUpdate(query);
75
76        query = "INSERT INTO minions(name, age, town_id)\n" +
77                "VALUES('min1', 15, 2),('min2', 25, 3),('min3', 19, 5),\n" +
78                "('min4', 23, 4),('min5', 50, 1)";
79        statement.executeUpdate(query);
80
81        query = "INSERT INTO villains(name, evilness_factor)\n" +
82                "VALUES('vil1', 'bad'),('vil2', 'super evil'),\n" +
83                "('vil3', 'evil'),('vil4', 'good'),\n" +
84                "('vil5', 'evil')";
85        statement.executeUpdate(query);
86
87        query = "INSERT INTO minions_villains(minion_id, villain_id)\n" +
88                "VALUES(2, 1),(3, 5),(1, 3),(5, 2),(4, 4),(1, 4),(3, 2),(2, 2),(1, 2),(4, 2)";
89        statement.executeUpdate(query);
90
91
92        //Close connections
93        statement.closeOnCompletion();
94        conn.close();
95    }
96}