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