API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Apr 15th, 2017
82
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 63.76 KB | None | 0 0
raw download clone embed print report
  1. package BE.Intec.Daniel.BabySteps;
  2.  
  3. import java.sql.Connection;
  4. import java.sql.SQLException;
  5. import java.util.Scanner;
  6.  
  7. public class StaffAdministrationApp {
  8. public static void main(String[] args) throws SQLException {
  9. GetConnection getCon = new GetConnection();
  10. Connection con = getCon.getConnection();
  11.  
  12. System.out.println("Connectie is tot stand gebracht!");
  13.  
  14. int menuItem;
  15. do {
  16. Scanner in = new Scanner(System.in);
  17. System.out.println(
  18. "Welkom bij het DakPlusPlus keuze menu.Wenst u:n1. Een nieuwe database aan te maken.n2. Uw nieuwe database te initaliseren.n3. Gegevens toe te voegenn4. Gegevens up to datenn5. Gegevens op te vragenn6. De rendabiliteit van een project berekenenn7. Het loon van een werknemer berekenenn0. Het programma af te sluiten.");
  19. menuItem = in.nextInt();
  20. switch (menuItem) {
  21. case 1:
  22. CreateDataBase newDb = new CreateDataBase(con);
  23. System.out.println("Database gecreëerd");
  24. String dbName = "/" + newDb.getDatabaseName();
  25. getCon.setUrl(dbName);
  26. con = getCon.getConnection();
  27. break;
  28. case 2:
  29. InitDatabase init = new InitDatabase(con);
  30. init.initDB();
  31. break;
  32. case 3:
  33. String more;
  34. int updateTable;
  35. do {
  36. System.out.println(
  37. "In welke Tabel wenst u gegeven toe te voegen?n1. Employeesn2. Projectsn3. Worked Hours");
  38. updateTable = in.nextInt();
  39. switch (updateTable) {
  40. case 1:
  41. do {
  42. Employee newEmployee = new Employee(con);
  43. newEmployee.addNew();
  44. System.out.println("Wenst u nog een werknemer toe te voegen? Y/N");
  45. more = in.next();
  46. } while (more.equals("y") || more.equals("Y"));
  47. break;
  48. case 2:
  49. do {
  50. Projects newProject = new Projects(con);
  51. newProject.addNew();
  52. System.out.println("Wenst u nog een project toe te voegen? Y/N");
  53. more = in.next();
  54. } while (more.equals("y") || more.equals("Y"));
  55. break;
  56. case 3:
  57. do {
  58. WorkedHours newWorkedHours = new WorkedHours(con);
  59. newWorkedHours.addNew();
  60. System.out.println("Wenst u nog een contract toe te voegen? Y/N");
  61. more = in.next();
  62. } while (more.equals("y") || more.equals("Y"));
  63. break;
  64. }
  65. } while (updateTable != 1 && updateTable != 2 && updateTable != 3);
  66. break;
  67. case 4:
  68. int updateTable2;
  69. do {
  70. System.out.println(
  71. "In welke Tabel wenst u gegevens aan te passen?n1. Employeesn2. Projectsn3. Worked Hours");
  72. updateTable2 = in.nextInt();
  73. switch (updateTable2) {
  74. case 1:
  75. do {
  76. Employee newEmployee = new Employee(con);
  77. newEmployee.updateItem();
  78. System.out.println("Wenst u nog een werknemer aan te passen? Y/N");
  79. more = in.next();
  80. } while (more.equals("y") || more.equals("Y"));
  81. break;
  82. case 2:
  83. Projects newProject = new Projects(con);
  84. newProject.updateItem();
  85. break;
  86. case 3:
  87. WorkedHours workedHours = new WorkedHours(con);
  88. workedHours.updateItem();
  89. break;
  90. }
  91. } while (updateTable2 != 1 && updateTable2 != 2 && updateTable2 != 3);
  92. break;
  93.  
  94. case 5:
  95. int table;
  96. do {
  97. System.out.println(
  98. "Van welke Tabel wenst u gegeven op te vragen?n1. Employeesn2. Projectsn3. Worked Hours");
  99. table = in.nextInt();
  100. switch (table) {
  101. case 1:
  102. int showEmployee;
  103. Employee employee = new Employee(con);
  104. do {
  105. do {
  106. System.out.println(
  107. "Wenst u de gegevens vann1. een specifieke werknemer nofn2. van alle werknemers?");
  108. showEmployee = in.nextInt();
  109. if (showEmployee == 1) {
  110. employee.getItem();
  111. } else if (showEmployee == 2) {
  112. employee.getAll();
  113. } else {
  114. System.out.println(
  115. "U dient 1 te kiezen voor een specifieke werknemer of 2 voor alle werknemers.");
  116. }
  117. } while (showEmployee != 1 && showEmployee != 2);
  118. System.out.println("Wenst u nog een andere werknemer op te zoeken? Y/N");
  119. more = in.next();
  120. } while (more.equalsIgnoreCase("y"));
  121. break;
  122. case 2:
  123. int showProject;
  124. do {
  125. System.out.println(
  126. "Wenst u de gegevens van 1. een specifiek project of 2. van alle projecten te zien?");
  127. showProject = in.nextInt();
  128. if (showProject == 1) {
  129. Projects project = new Projects(con);
  130. project.getItem();
  131. } else if (showProject == 2) {
  132. Projects project = new Projects(con);
  133. project.getAll();
  134. } else {
  135. System.out.println(
  136. "U dient 1 te kiezen voor een specifiek project of 2 voor alle projecten.");
  137. }
  138. } while (showProject != 1 && showProject != 2);
  139. break;
  140. case 3:
  141. int showWorkedHours;
  142. do {
  143. do {
  144. System.out.println(
  145. "Wenst u de gegevens van 1. een specifiek item of 2. van alle items te zien?");
  146. showWorkedHours = in.nextInt();
  147. if (showWorkedHours == 1) {
  148. WorkedHours workedhours = new WorkedHours(con);
  149. workedhours.getItem();
  150. } else if (showWorkedHours == 2) {
  151. WorkedHours workedhours = new WorkedHours(con);
  152. workedhours.getAll();
  153. } else {
  154. System.out.println(
  155. "U dient 1 te kiezen voor een specifiek project of 2 voor alle projecten.");
  156. }
  157. } while (showWorkedHours != 1 && showWorkedHours != 2);
  158. break;
  159.  
  160. } while (table != 1 && table != 2 && table != 3);
  161. }
  162. System.out.println("Wenst u nog meer gegevens van een andere tabel op te vragen? Y/N");
  163. more = in.next();
  164. } while (more.equalsIgnoreCase("y"));
  165. break;
  166. case 6:
  167. Profitability profit = new Profitability(con);
  168. do {
  169. if (profit.profitable()) {
  170. System.out.println("Dit project is rendabel.");
  171. } else {
  172. System.out.println("Dit project is niet rendabel");
  173. }
  174. System.out.println("Wenst u nog een project te onderzoeken?");
  175. more = in.next();
  176. } while (more.equalsIgnoreCase("y"));
  177. break;
  178. case 7:
  179. Wages monthlyPay = new Wages(con);
  180. do {
  181. monthlyPay.calculateWage();
  182. System.out.println("Wilt u nog een werknemers loon berekenen?Y/N");
  183. more = in.next();
  184. } while (more.equalsIgnoreCase("y"));
  185. break;
  186. case 0:
  187. System.out.println(
  188. "U hebt gekozen om het programma af te sluiten.nWij wensen u een prettige dag verder.");
  189. break;
  190. default:
  191. System.out.println("u dient het corresponderende cijfer te kiezen voor het gewenste menuitem");
  192. }
  193. } while (menuItem != 0);
  194. }
  195. }
  196.  
  197. package BE.Intec.Daniel.BabySteps;
  198.  
  199. import java.sql.DriverManager;
  200. import java.sql.SQLException;
  201. import java.util.Scanner;
  202.  
  203. public class GetConnection {
  204. private String url2 = "jdbc:mysql://";
  205. private String url;
  206. private String user;
  207. private String timezone = "?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=CET";
  208. private String password;
  209. private Scanner input = new Scanner(System.in);
  210.  
  211. public GetConnection() {
  212. System.out.println("Geef het adres zonder http:// of www. van de database in aub.");
  213. url2 += input.nextLine();
  214. url = url2 + timezone;
  215. System.out.println("Geef de username in aub.");
  216. user = input.nextLine();
  217. System.out.println("Geef het paswoord in aub.");
  218. password = input.nextLine();
  219.  
  220. }
  221.  
  222. public String setUrl(String dbName) {
  223. url2 += dbName;
  224. return url = url2 + timezone;
  225. }
  226.  
  227. public java.sql.Connection getConnection() throws SQLException {
  228. return DriverManager.getConnection(url, user, password);
  229. }
  230. }
  231.  
  232. package BE.Intec.Daniel.BabySteps;
  233.  
  234. import java.sql.Connection;
  235. import java.sql.SQLException;
  236. import java.util.Scanner;
  237. import com.mysql.cj.api.jdbc.Statement;
  238.  
  239. public class CreateDataBase {
  240. private Connection con;
  241. private java.sql.Statement stat;
  242. private String databaseName;
  243.  
  244. public CreateDataBase(Connection con) {
  245. this.con = con;
  246. createDataBase();
  247. }
  248.  
  249. public String createDataBase() {
  250. Scanner in = new Scanner(System.in);
  251. System.out.println("Hoe moet uw database noemen?");
  252. databaseName = in.nextLine();
  253. String succes = "";
  254. try {
  255. stat = con.createStatement();
  256. String statement = "CREATE DATABASE IF NOT EXISTS " + databaseName;
  257. stat.executeUpdate(statement);
  258. succes ="Uw databse werd succesvol gecreëerd";
  259. } catch (SQLException e) {
  260. System.out.println("Er ging iets fout");
  261. e.printStackTrace();
  262. }
  263. return succes;
  264. }
  265.  
  266. public String getDatabaseName() {
  267. return databaseName;
  268. }
  269. }
  270.  
  271. package BE.Intec.Daniel.BabySteps;
  272.  
  273. import java.sql.Connection;
  274. import java.sql.SQLException;
  275. import com.mysql.cj.api.jdbc.Statement;
  276.  
  277. public class InitDatabase {
  278. private Connection con;
  279. private java.sql.Statement stat;
  280. private GetConnection getCon;
  281. private int result;
  282.  
  283. public InitDatabase(Connection con) {
  284. this.con = con;
  285. }
  286.  
  287. public int createEmployeeTable() {
  288.  
  289. try {
  290. stat = con.createStatement();
  291. String statement = "CREATE TABLE IF NOT EXISTS Employees (id mediumint(8) unsigned NOT NULL auto_increment,Lastname varchar(255) default NULL,Firstname varchar(255) default NULL,Streetname varchar(255) default NULL,Zipcode varchar(10) default NULL, City varchar(100) default NULL, Birthday Date, Contracttype varchar(255) default NULL, Start Date, End Date, Wages Double Precision (10,2) default NULL, PRIMARY KEY (id)) AUTO_INCREMENT=1;";
  292. result = stat.executeUpdate(statement);
  293. System.out.println("Tabel employees werd succesvol aangemaakt");
  294. } catch (SQLException e) {
  295. System.out.println("Er ging iets fout");
  296. e.printStackTrace();
  297. }
  298. return result;
  299. }
  300.  
  301. public int createProjectTable() {
  302. try {
  303. stat = con.createStatement();
  304. String statement = "CREATE TABLE IF NOT EXISTS Projects (id mediumint(8) unsigned NOT NULL auto_increment,Customer varchar(255),StartDate Date,EndDate Date default null,Revenue Double Precision (10,2) default NULL,Cost Double Precision (10,2) default NULL,PRIMARY KEY (id)) AUTO_INCREMENT=1;";
  305. result = stat.executeUpdate(statement);
  306. System.out.println("Tabel projecs werd succesvol aangemaakt");
  307.  
  308. } catch (SQLException e) {
  309. System.out.println("Er ging iets fout");
  310. e.printStackTrace();
  311. }
  312. return result;
  313. }
  314.  
  315. public int createWorkedHoursTable() {
  316. try {
  317. stat = con.createStatement();
  318. String statement = "CREATE TABLE IF NOT EXISTS WorkedHours (id mediumint(8) unsigned NOT NULL auto_increment, EmployeeId mediumint default NULL,ProjectId mediumint,WorkerCost Double Precision (10,2),WorkHours varchar(100) default NULL,KilometersDriven mediumint default NULL,Driver mediumint default NULL,PRIMARY KEY (id)) AUTO_INCREMENT=1;";
  319. result = stat.executeUpdate(statement);
  320. System.out.println("Tabel workedHours werd succesvol aangemaakt");
  321.  
  322. } catch (SQLException e) {
  323. System.out.println("Er ging iets fout");
  324. e.printStackTrace();
  325. }
  326. return result;
  327. }
  328.  
  329. public int initDB() {
  330. result += createEmployeeTable();
  331. result += createProjectTable();
  332. result += createWorkedHoursTable();
  333. return result;
  334. }
  335. }
  336.  
  337. package BE.Intec.Daniel.BabySteps;
  338.  
  339. import java.util.Date;
  340. import java.util.Scanner;
  341. import java.sql.Connection;
  342. import java.sql.ResultSet;
  343. import java.sql.SQLException;
  344. import java.text.ParseException;
  345. import java.text.SimpleDateFormat;
  346. import java.time.LocalDate;
  347.  
  348. import com.mysql.cj.api.jdbc.Statement;
  349. import com.mysql.cj.jdbc.PreparedStatement;
  350.  
  351. public class Employee implements DakPlusPlusDAO {
  352. private Connection con;
  353. private java.sql.Statement stat;
  354. private String result;
  355. private Scanner in = new Scanner(System.in);
  356. private String lastName;
  357. private String name;
  358. private String more;
  359. private int r;
  360.  
  361. public Employee(Connection con) {
  362. this.con = con;
  363. }
  364.  
  365. @Override
  366. public void getAll() {
  367.  
  368. try {
  369. stat = con.createStatement();
  370. String statement = "SELECT * FROM Employees";
  371. ResultSet r = stat.executeQuery(statement);
  372. r.beforeFirst();
  373. while (r.next()) {
  374. result = "id: " + r.getInt(1) + "nNaam: " + r.getString("LastName") + "nVoornaam: "
  375. + r.getString("FirstName") + "nAdres: " + r.getString("StreetName") + "nPostcode: "
  376. + r.getString("ZipCode") + "nStad: " + r.getString("City") + "nGeboortedatum: "
  377. + r.getDate("BirthDay") + "nContracttype: " + r.getNString("ContractType") + "nStartdatum: "
  378. + r.getDate("Start") + "nEinddatum: " + r.getDate("End") + "nLoon: " + r.getDouble("Wages")
  379. + "n";
  380. System.out.println(result);
  381. }
  382. } catch (SQLException e) {
  383. System.out.println("Er is iets fout gegaan");
  384. e.printStackTrace();
  385. }
  386. }
  387.  
  388. @Override
  389. public void getItem() {
  390. int choice;
  391. do {
  392. System.out.println(
  393. "Wilt u 1. een specifieke werknemer opzoeken of 2. een kolom en waarde gebruiken om mogelijks een verzameling van werknemers terug te krijgen?");
  394. choice = in.nextInt();
  395. if (choice == 1) {
  396. try {
  397. System.out.println("Geef de achternaam van de gezochte werknemer.");
  398. lastName = in.next();
  399. System.out.println("Geef nu de voornaam van de gezochte gebruiker.");
  400. name = in.next();
  401. java.sql.PreparedStatement statement = con
  402. .prepareStatement("SELECT * FROM Employees where Lastname in (?) and Firstname in (?)");
  403. statement.setString(1, lastName);
  404. statement.setString(2, name);
  405. ResultSet r = statement.executeQuery();
  406. r.beforeFirst();
  407. while (r.next()) {
  408. result = "id: " + r.getInt(1) + "nNaam: " + r.getString("LastName") + "nVoornaam: "
  409. + r.getString("FirstName") + "nAdres: " + r.getString("StreetName") + "nPostcode: "
  410. + r.getString("ZipCode") + "nStad: " + r.getString("City") + "nGeboortedatum: "
  411. + r.getDate("BirthDay") + "nContracttype: " + r.getNString("ContractType")
  412. + "nStartdatum: " + r.getDate("Start") + "nEinddatum: " + r.getDate("End")
  413. + "nLoon: " + r.getDouble("Wages") + "n";
  414. System.out.println(result);
  415. }
  416. } catch (SQLException e) {
  417. System.out.println("Er is iets fout gegaan, controleer aub uw url, username en wachtwoord");
  418. e.printStackTrace();
  419.  
  420. }
  421. } else if (choice == 2) {
  422. int column;
  423. do {
  424. System.out.println(
  425. "Op welke kolom wilt u zoeken? U hebt de keuze uit:n1. Achternaamn2. Voornaamn3. Postcoden4. Stadn5. Contracttypen6. Startdatum van het contractn7. Einddatum van het contractn 8. Loon");
  426. column = in.nextInt();
  427. System.out.println("Geef de value waar u op wilt zoeken.");
  428. String value = in.next();
  429. switch (column) {
  430. case 1:
  431. try {
  432. java.sql.PreparedStatement statement = con
  433. .prepareStatement("SELECT * FROM Employees where Lastname in (?)");
  434. statement.setString(1, value);
  435. ResultSet r = statement.executeQuery();
  436. r.beforeFirst();
  437. while (r.next()) {
  438. result = "id: " + r.getInt(1) + "nNaam: " + r.getString("LastName") + "nVoornaam: "
  439. + r.getString("FirstName") + "nAdres: " + r.getString("StreetName")
  440. + "nPostcode: " + r.getString("ZipCode") + "nStad: " + r.getString("City")
  441. + "nGeboortedatum: " + r.getDate("BirthDay") + "nContracttype: "
  442. + r.getNString("ContractType") + "nStartdatum: " + r.getDate("Start")
  443. + "nEinddatum: " + r.getDate("End") + "nLoon: " + r.getDouble("Wages") + "n";
  444. System.out.println(result);
  445. }
  446. } catch (SQLException e) {
  447. System.out.println("Er is iets fout gegaan, controleer aub uw url, username en wachtwoord");
  448. e.printStackTrace();
  449.  
  450. }
  451. break;
  452. case 2:
  453. try {
  454. java.sql.PreparedStatement statement = con
  455. .prepareStatement("SELECT * FROM Employees where Firstname in (?)");
  456. statement.setString(1, value);
  457. ResultSet r = statement.executeQuery();
  458. r.beforeFirst();
  459. while (r.next()) {
  460. result = "id: " + r.getInt(1) + "nNaam: " + r.getString("LastName") + "nVoornaam: "
  461. + r.getString("FirstName") + "nAdres: " + r.getString("StreetName")
  462. + "nPostcode: " + r.getString("ZipCode") + "nStad: " + r.getString("City")
  463. + "nGeboortedatum: " + r.getDate("BirthDay") + "nContracttype: "
  464. + r.getNString("ContractType") + "nStartdatum: " + r.getDate("Start")
  465. + "nEinddatum: " + r.getDate("End") + "nLoon: " + r.getDouble("Wages") + "n";
  466. System.out.println(result);
  467. }
  468. } catch (SQLException e) {
  469. System.out.println("Er is iets fout gegaan, controleer aub uw url, username en wachtwoord");
  470. e.printStackTrace();
  471.  
  472. }
  473. break;
  474. case 3:
  475. try {
  476. java.sql.PreparedStatement statement = con
  477. .prepareStatement("SELECT * FROM Employees where Zipcode = ?");
  478. statement.setString(1, value);
  479. ResultSet r = statement.executeQuery();
  480. r.beforeFirst();
  481. while (r.next()) {
  482. result = "id: " + r.getInt(1) + "nNaam: " + r.getString("LastName") + "nVoornaam: "
  483. + r.getString("FirstName") + "nAdres: " + r.getString("StreetName")
  484. + "nPostcode: " + r.getString("ZipCode") + "nStad: " + r.getString("City")
  485. + "nGeboortedatum: " + r.getDate("BirthDay") + "nContracttype: "
  486. + r.getNString("ContractType") + "nStartdatum: " + r.getDate("Start")
  487. + "nEinddatum: " + r.getDate("End") + "nLoon: " + r.getDouble("Wages") + "n";
  488. System.out.println(result);
  489. }
  490. } catch (SQLException e) {
  491. System.out.println("Er is iets fout gegaan, controleer aub uw url, username en wachtwoord");
  492. e.printStackTrace();
  493.  
  494. }
  495. break;
  496. case 4:
  497. try {
  498. java.sql.PreparedStatement statement = con
  499. .prepareStatement("SELECT * FROM Employees where City in (?)");
  500. statement.setString(1, value);
  501. ResultSet r = statement.executeQuery();
  502. r.beforeFirst();
  503. while (r.next()) {
  504. result = "id: " + r.getInt(1) + "nNaam: " + r.getString("LastName") + "nVoornaam: "
  505. + r.getString("FirstName") + "nAdres: " + r.getString("StreetName")
  506. + "nPostcode: " + r.getString("ZipCode") + "nStad: " + r.getString("City")
  507. + "nGeboortedatum: " + r.getDate("BirthDay") + "nContracttype: "
  508. + r.getNString("ContractType") + "nStartdatum: " + r.getDate("Start")
  509. + "nEinddatum: " + r.getDate("End") + "nLoon: " + r.getDouble("Wages") + "n";
  510. System.out.println(result);
  511. }
  512. } catch (SQLException e) {
  513. System.out.println("Er is iets fout gegaan, controleer aub uw url, username en wachtwoord");
  514. e.printStackTrace();
  515.  
  516. }
  517. break;
  518. case 5:
  519. try {
  520. java.sql.PreparedStatement statement = con
  521. .prepareStatement("SELECT * FROM Employees where ContractType in (?)");
  522. statement.setString(1, value);
  523. ResultSet r = statement.executeQuery();
  524. r.beforeFirst();
  525. while (r.next()) {
  526. result = "id: " + r.getInt(1) + "nNaam: " + r.getString("LastName") + "nVoornaam: "
  527. + r.getString("FirstName") + "nAdres: " + r.getString("StreetName")
  528. + "nPostcode: " + r.getString("ZipCode") + "nStad: " + r.getString("City")
  529. + "nGeboortedatum: " + r.getDate("BirthDay") + "nContracttype: "
  530. + r.getNString("ContractType") + "nStartdatum: " + r.getDate("Start")
  531. + "nEinddatum: " + r.getDate("End") + "nLoon: " + r.getDouble("Wages") + "n";
  532. System.out.println(result);
  533. }
  534. } catch (SQLException e) {
  535. System.out.println("Er is iets fout gegaan, controleer aub uw url, username en wachtwoord");
  536. e.printStackTrace();
  537.  
  538. }
  539. break;
  540. case 6:
  541. try {
  542. SimpleDateFormat date = new SimpleDateFormat("dd/MM/yyyy");
  543. Date startdate = date.parse(value);
  544. java.sql.PreparedStatement statement = con
  545. .prepareStatement("SELECT * FROM Employees where Start in (?)");
  546. statement.setDate(1, (java.sql.Date) startdate);
  547. ResultSet r = statement.executeQuery();
  548. r.beforeFirst();
  549. while (r.next()) {
  550. result = "id: " + r.getInt(1) + "nNaam: " + r.getString("LastName") + "nVoornaam: "
  551. + r.getString("FirstName") + "nAdres: " + r.getString("StreetName")
  552. + "nPostcode: " + r.getString("ZipCode") + "nStad: " + r.getString("City")
  553. + "nGeboortedatum: " + r.getDate("BirthDay") + "nContracttype: "
  554. + r.getNString("ContractType") + "nStartdatum: " + r.getDate("Start")
  555. + "nEinddatum: " + r.getDate("End") + "nLoon: " + r.getDouble("Wages") + "n";
  556. System.out.println(result);
  557. }
  558. } catch (SQLException | ParseException e) {
  559. System.out.println("Er is iets fout gegaan, controleer aub uw url, username en wachtwoord");
  560. e.printStackTrace();
  561.  
  562. }
  563. break;
  564. case 7:
  565. try {
  566.  
  567. SimpleDateFormat date = new SimpleDateFormat("dd/MM/yyyy");
  568. Date enddate = date.parse(value);
  569. java.sql.PreparedStatement statement = con
  570. .prepareStatement("SELECT * FROM Employees where End in (?)");
  571. statement.setDate(1, (java.sql.Date) enddate);
  572. ResultSet r = statement.executeQuery();
  573. r.beforeFirst();
  574. while (r.next()) {
  575. result = "id: " + r.getInt(1) + "nNaam: " + r.getString("LastName") + "nVoornaam: "
  576. + r.getString("FirstName") + "nAdres: " + r.getString("StreetName")
  577. + "nPostcode: " + r.getString("ZipCode") + "nStad: " + r.getString("City")
  578. + "nGeboortedatum: " + r.getDate("BirthDay") + "nContracttype: "
  579. + r.getNString("ContractType") + "nStartdatum: " + r.getDate("Start")
  580. + "nEinddatum: " + r.getDate("End") + "nLoon: " + r.getDouble("Wages") + "n";
  581. System.out.println(result);
  582. }
  583. } catch (SQLException | ParseException e) {
  584. System.out.println("Er is iets fout gegaan, controleer aub uw url, username en wachtwoord");
  585. e.printStackTrace();
  586.  
  587. }
  588. break;
  589. case 8:
  590. try {
  591. java.sql.PreparedStatement statement = con
  592. .prepareStatement("SELECT * FROM Employees where Wages in (?)");
  593. statement.setString(1, value);
  594. ResultSet r = statement.executeQuery();
  595. r.beforeFirst();
  596. while (r.next()) {
  597. result = "id: " + r.getInt(1) + "nNaam: " + r.getString("LastName") + "nVoornaam: "
  598. + r.getString("FirstName") + "nAdres: " + r.getString("StreetName")
  599. + "nPostcode: " + r.getString("ZipCode") + "nStad: " + r.getString("City")
  600. + "nGeboortedatum: " + r.getDate("BirthDay") + "nContracttype: "
  601. + r.getNString("ContractType") + "nStartdatum: " + r.getDate("Start")
  602. + "nEinddatum: " + r.getDate("End") + "nLoon: " + r.getDouble("Wages") + "n";
  603. System.out.println(result);
  604. }
  605. } catch (SQLException e) {
  606. System.out.println("Er is iets fout gegaan, controleer aub uw url, username en wachtwoord");
  607. e.printStackTrace();
  608.  
  609. }
  610. break;
  611. default:
  612. System.out.println(
  613. "U hebt een verkeerde waarde ingevoerd.nU dient het cijfer te kiezen dat correspondeert met het gewenste menuitem.");
  614. break;
  615. }
  616. } while (column != 1 && column != 2 && column != 3 && column != 4 && column != 5 && column != 6
  617. && column != 7 && column != 8);
  618. } else {
  619. System.out.println(
  620. "U dient 1 te kiezen om een specifieke werknemer op te zoeken of 2 om een query in te voeren.");
  621. }
  622. } while (choice != 1 && choice != 2);
  623. }
  624.  
  625. @Override
  626. public int updateItem() {
  627. int column;
  628. System.out.println("Geef de achternaam van de werknemer die u wenst up te daten.");
  629. name = in.nextLine();
  630. System.out.println("Geef nu de voornaam van de werknemer die u wenst up te daten.");
  631. lastName = in.nextLine();
  632. do {
  633. System.out.println(
  634. "Welke kolom wenst u up te daten?nU hebt de keuze uit:n1. Adresn2. Contracttypen3. Einddatum van het contractn4. Loon");
  635. column = in.nextInt();
  636. do {
  637. switch (column) {
  638. case 1:
  639. System.out.println("Geef de straatnaam en huisnr in voor " + lastName + " " + name);
  640. String adress = in.nextLine();
  641. System.out.println("Geef de postcode in.");
  642. String zipcode = in.nextLine();
  643. System.out.println("Geef de stad in.");
  644. String city = in.nextLine();
  645. try {
  646. java.sql.PreparedStatement statement = con.prepareStatement(
  647. "UPDATE Employees SET Streetname = ?, zipcode = ?, city = ? where Lastname in (?) and Firstname in (?)");
  648. statement.setString(1, adress);
  649. statement.setString(2, zipcode);
  650. statement.setString(3, city);
  651. statement.setString(5, lastName);
  652. statement.setString(6, name);
  653. r = statement.executeUpdate();
  654. if (r != 0) {
  655. System.out.println("Uw update is geslaagd!");
  656. } else {
  657. System.out.println("Er ging iets fout!");
  658. }
  659. } catch (SQLException e) {
  660. System.out.println("Er is iets fout gegaan");
  661. e.printStackTrace();
  662. }
  663. break;
  664. case 2:
  665. System.out.println("Geef het nieuwe type in van het contract");
  666. String type = in.nextLine();
  667. try {
  668. java.sql.PreparedStatement statement = con.prepareStatement(
  669. "UPDATE Employees SET ContractType = ? where Lastname in (?) and Firstname in (?)");
  670. statement.setString(1, type);
  671. statement.setString(2, lastName);
  672. statement.setString(3, name);
  673. int r = statement.executeUpdate();
  674. if (r != 0) {
  675. System.out.println("Uw update is geslaagd!");
  676. } else {
  677. System.out.println("Er ging iets fout!");
  678. }
  679. } catch (SQLException e) {
  680. System.out.println("Er is iets fout gegaan");
  681. e.printStackTrace();
  682. }
  683. break;
  684. case 3:
  685. boolean check = false;
  686. System.out.println("Geef de einddatum van het contract in.");
  687. String end = in.nextLine();
  688. do {
  689.  
  690. SimpleDateFormat date = new SimpleDateFormat("dd/MM/yyyy");
  691. LocalDate localDate = LocalDate.now();
  692. Date d1 = java.sql.Date.valueOf(localDate);
  693. try {
  694. d1 = date.parse(end);
  695. } catch (ParseException e1) {
  696. e1.printStackTrace();
  697. }
  698. java.sql.Date enddate = new java.sql.Date(d1.getTime());
  699. Utility util = new Utility(con);
  700. if (util.startBeforeEnd(name, lastName, enddate) == false) {
  701. System.out.println("De einddatum kan niet voor de startdatum liggen.");
  702. System.out.println("Geef de nieuwe einddatum in");
  703. end = in.nextLine();
  704. check = true;
  705. } else {
  706. try {
  707. java.sql.PreparedStatement statement = con.prepareStatement(
  708. "UPDATE Employees SET End = ? where Lastname in (?) and Firstname in (?)");
  709. statement.setDate(1, enddate);
  710. statement.setString(2, lastName);
  711. statement.setString(3, name);
  712. int r = statement.executeUpdate();
  713. if (r != 0) {
  714. System.out.println("Uw update is geslaagd!");
  715. } else {
  716. System.out.println("Er ging iets fout!");
  717. }
  718. } catch (SQLException e) {
  719. System.out.println("Er is iets fout gegaan");
  720. e.printStackTrace();
  721. }
  722. }
  723. } while (check);
  724. break;
  725. case 4:
  726. System.out.println("Geef het nieuwe loon in");
  727. double wage = in.nextDouble();
  728. try {
  729. java.sql.PreparedStatement statement = con.prepareStatement(
  730. "UPDATE Employees SET Wages = ? where Lastname in (?) and Firstname in (?)");
  731. statement.setDouble(1, wage);
  732. statement.setString(2, lastName);
  733. statement.setString(3, name);
  734. int r = statement.executeUpdate();
  735. if (r != 0) {
  736. System.out.println("Uw update is geslaagd!");
  737. } else {
  738. System.out.println("Er ging iets fout!");
  739. }
  740. } catch (SQLException e) {
  741. System.out.println("Er is iets fout gegaan");
  742. e.printStackTrace();
  743. }
  744. break;
  745. }
  746. } while (column != 1 && column != 2 && column != 3 && column != 4);
  747. System.out.println("Wilt u nog een kolom updaten? Y/N");
  748. more = in.next();
  749. return r;
  750. } while (more.equalsIgnoreCase("y"));
  751. }
  752.  
  753. @Override
  754. public int addNew() {
  755. System.out.println("Geef de achternaam van de nieuwe werknemer.");
  756. lastName = in.nextLine();
  757. System.out.println("Geef nu de voornaam van de nieuwe werknemer.");
  758. name = in.nextLine();
  759. System.out.println("Geef de straatnaam en huisnr in voor " + lastName + " " + name);
  760. String adress = in.nextLine();
  761. System.out.println("Geef de postcode in.");
  762. String zipcode = in.nextLine();
  763. System.out.println("Geef de stad in.");
  764. String city = in.nextLine();
  765. System.out.println("Geef de geboortedatum in.");
  766. String birth = in.nextLine();
  767. System.out.println("Geef het contracttype in.");
  768. String contracttype = in.nextLine();
  769. System.out.println("Geef de startdatum van het contract in.");
  770. String start = in.nextLine();
  771. System.out.println("Geef het uurloon van " + name + " " + lastName + " in.");
  772. String wages = in.nextLine();
  773. double wage = Double.parseDouble(wages);
  774. try {
  775. SimpleDateFormat date = new SimpleDateFormat("dd/MM/yyyy");
  776. Date sdate = date.parse(start);
  777. java.sql.Date startdate = new java.sql.Date(sdate.getTime());
  778. sdate = date.parse(birth);
  779. java.sql.Date birthdate = new java.sql.Date(sdate.getTime());
  780. java.sql.PreparedStatement statement = con.prepareStatement(
  781. "INSERT INTO Employees (Lastname, Firstname, Streetname, zipcode, city , Birthday, contractType, start, wages) VALUES(?,?,?,?,?,?,?, ?, ?)");
  782. statement.setString(1, lastName);
  783. statement.setString(2, name);
  784. statement.setString(3, adress);
  785. statement.setString(4, zipcode);
  786. statement.setString(5, city);
  787. statement.setDate(6, birthdate);
  788. statement.setString(7, contracttype);
  789. statement.setDate(8, startdate);
  790. statement.setDouble(9, wage);
  791. r = statement.executeUpdate();
  792. if (r != 0) {
  793. System.out.println("De nieuwe werknemer is toegevoegd!");
  794. } else {
  795. System.out.println("Er ging iets fout!");
  796. }
  797. } catch (SQLException | ParseException e) {
  798. System.out.println("Er is iets fout gegaan");
  799. e.printStackTrace();
  800. }
  801. return r;
  802. }
  803.  
  804. }
  805.  
  806. package BE.Intec.Daniel.BabySteps;
  807.  
  808. import java.sql.Connection;
  809. import java.sql.ResultSet;
  810. import java.sql.SQLException;
  811. import java.text.ParseException;
  812. import java.text.SimpleDateFormat;
  813. import java.util.Date;
  814. import java.util.Scanner;
  815.  
  816. public class Projects implements DakPlusPlusDAO {
  817. private Connection con;
  818. private java.sql.Statement stat;
  819. private String result;
  820. private Scanner in = new Scanner(System.in);
  821. private int r;
  822.  
  823. public Projects(Connection con) {
  824. this.con = con;
  825. }
  826.  
  827. @Override
  828. public void getAll() {
  829. try {
  830. stat = con.createStatement();
  831. String statement = "SELECT * FROM Projects";
  832. ResultSet r = stat.executeQuery(statement);
  833. r.beforeFirst();
  834. while (r.next()) {
  835. result = "id: " + r.getInt("id") + "nKlant: " + r.getNString("Customer") + "nStartdatum: "
  836. + r.getDate("StartDate") + "nEinddatum: " + r.getDate("EndDate") + "nOpbrengst: "
  837. + r.getDouble("Revenue") + "nKost: " + r.getDouble("Cost");
  838. System.out.println(result);
  839. }
  840. } catch (SQLException e) {
  841. System.out.println("Er is iets fout gegaan");
  842. e.printStackTrace();
  843. }
  844. }
  845.  
  846. @Override
  847. public void getItem() {
  848. int select;
  849. do {
  850. System.out.println(
  851. "Wilt u zoeken op 1. projectid of 2. een columnvalue om mogelijks meerdere resultaten terug te krijgen.nBijvoorbeeld op klantnaam om alle projecten van deze klant te zien.");
  852. select = in.nextInt();
  853. if (select == 1) {
  854. System.out.println("Geef het projectid van het gezocht project.");
  855. int id = in.nextInt();
  856. try {
  857. java.sql.PreparedStatement statement = con.prepareStatement("SELECT * FROM Projects where id = ?");
  858. statement.setInt(1, id);
  859. ResultSet r = statement.executeQuery();
  860. r.beforeFirst();
  861. while (r.next()) {
  862. result = "id: " + r.getInt("id") + "nKlant: " + r.getNString("Customer") + "nStartdatum: "
  863. + r.getDate("StartDate") + "nEinddatum: " + r.getDate("EndDate") + "nOpbrengst: "
  864. + r.getDouble("Revenue") + "nKost: " + r.getDouble("Cost");
  865. System.out.println(result);
  866. }
  867. } catch (SQLException e) {
  868. System.out.println("Er is iets fout gegaan");
  869. e.printStackTrace();
  870. }
  871. } else if (select == 2) {
  872. int column;
  873. do {
  874. System.out.println(
  875. "Wilt u zoeken in de kolomn 1: Klantn 2: Startdatumn 3: Einddatumn 4: Opbrengstn 5: Kost");
  876. column = in.nextInt();
  877. System.out.println("Op welke waarde wilt u in deze kolom zoeken?");
  878. String value = in.next();
  879. switch (column) {
  880. case 1:
  881. try {
  882. java.sql.PreparedStatement statement = con
  883. .prepareStatement("SELECT * FROM Projects where customer = ?");
  884. statement.setString(1, value);
  885. ResultSet r = statement.executeQuery();
  886. r.beforeFirst();
  887. while (r.next()) {
  888. result = "id: " + r.getInt("id") + "nKlant: " + r.getNString("Customer")
  889. + "nStartdatum: " + r.getDate("StartDate") + "nEinddatum: "
  890. + r.getDate("EndDate") + "nOpbrengst: " + r.getDouble("Revenue") + "nKost: "
  891. + r.getDouble("Cost");
  892. System.out.println(result);
  893. }
  894. } catch (SQLException e) {
  895. System.out.println("Er is iets fout gegaan");
  896. e.printStackTrace();
  897. }
  898. break;
  899. case 2:
  900. try {
  901. SimpleDateFormat date = new SimpleDateFormat("dd/MM/yyyy");
  902. Date d1 = date.parse(value);
  903. java.sql.Date startdate = new java.sql.Date(d1.getTime());
  904. java.sql.PreparedStatement statement = con
  905. .prepareStatement("SELECT * FROM Projects where Startdate = ?");
  906. statement.setDate(1, startdate);
  907. ResultSet r = statement.executeQuery();
  908. r.beforeFirst();
  909. while (r.next()) {
  910. result = "id: " + r.getInt("id") + "nKlant: " + r.getNString("Customer")
  911. + "nStartdatum: " + r.getDate("StartDate") + "nEinddatum: "
  912. + r.getDate("EndDate") + "nOpbrengst: " + r.getDouble("Revenue") + "nKost: "
  913. + r.getDouble("Cost");
  914. System.out.println(result);
  915. }
  916. } catch (SQLException | ParseException e) {
  917. System.out.println("Er is iets fout gegaan");
  918. e.printStackTrace();
  919. }
  920. break;
  921. case 3:
  922. try {
  923. SimpleDateFormat date = new SimpleDateFormat("dd/MM/yyyy");
  924. Date d1 = date.parse(value);
  925. java.sql.Date enddate = new java.sql.Date(d1.getTime());
  926. java.sql.PreparedStatement statement = con
  927. .prepareStatement("SELECT * FROM Projects where EndDate = ?");
  928. statement.setDate(1, enddate);
  929. ResultSet r = statement.executeQuery();
  930. r.beforeFirst();
  931. while (r.next()) {
  932. result = "id: " + r.getInt("id") + "nKlant: " + r.getNString("Customer")
  933. + "nStartdatum: " + r.getDate("StartDate") + "nEinddatum: "
  934. + r.getDate("EndDate") + "nOpbrengst: " + r.getDouble("Revenue") + "nKost: "
  935. + r.getDouble("Cost");
  936. System.out.println(result);
  937. }
  938. } catch (SQLException | ParseException e) {
  939. System.out.println("Er is iets fout gegaan");
  940. e.printStackTrace();
  941. }
  942. break;
  943. case 4:
  944. try {
  945. java.sql.PreparedStatement statement = con
  946. .prepareStatement("SELECT * FROM Projects where Revenue = ?");
  947. double newValue = Double.parseDouble(value);
  948. statement.setDouble(1, newValue);
  949. ResultSet r = statement.executeQuery();
  950. r.beforeFirst();
  951. while (r.next()) {
  952. result = "id: " + r.getInt("id") + "nKlant: " + r.getNString("Customer")
  953. + "nStartdatum: " + r.getDate("StartDate") + "nEinddatum: "
  954. + r.getDate("EndDate") + "nOpbrengst: " + r.getDouble("Revenue") + "nKost: "
  955. + r.getDouble("Cost");
  956. System.out.println(result);
  957. }
  958. } catch (SQLException e) {
  959. System.out.println("Er is iets fout gegaan");
  960. e.printStackTrace();
  961. }
  962. break;
  963. case 5:
  964. try {
  965. java.sql.PreparedStatement statement = con
  966. .prepareStatement("SELECT * FROM Projects where Cost = ?");
  967. double newValue = Double.parseDouble(value);
  968. statement.setDouble(1, newValue);
  969. ResultSet r = statement.executeQuery();
  970. r.beforeFirst();
  971. while (r.next()) {
  972. result = "id: " + r.getInt("id") + "nKlant: " + r.getNString("Customer")
  973. + "nStartdatum: " + r.getDate("StartDate") + "nEinddatum: "
  974. + r.getDate("EndDate") + "nOpbrengst: " + r.getDouble("Revenue") + "nKost: "
  975. + r.getDouble("Cost");
  976. System.out.println(result);
  977. }
  978. } catch (SQLException e) {
  979. System.out.println("Er is iets fout gegaan");
  980. e.printStackTrace();
  981. }
  982. break;
  983. default:
  984. System.out.println("U dient een getal te kiezen van 1 tot en met 5");
  985. break;
  986. }
  987. } while (column > 5 || column < 1);
  988. } else {
  989. System.out
  990. .println("U dient 1 te kiezen om te zoeken via id of 2 om te zoeken via een bepaalde waarde.");
  991. }
  992. } while (select != 1 && select != 2);
  993. }
  994.  
  995. @Override
  996. public int updateItem() {
  997. String updateOther;
  998. int choice;
  999. do {
  1000. System.out.println("Geef de id in van het te updaten project");
  1001. int projectId = in.nextInt();
  1002. do {
  1003. System.out.println(
  1004. "Welke kolom wilt u updaten. Kies uit:n1. Startdatumn2. Eindatumn3. Opbrengstn4. Kostn0. Stop met dit project up te daten");
  1005. choice = in.nextInt();
  1006. switch (choice) {
  1007. case 1:
  1008. try {
  1009. System.out.println("Geef de nieuwe startdatum in.");
  1010. String newStartdate = in.next();
  1011. SimpleDateFormat date = new SimpleDateFormat("dd/MM/yyyy");
  1012. Date d1 = date.parse(newStartdate);
  1013. java.sql.Date startdate = new java.sql.Date(d1.getTime());
  1014. java.sql.PreparedStatement statement = con
  1015. .prepareStatement("UPDATE Projects SET StartDate = ? where id = ?");
  1016. statement.setDate(1, startdate);
  1017. statement.setInt(2, projectId);
  1018. r = statement.executeUpdate();
  1019. if (r != 0) {
  1020. System.out.println("Uw update is geslaagd!");
  1021. } else {
  1022. System.out.println("Er ging iets fout!");
  1023. }
  1024. } catch (SQLException | ParseException e) {
  1025. System.out.println("Er is iets fout gegaan");
  1026. e.printStackTrace();
  1027. }
  1028. break;
  1029. case 2:
  1030. try {
  1031. System.out.println("Geef de nieuwe einddatum in.");
  1032. String newEnddate = in.next();
  1033. SimpleDateFormat date = new SimpleDateFormat("dd/MM/yyyy");
  1034. Date d1 = date.parse(newEnddate);
  1035. java.sql.Date enddate = new java.sql.Date(d1.getTime());
  1036. java.sql.PreparedStatement statement = con
  1037. .prepareStatement("UPDATE Projects SET EndDate = ? where id = ?");
  1038. statement.setDate(1, enddate);
  1039. statement.setInt(2, projectId);
  1040. r = statement.executeUpdate();
  1041. if (r != 0) {
  1042. System.out.println("Uw update is geslaagd!");
  1043. } else {
  1044. System.out.println("Er ging iets fout!");
  1045. }
  1046. } catch (SQLException | ParseException e) {
  1047. System.out.println("Er is iets fout gegaan");
  1048. e.printStackTrace();
  1049. }
  1050. break;
  1051. case 3:
  1052. try {
  1053. System.out.println("Geef de nieuwe opbrengst in.");
  1054. Double newRevenue = in.nextDouble();
  1055. java.sql.PreparedStatement statement = con
  1056. .prepareStatement("UPDATE Projects SET Revenue = ? where id = ?");
  1057. statement.setDouble(1, newRevenue);
  1058. statement.setInt(2, projectId);
  1059. r = statement.executeUpdate();
  1060. if (r != 0) {
  1061. System.out.println("Uw update is geslaagd!");
  1062. } else {
  1063. System.out.println("Er ging iets fout!");
  1064. }
  1065. } catch (SQLException e) {
  1066. System.out.println("Er is iets fout gegaan");
  1067. e.printStackTrace();
  1068. }
  1069. break;
  1070. case 4:
  1071. do {
  1072. System.out.println("Wilt u 1. de data zelf invoeren of 2. De kost laten berekenen.");
  1073. choice = in.nextInt();
  1074. if (choice == 1) {
  1075. try {
  1076. System.out.println("Geef de nieuwe kost in.");
  1077. Double newCost = in.nextDouble();
  1078. java.sql.PreparedStatement statement = con
  1079. .prepareStatement("UPDATE Projects SET Cost = ? where id = ?");
  1080. statement.setDouble(1, newCost);
  1081. statement.setInt(2, projectId);
  1082. r = statement.executeUpdate();
  1083. if (r != 0) {
  1084. System.out.println("Uw update is geslaagd!");
  1085. } else {
  1086. System.out.println("Er ging iets fout!");
  1087. }
  1088. } catch (SQLException e) {
  1089. System.out.println("Er is iets fout gegaan");
  1090. e.printStackTrace();
  1091. }
  1092. } else if (choice == 2) {
  1093. ProjectCost cost = new ProjectCost(con);
  1094. double projectCost = cost.CalculteProjectCost(projectId);
  1095. try {
  1096. java.sql.PreparedStatement statement = con
  1097. .prepareStatement("UPDATE Projects SET Cost = ? where id = ?");
  1098. statement.setDouble(1, projectCost);
  1099. statement.setInt(2, projectId);
  1100. r = statement.executeUpdate();
  1101. if (r != 0) {
  1102. System.out.println("Uw update is geslaagd!");
  1103. } else {
  1104. System.out.println("Er ging iets fout!");
  1105. }
  1106. } catch (SQLException e) {
  1107. System.out.println("Er is iets fout gegaan");
  1108. e.printStackTrace();
  1109. }
  1110. } else {
  1111. System.out.println(
  1112. "U dient 1 te kiezen om de data zelf in te voeren of 2 om de data te laten berekenen");
  1113. }
  1114. } while (choice != 1 && choice != 2);
  1115. break;
  1116. case 0:
  1117. System.out.println("U hebt gekozen om te stoppen dit project up to daten");
  1118. break;
  1119. default:
  1120. System.out.println(
  1121. "U dient 1, 2, 3 of 4 te kiezen om een kolom up te daten of 0 om te stoppen met dit project te updaten.");
  1122. }
  1123. } while (choice != 0);
  1124. System.out.println("Wilt u nog een ander project updaten? Y/N");
  1125. updateOther = in.next();
  1126. return r;
  1127. } while (updateOther.equalsIgnoreCase("y"));
  1128. }
  1129.  
  1130. @Override
  1131. public int addNew() {
  1132. System.out.println("Geef de naam van de klant in");
  1133. String customer = in.nextLine();
  1134. System.out.println("Geef de startdatum van het project in.");
  1135. String startDate = in.next();
  1136. System.out.println(
  1137. "Indien geweten geef de opbrengst van het project in, druk anders op 0 (voor kommagetalen gebruik een , en geen .)");
  1138. Double revenue = in.nextDouble();
  1139. System.out.println(
  1140. "Indien geweten geef de kost van het project in, druk anders op 0 (voor kommagetalen gebruik een , en geen .)");
  1141. Double cost = in.nextDouble();
  1142. try {
  1143. SimpleDateFormat date = new SimpleDateFormat("dd/MM/yyyy");
  1144. Date d1 = date.parse(startDate);
  1145. java.sql.Date startdate = new java.sql.Date(d1.getTime());
  1146. java.sql.PreparedStatement statement = con
  1147. .prepareStatement("INSERT INTO PROJECTS (customer, StartDate, Revenue, Cost) VALUES(?, ?, ?, ?)");
  1148. statement.setString(1, customer);
  1149. statement.setDate(2, startdate);
  1150. statement.setDouble(3, revenue);
  1151. statement.setDouble(4, cost);
  1152. r = statement.executeUpdate();
  1153. if (r != 0) {
  1154. System.out.println("Het nieuwe project is toegevoegd!");
  1155. } else {
  1156. System.out.println("Er ging iets fout!");
  1157. }
  1158. } catch (SQLException | ParseException e) {
  1159. System.out.println("Er is iets fout gegaan");
  1160. e.printStackTrace();
  1161. }
  1162. return r;
  1163. }
  1164.  
  1165. }
  1166.  
  1167. package BE.Intec.Daniel.BabySteps;
  1168.  
  1169. import java.sql.Connection;
  1170. import java.sql.ResultSet;
  1171. import java.sql.SQLException;
  1172. import java.util.Scanner;
  1173.  
  1174. public class WorkedHours implements DakPlusPlusDAO {
  1175. private Connection con;
  1176. private java.sql.Statement stat;
  1177. private String result;
  1178. private Scanner in = new Scanner(System.in);
  1179. private int r;
  1180.  
  1181. public WorkedHours(Connection con) {
  1182. this.con = con;
  1183. }
  1184.  
  1185. @Override
  1186. public void getAll() {
  1187. try {
  1188. stat = con.createStatement();
  1189. String statement = "SELECT * FROM WorkedHours";
  1190. ResultSet r = stat.executeQuery(statement);
  1191. r.beforeFirst();
  1192. while (r.next()) {
  1193. result = "id: " + r.getInt("id") + "nEmployeeId: " + r.getInt("EmployeeId") + "nGewerkte Uren: "
  1194. + r.getInt("WorkHours") + "nProjectId: " + r.getInt("ProjectId") + "nKM: "
  1195. + r.getInt("KilometersDriven") + "nBestuurder: " + r.getInt("Driver") + "nkost Arbeider: "
  1196. + r.getDouble("WorkerCost") + "n";
  1197. System.out.println(result);
  1198. }
  1199. } catch (SQLException e) {
  1200. System.out.println("Er is iets fout gegaan");
  1201. e.printStackTrace();
  1202. }
  1203. }
  1204.  
  1205. @Override
  1206. public void getItem() {
  1207. int select;
  1208. do {
  1209. System.out.println(
  1210. "Wilt u zoeken op 1. Workedid of 2. een columnvalue om mogelijks meerdere resultaten terug te krijgen.");
  1211. select = in.nextInt();
  1212. if (select == 1) {
  1213. System.out.println("Geef het Workedid van het gezocht project.");
  1214. int id = in.nextInt();
  1215. try {
  1216. java.sql.PreparedStatement statement = con
  1217. .prepareStatement("SELECT * FROM WorkedHours where id = ?");
  1218. statement.setInt(1, id);
  1219. ResultSet r = statement.executeQuery();
  1220. r.beforeFirst();
  1221. while (r.next()) {
  1222. result = "id: " + r.getInt("id") + "nEmployeeId: " + r.getInt("EmployeeId")
  1223. + "nGewerkte Uren: " + r.getInt("WorkHours") + "nProjectId: " + r.getInt("ProjectId")
  1224. + "nKM: " + r.getInt("KilometersDriven") + "nBestuurder: " + r.getInt("Driver")
  1225. + "nkost Arbeider: " + r.getDouble("WorkerCost") + "n";
  1226. System.out.println(result);
  1227. }
  1228. } catch (SQLException e) {
  1229. System.out.println("Er is iets fout gegaan");
  1230. e.printStackTrace();
  1231. }
  1232. } else if (select == 2) {
  1233. int column;
  1234. String value;
  1235. do {
  1236. System.out.println(
  1237. "In welke kolom wenst u te zoeken. U kunt kiezen uit:n1. EmployeeIdn2. Gewerkte urenn3. ProjectIdn4. Afgelegde KMn5. Bestuurder ");
  1238. column = in.nextInt();
  1239. System.out.println("Op welke waarde wilt u in deze kolom zoeken?");
  1240. value = in.next();
  1241. switch (column) {
  1242. case 1:
  1243. try {
  1244. java.sql.PreparedStatement statement = con
  1245. .prepareStatement("SELECT * FROM WorkedHours where EmployeeId = ?");
  1246. statement.setString(1, value);
  1247. ResultSet r = statement.executeQuery();
  1248. r.beforeFirst();
  1249. while (r.next()) {
  1250. result = "id: " + r.getInt("id") + "nEmployeeId: " + r.getInt("EmployeeId")
  1251. + "nGewerkte Uren: " + r.getInt("WorkHours") + "nProjectId: "
  1252. + r.getInt("ProjectId") + "nKM: " + r.getInt("KilometersDriven")
  1253. + "nBestuurder: " + r.getInt("Driver") + "nkost Arbeider: "
  1254. + r.getDouble("WorkerCost") + "n";
  1255. System.out.println(result);
  1256. }
  1257. } catch (SQLException e) {
  1258. System.out.println("Er is iets fout gegaan");
  1259. e.printStackTrace();
  1260. }
  1261. break;
  1262. case 2:
  1263. int newValue = Integer.parseInt(value);
  1264. try {
  1265. java.sql.PreparedStatement statement = con
  1266. .prepareStatement("SELECT * FROM WorkedHours where WorkHours = ?");
  1267. statement.setInt(1, newValue);
  1268. ResultSet r = statement.executeQuery();
  1269. r.beforeFirst();
  1270. while (r.next()) {
  1271. result = "id: " + r.getInt("id") + "nEmployeeId: " + r.getInt("EmployeeId")
  1272. + "nGewerkte Uren: " + r.getInt("WorkHours") + "nProjectId: "
  1273. + r.getInt("ProjectId") + "nKM: " + r.getInt("KilometersDriven")
  1274. + "nBestuurder: " + r.getInt("Driver") + "nkost Arbeider: "
  1275. + r.getDouble("WorkerCost") + "n";
  1276. System.out.println(result);
  1277. }
  1278. } catch (SQLException e) {
  1279. System.out.println("Er is iets fout gegaan");
  1280. e.printStackTrace();
  1281. }
  1282. break;
  1283. case 3:
  1284. try {
  1285. java.sql.PreparedStatement statement = con
  1286. .prepareStatement("SELECT * FROM WorkedHours where ProjectId = ?");
  1287. statement.setString(1, value);
  1288. ResultSet r = statement.executeQuery();
  1289. r.beforeFirst();
  1290. while (r.next()) {
  1291. result = "id: " + r.getInt("id") + "nEmployeeId: " + r.getInt("EmployeeId")
  1292. + "nGewerkte Uren: " + r.getInt("WorkHours") + "nProjectId: "
  1293. + r.getInt("ProjectId") + "nKM: " + r.getInt("KilometersDriven")
  1294. + "nBestuurder: " + r.getInt("Driver") + "nkost Arbeider: "
  1295. + r.getDouble("WorkerCost") + "n";
  1296. ;
  1297. System.out.println(result);
  1298. }
  1299. } catch (SQLException e) {
  1300. System.out.println("Er is iets fout gegaan");
  1301. e.printStackTrace();
  1302. }
  1303. break;
  1304. case 4:
  1305. newValue = Integer.parseInt(value);
  1306. try {
  1307. java.sql.PreparedStatement statement = con
  1308. .prepareStatement("SELECT * FROM WorkedHours where KilometersDriven = ?");
  1309. statement.setInt(1, newValue);
  1310. ResultSet r = statement.executeQuery();
  1311. r.beforeFirst();
  1312. while (r.next()) {
  1313. result = "id: " + r.getInt("id") + "nEmployeeId: " + r.getInt("EmployeeId")
  1314. + "nGewerkte Uren: " + r.getInt("WorkHours") + "nProjectId: "
  1315. + r.getInt("ProjectId") + "nKM: " + r.getInt("KilometersDriven")
  1316. + "nBestuurder: " + r.getInt("Driver") + "nkost Arbeider: "
  1317. + r.getDouble("WorkerCost") + "n";
  1318. System.out.println(result);
  1319. }
  1320. } catch (SQLException e) {
  1321. System.out.println("Er is iets fout gegaan");
  1322. e.printStackTrace();
  1323. }
  1324. break;
  1325. case 5:
  1326. newValue = Integer.parseInt(value);
  1327. try {
  1328. java.sql.PreparedStatement statement = con
  1329. .prepareStatement("SELECT * FROM WorkedHours where Driver = ?");
  1330. statement.setInt(1, newValue);
  1331. ResultSet r = statement.executeQuery();
  1332. r.beforeFirst();
  1333. while (r.next()) {
  1334. result = "id: " + r.getInt("id") + "nEmployeeId: " + r.getInt("EmployeeId")
  1335. + "nGewerkte Uren: " + r.getInt("WorkHours") + "nProjectId: "
  1336. + r.getInt("ProjectId") + "nKM: " + r.getInt("KilometersDriven")
  1337. + "nBestuurder: " + r.getInt("Driver") + "nkost Arbeider: "
  1338. + r.getDouble("WorkerCost") + "n";
  1339. System.out.println(result);
  1340. }
  1341. } catch (SQLException e) {
  1342. System.out.println("Er is iets fout gegaan");
  1343. e.printStackTrace();
  1344. }
  1345. break;
  1346. default:
  1347. System.out.println("U dient het coresponderdeerde cijfer van het menuItem te kiezen");
  1348. }
  1349. } while (column != 1 && column != 2 && column != 3 && column != 4 && column != 5);
  1350. } else {
  1351. System.out.println("U dient 1 te kiezen om te zoeken op id of 2 om een query in te voeren");
  1352. }
  1353. } while (select != 1 && select != 2);
  1354.  
  1355. }
  1356.  
  1357. @Override
  1358. public int updateItem() {
  1359. String more;
  1360. do {
  1361. System.out.println("Geef de id in van de up te daten rij.");
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
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!