I've created both java files. CityDBQuery seems fine. I'm having issues with my populationdemo.java file. I'm having issues with my actionlisteners displaying results in tableTextArea. Could you help me get one of the actionlistener codes for a button to work and be able to display the results in the tableTextArea? thanks

CityDBQuery.java

Constructor accepts query as a String

Connects to CityDB

Executes query

Get the number of rows from result set

Get a metadata object for the result set.

Create an array of Strings for the column names.

Store the column names in the array.

Create a 2D String array for the table data.

Loop through resultset rows

Store the columns in the 2D array.

Go to the next row in the ResultSet.

Close the statement and connection objects.

PopulationDemo.java

Constructor

Set the window title to "Your Name CIS 285 Assignment 8

Specify an action for the close button.

Build Table Panel that holds the table that will be displayed to the user.

Build Button PanelA to hold the sorting and total buttons.

Build Button PanelB to hold the average, highest, lowest, and Exit buttons.

Add the panels to the content pane using BorderLayout

Pack and display.

buildTablePanel method builds a panel to hold the table that will be displayed to the user.

buildButtonPanelA method builds a panel to hold the sorting and total buttons.

buildButtonPanelB method builds a panel to hold the average, highest, lowest, and Exit buttons.

updateDisplayTable method updates the table that will be displayed to the user(given below)

Add AtionListener events for each Button

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;

/** 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 JTextArea tableTextArea; // 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 buildQueryPanel 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. tableTextArea = new JTextArea(20, 30);

// Turn line wrapping on. tableTextArea.setLineWrap(true);

// Add a scroll pane to the text area. JScrollPane qaScrollPane = new JScrollPane(tableTextArea); qaScrollPane.setHorizontalScrollBarPolicy( JScrollPane.HORIZONTAL_SCROLLBAR_NEVER); qaScrollPane.setVerticalScrollBarPolicy( JScrollPane.VERTICAL_SCROLLBAR_AS_NEEDED);

// Add the text area to the panel. tablePanel.add(qaScrollPane); }

/** The buildButtonPanel method builds a panel to hold the Submit and Exit buttons. */ private void updateDisplayTable() { //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); tablePanel.add(scrollPane); //pack and display pack(); setVisible(true); }

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); }

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

}

/** The SubmitButtonListener class is an action listener for the Submit button. */

private class SortAscendingButtonListener implements ActionListener { public void actionPerformed(ActionEvent e) { double ascending; 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 sqlStatement = "SELECT * FROM City ORDER BY Population"; //get the average population ResultSet sqlResult = stmt.executeQuery(sqlStatement); if(sqlResult.next()) ascending = sqlResult.getDouble(1); //Display the results //close the connection conn.close(); } 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) { double descending;

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 sqlStatement = "SELECT * FROM City ORDER BY Population DESC"; //get the average population ResultSet sqlResult = stmt.executeQuery(sqlStatement); if(sqlResult.next()) descending = sqlResult.getDouble(1); //Display the results //close the connection conn.close(); } 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) { String name; 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 sqlStatement = "SELECT * FROM City ORDER BY CityName"; //get the average population ResultSet sqlResult = stmt.executeQuery(sqlStatement); if(sqlResult.next()) name = sqlResult.getString(1); //Display the results //close the connection conn.close(); } 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) { //code } }

/** 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 //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 highStatement = "SELECT HIGH(Population) FROM City "; //get the average population ResultSet highResult = stmt.executeQuery(highStatement); if(highResult.next()) highest = highResult.getDouble(1); //Display the results //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 lowStatement = "SELECT LOW(Population) FROM City "; //get the average population ResultSet lowResult = stmt.executeQuery(lowStatement); if(lowResult.next()) lowest =lowResult.getDouble(1); //Display the results //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); } }

/** 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=CoffeeDB;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

// 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

// 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; } }

import java.sql.*;

/** This program creates the CityDB database. * */ public class CreateCityDB { public static void main(String[] args) throws Exception { String sql; 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 the Dvd table. System.out.println("Creating the City table..."); stmt.execute("CREATE TABLE City (" + "CityName CHAR(25) NOT NULL PRIMARY KEY, " + "Population Decimal)"); // Add some rows to the new table. sql = "INSERT INTO City VALUES" + "('Beijing', 12500000)"; stmt.executeUpdate(sql);

sql = "INSERT INTO City VALUES" + "('Buenos Aires', 13170000)"; stmt.executeUpdate(sql);

sql = "INSERT INTO City VALUES" + "('Cairo', 14450000)"; stmt.executeUpdate(sql);

sql = "INSERT INTO City VALUES" + "('Calcutta', 15100000)"; stmt.executeUpdate(sql);

sql = "INSERT INTO City VALUES" + "('Delhi', 18680000)"; stmt.executeUpdate(sql);

sql = "INSERT INTO City VALUES" + "('Jakarta', 18900000)"; stmt.executeUpdate(sql);

sql = "INSERT INTO City VALUES" + "('Karachi', 11800000)"; stmt.executeUpdate(sql);

sql = "INSERT INTO City VALUES" + "('Lagos', 13488000)"; stmt.executeUpdate(sql); sql = "INSERT INTO City VALUES" + "('London', 12875000)"; stmt.executeUpdate(sql);

sql = "INSERT INTO City VALUES" + "('Los Angeles', 15250000)"; stmt.executeUpdate(sql);

sql = "INSERT INTO City VALUES" + "('Manila', 16300000)"; stmt.executeUpdate(sql);

sql = "INSERT INTO City VALUES" + "('Mexico City', 20450000)"; stmt.executeUpdate(sql);

sql = "INSERT INTO City VALUES" + "('Moscow', 15000000)"; stmt.executeUpdate(sql);

sql = "INSERT INTO City VALUES" + "('Mumbai', 19200000)"; stmt.executeUpdate(sql);

sql = "INSERT INTO City VALUES" + "('New York City', 19750000)"; stmt.executeUpdate(sql);

sql = "INSERT INTO City VALUES" + "('Osaka', 17350000)"; stmt.executeUpdate(sql);

sql = "INSERT INTO City VALUES" + "('Sao Paulo', 18850000)"; stmt.executeUpdate(sql);

sql = "INSERT INTO City VALUES" + "('Seoul', 20550000)"; stmt.executeUpdate(sql);

sql = "INSERT INTO City VALUES" + "('Shanghai', 16650000)"; stmt.executeUpdate(sql);

sql = "INSERT INTO City VALUES" + "('Tokyo', 32450000)"; stmt.executeUpdate(sql); // Close the resources. stmt.close(); conn.close(); System.out.println("Done"); } catch(Exception ex) { System.out.println("ERROR: " + ex.getMessage()); } } }

CIS 285 nment 8- CH17 Population Database O X Assig Cit Name Population Beijing 125ET Buenos Aires 1.317E7 Cairo 1.445E7 Calcutta 1.51E7 Delhi 1.868E7 Jakarta 1.89E7 Karachi 1.18E7 Lagos 1.3488E7 London 1.287 5E7 os Angeles 1.525E7 Manila 1.63E7 Mexico City 2.045E7 Moscow 1.5E7 Mumbai 1.92E7 New York 1.975E7 Osaka 1.735E7 Sao Paulo 1.885E7 Seoul 2.055E7 Shanghai 1.665E7 Tokyo 3245ET Sort Ascending Sort Descending Sort Name Get Total Get Average Get Highest Get Lowest Ext 1. Create the City database in SQL Express 2. Populate the table using CreateCityDBjava found in code gaddis Chapter 17.zip 3. Modify the connection string to use SQL Express 4. Create 2 java files City DBQuery java Population Demoja CIS 285 nment 8- CH17 Population Database O X Assig Cit Name Population Beijing 125ET Buenos Aires 1.317E7 Cairo 1.445E7 Calcutta 1.51E7 Delhi 1.868E7 Jakarta 1.89E7 Karachi 1.18E7 Lagos 1.3488E7 London 1.287 5E7 os Angeles 1.525E7 Manila 1.63E7 Mexico City 2.045E7 Moscow 1.5E7 Mumbai 1.92E7 New York 1.975E7 Osaka 1.735E7 Sao Paulo 1.885E7 Seoul 2.055E7 Shanghai 1.665E7 Tokyo 3245ET Sort Ascending Sort Descending Sort Name Get Total Get Average Get Highest Get Lowest Ext 1. Create the City database in SQL Express 2. Populate the table using CreateCityDBjava found in code gaddis Chapter 17.zip 3. Modify the connection string to use SQL Express 4. Create 2 java files City DBQuery java Population Demoja