Having a JTable Selection.

[david foley]

OK,
I am just Wondering can any one point me in the Right Direction, I have two Tables inside my Database one is Patient and the Other is History.. Inside my Swing JFrame I have two JTables Under Each Other. When i Connected to the Database, The Patient Table populates. But how do i go about, when i Select a Name inside the Patient JTable, Get the History Table to populate with that person History

I take it I will need to Create another MYSQL Command, and Connect to that Table.

[K. Tsang]

Well first you need to have some kind of action when clicking on the patient record. Then in the action, query the history database and update/display the history jtable.

There are several ways to do the clicking action but I suggest you look into having a table model for the patient table, and maybe even for the history table.

[david foley]

For Some Reason, this code is working in a different Program i am reading from a book,

But when i try to add it to this Program i'm working on it does not seem to be working.

import java.awt.BorderLayout;
import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.table.TableModel;
import javax.swing.table.TableRowSorter;
import java.awt.Dimension;
import javax.swing.JMenu;
import javax.swing.JMenuBar;
import javax.swing.JMenuItem;
import javax.swing.JOptionPane;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.SQLException;
import javax.swing.*;
import com.jgoodies.forms.layout.FormLayout;
import com.jgoodies.forms.layout.ColumnSpec;
import com.jgoodies.forms.factories.FormFactory;
import com.jgoodies.forms.layout.RowSpec;

class NextPage extends JFrame
{
	
	 // database URL, User name and password
	   static final String DATABASE_URL = "jdbc:mysql://localhost/Doctor";
	   static final String USERNAME = "dbuser";
	   static final String PASSWORD = "dbpassword";
	   // default query retrieves all data from authors table
	   static final String DEFAULT_QUERY = "SELECT * FROM Patient";
	   
	   
    /**
     * Adding Serial Version to Program
     */
    private static final long serialVersionUID = 1L;

    /*
     * Private Fields for Creating Panels
     */
    private        JSplitPane    splitPaneV;
    private        JSplitPane    splitPaneH;
    private        JPanel        panel2;
    private        JPanel        panel3;
    private    	   JPanel        panel4;
   
    /*
     * Private Fields for Labels and Text Fields
     */
   
    private JTextField     PatientID;
    private JTextField     FirstName;
    private JTextField     LastName;   
    private JTextField     Address;
    private JTextField     PhoneNumber;
    private JLabel         lblFirstName1;
    private JLabel         lblLastName;
    private JLabel         lblAddress;
    private JLabel         lblPatientId;
    private JLabel        lblPhoneNumber;
    private JMenuBar	   menuBar;
    private JPanel panel;
    private ResultSetTableModel tableModel;
   


    public NextPage()
    {
        setTitle( "Doctor Records" ); //Set Title Bar String
   

        JPanel topPanel = new JPanel(); //Setting up Top Panel
        topPanel.setLayout( new BorderLayout() );// BorderLayout Sytle for Top Panel
        getContentPane().add( topPanel ); //Adding TopPanel to Content Pane

        // Create the panels
        createPanel1();
        createPanel2();
        createPanel4();
       
        // Create a splitter pane and Top Panel
       
        splitPaneV = new JSplitPane( JSplitPane.VERTICAL_SPLIT );
        topPanel.add( splitPaneV, BorderLayout.CENTER );
        splitPaneH = new JSplitPane( JSplitPane.HORIZONTAL_SPLIT );
        splitPaneH.setRightComponent( panel2 );
              PatientID = new JTextField(); // Add PatientID Text Field
              panel2.add(PatientID, "6, 2, 9, 1, left, top"); // Add PatientID text field to Panel 2
              PatientID.setColumns(10); // Set PatientID Columns Size to 10.
              lblFirstName1 = new JLabel("First Name"); //Set String to JLabel
              panel2.add(lblFirstName1, "2, 4, 3, 1, left, center"); //Add First Name Label to Panel2
              FirstName = new JTextField(); // Add First Name Text Field
              panel2.add(FirstName, "6, 4, 9, 1, left, top"); // Add First Name Text Field to Panel 2
              FirstName.setColumns(10); // Set Columns Size to 10
              lblLastName = new JLabel("Last Name");//Set String to JLabel
              panel2.add(lblLastName, "2, 6, 3, 1, left, center");// Add Last Name  Label to Panel 2.
              LastName = new JTextField();// Add LasntName Text Field
              panel2.add(LastName, "6, 6, 9, 1, left, top");// Add LastName text field to Panel 2
              LastName.setColumns(10);// Set Columns Size to 10
         
              lblAddress = new JLabel("Address");//Set String to JLabel
              panel2.add(lblAddress, "2, 8, 3, 1, left, center");// Add Address Label to Panel 2.
         Address = new JTextField();// Add Address Text Field
         panel2.add(Address, "6, 8, 9, 1, left, top");// Add Address text field to Panel 2
         Address.setColumns(10);// Set Columns Size to 10
        
         lblPhoneNumber = new JLabel("Phone Number");//Set String to JLabel
         panel2.add(lblPhoneNumber, "2, 10, 3, 1, left, center");// Add PhoneNumber Label to Panel 2.
        PhoneNumber = new JTextField();// Add PhoneNumber Text Field
        panel2.add(PhoneNumber, "6, 10, 9, 1, left, top");// Add Phone Number text field to Panel 2
        PhoneNumber.setColumns(10);// Set Columns Size to 10
        splitPaneV.setLeftComponent( splitPaneH );
        splitPaneV.setBottomComponent( panel4 );
     
     
        /*
         * Create & Add Panel 3 to
         * the Top Panel
         */
        
        panel = new JPanel();
        topPanel.add(panel, BorderLayout.SOUTH);
        
        panel3 = new JPanel();
        topPanel.add(panel3, BorderLayout.NORTH);
        panel3.setLayout( new BorderLayout() );
        panel3.setPreferredSize( new Dimension( 400, 100 ) );
        panel3.setMinimumSize( new Dimension( 100, 50 ) );
        panel3.add( new JLabel( "Patients:" ), BorderLayout.NORTH );


       
        
        /*
         * File Menu 
         * And Setting up the Action Listeners
         *  for Each Menu Item
         */
        
        JMenuBar menubar = new JMenuBar();
        JMenu file = new JMenu("File");// Adding a File Menu
        JMenuItem newItem = new JMenuItem("New");// Adding New Section to Menu
        JMenuItem openItem = new JMenuItem("Open File");// Adding Open File Section to Menu
        JMenuItem saveItem = new JMenuItem("Save");// Adding Save Section to Menu
        JMenuItem printItem = new JMenuItem("Print");// Adding Print Section to Menu
        JMenuItem exitItem = new JMenuItem("Exit");// Adding Exit Section to Menu
        JMenu help = new JMenu("Help"); // Adding a Help Menu
        JMenuItem aboutItem = new JMenuItem("About"); // Adding About Section to Menu
        
        
        /*
         * Action Listener to Exit the Program 
         */
       exitItem.addActionListener(new ActionListener() {
        	public void actionPerformed(ActionEvent e) 
        	{
        		System.exit(0);
        		 
        	}
        });
        
        
        /*
         * Action Listener to Have the About Page Show Up
         */
        aboutItem.addActionListener(new ActionListener() {
        	public void actionPerformed(ActionEvent e) 
        	{
        		About about= new About(); // Creating the About Page
        		about.setSize(new Dimension(500,300)); //Setting Size for Dialog
        		about.setLocation(300, 50); // Setting up the Startup Location
        		 about.setVisible(true); // Setting Visible to True
        		 
        	}
        });
        
      
        setJMenuBar(menuBar);
        file.add(newItem);
        file.add(openItem);
        file.add(saveItem);
        file.add(printItem);
        file.add(exitItem);
        help.add(aboutItem);
        menubar.add(file);
        menubar.add(help);
        
        setJMenuBar(menubar);
       
        JTable resultTable = new JTable( tableModel );
    
        panel3.add( new JScrollPane( resultTable ), BorderLayout.CENTER );
     
        try
    	  {
    	  tableModel = new ResultSetTableModel( DATABASE_URL,
    	            USERNAME, PASSWORD, DEFAULT_QUERY );
    	  } catch ( SQLException sqlException ) 
    	      {
    	         JOptionPane.showMessageDialog( null, sqlException.getMessage(), 
    	            "Database error", JOptionPane.ERROR_MESSAGE );
    	               
    	         // ensure database connection is closed
    	         tableModel.disconnectFromDatabase();
    	         
    	   
    	       System.exit( 1 ); // terminate application
    	      } // end catch
        
        new ActionListener() 
        {
           // pass query to table model
           public void actionPerformed( ActionEvent event )
           {
        	   
           } // end actionPerformed
        }  // end ActionListener inner class          
     ; // end call to addActionListener
     
     final TableRowSorter< TableModel > sorter = 
        new TableRowSorter< TableModel >( tableModel );
     resultTable.setRowSorter( sorter );
     setSize( 500, 250 ); // set window size
     setVisible( true );
    }


        
       //-------------------------=============--------------------------------------------------------------------------------// 

    public void createPanel1()
    {

    }
   
    /*
     * Create Panel 2.
     * Add TextFields and Labels inside Panel2
     */
   
    public void createPanel2()
    {
        panel2 = new JPanel();
            panel2.setLayout(new FormLayout(new ColumnSpec[] {
            		FormFactory.LABEL_COMPONENT_GAP_COLSPEC,
            		ColumnSpec.decode("61px"),
            		FormFactory.LABEL_COMPONENT_GAP_COLSPEC,
            		ColumnSpec.decode("66px"),
            		FormFactory.RELATED_GAP_COLSPEC,
            		FormFactory.DEFAULT_COLSPEC,
            		FormFactory.LABEL_COMPONENT_GAP_COLSPEC,
            		ColumnSpec.decode("51px"),
            		FormFactory.LABEL_COMPONENT_GAP_COLSPEC,
            		ColumnSpec.decode("3px"),
            		FormFactory.RELATED_GAP_COLSPEC,
            		FormFactory.DEFAULT_COLSPEC,
            		FormFactory.LABEL_COMPONENT_GAP_COLSPEC,
            		ColumnSpec.decode("68px"),
            		FormFactory.LABEL_COMPONENT_GAP_COLSPEC,
            		ColumnSpec.decode("49px"),
            		FormFactory.LABEL_COMPONENT_GAP_COLSPEC,
            		ColumnSpec.decode("78px"),
            		FormFactory.LABEL_COMPONENT_GAP_COLSPEC,
            		ColumnSpec.decode("66px"),},
            	new RowSpec[] {
            		FormFactory.UNRELATED_GAP_ROWSPEC,
            		RowSpec.decode("28px"),
            		FormFactory.RELATED_GAP_ROWSPEC,
            		FormFactory.DEFAULT_ROWSPEC,
            		FormFactory.UNRELATED_GAP_ROWSPEC,
            		RowSpec.decode("28px"),
            		FormFactory.RELATED_GAP_ROWSPEC,
            		FormFactory.DEFAULT_ROWSPEC,
            		FormFactory.UNRELATED_GAP_ROWSPEC,
            		RowSpec.decode("28px"),}));
            lblPatientId = new JLabel("Patient ID"); //Set String to JLabel
            panel2.add(lblPatientId, "2, 2, 3, 1, left, center"); // Add Patient Label to Panel 2.
           
    
   
    }

   
   
    /*
     * Create Panel FOUR. This will be for the TableModel for Patient History
     *
     */
    public void createPanel4()
    {
        panel4 = new JPanel();
        panel4.setLayout( new BorderLayout() ); //Setting up LayOut as BorderLayout
        panel4.setPreferredSize( new Dimension( 400, 100 ) ); //Setting PreferredSize
        panel4.setMinimumSize( new Dimension( 100, 50 ) ); // Placing Minimum Size to Go
        panel4.add( new JLabel( "Patient History:" ), BorderLayout.NORTH ); //Adding Label to Panel 4.
        panel4.add( new JTextArea(), BorderLayout.CENTER ); //Setting Text Border Center
    }
    
	 
	  
	  
	

    public static void main( String args[] )
    {
        /*
         * Create an instance of the Doctor application
         */
        NextPage mainFrame = new NextPage(); // Create New Frame
        mainFrame.pack(); // Set pack to clear any Free Space
        mainFrame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE); //Kill the Program Once Exit is CLicked On.
        mainFrame.setSize(new Dimension(1000,800)); // Set JFrame Size
        mainFrame.setLocation(500, 100); //Set the Location of the Program, So it Will be Located more to the Center Once the Application Starts
        mainFrame.setVisible( true ); //Set the Visible to True.
 
    
}
}

import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import javax.swing.table.AbstractTableModel;

// ResultSet rows and columns are counted from 1 and JTable 
// rows and columns are counted from 0. When processing 
// ResultSet rows or columns for use in a JTable, it is 
// necessary to add 1 to the row or column number to manipulate
// the appropriate ResultSet column (i.e., JTable column 0 is 
// ResultSet column 1 and JTable row 0 is ResultSet row 1).
public class ResultSetTableModel extends AbstractTableModel 
{
   private Connection connection;
   private Statement statement;
   private ResultSet resultSet;
   private ResultSetMetaData metaData;
   private int numberOfRows;

   // keep track of database connection status
   private boolean connectedToDatabase = false;
   
   // constructor initializes resultSet and obtains its meta data object;
   // determines number of rows
   public ResultSetTableModel( String url, String username,
      String password, String query ) throws SQLException
   {         
      // connect to database
      connection = DriverManager.getConnection( url, username, password );

      // create Statement to query database
      statement = connection.createStatement( 
         ResultSet.TYPE_SCROLL_INSENSITIVE,
         ResultSet.CONCUR_READ_ONLY );

      // update database connection status
      connectedToDatabase = true;

      // set query and execute it
      setQuery( query );
   } // end constructor ResultSetTableModel

   // get class that represents column type
   public Class getColumnClass( int column ) throws IllegalStateException
   {
      // ensure database connection is available
      if ( !connectedToDatabase ) 
         throw new IllegalStateException( "Not Connected to Database" );

      // determine Java class of column
      try 
      {
         String className = metaData.getColumnClassName( column + 1 );
         
         // return Class object that represents className
         return Class.forName( className );
      } // end try
      catch ( Exception exception ) 
      {
         exception.printStackTrace();
      } // end catch
      
      return Object.class; // if problems occur above, assume type Object
   } // end method getColumnClass

   // get number of columns in ResultSet
   public int getColumnCount() throws IllegalStateException
   {   
      // ensure database connection is available
      if ( !connectedToDatabase ) 
         throw new IllegalStateException( "Not Connected to Database" );

      // determine number of columns
      try 
      {
         return metaData.getColumnCount(); 
      } // end try
      catch ( SQLException sqlException ) 
      {
         sqlException.printStackTrace();
      } // end catch
      
      return 0; // if problems occur above, return 0 for number of columns
   } // end method getColumnCount

   // get name of a particular column in ResultSet
   public String getColumnName( int column ) throws IllegalStateException
   {    
      // ensure database connection is available
      if ( !connectedToDatabase ) 
         throw new IllegalStateException( "Not Connected to Database" );

      // determine column name
      try 
      {
         return metaData.getColumnName( column + 1 );  
      } // end try
      catch ( SQLException sqlException ) 
      {
         sqlException.printStackTrace();
      } // end catch
      
      return ""; // if problems, return empty string for column name
   } // end method getColumnName

   // return number of rows in ResultSet
   public int getRowCount() throws IllegalStateException
   {      
      // ensure database connection is available
      if ( !connectedToDatabase ) 
         throw new IllegalStateException( "Not Connected to Database" );
 
      return numberOfRows;
   } // end method getRowCount

   // obtain value in particular row and column
   public Object getValueAt( int row, int column ) 
      throws IllegalStateException
   {
      // ensure database connection is available
      if ( !connectedToDatabase ) 
         throw new IllegalStateException( "Not Connected to Database" );

      // obtain a value at specified ResultSet row and column
      try 
      {
         resultSet.absolute( row + 1 );
         return resultSet.getObject( column + 1 );
      } // end try
      catch ( SQLException sqlException ) 
      {
         sqlException.printStackTrace();
      } // end catch
      
      return ""; // if problems, return empty string object
   } // end method getValueAt
   
   // set new database query string
   public void setQuery( String query ) 
      throws SQLException, IllegalStateException 
   {
      // ensure database connection is available
      if ( !connectedToDatabase ) 
         throw new IllegalStateException( "Not Connected to Database" );

      // specify query and execute it
      resultSet = statement.executeQuery( query );

      // obtain meta data for ResultSet
      metaData = resultSet.getMetaData();

      // determine number of rows in ResultSet
      resultSet.last();                   // move to last row
      numberOfRows = resultSet.getRow();  // get row number      
      
      // notify JTable that model has changed
      fireTableStructureChanged();
   } // end method setQuery

   // close Statement and Connection               
   public void disconnectFromDatabase()            
   {              
      if ( connectedToDatabase )                  
      {
         // close Statement and Connection            
         try                                          
         {                                            
            resultSet.close();                        
            statement.close();                        
            connection.close();                       
         } // end try                                 
         catch ( SQLException sqlException )          
         {                                            
            sqlException.printStackTrace();           
         } // end catch                               
         finally  // update database connection status
         {                                            
            connectedToDatabase = false;              
         } // end finally                             
      } // end if
   } // end method disconnectFromDatabase          
}  // end class ResultSetTableModel

[K. Tsang]

Hello

Your code looks right but it doesn't seem to work. When you select the patient record, does the history query get executed? Is the history query even correct? Do you see "changes" to the JTable?

You may want to output the return value from getValueAt() to check what you have.


After thinking about it, the table model approach may be a bit over the top (complicating things). So instead just set the JTable's selectionModel which extends DefaultListSelectionModel. In the overridden valueChanged() method, do the query stuff.

Sorry for the mis-direction.

[david foley]

Hi,

When i Run the Code, Nothing is Coming up on the JTable.. So i have yet to move onto the History JTable..

I'm kinda out of ideas as to what may be causing this problem i have messed around with the code and no joy.

[K. Tsang]

Hello David,

Did you able to get something going? I did a something testing and using a selection model will get you started.

For my testing I have
Patient class = patientId, firstName, lastName
PatientHistory class = historyId, patientId, txnDate, description

patientId for PatientHistory is clearly a foreign key in DB
I hard-coded the data into maps

 // class extends JFrame
        // set JMenuBar, default close operation, frame title 
        JPanel panel = new JPanel();
        panel.setLayout(new GridLayout(2,1)); // JTable on top and bottom
        
        PatientTableModel pmodel = new PatientTableModel(getPatientRecords()); // getPatientRecords() just get all patients records from DB
        JTable patientTable = new JTable(pmodel);
        JScrollPane patientPane = new JScrollPane(patientTable);
        
        HistoryTableModel hmodel = new HistoryTableModel(getHistoryRecords()); // getHistoryRecords() just get all history records from DB
        JTable historyTable = new JTable(hmodel);
        JScrollPane historyPane = new JScrollPane(historyTable);
        
        // important line
        patientTable.setSelectionModel(new PatientSelector(patientTable, getPatientRecords(), historyTable, getHistoryRecords())); 

        panel.add(patientPane); //top
        panel.add(historyPane); //bottom
        add(panel, BorderLayout.CENTER);

       // set visible, pack() etc

public class PatientSelector extends DefaultListSelectionModel {
    
    private JTable patientTable;
    private Map<Integer, Patient> patientMap;
    private JTable historyTable;
    private Map<Integer, PatientHistory> historyMap;
    
    public PatientSelector(JTable table1, Map<Integer, Patient> map1, 
            JTable table2, Map<Integer, PatientHistory> map2) {
        patientTable = table1;
        patientMap = map1;
        historyTable = table2;
        historyMap = map2;
        setSelectionMode(SINGLE_SELECTION);
        // important line
        addListSelectionListener(new ActionEnabler()); 
    }
    
    private class ActionEnabler implements ListSelectionListener {

        @Override
        public void valueChanged(ListSelectionEvent e) {
            // get a hold of the selected row for DB lookup
            Patient p = patientMap.get(patientTable.getSelectedRow()+1); // JTable lists are zero-based so +1 to match the map records
            System.out.println(p.getFirstName());

            // query the history DB table
            Map<Integer, PatientHistory> localMap = new HashMap<Integer, PatientHistory>();
            for (int i=0; i<historyMap.size(); i++) {
                PatientHistory ph = historyMap.get(i);
                    if (ph.getPatientId() == p.getPatientId()) {
                        localMap.put(ph.getHistoryId(), ph);
                    }
            }

            // update the history table
            historyTable.setModel(new HistoryTableModel(localMap));
            historyTable.repaint();
        }

    } 

}

Of course instead of all those maps, you mostly want to pass in the returned ResultSet to the table models rather than doing the query inside the table model like what you have done. This will also reduced the number of connections. Also in my code the "historyMap" passed in the PatientSelector class is the original full set of data. And inside the selector, I used a localMap to refresh the history table.

[david foley]

Thanks for your Reply.

I did get the Two Tables working.
with a ResultSetModel, I am trying to figure out how i get the ActionListener() Working with the Tables.

I have my Query for History
SELECT * FROM History WHERE patientid = ?

So i am Hoping that when the user clicks on a Record inside JTable that it should pick up what the patientid is and display the Query1 into the History JTable

[david foley]

Ok i have The Selection Working if i hard Code the Query

  static final String DEFAULT_QUERY1 = 
		   ("SELECT PatientId, Date, Illness FROM History WHERE PatientId = 3" );

But how can i get it to work with this ?

                  JScrollPane scrollPane = new JScrollPane();
                  splitPane.setLeftComponent(scrollPane);
                  
                  final JScrollPane scrollPane_1 = new JScrollPane();
                  splitPane.setRightComponent(scrollPane_1);
                  
                  // tableModel holds patient
                  final JTable resultTable = new JTable(tableModel);
                  resultTable.addMouseListener(new MouseAdapter() {
                  	@Override
                  	public void mouseClicked(MouseEvent e) {
                  		
                  		// JOptionPane.showMessageDialog( null, "hello" + resultTable.getSelectedRow());
                  		 JTable resultTable1 = new JTable(tableModel1);
                  		  scrollPane_1.setViewportView(resultTable1);
                  		  
                  
                  	}
                  });
                  scrollPane.setViewportView(resultTable);

if i hard code it doesn't make a difference which Row I select it going to give me the Same History i would like to get it working that once i select the Row it gives me the History table for that person.


I have tried

  static final String DEFAULT_QUERY1 = 
		   ("SELECT PatientId, Date, Illness FROM History WHERE PatientId = ?" );
("SELECT PatientId, Date, Illness FROM History WHERE PatientId <= ?;" );

No of them are working giving me a YOU have an error in your SQL Syntax check the Manual that corresponds to your MySQL Server Version for the right Syntax to use near '?') at line 1
and the console gives me
Exception in thread "main" java.lang.NullPointerException
at DisplayQueryResults.<init>(DisplayQueryResults.java:329)
at DisplayQueryResults.main(DisplayQueryResults.java:363)

[K. Tsang]

The ? in the query is part of the string in your case. The query should end at "... where patientid =".

In your code then you do

query + patientId

to make your query complete.

To get the patientId from the JTable, you first need to get the selected row, look up the patientId in the patient result set then query the history table.