-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathjdbc.java
167 lines (142 loc) · 7.54 KB
/
jdbc.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
/*H***********************************************************************
* FILENAME : jdbc.java
*
* DESCRIPTION :
* Uses jdbc to produce different views of the airline database
*
* NOTES :
* For the user, password, db, and jdbc variables,
* you must enter your own personal information.
*
* Copyright 2018, Jacob Wilkins. All rights reserved.
*
* AUTHOR : Jacob Wilkins START DATE : 22 Apr 18
*
*H*/
import java.sql.*;
import java.util.Scanner;
final class mysql {
final static String user = "username";
final static String password = "password";
final static String db = "databaseName";
final static String jdbc = "connectionURL" + db + "?user=" + user + "&password=" + password;
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.jdbc.Driver").newInstance();
Connection con = DriverManager.getConnection(jdbc);
boolean bool = true;
Scanner in = new Scanner(System.in);
int a;
while (bool) {
System.out.println("1. View of Unassigned Pilot Flights");
System.out.println("2. View of Due For Maintenance Planes");
System.out.println("3. View of Pilot Fly Assignments");
System.out.println("4. View of Pilot FlightLegs Count");
System.out.println("5. Quit");
System.out.print("Enter a Number: ");
a = in .nextInt();
System.out.print("\n");
int count = 0;
ResultSet rs = null;
Statement st = null;
Statement stmt = null;
switch (a) {
case 1:
count = 0;
st = con.createStatement();
rs = st.executeQuery("SELECT FLNO, Seq, FDate FROM FlightLegInstance WHERE Pilot IS NULL");
System.out.println(" \tFLNO\tSeq\tFDate");
System.out.println(" -----------------------");
while (rs.next()) {
count++;
String str = rs.getString("FLNO") + "\t" + rs.getString("Seq") + "\t" + rs.getString("FDate");
System.out.println(count + ".\t" + str);
}
System.out.println("\n");
rs.close();
try {
stmt = con.createStatement();
String str2 = "CREATE VIEW V1 As SELECT FLNO, Seq, FDate FROM FlightLegInstance WHERE Pilot IS NOT NULL";
stmt.executeUpdate(str2);
System.out.println("VIEW successfully created!\n");
} catch (SQLException e) {
System.out.println("VIEW already exists!\n");
}
st.close();
break;
case 2:
count = 0;
st = con.createStatement();
rs = st.executeQuery("SELECT ID, Maker, Model, LastMaint FROM Plane WHERE LastMaint < DATE_ADD(SYSDATE(), INTERVAL -60 DAY)");
System.out.println(" \tID\tMaker\tModel\tLastMaint");
System.out.println(" -----------------------------------");
while (rs.next()) {
count++;
String str = rs.getString("ID") + "\t" + rs.getString("Maker") + "\t" + rs.getString("Model") + "\t" + rs.getString("LastMaint");
System.out.println(count + ".\t" + str);
}
System.out.println("\n");
rs.close();
try {
stmt = con.createStatement();
String str2 = "CREATE VIEW V2 As SELECT ID, Maker, Model, LastMaint FROM Plane WHERE LastMaint < DATE_ADD(SYSDATE(), INTERVAL -60 DAY)";
stmt.executeUpdate(str2);
System.out.println("VIEW successfully created!\n");
} catch (SQLException e) {
System.out.println("VIEW already exists!\n");
}
st.close();
break;
case 3:
count = 0;
st = con.createStatement();
rs = st.executeQuery("SELECT p.ID, p.Name, fli.FLNO, fl.FromA, fl.ToA, fli.FDate FROM Pilot p, FlightLegInstance fli, FlightLeg fl WHERE fli.FLNO = fl.FLNO AND fli.Seq = fl.Seq AND p.ID = fli.Pilot");
System.out.println(" \tID\tName\tFLNO\tFromA\tToA\tFDate");
System.out.println(" ---------------------------------------------------");
while(rs.next()) {
count++;
String str = rs.getString("p.ID") + "\t" + rs.getString("p.Name") + "\t" + rs.getString("fli.FLNO") + "\t" + rs.getString("fl.FromA") + "\t" + rs.getString("fl.ToA") + "\t" + rs.getString("fli.FDate");
System.out.println(count + ".\t" + str);
}
System.out.println("\n");
rs.close();
try {
stmt = con.createStatement();
String str2 = "CREATE VIEW V3 As SELECT p.ID, p.Name, fli.FLNO, fl.FromA, fl.ToA, fli.FDate FROM Pilot p, FlightLegInstance fli, FlightLeg fl WHERE fli.FLNO = fl.FLNO AND fli.Seq = fl.Seq AND p.ID = fli.Pilot";
stmt.executeUpdate(str2);
System.out.println("VIEW successfully created!\n");
} catch (SQLException e) {
System.out.println("VIEW already exists!\n");
}
st.close();
break;
case 4:
count = 0;
st = con.createStatement();
rs = st.executeQuery("SELECT fli.Pilot, p.Name, fli.FDate, COUNT(*) FROM FlightLegInstance fli, Pilot p WHERE p.ID = fli.Pilot GROUP BY YEAR(fli.FDate) + '-' + MONTH(fli.FDate), fli.Pilot");
System.out.println(" \tPilot\tName\tFDate\t\tCount");
System.out.println(" ---------------------------------------");
while (rs.next()) {
count++;
String str = rs.getString("fli.Pilot") + "\t" + rs.getString("p.Name") + "\t" + rs.getString("fli.FDate") + "\t" + rs.getString("COUNT(*)");
System.out.println(count + ".\t" + str);
}
System.out.println("\n");
rs.close();
try {
stmt = con.createStatement();
String str2 = "CREATE VIEW V4 SELECT fli.Pilot, p.Name, fli.FDate, COUNT(*) FROM FlightLegInstance fli, Pilot p WHERE p.ID = fli.Pilot GROUP BY YEAR(fli.FDate) + '-' + MONTH(fli.FDate), fli.Pilot";
stmt.executeUpdate(str2);
System.out.println("VIEW successfully created!\n");
} catch (SQLException e) {
System.out.println("VIEW already exists!\n");
}
st.close();
break;
case 5:
bool = false;
break;
}
}
con.close();
}
}