· 7 years ago · Jan 12, 2019, 08:58 PM
1public void initDatabase() {
2 try {
3 statement.executeUpdate("drop index if exists ATTENDANCE.Relationship_4_FK;\n"
4 + "\n"
5 + "drop index if exists ATTENDANCE.ATTENDANCE_PK;\n"
6 + "\n"
7 + "drop table if exists ATTENDANCE;\n"
8 + "\n"
9 + "drop index if exists BILL.\"BILL-PROJECT_FK\";\n"
10 + "\n"
11 + "drop index if exists BILL.\"EMP-BILL_FK\";\n"
12 + "\n"
13 + "drop index if exists BILL.BILL_PK;\n"
14 + "\n"
15 + "drop table if exists BILL;\n"
16 + "\n"
17 + "drop index if exists \"EMP-PROJECT\".\"EMP-PROJECT2_FK\";\n"
18 + "\n"
19 + "drop index if exists \"EMP-PROJECT\".\"EMP-PROJECT_FK\";\n"
20 + "\n"
21 + "drop index if exists \"EMP-PROJECT\".\"EMP-PROJECT_PK\";\n"
22 + "\n"
23 + "drop table if exists \"EMP-PROJECT\";\n"
24 + "\n"
25 + "drop index if exists EMPLOYEE.EMPLOYEE_PK;\n"
26 + "\n"
27 + "drop table if exists EMPLOYEE;\n"
28 + "\n"
29 + "drop index if exists HOLIDAY.\"EMP-HOL_FK\";\n"
30 + "\n"
31 + "drop index if exists HOLIDAY.HOLIDAY_PK;\n"
32 + "\n"
33 + "drop table if exists HOLIDAY;\n"
34 + "\n"
35 + "drop index if exists PROJECT.PROJECT_PK;\n"
36 + "\n"
37 + "drop table if exists PROJECT;\n"
38 + "\n"
39 + "drop index if exists TASK.\"EMP-TASK_FK\";\n"
40 + "\n"
41 + "drop index if exists TASK.\"PROJECT-TASK_FK\";\n"
42 + "\n"
43 + "drop index if exists TASK.TASK_PK;\n"
44 + "\n"
45 + "drop table if exists TASK;");
46 statement.executeUpdate("CREATE TABLE \"EMPLOYEE\" (\n"
47 + "\"emp_id\" INTEGER NOT NULL,\n"
48 + "\"isManager\" INTEGER NOT NULL,\n"
49 + "\"name\" TEXT NOT NULL,\n"
50 + "\"surname\" TEXT NOT NULL,\n"
51 + "\"wage\" TEXT NOT NULL,\n"
52 + "\"adress\" TEXT NOT NULL,\n"
53 + "\"email\" TEXT NOT NULL,\n"
54 + "\"phone\" TEXT NOT NULL,\n"
55 + "\"nationalIdNumber\" INTEGER NOT NULL,\n"
56 + "\"isStillEmployee\" TEXT NOT NULL,\n"
57 + "\"usedHolidays\" INTEGER NOT NULL,\n"
58 + "\"unUsedHolidays\" INTEGER NOT NULL,\n"
59 + "\"loginID\" TEXT NOT NULL,\n"
60 + "\"password\" INTEGER NOT NULL,\n"
61 + "PRIMARY KEY (\"emp_id\") ,\n"
62 + "CONSTRAINT \"fk_EMPLOYEE_PROJECT_1\" FOREIGN KEY () REFERENCES \"PROJECT\" (),\n"
63 + "CONSTRAINT \"fk_EMPLOYEE_ATTENDANCE_1\" FOREIGN KEY () REFERENCES \"ATTENDANCE\" (),\n"
64 + "CONSTRAINT \"fk_EMPLOYEE_HOLIDAY_1\" FOREIGN KEY () REFERENCES \"HOLIDAY\" (),\n"
65 + "CONSTRAINT \"fk_EMPLOYEE_BILL_1\" FOREIGN KEY () REFERENCES \"BILL\" (),\n"
66 + "CONSTRAINT \"fk_EMPLOYEE_TASK_1\" FOREIGN KEY () REFERENCES \"TASK\" ()\n"
67 + ");");
68 statement.executeUpdate("\n"
69 + "CREATE TABLE \"PROJECT\" (\n"
70 + "\"project_id\" INTEGER NOT NULL,\n"
71 + "\"projectName\" TEXT NOT NULL,\n"
72 + "\"description\" TEXT NOT NULL,\n"
73 + "\"budget\" INTEGER NOT NULL,\n"
74 + "\"startOfProject\" TEXT NOT NULL,\n"
75 + "\"deadlineOfProject\" TEXT NOT NULL,\n"
76 + "\"endOfProjectDate\" TEXT NOT NULL,\n"
77 + "\"isProjectDone\" INTEGER NOT NULL,\n"
78 + "\"commentProject\" TEXT NOT NULL,\n"
79 + "\"resposibleForProject\" TEXT NOT NULL,\n"
80 + "PRIMARY KEY (\"project_id\") ,\n"
81 + "CONSTRAINT \"fk_PROJECT_TASK_1\" FOREIGN KEY () REFERENCES \"TASK\" (),\n"
82 + "CONSTRAINT \"fk_PROJECT_BILL_1\" FOREIGN KEY () REFERENCES \"BILL\" ()\n"
83 + ");\n"
84 + "CREATE TABLE \"TASK\" (\n"
85 + "\"task_id\" INTEGER NOT NULL,\n"
86 + "\"nameOfTask\" TEXT NOT NULL,\n"
87 + "\"descriptionOfTask\" TEXT NOT NULL,\n"
88 + "\"isTaskDone\" INTEGER NOT NULL,\n"
89 + "\"commentTask\" TEXT NOT NULL,\n"
90 + "\"deadlineTask\" TEXT NOT NULL,\n"
91 + "\"responsibleForTask\" TEXT NOT NULL,\n"
92 + "PRIMARY KEY (\"task_id\") \n"
93 + ");\n"
94 + "CREATE TABLE \"BILL\" (\n"
95 + "\"bill_id\" INTEGER NOT NULL,\n"
96 + "\"subject\" TEXT NOT NULL,\n"
97 + "\"price\" REAL NOT NULL,\n"
98 + "\"mustPayUntil\" TEXT NOT NULL,\n"
99 + "\"typeOfPayment\" TEXT NOT NULL,\n"
100 + "\"isDonePayment\" INTEGER NOT NULL,\n"
101 + "\"dateOfCreation\" TEXT NOT NULL,\n"
102 + "\"paymentDay\" TEXT NOT NULL,\n"
103 + "\"isContractor\" INTEGER NOT NULL,\n"
104 + "PRIMARY KEY (\"bill_id\") \n"
105 + ");\n"
106 + "CREATE TABLE \"ATTENDANCE\" (\n"
107 + "\"attendance_id\" INTEGER NOT NULL,\n"
108 + "\"startOfWork\" TEXT NOT NULL,\n"
109 + "\"endOfWork\" TEXT NOT NULL,\n"
110 + "\"isOvertime\" INTEGER NOT NULL,\n"
111 + "\"isNationalHoliday\" INTEGER NOT NULL,\n"
112 + "PRIMARY KEY (\"attendance_id\") \n"
113 + ");\n"
114 + "CREATE TABLE \"HOLIDAY\" (\n"
115 + "\"holiday_id\" INTEGER NOT NULL,\n"
116 + "\"isApproved\" INTEGER NOT NULL,\n"
117 + "\"startHoliday\" TEXT NOT NULL,\n"
118 + "\"endHoliday\" TEXT NOT NULL,\n"
119 + "\"isPayd\" TEXT NOT NULL,\n"
120 + "\"isHealthHoliday\" INTEGER NOT NULL,\n"
121 + "PRIMARY KEY (\"holiday_id\") \n"
122 + ");");
123
124 ResultSet rs = statement.executeQuery("select zam_id from ZAM;");
125 while (rs.next()) {
126 System.out.println(rs.getInt("zam_id"));
127 }
128
129 } catch (Exception x) {
130 x.printStackTrace();
131 System.err.println("Error when building database tables.");
132 }
133 }