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