· 9 years ago · Jun 15, 2017, 08:38 PM
1
2connect_jps.java
3package sample_jps;
4import java.sql.*;
5public class connect_jps {
6
7 public static void main(String[] args) {
8
9 Connection conn = null;
10
11 try {
12 Class.forName("com.mysql.jdbc.Driver");
13 conn = DriverManager.getConnection(
14 "jdbc:mysql://localhost/mydb_jps","root","12345");
15 System.out.println("Connection Established");
16 }
17 catch (Exception e) {
18 System.err.println("Cannot connect to server");
19 }
20 finally {
21 if (conn != null) {
22 try {
23 conn.close();
24 System.out.println("Connection Terminated.");
25 }
26 catch (Exception e) { /* Ignore close errors */ }
27 }
28 }
29 }
30}
31
32
33insert_jps.java
34package sample_jps;
35
36import java.sql.*;
37import javax.swing.*;
38public class insert_jps {
39 insert_jps()
40 {
41 try
42 {
43 Class.forName("com.mysql.jdbc.Driver");
44 Connection conn = DriverManager.getConnection(
45 "jdbc:mysql://localhost/my3csd_jps","root","12345");
46 String studnof = JOptionPane.showInputDialog("Student#");
47 String namef = JOptionPane.showInputDialog("Name");
48 String sectionf = JOptionPane.showInputDialog("Section");
49 String tuitionf = JOptionPane.showInputDialog("Tuition");
50 String bdayf = JOptionPane.showInputDialog("Birthday");
51 String sql = "INSERT INTO students VALUES("
52 + "'" + studnof + "'" + ","
53 + "'" + namef + "'" + ","
54 +"'" + sectionf + "'" + ","
55 + tuitionf + ","
56 + "'" + bdayf + "'"
57 + ")";
58
59 PreparedStatement stmt = conn.prepareStatement(sql);
60 stmt.executeUpdate();
61 JOptionPane.showMessageDialog(null, studnof + "\nINSERTED!");
62 stmt.close();
63 conn.close();
64 }
65 catch(Exception e)
66 {
67 JOptionPane.showMessageDialog(null, e.getMessage());
68 }
69 }
70 public static void main(String[] args)
71 {
72 insert_jps i = new insert_jps();
73 }
74}
75
76
77View_jps.java
78package Sample_JPS;
79
80import java.sql.*;
81import javax.swing.JOptionPane;
82
83public class view_jps {
84
85 public view_jps()
86 {
87 String outs = null;
88 try
89 {
90 Class.forName("com.mysql.jdbc.Driver");
91 Connection conn = DriverManager.getConnection(
92 "jdbc:mysql://localhost/my3csd_jps","root","12345"
93 );
94
95 String SQL = "SELECT * FROM students";
96
97 PreparedStatement stmt = conn.prepareStatement(SQL);
98 ResultSet rs = stmt.executeQuery();
99
100 while(rs.next())
101 {
102 outs = String.format(
103 "%-12s %-20s %4s\t P%,10.2f\t %s",
104 rs.getString("studno"),
105 rs.getString("name"),
106 rs.getString("section"),
107 rs.getDouble("tuition"),
108 rs.getDate("birthday")
109 );
110 System.out.println(outs);
111 }
112 stmt.close();
113 conn.close();
114
115
116 }
117 catch(Exception e)
118 {
119 JOptionPane.showMessageDialog(null, e.getMessage());
120 }
121 }
122 public static void main(String[] args) {
123 view_jps v = new view_jps();
124 }
125}
126
127
128Delete_jps.java
129package sample_jps;
130
131import java.sql.*;
132import javax.swing.*;
133
134public class delete_jps {
135 delete_jps() {
136 try
137 {
138 Class.forName("com.mysql.jdbc.Driver");
139 Connection conn = DriverManager.getConnection(
140 "jdbc:mysql://localhost/my3csd_jps", "root", "12345"
141 );
142 String sno = JOptionPane.showInputDialog("Student Number");
143
144 String SQL = "DELETE FROM students WHERE studno = '"+ sno + "'";
145
146 PreparedStatement stmt = conn.prepareStatement(SQL);
147 int n = stmt.executeUpdate();
148
149 if(n != 0)
150 JOptionPane.showMessageDialog(null, sno + " Deleted! ");
151 else
152 JOptionPane.showMessageDialog(null, sno + "NOT Found!");
153
154 stmt.close();
155 conn.close();
156 }
157 catch(Exception e)
158 {
159 JOptionPane.showMessageDialog(null, e.getMessage());
160 }
161 }
162 public static void main(String[] args)
163 {
164 delete_jps d = new delete_jps();
165 }
166}
167
168
169ModifyMethods_jps.java
170package sangoyo_employee;
171
172import java.sql.*;
173import javax.swing.*;
174
175
176public class modifyMethods_jps {
177
178 public static void delete() {
179 try
180 {
181 Class.forName("com.mysql.jdbc.Driver");
182 Connection conn = DriverManager.getConnection(
183 "jdbc:mysql://localhost/employees_sangoyo_3csd", "root", "12345"
184 );
185 String emp_id = JOptionPane.showInputDialog("Employee ID");
186
187 String SQL = "DELETE FROM employee_data_jps WHERE emp_id = '"+ emp_id + "'";
188
189 PreparedStatement stmt = conn.prepareStatement(SQL);
190 int n = stmt.executeUpdate();
191
192 if(n != 0)
193 JOptionPane.showMessageDialog(null, emp_id + " Deleted! ");
194 else
195 JOptionPane.showMessageDialog(null, emp_id + "NOT Found!");
196
197 stmt.close();
198 conn.close();
199 }
200 catch(Exception e)
201 {
202 JOptionPane.showMessageDialog(null, e.getMessage());
203 }
204 }
205
206 public static void insert() {
207 try
208 {
209 Class.forName("com.mysql.jdbc.Driver");
210 Connection conn = DriverManager.getConnection(
211 "jdbc:mysql://localhost/employees_sangoyo_3csd","root","12345");
212
213 String f_name = JOptionPane.showInputDialog("First Name");
214 String l_name = JOptionPane.showInputDialog("Last Name");
215 String title = JOptionPane.showInputDialog("Title");
216 String age = JOptionPane.showInputDialog("Age");
217 String yos = JOptionPane.showInputDialog("Years of Service");
218 String salary = JOptionPane.showInputDialog("Salary");
219 String perks = JOptionPane.showInputDialog("Perks");
220 String email = JOptionPane.showInputDialog("Email");
221 String sql = "INSERT INTO employee_data_jps (f_name, l_name, title, age, yos, salary, perks, email) VALUES("
222 + "'" + f_name + "'" + ","
223 + "'" + l_name + "'" + ","
224 + "'" + title + "'" + ","
225 + age + "," + yos + ","
226 + salary + "," + perks + ","
227 + "'" + email + "'"
228 + ")";
229
230
231 PreparedStatement stmt = conn.prepareStatement(sql);
232 stmt.executeUpdate();
233 JOptionPane.showMessageDialog(null, f_name + "\nINSERTED!");
234 stmt.close();
235 conn.close();
236 }
237 catch(Exception e)
238 {
239 JOptionPane.showMessageDialog(null, e.getMessage());
240 }
241 }
242
243 public static void view() {
244
245 String outs = null;
246 try {
247 Class.forName("com.mysql.jdbc.Driver");
248 Connection conn = DriverManager.getConnection(
249 "jdbc:mysql://localhost/employees_sangoyo_3csd","root","12345"
250 );
251
252 String SQL = "SELECT * FROM employee_data_jps";
253
254 PreparedStatement stmt = conn.prepareStatement(SQL);
255 ResultSet rs = stmt.executeQuery();
256
257 while(rs.next()) {
258 outs = String.format(
259 "%-4d %-20s %-20s %30s\t %2d\t %d\t P%,10.2f\t P%,10.2f\t %-30s",
260 rs.getInt("emp_id"),
261 rs.getString("f_name"),
262 rs.getString("l_name"),
263 rs.getString("title"),
264 rs.getInt("age"),
265 rs.getInt("yos"),
266 rs.getDouble("salary"),
267 rs.getDouble("perks"),
268 rs.getString("email")
269 );
270 System.out.println(outs);
271 }
272
273 stmt.close();
274 conn.close();
275 }
276
277 catch(Exception e) {
278 JOptionPane.showMessageDialog(null, e.getMessage());
279 }
280 }
281
282}
283
284
285
286
287mySQLscript_JPS.sql
288
289USE my3CSD_jps;
290
291DROP TABLE IF EXISTS students;
292DROP TABLE IF EXISTS subjects;
293DROP TABLE IF EXISTS enroll;
294
295CREATE TABLE students(
296 studno VARCHAR(10) NOT NULL,
297 name VARCHAR(30),
298 section VARCHAR(4),
299 tuition DOUBLE(10,2),
300 birthday DATE DEFAULT '0000-00-00',
301 PRIMARY KEY (studno)
302 );
303
304CREATE TABLE subjects(
305 subjcode VARCHAR(5) NOT NULL,
306 title VARCHAR(25) NOT NULL,
307 units INTEGER(1),
308 PRIMARY KEY (subjcode)
309 );
310
311CREATE TABLE enroll(
312 studno VARCHAR(10) NOT NULL,
313 subjcode VARCHAR(5) NOT NULL
314 );
315
316INSERT INTO students VALUES
317 ('2007008208','Susan Estanislao','3CSD',38000.00,'1990-08-20'),
318 ('2007012345','Jess Agbayani','3CSA',35000.25,'1990-12-12'),
319 ('2007001234','Cedric Manahan','3ISA',40000.50,'1999-01-31'),
320 ('2007001122','Devra Galleto','3ISB',45000.75,'1995-09-20'),
321 ('2007000011','Chlowee Cubangbang','3ISD',42000.50,'1990-11-20'),
322 ('2007001212','Deo Agbayani','3ITE',38000.00,'1990-01-20'),
323 ('2008006600','Jerson Sangoyo','3CSD',44000.00,'1990-10-23');
324
325INSERT INTO subjects VALUES
326 ('CS113','Unix Operating System',1),
327 ('IM201','Database Management',3),
328 ('IT103','Computer Organization',3),
329 ('ICS3L','Java Programming Lab',1);
330
331INSERT INTO enroll VALUES
332 ('2007008208','ICS3L'),
333 ('2007008208','CS113'),
334 ('2007008208','IM201'),
335 ('2007012345','CS113'),
336 ('2007012345','IM201'),
337 ('2007012345','IT103'),
338 ('2007012345','ICS3L'),
339 ('2007001234','CS113'),
340 ('2007001234','ICS3L'),
341 ('2007001234','IT103'),
342 ('2007001122','CS113'),
343 ('2007001122','IM201'),
344 ('2007001122','IT103'),
345 ('2007000011','IM201'),
346 ('2007000011','CS113'),
347 ('2007001212','CS113'),
348 ('2007001212','ICS3L'),
349 ('2008006600','IM201');
350 ('2008006600','CS113');
351 ('2008006600','IT103');