Chapter 11: Examples of Java Applications

This chapter provides many examples of Java applets or standalone applications. Each example covers a particular topic discussed in this book.

Handling Simple Database Statements

Almost all database applications written in Java handle normal rows of data. SQL (Structured Query Language) only provides ways of inserting and extracting data in a tabular format, while permitting complex queries to be issued.

A simple Interactive SQL client application

My first example is a simple ISQL client application that shows how to handle normal rows of data in a simple way. ISQL stands for Interactive SQL, which means that this client can be used to send queries to a database and then retrieve the results in a text-based interface. This ISQL client is a standalone Java application.

As shown in Figure 11-1, after this application is launched, a number of questions appear on the screen. The questions prompt you for a Java Database Connectivity (JDBC) uniform resource locator (URL), a database login, and a database password. You then enter SQL queries from the keyboard, and the results immediately appear on-screen. This application is really a primitive ISQL client. However, it does support SQL selects, updates, and multiline queries.

Figure 11-1: The ISQL standalone application.

As Figure 11-1 shows, this simple ISQL standalone application runs in a terminal window. Its input is taken from the standard input, and its output is directed to the standard output. Default values are provided for connection parameters just for ease of use.

Listing 11-1 gives you an example of an ISQL session. It uses a database with only three tables: clients, accounts, and transaction history. These tables are used in another example later in the Bank of Java applet.

Listing 11-1: Simple Java ISQL

Enter the url or [ENTER] for jdbc:odbc:netbank : Enter the login or [ENTER] for dba : Enter the passwd or [ENTER] for default : Type ‘quit’ on a blank line to exit, or ‘go’ to execute the query. 1> select * from clients 2> go ownerno, name, address 1, Bernard Van Haecke, Brussels, 1000 2, John Doe, Imola Circuit, KM83 3, Jane Doe, Imola Circuit, KM83 4, Santa Klaus, North Pole, 1 5, Little Duke, Java Island, 1 6, The Bank, Downtown LA 1> select name, acctno, balance 2> from clients, accounts 3> where clients.ownerno = accounts.ownerno 4> order by balance 5> go name, acctno, balance Little Duke, 5, -840 Jane Doe, 3, 320600 Bernard Van Haecke, 1, 991900 John Doe, 2, 1256050 Santa Klaus, 4, 8892750 The Bank, 6, 999999995904 1> select distinct typetransaction 2> from history 3> go typetransaction Received Transferred Withdraw 1> select sum(balance) 2> from accounts 3> go sum(balance) 1000011464704 1> update accounts 2> set balance = balance + (balance * 0.05) 3> go 6 row(s) affected. 1> quit

There is only one class for this example: The class isql handles everything. The class constructor initializes the database connection and then calls a method that handles the user’s input in a loop to enable entry of multiple queries. The keywords go and quit are caught to process a query or quit the application. The database connection is closed when a quit is issued or a fatal error occurs.

Listing 11-2 shows the source code for this ISQL client.

Listing 11-2: An Interactive SQL Client

import java.sql.*; import java.io.*; import java.util.*; /**  * Title:        isql  * Description:  * Company:  * @author Bernard Van Haecke  * @version 1.0 */ public class isql {   static DataInputStream kbd = new DataInputStream(System.in);   static String url = "jdbc:odbc:netbank";   static String driver = "sun.jdbc.odbc.JdbcOdbcDriver";   static String login = "dba";   static String passwd = "javabank";   static Connection curConn = null;   public static void main(String argv[]) throws IOException   {     String temp = "";     System.out.println("Simple Java Isql, by Bernard Van Haecke,   1996.\n");     System.out.print("Enter the url or [ENTER] for " + url + " : ");     System.out.flush();     temp = kbd.readLine();     if (!temp.equals("")) url = temp;     System.out.print("Enter the login or [ENTER] for " + login +  " : ");     System.out.flush();     temp = kbd.readLine();     if (!temp.equals("")) login = temp;     System.out.print("Enter the passwd or [ENTER] for default :  ");     System.out.flush();     temp = kbd.readLine();     if (!temp.equals("")) passwd = temp;     isql session = new isql();   }   public isql() throws IOException   {     try {       Class.forName(driver);       curConn = DriverManager.getConnection(url, login,  passwd);       checkForWarnings(curConn.getWarnings ());     }     catch(java.lang.Exception ex) {       System.out.println("url : " + url);       System.out.println("login : " + login);       System.out.println("passwd : " + passwd);       ex.printStackTrace();       return;     }     processQueries();     finalize();   }   protected void finalize()   {     try {       curConn.close();     }     catch (SQLException ex) { }   }   private void processQueries() throws IOException   {     int i = 1;     String temp = "";     String query = "";     String results = "";     System.out.println("Type ‘quit’ on a blank line to exit, or ‘go’ to execute      the query.");     do {       System.out.print(i + "> ");       System.out.flush();       temp = kbd.readLine();       if (temp.equals("quit"))       break;       if (temp.equals("go")) {         executeThisQuery(query);         i = 1;         query = "";       }       else {         query = query + " " + temp;         i++;       }     } while (true);   }   private void executeThisQuery(String sqlText)   {     boolean resultSetIsAvailable;     boolean moreResultsAvailable;     int i = 0;     int res=0;     try {       Statement curStmt = curConn.createStatement();       resultSetIsAvailable = curStmt.execute(sqlText);       ResultSet rs = null;       for (moreResultsAvailable = true;  moreResultsAvailable;)       {         checkForWarnings(curConn.getWarnings());         if (resultSetIsAvailable)         {           if ((rs = curStmt.getResultSet()) !=  null)           {             // we have a resultset             checkForWarnings(curStmt.getWarnings());             ResultSetMetaData rsmd = rs.getMetaData();             int numCols = rsmd.getColumnCount();             // display column headers             for (i = 1; i <= numCols; i++)             {               if (i > 1) System.out.print(", ");               System.out.print(rsmd.               getColumnLabel(i));             }             System.out.println("");             // step through the rows             while (rs.next())             {               // process the columns               for (i = 1; i <= numCols;  i++)               {                 if (i > 1) System.out.print(", ");                 System.out.print(rs.getString(i));               }               System.out.println("");             }           }         }         else         {           if ((res = curStmt.getUpdateCount()) != -1)           {             // we have an updatecount             System.out.println(res + "  row(s) affected.");           }           // else no more results           else           {             moreResultsAvailable = false;           }         }         if (moreResultsAvailable)         {           resultSetIsAvailable =  curStmt.getMoreResults();         }       }       if (rs != null) rs.close();       curStmt.close();     }     catch (SQLException ex) {       // Unexpected SQL exception.       ex.printStackTrace ();     }     catch (java.lang.Exception ex) {       // Got some other type of exception. Dump it.       ex.printStackTrace ();     }   }   private static void checkForWarnings (SQLWarning warn) throws SQLException   {     while (warn != null) {       System.out.println(warn);       warn = warn.getNextWarning();     }   } }

A simple ISQL client applet

My second example is another ISQL client. It is different from the previous example because it runs as an applet. Anyone with a Java-enabled World Wide Web (WWW) browser can load the Hypertext Markup Language (HTML) file containing the applet tag that calls this applet. The logic to execute the SQL statements is similar to the logic of the command-line ISQL example.

A 100 percent Java JDBC driver must be supplied with such an applet. The JDBC-ODBC Bridge, for example, wouldn’t work because it calls native methods to talk to ODBC. “All-Java” drivers are becoming available as more and more developers implement the JDBC Driver Application Programming Interface (API).

It is remarkably simple to provide the 100 percent Java JDBC driver classes to the calling browser: The classes — the JDBC driver package — must be available in the directory where the applet resides. As soon as the Java applet is loaded in the virtual machine running in the WWW browser, the class loader notices that it needs additional classes, and a call to Class.forName() dynamically loads the appropriate driver class. The Hypertext Transfer Protocol (HTTP) server then sends the requested classes.

As shown in Figure 11-2, the applet lets you enter a SQL query in a floating window.

Figure 11-2: The ISQL applet.

Clicking the Go! button sends the query to the database and retrieves the results. Figure 11-3 shows the resulting rows of data displayed in another floating window.

Figure 11-3: Executing SQL statements.

The graphical user interface (GUI) part of this applet was created using Marimba Bongo, which generates a 100 percent portable .gui file containing the persisted GUI objects. This file contains a persistent form of the widgets used in this example. The unzipped Marimba classes must be in the CLASSPATH or available in the applet’s home directory on the WWW server to run this example.

On the Web 

The .gui file uses several Marimba Bongo classes, which are available in demo version on this book’s companion Web site at www.hungryminds.com/extras/. Please see the Preface for details on accessing files on the Web site.

The following is the HTML file that calls the applet. The connection parameters are passed as arguments to the applet for greater convenience. These parameters specify which JDBC driver to load and which database URL to use. Note that the JDBC URL is dependent of the data source and driver. Its format varies a lot between products. In this case, you can notice that the database host is database. eng and its TCP/IP port is 8192.

<applet name="jsql" code="jsql.class" width="430" height="390" align="Top" alt="If you had a java-enabled browser, you would see an applet here." > <param name="driver" value="com.sybase.jdbc.SybDriver"> <param name="url" value="jdbc:sybase:Tds:database.eng:8192"> <param name="login" value="guest"> <param name="password" value="javabank"> <param name="catalog" value="javabank"> <hr>If your browser recognized the applet tag, you would see an applet here.<hr> </applet> 

Listing 11-3 contains the source code for this ISQL applet example. Remember that the Marimba classes must be in the CLASSPATH or in the applet’s home directory on the WWW server to run.

Listing 11-3: An Interactive SQL Applet

import java.awt.*; import java.sql.*; import java.lang.*; import java.util.*; import java.net.*; import marimba.gui.*; /**  * Title:        jsql  * Description:  * Company:  * @author Bernard Van Haecke  * @version 1.0  */ public class jsql extends java.applet.Applet {   Presentation presentation;   PlayerPanel player;   PlayerUtil util;   Connection conn = null;   DatabaseMetaData mtdt = null;   ResultSet rs = null;   /**   * initialize the applet   */   public void init() {     try {     presentation =      Presentation.getPresentation(new URL(getDocumentBase(), "jsql.gui"));     }     catch (MalformedURLException ex) {       ex.printStackTrace();     }     // create a player panel     setLayout(new BorderLayout());     add("Center", player = new PlayerPanel());     // set the presentation     player.setPresentation(presentation);     // create a player utility object     util = new PlayerUtil(player);     // load applet parameters      ((ChoiceWidget) util.getWidget("dataURL")).addChoice(getParameter("url"));      ((ChoiceWidget)       util.getWidget("dataDriver") ).addChoice(getParameter("driver"));     util.setText("dataLogin", getParameter("login"));     util.setText("dataPasswd", getParameter("password"));     util.setText("dataCatalog", getParameter("catalog"));     // add some jdbc connection choices     // jdbc-odbc bridge     addDriverInfo("jdbc:odbc:data-source-name",       "sun.jdbc.odbc.JdbcOdbcDriver");     // sybase’s driver     addDriverInfo("jdbc:sybase:Tds:host.domain.com:8192",       "com.sybase.jdbc.SybDriver");     // connect software’s sybase driver     addDriverInfo("jdbc:sybase://host.domain.com:8192",       "connect.sybase.SybaseDriver");     // funny driver     addDriverInfo("foo:bar:database", "foo.bar.Driver");   }   /**   * add a new entry in the url and driver listboxes   */   public void addDriverInfo(String url, String driver)   {     // add entry for this driver provider      ((ChoiceWidget) util.getWidget("dataURL")).addChoice(url);      ((ChoiceWidget) util.getWidget("dataDriver")).addChoice(driver);   }   /**   * we handle all gui events here   */   public boolean handleEvent(Event evt)   {     try {       if ((evt.id == Event.ACTION_EVENT) && (evt.target instanceof Widget)) {         Widget w = (Widget)evt.target;         String nm = w.getName();         if (nm != null) System.out.println("Event: " + nm);         // The user has logged in.         if (nm.equals("dataLoginButton")) {           String url = util.getText("dataURL").trim();           String uid = util.getText("dataLogin").trim();           String pwd = util.getText("dataPasswd").trim();           String catalog = util.getText("dataCatalog").trim();           String driver = util.getText("dataDriver").trim();           try {             Class.forName(driver);             conn = DriverManager.getConnection(url, uid, pwd);             if (conn != null) {               mtdt = conn.getMetaData();               conn.setCatalog(catalog);             }           }           catch (SQLException ex) {             System.out.println(ex);           }           catch (java.lang.Exception ex) {             System.out.println(ex);           }         }         // The user has clicked logout         if (nm.equals("dataLogoutButton")) {           if (conn != null) {             conn.close();           }         }         // execute the sql query         if (nm.equals("isqlGoButton")) {           String query = util.getText("isqlQueryText");           if (true)           {             TableWidget tbl = (TableWidget)  util.getWidget("isqlResultTable");             tbl.removeAllRows();             tbl.removeAllColumns();             util.show("isqlResultWindow", true);             ResultSet rs = getSingleRS(query);             Vector headers = getRSColumnHeadersAsVector(rs);             int i;             for (i=0; i<headers.size(); i++)             tbl.addColumn((String) headers.elementAt(i));             Vector rows = getRSRowsAsVector(rs);             for (i=0; i<rows.size(); i++)               tbl.addRow((Vector) rows.elementAt(i));             rs.close();           }         }         // close the isql result window         if (nm.equals("resultCloseButton")) {           util.show("isqlResultWindow", false);         }       }     }     catch(java.lang.Exception ex) {       ex.printStackTrace();     }     return super.handleEvent(evt);   }   /**   * return the resultset of a simple query   */   public ResultSet getSingleRS(String sqlText)   {     ResultSet rs = null;     int res;     try {       Statement st = conn.createStatement();       if (st.execute(sqlText)) {         // okay it’s not an update count         rs = st.getResultSet();       }       else if ((res = st.getUpdateCount()) != -1) {         // it’s an update count         // we could display it       }     } catch (SQLException ex) { ex.printStackTrace(); }     return rs;   }   /**   * return the column headers of a resultset as vector   */   public Vector getRSColumnHeadersAsVector(ResultSet rs) {     int i;     Vector v = new Vector();     try {       ResultSetMetaData rsmd = rs.getMetaData();       int numCols = rsmd.getColumnCount();       // fetch column headers       for (i = 1; i <= numCols; i++)       {         v.addElement(rsmd.getColumnLabel(i));       }     }     catch (SQLException ex)     {     }     return v;   }   /**   * return a resultset as vector   */   public Vector getRSRowsAsVector(ResultSet rs) {     ProgressIndicatorWidget bar = (ProgressIndicatorWidget)      util.getWidget( "sqlProgressBar");     int barValue = 0;     Vector v = new Vector();     Vector r = null;     int i;     try {       ResultSetMetaData rsmd = rs.getMetaData();       int numCols = rsmd.getColumnCount();       bar.setValue(0);       // step through the rows       while (rs.next())       {         // process the columns         r = new Vector();         for (i = 1; i <= numCols; i++)         {           r.addElement(rs.getString(i));         }         v.addElement(r);         if (barValue < 100) {           barValue = barValue + 10;         }         else {           barValue = 0;         }         bar.setValue(barValue);         bar.repaint();       }     }     catch (SQLException ex)     {     }     return v;   } }