I'm trying to display sql table in a jtable in the table panel. I used a table formatter class to make sure I was able to pull data from sql and get it to display the results. So when I currently click a button it does the sorting but if pops up separate table instead of on my jtable. I need it to be when I click button sort ascending, sort descending, sort by name it will display results on the jtable I created in the table panel. Please show me how to fix my updateTableDisplay method and my buildTablePanel method and change my code for my buttons to call those methods to display results. Thanks for any help.

import javax.swing.*; import java.awt.*; import java.awt.event.*; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; import java.util.Formatter;

/** The PopulationDemo class is a simple database veiwer for the CoffeeDB database. */

public class PopulationDemo extends JFrame { JPanel tablePanel; // A panel to hold the table to display to user JPanel buttonPanelA; // A panel to hold the buttons for sorting and total buttons JPanel buttonPanelB; // A panel to hold the average, highest, lowest and exit button JTable cityTable; // The user enters a JButton sortAscendingButton; // To sort ascending order JButton sortDescendingButton; //to sort descending JButton sortNameButton; //to sort by name JButton getTotalButton; // to get the total JButton getAverageButton; //get average JButton getHighestButton; //get highest JButton getLowestButton; // get lowest JButton exitButton; // To quit the application

/** Constructor */

public PopulationDemo() { // Set the window title. setTitle("Samantha Bufalo CIS 285 Assignment 8");

// Specify an action for the close button. setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

// Build the Query Panel. buildtablePanel();

// Build the Button Panel. buildButtonPanelA(); //Build the ButtonB Panel buildButtonPanelB(); //update table updateDisplayTable(); // Add the panels to the content pane. add(tablePanel, BorderLayout.NORTH); add(buttonPanelA, BorderLayout.CENTER); add(buttonPanelB, BorderLayout.SOUTH);

// Pack and display. pack(); setVisible(true); }

/** The buildtablePanel method builds a panel to hold the text area that the user will enter a query into. */

private void buildtablePanel() { // Create a panel. tablePanel = new JPanel();

// Create a text area, 8 rows by 50 columns. cityTable = new JTable(); // Put the table in a scroll pane. JScrollPane scrollPane = new JScrollPane();

// Add the table to the content pane. add(scrollPane, BorderLayout.CENTER); // Set the size and display. setSize(WIDTH, HEIGHT);

// Add the text area to the panel. tablePanel.add(scrollPane); //pack and display pack(); setVisible(true); }

/** The buildButtonPanel method builds a panel to hold the Submit and Exit buttons. */

private void buildButtonPanelA() { // Create a panel. buttonPanelA = new JPanel(); //Create a Sort Ascending button sortAscendingButton = new JButton("Sort Ascending"); //Create a Sort Descending button sortDescendingButton = new JButton("Sort Descending"); //Create a Sort Name button sortNameButton = new JButton("Sort Name"); //Create a Get Total getTotalButton = new JButton("Get Total");

// Register an action listener for the Sort Ascending button. sortAscendingButton.addActionListener(new SortAscendingButtonListener()); //Register an action listener for the Sort Descending button. sortDescendingButton.addActionListener(new SortDescendingButtonListener()); //Register an action listener for the Sort Name button sortNameButton.addActionListener(new SortNameButtonListener()); //Register an action listener for the Get Total button getTotalButton.addActionListener(new GetTotalButtonListener());

// Add the two buttons to the panel. buttonPanelA.add(sortAscendingButton); buttonPanelA.add(sortDescendingButton); buttonPanelA.add(sortNameButton); buttonPanelA.add(getTotalButton); } private void buildButtonPanelB() { // Create a panel. buttonPanelB = new JPanel(); //Create a Average button getAverageButton = new JButton("Get Average"); //Create a Highest button getHighestButton = new JButton("Get Highest"); //Create a Get Lowest button getLowestButton = new JButton("Get Lowest"); //Create a Exit exitButton = new JButton("EXit");

// Register an action listener for the Sort Ascending button. getAverageButton.addActionListener(new GetAverageButtonListener()); // Register an action listener for the Sort Descending button. getHighestButton.addActionListener(new GetHighestButtonListener()); //Register an action listener for the Sort Name button getLowestButton.addActionListener(new GetLowestButtonListener()); //Register an action listener for the Get Total button exitButton.addActionListener(new ExitButtonListener());

// Add the two buttons to the panel. buttonPanelB.add(getAverageButton); buttonPanelB.add(getHighestButton); buttonPanelB.add(getLowestButton); buttonPanelB.add(exitButton); }

/** The SortAscendingButtonListener class is an action listener for the sort ascending button. */

private class SortAscendingButtonListener implements ActionListener { public void actionPerformed(ActionEvent e) { try { String query = cityTable.getName(); query = "SELECT * FROM City ORDER BY Population"; CityDBQuery dbQuery = new CityDBQuery(query); //Get the column names String[] colNames = dbQuery.getColumnNames(); //Get the table data String[][] rowData = dbQuery.getTableData(); //Display the results in a table TableFormatter table = new TableFormatter(rowData, colNames); } catch(Exception ex) { System.out.println("ERROR: " + ex.getMessage());; } }

}

/** The sortDescendingButton is an action listener * for the sort descending button */

private class SortDescendingButtonListener implements ActionListener { public void actionPerformed(ActionEvent e) { try { String query = cityTable.getName(); query = "SELECT * FROM City ORDER BY Population DESC"; CityDBQuery dbQuery = new CityDBQuery(query); //Get the column names String[] colNames = dbQuery.getColumnNames(); //Get the table data String[][] rowData = dbQuery.getTableData(); //Display the results in a table TableFormatter table = new TableFormatter(rowData, colNames); } catch(Exception ex) { System.out.println("ERROR: " + ex.getMessage());; } }

}

/** the SortNameButtonListener is an action listener * for the sort name button */ private class SortNameButtonListener implements ActionListener { public void actionPerformed(ActionEvent e) {

try { String query = cityTable.getName(); query = "SELECT * FROM City ORDER BY CityName"; CityDBQuery dbQuery = new CityDBQuery(query); //Get the column names String[] colNames = dbQuery.getColumnNames(); //Get the table data String[][] rowData = dbQuery.getTableData(); //Display the results in a table TableFormatter table = new TableFormatter(rowData, colNames); } catch(Exception ex) { System.out.println("ERROR: " + ex.getMessage());; } } }

/** The GetTotalButtonListener class is an action listener * for the get total button */

private class GetTotalButtonListener implements ActionListener { public void actionPerformed(ActionEvent e) {

double total = 0.0;

final String DB_URL = "jdbc:sqlserver://localhost;instanceName=sqlexpress;" + "databaseName=CoffeeDB;integratedSecurity=true;create=true";

String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";

try { //Create a connection to the database Connection conn = DriverManager.getConnection(DB_URL); //Create a statement object Statement stmt = conn.createStatement(); //Create SELECT statements to get the average prices. String sumStatement = "SELECT SUM(Population) FROM City "; //get the average population ResultSet sumResult = stmt.executeQuery(sumStatement); if(sumResult.next()) total = sumResult.getDouble(1); //Display the results System.out.printf("Total price: $%.2f ", total); //close the connection conn.close(); } catch(Exception ex) { System.out.println("ERROR: " + ex.getMessage());; } } }

/** the GetAverageButtonListener class is an action listener * for the get average button */ private class GetAverageButtonListener implements ActionListener { public void actionPerformed(ActionEvent e) { double average = 0.0; final String DB_URL = "jdbc:sqlserver://localhost;instanceName=sqlexpress;" + "databaseName=CoffeeDB;integratedSecurity=true;create=true"; String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; try { //Create a connection to the database Connection conn = DriverManager.getConnection(DB_URL); //Create a statement object Statement stmt = conn.createStatement(); //Create SELECT statements to get the average prices. String avgStatement = "SELECT AVG(Population) FROM City "; //get the average population ResultSet avgResult = stmt.executeQuery(avgStatement); if(avgResult.next()) average = avgResult.getDouble(1); //Display the results System.out.printf("Average price: $%.2f ", average); //close the connection conn.close(); } catch(Exception ex) { System.out.println("ERROR: " + ex.getMessage());; } } }

/**The GetHighestButtonListener class is an action listener * for the get highest button */

private class GetHighestButtonListener implements ActionListener { public void actionPerformed(ActionEvent e) { double highest = 0.0; final String DB_URL = "jdbc:sqlserver://localhost;instanceName=sqlexpress;" + "databaseName=CoffeeDB;integratedSecurity=true;create=true"; String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; try { //Create a connection to the database Connection conn = DriverManager.getConnection(DB_URL); //Create a statement object Statement stmt = conn.createStatement(); //Create SELECT statements to get the average prices. String maxStatement = "SELECT MAX(Population) FROM City "; //get the highest population ResultSet maxResult = stmt.executeQuery(maxStatement); if(maxResult.next()) highest = maxResult.getDouble(1); //Display the results System.out.printf("Highest price: $%.2f ", highest); //close the connection conn.close(); } catch(Exception ex) { System.out.println("ERROR: " + ex.getMessage());; } } }

/**The GetLowestButtonListener class is an action listner * for the get lowest Button */

private class GetLowestButtonListener implements ActionListener { public void actionPerformed(ActionEvent e) { double lowest = 0.0; final String DB_URL = "jdbc:sqlserver://localhost;instanceName=sqlexpress;" + "databaseName=CoffeeDB;integratedSecurity=true;create=true"; String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; try { //Create a connection to the database Connection conn = DriverManager.getConnection(DB_URL); //Create a statement object Statement stmt = conn.createStatement(); //Create SELECT statements to get the average prices. String minStatement = "SELECT MIN(Population) FROM City "; //get the average population ResultSet minResult = stmt.executeQuery(minStatement); if(minResult.next()) lowest = minResult.getDouble(1); //Display the results System.out.printf("Lowest price: $%.2f ", lowest); //close the connection conn.close(); } catch(Exception ex) { System.out.println("ERROR: " + ex.getMessage());; } } }

/** The ExitButtonListener class is an action listener for the Exit button. */

private class ExitButtonListener implements ActionListener { public void actionPerformed(ActionEvent e) { // End the application. System.exit(0); } }

private void updateDisplayTable() { CityDBQuery dbQuery = new CityDBQuery(query); //Get the column names String[] colNames = dbQuery.getColumnNames(); //Get the table data String[][] data = dbQuery.getTableData(); //Remove the old scroll pane tablePanel.remove(scrollPane); //Create a JTable with the results cityTable = new JTable(data, colNames); //Put the table in a scroll pane scrollPane = new JScrollPane(cityTable); //Add the table to the panel tablePanel.add(scrollPane); //Pack and display pack(); setVisible(true); }

/** The main method creates an instance of the class. */

public static void main(String[] args) { new PopulationDemo(); } }

import java.sql.*;

/** This class executes queries on the CoffeeDB database and provides the results in arrays. */

public class CityDBQuery { // Database URL Constant public final String DB_URL = "jdbc:sqlserver://localhost;instanceName=sqlexpress;" + "databaseName=CityDB;integratedSecurity=true;create=true";

String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";

private Connection conn; // Database connection private String[][] tableData; // Table data private String[] colNames; // Column names

/** Constructor */

public CityDBQuery(String query) { // Get a connection to the database. getDatabaseConnection();

try { // Create a Statement object for the query. Statement stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

// Execute the query. ResultSet resultSet = stmt.executeQuery(query);

// Get the number of rows. resultSet.last(); // Move to last row int numRows = resultSet.getRow(); // Get row number resultSet.first(); // Move to first row

// Get a metadata object for the result set. ResultSetMetaData meta = resultSet.getMetaData();

// Create an array of Strings for the column names. colNames = new String[meta.getColumnCount()];

// Store the column names in the colNames array. for (int i = 0; i < meta.getColumnCount(); i++) { // Get a column name. colNames[i] = meta.getColumnLabel(i+1); }

// Create a 2D String array for the table data. tableData = new String[numRows][meta.getColumnCount()];

// Store the columns in the tableData array. for (int row = 0; row < numRows; row++) { for (int col = 0; col < meta.getColumnCount(); col++) { tableData[row][col] = resultSet.getString(col + 1); }

// Go to the next row in the ResultSet. resultSet.next(); }

// Close the statement and connection objects. stmt.close(); conn.close(); } catch (SQLException ex) { ex.printStackTrace(); } }

/** The getDatabaseConnection method loads the JDBC and gets a connection to the database. */

private void getDatabaseConnection() { try { // Create a connection to the database. conn = DriverManager.getConnection(DB_URL); } catch (Exception ex) { ex.printStackTrace(); System.exit(0); } }

/** The getColumnNames method returns the column names. */

public String[] getColumnNames() { return colNames; }

/** The getTableData method returns the table data. */

public String[][] getTableData() { return tableData; } }