/ ** Copyright (c) 2000 David Flanagan. All rights reserved. * This code is from the book jsp servlet ejb 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 JSP servlet ejb .sql. *; import JSP servlet ejb .io. *; / *** a general-purpose sql interpreter program, ** / public class executesql {public STATIC void main (string [] args) {connection conn = null; // o = = null, user = "" "" "", password = ""; // parse all the command -Line ArgumentsFor (int N = 0; n
Squeries and executing themwhile ("sql>"); // prompt the userSystem.out.flush (); // make the prompt Appear now.String SQL = in.readline (); // Get a line of infut from user // quit when the user type "quit" .IF ((SQL == null) || SQL.Equals ("quit")) Break; // ignore blank linesif (SQL.Length () == 0) Continue; // Now, Execute the user's line of sql and display result.try {// we don't know if this is a query or some knowledge () INSTEAD OF EXECUTEQUERY () // or executeUpdate () ife qRUE IS true, IT WAS / / A Query, Else An update.Boolean Status = S.Execute (SQL); // Some Complex SQL Queries Can Return More Than One ONE Set // of results, so loop unsults {if (status) {// it is a query and return a resultsetResultset (); // Get ResultsPrintResultStable (RS, System.out); // Display theme} else {// if the sql command That Was Executed Was Some // Kind of Update Rather Than A Query, Then IT // Doesn't Retu Rn a resultset. Instead, We Just // Print The Numr of Rows That Were Affected.int Numupdates = S.GetupdateCount (); System.out.println ("OK." Numupdates "Rows Affected.");} / / NOW Go See if there is estats = s.getmoreresults ();} | = -1);} ///- // / If a SQLException is thrown, display an error message.// Note that SQLExceptions can have a general message and a // DB-specific message returned by getSQLState () catch (SQLException e) {System.err.println ( "SQLException: " E.GetMessage () ": " E.GETSQLSTATE ());
} // Each time through this loop, check to see if there were any // warnings Note that there can be a whole chain of warnings.finally {// print out any warnings that occurredSQLWarning w;. For (w = conn.getWarnings (); w! = null; w = w.Getnextwarning ()) System.err.println ("Warning:" w.getMessage () ": w.getsqlstate ());}}} // handle exceptions that occur during argument parsing, database // connection setup, etc. For SQLExceptions, print the details.catch (Exception e) {System.err.println (e); if (e instanceof SQLException) System.err.println ( " SQL State: " ((sqlexception) e); system.err.println (" USAGE: JSP Servlet EJB EXECUTESQL [-d ] " " [- u ] [-p ] ");} // Be sure to always close the database connection when we exit, // whether we exit because the user types 'quit' or because of an // exception thrown while setting things up . C i i i i i i i i i i i i i {Conn (Exception E) {}}} / *** This method attempts to output the contents of a resultset in a * textual table. IT Relies on The ResultSetMetadata Class, But A Fair * bit of the code is simple string manipulation ** / static void printResultsTable (ResultSet rs, OutputStream output) throws SQLException {// Set up the output streamPrintWriter out = new PrintWriter (output);. // Get some "meta data" (column names, ETC.) About the resultSResultSetMetadata metadata = rs.getMetadata (); // variables to hold important data about the table to be displayedint numcols = metadata.getColumnCount ();
//how many columnString [] labels = new string [Numcols]; // the colorths = new int [numcolls]; // the width of eachint [] colpos = new int [numcols]; // start position Of Eachint LineWidth; // Total Width of Table // Figure Out How Wide The Column Are, Where Each Row of The Table Will BE, ETC.LineWidth = 1; // for the initial '| '.for (int i = 0; i 500) size = 30; // Don't allow unreasonable sizesint labelsize = labels [i] .length (); if (labelsize> size) size = labelsize; COLWIDTHS [i] = size 1; // save the colorn the size linewidth = C olwidths [i] 2; // increment total size} // Create a horizontal divider line we use in the table.// Also create a blank line that is the initial value of each // line of the tableStringBuffer divider = new StringBuffer (linewidth); stringbuffer blankline = new stringbuffer (linewidth); for (int i = 0; i
// Begin the table output with a divider lineout.println (divider); // The next line of the table contains the column labels.// Begin with a blank line, and put the column names and column // divider characters "| "INTO it. Overwrite () is defined be.StringBuffer line = new stringbuffler (BLANKLINE.TOSTRING ()); line.setcharat (0, '|'); for (int i = 0; i Blen) Slen = Blen-Pos; // does it fit? for (int i = 0; i
============================================================================================================================================================================================================= ========== / ** Copyright (C) 2000 David Flanagan. All rights reserved. * This code is from the book JSP servlet EJB 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), * Visithttp://www.davidflanagan.com/javaexamples2
. * / Package com.davidflanagan.examples.sql; import jsp servlet ejb .sql *;. Import jsp servlet ejb .util.Properties; / *** This class uses the DatabaseMetaData class to obtain information about * the database, the JDBC . driver, and the tables in the database, or about * the columns of a named table ** / public class GetDBInfo {public static void main (String [] args) {Connection c = null; // The JDBC connection to the database servertry {// Look for the properties file DB.props in the same directory as // this program It will contain default values for the various // parameters needed to connect to a databaseProperties p = new Properties ();. try {p. load (GetDBInfo.class.getResourceAsStream ( "DB.props"));} catch (Exception e) {} // Get default values from the properties fileString driver = p.getProperty ( "driver"); // Driver class nameString server = P.GetProperty ("Server", "); // jdbc url for serverstring user = p.getProperty (" user ","); // dB user namestring password = p.getProperty ("password", "" ); / / db Password // THESE VARIABLES DON't Have DefaultsString Database = Null; // The DB Name (Appended to Server URL) String Table = Null; // The Optional Name of a Table in The db // Parse The Command-Line Args to override the default values Abovefor (int i = 0; i Else IF (args [i] .Equals ("- s")) Server = args [ i]; // - s else if (args [i] .Equals ("- u") User = args [ i]; // - u else IF (args [i] .Equals ("- p")) Password = args [ i];
Else if (Database == NULL) Database = args [i]; // else if (table == null) Table = args [i]; //
else throw new IllegalGumentexception ("Unknown Argument:" Args [I]);} // make Sure That at Least a Server OR a Database Were Specified. // IF NOT, We Have No IDEA What To Connect To, And Cannot Continue.if ((Server.Length () = = 0) && (Database.Length () == 0)) "No Database Specified."); // Load The DB Driver, IF ANY WAS Specified.if (Driver! = NULL) Class.Forname ( driver); // Now attempt to open a connection to the specified database on // the specified server, using the specified name and passwordc = DriverManager.getConnection (server database, user, password); // Get the DatabaseMetaData object for the . connection This is the // object that will return us all the data we're interested in hereDatabaseMetaData md = c.getMetaData (); // Display information about the server, the driver, etc.System.out.println ( "DBMS : " Md.GetDatabaseProductName () " " Md.getDatabaseProductVersion ()); System.out.Println ("JDBC Driver:" Md.GetDriverName () "" md.getdriverversion ()); System.out.Println ("Database:" md.geturl ))); System.out.println ("User:" md.getusername ()); // now, if the user did Not Specify A Table, Then Display a list of // all tables defined in the named Database. Note. Note That Tables Are // Returned In A ResultSet, Just Like Query Results Are.if (Table == Null) {System.out.Println ("Tables:"); ResultSet R = Md.gettables (",", " %, NULL;
While (R.Next ()) System.out.println ("/ T" R.GetString (3));} // OtherWise, List All Column of the Specified Table./Again, Information About The ColumnS is Returned IN a results {system.out.println ("Column of" Table ":"); ResultSet r = md.getColumns (",", ","% "); while (r.next ()) System.out.println ("/ t" r.getstring (4) ":" r.getstring (6));}} // print an error message if anything goes wrong.catch (exception e) {system .rr.println (e); if (e instanceof sqlexception) system.err.println ((SQLException) ((SQLEXCEPTION) E); System.err.Println ("Usage: JSP Servlet EJB Getdbinfo [-D ] / n " " / t [-u ] ");} // always remember to close the connection Object WHEN WE 'RE DONE! FINALLY {Try {c.close ();} Catch (Exception E) {}}}} ========================= ================================================= / ** CopyRight (C) 2000 David Flanagan. All Rights Reserved. * This Code Is from The Book JSP Servlet EJB EXAMPLES IN A NUTSHELL, 2nd Edit ion. * 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 (recomment), * Visithttp://www.davidflanagan.com/javaexamples2
* / package com.davidflanagan.examples.sql; import JSP servlet ejb .rmi. *; import JSP servlet ejb .rmi.server. *; import jsp servlet ejb .rmi.registry. *; import JSP servlet ejb .sql. *; import jsp servlet ejb .io *;. import jsp servlet ejb .util *;. import jsp servlet ejb .util.Date; // import explicitly to disambiguate from jsp servlet ejb .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 UnicastRemoteObjectimplements RemoteBank {Connection db;. // THE CONNECTION TO The Database That Stores Account Info / ** The Constructor. Just Save The Database Connection Object Away * / Public RemoteDbbankserve r (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 nameStatement s = null; try {s = db.createstatement (); S.ExecuteQuery ("SELECT * ACCUNTS 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 NNSERT ANSERT AN OF ANSTME OF IN THE // 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))") .cuteUpdate ("INSERT INTO" "" " " New Date () ")"); // and if we've been successful so far, commit these Updates, Sa FAR, COMMIT THESE UPDATES, // ending the atomic transaction All the methods below also use // this atomic transaction commit / rollback schemedb.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 BankingExceptionthrow new BankingException ( "SQLException: E.GetMessage () ": E.GetsqlState ());} // no matter what happens, don't forget to close the db statementFinalLinally {try {s.close ();} catch (Exception E ) {}}} / ** * this convenience method CHECKS WHETHER THE NAME AND Password Match * an existing account. I F 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 Tables.executeUpdate ("delete from accounts" "where name = '" name "" "and password ='" password ""); // and Drop the Transaction History Table for this Accounts.executeUpdate ("DROP TABLE" Name "_history"); db.commit ();} catch (sqlexception e) {try {db.rollback ();} catch Exception E2) {} throw new baningException ("SQLException:" E.GetMessage () ":" E.GETSQLSTATE ();} finally {try {s.close ();} catch (Exception E) {} } // finally, return what for balance remained in the accountreturn 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 balances.executeUpdate ("Update Accounts" "Set Balance =" Balance Money.Amount " " Where Name = '" Name " "and password =' " Password " ');
// add a row to the transaction historys.executeUpdate ("INSERT INTO" Name "_history" "VALUES ('Deposite" Money.Amount "AT" New Date () ")"); DB .commit ();} catch (sqlexception e) {Try {db.rollback ();} catch (Exception E2) {} throw new baningException ("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
} / ** Return the balance of the specified account * / public synchronized int getBalance (String name, String password) throws RemoteException, BankingException {int balance; try {// Get the balancebalance = verify (name, password); // Commit THE transactionsdb.commit ();} catch (sqlexception e) {Try {db.rollback ();} catch (Exception E2) {} throw new bankingException ("SQLException:" E.getMessage () ": E .getSQLState ());} // Return the balancereturn 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, evening we don't // care what the current balance is.Verify (name, password); s = db.createstatement (); // Request everyhes.executeQuery ("Select * from" name "_history"); // Get the results of the query and put the in a VectorResultset R = S.Getre Sultset (); whene (r.next ()) list.add (r.GetString (1)); // commit the transactionsDb.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 profment (); try {p.load (new fileinputstream (args [0]));} catch (exception e) {try {p.load (New fileInputStream ("Bankdb.props")) } catch (Exception E2) {}} // the bandb.props file (or file specified on the command line // must contain "" 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 data base driver classClass.forName (driver); // Connect to the database that stores our accountsConnection db = DriverManager.getConnection (database, user, password); // Configure the database to allow multiple queries and updates // to be grouped into atomic transactionsdb.setAutoCommit (false); db.setTransactionIsolation (Connection.TRANSACTION_READ_COMMITTED); // Create a server object that uses our database connectionRemoteDBBankServer bank = new RemoteDBBankServer (db); // Read a system property to figure out how to name this server. // use "second.string name = system.getproperty (" Bankname "as the default.string name = system.getproperty
