// TableDisplay.java
// Andrew Davison, ad@coe.psu.ac.th, Nov 2022

/* Displays a specified table from a database inside
   a scrollable JTable in a window. If the user doesn't
   supply a table name then the first table found in the
   database is shown.

   The window can be resized, and the columns reordered.

   A row number column is added to the display, which is
   gray with white, centered text. That uses the
   ColumnColorRenderer class.

   If a cell is clicked upon, a message box opens to show the
   cell's text, and the text is printed to stdout.

   Usage:
     > compile TableDisplay.java
     > run TableDisplay TableDisplay <db fnm> [<table name>]
   e.g.
     > run TableDisplay names.db
     > run TableDisplay chinook.db invoices 
*/

import java.awt.*;
import java.awt.event.*;
import java.util.*;
import javax.swing.*;
import javax.swing.table.*;
import javax.swing.event.*;

import java.sql.*;



public class TableDisplay extends JFrame 
{
  private Connection conn;
   
   
  public TableDisplay(String dbFnm, String tableNm) 
  {   
    JPanel pane = null;
    try {
      System.out.println("Connecting to " + dbFnm);
      conn = DriverManager.getConnection("jdbc:sqlite:" + dbFnm);

      if (tableNm == null) {
        // select the first table in the database
        ArrayList<String> tableNms =  DBUtils.getTableNames(conn);
        System.out.println("Tables: \n" + tableNms);
        tableNm = tableNms.get(0);
      }
      System.out.println("Displaying table \"" + tableNm + "\"");
      setTitle(dbFnm + ": " + tableNm);

      Statement stat = conn.createStatement();
      ResultSet rs = stat.executeQuery("SELECT * from " + tableNm);
      pane = displayResultSet(rs);  // load the data into a JPanel
    } 
    catch (SQLException e) 
    {  System.out.println(e.getMessage());
       System.exit(1);
    }

    setContentPane(pane);

    // setDefaultCloseOperation(WindowConstants.EXIT_ON_CLOSE);
    addWindowListener(new WindowAdapter() {   
      public void windowClosing(WindowEvent ev) 
      { 
        try {
          conn.close();
        }
        catch (SQLException ex) {}
        System.exit(0);
      }
    });

    pack();
    setLocationRelativeTo(null);
    setVisible(true);
  }  // end of TableDisplay()
  
  

  private JPanel displayResultSet(ResultSet rs) 
                                throws SQLException
  /* Build a JTable with the column names in the first row
     and the ResultSet records in the subsequent rows.

     Add an extra column to show the row number, which is
     colored gray, with white, centered text.

     Make the table cells clickable, and display the text
     in a message box and to stdout.

     The table is added to a scroll pane, which is placed
     in a resizable JPanel
  */
  {
    if (!rs.next()) {
      JOptionPane.showMessageDialog(null, 
                      "ResultSet contains no records");
      return null;
    }

    ResultSetMetaData rsmd = rs.getMetaData();
    int numCols = rsmd.getColumnCount();

    // get column names from the ResultSet
    String[] columnNms = new String[numCols+1];
    columnNms[0] = " ";   // the row number column has no label
    for (int i = 1; i <= numCols; i++) 
      columnNms[i] = rsmd.getColumnName(i);

    // create a table using the column names as row 0
    DefaultTableModel tableModel = new DefaultTableModel(columnNms, 0);
    JTable table = new JTable(tableModel);

    // fill the table with the ResultSet, one row at a time
    int rowNo = 1;
    do {
      tableModel.addRow(getRow(rowNo, rs, numCols)); 
      rowNo++;
    } while (rs.next());

    // make first column gray with white text, and center the numbers
    TableColumn tColumn = table.getColumnModel().getColumn(0);
    tColumn.setCellRenderer(
          new ColumnColorRenderer(Color.LIGHT_GRAY, Color.WHITE));
    tColumn.setPreferredWidth(30);

    // make the table's cells clickable
    table.getSelectionModel().addListSelectionListener(
                                     new ListSelectionListener(){
      public void valueChanged(ListSelectionEvent event) 
      {
        String s = table.getValueAt(table.getSelectedRow(), 
                                    table.getSelectedColumn()).toString();
        JOptionPane.showMessageDialog(null, "Selected: " + s);
        System.out.println("Selected: " + s);
      }
    });

    // make table scrollable and add to a resizeable panel
    JPanel p = new JPanel();
    p.setLayout(new BorderLayout());
    p.add( new JScrollPane(table), BorderLayout.CENTER);
    return p;
  }  // end of displayResultSet()
  



  private Object[] getRow(int rowNo, ResultSet rs, int numCols) 
                                             throws SQLException
  /* return current row of resultset as an Object array
     with an extra entry for the row number
  */
  { 
    Object[] row = new Object[numCols+1];
    row[0] = rowNo;   // add row number
    for (int i = 1; i <= numCols; i++)
      row[i] = rs.getObject(i);  // don't worry about data types
    return row;
  }  // end of getRow()


// ------------------------------------------------------

  public static void main( String args[] ) 
  {  
    if ((args.length < 1) || (args.length > 2)) {
      System.out.println("Usage: java TableDisplay <db fnm> [<table name>]");
      System.exit(1);
    }
    if (args.length == 2)
      new TableDisplay(args[0], args[1]);
    else
      new TableDisplay(args[0], null);
  }  // end of main()

  
}  // end of TableDisplay class