· 7 years ago · Sep 16, 2018, 06:32 AM
1import java.io.BufferedReader;
2import java.io.File;
3import java.io.FileReader;
4import java.sql.Connection;
5import java.sql.DriverManager;
6import java.sql.ResultSet;
7import java.sql.SQLException;
8import java.sql.Statement;
9import java.util.ArrayList;
10import java.util.Arrays;
11import java.util.List;
12import javax.swing.JOptionPane;
13
14public class Dao
15{
16
17 static final String DB_URL = "jdbc:mysql://www.papademas.net/tickets?autoReconnect=true&useSSL=false";
18 static final String USER = "fp411";
19 static final String PASS = "411";
20 // instance fields
21 static Connection con = null;
22 Statement state = null;
23
24 // constructor
25 public static Connection getConnection()
26 {
27 // Setup the connection with the DB
28 try
29 {
30 con = DriverManager.getConnection(DB_URL, USER, PASS);
31 }
32 catch (SQLException e)
33 {
34 e.printStackTrace();
35 }
36 return con;
37 }
38
39 public void createTables()
40 {
41 // variables for SQL Query table creations
42 final String createTicketsTable = "CREATE TABLE jkrupa_tickets2(ticket_id INT AUTO_INCREMENT PRIMARY KEY, ticket_issuer VARCHAR(30), ticket_description VARCHAR(200), ticket_status VARCHAR(30), ticket_opendate DATETIME, ticket_closedate DATETIME)";
43 final String createUsersTable = "CREATE TABLE jkrupa_users2(uid INT AUTO_INCREMENT PRIMARY KEY, uname VARCHAR(30), upass VARCHAR(30))";
44 final String createTicketsHistoryTable = "CREATE TABLE jkrupa_ticketshistory2(ticket_id INT AUTO_INCREMENT PRIMARY KEY, ticket_issuer VARCHAR(30), ticket_description VARCHAR(200), ticket_status VARCHAR(30), ticket_opendate DATETIME, ticket_closedate DATETIME)";
45
46 try
47 {
48
49 // create table
50
51 state = getConnection().createStatement();
52
53 state.executeUpdate(createTicketsTable);
54 state.executeUpdate(createUsersTable);
55 state.executeUpdate(createTicketsHistoryTable);
56 System.out.println("Created tables in database");
57
58 // end create table
59 // close connection/statement object
60 state.close();
61 con.close();
62 }
63 catch (Exception e)
64 {
65 System.out.println(e.getMessage());
66 }
67 // add users to user table
68 addUsers();
69 }
70
71 public void addUsers()
72 {
73 // add list of users from userlist.csv file to users table
74
75 // variables for SQL Query inserts
76 String sql;
77 Connection con = null;
78 Statement state = null;
79 BufferedReader br;
80 List<List<String>> array = new ArrayList<>(); // array list to hold
81 // spreadsheet rows &
82 // columns
83
84 // read data from file
85 try {
86 br = new BufferedReader(new FileReader(new File("src/userlist.csv")));
87
88 String line;
89 while ((line = br.readLine()) != null)
90 {
91 array.add(Arrays.asList(line.split(",")));
92 }
93 }
94 catch (Exception e)
95 {
96 System.out.println("Error loading file");
97 }
98
99 try
100 {
101
102 // Setup the connection with the DB
103 con = DriverManager.getConnection(DB_URL, USER, PASS);
104 state = con.createStatement();
105
106 // create loop to grab each array index containing a list of values
107 // and PASS (insert) that data into your User table
108 for (List<String> rowData : array)
109 {
110 sql = "insert into jkrupa_users2(uname,upass) " + "values('" + rowData.get(0) + "','" + rowData.get(1) + "');";
111 state.executeUpdate(sql);
112 }
113 System.out.println("Inserts completed");
114
115 // close connection/statement object
116 state.close();
117 con.close();
118 }
119 catch (Exception e)
120 {
121 System.out.println(e.getMessage());
122 }
123 }
124
125 public static void deleteUser() throws SQLException
126 {
127
128 int dialogButton = JOptionPane.YES_NO_OPTION;
129 Connection con;
130 con = DriverManager.getConnection(DB_URL, USER, PASS);
131
132 Statement statement = con.createStatement();
133 String userID = JOptionPane.showInputDialog(null, "Please enter the user id that you want to delete");
134
135 ResultSet results = statement.executeQuery("SELECT * FROM jkrupa_users2 WHERE uid=" + userID);
136
137 if(results.next())
138 {
139 int editName = JOptionPane.showConfirmDialog(null, "Are you sure you want to delete this User?", "Warning", dialogButton);
140
141 if(editName == JOptionPane.YES_OPTION)
142 {
143 statement.executeUpdate("DELETE FROM jkrupa_users2 WHERE uid=" + userID);
144 }
145 }
146 }
147 public static void updateUser() throws SQLException
148 {
149 Connection con;
150 con = DriverManager.getConnection(DB_URL, USER, PASS);
151 Statement statement = con.createStatement();
152
153 String userID = JOptionPane.showInputDialog(null, "Please enter the user id that you want to update");
154 ResultSet results = statement.executeQuery("SELECT * FROM jkrupa_users2 WHERE uid=" + userID);
155
156 if(results.next())
157 {
158 String editName = JOptionPane.showInputDialog("Edit User", results.getString("uname"));
159 String editPass = JOptionPane.showInputDialog("Edit Password",results.getString("upass"));
160
161 statement.executeUpdate("UPDATE jkrupa_users2 SET uname = " + "'" + editName + "'" + "WHERE uid =" + userID);
162 statement.executeUpdate("UPDATE jkrupa_users2 SET upass = " + "'" + editPass + "'" + "WHERE uid =" + userID);
163 }
164 }
165 }
166Login.java:
167import java.awt.Color;
168import java.awt.FlowLayout;
169import java.awt.GridLayout;
170import java.awt.event.ActionEvent;
171import java.awt.event.ActionListener;
172import java.awt.event.WindowAdapter;
173import java.awt.event.WindowEvent;
174import java.sql.Connection;
175import java.sql.ResultSet;
176import java.sql.SQLException;
177
178import javax.swing.JButton;
179import javax.swing.JFrame;
180import javax.swing.JLabel;
181import javax.swing.JOptionPane;
182import javax.swing.JPanel;
183import javax.swing.JPasswordField;
184import javax.swing.JTextField;
185
186import com.mysql.jdbc.PreparedStatement;
187
188public class Login
189{
190
191 // create instance fields for class
192 private JFrame mainFrame;
193 private JLabel headerLabel;
194 private JLabel statusLabel;
195 private JLabel namelabel;
196 private JLabel passwordLabel;
197 private JTextField userText;
198 private JPasswordField passwordText;
199 private JButton loginButton;
200 private JPanel controlPanel;
201
202 public Login()
203 {
204 prepareGUI();
205 showTextFields();
206 }
207
208 private void prepareGUI() {
209
210 // instantiate objects
211
212 mainFrame = new JFrame("Login"); // title of window form
213 headerLabel = new JLabel("", JLabel.CENTER);
214 statusLabel = new JLabel("", JLabel.CENTER);
215 controlPanel = new JPanel();
216
217 // window frame settings
218 mainFrame.setSize(400, 400);
219 mainFrame.setLayout(new GridLayout(3, 1));
220 mainFrame.getContentPane().setBackground(Color.lightGray);
221 mainFrame.setLocationRelativeTo(null);
222
223 // frame object settings
224 headerLabel.setText("Account Access");
225 statusLabel.setSize(350, 100);
226
227 // add frame objects to mainframe
228 mainFrame.add(headerLabel);
229 mainFrame.add(controlPanel);
230 mainFrame.add(statusLabel);
231
232 mainFrame.addWindowListener(new WindowAdapter()
233 {
234 public void windowClosing(WindowEvent wE)
235 { // define a window close
236 // operation
237 System.exit(0);
238 }
239 });
240 }
241
242 private void showTextFields()
243 {
244
245 // instantiate controls
246 namelabel = new JLabel("User ID: ", JLabel.RIGHT);
247 passwordLabel = new JLabel("Password: ", JLabel.CENTER);
248 userText = new JTextField(6);
249 passwordText = new JPasswordField(6);
250
251 loginButton = new JButton("Login");
252 loginButton.addActionListener(new ActionListener()
253 {
254
255 public void actionPerformed(ActionEvent e)
256 {
257
258 /*
259 * Check credentials for various users
260 *
261 * Administrator is a super user to the ticket system Code below
262 * uses a default (temporary) hard coded admin user
263 * name/password for verification. You can change this setting
264 * if you like.
265 */
266 // Create user friendly variable name to store user name from text box
267 String userName = userText.getText();
268 // convert characters from password field to string for input validation
269 String password = new String(passwordText.getPassword());
270 boolean adminFlag = false;
271 if (userName.equals("admin") && password.equals("admin1"))
272 {
273 adminFlag = true;
274 // close of Login window
275 mainFrame.dispose();
276 // open up ticketsGUI file upon successful login
277 new ticketsGUI("Admin"); // establish role as admin via constructor call
278 }
279 /*
280 * match credentials from text fields with users table for a
281 * match for regular users
282 */
283 else if (!adminFlag)
284 {
285
286 Connection con = Dao.getConnection();
287 String queryString = "SELECT uname, upass FROM jkrupa_users2 where uname=? and upass=?";
288 PreparedStatement ps;
289 ResultSet results = null;
290 try {
291 // set up prepared statements to execute query string cleanly and safely
292 ps = (PreparedStatement) con.prepareStatement(queryString);
293 ps.setString(1, userName);
294 ps.setString(2, password);
295 results = ps.executeQuery();
296 if (results.next()) { // verify if a record match exists
297 // in table
298 JOptionPane.showMessageDialog(null, "Logging In");
299 // close of Login window
300 mainFrame.dispose();
301 // open up ticketsGUI file upon successful login
302 new ticketsGUI(userName); // establish role as
303 // regular user via
304 // constructor call
305 }
306 else
307 {
308 JOptionPane.showMessageDialog(null, "Please Check Username and Password ");
309 }
310 }
311 catch (SQLException e1)
312 {
313 // TODO Auto-generated catch block
314 e1.printStackTrace();
315 }
316 finally
317 {
318 try
319 {
320 results.close();
321 }
322 catch (SQLException e1)
323 {
324 // TODO Auto-generated catch block
325 e1.printStackTrace();
326 }
327 try
328 {
329 con.close();
330 }
331 catch (SQLException e1)
332 {
333 // TODO Auto-generated catch block
334 e1.printStackTrace();
335 }
336 }
337 }
338
339 }
340
341 });
342 // add layout type /background color to control panel
343 controlPanel.setLayout(new FlowLayout());
344 controlPanel.setBackground(Color.lightGray);
345 // add controls to control panel
346 controlPanel.add(namelabel);
347 controlPanel.add(userText);
348 controlPanel.add(passwordLabel);
349 controlPanel.add(passwordText);
350 controlPanel.add(loginButton);
351 // lastly set visibility of Window as all controls are instantiated for
352 // frame
353 mainFrame.setVisible(true);
354
355 }
356
357 public static void main(String[] args)
358 {
359 new Login();
360 }
361
362}
363TicketsGUI.java
364import java.awt.Color;
365import java.awt.event.ActionEvent;
366import java.awt.event.ActionListener;
367import java.awt.event.WindowAdapter;
368import java.awt.event.WindowEvent;
369import java.sql.Connection;
370import java.sql.DriverManager;
371import java.sql.ResultSet;
372import java.sql.SQLException;
373import java.sql.Statement;
374
375import javax.swing.JFrame;
376import javax.swing.JMenu;
377import javax.swing.JMenuBar;
378import javax.swing.JMenuItem;
379import javax.swing.JOptionPane;
380import javax.swing.JScrollPane;
381import javax.swing.JTable;
382
383
384public class ticketsGUI implements ActionListener
385{
386
387 static final String DB_URL = "jdbc:mysql://www.papademas.net/tickets?autoReconnect=true&useSSL=false";
388 static final String USER = "fp411";
389 static final String PASS = "411";
390 // class level member objects
391
392 Dao dao = new Dao(); // for CRUD operations
393 String chkIfAdmin = null;
394 private JFrame mainFrame;
395
396 JScrollPane sp = null;
397
398 // Main menu object items
399
400 private JMenu mnuFile = new JMenu("File");
401 private JMenu mnuAdmin = new JMenu("Admin");
402 private JMenu mnuTickets = new JMenu("Tickets");
403
404 // Sub menu item objects for all Main menu item objects
405 JMenuItem mnuItemExit;
406 JMenuItem mnuItemUpdate;
407 JMenuItem mnuItemDelete;
408 JMenuItem mnuItemUpdateUser;
409 JMenuItem mnuItemDeleteUser;
410 JMenuItem mnuItemOpenTicket;
411 JMenuItem mnuItemViewTicket;
412 JMenuItem mnuItemViewTicketHistory;
413
414 // constructor
415 public ticketsGUI(String verifyRole)
416 {
417
418 chkIfAdmin = verifyRole;
419 JOptionPane.showMessageDialog(null, "Welcome " + verifyRole);
420 if (chkIfAdmin.equals("Admin"))
421
422 dao.createTables();
423
424 createMenu();
425 prepareGUI();
426 }
427
428 private void createMenu()
429 {
430
431 /* Initialize sub menu items **************************************/
432 if (chkIfAdmin.equals("Admin"))
433 {
434 // initialize sub menu item for File main menu
435 mnuItemExit = new JMenuItem("Exit");
436 // add to File main menu item
437 mnuFile.add(mnuItemExit);
438
439 // initialize first sub menu items for Admin main menu
440 mnuItemUpdate = new JMenuItem("Update Ticket");
441 // add to Admin main menu item
442 mnuAdmin.add(mnuItemUpdate);
443
444 // initialize second sub menu items for Admin main menu
445 mnuItemDelete = new JMenuItem("Delete Ticket");
446 // add to Admin main menu item
447 mnuAdmin.add(mnuItemDelete);
448
449 // initialize second sub menu items for Admin main menu
450 mnuItemUpdateUser = new JMenuItem("Update User");
451 // add to Admin main menu item
452 mnuAdmin.add(mnuItemUpdateUser);
453
454 // initialize second sub menu items for Admin main menu
455 mnuItemDeleteUser = new JMenuItem("Delete User");
456 // add to Admin main menu item
457 mnuAdmin.add(mnuItemDeleteUser);
458
459 // initialize first sub menu item for Tickets main menu
460 mnuItemOpenTicket = new JMenuItem("Open Ticket");
461 // add to Ticket Main menu item
462 mnuTickets.add(mnuItemOpenTicket);
463
464 // initialize second sub menu item for Tickets main menu
465 mnuItemViewTicket = new JMenuItem("View Ticket");
466 // add to Ticket Main menu item
467 mnuTickets.add(mnuItemViewTicket);
468
469 // initialize second sub menu item for Tickets main menu
470 mnuItemViewTicketHistory = new JMenuItem("View Ticket History");
471 // add to Ticket Main menu item
472 mnuTickets.add(mnuItemViewTicketHistory);
473
474
475
476 /* Add action listeners for each desired menu item *************/
477 mnuItemExit.addActionListener(this);
478 mnuItemUpdate.addActionListener(this);
479 mnuItemDelete.addActionListener(this);
480 mnuItemOpenTicket.addActionListener(this);
481 mnuItemViewTicket.addActionListener(this);
482 mnuItemViewTicketHistory.addActionListener(this);
483 mnuItemUpdateUser.addActionListener(this);
484 mnuItemDeleteUser.addActionListener(this);
485 }
486
487 else
488 {
489 // initialize sub menu item for File main menu
490 mnuItemExit = new JMenuItem("Exit");
491 // add to File main menu item
492 mnuFile.add(mnuItemExit);
493
494 // initialize first sub menu item for Tickets main menu
495 mnuItemOpenTicket = new JMenuItem("Open Ticket");
496 // add to Ticket Main menu item
497 mnuTickets.add(mnuItemOpenTicket);
498
499 // initialize second sub menu item for Tickets main menu
500 mnuItemViewTicket = new JMenuItem("View Ticket");
501 // add to Ticket Main menu item
502 mnuTickets.add(mnuItemViewTicket);
503
504 // initialize second sub menu item for Tickets main menu
505 mnuItemViewTicketHistory = new JMenuItem("View Ticket History");
506 // add to Ticket Main menu item
507 mnuTickets.add(mnuItemViewTicketHistory);
508
509 mnuItemExit.addActionListener(this);
510 mnuItemOpenTicket.addActionListener(this);
511 mnuItemViewTicket.addActionListener(this);
512 mnuItemViewTicketHistory.addActionListener(this);
513 }
514
515 }
516
517 private void prepareGUI()
518 {
519 // initialize frame object
520 mainFrame = new JFrame("Tickets");
521
522 // create jmenu bar
523 JMenuBar bar = new JMenuBar();
524 if (chkIfAdmin.equals("Admin"))
525 {
526 bar.add(mnuFile); // add main menu items in order, to JMenuBar
527 bar.add(mnuAdmin);
528 bar.add(mnuTickets);
529 }
530 else
531 {
532 bar.add(mnuFile); // add main menu items in order, to JMenuBar
533 bar.add(mnuTickets);
534 }
535 // add menu bar components to frame
536 mainFrame.setJMenuBar(bar);
537
538 mainFrame.addWindowListener(new WindowAdapter()
539 {
540 // define a window close operation
541 public void windowClosing(WindowEvent wE)
542 {
543 System.exit(0);
544 }
545 });
546 // set frame options
547 mainFrame.setSize(400, 400);
548 mainFrame.getContentPane().setBackground(Color.LIGHT_GRAY);
549 mainFrame.setLocationRelativeTo(null);
550 mainFrame.setVisible(true);
551 }
552
553 /*
554 * action listener fires up items clicked on from sub menus with one action
555 * performed event handler!
556 */
557 @Override
558 public void actionPerformed(ActionEvent e)
559 {
560 // TODO Auto-generated method stub
561
562 // implement actions for sub menu items
563 if (e.getSource() == mnuItemExit)
564 {
565 System.exit(0);
566 }
567 else if (e.getSource() == mnuItemOpenTicket)
568 {
569 try
570 {
571
572 // get ticket information
573 String ticketName = JOptionPane.showInputDialog(null, "Enter your name");
574 String ticketDesc = JOptionPane.showInputDialog(null, "Enter a ticket description");
575 String ticketStatus = "Open";
576
577 java.util.Date dt = new java.util.Date();
578 java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
579 String ticketOpenDate = sdf.format(dt);
580
581
582 // insert ticket information to database
583 Connection dbcon = DriverManager.getConnection(DB_URL, USER, PASS);
584
585 Statement statement = dbcon.createStatement();
586
587 int result = statement.executeUpdate("Insert into jkrupa_tickets2(ticket_issuer, ticket_description, ticket_status, ticket_opendate) values(" + " '" + ticketName + "','" + ticketDesc + "','" + ticketStatus + "','" + ticketOpenDate + "')", Statement.RETURN_GENERATED_KEYS);
588
589 // retrieve ticket id number newly auto generated upon record
590 // insertion
591 ResultSet resultSet = null;
592 resultSet = statement.getGeneratedKeys();
593 int id = 0;
594 if (resultSet.next())
595 {
596 id = resultSet.getInt(1); // retrieve first field in table
597 }
598 // display results if successful or not to console / dialog box
599 if (result != 0)
600 {
601 statement.executeUpdate("Insert into jkrupa_ticketshistory2(ticket_id, ticket_issuer, ticket_description, ticket_status, ticket_opendate) values(" + id + "," + " '" + ticketName + "','" + ticketDesc + "','" + ticketStatus + "','" + ticketOpenDate + "')");
602 System.out.println("Ticket ID : " + id + " created successfully!!!");
603 JOptionPane.showMessageDialog(null, "Ticket id: " + id + " created");
604 }
605 else
606 {
607 System.out.println("Ticket cannot be created!!!");
608 }
609
610 }
611 catch (SQLException ex)
612 {
613 ex.printStackTrace();
614 }
615 }
616 else if (e.getSource() == mnuItemViewTicket)
617 {
618 // retrieve ticket information for viewing in JTable
619 try
620 {
621
622 ResultSet results;
623 Connection dbConn = DriverManager.getConnection(DB_URL, USER, PASS);
624 Statement statement = dbConn.createStatement();
625 if (chkIfAdmin.equals("Admin"))
626 {
627 results = statement.executeQuery("SELECT * FROM jkrupa_tickets2");
628 }
629 else
630 {
631 results = statement.executeQuery("SELECT * FROM jkrupa_tickets2 WHERE ticket_issuer = \"" + chkIfAdmin + "\"");
632 }
633
634 // Use JTable built in functionality to build a table model and
635 // display the table model off your result set!!!
636 JTable jt = new JTable(ticketsJTable.buildTableModel(results));
637
638 jt.setBounds(30, 40, 200, 300);
639 sp = new JScrollPane(jt);
640 mainFrame.add(sp);
641 mainFrame.setVisible(true); // refreshes or repaints frame on
642 // screen
643 statement.close();
644 dbConn.close(); // close connections!!!
645
646 }
647 catch (SQLException e1)
648 {
649 e1.printStackTrace();
650 }
651 }
652 //Admins are able to update tickets from tickets table
653 else if (e.getSource() == mnuItemUpdate)
654 {
655 try
656 {
657
658 ResultSet results;
659 Connection dbConn = DriverManager.getConnection(DB_URL, USER, PASS);
660 Statement statement = dbConn.createStatement();
661 String ticket_status = null, ticket_descript = null, ticket_closedate = null;
662
663 results = statement.executeQuery("SELECT * FROM jkrupa_tickets2");
664
665 // Use JTable built in functionality to build a table model and
666 // display the table model off your result set!!!
667 JTable jt = new JTable(ticketsJTable.buildTableModel(results));
668 int dialogButton = JOptionPane.YES_NO_OPTION;
669
670 jt.setBounds(30, 40, 200, 300);
671 sp = new JScrollPane(jt);
672 mainFrame.add(sp);
673 mainFrame.setVisible(true); // refreshes or repaints frame on
674 // screen
675
676 String ticket_id = JOptionPane.showInputDialog(null, "Please enter the ticket number you want to update");
677 int status = JOptionPane.showConfirmDialog(null, "Do you want to change the status of ticket_id " + ticket_id + " ?", "Warning", dialogButton);
678 int descript = JOptionPane.showConfirmDialog(null, "Do you want to change the description of ticket_id " + ticket_id + " ?", "Warning", dialogButton);
679 int date = JOptionPane.showConfirmDialog(null, "Do you want to close ticket " + ticket_id + " ?", "Warning", dialogButton);
680
681
682 if(status == JOptionPane.YES_OPTION)
683 {
684 ticket_status = JOptionPane.showInputDialog(null, "Please enter the new status of that ticket number");
685 }
686
687 if(descript == JOptionPane.YES_OPTION)
688 {
689 ticket_descript = JOptionPane.showInputDialog(null, "Please enter the new ticket description");
690 }
691
692 if(status == JOptionPane.YES_OPTION)
693 {
694 statement.executeUpdate("UPDATE jkrupa_tickets2 SET ticket_status = " + " '" + ticket_status + " '" + " WHERE ticket_id = " + ticket_id);
695 statement.executeUpdate("UPDATE jkrupa_ticketshistory2 SET ticket_status = " + " '" + ticket_status + " '" + " WHERE ticket_id = " + ticket_id);
696 }
697
698 if(descript == JOptionPane.YES_OPTION)
699 {
700 statement.executeUpdate("UPDATE jkrupa_tickets2 SET ticket_description = " + " '" + ticket_descript + " '" + " WHERE ticket_id = " + ticket_id);
701 statement.executeUpdate("UPDATE jkrupa_ticketshistory2 SET ticket_description = " + " '" + ticket_descript + " '" + " WHERE ticket_id = " + ticket_id);
702 }
703
704 if(date == JOptionPane.YES_OPTION)
705 {
706 java.util.Date dt = new java.util.Date();
707 java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
708 ticket_closedate = sdf.format(dt);
709
710 statement.executeUpdate("UPDATE jkrupa_tickets2 SET ticket_closedate = " + " '" + ticket_closedate + " '" + " WHERE ticket_id = " + ticket_id);
711 statement.executeUpdate("UPDATE jkrupa_ticketshistory2 SET ticket_closedate = " + " '" + ticket_closedate + " '" + " WHERE ticket_id = " + ticket_id);
712
713 statement.executeUpdate("UPDATE jkrupa_tickets2 SET ticket_status = " + " '" + "Closed" + " '" + " WHERE ticket_id = " + ticket_id);
714 statement.executeUpdate("UPDATE jkrupa_ticketshistory2 SET ticket_status = " + " '" + "Closed" + " '" + " WHERE ticket_id = " + ticket_id);
715 }
716
717 System.out.println("Ticket ID : " + ticket_id + " updated successfully!!!");
718 JOptionPane.showMessageDialog(null, "Ticket id: " + ticket_id + " updated");
719
720 statement.close();
721 dbConn.close(); // close connections!!!
722
723 }
724
725 catch (SQLException e1)
726 {
727 e1.printStackTrace();
728 }
729 }
730 //Admins are able to delete tickets from tickets table
731 else if (e.getSource() == mnuItemDelete)
732 {
733 try
734 {
735 ResultSet results;
736 Connection dbConn = DriverManager.getConnection(DB_URL, USER, PASS);
737 Statement statement = dbConn.createStatement();
738
739 results = statement.executeQuery("SELECT * FROM jkrupa_tickets2");
740
741 // Use JTable built in functionality to build a table model and
742 // display the table model off your result set!!!
743 JTable jt = new JTable(ticketsJTable.buildTableModel(results));
744 int dialogButton = JOptionPane.YES_NO_OPTION;
745
746 jt.setBounds(30, 40, 200, 300);
747 sp = new JScrollPane(jt);
748 mainFrame.add(sp);
749 mainFrame.setVisible(true); // refreshes or repaints frame on
750 // screen
751
752 String ticket_id = JOptionPane.showInputDialog(null, "Please enter the ticket number you want to delete");
753 int delete = JOptionPane.showConfirmDialog(null, "Are you sure you want to delete ticket_id " + ticket_id + " ?", "Warning", dialogButton);
754
755 if(delete == JOptionPane.YES_OPTION)
756 {
757 statement.executeUpdate("DELETE FROM jkrupa_tickets2 WHERE ticket_id = " + ticket_id);
758 }
759
760 System.out.println("Ticket ID : " + ticket_id + " deleted successfully!!!");
761 JOptionPane.showMessageDialog(null, "Ticket id: " + ticket_id + " deleted");
762
763 statement.close();
764 dbConn.close(); // close connections!!!
765
766 }
767
768 catch (SQLException e1)
769 {
770 e1.printStackTrace();
771 }
772 }
773 //Shows the ticket history of all user tickets, closed, open, past, or present, and for admins, the ticket history from every user
774 else if (e.getSource() == mnuItemViewTicketHistory)
775 {
776 try
777 {
778
779 ResultSet results;
780 Connection dbConn = DriverManager.getConnection(DB_URL, USER, PASS);
781 Statement statement = dbConn.createStatement();
782 if (chkIfAdmin.equals("Admin"))
783 {
784 results = statement.executeQuery("SELECT * FROM jkrupa_ticketshistory2");
785 }
786 else
787 {
788 results = statement.executeQuery("SELECT * FROM jkrupa_ticketshistory2 WHERE ticket_issuer = \"" + chkIfAdmin + "\"");
789 }
790
791 // Use JTable built in functionality to build a table model and
792 // display the table model off your result set!!!
793 JTable jt = new JTable(ticketsJTable.buildTableModel(results));
794
795 jt.setBounds(30, 40, 200, 300);
796 sp = new JScrollPane(jt);
797 mainFrame.add(sp);
798 mainFrame.setVisible(true); // refreshes or repaints frame on
799 // screen
800 statement.close();
801 dbConn.close(); // close connections!!!
802
803 }
804 catch (SQLException e1)
805 {
806 e1.printStackTrace();
807 }
808 }
809 //View and updates users from user list table
810 else if (e.getSource() == mnuItemUpdateUser)
811 {
812 try
813 {
814
815 ResultSet results;
816 Connection dbConn = DriverManager.getConnection(DB_URL, USER, PASS);
817 Statement statement = dbConn.createStatement();
818
819 results = statement.executeQuery("SELECT * FROM jkrupa_users2");
820
821
822 JTable jt = new JTable(ticketsJTable.buildTableModel(results));
823
824 jt.setBounds(30, 40, 200, 300);
825 sp = new JScrollPane(jt);
826 mainFrame.add(sp);
827 mainFrame.setVisible(true);
828
829 Dao.updateUser();
830 }
831
832 catch (SQLException e1)
833 {
834 e1.printStackTrace();
835 }
836
837 }
838 //View and delete users from list table
839 else if (e.getSource() == mnuItemDeleteUser)
840 {
841 try
842 {
843 ResultSet results;
844 Connection dbConn = DriverManager.getConnection(DB_URL, USER, PASS);
845 Statement statement = dbConn.createStatement();
846
847 results = statement.executeQuery("SELECT * FROM jkrupa_users2");
848
849
850 JTable jt = new JTable(ticketsJTable.buildTableModel(results));
851
852 jt.setBounds(30, 40, 200, 300);
853 sp = new JScrollPane(jt);
854 mainFrame.add(sp);
855 mainFrame.setVisible(true);
856
857 Dao.deleteUser();
858 }
859
860 catch (SQLException e1)
861 {
862 e1.printStackTrace();
863 }
864 }
865 }
866
867}
868ticketsJTable.java
869import java.sql.ResultSet;
870import java.sql.ResultSetMetaData;
871import java.sql.SQLException;
872import java.util.Vector;
873
874import javax.swing.table.DefaultTableModel;
875
876public class ticketsJTable
877{
878
879 @SuppressWarnings("unused")
880 private final DefaultTableModel tableModel = new DefaultTableModel();
881
882 public static DefaultTableModel buildTableModel(ResultSet rs) throws SQLException
883 {
884
885 ResultSetMetaData metaData = rs.getMetaData();
886
887 // names of columns
888 Vector<String> columnNames = new Vector<String>();
889 int columnCount = metaData.getColumnCount();
890 for (int column = 1; column <= columnCount; column++)
891 {
892 columnNames.add(metaData.getColumnName(column));
893 }
894
895 // data of the table
896 Vector<Vector<Object>> data = new Vector<Vector<Object>>();
897 while (rs.next())
898 {
899 Vector<Object> vector = new Vector<Object>();
900 for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++)
901 {
902 vector.add(rs.getObject(columnIndex));
903 }
904 data.add(vector);
905 }
906 // return data/col.names for JTable
907 return new DefaultTableModel(data, columnNames);
908
909 }
910
911}