· 8 years ago · Aug 15, 2018, 12:06 AM
1package test;
2
3import java.sql.*;
4import javax.swing.JOptionPane;
5
6
7public class Test
8
9 {
10 public static String zzz = "";
11 public static void main (String [] args)
12 throws SQLException, ClassNotFoundException
13 {
14 String path = "jdbc:mysql://localhost/employees",
15 user = "root",
16 password = "Sparsky";
17 try
18 {
19 Class.forName ("com.mysql.jdbc.Driver");
20 }
21 catch (ClassNotFoundException cnfe)
22 {
23 System.out.println (cnfe.getMessage ());
24 }
25
26 Class.forName ("com.mysql.jdbc.Driver");
27 Connection cn =
28 DriverManager.getConnection (path, user, password);
29 Statement st = cn.createStatement ();
30 String sqlCommand = "";
31 sqlCommand =
32 "CREATE TABLE IF NOT EXISTS allEmployees(IDNumber int(9) PRIMARY KEY NOT NULL, firstName VARCHAR(10), middleInitial VARCHAR(4), lastName VARCHAR(15), dateOfBirth DATE,dateOfEmployment DATE, addressLine1 VARCHAR(30), addressLine2 VARCHAR(18), city VARCHAR(18), province VARCHAR(18), postalCode VARCHAR(7), fixedPhoneNumber VARCHAR(12), mobilePhoneNumber VARCHAR(12), salary DECIMAL(8,2));";
33 st.executeUpdate (sqlCommand);
34 sqlCommand = "LOAD DATA LOCAL INFILE '/employee.txt' INTO TABLE allEmployees LINES TERMINATED BY '\r\n';";
35 st.executeUpdate (sqlCommand);
36 sqlCommand = "SELECT firstName, lastName, salary FROM allEmployees WHERE salary=(SELECT max(salary) FROM AllEmployees);";
37 st.executeQuery (sqlCommand);
38 ResultSet rs = st.executeQuery (sqlCommand);
39 while (rs.next ())
40 {
41 String sal = rs.getString("salary");
42 System.out.println("Max salary = " + sal);
43 JOptionPane.showMessageDialog(
44 null,
45 "Max salary = " + sal,
46 "MySQL",
47 JOptionPane.INFORMATION_MESSAGE);
48 }
49 sqlCommand = "SELECT firstName, lastName, salary FROM allEmployees WHERE salary=(SELECT min(salary) FROM AllEmployees); ";
50 st.executeQuery (sqlCommand);
51 rs = st.executeQuery (sqlCommand);
52 while (rs.next ())
53 {
54 String sal = rs.getString("salary");
55 System.out.println("Min salary = " + sal);
56 JOptionPane.showMessageDialog(
57 null,
58 "Min salary = " + sal,
59 "MySQL",
60 JOptionPane.INFORMATION_MESSAGE);
61 }
62 sqlCommand = "SELECT SUM(salary) from AllEmployees;";
63 st.executeQuery (sqlCommand);
64 rs = st.executeQuery (sqlCommand);
65 while (rs.next ())
66 {
67 String sal = rs.getString("SUM(salary)");
68 System.out.println("Sum salary = " + sal);
69 JOptionPane.showMessageDialog(
70 null,
71 "Sum salary = " + sal,
72 "MySQL",
73 JOptionPane.INFORMATION_MESSAGE);
74
75 }
76 sqlCommand = "SELECT AVG(salary) from AllEmployees;";
77 st.executeQuery (sqlCommand);
78 rs = st.executeQuery (sqlCommand);
79 while (rs.next ())
80 {
81 String sal = rs.getString("AVG(salary)");
82 System.out.println("Average Salary = " + sal);
83 JOptionPane.showMessageDialog(
84 null,
85 "Average Salary = " + sal,
86 "MySQL",
87 JOptionPane.INFORMATION_MESSAGE);
88 }
89 sqlCommand = "SELECT firstName, lastName, dateOfEmployment FROM allEmployees WHERE dateOfEmployment=(SELECT MAX(dateOfEmployment) FROM AllEmployees);";
90 st.executeQuery (sqlCommand);
91 rs = st.executeQuery (sqlCommand);
92 while (rs.next ())
93 {
94 String firstName = rs.getString("firstName");
95 String lastName = rs.getString("lastName");
96 String dateOfEmployment = rs.getString("dateOfEmployment");
97 System.out.println("Max dateOfEmployment = " + firstName + "\t" + lastName + "\t" + dateOfEmployment);
98 JOptionPane.showMessageDialog(
99 null,
100 "Max dateOfEmployment = " + firstName + " " + lastName + " " + dateOfEmployment,
101 "MySQL",
102 JOptionPane.INFORMATION_MESSAGE);
103 }
104 sqlCommand = "SELECT firstName, lastName, dateOfEmployment FROM allEmployees WHERE dateOfEmployment=(SELECT MIN(dateOfEmployment) FROM AllEmployees);";
105 st.executeQuery (sqlCommand);
106 rs = st.executeQuery (sqlCommand);
107 while (rs.next ())
108 {
109 String firstName = rs.getString("firstName");
110 String lastName = rs.getString("lastName");
111 String dateOfEmployment = rs.getString("dateOfEmployment");
112 System.out.println("Min dateOfEmployment = " + firstName + "\t" + lastName + "\t" + dateOfEmployment);
113 JOptionPane.showMessageDialog(
114 null,
115 "Min dateOfEmployment = " + firstName + " " + lastName + " " + dateOfEmployment,
116 "MySQL",
117 JOptionPane.INFORMATION_MESSAGE);
118 }
119 sqlCommand = "SELECT firstName, lastName, dateOfBirth FROM allEmployees WHERE dateOfBirth=(SELECT MAX(dateOfBirth) FROM AllEmployees);";
120 st.executeQuery (sqlCommand);
121 rs = st.executeQuery (sqlCommand);
122 while (rs.next ())
123 {
124 String firstName = rs.getString("firstName");
125 String lastName = rs.getString("lastName");
126 String dateOfBirth = rs.getString("dateOfBirth");
127 System.out.println("Max dateOfBirth = " + firstName + "\t" + lastName + "\t" + dateOfBirth);
128 JOptionPane.showMessageDialog(
129 null,
130 "Max dateOfBirth = " + firstName + " " + lastName + " " + dateOfBirth,
131 "MySQL",
132 JOptionPane.INFORMATION_MESSAGE);
133 }
134 sqlCommand = "SELECT firstName, lastName, dateOfBirth FROM allEmployees WHERE dateOfBirth=(SELECT MIN(dateOfBirth) FROM AllEmployees);";
135 st.executeQuery (sqlCommand);
136 rs = st.executeQuery (sqlCommand);
137 while (rs.next ())
138 {
139 String firstName = rs.getString("firstName");
140 String lastName = rs.getString("lastName");
141 String dateOfBirth = rs.getString("dateOfBirth");
142 System.out.println("Min dateOfBirth = " + firstName + "\t" + lastName + "\t" + dateOfBirth);
143 JOptionPane.showMessageDialog(
144 null,
145 "Min dateOfBirth = " + firstName + " " + lastName + " " + dateOfBirth,
146 "MySQL",
147 JOptionPane.INFORMATION_MESSAGE);
148 }
149 sqlCommand = "SELECT AVG(DATEDIFF(CURDATE(), dateOfBirth) / 365.24) from allEmployees;";
150 st.executeQuery (sqlCommand);
151 rs = st.executeQuery (sqlCommand);
152 while (rs.next ())
153 {
154 String dateOfBirth = rs.getString("AVG(DATEDIFF(CURDATE(), dateOfBirth) / 365.24)");
155 System.out.println("Average age = " + dateOfBirth);
156 JOptionPane.showMessageDialog(
157 null,
158 "Average age = " + dateOfBirth,
159 "MySQL",
160 JOptionPane.INFORMATION_MESSAGE);
161 }
162 sqlCommand = "ALTER TABLE allEmployees DROP fixedPhoneNumber, DROP mobilePhoneNumber;";
163 st.executeUpdate (sqlCommand);
164 sqlCommand = "UPDATE allEmployees SET middleInitial = 'A' WHERE firstName = 'Jane';";
165 st.executeUpdate (sqlCommand);
166 sqlCommand = "UPDATE allEmployees SET middleInitial = 'B' WHERE year(dateOfBirth) = '1954';";
167 st.executeUpdate (sqlCommand);
168 sqlCommand = "UPDATE allEmployees SET middleInitial = 'C' WHERE addressLine1 LIKE '%3547%';";
169 st.executeUpdate (sqlCommand);
170 sqlCommand = "UPDATE allEmployees SET middleInitial = 'D' WHERE city = 'Milton';";
171 st.executeUpdate (sqlCommand);
172 sqlCommand = "UPDATE allEmployees SET middleInitial = 'E' WHERE salary < 101000;";
173 st.executeUpdate (sqlCommand);
174 sqlCommand = "UPDATE allEmployees SET middleInitial = 'F' WHERE day(dateOfBirth) = '8' AND month(dateOfBirth) = '11';";
175 st.executeUpdate (sqlCommand);
176 sqlCommand = "ALTER TABLE allEmployees ADD gender VARCHAR(2) AFTER lastName;";
177 st.executeUpdate (sqlCommand);
178 sqlCommand = "ALTER TABLE allEmployees ADD maritalStatus VARCHAR(2) FIRST;";
179 st.executeUpdate (sqlCommand);
180 sqlCommand = "ALTER TABLE allEmployees ADD numChildren VARCHAR(2) AFTER maritalStatus;";
181 st.executeUpdate (sqlCommand);
182 sqlCommand = "UPDATE allEmployees SET gender = 'M';";
183 st.executeUpdate (sqlCommand);
184 sqlCommand = "UPDATE allEmployees SET gender = 'F' WHERE firstName = 'JANE';";
185 st.executeUpdate (sqlCommand);
186 sqlCommand = "UPDATE allEmployees SET gender = 'F' WHERE firstName = 'BETH';";
187 st.executeUpdate (sqlCommand);
188 sqlCommand = "UPDATE allEmployees SET gender = 'F' WHERE firstName = 'MARY';";
189 st.executeUpdate (sqlCommand);
190 sqlCommand = "UPDATE allEmployees SET gender = 'F' WHERE firstName = 'JILL';";
191 st.executeUpdate (sqlCommand);
192 sqlCommand = "UPDATE allEmployees SET gender = 'F' WHERE firstName = 'KATE';";
193 st.executeUpdate (sqlCommand);
194 sqlCommand = "DELETE FROM allEmployees WHERE IDNumber = '381654729';";
195 st.executeUpdate (sqlCommand);
196 sqlCommand = "SELECT * FROM allEmployees;";
197 rs = st.executeQuery (sqlCommand);
198 JOptionPane.showMessageDialog(
199 null,
200 "Updated Table:",
201 "MySQL",
202 JOptionPane.INFORMATION_MESSAGE);
203 System.out.println("Updated Table:");
204 while (rs.next ())
205 {
206 int primKey = rs.getInt ("IDNumber");
207 String fname = rs.getString ("firstName");
208 String mname = rs.getString ("middleInitial");
209 String lname = rs.getString ("lastName");
210 String dateOfBirth = rs.getString ("dateOfBirth");
211 String dateOfEmployment = rs.getString ("dateOfEmployment");
212 String addressLine1 = rs.getString ("addressLine1");
213 String addressLine2 = rs.getString ("addressLine2");
214 String city = rs.getString ("city");
215 String province = rs.getString ("province");
216 String postalCode = rs.getString ("postalCode");
217// String fixedPhoneNumber = rs.getString ("fixedPhoneNumber");
218// String mobilePhoneNumber = rs.getString ("mobilePhoneNumber");
219 String salary = rs.getString ("salary");
220 System.out.println (primKey + "\t" + fname + "\t" + mname + "\t" + lname + "\t" + dateOfBirth + "\t" + dateOfEmployment + "\t" + addressLine1 + "\t" + addressLine2 + "\t" + city + "\t" + province + "\t" + postalCode + "\t" /* + fixedPhoneNumber + "\t" + mobilePhoneNumber + "\t" */ + salary);
221 }
222 sqlCommand = "DROP TABLE allEmployees;";
223 st.executeUpdate (sqlCommand);
224 }
225 }