I have to do the following in Java:

III. Create a Java file for database connectivity / CRUD implementations

Functions your CRUD methods should perform:

-Inserting a ticket into the database with some ticketnum-Update record (fields?) /by ticketnum

-Delete (purge) record /by ticketnum. Include a popup message dialog box asking the user

if it is okay to delete the record. Show the ticket number in question in the popup box area.

-View record(s) /by ticketnum(s)

-Close a ticket /by ticketnum

How do I finish the code (added below) to fit the requirements from above?

Below is other infomration that might help.

The following is what the javafile should have since the above mentions about the tickets:

TICKETSGUI.java file

Run thru some of the menus that have event handling put in place already for various sub menus. For example Open a ticket and View a ticket. Then exit. These should all work. But wait.

Should a regular user for example see everyones tickets or just there own? Adjust for that.

Also should a regular user see the Admin menu or its sub menu features? Not at all. Adjust for that as well.

A quick and simple suggestion to at least block out the views of regular users would be to set the visibility off if the user is not an admin.

Finish the sub menus in the actionPerformed event where you see comments towards the end of the event handler namely where it says

/*

* continue implementing any other desired sub menu items (like

* forupdate and delete sub menus for example) with similar

* syntax &logic as shown above*

*/

Note coding sub menu items in the event handler can follow any order. Order in other words does not matter, what matters how you handle events fired by a menu selection.

The main thing when coding any menu items is to just use the name of your sub menu object youcreate or that has been created already in code

ex. for the following

elseif (e.getSource() == someMenuItem)

you would include for someMenuItem your sub menu object name and code in your action for when the user chooses that particular menu item.

Finally a nice little goodie or easter egg has been provided in the file. The code below

retrieves the id of any id that was just auto incremented as a result of an insert!! Very cool and useful indeed. You can show the auto generated id to the console or via a dialog message box and/or use it for some query.

Example: take the ticket id number generated and add that id on some insert to some other table along with any data for later referencing.

// retrieve ticket id number newly auto generated upon record insertion

ResultSet resultSet = null;

resultSet = statement.getGeneratedKeys();

intid = 0;

if (resultSet.next()) {

id = resultSet.getInt(1); // retrieve first field in table

}

// display results if successful or not to console / dialog box

if (result != 0) {

System.out.println("Ticket ID : " + id + " created successfully!!!");

JOptionPane.showMessageDialog(null, "Ticket id: " + id + " created");

} else {

System.out.println("Ticket cannot be created!!!");

}

Another is a file called: (I am not sure if this is will help as well.)

TICKETSJTABLE.java file

Nothing to do here everything works automatically pulling your metadata and placing that into the JTable to set up column structure and the grabbing of row data is accomplished automatically as well. Unless you want to tweak the table that is up to you.

After your code work is pretty much complete, please include a 3^rd table of choice and what interaction you would like that table to contain. Perhaps run some report to show verification of table data from your new table. Example tables can be a location table, ticket history table, some table to hold analytics (current vs. past?) for quick queries, etc.

The following is the started code for TicketsGUI:

ticketsGUI:

import java.awt.Color;

import java.awt.event.ActionEvent;

import java.awt.event.ActionListener;

import java.awt.event.WindowAdapter;

import java.awt.event.WindowEvent;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import javax.swing.JFrame;

import javax.swing.JMenu;

import javax.swing.JMenuBar;

import javax.swing.JMenuItem;

import javax.swing.JOptionPane;

import javax.swing.JScrollPane;

import javax.swing.JTable;

public class ticketsGUI implements ActionListener {

// class level member objects

Dao dao = new Dao(); // for CRUD operations

String chkIfAdmin = null;

private JFrame mainFrame;

JScrollPane sp = null;

// Main menu object items

private JMenu mnuFile = new JMenu("File");

private JMenu mnuAdmin = new JMenu("Admin");

private JMenu mnuTickets = new JMenu("Tickets");

/* add any more Main menu object items below */

// Sub menu item objects for all Main menu item objects

JMenuItem mnuItemExit;

JMenuItem mnuItemUpdate;

JMenuItem mnuItemDelete;

JMenuItem mnuItemOpenTicket;

JMenuItem mnuItemViewTicket;

/* add any more Sub object items below */

// constructor

public ticketsGUI(String verifyRole) {

chkIfAdmin = verifyRole;

JOptionPane.showMessageDialog(null, "Welcome " + verifyRole);

if (chkIfAdmin.equals("Admin"))

dao.createTables(); // fire up table creations (tickets / user

// tables)

/*

* else do something else if you like

*

*/

createMenu();

prepareGUI();

}

private void createMenu() {

/* Initialize sub menu items **************************************/

// initialize sub menu item for File main menu

mnuItemExit = new JMenuItem("Exit");

// add to File main menu item

mnuFile.add(mnuItemExit);

// initialize first sub menu items for Admin main menu

mnuItemUpdate = new JMenuItem("Update Ticket");

// add to Admin main menu item

mnuAdmin.add(mnuItemUpdate);

// initialize second sub menu items for Admin main menu

mnuItemDelete = new JMenuItem("Delete Ticket");

// add to Admin main menu item

mnuAdmin.add(mnuItemDelete);

// initialize first sub menu item for Tickets main menu

mnuItemOpenTicket = new JMenuItem("Open Ticket");

// add to Ticket Main menu item

mnuTickets.add(mnuItemOpenTicket);

// initialize second sub menu item for Tickets main menu

mnuItemViewTicket = new JMenuItem("View Ticket");

// add to Ticket Main menu item

mnuTickets.add(mnuItemViewTicket);

// initialize any more desired sub menu items below

/* Add action listeners for each desired menu item *************/

mnuItemExit.addActionListener(this);

mnuItemUpdate.addActionListener(this);

mnuItemDelete.addActionListener(this);

mnuItemOpenTicket.addActionListener(this);

mnuItemViewTicket.addActionListener(this);

// add any more listeners for any additional sub menu items if desired

}

private void prepareGUI() {

// initialize frame object

mainFrame = new JFrame("Tickets");

// create jmenu bar

JMenuBar bar = new JMenuBar();

bar.add(mnuFile); // add main menu items in order, to JMenuBar

bar.add(mnuAdmin);

bar.add(mnuTickets);

// add menu bar components to frame

mainFrame.setJMenuBar(bar);

mainFrame.addWindowListener(new WindowAdapter() {

// define a window close operation

public void windowClosing(WindowEvent wE) {

System.exit(0);

}

});

// set frame options

mainFrame.setSize(400, 400);

mainFrame.getContentPane().setBackground(Color.LIGHT_GRAY);

mainFrame.setLocationRelativeTo(null);

mainFrame.setVisible(true);

}

/*

* action listener fires up items clicked on from sub menus with one action

* performed event handler!

*/

@Override

public void actionPerformed(ActionEvent e) {

// TODO Auto-generated method stub

// implement actions for sub menu items

if (e.getSource() == mnuItemExit) {

System.exit(0);

} else if (e.getSource() == mnuItemOpenTicket) {

try {

// get ticket information

String ticketName = JOptionPane.showInputDialog(null, "Enter your name");

String ticketDesc = JOptionPane.showInputDialog(null, "Enter a ticket description");

// insert ticket information to database

Connection dbConn = DriverManager

.getConnection("jdbc:mysql://www.papademas.net/tickets?autoReconnect=true&useSSL=false"

+ "&user=fp411&password=411");

Statement statement = dbConn.createStatement();

int result = statement

.executeUpdate("Insert into jpapa_tickets(ticket_issuer, ticket_description) values(" + " '"

+ ticketName + "','" + ticketDesc + "')", Statement.RETURN_GENERATED_KEYS);

// retrieve ticket id number newly auto generated upon record

// insertion

ResultSet resultSet = null;

resultSet = statement.getGeneratedKeys();

int id = 0;

if (resultSet.next()) {

id = resultSet.getInt(1); // retrieve first field in table

}

// display results if successful or not to console / dialog box

if (result != 0) {

System.out.println("Ticket ID : " + id + " created successfully!!!");

JOptionPane.showMessageDialog(null, "Ticket id: " + id + " created");

} else {

System.out.println("Ticket cannot be created!!!");

}

} catch (SQLException ex) {

// TODO Auto-generated catch block

ex.printStackTrace();

}

} else if (e.getSource() == mnuItemViewTicket) {

// retrieve ticket information for viewing in JTable

try {

Connection dbConn = DriverManager

.getConnection("jdbc:mysql://www.papademas.net/tickets?autoReconnect=true&useSSL=false"

+ "&user=fp411&password=411");

Statement statement = dbConn.createStatement();

ResultSet results = statement.executeQuery("SELECT * FROM jpapa_tickets");

// Use JTable built in functionality to build a table model and

// display the table model off your result set!!!

JTable jt = new JTable(ticketsJTable.buildTableModel(results));

jt.setBounds(30, 40, 200, 300);

sp = new JScrollPane(jt);

mainFrame.add(sp);

mainFrame.setVisible(true); // refreshes or repaints frame on

// screen

statement.close();

The following code is the start for TicketJTable:

TicketsJTable:

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.SQLException;

import java.util.Vector;

import javax.swing.table.DefaultTableModel;

public class ticketsJTable {

@SuppressWarnings("unused")

private final DefaultTableModel tableModel = new DefaultTableModel();

public static DefaultTableModel buildTableModel(ResultSet rs) throws SQLException {

ResultSetMetaData metaData = rs.getMetaData();

// names of columns

Vector columnNames = new Vector();

int columnCount = metaData.getColumnCount();

for (int column = 1; column <= columnCount; column++) {

columnNames.add(metaData.getColumnName(column));

}

// data of the table

Vector> data = new Vector>();

while (rs.next()) {

Vector