I\'m trying to display sql table in a jtable in the table panel. I used a table
ID: 3809448 • Letter: I
Question
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;
}
}
Explanation / Answer
import java.awt.*;
import java.awt.event.*;
import java.sql.*;
import java.util.Vector;
import javax.swing.*;
import javax.swing.table.DefaultTableModel;
public class DisplayEmpData extends JFrame implements ActionListener {
JFrame frame1;
JLabel l0, l1, l2;
JComboBox c1;
JButton b1;
Connection con;
ResultSet rs, rs1;
Statement st, st1;
PreparedStatement pst;
String ids;
static JTable table;
String[] columnNames = {"User name", "Email", "Password", "Country"};
String from;
DisplayEmpData() {
l0 = new JLabel("Fatching Employee Information");
l0.setForeground(Color.red);
l0.setFont(new Font("Serif", Font.BOLD, 20));
l1 = new JLabel("Select name");
b1 = new JButton("submit");
l0.setBounds(100, 50, 350, 40);
l1.setBounds(75, 110, 75, 20);
b1.setBounds(150, 150, 150, 20);
b1.addActionListener(this);
setTitle("Fetching Student Info From DataBase");
setLayout(null);
setVisible(true);
setSize(500, 500);
setDefaultCloseOperation(WindowConstants.EXIT_ON_CLOSE);
add(l0);
add(l1);;
add(b1);
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.getConnection("jdbc:oracle:thin:@mcndesktop07:1521:xe", "sandeep", "welcome");
st = con.createStatement();
rs = st.executeQuery("select uname from emp");
Vector v = new Vector();
while (rs.next()) {
ids = rs.getString(1);
v.add(ids);
}
c1 = new JComboBox(v);
c1.setBounds(150, 110, 150, 20);
add(c1);
st.close();
rs.close();
} catch (Exception e) {
}
}
public void actionPerformed(ActionEvent ae) {
if (ae.getSource() == b1) {
showTableData();
}
}
public void showTableData() {
frame1 = new JFrame("Database Search Result");
frame1.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
frame1.setLayout(new BorderLayout());
//TableModel tm = new TableModel();
DefaultTableModel model = new DefaultTableModel();
model.setColumnIdentifiers(columnNames);
//DefaultTableModel model = new DefaultTableModel(tm.getData1(), tm.getColumnNames());
//table = new JTable(model);
table = new JTable();
table.setModel(model);
table.setAutoResizeMode(JTable.AUTO_RESIZE_ALL_COLUMNS);
table.setFillsViewportHeight(true);
JScrollPane scroll = new JScrollPane(table);
scroll.setHorizontalScrollBarPolicy(
JScrollPane.HORIZONTAL_SCROLLBAR_AS_NEEDED);
scroll.setVerticalScrollBarPolicy(
JScrollPane.VERTICAL_SCROLLBAR_AS_NEEDED);
from = (String) c1.getSelectedItem();
//String textvalue = textbox.getText();
String uname = "";
String email = "";
String pass = "";
String cou = "";
try {
pst = con.prepareStatement("select * from emp where UNAME='" + from + "'");
ResultSet rs = pst.executeQuery();
int i = 0;
if (rs.next()) {
uname = rs.getString("uname");
email = rs.getString("umail");
pass = rs.getString("upass");
cou = rs.getString("ucountry");
model.addRow(new Object[]{uname, email, pass, cou});
i++;
}
if (i < 1) {
JOptionPane.showMessageDialog(null, "No Record Found", "Error", JOptionPane.ERROR_MESSAGE);
}
if (i == 1) {
System.out.println(i + " Record Found");
} else {
System.out.println(i + " Records Found");
}
} catch (Exception ex) {
JOptionPane.showMessageDialog(null, ex.getMessage(), "Error", JOptionPane.ERROR_MESSAGE);
}
frame1.add(scroll);
frame1.setVisible(true);
frame1.setSize(400, 300);
}
public static void main(String args[]) {
new DisplayEmpData();
}
}