Dealing with Database Transactions

As shown in Chapter 7, transactions group the execution of a number of SQL statements to maintain consistency in multiuser environments. In the following example, I group multiple SQL INSERT statements to ensure consistent money transfers by using database transactions.

The Bank of Java applet is similar to the software provided by banks to their customers to perform operations on their accounts from their home computers with telephones and modems. It was quite simplified to illustrate database transactions with JDBC.

The main operations are these:

  1. Welcome a client and prompt for the client’s ID and PIN code.

  2. Look up the client’s balance and transaction history in the database.

  3. Perform money transfers to other accounts.

  4. For fun, withdraw virtual $20 banknotes.

The information about clients and their accounts is, of course, stored in a database. The data structure was kept simple to allow a quick understanding of the whole application and the transaction mechanism. Database transactions are performed in the main thread, and a second thread serves as a clock. Each time an update is made to the database, the SQL queries that perform the update are grouped in a single transaction unit. By doing so, no inconsistent update can bring the database to an incoherent state.

If the client/server link is broken in the middle of a transaction, the transaction will be canceled; otherwise, it is committed. Canceling a transaction is called transaction rollback. Rolling back the transaction prevents unlogged transfers or unlogged withdrawals. Indeed, transfers and withdrawals are SQL INSERTs in database tables such as accounts and history log.

The GUI part of this applet was written using Marimba Bongo, which generates a 100 percent portable .gui file. This file contains a persistent form of the widgets used in this example. The unzipped Marimba classes must be in the CLASSPATH or present on the WWW server to run this example. The GUI part of the application uses the Marimba Bongo classes that are persisted to a portable file, which is the main reason why almost no GUI code is present in the source code. Each GUI control is a Marimba widget, which has a name and various properties that are also persisted in the permanent GUI file.

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.

Figure 11-5 shows the welcome screen of the Bank of Java applet. This screen prompts for an account number and a PIN code. There is a status bar below the validate button. If the PIN code is incorrect, this status bar displays an error message.

click to expand
Figure 11-5: The JavaBank applet login screen.

The names of the main controls on the welcome screen are as follows:

  • welcomeAccountNo: A text field to get the account number

  • welcomePinCode: A text field to get the personal identification number

  • welcomeOkButton: A button to log in to the database

  • welcomeEndButton: A button to close the database connection and quit the application

  • statusBar: A noneditable text field to display various messages

The values associated with these controls are checked and set within the program; the control names refer to them as instances of the Marimba GUI widgets.

After the client has logged in, a lookup is performed in the database, and the customer’s account balance and transaction history are displayed, as shown in Figure 11-6.

click to expand
Figure 11-6: The JavaBank applet account balance.

After the client has logged in, the available controls are the following:

  • balanceHistory: A scrollable text list to display the transaction history for the current account

  • balanceBalance: A noneditable text field to display the balance of the account

  • balanceRefreshButton: A button to refresh the history log

  • balanceEndButton: A button to exit the session

  • statusBar: A noneditable text field to display various messages

The client’s account balance and transaction history display each time the refresh button is pressed.

The next screen panel enables transfers to other accounts. The name and address of the recipient is looked up in the database and displayed in the status bar. Then the amount of money is transferred, as shown in Figure 11-7.

click to expand
Figure 11-7: The JavaBank applet transfer screen.

The control names for the transfer screen include the following:

  • transferAccountNo: A text field for the recipient’s account number

  • transferAmount: A text field for the amount of money to transfer

  • transferYesButton: A button to commit the transfer

  • transferEndButton: A button to exit the session

  • statusBar: A noneditable text field to display various messages

This is the automatic teller machine (ATM) panel. After choosing an amount to withdraw and after pressing the withdraw button, a Java banknote appears and scrolls on the screen. Figure 11-8 shows this panel.

click to expand
Figure 11-8: The JavaBank applet cash withdrawal.

The withdrawal screen controls include the following:

  • withdrawXX (where XX is one of 20, 100, 200, or 1,000): Radio buttons used to select the amount of money to withdraw

  • withdrawImage: A picture of a banknote that displays and scrolls when the withdrawal is committed

  • withdrawWithdrawButton: A button to commit the withdrawal

  • withdrawEndButton: A button to exit the session

  • statusBar: A noneditable text field to display various messages

The applet contains two important classes: Account and NetBank. NetBank is the main class, which handles user input, and Account has specific methods to perform normal bank operations on an account. The most essential part of this example, the JDBC and SQL code, is in the Account class.

The HTML file

This is the HTML page that calls the applet:

<html> <title>JavaBank Applet</title> <head> <h1>Welcome to JavaBank!</h1> </head> <body> <applet code=NetBank.class width=480 height=380> </applet> </body> </html>

Account.java

Listing 11-18 contains the source code for the Account.java class.

Listing 11-18: Account.java

start example
import java.sql.*; import java.io.*; import java.util.*; import java.net.*; /**  * Title:        Account  * Description:  * Company:  * @author Bernard Van Haecke  * @version 1.0  */ public class Account {   long acctNo = 0;   Connection curConn;   public Account(String url, String uid, String pwd) {     try {       Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");       curConn = DriverManager.getConnection(url, uid, pwd);     }     catch(java.lang.Exception ex) {       ex.printStackTrace();     }   }   protected void finalize()   {     try {       curConn.close();     } catch (SQLException ex) { }   }   public boolean verifyPinCode(long checkAcctNo, String checkPinCode)   {     boolean rc = (0 == 1);     String acctPinCode = "";     String curQueryString = "SELECT pincode FROM accounts WHERE acctno = " +      checkAcctNo;     try {       Statement curSt = curConn.createStatement();       curSt.setQueryTimeout(60);       ResultSet curRs = curSt.executeQuery(curQueryString);       while (curRs.next()) {         acctPinCode = curRs.getString("pincode");       }       curRs.close();       curSt.close();       rc = (checkPinCode.compareTo(acctPinCode) == 0);     } catch (SQLException ex) { }     if (rc) {       acctNo = checkAcctNo;     }     return rc;   }   public float checkAcctBalance()   {     float acctBalance = 0;     String curQueryString = "SELECT balance FROM accounts WHERE acctno = " +      acctNo;     try {       Statement curSt = curConn.createStatement();       curSt.setQueryTimeout(60);       ResultSet curRs = curSt.executeQuery(curQueryString);       while (curRs.next()) {         acctBalance = curRs.getFloat("balance");       }       curRs.close();       curSt.close();     } catch (SQLException ex) { }     return (acctBalance);   }   public Vector checkHistory()   {     Vector acctTransactionHistory = new Vector();     String curQueryString = "SELECT tdate, typetransaction, otheracct, amount,      ipaddress "     + "FROM history WHERE acctno = " + acctNo;     try {       Statement curSt = curConn.createStatement();       curSt.setQueryTimeout(60);       ResultSet curRs = curSt.executeQuery(curQueryString);       while (curRs.next()) {         acctTransactionHistory.addElement(curRs.getString(1) + " "           + curRs.getString(2) + " "           + curRs.getString(3) + " "           + curRs.getString(4) + " "           + curRs.getString(5));       }       curRs.close();       curSt.close();     } catch (SQLException ex) { }     return (acctTransactionHistory);   }   public Vector checkAcctOwnerName(long checkAcctNo)   {     Vector acctOwner = new Vector();     String curQueryString = "SELECT name, address FROM clients WHERE ownerno = " +      "(SELECT ownerno FROM accounts WHERE acctno = " + checkAcctNo + ")";     try {       Statement curSt = curConn.createStatement();       curSt.setQueryTimeout(60);       ResultSet curRs = curSt.executeQuery(curQueryString);       while (curRs.next()) {         acctOwner.addElement(curRs.getString("name"));         acctOwner.addElement(curRs.getString("address"));       }       curRs.close();       curSt.close();     } catch (SQLException ex) { }     return (acctOwner);   }   public void makeTransfer(long toAcctNo, float amount)   {     String curUpdateString = "UPDATE accounts SET balance =  balance + ?      WHERE acctno = ?";     String logInsertString = "INSERT INTO history (tdate, acctno, typetransaction,       otheracct, " +      "amount, ipaddress) VALUES (?, ?, ?, ?, ?, ?)";     try {       curConn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE   );       curConn.setAutoCommit(false);       PreparedStatement curSt =  curConn.prepareStatement(curUpdateString);       curSt.setQueryTimeout(60);       curSt.setFloat(1, -amount);       curSt.setLong(2, acctNo);       curSt.executeUpdate();       curSt.setFloat(1, amount);       curSt.setLong(2, toAcctNo);       curSt.executeUpdate();       java.util.Date toDay = new java.util.Date();       String localHost = "";       try {         localHost =  InetAddress.getLocalHost().toString();       }       catch (UnknownHostException ex) {         localHost = "localhost/127.0.0.1";       }       PreparedStatement logSt =  curConn.prepareStatement(logInsertString);       logSt.setQueryTimeout(60);       logSt.setString(1, toDay.toGMTString());       logSt.setLong(2, acctNo);       logSt.setString(3, "Transfert");       logSt.setLong(4, toAcctNo);       logSt.setFloat(5, -amount);       logSt.setString(6, localHost);       logSt.executeUpdate();       logSt.setString(1, toDay.toGMTString());       logSt.setLong(2, toAcctNo);       logSt.setString(3, "Received");       logSt.setLong(4, acctNo);       logSt.setFloat(5, amount);       logSt.setString(6, localHost);       logSt.executeUpdate();       curConn.commit();       curConn.setTransactionIsolation(Connection.TRANSACTION_NONE);       curSt.close();       logSt.close();     } catch (SQLException ex) { }   }   public void cashWithdraw(float amount)   {     String curUpdateString = "UPDATE accounts SET balance = balance + ?      WHERE acctno = ?";     String logInsertString = "INSERT INTO history (tdate, acctno,      typetransaction, otheracct, " +      " amount, ipaddress) VALUES (?, ?, ?, ?, ?, ?)";     try {       curConn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);       curConn.setAutoCommit(false);       PreparedStatement curSt = curConn.prepareStatement(curUpdateString);       curSt.setQueryTimeout(60);       curSt.setFloat(1, -amount);       curSt.setLong(2, acctNo);       curSt.executeUpdate();       java.util.Date toDay = new java.util.Date();       String localHost = "";       try {         localHost =  InetAddress.getLocalHost().toString();       }       catch (UnknownHostException ex) {         localHost = "localhost/127.0.0.1";       }       PreparedStatement logSt = curConn.prepareStatement(logInsertString);       logSt.setQueryTimeout(60);       logSt.setString(1, toDay.toGMTString());       logSt.setLong(2, acctNo);       logSt.setString(3, "Withdraw");       logSt.setLong(4, 0);       logSt.setFloat(5, -amount);       logSt.setString(6, localHost);       logSt.executeUpdate();       curConn.commit();       curConn.setTransactionIsolation(Connection.TRANSACTION_NONE);       curSt.close();       logSt.close();     } catch (SQLException ex) { }   } }
end example

NetBank.java

Listing 11-19 contains the source code for NetBank.java.

Listing 11-19: NetBank.java

start example
import java.awt.*; import java.sql.*; import java.lang.*; import java.util.*; import java.net.*; import marimba.gui.*; /**  * Title:        NetBank  * Description:  * Company:  * @author Bernard Van Haecke  * @version 1.0  */ public class NetBank extends java.applet.Applet {   Presentation presentation;   PlayerPanel player;   PlayerUtil util;   Account curAcct = null;   float curWithdrawAmount = 0;   public void init()   {     try {       presentation = Presentation.getPresentation(new        URL(getDocumentBase(), "netbank.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);      ((FolderWidget)util.getWidget("netbankFolder")).setTabMode(0);     // Initialize the clock thread     TimeT t;     t = new TimeT((TextBoxWidget)util.getWidget("clockLabel"));     t.start();   }   public void logoutRequest()   {     util.setText("welcomeAccountNo", "");     util.setText("welcomePinCode", "");     util.setText("balanceBalance", "");     util.setText("balanceHistory", "");     util.setText("transferAccountNo", "");     util.setText("transferAmount", "");     util.setText("statusBar", "");     curAcct.finalize();     util.gotoPage("welcomePage");   }   public void displayBalance()   {     String bal = (new Float(curAcct.checkAcctBalance())).toString();     util.setText("balanceBalance", bal);     Vector v = curAcct.checkHistory();     String s = "";     for (int i=0; i < v.size(); i++)       s = s + v.elementAt(i) + "\n";     util.setText("balanceHistory", s);     util.setText("statusBar", "");   }   public boolean handleEvent(Event evt)   {     if ((evt.id == Event.ACTION_EVENT) && (evt.target instanceof Widget)) {       Widget w = (Widget)evt.target;       String nm = w.getName();       System.out.println("Event: " + nm);       // The user has switched pages.       if (nm.equals("netbankFolder")) {         util.setText("statusBar", "");       }       if (nm.equals("loginPanelButton")) {         util.gotoPage("welcomePage");       }       if (nm.equals("balancePanelButton")) {         util.gotoPage("balancePage");       }       if (nm.equals("transferPanelButton")) {         util.gotoPage("transferPage");       }       if (nm.equals("cashPanelButton")) {         util.gotoPage("withdrawPage");       }       // The user has logged in.       if (nm.equals("welcomeOkButton")) {         Long acct = new Long(util.getText("welcomeAccountNo").trim());         String pin = util.getText("welcomePinCode").trim();         util.setText("statusBar", "Please wait...");         if ((acct.longValue() > 0) && (pin.length() > 0))         {           curAcct = new Account("jdbc:odbc:netbank", "dba", "javabank");           if (curAcct.verifyPinCode(acct.longValue(), pin))           {             Vector v = curAcct.checkAcctOwnerName(acct.longValue());             util.setText("statusBar", "Welcome " + v.elementAt(0) + ", " +              v.elementAt(1));             util.gotoPage("balancePage");             displayBalance();           }           else           {             // wrong info             util.setText("statusBar", "Account number or PIN invalid!");             logoutRequest();           }         }         else           util.setText("statusBar", "Please enter your account number first!");       }       // The user has clicked refresh       if (nm.equals("balanceRefreshButton")) {         util.setText("statusBar", "Please wait...");         displayBalance();       }       // The user has clicked OK to transfer money       if (nm.equals("transferYesButton")) {         Long acct = new Long(util.getText("transferAccountNo").trim());         Float amnt = new Float(util.getText("transferAmount").trim());         util.setText("statusBar", "Please wait...");         if ((acct.longValue() > 0) && (amnt.floatValue() > 0))         {           if (0 == 0) // should verify the transferAcctNo           {             curAcct.makeTransfer(acct.longValue(), amnt.floatValue());             Vector v = curAcct.checkAcctOwnerName(acct.longValue());             util.setText("statusBar", "Transfered to " + v.elementAt(0) + ", " +              v.elementAt(1));             util.setText("transferAccountNo", "");             util.setText("transferAmount", "");           } else           {             // acct does not exist in database             util.setText("statusBar", "INVALID ACCT NO OR PIN CODE!!!");           }         }         else           util.setText("statusBar", "Please enter the account number first!");       }       // Check the selection for cash       if (nm.equals("withdraw20")) {         curWithdrawAmount = 20;       } else if (nm.equals("withdraw100")) {         curWithdrawAmount = 100;       } else if (nm.equals("withdraw200")) {         curWithdrawAmount = 200;       } else if (nm.equals("withdraw1000")) {         curWithdrawAmount = 1000;       }       // The user has clicked withdraw       if (nm.equals("withdrawWithdrawButton")) {         util.setText("statusBar", "Please wait.");         curAcct.cashWithdraw(curWithdrawAmount);         util.setText("statusBar", "Please wait...");         ImageWidget img = (ImageWidget) util.getWidget("bankNote");         img.show();         for (int i = 0; i < 3050; i++)         {           img.reshape(60, 120 - (i / 10), 100, 10 + (i / 10));           img.repaint();         }         //img.hide();       }       // The user has clicked EndPage to log out       if (nm.equals("welcomeEndButton")) {         logoutRequest();         System.out.println("Ended");         return true;       }     }     return super.handleEvent(evt);   } } 
end example

TimeT.java

Listing 11-20 contains the source code for TimeT.java. This class is a simple thread that runs on its own. It updates the clock time on-screen every second. It doesn’t play any role related to JDBC.

Listing 11-20: TimeT.java

start example
import java.io.*; import java.lang.*; import java.util.*; import marimba.gui.*; /**  * Title:        TimeT  * Description:  * Company:  * @author Bernard Van Haecke  * @version 1.0  */ public class TimeT extends Thread {   private Thread GetTime;   private boolean bRun;   TextBoxWidget tbw;   public TimeT(TextBoxWidget t) {     tbw = t;   }   public void start() {     bRun = true;     GetTime = new Thread(this);     GetTime.start();   }   public void run() {     while(bRun)     {       try {GetTime.sleep(1000);}       catch (InterruptedException e) { }       String today = (new Date()).toString();       tbw.setText(today);     }   } } 
end example

The Data Definition Language for the Bank of Java applet

Some keywords for this example are Transact-SQL. You may want to modify the DDL (Data Definition Language) and DML (Data Manipulation Language) for your DBMS. Listing 11-21 shows how to create the tables for this example.

Listing 11-21: DDL to Create the Tables for the Bank of Java Applet

start example
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% % Create tables %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% CREATE TABLE "accounts" ( "acctno"                        integer NULL, "pincode"                       varchar(50) NULL, "ownerno"                       integer NULL, "datecreated"                   varchar(50) NULL, "balance"                       float NULL, ); CREATE TABLE "clients" ( "ownerno"                       integer NULL, "name"                          varchar(50) NULL, "address"                       varchar(50) NULL, ); CREATE TABLE "history" ( "tdate"                         varchar(50) NULL, "acctno"                        integer NULL, "typetransaction"               varchar(20) NULL, "otheracct"                     integer NULL, "amount"                        float NULL,"                     varchar(50) NULL,);"/> INSERT INTO "accounts" ("acctno","pincode","ownerno","datecreated","balance") VALUES (‘1’,’1111’,’1’,’’,’992800’); INSERT INTO "accounts" ("acctno","pincode","ownerno","datecreated","balance") VALUES (‘2’,’2222’,’2’,’’,’1257450’); INSERT INTO "accounts" ("acctno","pincode","ownerno","datecreated","balance") VALUES (‘3’,’3333’,’3’,’’,’320700’); INSERT INTO "accounts" ("acctno","pincode","ownerno","datecreated","balance") VALUES (‘4’,’4444’,’4’,’’,’8900750’); INSERT INTO "accounts" ("acctno","pincode","ownerno","datecreated","balance") VALUES (‘5’,’5555’,’5’,’’,’-840’); INSERT INTO "accounts" ("acctno","pincode","ownerno","datecreated","balance") VALUES (‘6’,’6666’,’6’,’’,’999999995904’); INSERT INTO "clients" ("ownerno", "name", "address") VALUES (‘1’,’Bernard Van Haecke’,’Brussels, 1000’); INSERT INTO "clients" ("ownerno", "name", "address") VALUES (‘2’,’John Doe’,’Imola Circuit, KM83’); INSERT INTO "clients" ("ownerno", "name", "address") VALUES (‘3’,’Jane Doe’,’Imola Circuit, KM83’); INSERT INTO "clients" ("ownerno", "name", "address") VALUES (‘4’,’Santa Claus’,’North Pole, 1’); INSERT INTO "clients" ("ownerno", "name", "address") VALUES (‘5’,’Little Duke’,’Java Island, 1’); INSERT INTO "clients" ("ownerno", "name", "address") VALUES (‘6’,’The Bank’,’Downtown LA’); INSERT INTO "history"  ("tdate","acctno","typetransaction","otheracct","amount","ipaddress") VALUES (‘23 Oct 1996 20:30:15 GMT’,’1’,’Transfert’,’4’,  ‘-1000’,’localhost/127.0.0.1’); INSERT INTO "history"   ("tdate","acctno","typetransaction","otheracct","amount","ipaddress") VALUES (‘23 Oct 1996 20:30:15 GMT’,’4’,’Received’,’1’,  ‘1000’,’localhost/127.0.0.1’); INSERT INTO "history"   ("tdate","acctno","typetransaction","otheracct","amount","ipaddress") VALUES (‘24 Oct 1996 21:18:43 GMT’,’5’,’Withdraw’,’0’,  ‘-20’,’localhost/127.0.0.1’);
end example



JDBC 3. 0. JAVA Database Connectivity
JDBC 3: Java Database Connectivity
ISBN: 0764548751
EAN: 2147483647
Year: 2002
Pages: 148

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net