学习代码

/*
* 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; // Our JDBC connection to the database server
try {
String driver = null, url = null, user = "", password = "";

// Parse all the command-line arguments
for(int n = 0; n < args.length; n++) {
if (args[n].equals("-d")) driver = args[++n];
else if (args[n].equals("-u")) user = args[++n];
else if (args[n].equals("-p")) password = args[++n];
else if (url == null) url = args[n];
else throw new IllegalArgumentException("Unknown argument.");
}

// The only required argument is the database URL.
if (url == null)
throw new IllegalArgumentException("No database specified");

// If the user specified the classname for the DB driver, load
// that class dynamically. This gives the driver the opportunity
// to register itself with the DriverManager.
if (driver != null) Class.forName(driver);

// Now open a connection the specified database, using the
// user-specified username and password, if any. The driver
// manager will try all of the DB drivers it knows about to try to
// parse the URL and connect to the DB server.
conn = DriverManager.getConnection(url, user, password);

// Now create the statement object we'll use to talk to the DB
Statement s = conn.createStatement();

// Get a stream to read from the console
BufferedReader in =
new BufferedReader(new InputStreamReader(System.in));

// Loop forever, reading the user's queries and executing them
while(true) {
System.out.print("sql> "); // prompt the user
System.out.flush(); // make the prompt appear now.
String sql = in.readLine(); // get a line of input from user

// Quit when the user types "quit".
if ((sql == null) || sql.equals("quit")) break;

// Ignore blank lines
if (sql.length() == 0) continue;

// Now, execute the user's line of SQL and display results.
try {
// We don't know if this is a query or some kind of
// update, so we use execute() instead of executeQuery()
// or executeUpdate() If the return value is true, it was
// a query, else an update.
boolean status = s.execute(sql);

// Some complex SQL queries can return more than one set
// of results, so loop until there are no more results
do {
if (status) { // it was a query and returns a ResultSet
ResultSet rs = s.getResultSet(); // Get results
printResultsTable(rs, System.out); // Display them
}
else {
// If the SQL command that was executed was some
// kind of update rather than a query, then it
// doesn't return a ResultSet. Instead, we just
// print the number of rows that were affected.
int numUpdates = s.getUpdateCount();
System.out.println("Ok. " + numUpdates +
" rows affected.");
}

// Now go see if there are even more results, and
// continue the results display loop if there are.
status = s.getMoreResults();
} while(status || s.getUpdateCount() != -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 occurred
SQLWarning 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).getSQLState());
System.err.println("Usage: jsp servlet ejb ExecuteSQL [-d <driver>] " +
"[-u <user>] [-p <password>] <database URL>");
}

// 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. Closing this connection
// also implicitly closes any open statements and result sets
// associated with it.
finally {
try { conn.close(); } catch (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 stream
PrintWriter out = new PrintWriter(output);

// Get some "meta data" (column names, etc.) about the results
ResultSetMetaData metadata = rs.getMetaData();

// Variables to hold important data about the table to be displayed
int numcols = metadata.getColumnCount(); // how many columns
String[] labels = new String[numcols]; // the column labels
int[] colwidths = new int[numcols]; // the width of each
int[] colpos = new int[numcols]; // start position of each
int linewidth; // total width of table

// Figure out how wide the columns are, where each one begins,
// how wide each row of the table will be, etc.
linewidth = 1; // for the initial '|'.
for(int i = 0; i < numcols; i++) { // for each column
colpos[i] = linewidth; // save its position
labels[i] = metadata.getColumnLabel(i+1); // get its label
// Get the column width. If the db doesn't report one, guess
// 30 characters. Then check the length of the label, and use
// it if it is larger than the column width
int size = metadata.getColumnDisplaySize(i+1);
if (size == -1) size = 30; // Some drivers return -1...
if (size > 500) size = 30; // Don't allow unreasonable sizes
int labelsize = labels[i].length();
if (labelsize > size) size = labelsize;
colwidths[i] = size + 1; // save the column the size
linewidth += colwidths[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 table
StringBuffer divider = new StringBuffer(linewidth);
StringBuffer blankline = new StringBuffer(linewidth);
for(int i = 0; i < linewidth; i++) {
divider.insert(i, '-');
blankline.insert(i, " ");
}
// Put special marks in the divider line at the column positions
for(int i=0; i<numcols; i++) divider.setCharAt(colpos[i]-1,'+');
divider.setCharAt(linewidth-1, '+');

// Begin the table output with a divider line
out.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 below.
StringBuffer line = new StringBuffer(blankline.toString());
line.setCharAt(0, '|');
for(int i = 0; i < numcols; i++) {
int pos = colpos[i] + 1 + (colwidths[i]-labels[i].length())/2;
overwrite(line, pos, labels[i]);
overwrite(line, colpos[i] + colwidths[i], " |");
}

// Then output the line of column labels and another divider
out.println(line);
out.println(divider);

// Now, output the table data. Loop through the ResultSet, using
// the next() method to get the rows one at a time. Obtain the
// value of each column with getObject(), and output it, much as
// we did for the column labels above.
while(rs.next()) {
line = new StringBuffer(blankline.toString());
line.setCharAt(0, '|');
for(int i = 0; i < numcols; i++) {
Object value = rs.getObject(i+1);
if (value != null)
overwrite(line, colpos[i] + 1, value.toString().trim());
overwrite(line, colpos[i] + colwidths[i], " |");
}
out.println(line);
}

// Finally, end the table with one last divider line.
out.println(divider);
out.flush();
}

/** This utility method is used when printing the table of results */
static void overwrite(StringBuffer b, int pos, String s) {
int slen = s.length(); // string length
int blen = b.length(); // buffer length
if (pos+slen > blen) slen = blen-pos; // does it fit?
for(int i = 0; i < slen; i++) // copy string into buffer
b.setCharAt(pos+i, s.charAt(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),
* visit http://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 server
try {
// 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 database
Properties p = new Properties();
try { p.load(GetDBInfo.class.getResourceAsStream("DB.props")); }
catch (Exception e) {}

// Get default values from the properties file
String driver = p.getProperty("driver"); // Driver class name
String server = p.getProperty("server", ""); // JDBC URL for server
String user = p.getProperty("user", ""); // db user name
String password = p.getProperty("password", ""); // db password

// These variables don't have defaults
String 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 above
for(int i = 0; i < args.length; i++) {
if (args[i].equals("-d")) driver = args[++i]; //-d <driver>
else if (args[i].equals("-s")) server = args[++i];//-s <server>
else if (args[i].equals("-u")) user = args[++i]; //-u <user>
else if (args[i].equals("-p")) password = args[++i];
else if (database == null) database = args[i]; // <dbname>
else if (table == null) table = args[i]; // <table>
else throw new IllegalArgumentException("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))
throw new IllegalArgumentException("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 password
c = 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 here
DatabaseMetaData 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 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 columns of the specified table.
// Again, information about the columns is returned in a ResultSet
else {
System.out.println("Columns of " + table + ": ");
ResultSet r = md.getColumns("", "", table, "%");
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.err.println(e);
if (e instanceof SQLException)
System.err.println(((SQLException)e).getSQLState());
System.err.println("Usage: jsp servlet ejb GetDBInfo [-d <driver] " +
"[-s <dbserver>]/n" +
"/t[-u <username>] [-p <password>] <dbname>");
}
// 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 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 .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.Date
import 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: jsp servlet ejb [-Dbankname=<name>] " +
"com.davidflanagan.examples.sql.RemoteDBBankServer " +
"[<dbpropsfile>]");
System.exit(1);
}
}
}