· 9 years ago · Oct 04, 2016, 08:36 AM
1package preexer1;
2
3import java.util.Scanner;
4import java.sql.*;
5import java.text.*;
6public class PreExer1 {
7
8 private static Statement st;
9 private static ResultSet rs;
10 private static Connection con;
11 String smonth, syear;
12 static Scanner in = new Scanner(System.in);
13
14 public PreExer1() {
15 try {
16 Class.forName("com.mysql.jdbc.Driver");
17 con = DriverManager.getConnection("jdbc:mysql://10.4.45.181:3306/information_schema", "root", "root");
18 st = con.createStatement();
19 } catch (Exception ex) {
20 System.out.println("Error: " + ex);
21
22 }
23 }
24 public static void login(String user, String pass) {
25 try {
26 Scanner scan = new Scanner(System.in);
27 Class.forName("com.mysql.jdbc.Driver");
28 con = DriverManager.getConnection("jdbc:mysql://10.4.45.181:3306/information_schema", ""+user+"", ""+pass+"");
29 st = con.createStatement();
30 String query = "show databases";
31 rs = st.executeQuery(query);
32 while(rs.next()) {
33 System.out.println(rs.getString(1));
34 }
35
36 System.out.print("Database: ");
37 String db = scan.next();
38
39 query = "use "+db+"";
40 st.executeUpdate(query);
41
42 view(user);
43 } catch (Exception ex) {
44 System.out.println("Error: " + ex);
45 }
46 }
47
48
49 public static void Grades() {
50 try {
51 int userID;
52 System.out.println("Enter Student ID: ");
53 int studid = in.nextInt();
54 String query = "SELECT distinct code, prelim, midterm, prefinal, round((prelim+midterm+preFinal)/3,0) as final FROM grades, subjects, students, enrolled where(students.SID ="+studid+" = enrolled.Students_sid and subjects.subid = enrolled.Subjects_subid and enrolled.eid = grades.Enrolled_eid)";
55 rs = st.executeQuery(query);
56 System.out.println("SubjCode----Prelim----Midterm----PFinal");
57 while(rs.next()) {
58 String c = rs.getString("code");
59 String pr = rs.getString("prelim");
60 String mid = rs.getString("midterm");
61 String pf = rs.getString("prefinal");
62 String f = rs.getString("FINAL");
63 System.out.println(c + "----------" + pr + "----------" + mid + "----------" + pf + "----------" + f);
64 }
65 }catch(Exception ex) {
66 System.out.println(ex);
67 }
68 }
69
70 public static void classList() {
71 try {
72 System.out.println("Enter Subject ID: ");
73 int subid = in.nextInt();
74 String query = "select distinct sid, Gender, lastname, firsntame, middlename from subjects, enrolled, students where("+subid+" =Enrolled.Subjects_subid and students.sid=enrolled.Students_sid)";
75
76 rs = st.executeQuery(query);
77 System.out.println("Studid Studname StudGender");
78 while (rs.next()) {
79 String s = rs.getString("sid");
80 String f = rs.getString("firsntame");
81 String g = rs.getString("Gender");
82 System.out.println(s + "----------" + f + "----------" + g);
83 }
84 } catch(Exception ex) {
85 System.out.println(ex);
86 }
87 }
88
89 public static void insertGrades() {
90 try {
91 System.out.print("Enter Students ID: ");
92 int sid = in.nextInt();
93 System.out.print("Enter Subject ID: ");
94 int subid = in.nextInt();
95 System.out.print("Enter Prelim Grade: ");
96 int prelim = in.nextInt();
97 System.out.print("Enter Midterm Grade: ");
98 int midterm = in.nextInt();
99 System.out.print("Enter Prefinal Grade: ");
100 int prefinal = in.nextInt();
101
102
103 int eid = 0;
104 String query = "select eid from enrolled where("+sid+" = enrolled.Students_sid and "+subid+" =enrolled.Subjects_subid)";
105 rs = st.executeQuery(query);
106
107 while(rs.next()) {
108 eid = rs.getInt("eid");
109 }
110 query = "insert into grades values("+prelim+","+midterm+","+prefinal+","+eid+")";
111 st.executeUpdate(query);
112
113 }catch(Exception ex) {
114 System.out.println(ex);
115 }
116 }
117
118
119
120 public void getTime(){
121 try {
122 String query = "select month(now()) as month, year(now()) as year";
123 rs = st.executeQuery(query);
124
125 rs.next();
126 smonth = rs.getString("month");
127 syear = rs.getString("year");
128 int m = Integer.valueOf(smonth);
129 int y = Integer.valueOf(syear);
130
131 createTable(m,y);
132 }
133 catch(Exception ex) {
134 System.out.println(ex);
135 }
136 }
137
138 public static void createTable(int month, int year) {
139 String x = null;
140 if(month!=6 && month!=10) {
141 System.out.print("Enrollment Closed");
142 }
143
144 if(month==6){
145 x = "1st";
146 }
147 else if(month==10){
148 x = "2nd";
149 }
150 System.out.println();
151
152 String y = "Sy"+year+""+x+"sem";
153
154 try {
155 String fsem = "CREATE SCHEMA IF NOT EXISTS `"+y+"` DEFAULT CHARACTER SET utf8 ";
156 st.executeUpdate(fsem);
157 fsem = "USE `"+y+"`";
158 st.executeUpdate(fsem);
159 fsem = "CREATE TABLE IF NOT EXISTS `"+y+"`.`students` (\n" +
160 " `sid` INT NOT NULL AUTO_INCREMENT,\n" +
161 " `firstname` VARCHAR(45) NULL,\n" +
162 " `middlename` VARCHAR(45) NULL,\n" +
163 " `lastname` VARCHAR(45) NULL,\n" +
164 " `Gender` VARCHAR(10) NULL,\n" +
165 " `Address` VARCHAR(45) NULL,\n" +
166 " PRIMARY KEY (`sid`))\n" +
167 "ENGINE = InnoDB";
168 st.executeUpdate(fsem);
169 fsem = "CREATE TABLE IF NOT EXISTS `"+y+"`.`Teachers` (\n" +
170 "`tid` INT NOT NULL AUTO_INCREMENT,\n" +
171 "`firstname` VARCHAR(45) NULL,\n" +
172 "`middlename` VARCHAR(45) NULL,\n" +
173 "`lastname` VARCHAR(45) NULL,\n" +
174 "`Division` VARCHAR(45) NULL,\n" +
175 "PRIMARY KEY (`tid`))\n" +
176 "ENGINE = InnoDB";
177 st.executeUpdate(fsem);
178 fsem = "CREATE TABLE IF NOT EXISTS `"+y+"`.`Subjects` (\n" +
179 "`subid` INT NOT NULL AUTO_INCREMENT,\n" +
180 "`code` VARCHAR(45) NULL,\n" +
181 "`description` VARCHAR(45) NULL,\n" +
182 "`units` INT NULL,\n" +
183 "`isMajor` TINYINT(1) NULL,\n" +
184 "`Teachers_tid` INT NOT NULL,\n" +
185 "PRIMARY KEY (`subid`),\n" +
186 "INDEX `fk_Subjects_Teachers1_idx` (`Teachers_tid` ASC),\n" +
187 "CONSTRAINT `fk_Subjects_Teachers1`\n" +
188 "FOREIGN KEY (`Teachers_tid`)\n" +
189 "REFERENCES `"+y+"`.`Teachers` (`tid`)\n" +
190 "ON DELETE NO ACTION\n" +
191 "ON UPDATE NO ACTION)\n" +
192 "ENGINE = InnoDB";
193 st.executeUpdate(fsem);
194 fsem = "CREATE TABLE IF NOT EXISTS `"+y+"`.`Enrolled` (\n" +
195 "`eid` INT NOT NULL AUTO_INCREMENT,\n" +
196 "`students_sid` INT NOT NULL,\n" +
197 "`Subjects_subid` INT NOT NULL,\n" +
198 "PRIMARY KEY (`eid`),\n" +
199 "INDEX `fk_Enrolled_students_idx` (`students_sid` ASC),\n" +
200 "INDEX `fk_Enrolled_Subjects1_idx` (`Subjects_subid` ASC),\n" +
201 "CONSTRAINT `fk_Enrolled_students`\n" +
202 "FOREIGN KEY (`students_sid`)\n" +
203 "REFERENCES `"+y+"`.`students` (`sid`)\n" +
204 "ON DELETE NO ACTION\n" +
205 "ON UPDATE NO ACTION,\n" +
206 "CONSTRAINT `fk_Enrolled_Subjects1`\n" +
207 "FOREIGN KEY (`Subjects_subid`)\n" +
208 "REFERENCES `"+y+"`.`Subjects` (`subid`)\n" +
209 "ON DELETE NO ACTION\n" +
210 "ON UPDATE NO ACTION)\n" +
211 "ENGINE = InnoDB\n";
212 st.executeUpdate(fsem);
213
214 fsem = "CREATE TABLE IF NOT EXISTS `"+y+"`.`Grades` (\n" +
215 "`prelim` INT NULL,\n" +
216 "`midterm` INT NULL,\n" +
217 "`prefinal` INT NULL,\n" +
218 "`Enrolled_eid` INT NOT NULL,\n" +
219 "INDEX `fk_Grades_Enrolled1_idx` (`Enrolled_eid` ASC),\n" +
220 "CONSTRAINT `fk_Grades_Enrolled1`\n" +
221 "FOREIGN KEY (`Enrolled_eid`)\n" +
222 "REFERENCES `"+y+"`.`Enrolled` (`eid`)\n" +
223 "ON DELETE NO ACTION\n" +
224 "ON UPDATE NO ACTION)\n" +
225 "ENGINE = InnoDB";
226 st.executeUpdate(fsem);
227
228 System.out.println("Database Created");
229
230 }catch(Exception ex) {
231 System.out.println(ex);
232 }
233
234 }
235
236
237 public static void view(String user) {
238 try {
239 String query = "select * from students where sid = '"+user+"'";
240 rs = st.executeQuery(query);
241 while(rs.next()) {
242 String fn = rs.getString("firstname");
243 String mn = rs.getString("middlename");
244 String ln = rs.getString("lastname");
245 String g = rs.getString("gender");
246 String ad = rs.getString("address");
247
248 System.out.println("Name: " +fn + " " + mn + " " + ln);
249 System.out.println("Gender: " + g);
250 System.out.println("Address: " + ad);
251 }
252 }
253 catch(Exception ex) {
254 System.out.println(ex);
255 }
256 }
257
258 public static void register() {
259 try {
260 Scanner scan = new Scanner(System.in);
261 String query = "show databases";
262 rs = st.executeQuery(query);
263
264 while(rs.next()) {
265 System.out.println(rs.getString(1));
266 }
267 System.out.print("Database: ");
268 String db = scan.next();
269
270 query = "use "+db+"";
271 st.executeUpdate(query);
272
273 System.out.print("Enter firstname: ");
274 String fname = scan.next();
275 System.out.print("Enter middlename: ");
276 String mname = scan.next();
277 System.out.print("Enter lastname: ");
278 String lname = scan.next();
279 System.out.print("Enter gender: ");
280 String g = scan.next();
281 System.out.print("Enter address: ");
282 String add = scan.next();
283
284 query = "insert into students(firstname, middlename, lastname, gender, address) values('"+fname+"', '"+mname+"', '"+lname+"', '"+g+"', '"+add+"')";
285 st.executeUpdate(query);
286
287 String user = id(fname, mname, lname, g, add);
288 String pass = fname;
289
290 System.out.println(user);
291
292 grant(user, pass, db);
293
294
295 }
296 catch(Exception ex) {
297 System.out.println("Error: " + ex);
298 }
299 }
300
301 public static String id(String fname, String mname, String lname, String g, String add) {
302 String user = null;
303 try {
304 String query = "select sid from students order by sid desc limit 1";
305 rs = st.executeQuery(query);
306 rs.next();
307 user = rs.getString("sid");
308 }
309 catch(Exception ex) {
310 System.out.println(ex);
311 }
312
313 return user;
314 }
315
316 public static void grant(String user, String pass, String db){
317 try {
318 String query = "create user '"+user+"'@10.4.45.102 identified by '"+pass+"'";
319 st.executeUpdate(query);
320
321 query = "grant select on "+db+".* to '"+user+"'@10.4.45.102 identified by '"+pass+"'";
322 st.executeQuery(query);
323
324 }
325
326 catch(Exception ex) {
327 System.out.println("Error: " + ex);
328 }
329 login(user, pass);
330 }
331
332}