/*
* TableTest.java
*
* Created on July 5, 2002, 2:45 PM
*/
package subjectData;
/**
*
* @author Joset A. Etzel
*/
import subjectData.*; import java.sql.*; import java.awt.event.*; import javax.swing.*; import javax.swing.table.*;
public class TableTest extends javax.swing.JInternalFrame {
Connection conData; Statement stmSQL; //variables for sql database access
/** Creates new form TableTest */
public TableTest() {
initComponents();
try {
Class.forName("org.gjt.mm.mysql.Driver");
conData = SubjectData.getDatabaseConnection();
} catch (Exception e) { e.printStackTrace(); }
FillLists(); //call sub to put the choices in the list boxes
ExcelAdapter myAd = new ExcelAdapter(tblResults); //call code to let the table copy and paste into Excel
}
private void FillLists() {
//this sub fills the list boxes with the appropriate choices from the database
String strList[]; int intSize = 0;
try { //fill up lstExperiment
stmSQL = conData.createStatement(); //stmSQL is global - have to get it ready
ResultSet rssSubjects = stmSQL.executeQuery("SELECT boxChoice FROM comboBoxes WHERE boxID = 'ExperimentType' ORDER BY boxChoice");
while(rssSubjects.next()) { intSize = intSize + 1; } //count the number of rows in the resultset
rssSubjects.beforeFirst(); //move to the start of the resultset again
strList = new String[intSize]; //get the array ready to hold the data
intSize = 0;
//get the data out of the database
while(rssSubjects.next()) { strList[intSize] = rssSubjects.getString("boxChoice"); //store the list item
intSize = intSize + 1; } //update count for next time
lstExperiment.setListData(strList); //done with the result set, so add the data to the list
} catch(java.sql.SQLException e) { e.printStackTrace(); }
try { //fill up lstType (subject type - normal, lesion, etc)
stmSQL = conData.createStatement(); //stmSQL is global - have to get it ready
ResultSet rssSubjects = stmSQL.executeQuery("SELECT boxChoice FROM comboBoxes WHERE boxID = 'SubjectType' ORDER BY boxChoice");
while(rssSubjects.next()) { intSize = intSize + 1; } //count the number of rows in the resultset
rssSubjects.beforeFirst(); //move to the start of the resultset again
strList = new String[intSize]; //get the array ready to hold the data
intSize = 0;
//get the data out of the database
while(rssSubjects.next()) { strList[intSize] = rssSubjects.getString("boxChoice"); //store the list item
intSize = intSize + 1; } //update count for next time
lstType.setListData(strList); //done with the result set, so add the data to the list
} catch(java.sql.SQLException e) { e.printStackTrace(); }
}
/** This method is called from within the constructor to
* initialize the form.
* WARNING: Do NOT modify this code. The content of this method is
* always regenerated by the Form Editor.
*/
private void initComponents() {//GEN-BEGIN:initComponents
java.awt.GridBagConstraints gridBagConstraints;
jScrollPane1 = new javax.swing.JScrollPane();
tblResults = new javax.swing.JTable();
cmdExecute = new javax.swing.JButton();
cmdClose = new javax.swing.JButton();
lblSubject = new javax.swing.JLabel();
lblType = new javax.swing.JLabel();
lblExperiment = new javax.swing.JLabel();
cmdReset = new javax.swing.JButton();
jScrollPane2 = new javax.swing.JScrollPane();
lstType = new javax.swing.JList();
jScrollPane3 = new javax.swing.JScrollPane();
lstExperiment = new javax.swing.JList();
getContentPane().setLayout(new java.awt.GridBagLayout());
setIconifiable(true);
setMaximizable(true);
setResizable(true);
jScrollPane1.setMinimumSize(new java.awt.Dimension(600, 200));
jScrollPane1.setPreferredSize(new java.awt.Dimension(600, 200));
tblResults.setModel(new javax.swing.table.DefaultTableModel(
new Object [][] {
},
new String [] {
"#", "subID", "name or PPG", "subject type", "experiment", "session date", "resp", "HRV"
}
) {
Class[] types = new Class [] {
java.lang.String.class, java.lang.String.class, java.lang.String.class, java.lang.String.class, java.lang.String.class, java.lang.String.class, java.lang.Boolean.class, java.lang.Boolean.class
};
boolean[] canEdit = new boolean [] {
false, false, false, false, false, false, false, false
};
public Class getColumnClass(int columnIndex) {
return types [columnIndex];
}
public boolean isCellEditable(int rowIndex, int columnIndex) {
return canEdit [columnIndex];
}
});
tblResults.setMinimumSize(new java.awt.Dimension(300, 64));
jScrollPane1.setViewportView(tblResults);
gridBagConstraints = new java.awt.GridBagConstraints();
gridBagConstraints.gridx = 0;
gridBagConstraints.gridy = 3;
gridBagConstraints.gridwidth = 4;
getContentPane().add(jScrollPane1, gridBagConstraints);
cmdExecute.setText("Search");
cmdExecute.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(java.awt.event.ActionEvent evt) {
cmdExecuteActionPerformed(evt);
}
});
gridBagConstraints = new java.awt.GridBagConstraints();
gridBagConstraints.gridx = 3;
gridBagConstraints.gridy = 2;
gridBagConstraints.anchor = java.awt.GridBagConstraints.EAST;
getContentPane().add(cmdExecute, gridBagConstraints);
cmdClose.setText("Close");
cmdClose.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(java.awt.event.ActionEvent evt) {
cmdCloseActionPerformed(evt);
}
});
gridBagConstraints = new java.awt.GridBagConstraints();
gridBagConstraints.gridx = 3;
gridBagConstraints.gridy = 4;
gridBagConstraints.insets = new java.awt.Insets(4, 0, 0, 0);
gridBagConstraints.anchor = java.awt.GridBagConstraints.EAST;
getContentPane().add(cmdClose, gridBagConstraints);
lblSubject.setText("Show subjects with these characteristics:");
gridBagConstraints = new java.awt.GridBagConstraints();
gridBagConstraints.gridx = 0;
gridBagConstraints.gridy = 0;
gridBagConstraints.gridwidth = 4;
gridBagConstraints.anchor = java.awt.GridBagConstraints.WEST;
gridBagConstraints.insets = new java.awt.Insets(2, 0, 6, 0);
getContentPane().add(lblSubject, gridBagConstraints);
lblType.setText("Subject Type");
gridBagConstraints = new java.awt.GridBagConstraints();
gridBagConstraints.gridx = 0;
gridBagConstraints.gridy = 1;
gridBagConstraints.insets = new java.awt.Insets(0, 0, 2, 0);
getContentPane().add(lblType, gridBagConstraints);
lblExperiment.setText("Experiment");
gridBagConstraints = new java.awt.GridBagConstraints();
gridBagConstraints.gridx = 1;
gridBagConstraints.gridy = 1;
gridBagConstraints.insets = new java.awt.Insets(0, 0, 2, 0);
getContentPane().add(lblExperiment, gridBagConstraints);
cmdReset.setText("Reset");
cmdReset.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(java.awt.event.ActionEvent evt) {
cmdResetActionPerformed(evt);
}
});
gridBagConstraints = new java.awt.GridBagConstraints();
gridBagConstraints.gridx = 2;
gridBagConstraints.gridy = 2;
gridBagConstraints.insets = new java.awt.Insets(0, 20, 0, 0);
getContentPane().add(cmdReset, gridBagConstraints);
jScrollPane2.setPreferredSize(new java.awt.Dimension(220, 100));
lstType.setMinimumSize(new java.awt.Dimension(100, 50));
lstType.setPreferredSize(new java.awt.Dimension(200, 80));
jScrollPane2.setViewportView(lstType);
gridBagConstraints = new java.awt.GridBagConstraints();
gridBagConstraints.gridx = 0;
gridBagConstraints.gridy = 2;
gridBagConstraints.insets = new java.awt.Insets(0, 0, 5, 0);
getContentPane().add(jScrollPane2, gridBagConstraints);
jScrollPane3.setPreferredSize(new java.awt.Dimension(220, 100));
lstExperiment.setMinimumSize(new java.awt.Dimension(100, 50));
lstExperiment.setPreferredSize(new java.awt.Dimension(200, 80));
jScrollPane3.setViewportView(lstExperiment);
gridBagConstraints = new java.awt.GridBagConstraints();
gridBagConstraints.gridx = 1;
gridBagConstraints.gridy = 2;
gridBagConstraints.insets = new java.awt.Insets(0, 10, 5, 0);
getContentPane().add(jScrollPane3, gridBagConstraints);
pack();
}//GEN-END:initComponents
private void cmdResetActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_cmdResetActionPerformed
//de-selects the choices in lstType & lstExperiment
lstType.clearSelection();
lstExperiment.clearSelection();
}//GEN-LAST:event_cmdResetActionPerformed
private void cmdExecuteActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_cmdExecuteActionPerformed
//do the search based on list box selection and show the results in the table
int intC = 0; int intExpRowCount = 0; Object[] strExpItems; String strSQL = "";
Object objVec[] = {"", "", "", "", new Boolean(false), new Boolean(false)};
Boolean BolTemp; int intSubID = 0; int intSesID = 0; String strSubType = "";
DefaultTableModel tblModel = (DefaultTableModel)tblResults.getModel(); //get the table model for the table so can alter it
if (tblModel.getRowCount() > 0) { tblModel.setRowCount(0); } //get rid of previous rows, in case
strExpItems = lstExperiment.getSelectedValues(); //load picked choices into object vector
intExpRowCount = strExpItems.length; //number of selected things in lstExperiment
if (intExpRowCount > 0) { //need to do a selection from session based on expType
strSQL = "SELECT subID, sessionID, expDate, expType FROM sessionData WHERE expType = '";
intC = 0;
while (intC < intExpRowCount) { strSQL = strSQL + (String)strExpItems[intC] + "'";
intC++; //increment counter so can do test below
if (intC < intExpRowCount) { strSQL = strSQL + " OR expType = '"; }
}
}
else { strSQL = "SELECT subID, sessionID, expDate, expType FROM sessionData"; } //pick all expTypes
try{
stmSQL = conData.createStatement(); //retrieve subID, sessionID, expDate for chosen expType(s)
ResultSet rssSubjects = stmSQL.executeQuery(strSQL);
rssSubjects.beforeFirst();
intC = -1; //set so counter comes out ok
while (rssSubjects.next() == true) {
intSubID = rssSubjects.getInt("subID");
intSesID = rssSubjects.getInt("sessionID");
strSubType = GetSubjectType(intSubID);
if( SubjectTypeMatches(strSubType) == true ) { //correct type so show in table
intC++; tblModel.addRow(objVec); //add blank row to table
tblModel.setValueAt("" + (intC + 1), intC, 0); //counter in column 0
tblModel.setValueAt("" + intSubID, intC, 1); //column 1 is subject ID
tblModel.setValueAt(GetNamePPG(intSubID), intC, 2); //column 2 is subject name or PPG#
tblModel.setValueAt(strSubType, intC, 3); //column 3 is subject type
tblModel.setValueAt(rssSubjects.getString("expType"), intC, 4); //column 4 is experiment type
tblModel.setValueAt("" + rssSubjects.getDate("expDate"), intC, 5); //column 5 is experiment date
BolTemp = new Boolean(HasData(intSubID, intSesID, "respMeasures"));
tblModel.setValueAt(BolTemp, intC, 6); //column 6 is true/false for respiration data present
BolTemp = new Boolean(HasData(intSubID, intSesID, "heartMeasures"));
tblModel.setValueAt(BolTemp, intC, 7); //column 7 is true/false for heart data present
}
}
if( intC == -1 ) { //no records in recordset, show user something
tblModel.addRow(objVec);
tblModel.setValueAt("no subjects", 0, 0); //show subject counter
}
} catch(java.sql.SQLException e) { e.printStackTrace(); }
}//GEN-LAST:event_cmdExecuteActionPerformed
private boolean HasData(int intSubjectID, int intSessionID, String strTable) {
//returns true if a listing for the subject and session in the table name passed, otherwise false
String strTemp = ""; boolean bolDataPresent = true;
strTemp = "SELECT * FROM " + strTable + " WHERE subID = \"" + intSubjectID + "\" AND sessionID = \"" + intSessionID + "\"";
try {
ResultSet rssSubjects = stmSQL.executeQuery(strTemp); //check for existing data in that table of the database
if ( rssSubjects.next() == false ) { bolDataPresent = false; } //no data in table, so can return false
else { bolDataPresent = true; } //data in table, so return true
}catch(java.sql.SQLException e) { e.printStackTrace(); }
return bolDataPresent;
}
private String GetNamePPG(int intSubjectID) {
//returns the name or PPG# of the subject whose subjectID was passed
int intC = 0; String strName = "";
try{
stmSQL = conData.createStatement();
ResultSet rssSubjects = stmSQL.executeQuery("SELECT firstName, middleInitial, lastName, PPG FROM subject WHERE subID='" + intSubjectID + "'");
intC = -1; //set so counter comes out ok
while(rssSubjects.next()) {
intC++;
strName = rssSubjects.getString("PPG");
if( strName.equals("") == true ) { strName = rssSubjects.getString("lastName") + ", " + rssSubjects.getString("firstName") + " " + rssSubjects.getString("middleInitial") + "."; }
}
} catch(java.sql.SQLException e) { e.printStackTrace(); }
return strName;
}
private String GetSubjectType(int intSubjectID) {
//returns the subjectType of the subject whose subjectID was passed
int intC = 0; String strType = "";
try{
stmSQL = conData.createStatement();
ResultSet rssSubjects = stmSQL.executeQuery("SELECT subjectType FROM subject WHERE subID='" + intSubjectID + "'");
intC = -1; //set so counter comes out ok
while(rssSubjects.next()) { intC++; strType = rssSubjects.getString("subjectType"); }
} catch(java.sql.SQLException e) { e.printStackTrace(); }
return strType;
}
private boolean SubjectTypeMatches(String strSubType) {
//returns true if strSubType matches one of the picked subject types in lstType, false otherwise
//if no subject types picked in lstType then returns true
Object[] objTypeItems; int intTypeRowCount = 0; int intC = 0; boolean bolMatches = false;
objTypeItems = lstType.getSelectedValues(); //load picked choices into object vector
intTypeRowCount = objTypeItems.length; //number of selected things in lstType
if( intTypeRowCount == 0 ) { bolMatches = true; } //nothing picked so all types ok
else {
while( intC < intTypeRowCount ) {
if( objTypeItems[intC].equals(strSubType) == true ) { bolMatches = true; }
intC++;
}
}
return bolMatches; //default false, so returns false unless changed in ifs
}
private void cmdCloseActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_cmdCloseActionPerformed
try { this.setClosed(true); }
catch( java.beans.PropertyVetoException e ) { e.printStackTrace(); }
}//GEN-LAST:event_cmdCloseActionPerformed
// Variables declaration - do not modify//GEN-BEGIN:variables
private javax.swing.JButton cmdClose;
private javax.swing.JButton cmdExecute;
private javax.swing.JButton cmdReset;
private javax.swing.JScrollPane jScrollPane1;
private javax.swing.JScrollPane jScrollPane2;
private javax.swing.JScrollPane jScrollPane3;
private javax.swing.JLabel lblExperiment;
private javax.swing.JLabel lblSubject;
private javax.swing.JLabel lblType;
private javax.swing.JList lstExperiment;
private javax.swing.JList lstType;
private javax.swing.JTable tblResults;
// End of variables declaration//GEN-END:variables
}