The MSQL Java Package

In this section will will write our own SQL client to connect to our MSQL server and manipulate some data. To make our live easier, we will use the "MSQL Java" package, written by Darryl Collins from the University of Queensland in Australia. The very first thing you must do is to download that package to your own computer.

Click here to download the MSQL Java package

The package consists of several *.java and corresponding *.class files in zipped format. Make sure you save those files into a separate, new directory on your computer. Once you saved the above file, you need to unzip it (for questions, post !). All *.java and *.class files must reside in a new directory. You can, if you like, delete the zip file after you unzipped the files.

All applets and programs that you write using the MSQL Java package must be saved in the same directory as the MSQL Java files downloaded above. For a tutorial written by Darryl Collins, check a separate link for this lecture. Here, we will create a few applets that manipulate tables in the database "csas4083".

Note: I will assume the following for the remaining examples:

	you have downloaded an unzipped the MSQL Java package, described above
	you have created a table in the "csas4083" database, using the "msql client", as described in the previous lecture.
	your database contains four fields, "fname", "lname", "phone", and "email"
	your database is named "username_address", where "username" is your Sciris user name

In all examples below, I will use my own table, named "wachsmut_address". You should substitute your own table name whenever appropriate. If you did not create your own table, however, feel free to manipulate my table instead, if you must. For details on the classes and methods available in the MSQL Java package, check the appropriate link for this lecture (you should download that MSQL API to your local computer as well).

Example 1: Let's retrieve all last names from our table, and display them in a list. We will create an applet with one "Load" button. When clicking on that button, our applet will connect to the MSQL server, retrieve all last names, and place them into a list. Note that only the last names will be retrieved. Here is the code:

import java.applet.*;
import java.awt.*;

public class AddressNames extends Applet
{
   private Button load = new Button("Load");
   private List names = new List();
   private Label status = new Label("Click on Load to connect");

   private Msql server = new Msql();

   public void init()
   {
      Panel buttons = new Panel();
      buttons.setLayout(new FlowLayout());
      buttons.add(load);
      
      setLayout(new BorderLayout());
      add("North", status);
      add("Center", names);
      add("South", buttons);
   }

   public boolean action(Event e, Object o)
   {
      if (e.target == load)
         handleLoad();
      return true;
   }

   private void handleLoad()
   {
      try
      {
         status.setText("Connecting, please wait ...");
         server.Connect("sciris.shu.edu","wachsmut");
         status.setText("Selecting database, please wait ...");
         server.SelectDB("csas4083");
         status.setText("Retrieving data, please wait ...");
         MsqlResult result = server.Query("select lname from wachsmut_address");
        
         String row[]; 
         int rows = result.NumRows(); 

         while((row = result.FetchRow()) != null) 
            names.addItem(row[0]); 
         status.setText("List of last names in the database:");
         server.Close();  
      }
      catch(MsqlException e)
      {
         status.setText("Unknown error. Check Java console for details."); 
         System.err.println(e);
      }
   }
}

Here is the applet in action. Note that the applet must be stored on Sciris (because of security restrictions), while this homepage is on "www.shu.edu" - thus, I have to use the "CODEBASE" option in the APPLET tag ...

There are the usual setup methods in the init method, and the standard action method. One nice feature is the use of a "Label" to display status information. I could have used the browser's status line as well (with showStatus("blah blah")), but this is somewhat nicer.

Don't forget to change the name of the database, and the name under which to connect, to your own name if you created your own table.

The main code is in the "handleLoad" method. Here's what's happening (removing the status.setText() lines and the "catch" block):

server.Connect("sciris.shu.edu","wachsmut"); Connects to MSQL database server running on "sciris.shu.edu" as user "wachsmut". No password is required to connect to the Msql server in this case. Note that for this to work, your applet must be loaded from the same machine "sciris.shu.edu"; otherwise, applet security restrictions will prevent you from making a connection. You can, however, use the "appletviewer": it allows you to relax the security restrictions by clickin on "Applet | Properties" and changing the default entries. If a connection can not be established, the "catch" block will display an error message in the status line. server.SelectDB("csas4083"); Once connected, this command requests that the database "csas4083" is selected for further processing. You must always select a database first, before processing the table info. MsqlResult result = server.Query("select lname from wachsmut_address");  Sending the SQL query. This is the same query that I used to type in when using the "msql client" on Sciris. However, the user of my applet does not need to know any SQL. They just click on the button, and the appropriate command is sent automatically. When successful, this command will return the data in an object of type MsqlResult (which is, bascially, a table representing all or some of the data). In our case, our table will have only one column, since we only selected the "lname" column. It will have as many rows as there are data entries. String row[];
int rows = result.NumRows();
while((row = result.FetchRow()) != null)
   names.addItem(row[0]); This is the code to retrieve the data from the field "result" of type "MsqlResult". We need an array of Strings for each row (actually, right now a row has only one column, so an array may not be necessary).  Then, in the while loop, we use the "FetchRow" method of the "MsqlResult" to fetch a row of data. We store it in "row", and keep doing that until the "row" is null, i.e. we have looked at all the data. The "FetchRow" method automatically starts with the first row, and each subsequent call to that method will retrieve the next row automatically, or return null if there is no more next row. Inside the while loop, the 0th entry of the array of Strings row is added to our list of names. It's the 0th entry, because each row only has one column anyway, in this case. server.Close();  Closing the connection to the Msql server. That should always be used after the data has been successfully retrieved or manipulated.  By the way, what will happen if you click on "Load", wait until the names are there, then click on "Load" again ? That's not, of course, what we want, so how could you change the applet to remedy the situation ? 

Example 2: Now let's retrieve all information, but this time we'll store it in a TextArea instead of a List. We do that because a TextArea can be scrolled left and right, while a List can only go up and down. Here's the code, with the major modifications in the "handleLoad" method.

import java.applet.*;
import java.awt.*;

public class Addresses extends Applet
{
   private Button load = new Button("Load");
   private TextArea names = new TextArea();
   private Label status = new Label("Click on Load to connect");

   private Msql server = new Msql();

   public void init()
   {
      Panel buttons = new Panel();
      buttons.setLayout(new FlowLayout());
      buttons.add(load);
      
      setLayout(new BorderLayout());
      add("North", status);
      add("Center", names);
      add("South", buttons);
   }

   public boolean action(Event e, Object o)
   {
      if (e.target == load)
         handleLoad();
      return true;
   }

   private void handleLoad()
   {
      try
      {
         status.setText("Connecting, please wait ...");
         server.Connect("sciris.shu.edu","wachsmut");
         status.setText("Selecting database, please wait ...");
         server.SelectDB("csas4083");
         status.setText("Retrieving data, please wait ...");
         MsqlResult result = server.Query("select * from wachsmut_address");
        
         String row[]; 
         int rows = result.NumRows(); 
         int cols = result.NumFields();

         names.setText("");
         while((row = result.FetchRow()) != null) 
         {
            names.appendText(row[0] + " " + row[1] + "\n");
            if (!row[2].equals(""))
               names.appendText("\t Phone: " + row[2] +"\n");
            if (!row[3].equals("")) 
               names.appendText("\t Email: " + row[3] + "\n"); 
         }
         status.setText("List of last names in the database:");
         server.Close();  
      }
      catch(MsqlException e)
      {
         status.setText("Unknown error. Check Java console for details."); 
         System.err.println(e);
      }
   }
}

Here's the applet in action.

The changes were pretty simple: I changed the type of the "names" Field from "List" to "TextArea", and the "addItem" methods in the "handleLoad" method to "appendText". Then I also append row[i] objects, with the approriate label in front, if they are present, to get a reasonably nice display format. Remember that "\n" stands for "new line" and "\t" for tab character. Don't forget to change the name of the database, and the name under which to connect, to your own name if you created your own table.

Example 3: Now let's change our AddressBook applet so that we can enter new information to our address list, as well as delete the existing information. We will this time display only one row at a time, but we will add buttons to "Add", "Change", or "Delete" the information, as well as a "Next" and a "Previous" button to move around in our database. The data is loaded as soon as the applet initializes, so the "Load" button is no longer needed.

Note that the variable "result" is now a field in our class, so that every method can access that information. Also, another variable 'currentRow' has been added as a field. For the rest, read the code carefully, figure out what it does, and why it might work, and if you have any questions, POST THEM !
 

import java.applet.*;
import java.awt.*;

public class AddressBook extends Applet
{
   private static final String HOST  = "sciris.shu.edu";
   private static final String USER  = "wachsmut";
   private static final String DBASE = "csas4083";
   private static final String TABLE = "wachsmut_address";
   private static final int    nTexts = 4;

   private Button back = new Button("<=");
   private Button next = new Button("=>");
   private Button add  = new Button("Add");
   private Button del  = new Button("Delete");
   private Button mod  = new Button("Change");

   private TextField texts[] = {new TextField() , new TextField(), 
                                 new TextField(),  new TextField() };
   private Label labels[] = {new Label("First Name:"), new Label("Last Name"),
                             new Label("Phone Number"), new Label("Email Address") };
   private MsqlFieldDesc fields[] = null;

   private Label status = new Label("Click on Load to connect");
   private MsqlResult result = null;
   private int currentRow = 0;
   private Msql server = new Msql();

   public void init()
   {
      Panel buttons = new Panel();
      buttons.setLayout(new FlowLayout());
      buttons.add(back);
      buttons.add(next);
      buttons.add(add);
      buttons.add(del);
      buttons.add(mod);
    
      Panel dataGrid = new Panel();
      dataGrid.setLayout(new GridLayout(4, 2));
      for (int i = 0; i < nTexts; i++)
      {
         dataGrid.add(labels[i]);
         dataGrid.add(texts[i]);
      }

      setLayout(new BorderLayout());
      add("North", status);
      add("Center", dataGrid);
      add("South", buttons);

      sendQuery("select * from " + TABLE);
      fields = result.ListFields();
      showRow();
   }
   public boolean action(Event e, Object o)
   {
      if (e.target == next)
         handleNext();
      else if (e.target == back)
         handleBack();
      else if (e.target == add)
         handleAdd();
      else if (e.target == del)
         handleDel();
      else if (e.target == mod)
         handleMod();
      return true;
   }
   private void handleNext()
   {
      if (currentRow < result.NumRows() - 1)
      {
         currentRow++;
         showRow();
      }
      else
         status.setText("No more data available.");
   }
   private void handleBack()
   {
      if (currentRow > 0)
      {
         currentRow--;
         showRow();
      }
      else
         status.setText("Already at first data set.");
   }
   private void handleAdd()
   {
      if (!texts[1].getText().trim().equals(""))
      {  
         String sqlQuery = new String("insert into " + TABLE + " values (");
         for (int i = 0; i < nTexts-1; i++)
            sqlQuery += "'" + texts[i].getText().trim() +"',";
         sqlQuery += "'" + texts[nTexts-1].getText().trim() +"')";

         sendQuery(sqlQuery);
         sendQuery("select * from " + TABLE);

         currentRow = result.NumRows()-1;
         showRow();
      }
      else
         status.setText("You must provide at least a last name.");
   }

   private void handleDel()
   {
      String sqlQuery = new String("delete from " + TABLE + " where ");
      for (int i = 0; i < nTexts-1; i++)
         sqlQuery += fields[i].FieldName()+ " = '" + texts[i].getText().trim() + "' and ";
      sqlQuery += fields[nTexts-1].FieldName() + " = '" + texts[nTexts-1].getText().trim() +"'";

      sendQuery(sqlQuery);
      sendQuery("select * from " + TABLE);
      if (currentRow == result.NumRows())
         currentRow--;
      showRow();
   }

   private void handleMod()
   {
      result.DataSeek(currentRow);
      String row[] = result.FetchRow();

      String sqlQuery = new String("update " + TABLE + " set ");
      for (int i = 0; i < nTexts - 1; i++)
         sqlQuery += fields[i].FieldName() + " = '" + texts[i].getText().trim() + "', ";
      sqlQuery += fields[nTexts-1].FieldName() + " = '" + texts[nTexts-1].getText().trim() +"'";

      sqlQuery += " where ";
      for (int i = 0; i < nTexts - 1; i++)      
         sqlQuery += fields[i].FieldName() + " = '" + row[i] + "' and ";
      sqlQuery += fields[nTexts-1].FieldName() + " = '" + row[nTexts-1] +"'";

      sendQuery(sqlQuery);
      sendQuery("select * from " + TABLE);
      showRow();      
   }
   private void showRow()
   {
      result.DataSeek(currentRow);
      String row[] = result.FetchRow();
      for (int i = 0; i < nTexts; i++)
         texts[i].setText(row[i]);
      status.setText("Showing row " + (currentRow+1) + " of " + result.NumRows());
   }

   private void sendQuery(String query)
   {
      try
      {
         status.setText("Connecting, please wait ...");
         server.Connect(HOST,USER);
         server.SelectDB(DBASE);
         result = server.Query(query);
         server.Close();  
      }
      catch(MsqlException e)
      {
         status.setText("Unknown error. Check Java console for details.");
         System.err.println("Sending: " + query);
         System.err.println(e);
         result = null;
      }
   }
}

Here's the table in action. Note that are dealing with "life" data: any change, addition, or deletion you make will immediately be saved. Everybody else will then see those changes. So, be careful and don't delete all of the data. Only delete data that you yourself has entered.

      if (!texts[1].getText().trim().equals(""))

         String sqlQuery = new String("insert into " + TABLE + " values (");
         for (int i = 0; i < nTexts-1; i++)
            sqlQuery += "'" + texts[i].getText().trim() +"',";
         sqlQuery += "'" + texts[nTexts-1].getText().trim() +"')";

         sendQuery(sqlQuery);

        sendQuery("select * from " + TABLE);

         currentRow = result.NumRows()-1;
         showRow();