/* * Copyright (c) 2000 David Flanagan.  All rights reserved. * This code is from the book Java Examples in a Nutshell, 2nd Edition. * It is provided AS-IS, WITHOUT ANY WARRANTY either expressed or implied. * You may study, use, and modify it for any non-commercial purpose. * You may distribute it non-commercially as long as you retain this notice. * For a commercial use license, or to purchase the book (recommended), * visit http://www.davidflanagan.com/javaexamples2. */package com.davidflanagan.examples.sql;import java.rmi.*;import java.rmi.server.*;import java.rmi.registry.*;import java.sql.*;import java.io.*;import java.util.*;import java.util.Date; // import explicitly to disambiguate from java.sql.Dateimport com.davidflanagan.examples.rmi.Bank.*;  // Import inner classes of Bank/** * This class is another implementation of the RemoteBank interface. * It uses a database connection as its back end, so that client data isn't * lost if the server goes down.  Note that it takes the database connection * out of "auto commit" mode and explicitly calls commit() and rollback() to * ensure that updates happen atomically. **/public class RemoteDBBankServer extends UnicastRemoteObject    implements RemoteBank{    Connection db;   // The connection to the database that stores account info        /** The constructor.  Just save the database connection object away */    public RemoteDBBankServer(Connection db) throws RemoteException {         this.db = db;    }        /** Open an account */    public synchronized void openAccount(String name, String password)	throws RemoteException, BankingException    {        // First, check if there is already an account with that name        Statement s = null;        try {             s = db.createStatement();            s.executeQuery("SELECT * FROM accounts WHERE name='" + name + "'");            ResultSet r = s.getResultSet();            if (r.next()) throw new BankingException("Account name in use.");	                // If it doesn't exist, go ahead and create it Also, create a            // table for the transaction history of this account and insert an            // initial transaction into it.            s = db.createStatement();            s.executeUpdate("INSERT INTO accounts VALUES ('" + name + "', '" +			    password + "', 0)");            s.executeUpdate("CREATE TABLE " + name + 			    "_history (msg VARCHAR(80))");            s.executeUpdate("INSERT INTO " + name + "_history " +			    "VALUES ('Account opened at " + new Date() + "')");	                // And if we've been successful so far, commit these updates,            // ending the atomic transaction.  All the methods below also use            // this atomic transaction commit/rollback scheme            db.commit();        }        catch(SQLException e) {            // If an exception was thrown, "rollback" the prior updates,            // removing them from the database.  This also ends the atomic            // transaction.            try { db.rollback(); } catch (Exception e2) {}            // Pass the SQLException on in the body of a BankingException            throw new BankingException("SQLException: " + e.getMessage() + 				       ": " + e.getSQLState());        }        // No matter what happens, don't forget to close the DB Statement        finally { try { s.close(); } catch (Exception e) {} }    }        /**      * This convenience method checks whether the name and password match     * an existing account.  If so, it returns the balance in that account.     * If not, it throws an exception.  Note that this method does not call     * commit() or rollback(), so its query is part of a larger transaction.     **/    public int verify(String name, String password) 	throws BankingException, SQLException    {        Statement s = null;        try {            s = db.createStatement();            s.executeQuery("SELECT balance FROM accounts " +			   "WHERE name='" + name + "' " +			   "  AND password = '" + password + "'");            ResultSet r = s.getResultSet();            if (!r.next())                throw new BankingException("Bad account name or password");            return r.getInt(1);        }        finally { try { s.close(); } catch (Exception e) {} }    }        /** Close a named account */    public synchronized FunnyMoney closeAccount(String name, String password)	throws RemoteException, BankingException    {        int balance = 0;        Statement s = null;        try {            balance = verify(name, password);            s = db.createStatement();            // Delete the account from the accounts table            s.executeUpdate("DELETE FROM accounts " + 			    "WHERE name = '" + name + "' " +			    "  AND password = '" + password + "'");            // And drop the transaction history table for this account            s.executeUpdate("DROP TABLE " + name + "_history");            db.commit();        }        catch (SQLException e) {            try { db.rollback(); } catch (Exception e2) {}            throw new BankingException("SQLException: " + e.getMessage() + 				       ": " + e.getSQLState());        }        finally { try { s.close(); } catch (Exception e) {} }	        // Finally, return whatever balance remained in the account        return new FunnyMoney(balance);    }        /** Deposit the specified money into the named account */    public synchronized void deposit(String name, String password, 				     FunnyMoney money) 	throws RemoteException, BankingException    {        int balance = 0;         Statement s = null;        try {            balance = verify(name, password);            s = db.createStatement();            // Update the balance            s.executeUpdate("UPDATE accounts " +			    "SET balance = " + balance + money.amount + " " +			    "WHERE name='" + name + "' " +			    "  AND password = '" + password + "'");            // Add a row to the transaction history            s.executeUpdate("INSERT INTO " + name + "_history " + 			    "VALUES ('Deposited " + money.amount + 			    " at " + new Date() + "')");            db.commit();        }        catch (SQLException e) {            try { db.rollback(); } catch (Exception e2) {}            throw new BankingException("SQLException: " + e.getMessage() + 				       ": " + e.getSQLState());        }        finally { try { s.close(); } catch (Exception e) {} }    }        /** Withdraw the specified amount from the named account */    public synchronized FunnyMoney withdraw(String name, String password, 					    int amount)	throws RemoteException, BankingException    {        int balance = 0;        Statement s = null;        try {            balance = verify(name, password);            if (balance < amount)		throw new BankingException("Insufficient Funds");            s = db.createStatement();            // Update the account balance            s.executeUpdate("UPDATE accounts " +			    "SET balance = " + (balance - amount) + " " +			    "WHERE name='" + name + "' " +			    "  AND password = '" + password + "'");            // Add a row to the transaction history            s.executeUpdate("INSERT INTO " + name + "_history " + 			    "VALUES ('Withdrew " + amount + 			    " at " + new Date() + "')");            db.commit();        }        catch (SQLException e) {            try { db.rollback(); } catch (Exception e2) {}            throw new BankingException("SQLException: " + e.getMessage() + 				       ": " + e.getSQLState());        }        finally { try { s.close(); } catch (Exception e) {} }	        return new FunnyMoney(amount);    }        /** Return the balance of the specified account */    public synchronized int getBalance(String name, String password)	throws RemoteException, BankingException    {        int balance;        try {            // Get the balance            balance = verify(name, password);            // Commit the transaction            db.commit();        }        catch (SQLException e) {            try { db.rollback(); } catch (Exception e2) {}            throw new BankingException("SQLException: " + e.getMessage() + 				       ": " + e.getSQLState());        }        // Return the balance        return balance;    }        /** Get the transaction history of the named account */    public synchronized List getTransactionHistory(String name, 						   String password)	throws RemoteException, BankingException    {        Statement s = null;        List list = new ArrayList();        try {            // Call verify to check the password, even though we don't             // care what the current balance is.            verify(name, password);            s = db.createStatement();            // Request everything out of the history table            s.executeQuery("SELECT * from " + name + "_history");            // Get the results of the query and put them in a Vector            ResultSet r = s.getResultSet();            while(r.next()) list.add(r.getString(1));            // Commit the transaction            db.commit();        }        catch (SQLException e) {            try { db.rollback(); } catch (Exception e2) {}            throw new BankingException("SQLException: " + e.getMessage() + 				       ": " + e.getSQLState());        }        finally { try { s.close(); } catch (Exception e) {} }        // Return the Vector of transaction history.        return list;    }        /**     * This main() method is the standalone program that figures out what     * database to connect to with what driver, connects to the database,     * creates a RemoteDBBankServer object, and registers it with the registry,     * making it available for client use     **/    public static void main(String[] args) {        try {            // Create a new Properties object.  Attempt to initialize it from            // the BankDB.props file or the file optionally specified on the             // command line, ignoring errors.            Properties p = new Properties();            try { p.load(new FileInputStream(args[0])); }            catch (Exception e) {                try { p.load(new FileInputStream("BankDB.props")); }                catch (Exception e2) {}            }	                // The BankDB.props file (or file specified on the command line)            // must contain properties "driver" and "database", and may            // optionally contain properties "user" and "password".            String driver = p.getProperty("driver");            String database = p.getProperty("database");            String user = p.getProperty("user", "");            String password = p.getProperty("password", "");	                // Load the database driver class            Class.forName(driver);	                // Connect to the database that stores our accounts            Connection db = DriverManager.getConnection(database,							user, password);	                // Configure the database to allow multiple queries and updates            // to be grouped into atomic transactions            db.setAutoCommit(false);            db.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);	                // Create a server object that uses our database connection            RemoteDBBankServer bank = new RemoteDBBankServer(db);	                // Read a system property to figure out how to name this server.            // Use "SecondRemote" as the default.            String name = System.getProperty("bankname", "SecondRemote");	                // Register the server with the name            Naming.rebind(name, bank);	                // And tell everyone that we're up and running.            System.out.println(name + " is open and ready for customers.");        }        catch (Exception e) {            System.err.println(e);            if (e instanceof SQLException)                 System.err.println("SQL State: " +				   ((SQLException)e).getSQLState());            System.err.println("Usage: java [-Dbankname=<name>] " +		        "com.davidflanagan.examples.sql.RemoteDBBankServer " +			       "[<dbpropsfile>]");            System.exit(1);        }    }}