JDBC source code

public class JDBC extends java.applet.Applet implements ActionListener
{
//components ***************************************************************************
//Button names
Button ConnectBtn; //Button to connect to Database
Button QueryBtn; //Button to send general query statement to Database
Button AddRecButton; //Button to add a record
Button FindRecButton; //Button to find a record
Button DelRecButton; //Button to delete a record
Button ListAllButton; //Button to list all records
Button ReportButton; //Button to print a report
Button ClearNPButton; //Button will clear New Patient fields
Button ClearPAButton; //Button will clear Patient Account fields
Button UpdateRecordButton; //Button will update Patient services

//TextField names
TextField QueryField;
TextField PAEnterPatID; //enter patient's last name for Patient Account
TextField ItemCodeField; //for add new record
TextField PatientIDnumField; //for add new record
TextField FirstNameField; //for add new patient
TextField LastNameField; //for add new patient
TextField AddressField; //for add new patient
TextField CityField; //for add new patient
TextField StateField; //for add new patient
TextField ZipField; //for add new patient
TextField DateAdmitField; //for add new patient
TextField DateDischField; //for add new patient
TextField BottomEntePatID; //for queries

//TextArea names**********************************************************************
TextArea MainOutputField; //main output field
TextArea MedicalServicesField; //to list all medical services

//Database CONNECTIVITY ***************************************************************
String url = "jdbc:msql://dot.aloha.net:1114/test";
String Name = "";
String Passwd = "";
Connection con;
Object nclass;
String holdString =""; //string to hold user input
String holdString2 =""; //string to hold user input
String holdQuery =""; //string to hold a query string
String finalString =""; //string to hold reformated output
int counterChar =0; //a counter
int counterCol =0; //a counter
final int maxCol = 20; //max withdth of any column of data
int spacer =0; //to space out columns in the database
int holdData = 0;// holds a number
char stringBit =' ';// holds a character

//Button Commands *********************************************************************
final static String CONNECT = "CONNECT"; //for ConnectBtn
final static String QUERY = "QUERY"; //for QueryBtn
final static String ADDREC = "ADDREC"; //for AddRecButton;
final static String FINDREC = "FINDREC"; //for FindRecButton;
final static String DELREC = "DELREC"; //for DelRecButton;
final static String LISTALL = "LISTALL"; //for ListAllButton;
final static String REPORT = "REPORT"; //for ReportButton;
final static String CLEARNP = "CLEARNP"; //for ClearNPButton;
final static String CLEARPA = "CLEARPA"; //for ClearPAButton;
final static String UPDATE = "UPDATE"; //for UpdateRecordButton;

//Labels *******************************************************************************
Label connectLabel = new Label("Press to connect to Database");
Label generalqueryLabel = new Label("General Query");
Label patnumLabel = new Label("Patient ID#");
Label firstnameLabel = new Label("First Name");
Label lastnameLabel = new Label("Last Name");
Label addressLabel = new Label("Address");
Label cityLabel = new Label("City");
Label stateLabel = new Label("State");
Label zipLabel = new Label("Zip");
Label dateadmLabel = new Label("Date Admitted");
Label datedischLabel = new Label("Date Discharged");
Label enterlastnameLabel = new Label("Enter Patient ID#");
Label enterlastname2Label = new Label("Enter Patient ID#");
Label itemcodeLabel = new Label("Item Code");
Label descriptLabel = new Label("Description");
Label chargeLabel = new Label("Charge");
Label spaceLabel = new Label("");
Label newpatientLabel = new Label(" ");
Label patientaccountLabel = new Label("Patient Account");

//init *******************************************************************************************
public void init()
{
//gridbag init **********************************************************************
GridBagLayout myLayout = new GridBagLayout();
GridBagConstraints myConstraints = new GridBagConstraints();
setLayout(myLayout);
myConstraints.fill = GridBagConstraints.BOTH; //components sizable in both directions
myConstraints.insets = new Insets(5,5,5,5); // 5 pixel buffer zone

setFont(new Font("Times", Font.PLAIN, 12));

setBackground(Color.black);
setForeground(Color.white);

// buttons **************************************************************************
ConnectBtn = new Button();
ConnectBtn.setLabel ("Connect to Database");
ConnectBtn.setActionCommand(CONNECT);
ConnectBtn.addActionListener(this); // listens for button click

QueryBtn = new Button();
QueryBtn.setLabel ("General Query");
QueryBtn.setActionCommand(QUERY);
QueryBtn.addActionListener(this); // listens for button click

AddRecButton = new Button();
AddRecButton.setLabel ("Add Record");
AddRecButton.setActionCommand(ADDREC);
AddRecButton.addActionListener(this); // listens for button click

FindRecButton = new Button();
FindRecButton.setLabel ("Find Record");
FindRecButton.setActionCommand(FINDREC);
FindRecButton.addActionListener(this); // listens for button click

DelRecButton = new Button();
DelRecButton.setLabel ("Delete Record");
DelRecButton.setActionCommand(DELREC);
DelRecButton.addActionListener(this); // listens for button click

ListAllButton = new Button();
ListAllButton.setLabel ("List All Patients");
ListAllButton.setActionCommand(LISTALL);
ListAllButton.addActionListener(this); // listens for button click

ReportButton = new Button();
ReportButton.setLabel ("Genetrate Report");
ReportButton.setActionCommand(REPORT);
ReportButton.addActionListener(this); // listens for button click

ClearNPButton = new Button();
ClearNPButton.setLabel ("Clear Fields");
ClearNPButton.setActionCommand(CLEARNP);
ClearNPButton.addActionListener(this); // listens for button click

ClearPAButton = new Button();
ClearPAButton.setLabel ("Clear Fields");
ClearPAButton.setActionCommand(CLEARPA);
ClearPAButton.addActionListener(this); // listens for button click

UpdateRecordButton = new Button();
UpdateRecordButton.setLabel ("Update Record");
UpdateRecordButton.setActionCommand(UPDATE);
UpdateRecordButton.addActionListener(this); // listens for button click

// TextFields **********************************************************************
QueryField = new TextField(20);
PAEnterPatID = new TextField(10);
ItemCodeField= new TextField(10);
PatientIDnumField = new TextField(6);
FirstNameField = new TextField(20);
LastNameField = new TextField(20);
AddressField = new TextField(30);
CityField = new TextField(12);
StateField = new TextField(4);
ZipField = new TextField(7);
DateAdmitField = new TextField(12);
DateDischField = new TextField(12);
BottomEntePatID = new TextField(10);

// TextArea ************************************************************************
MainOutputField = new TextArea(10,50); // the area to enter
MedicalServicesField = new TextArea(8,30); // the area to enter

// layout **************************************************************************
myConstraints.gridx = 1; myConstraints.gridy = 1;
myConstraints.gridwidth = 2; myConstraints.gridheight = 1;
myConstraints.weightx = 0.0; myConstraints.weighty = 0.0;
myLayout.setConstraints(connectLabel, myConstraints);
add(connectLabel);

myConstraints.gridx = 3; myConstraints.gridy = 1;
myConstraints.gridwidth = 5; myConstraints.gridheight = 1;
myConstraints.weightx = 0.0; myConstraints.weighty = 0.0;
myLayout.setConstraints(ConnectBtn, myConstraints);
add(ConnectBtn);

myConstraints.gridx = 1; myConstraints.gridy = 15;
myConstraints.gridwidth = 1; myConstraints.gridheight = 1;
myConstraints.weightx = 0.0; myConstraints.weighty = 0.0;
myLayout.setConstraints(generalqueryLabel, myConstraints);
add(generalqueryLabel);

myConstraints.gridx = 2; myConstraints.gridy = 15;
myConstraints.gridwidth = 2; myConstraints.gridheight = 1;
myConstraints.weightx = 0.0; myConstraints.weighty = 0.0;
myLayout.setConstraints(QueryField, myConstraints);
add(QueryField);

myConstraints.gridx = 4; myConstraints.gridy = 15;
myConstraints.gridwidth = 1; myConstraints.gridheight = 1;
myConstraints.weightx = 0.0; myConstraints.weighty = 0.0;
myLayout.setConstraints(QueryBtn, myConstraints);
add(QueryBtn);

myConstraints.gridx = 1; myConstraints.gridy = 3;
myConstraints.gridwidth = 1; myConstraints.gridheight = 1;
myConstraints.weightx = 0.0; myConstraints.weighty = 0.0;
myLayout.setConstraints(newpatientLabel, myConstraints);
add(newpatientLabel);

myConstraints.gridx = 1; myConstraints.gridy = 4;
myConstraints.gridwidth = 1; myConstraints.gridheight = 1;
myConstraints.weightx = 0.0; myConstraints.weighty = 0.0;
myLayout.setConstraints(patnumLabel, myConstraints);
add(patnumLabel);

myConstraints.gridx = 2; myConstraints.gridy = 4;
myConstraints.gridwidth = 1; myConstraints.gridheight = 1;
myConstraints.weightx = 0.0; myConstraints.weighty = 0.0;
myLayout.setConstraints(PatientIDnumField, myConstraints);
add(PatientIDnumField);

myConstraints.gridx = 5; myConstraints.gridy = 4;
myConstraints.gridwidth = 1; myConstraints.gridheight = 1;
myConstraints.weightx = 0.0; myConstraints.weighty = 0.0;
myLayout.setConstraints(enterlastnameLabel, myConstraints);
add(enterlastnameLabel);

myConstraints.gridx = 1; myConstraints.gridy = 5;
myConstraints.gridwidth = 1; myConstraints.gridheight = 1;
myConstraints.weightx = 0.0; myConstraints.weighty = 0.0;
myLayout.setConstraints(firstnameLabel, myConstraints);
add(firstnameLabel);

myConstraints.gridx = 2; myConstraints.gridy = 5;
myConstraints.gridwidth = 2; myConstraints.gridheight = 1;
myConstraints.weightx = 0.0; myConstraints.weighty = 0.0;
myLayout.setConstraints(FirstNameField, myConstraints);
add(FirstNameField);

myConstraints.gridx = 5; myConstraints.gridy = 5;
myConstraints.gridwidth = 1; myConstraints.gridheight = 1;
myConstraints.weightx = 0.0; myConstraints.weighty = 0.0;
myLayout.setConstraints(itemcodeLabel, myConstraints);
add(itemcodeLabel);

myConstraints.gridx = 1; myConstraints.gridy = 6;
myConstraints.gridwidth = 1; myConstraints.gridheight = 1;
myConstraints.weightx = 0.0; myConstraints.weighty = 0.0;
myLayout.setConstraints(lastnameLabel, myConstraints);
add(lastnameLabel);

myConstraints.gridx = 2; myConstraints.gridy = 6;
myConstraints.gridwidth = 2; myConstraints.gridheight = 1;
myConstraints.weightx = 0.0; myConstraints.weighty = 0.0;
myLayout.setConstraints(LastNameField, myConstraints);
add(LastNameField);

myConstraints.gridx = 1; myConstraints.gridy = 7;
myConstraints.gridwidth = 1; myConstraints.gridheight = 1;
myConstraints.weightx = 0.0; myConstraints.weighty = 0.0;
myLayout.setConstraints(addressLabel, myConstraints);
add(addressLabel);

myConstraints.gridx = 2; myConstraints.gridy = 7;
myConstraints.gridwidth = 3; myConstraints.gridheight = 1;
myConstraints.weightx = 0.0; myConstraints.weighty = 0.0;
myLayout.setConstraints(AddressField, myConstraints);
add(AddressField);

myConstraints.gridx = 1; myConstraints.gridy = 8;
myConstraints.gridwidth = 1; myConstraints.gridheight = 1;
myConstraints.weightx = 0.0; myConstraints.weighty = 0.0;
myLayout.setConstraints(cityLabel, myConstraints);
add(cityLabel);

myConstraints.gridx = 2; myConstraints.gridy = 8;
myConstraints.gridwidth = 2; myConstraints.gridheight = 1;
myConstraints.weightx = 0.0; myConstraints.weighty = 0.0;
myLayout.setConstraints(CityField, myConstraints);
add(CityField);

myConstraints.gridx = 1; myConstraints.gridy = 9;
myConstraints.gridwidth = 1; myConstraints.gridheight = 1;
myConstraints.weightx = 0.0; myConstraints.weighty = 0.0;
myLayout.setConstraints(stateLabel, myConstraints);
add(stateLabel);

myConstraints.gridx = 2; myConstraints.gridy = 9;
myConstraints.gridwidth = 1; myConstraints.gridheight = 1;
myConstraints.weightx = 0.0; myConstraints.weighty = 0.0;
myLayout.setConstraints(StateField, myConstraints);
add(StateField);

myConstraints.gridx = 3; myConstraints.gridy = 9;
myConstraints.gridwidth = 1; myConstraints.gridheight = 1;
myConstraints.weightx = 0.0; myConstraints.weighty = 0.0;
myLayout.setConstraints(zipLabel, myConstraints);
add(zipLabel);

myConstraints.gridx = 4; myConstraints.gridy = 9;
myConstraints.gridwidth = 1; myConstraints.gridheight = 1;
myConstraints.weightx = 0.0; myConstraints.weighty = 0.0;
myLayout.setConstraints(ZipField, myConstraints);
add(ZipField);

myConstraints.gridx = 1; myConstraints.gridy = 10;
myConstraints.gridwidth = 1; myConstraints.gridheight = 1;
myConstraints.weightx = 0.0; myConstraints.weighty = 0.0;
myLayout.setConstraints(dateadmLabel, myConstraints);
add(dateadmLabel);

myConstraints.gridx = 2; myConstraints.gridy = 10;
myConstraints.gridwidth = 2; myConstraints.gridheight = 1;
myConstraints.weightx = 0.0; myConstraints.weighty = 0.0;
myLayout.setConstraints(DateAdmitField, myConstraints);
add(DateAdmitField);

myConstraints.gridx = 1; myConstraints.gridy = 11;
myConstraints.gridwidth = 1; myConstraints.gridheight = 1;
myConstraints.weightx = 0.0; myConstraints.weighty = 0.0;
myLayout.setConstraints(datedischLabel, myConstraints);
add(datedischLabel);

myConstraints.gridx = 2; myConstraints.gridy = 11;
myConstraints.gridwidth = 2; myConstraints.gridheight = 1;
myConstraints.weightx = 0.0; myConstraints.weighty = 0.0;
myLayout.setConstraints(DateDischField, myConstraints);
add(DateDischField);

myConstraints.gridx = 2; myConstraints.gridy = 12;
myConstraints.gridwidth = 1; myConstraints.gridheight = 1;
myConstraints.weightx = 0.0; myConstraints.weighty = 0.0;
myLayout.setConstraints(AddRecButton, myConstraints);
add(AddRecButton);

myConstraints.gridx = 4; myConstraints.gridy = 12;
myConstraints.gridwidth = 1; myConstraints.gridheight = 1;
myConstraints.weightx = 0.0; myConstraints.weighty = 0.0;
myLayout.setConstraints(ClearNPButton, myConstraints);
add(ClearNPButton);

// below is right side fiels (Patient Account)

myConstraints.gridx = 6; myConstraints.gridy = 4;
myConstraints.gridwidth = 1; myConstraints.gridheight = 1;
myConstraints.weightx = 0.0; myConstraints.weighty = 0.0;
myLayout.setConstraints(PAEnterPatID, myConstraints);
add(PAEnterPatID);

myConstraints.gridx = 6; myConstraints.gridy = 5;
myConstraints.gridwidth = 1; myConstraints.gridheight = 1;
myConstraints.weightx = 0.0; myConstraints.weighty = 0.0;
myLayout.setConstraints(ItemCodeField, myConstraints);
add(ItemCodeField);

myConstraints.gridx = 5; myConstraints.gridy = 6;
myConstraints.gridwidth = 1; myConstraints.gridheight = 1;
myConstraints.weightx = 0.0; myConstraints.weighty = 0.0;
myLayout.setConstraints(UpdateRecordButton, myConstraints);
add(UpdateRecordButton);

myConstraints.gridx = 7; myConstraints.gridy = 6;
myConstraints.gridwidth = 1; myConstraints.gridheight = 1;
myConstraints.weightx = 0.0; myConstraints.weighty = 0.0;
myLayout.setConstraints(ClearPAButton, myConstraints);
add(ClearPAButton);

myConstraints.gridx = 1; myConstraints.gridy = 14;
myConstraints.gridwidth = 1; myConstraints.gridheight = 1;
myConstraints.weightx = 0.0; myConstraints.weighty = 0.0;
myLayout.setConstraints(enterlastname2Label, myConstraints);
add(enterlastname2Label);

myConstraints.gridx = 2; myConstraints.gridy = 14;
myConstraints.gridwidth = 1; myConstraints.gridheight = 1;
myConstraints.weightx = 0.0; myConstraints.weighty = 0.0;
myLayout.setConstraints(BottomEntePatID, myConstraints);
add(BottomEntePatID);

myConstraints.gridx = 3; myConstraints.gridy = 14;
myConstraints.gridwidth = 2; myConstraints.gridheight = 1;
myConstraints.weightx = 0.0; myConstraints.weighty = 0.0;
myLayout.setConstraints(FindRecButton, myConstraints);
add(FindRecButton);

myConstraints.gridx = 5; myConstraints.gridy = 14;
myConstraints.gridwidth = 2; myConstraints.gridheight = 1;
myConstraints.weightx = 0.0; myConstraints.weighty = 0.0;
myLayout.setConstraints(ReportButton, myConstraints);
add(ReportButton);

myConstraints.gridx = 7; myConstraints.gridy = 14;
myConstraints.gridwidth = 1; myConstraints.gridheight = 1;
myConstraints.weightx = 0.0; myConstraints.weighty = 0.0;
myLayout.setConstraints(DelRecButton, myConstraints);
add(DelRecButton);

myConstraints.gridx = 5; myConstraints.gridy = 15;
myConstraints.gridwidth = 3; myConstraints.gridheight = 1;
myConstraints.weightx = 0.0; myConstraints.weighty = 0.0;
myLayout.setConstraints(ListAllButton, myConstraints);
add(ListAllButton);

myConstraints.gridx = 1; myConstraints.gridy = 16;
myConstraints.gridwidth = 7; myConstraints.gridheight = 1;
myConstraints.weightx = 0.0; myConstraints.weighty = 0.0;
myLayout.setConstraints(MainOutputField, myConstraints);
add(MainOutputField);

myConstraints.gridx = 1; myConstraints.gridy = 13;
myConstraints.gridwidth = 7; myConstraints.gridheight = 1;
myConstraints.weightx = 0.0; myConstraints.weighty = 0.0;
myLayout.setConstraints(spaceLabel, myConstraints);
add(spaceLabel);

myConstraints.gridx = 5; myConstraints.gridy = 7;
myConstraints.gridwidth = 3; myConstraints.gridheight = 6;
myConstraints.weightx = 0.0; myConstraints.weighty = 0.0;
myLayout.setConstraints(MedicalServicesField, myConstraints);
add(MedicalServicesField);

//end gridbag layout ***************************************************************

//set field areas to editable (true/false) *****************************************
//true
QueryField.setEditable(true);
PAEnterPatID.setEditable(true);
PatientIDnumField.setEditable(true);
FirstNameField.setEditable(true);
LastNameField.setEditable(true);
AddressField.setEditable(true);
CityField.setEditable(true);
StateField.setEditable(true);
ZipField.setEditable(true);
DateAdmitField.setEditable(true);
DateDischField.setEditable(true);
//false
MainOutputField.setEditable(false);
MedicalServicesField.setEditable(false);

}// end init method

//******************************************************************************************
public void paint (Graphics g)
{
g.setColor(Color.white);
g.drawRect(6, 25, 786, 40); //top box
g.setFont(new Font("Arial", Font.PLAIN, 20));
g.drawString("New Patient", 160, 92);
g.drawString("Patient Account", 540, 92);
g.drawRect(6, 96, 427, 300); //left box
g.drawRect(436, 96, 356, 300); //right box
g.drawRect(6, 420, 786, 250); //bottom box
}//end paint method

//actionPerformed*****************************************************************************************
public void actionPerformed(ActionEvent aClick) // check if button is clicked and update
{
String command = aClick.getActionCommand();
//Connect to Database function************************************************************************************
if (command == CONNECT)
{
holdString="";
holdString2="";
holdQuery="";
finalString ="";
MainOutputField.setText("Connecting to Database");
try
{
//Create instance of the driver we want to use
Class.forName("com.imaginary.sql.msql.MsqlDriver"); //location of msql driver
con = DriverManager.getConnection("jdbc:msql://dot.aloha.net:1114/test", Name, Passwd);
ConnectBtn.setLabel("Reconnect to Database");
MainOutputField.setText("Connection to Database ESTABLISHED\n");

}//end try
catch( Exception e )
{
e.printStackTrace();
MainOutputField.setText(e.getMessage());
}//end catch

MedicalServicesField.setFont(new Font("Courier", Font.PLAIN, 12));
MedicalServicesField.setText(" Hospital Services \n\n");
MedicalServicesField.append("Code Decription Charge\n");
MedicalServicesField.append("---- ---------- ------");
holdString = (Select ("Select * from dsh_services ORDER BY dsh_services.item_code"));
int stringLenght = holdString.length(); //get the lenght of the query output
counterChar = 0;// reset counter
counterCol = 0;// reset counter
for (int i = 0; i < stringLenght; i++)
{
stringBit = holdString.charAt(i); //get each character in the string one at a time
if (stringBit == 42)// if it is the * character
{
counterCol +=1; //increment column counter
switch (counterCol)
{
case 1:
finalString += (" ");
break;
case 2:
spacer = (maxCol - counterChar +3);
for (int x = 1; x < spacer; x++)
{
finalString += (" ");//line up columns
}//end for
break;
case 3:
counterCol = 0;// reset counter
break;
}//end switch
counterChar = 0;// reset counter
}//end if

else if (stringBit == 10)
{
MedicalServicesField.append("\n");//add carriage return at end of line
MedicalServicesField.append(finalString);//print the line when CR is encountered
finalString = ""; // clear contents of finalString after printing the line
}//end else if
else
{
finalString += stringBit;// add char to final output string
counterChar +=1; //increment word length counter
}//end else
}//end for
}// end if command = CONNECT

//Query field******************************************************************************************
else if (command == QUERY)
{
System.out.println(QueryField.getText());
MainOutputField.setText ("Sending Query\n");
MainOutputField.setText(Select(QueryField.getText()));
}//end if command = QUERY

//add a record to the database************************************************************************************
else if (command == ADDREC)
{
System.out.println(PatientIDnumField.getText());
System.out.println(FirstNameField.getText());
System.out.println(LastNameField.getText());
System.out.println(AddressField.getText());
System.out.println(CityField.getText());
System.out.println(StateField.getText());
System.out.println(ZipField.getText());
System.out.println(DateAdmitField.getText());
System.out.println(DateDischField.getText());
MainOutputField.setText("Adding record\n");
MainOutputField.setText(Select("INSERT into dsh_patient values ("
+ PatientIDnumField.getText() + ",\'"
+ FirstNameField.getText() + "\',\'"
+ LastNameField.getText() + "\',\'"
+ AddressField.getText() + "\',\'"
+ CityField.getText() + "\',\'"
+ StateField.getText() + "\',"
+ ZipField.getText() + ",\'"
+ DateAdmitField.getText() + "\',\'"
+ DateDischField.getText() + "\')"));
MainOutputField.setText("Database record added \n");
}//end if command = ADDREC

//for ClearNPButton******************************************************************************************
else if (command == CLEARNP)
{
PatientIDnumField.setText("");
FirstNameField.setText("");
LastNameField.setText("");
AddressField.setText("");
CityField.setText("");
StateField.setText("");
ZipField.setText("");
DateAdmitField.setText("");
DateDischField.setText("");
}//end if command = CLEARNP

// ClearPAButton ******************************************************************************************
else if (command == CLEARPA)
{
PAEnterPatID.setText("");
ItemCodeField.setText("");
finalString = "";
MedicalServicesField.setFont(new Font("Courier", Font.PLAIN, 12));
MedicalServicesField.setText(" Hospital Services \n\n");
MedicalServicesField.append("Code Decription Charge\n");
MedicalServicesField.append("---- ---------- ------");
holdString = (Select ("Select * from dsh_services ORDER BY dsh_services.item_code"));
int stringLenght = holdString.length(); //get the lenght of the query output
counterChar = 0;// reset counter
counterCol = 0;// reset counter
for (int i = 0; i < stringLenght; i++)
{
stringBit = holdString.charAt(i); //get each character in the string one at a time
if (stringBit == 42)// if it is the * character
{
counterCol +=1; //increment column counter
switch (counterCol)
{
case 1:
finalString += (" ");
break;
case 2:
spacer = (maxCol - counterChar +3);
for (int x = 1; x < spacer; x++)
{
finalString += (" ");//line up columns
}//end for
break;
case 3:
counterCol = 0;// reset counter
break;
}//end switch
counterChar = 0;// reset counter
}//end if

}//end if command = CLEARPA

//find a record in the database***********************************************************************************
else if (command == FINDREC)
{
holdString="";
holdString2="";
holdQuery="";
finalString ="";
System.out.println(BottomEntePatID.getText());
holdString = BottomEntePatID.getText();
MainOutputField.setText("Finding record... Patient ID#= " +holdString);
holdQuery = (Select("SELECT DISTINCT dsh_patient.patient_id, "
+ " dsh_patient.first_name, "
+ " dsh_patient.last_name, "
+ " dsh_patient.address, "
+ " dsh_patient.city, "
+ " dsh_patient.state, "
+ " dsh_patient.zip, "
+ " dsh_patient.date_in, "
+ " dsh_patient.date_out "
+ " FROM dsh_patient "
+ " WHERE dsh_patient.patient_id = "+ holdString +""
));

MainOutputField.setFont(new Font("Courier", Font.PLAIN, 12));
MainOutputField.setText("ID # First Name Last Name Street Address City State Zip Date Admitted Date Discharged\n");
MainOutputField.append("---- ---------- --------- -------------- ------ ----- ----- ------------- ---------------\n");
int stringLenght = holdQuery.length(); //get the lenght of the query output
counterChar = 0;// reset counter
counterCol = 0;// reset counter
for (int i = 0; i < stringLenght; i++)
{
stringBit = holdQuery.charAt(i); //get each character in the string one at a time
if (stringBit == 42)// if it is the * character
{
counterCol +=1; //increment column counter
switch (counterCol)
{
case 1:
finalString += (" ");
break;
case 2:
case 3:
case 4:
case 5:
spacer = (maxCol - counterChar);
for (int x = 1; x < spacer; x++)
{
finalString += (" ");//line up columns
}//end for
break;
case 6:
case 7:
case 8:
finalString += (" ");
break;
case 9:
counterCol = 0;// reset counter
break;
}//end switch
counterChar = 0;// reset counter
}//end if
else if (stringBit == 10)
{
MainOutputField.append("\n");//add carriage return at end of line
MainOutputField.append(finalString);//print the line when CR is encountered
finalString = ""; // clear contents of finalString after printing the line
}//end else if
else
{
finalString += stringBit;// add char to final output string
counterChar +=1; //increment word length counter
}//end else if
}//end for loop
}//end if command = FINDREC

//delete a record from the database*******************************************************************************
else if (command == DELREC)
{
System.out.println(BottomEntePatID.getText());
holdString = BottomEntePatID.getText();
MainOutputField.setText("Deleting record\n");
MainOutputField.setText(Select("DELETE FROM dsh_patient WHERE patient_id = "+ holdString +""));;
}//end if command = DELREC

//list all records from the database******************************************************************************
else if (command == LISTALL)
{
finalString = "";
MainOutputField.setFont(new Font("Courier", Font.PLAIN, 12));
MainOutputField.setText("ID # First Name Last Name Street Address City State Zip Date Admitted Date Discharged\n");
MainOutputField.append("---- ---------- --------- -------------- ------ ----- ----- ------------- ---------------");
holdString = (Select("Select * from dsh_patient ORDER BY last_name"));

int stringLenght = holdString.length(); //get the lenght of the query output
counterChar = 0;// reset counter
counterCol = 0;// reset counter
for (int i = 0; i < stringLenght; i++)
{
stringBit = holdString.charAt(i); //get each character in the string one at a time
if (stringBit == 42)// if it is the * character
{
counterCol +=1; //increment column counter
switch (counterCol)
{
case 1:
finalString += (" ");
break;
case 2:
case 3:
case 4:
case 5:
spacer = (maxCol - counterChar);
for (int x = 1; x < spacer; x++)
{
finalString += (" ");//line up columns
}
break;
case 6:
case 7:
case 8:
finalString += (" ");
break;
case 9:
counterCol = 0;// reset counter
break;
}//end switch
counterChar = 0;// reset counter
}//end if
else if (stringBit == 10)
{
MainOutputField.append("\n");//add carriage return at end of line
MainOutputField.append(finalString);//print the line when CR is encountered
finalString = ""; // clear contents of finalString after printing the line
}//end else if
else
{
finalString += stringBit;// add char to final output string
counterChar +=1; //increment word length counter
}//end else

}//end for loop

}//end if command = LISTALL

//print report from the database***********************************************************************************
else if (command == REPORT)
{
holdString="";
holdString2="";
holdQuery="";
finalString ="";
System.out.println(BottomEntePatID.getText());
holdString = BottomEntePatID.getText();
MainOutputField.setText("Preparing Report\n");
MainOutputField.setText(" St. Samuel Memorial Hospital\n");
MainOutputField.append (" 200 Lake Forest Dr.\n");
MainOutputField.append (" Anywhere, USA\n\n");
MainOutputField.append ("Statement of Account\n\n");
holdQuery = (Select("SELECT DISTINCT dsh_patient.patient_id, "
+ " dsh_patient.first_name, "
+ " dsh_patient.last_name, "
+ " dsh_patient.address, "
+ " dsh_patient.city, "
+ " dsh_patient.state, "
+ " dsh_patient.zip, "
+ " dsh_patient.date_in, "
+ " dsh_patient.date_out "
+ " FROM dsh_patient "
+ " WHERE dsh_patient.patient_id = "+ holdString +""
));
int stringLenght = holdQuery.length(); //get the lenght of the query output
counterChar = 0;// reset counter
counterCol = 0;// reset counter
for (int i = 0; i < stringLenght; i++)
{
stringBit = holdQuery.charAt(i); //get each character in the string one at a time
if (stringBit == 42)// if it is the * character
{
counterCol +=1; //increment column counter
switch (counterCol)
{
case 1:
case 2:
finalString += ("");
break;
case 3:
finalString += ("\n");
break;
case 4:
case 5:
case 6:
finalString += (" ");
break;
case 7:
finalString += ("\nDate Admitted: ");
break;
case 8:
finalString += ("\nDate Discharged: ");
break;
case 9:
counterCol = 0;// reset counter
break;
}//end switch
counterChar = 0;// reset counter
}//end if
else if (stringBit == 10)
{
MainOutputField.append("\n");//add carriage return at end of line
MainOutputField.append(finalString);//print the line when CR is encountered
finalString = ""; // clear contents of finalString after printing the line
}//end else if
else
{
finalString += stringBit;// add char to final output string
counterChar +=1; //increment word length counter
}//end else
}//end for
MainOutputField.append ("\n\n");
MainOutputField.append("Code Decription Charge\n");
MainOutputField.append("---- ---------- ------\n");
//holdQuery contains DISTINCT data for only one patient (=selected patient ID#)
holdQuery = (Select("SELECT dsh_patient_charges.item_code, "
+ " dsh_services.description, "
+ " dsh_services.charge "
+ " FROM dsh_services, dsh_patient_charges "
+ " WHERE dsh_patient_charges.patient_id = "+ holdString +""
+ " AND dsh_patient_charges.item_code = dsh_services.item_code"
));
stringLenght = holdQuery.length(); //get the lenght of the query output
counterChar = 0;// reset counter
counterCol = 0;// reset counter
for (int i = 0; i < stringLenght; i++)
{
stringBit = holdQuery.charAt(i); //get each character in the string one at a time
if (stringBit == 42)// if it is the * character
{
counterCol +=1; //increment column counter
switch (counterCol)
{
case 1:
finalString += (" ");
break;
case 2:
spacer = (maxCol - counterChar +11);
for (int x = 1; x < spacer; x++)
{
finalString += (" ");//line up columns
}
break;
case 3:
counterCol = 0;// reset counter
counterChar = 0;// reset counter
break;
}//end switch
}//end if

else if (stringBit == 10)
{
MainOutputField.append("\n");//add carriage return at end of line
MainOutputField.append(finalString);//print the line when CR is encountered
finalString = ""; // clear contents of finalString after printing the line
}//end else if
else
{
finalString += stringBit;// add char to final output string
counterChar +=1; //increment word length counter
}//end else if
}//end for

MainOutputField.append ("\n\n");
MainOutputField.append ("------------------------------------------------\n");

//calculate Balance Due

//test Compute query
int sum = 0;
finalString ="";
//uses TOTAL method which gets only charges (column) data
holdQuery = (Total("SELECT dsh_services.charge "
+ " FROM dsh_services, dsh_patient_charges "
+ " WHERE dsh_patient_charges.patient_id = "+ holdString +""
+ " AND dsh_patient_charges.item_code = dsh_services.item_code"
));
stringLenght = holdQuery.length(); //get the lenght of the query output
for (int z = 0; z<stringLenght; z++)
{
stringBit = holdQuery.charAt(z);// get each number (as char)
if (stringBit != '.')// if it is NOT a decimal
{
finalString += stringBit;//combine the characters into a complete number
}//end if
else//if '.' is reached, add the number to the total sum
{
sum += Integer.parseInt(finalString);
z+=2;// move forward 2 in string
finalString =""; //clear finalString
}//end else

}//end for

MainOutputField.append (" Balance Due: $"+ sum +".00");
}//end if command = LISTALL

// UpdateRecordButton ******************************************************************************************
else if (command == UPDATE)
{
String revOrderString = "";
holdString="";
holdString2="";
holdQuery="";
finalString ="";
holdData =0;
holdString = PAEnterPatID.getText();; //patient's ID# input
holdString2 = ItemCodeField.getText();; //item code input
MedicalServicesField.setText("Adding Database record\n");
// first find the last record in the table
// get all tracking numbers from table dsh_patient_charges
holdQuery= (Update ("SELECT dsh_patient_charges.tracking_num FROM dsh_patient_charges"));
//get tracking record number from string and cast it to an integer
finalString = "";
int stringLenght = holdQuery.length(); //get the lenght of the query output
for (int y = stringLenght-2; y>stringLenght -6; y--)//gets only the last tracking number
{
stringBit = holdQuery.charAt(y);// reads each character one by one
finalString += stringBit;//puts the characters together
}//end for
//reverse order of the string
revOrderString = finalString;//hold finalString's contents
finalString = "";//clear finalString
for (int z = 3; z >-1; z--)
{
stringBit = revOrderString.charAt(z);// reads each character one by one
finalString += stringBit;// add each character back to finalString
}
holdData = Integer.parseInt(finalString);// make the string into an integer.
holdData +=1; //increment it by one for the next record row
MedicalServicesField.setText(Select("INSERT into dsh_patient_charges values ("
+ holdData + ","
+ holdString + ","
+ holdString2+ ")"));
MedicalServicesField.append("\n Inserted "+ holdData +", "+ holdString + ", and "+ holdString2+" into database");
}//end else if command == UPDATE

}// end actionPerformed method

//general query function*****************************************************************************
public String Select(String QueryLine)
{
String Output="";
int columns;
int pos;

try //These methods can throw exceptions, query probs, connection probs
{
Statement stmt = con.createStatement();
//Instantiate a Statement class to execute query
ResultSet rs = stmt.executeQuery(QueryLine);
//Similiar to a pointer that can be used to retreive results from jdbc connection
columns=(rs.getMetaData()).getColumnCount();
//metadata objects contains a getColumnCount method to show how many columns are retreived
System.out.println((rs.getMetaData()).getColumnCount());

while(rs.next())
{
//Fetch rows one by one
for( pos=1; pos<=columns; pos++)
{
Output+=rs.getString(pos)+" *";//Cast result in the form of a string
// seperate with * character
}
Output+="\n";//for each row we fetch add carriage return
}
stmt.close();
}

catch( Exception e )
{
e.printStackTrace();
Output=e.getMessage();
}
return Output;//before exiting, return the result we got
}// end String Select method

// total function**************************************************************************************************************
//to compute the total for the Record button function
public String Total(String QueryLine)
{
String Output="";
int columns;
int pos;

try
{
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(QueryLine);
columns=(rs.getMetaData()).getColumnCount();
System.out.println((rs.getMetaData()).getColumnCount());

while(rs.next())
{
//Fetch rows one by one
for( pos=1; pos<=columns; pos++)
{
Output+=rs.getString(pos)+"";//Cast result in the form of a string
// no seperation character
}
//Output+="\n";//for each row we fetch add carriage return
}
stmt.close();
}

catch( Exception e )
{
e.printStackTrace();
Output=e.getMessage();
}
return Output;//before exiting, return the result we got
}// end String Compute method

// Update function**************************************************************************************************************
//to retreive the last record in the tracking column (primary key) to allow addition of next record
public String Update(String QueryLine)
{
String Output="";
int columns;
int pos;

try
{
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(QueryLine);
columns=(rs.getMetaData()).getColumnCount();
System.out.println((rs.getMetaData()).getColumnCount());

while(rs.next())
{
//Fetch rows one by one
for( pos=1; pos<=columns; pos++)
{
Output+=rs.getString(pos)+"";//Cast result in the form of a string
// no seperation character
}
Output+="\n";//for each row we fetch add carriage return
}

stmt.close();
}

catch( Exception e )
{
e.printStackTrace();
Output=e.getMessage();
}
return Output;//before exiting, return the result we got
}// end String Compute method

//destroy******************************************************************************************
public void destroy()
{
try
{
con.close();
}
catch( Exception e )
{
e.printStackTrace();
System.out.println(e.getMessage());
}
} //end destroy method

} //end applet*************************************************

*************************************