Last Updated
// Database class to implement stock market access using MsqlJava
package COM.xinit.demon.co.uk.3tier.database;
import msql.*;
import java.util.Vector;
public class Database implements DBAccess {
private Msql msql; //Database connection
private MsqlResult result; //Holds return values
private static String database = "StockMarket"; //name of database to connect to
/**
constructor for database object - establishes connection to DB server
@exception msql.MsqlException database failure generic exception
@param String serverName Name of database server
@return None
*/
public Database (String serverName) throws MsqlException {
msql = new Msql();
msql.Connect ( serverName );
msql.SelectDB ( database );
}
/**
close database object
@exception msql.MsqlException database failure generic exception
@return None
*/
public void close () throws MsqlException {
msql.Close();
}
/**
Gets all the stock records from the Stock table
@returns An array of StockRec
*/
public StockRec[] getAllStocks()
{
try
{
Vector list = new Vector(10,10);
MsqlResult result = msql.Query("SELECT * FROM Stock");
int numres = 0;
String row[];
while ((row = result.FetchRow()) != null) {
StockRec str = new StockRec((String)row[0],
Float.valueOf(row[1]).floatValue());
numres++;
list.addElement(str);
}
StockRec[] allstocks = new StockRec[ numres ];
list.copyInto (allstocks);
return allstocks;
} catch (MsqlException e)
{
System.err.println( "There are no stocks to list" );
}
return null;
}
/**
Gets the number of shares of type symbol owned by the customer
@param ssn a String with the ssn
@param symbol a String with the shares identifier
@returns the number of shares owned
@exception database.RecordNotFoundException if ssn not in Customer table
*/
private int getNumberShares(String ssn, String symbol) throws RecordNotFoundException {
MsqlResult result = null;
int qtyRet=0;
String row[];
String qry = null;
qry = "SELECT Shares.quantity FROM Shares WHERE Shares.ssn = '" + ssn + "' AND Shares.symbol = '" + symbol + "'";
// System.err.println("Query: " + qry);
try {
result = msql.Query(qry);
}
catch (MsqlException e) {
System.err.println("getNumberShares sql query failed");
System.err.println("Query: " + qry);
System.err.println("Exception: " + e.getMessage());
}
// System.err.println("num Rows " + result.NumRows());
if (result.NumRows() < 1) {
throw new RecordNotFoundException();
}
else {
row=result.FetchRow();
qtyRet = (new Integer(row[0])).intValue(); // should only return but this just sets to last
}
// System.err.println("qtyRet " + qtyRet);
return qtyRet;
}
/**
Updates the Shares table with new quantity of shares
@param ssn a String with the ssn
@param symbol a String with the shares identifier
@param quantity the number of shares to sell
@exception database.RecordNotFoundException if ssn not in Customer table
@exception database.InvalidTransactionException if ssn does not own enough shares of given stock.
*/
public void sellShares(String ssn, String symbol, int quantity) throws RecordNotFoundException, InvalidTransactionException {
MsqlResult result = null;
int tmpQty=0;
String qry;
Integer tmpQty2;
tmpQty = getNumberShares(ssn, symbol) - quantity; // Calculate new value
if (tmpQty < 0) {
throw new InvalidTransactionException();
}
tmpQty2 = new Integer(tmpQty);
qry = "UPDATE Shares SET Shares.quantity=" + tmpQty2.toString() + " WHERE Shares.ssn = '" + ssn + "' AND Shares.symbol = '" + symbol + "'";
try {
result = msql.Query(qry);
}
catch (MsqlException e) {
System.err.println("sellShares sql query failed: ");
System.err.println("Query: " + qry);
System.err.println("Exception " + e.getMessage());
}
}
/**
Sells all shares of the specified type for the customer
@param ssn a String with the ssn of the Customer
@param symbol a String with the shares identifier
@exception database.RecordNotFoundException if ssn not in Customer table
@exception database.InvalidTransactionException if ssn does not have any shares of given stock.
*/
public void sellShares(String ssn, String symbol) throws RecordNotFoundException, InvalidTransactionException {
int tmpQty = 0;
tmpQty = getNumberShares(ssn, symbol);
if (tmpQty < 1) {
throw new InvalidTransactionException();
}
sellShares(ssn, symbol, tmpQty);
}
/**
Either adds a new entry to the Shares table or updates an existing entry.
@param ssn a String with the ssn for the customer
@param symbol a String with the shares identifier
@param quantity the number of shares to buy
@exception database.RecordNotFoundException if ssn not in Customer table
*/
public void buyShares(String ssn, String symbol, int quantity) throws RecordNotFoundException {
MsqlResult result = null;
String qry;
Integer tmpQty2 = null;
int tmpQty = 0;;
if (!ssnExists(ssn)) {
throw new RecordNotFoundException();
}
// Check to see is the customer has any shares of this type
try {
tmpQty = getNumberShares(ssn, symbol);
// Shares already exist so UPDATE
tmpQty2 = new Integer(tmpQty + quantity);
qry = "UPDATE Shares SET Shares.quantity=" + tmpQty2.toString() + " WHERE Shares.ssn = '" + ssn + "' AND Shares.symbol = '" + symbol + "'";
}
catch (RecordNotFoundException e) {
tmpQty2 = new Integer(quantity);
qry = "INSERT INTO Shares (ssn, symbol, quantity) VALUES ('" + ssn + "', '" + symbol + "', " + tmpQty2.toString() + ")";
}
try {
result = msql.Query(qry);
}
catch (MsqlException e) {
System.err.println("buyShares sql query (INSERT or UPDATE) failed");
System.err.println("Query: " + qry);
System.err.println("Exception" + e.getMessage());
}
}
/**
Updates price in Stock table. Note: assumes valid stock symbol
@param symbol a String with the shares identifier
@param price the price per share.
@exception database.RecordNotFoundException if ssn not in Customer table
*/
public void updateStockPrice(String symbol, float price)
{
MsqlResult result = null;
String qry = null;
if (getStockPrice(symbol) != 1.0) {
qry = "UPDATE Stock SET Stock.price = " +
Float.toString(price) +
" WHERE Stock.symbol = '" + symbol + "'";
}
else {
qry = "INSERT INTO Stock (symbol, price) VALUES ('" +
symbol + "', " + Float.toString(price) + ")";
}
try {
result = msql.Query(qry);
}
catch (MsqlException e) {
System.err.println("Database.updateStockPrice(" +
qry + " " + e.getMessage());
e.printStackTrace();
}
}
/**
Returns price from Stock table
@param symbol a String with the shares identifier
@returns a float with the price per share or -1.0 on failure
*/
public float getStockPrice(String symbol) {
float priceRet = 1.0F;
MsqlResult result = null;
String row[];
try {
result = msql.Query("SELECT Stock.price FROM Stock WHERE Stock.symbol = '" + symbol + "'");
}
catch (MsqlException e) {
System.err.println("getStockPrice sql SELECT failed: " + e.getMessage());
}
priceRet = 0.0F;
while ((row=result.FetchRow()) != null) {
priceRet = (new Float(row[0])).floatValue(); // should only return but this just sets to last
}
return priceRet;
}
/**
Check whether the supplied ssn exists in the customer database
@param String ssn Unique social security number of customer
@return boolean True if the ssn number exists, false otherwise
*/
public boolean ssnExists (String ssn) {
try {
MsqlResult result = msql.Query("SELECT Customer.ssn FROM Customer WHERE Customer.ssn = '"+ssn+"'");
return result.FetchRow() != null;
} catch (MsqlException e) {
System.err.println( "Database.ssnExists() failed" );
e.printStackTrace();
return false;
}
}
/**
Add a new customer into the customer database table
@exception DuplicateIDException thrown if the supplied ssn number is not unique
@param String name Name of customer
@param String ssn Unique social security number of customer
@param String address Address of customer
@return None
*/
public void addCustomer (String name,String ssn,String address) throws DuplicateIDException {
if ( ssnExists(ssn) ) {
throw new DuplicateIDException();
} else {
try {
msql.Query ( "INSERT INTO Customer (ssn, cust_name, address ) VALUES ( '"+ssn+"','"+name+"','"+address+"')" );
} catch (MsqlException e) {
System.err.println( "addCustomer INSERT failed");
e.printStackTrace();
}
}
}
/**
Delete a customer from the customer database table
@exception RecordNotFoundException thrown if the supplied ssn number is not found
@param String ssn Unique social security number of customer
@return None
*/
public void deleteCustomer (String ssn) throws RecordNotFoundException {
if ( !ssnExists(ssn) ) {
throw new RecordNotFoundException();
} else {
try {
msql.Query ( "DELETE FROM Customer WHERE Customer.ssn = '"+ssn+"'" );
} catch (MsqlException e) {
System.err.println( "Database.deleteCustomer failed" );
e.printStackTrace();
}
}
}
/**
Update a customer in the customer database table
@exception RecordNotFoundException thrown if the supplied ssn number is not found
@param String name Name of customer
@param String ssn Unique social security number of customer (cannot be changed)
@param String address Address of customer
@return None
*/
public void updateCustomer (String name,String ssn,String address) throws RecordNotFoundException {
if ( !ssnExists(ssn) ) {
throw new RecordNotFoundException();
} else {
try {
msql.Query ( "UPDATE Customer SET Customer.cust_name = '"+name+"', Customer.address = '"+
address+"' WHERE Customer.ssn = '"+ssn+"'" );
} catch (MsqlException e) {
System.err.println( "Database.updateCustomer() UPDATE failed" );
e.printStackTrace();
}
}
}
/**
Return a given customer from the customer database table
@exception RecordNotFoundException thrown if the supplied ssn number is not found
@param String ssn Unique social security number of customer
@return CustomerRec The customer requested
*/
public CustomerRec getCustomer (String ssn) throws RecordNotFoundException {
String qry = null;
if ( !ssnExists(ssn) ) {
// System.err.println("ssn does not exist: " + ssn);
throw new RecordNotFoundException();
} else {
try {
// System.err.println("We got here");
qry = "SELECT Customer.cust_name,Customer.address FROM Customer WHERE Customer.ssn = '"+ssn+"'";
MsqlResult result = msql.Query(qry);
CustomerRec customer = new CustomerRec();
String row[];
customer.ssn = ssn;
if ((row = result.FetchRow()) != null) {
customer.name = row[0];
customer.addr = row[1];
}
try {
// System.err.println("Pre getPortfolio()");
customer.portfolio = getPortfolio(ssn);
// System.err.println("Post getPortfolio()");
}
catch (RecordNotFoundException e) {
System.err.println("ssn could not be found");
throw new RecordNotFoundException();
}
return customer;
} catch (MsqlException e) {
System.err.println( "Database.getCustomer() SELECT failed" );
e.printStackTrace();
}
}
return null;
}
/**
Return all customers from the customer database table
@return CustomerRec[] Array of all customers
*/
public CustomerRec[] getAllCustomers ()
{
try
{
Vector list = new Vector(10,10);
MsqlResult result = msql.Query("SELECT Customer.ssn, Customer.cust_name, Customer.address FROM Customer");
int numres = 0;
String row[];
while ((row = result.FetchRow()) != null) {
CustomerRec customer = new CustomerRec();
numres++;
customer.ssn = row[0];
customer.name = row[1];
customer.addr = row[2];
list.addElement(customer);
}
CustomerRec[] allcusts = new CustomerRec[numres];
list.copyInto (allcusts);
return allcusts;
} catch (MsqlException e)
{
System.err.println( "There are no customers to list" );
}
return null;
}
/**
Return a customer's portfolio
@exception RecordNotFoundException thrown if the supplied ssn number is not found
@param String ssn Unique social security number of customer
@return Vector The portfolio for the requested customer as SharesRec objects
*/
public Vector getPortfolio (String ssn) throws RecordNotFoundException {
if ( !ssnExists(ssn) ) {
throw new RecordNotFoundException();
} else {
try {
Vector list = new Vector(10,10);
MsqlResult result = msql.Query("SELECT Shares.symbol,Shares.quantity FROM Shares WHERE Shares.ssn = '"+ssn+"'");
String row[];
while ((row = result.FetchRow()) != null) {
SharesRec shares = new SharesRec();
shares.ssn = ssn;
shares.symbol = row[0];
shares.quantity = Integer.parseInt(row[1]);
list.addElement(shares);
}
return list;
} catch (MsqlException e) {
System.err.println( "getPortfolio query failed" );
} catch (NumberFormatException e) {
System.err.println( "Share quantity invalid" );
}
}
return null;
}
}
Last Updated