API tools faq
paste
Guest User

Untitled

a guest
Aug 14th, 2018
113
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 13.84 KB | None | 0 0
raw download clone embed print report
  1. package test;
  2.  
  3. import java.sql.*;
  4. import javax.swing.JOptionPane;
  5.  
  6.  
  7. public 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. }
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!