· 4 years ago · Mar 25, 2021, 10:00 AM
1import java.sql.*;
2import java.util.Scanner;
3
4public class Main {
5
6 public static void main(String[] args) throws SQLException {
7 String URL = "jdbc:mysql://localhost:3306/";
8 String DB = "hdipapdev";
9 String USERNAME = "postgres";
10 String PASSWORD = "postgres";
11
12 Connection connection = null;
13
14 try {
15 connection = DriverManager.getConnection(URL + DB, USERNAME, PASSWORD);
16 Statement statement = connection.createStatement();
17
18 statement.execute(
19 "CREATE TABLE IF NOT EXISTS modules(" +
20 "module_code VARCHAR UNIQUE, " +
21 "module_name VARCHAR," +
22 "PRIMARY KEY (module_code)" +
23 ")"
24 );
25
26 statement.execute(
27 "CREATE TABLE IF NOT EXISTS courses(" +
28 "course_code VARCHAR UNIQUE, " +
29 "course_name VARCHAR, " +
30 "module_1 VARCHAR, " +
31 "module_2 VARCHAR, " +
32 "module_3 VARCHAR, " +
33 "PRIMARY KEY (course_code)," +
34 "FOREIGN KEY (module_1) REFERENCES modules(module_code)," +
35 "FOREIGN KEY (module_2) REFERENCES modules(module_code)," +
36 "FOREIGN KEY (module_3) REFERENCES modules(module_code)" +
37 ")"
38 );
39
40 statement.execute(
41 "CREATE TABLE IF NOT EXISTS students(" +
42 "student_number INTEGER UNIQUE, " +
43 "name VARCHAR, " +
44 "address VARCHAR, " +
45 "course_code VARCHAR, " +
46 "PRIMARY KEY (student_number)," +
47 "FOREIGN KEY (course_code) REFERENCES courses(course_code)" +
48 ")"
49 );
50
51 Scanner scan = new Scanner(System.in);
52 String more = "y";
53
54 while (more.equals("y")) {
55 System.out.println("Press:");
56 System.out.println("M - to create module");
57 System.out.println("C - to create course");
58 System.out.println("S - to create student");
59 System.out.println("P - to print data");
60
61 switch (scan.nextLine()) {
62 case "M": {
63 System.out.println("Enter module code");
64 String moduleCode = scan.nextLine();
65
66 System.out.println("Enter module name");
67 String moduleName = scan.nextLine();
68
69 statement.execute("INSERT INTO modules VALUES('" + moduleCode + "','" + moduleName + "')");
70
71 System.out.println("Module created");
72 break;
73
74 }
75 case "C": {
76 System.out.println("Enter course code");
77 String courseCode = scan.nextLine();
78
79 System.out.println("Enter course name");
80 String courseName = scan.nextLine();
81
82 System.out.println("Enter first module code");
83 String firstModule = scan.nextLine();
84
85 System.out.println("Enter second module code");
86 String secondModule = scan.nextLine();
87
88 System.out.println("Enter third module code");
89 String thirdModule = scan.nextLine();
90
91 statement.execute("INSERT INTO courses VALUES('" + courseCode + "','" + courseName + "','" + firstModule + "','" + secondModule + "','" + thirdModule + "')");
92
93 System.out.println("Course created");
94 break;
95
96 }
97 case "S": {
98 System.out.println("Enter student number");
99 int studentNumber = scan.nextInt();
100 scan.nextLine();
101
102 System.out.println("Enter address");
103 String address = scan.nextLine();
104
105 System.out.println("Enter course code");
106 String courseCode = scan.nextLine();
107
108 statement.execute("INSERT INTO students VALUES(" + studentNumber + ",'" + address + "','" + courseCode + "')");
109
110 System.out.println("Student created");
111 break;
112
113 }
114 case "P": {
115 System.out.println("Enter course code");
116 String courseCode = scan.nextLine();
117
118 ResultSet course = statement.executeQuery("SELECT * FROM courses WHERE course_code = '" + courseCode + "'");
119 if (course.next()) {
120 String code = course.getString(1);
121 String name = course.getString(2);
122 String firstModule = course.getString(3);
123 String secondModule = course.getString(4);
124 String thirdModule = course.getString(5);
125
126 System.out.println("Course code: " + code);
127 System.out.println("Course name: " + name);
128 System.out.println("First module code: " + firstModule);
129 System.out.println("Second module code: " + secondModule);
130 System.out.println("Third module code: " + thirdModule);
131 }
132
133 ResultSet students = statement.executeQuery("SELECT * FROM students WHERE course_code = '" + courseCode + "'");
134
135 System.out.println("Students:");
136 while (students.next()) {
137 int number = students.getInt(1);
138 String address = students.getString(2);
139
140 System.out.println("Student number: " + number + ", address: " + address);
141 }
142
143 break;
144
145 }
146 default: {
147 System.out.println("Invalid command!");
148 }
149 }
150
151 System.out.println("Would you like to continue? (y/n)");
152 more = scan.nextLine();
153 more.toLowerCase();
154 }
155
156 } catch (Exception e) {
157 e.printStackTrace();
158 } finally {
159 if (connection != null)
160 connection.close();
161 }
162 }
163}
164