From scm-return-45599-apmail-geronimo-scm-archive=geronimo.apache.org@geronimo.apache.org Thu Feb 17 08:24:41 2011 Return-Path: Delivered-To: apmail-geronimo-scm-archive@www.apache.org Received: (qmail 44034 invoked from network); 17 Feb 2011 08:24:41 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.3) by minotaur.apache.org with SMTP; 17 Feb 2011 08:24:41 -0000 Received: (qmail 1188 invoked by uid 500); 17 Feb 2011 08:24:41 -0000 Delivered-To: apmail-geronimo-scm-archive@geronimo.apache.org Received: (qmail 323 invoked by uid 500); 17 Feb 2011 08:24:39 -0000 Mailing-List: contact scm-help@geronimo.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: Reply-To: dev@geronimo.apache.org List-Id: Delivered-To: mailing list scm@geronimo.apache.org Received: (qmail 165 invoked by uid 99); 17 Feb 2011 08:24:38 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 17 Feb 2011 08:24:38 +0000 X-ASF-Spam-Status: No, hits=-1998.5 required=5.0 tests=ALL_TRUSTED,TVD_PH_BODY_ACCOUNTS_PRE,T_FILL_THIS_FORM_SHORT X-Spam-Check-By: apache.org Received: from [140.211.11.4] (HELO eris.apache.org) (140.211.11.4) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 17 Feb 2011 08:24:28 +0000 Received: by eris.apache.org (Postfix, from userid 65534) id 80BB42388A6C; Thu, 17 Feb 2011 08:24:06 +0000 (UTC) Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Subject: svn commit: r1071543 [3/15] - in /geronimo/daytrader/trunk: ./ assemblies/javaee/ javaee6/ javaee6/assemblies/ javaee6/assemblies/daytrader-ear/ javaee6/assemblies/daytrader-ear/src/ javaee6/assemblies/daytrader-ear/src/main/ javaee6/assemblies/daytrad... Date: Thu, 17 Feb 2011 08:24:04 -0000 To: scm@geronimo.apache.org From: violalu@apache.org X-Mailer: svnmailer-1.0.8 Message-Id: <20110217082406.80BB42388A6C@eris.apache.org> X-Virus-Checked: Checked by ClamAV on apache.org Added: geronimo/daytrader/trunk/javaee6/modules/core/src/main/java/org/apache/geronimo/daytrader/javaee6/core/direct/TradeJDBCDirect.java URL: http://svn.apache.org/viewvc/geronimo/daytrader/trunk/javaee6/modules/core/src/main/java/org/apache/geronimo/daytrader/javaee6/core/direct/TradeJDBCDirect.java?rev=1071543&view=auto ============================================================================== --- geronimo/daytrader/trunk/javaee6/modules/core/src/main/java/org/apache/geronimo/daytrader/javaee6/core/direct/TradeJDBCDirect.java (added) +++ geronimo/daytrader/trunk/javaee6/modules/core/src/main/java/org/apache/geronimo/daytrader/javaee6/core/direct/TradeJDBCDirect.java Thu Feb 17 08:23:57 2011 @@ -0,0 +1,1931 @@ +/** + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to You under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package org.apache.geronimo.daytrader.javaee6.core.direct; + +import java.math.BigDecimal; +import java.util.Collection; +import java.util.ArrayList; +import javax.naming.InitialContext; + +import javax.sql.DataSource; + +import org.apache.geronimo.daytrader.javaee6.entities.*; +import org.apache.geronimo.daytrader.javaee6.core.api.*; +import org.apache.geronimo.daytrader.javaee6.core.beans.*; +import org.apache.geronimo.daytrader.javaee6.utils.*; + +import java.sql.Connection; +import java.sql.DatabaseMetaData; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Statement; +import java.sql.Timestamp; + + +/** + * TradeJDBCDirect uses direct JDBC access to a + * javax.sql.DataSource to implement the business methods of the + * Trade online broker application. These business methods represent the + * features and operations that can be performed by customers of the brokerage + * such as login, logout, get a stock quote, buy or sell a stock, etc. and are + * specified in the {@link org.apache.geronimo.samples.daytrader.TradeServices} + * interface + * + * Note: In order for this class to be thread-safe, a new TradeJDBC must be + * created for each call to a method from the TradeInterface interface. + * Otherwise, pooled connections may not be released. + * + * @see org.apache.geronimo.samples.daytrader.TradeServices + * + */ + +public class TradeJDBCDirect implements TradeServices, TradeDBServices { + + private static String dsName = TradeConfig.DATASOURCE; + + private static DataSource datasource = null; + + private static BigDecimal ZERO = new BigDecimal(0.0); + + private boolean inGlobalTxn = false; + + private boolean inSession = false; + + private static InitialContext context; + + /** + * Zero arg constructor for TradeJDBCDirect + */ + public TradeJDBCDirect() { + if (initialized == false) + init(); + } + + public TradeJDBCDirect(boolean inSession) { + if (initialized == false) + init(); + + this.inSession = inSession; + } + + /** + * @see TradeServices#getMarketSummary() + */ + public MarketSummaryDataBean getMarketSummary() throws Exception { + + MarketSummaryDataBean marketSummaryData = null; + Connection conn = null; + try { + if (Log.doTrace()) + Log.trace("TradeJDBCDirect:getMarketSummary - inSession(" + this.inSession + ")"); + + conn = getConn(); + PreparedStatement stmt = + getStatement(conn, getTSIAQuotesOrderByChangeSQL, ResultSet.TYPE_SCROLL_INSENSITIVE, + ResultSet.CONCUR_READ_ONLY); + + ArrayList topGainersData = new ArrayList(5); + ArrayList topLosersData = new ArrayList(5); + + ResultSet rs = stmt.executeQuery(); + + int count = 0; + while (rs.next() && (count++ < 5)) { + QuoteDataBean quoteData = getQuoteDataFromResultSet(rs); + topLosersData.add(quoteData); + } + + stmt.close(); + stmt = + getStatement(conn, "select * from quoteejb q where q.symbol like 's:1__' order by q.change1 DESC", + ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); + rs = stmt.executeQuery(); + + count = 0; + while (rs.next() && (count++ < 5)) { + QuoteDataBean quoteData = getQuoteDataFromResultSet(rs); + topGainersData.add(quoteData); + } + + stmt.close(); + + BigDecimal TSIA = ZERO; + BigDecimal openTSIA = ZERO; + double volume = 0.0; + + if ((topGainersData.size() > 0) || (topLosersData.size() > 0)) { + + stmt = getStatement(conn, getTSIASQL); + rs = stmt.executeQuery(); + + if (!rs.next()) + Log.error("TradeJDBCDirect:getMarketSummary -- error w/ getTSIASQL -- no results"); + else + TSIA = rs.getBigDecimal("TSIA"); + stmt.close(); + + stmt = getStatement(conn, getOpenTSIASQL); + rs = stmt.executeQuery(); + + if (!rs.next()) + Log.error("TradeJDBCDirect:getMarketSummary -- error w/ getOpenTSIASQL -- no results"); + else + openTSIA = rs.getBigDecimal("openTSIA"); + stmt.close(); + + stmt = getStatement(conn, getTSIATotalVolumeSQL); + rs = stmt.executeQuery(); + + if (!rs.next()) + Log.error("TradeJDBCDirect:getMarketSummary -- error w/ getTSIATotalVolumeSQL -- no results"); + else + volume = rs.getDouble("totalVolume"); + stmt.close(); + } + commit(conn); + + marketSummaryData = new MarketSummaryDataBean(TSIA, openTSIA, volume, topGainersData, topLosersData); + + } + + catch (Exception e) { + Log.error("TradeJDBCDirect:getMarketSummary -- error getting summary", e); + rollBack(conn, e); + } finally { + releaseConn(conn); + } + return marketSummaryData; + + } + + /** + * @see TradeServices#buy(String, String, double) + */ + public OrderDataBean buy(String userID, String symbol, double quantity, int orderProcessingMode) throws Exception { + + Connection conn = null; + OrderDataBean orderData = null; + + /* + * total = (quantity * purchasePrice) + orderFee + */ + BigDecimal total; + + try { + if (Log.doTrace()) + Log.trace("TradeJDBCDirect:buy - inSession(" + this.inSession + ")", userID, symbol, new Double(quantity)); + + conn = getConn(); + + AccountDataBean accountData = getAccountData(conn, userID); + QuoteDataBean quoteData = getQuoteData(conn, symbol); + HoldingDataBean holdingData = null; // the buy operation will create + // the holding + + orderData = createOrder(conn, accountData, quoteData, holdingData, "buy", quantity); + + // Update -- account should be credited during completeOrder + BigDecimal price = quoteData.getPrice(); + BigDecimal orderFee = orderData.getOrderFee(); + total = (new BigDecimal(quantity).multiply(price)).add(orderFee); + // subtract total from account balance + creditAccountBalance(conn, accountData, total.negate()); + + completeOrder(conn, orderData.getOrderID()); + + orderData = getOrderData(conn, orderData.getOrderID().intValue()); + + commit(conn); + + } catch (Exception e) { + Log.error("TradeJDBCDirect:buy error - rolling back", e); + rollBack(conn, e); + } finally { + releaseConn(conn); + } + + return orderData; + } + + /** + * @see TradeServices#sell(String, Integer) + */ + public OrderDataBean sell(String userID, Integer holdingID, int orderProcessingMode) throws Exception { + Connection conn = null; + OrderDataBean orderData = null; + + /* + * total = (quantity * purchasePrice) + orderFee + */ + BigDecimal total; + + try { + if (Log.doTrace()) + Log.trace("TradeJDBCDirect:sell - inSession(" + this.inSession + ")", userID, holdingID); + + conn = getConn(); + + AccountDataBean accountData = getAccountData(conn, userID); + HoldingDataBean holdingData = getHoldingData(conn, holdingID.intValue()); + QuoteDataBean quoteData = null; + if (holdingData != null) + quoteData = getQuoteData(conn, holdingData.getQuoteID()); + + if ((accountData == null) || (holdingData == null) || (quoteData == null)) { + String error = + "TradeJDBCDirect:sell -- error selling stock -- unable to find: \n\taccount=" + accountData + + "\n\tholding=" + holdingData + "\n\tquote=" + quoteData + "\nfor user: " + userID + + " and holdingID: " + holdingID; + Log.error(error); + rollBack(conn, new Exception(error)); + + return orderData; + } + + double quantity = holdingData.getQuantity(); + + orderData = createOrder(conn, accountData, quoteData, holdingData, "sell", quantity); + + // Set the holdingSymbol purchaseDate to selling to signify the sell + // is "inflight" + updateHoldingStatus(conn, holdingData.getHoldingID(), holdingData.getQuoteID()); + + // UPDATE -- account should be credited during completeOrder + BigDecimal price = quoteData.getPrice(); + BigDecimal orderFee = orderData.getOrderFee(); + total = (new BigDecimal(quantity).multiply(price)).subtract(orderFee); + creditAccountBalance(conn, accountData, total); + + completeOrder(conn, orderData.getOrderID()); + + orderData = getOrderData(conn, orderData.getOrderID().intValue()); + + commit(conn); + + } catch (Exception e) { + Log.error("TradeJDBCDirect:sell error", e); + rollBack(conn, e); + + } finally { + releaseConn(conn); + } + + return orderData; + } + + /** + * @see TradeServices#queueOrder(Integer) + */ + public void queueOrder(Integer orderID, boolean twoPhase) throws Exception { + throw new RuntimeException("TradeServices#queueOrder(Integer) is not supported in this runtime mode"); + } + + /** + * @see TradeServices#completeOrder(Integer) + */ + public OrderDataBean completeOrder(Integer orderID, boolean twoPhase) throws Exception { + OrderDataBean orderData = null; + Connection conn = null; + + try { // twoPhase + + if (Log.doTrace()) + Log.trace("TradeJDBCDirect:completeOrder - inSession(" + this.inSession + ")", orderID); + setInGlobalTxn(!inSession && twoPhase); + conn = getConn(); + orderData = completeOrder(conn, orderID); + commit(conn); + + } catch (Exception e) { + Log.error("TradeJDBCDirect:completeOrder -- error completing order", e); + rollBack(conn, e); + cancelOrder(orderID, twoPhase); + } finally { + releaseConn(conn); + } + + return orderData; + + } + + private OrderDataBean completeOrder(Connection conn, Integer orderID) throws Exception { + + OrderDataBean orderData = null; + if (Log.doTrace()) + Log.trace("TradeJDBCDirect:completeOrderInternal - inSession(" + this.inSession + ")", orderID); + + PreparedStatement stmt = getStatement(conn, getOrderSQL); + stmt.setInt(1, orderID.intValue()); + + ResultSet rs = stmt.executeQuery(); + + if (!rs.next()) { + Log.error("TradeJDBCDirect:completeOrder -- unable to find order: " + orderID); + stmt.close(); + return orderData; + } + orderData = getOrderDataFromResultSet(rs); + + String orderType = orderData.getOrderType(); + String orderStatus = orderData.getOrderStatus(); + + // if (order.isCompleted()) + if ((orderStatus.compareToIgnoreCase("completed") == 0) + || (orderStatus.compareToIgnoreCase("alertcompleted") == 0) + || (orderStatus.compareToIgnoreCase("cancelled") == 0)) + throw new Exception("TradeJDBCDirect:completeOrder -- attempt to complete Order that is already completed"); + + int accountID = rs.getInt("account_accountID"); + String quoteID = rs.getString("quote_symbol"); + int holdingID = rs.getInt("holding_holdingID"); + + BigDecimal price = orderData.getPrice(); + double quantity = orderData.getQuantity(); + BigDecimal orderFee = orderData.getOrderFee(); + + // get the data for the account and quote + // the holding will be created for a buy or extracted for a sell + + /* + * Use the AccountID and Quote Symbol from the Order AccountDataBean accountData = getAccountData(accountID, + * conn); QuoteDataBean quoteData = getQuoteData(conn, quoteID); + */ + String userID = getAccountProfileData(conn, new Integer(accountID)).getUserID(); + + HoldingDataBean holdingData = null; + + if (Log.doTrace()) + Log.trace("TradeJDBCDirect:completeOrder--> Completing Order " + orderData.getOrderID() + "\n\t Order info: " + + orderData + "\n\t Account info: " + accountID + "\n\t Quote info: " + quoteID); + + // if (order.isBuy()) + if (orderType.compareToIgnoreCase("buy") == 0) { + /* + * Complete a Buy operation - create a new Holding for the Account - deduct the Order cost from the Account + * balance + */ + + holdingData = createHolding(conn, accountID, quoteID, quantity, price); + updateOrderHolding(conn, orderID.intValue(), holdingData.getHoldingID().intValue()); + } + + // if (order.isSell()) { + if (orderType.compareToIgnoreCase("sell") == 0) { + /* + * Complete a Sell operation - remove the Holding from the Account - deposit the Order proceeds to the + * Account balance + */ + holdingData = getHoldingData(conn, holdingID); + if (holdingData == null) + Log.debug("TradeJDBCDirect:completeOrder:sell -- user: " + userID + " already sold holding: " + holdingID); + else + removeHolding(conn, holdingID, orderID.intValue()); + + } + + updateOrderStatus(conn, orderData.getOrderID(), "closed"); + + if (Log.doTrace()) + Log.trace("TradeJDBCDirect:completeOrder--> Completed Order " + orderData.getOrderID() + "\n\t Order info: " + + orderData + "\n\t Account info: " + accountID + "\n\t Quote info: " + quoteID + "\n\t Holding info: " + + holdingData); + + stmt.close(); + + commit(conn); + + // signify this order for user userID is complete + TradeAction tradeAction = new TradeAction(this); + tradeAction.orderCompleted(userID, orderID); + + return orderData; + } + + /** + * @see TradeServices#cancelOrder(Integer, boolean) + */ + public void cancelOrder(Integer orderID, boolean twoPhase) throws Exception { + OrderDataBean orderData = null; + Connection conn = null; + try { + if (Log.doTrace()) + Log.trace("TradeJDBCDirect:cancelOrder - inSession(" + this.inSession + ")", orderID); + setInGlobalTxn(!inSession && twoPhase); + conn = getConn(); + cancelOrder(conn, orderID); + commit(conn); + + } catch (Exception e) { + Log.error("TradeJDBCDirect:cancelOrder -- error cancelling order: " + orderID, e); + rollBack(conn, e); + } finally { + releaseConn(conn); + } + } + + private void cancelOrder(Connection conn, Integer orderID) throws Exception { + updateOrderStatus(conn, orderID, "cancelled"); + } + + public void orderCompleted(String userID, Integer orderID) throws Exception { + throw new UnsupportedOperationException("TradeJDBCDirect:orderCompleted method not supported"); + } + + private HoldingDataBean createHolding(Connection conn, int accountID, String symbol, double quantity, + BigDecimal purchasePrice) throws Exception { + HoldingDataBean holdingData = null; + + Timestamp purchaseDate = new Timestamp(System.currentTimeMillis()); + PreparedStatement stmt = getStatement(conn, createHoldingSQL); + + Integer holdingID = KeySequenceDirect.getNextID(conn, "holding", inSession, getInGlobalTxn()); + stmt.setInt(1, holdingID.intValue()); + stmt.setTimestamp(2, purchaseDate); + stmt.setBigDecimal(3, purchasePrice); + stmt.setDouble(4, quantity); + stmt.setString(5, symbol); + stmt.setInt(6, accountID); + int rowCount = stmt.executeUpdate(); + + stmt.close(); + + return getHoldingData(conn, holdingID.intValue()); + } + + private void removeHolding(Connection conn, int holdingID, int orderID) throws Exception { + PreparedStatement stmt = getStatement(conn, removeHoldingSQL); + + stmt.setInt(1, holdingID); + int rowCount = stmt.executeUpdate(); + stmt.close(); + + // set the HoldingID to NULL for the purchase and sell order now that + // the holding as been removed + stmt = getStatement(conn, removeHoldingFromOrderSQL); + + stmt.setInt(1, holdingID); + rowCount = stmt.executeUpdate(); + stmt.close(); + + } + + private OrderDataBean createOrder(Connection conn, AccountDataBean accountData, QuoteDataBean quoteData, + HoldingDataBean holdingData, String orderType, double quantity) throws Exception { + OrderDataBean orderData = null; + + Timestamp currentDate = new Timestamp(System.currentTimeMillis()); + + PreparedStatement stmt = getStatement(conn, createOrderSQL); + + Integer orderID = KeySequenceDirect.getNextID(conn, "order", inSession, getInGlobalTxn()); + stmt.setInt(1, orderID.intValue()); + stmt.setString(2, orderType); + stmt.setString(3, "open"); + stmt.setTimestamp(4, currentDate); + stmt.setDouble(5, quantity); + stmt.setBigDecimal(6, quoteData.getPrice().setScale(FinancialUtils.SCALE, FinancialUtils.ROUND)); + stmt.setBigDecimal(7, TradeConfig.getOrderFee(orderType)); + stmt.setInt(8, accountData.getAccountID().intValue()); + if (holdingData == null) + stmt.setNull(9, java.sql.Types.INTEGER); + else + stmt.setInt(9, holdingData.getHoldingID().intValue()); + stmt.setString(10, quoteData.getSymbol()); + int rowCount = stmt.executeUpdate(); + + stmt.close(); + + return getOrderData(conn, orderID.intValue()); + } + + /** + * @see TradeServices#getOrders(String) + */ + public Collection getOrders(String userID) throws Exception { + Collection orderDataBeans = new ArrayList(); + Connection conn = null; + try { + if (Log.doTrace()) + Log.trace("TradeJDBCDirect:getOrders - inSession(" + this.inSession + ")", userID); + + conn = getConn(); + PreparedStatement stmt = getStatement(conn, getOrdersByUserSQL); + stmt.setString(1, userID); + + ResultSet rs = stmt.executeQuery(); + + // TODO: return top 5 orders for now -- next version will add a + // getAllOrders method + // also need to get orders sorted by order id descending + int i = 0; + while ((rs.next()) && (i++ < 5)) { + OrderDataBean orderData = getOrderDataFromResultSet(rs); + orderDataBeans.add(orderData); + } + + stmt.close(); + commit(conn); + + } catch (Exception e) { + Log.error("TradeJDBCDirect:getOrders -- error getting user orders", e); + rollBack(conn, e); + } finally { + releaseConn(conn); + } + return orderDataBeans; + } + + /** + * @see TradeServices#getClosedOrders(String) + */ + public Collection getClosedOrders(String userID) throws Exception { + Collection orderDataBeans = new ArrayList(); + Connection conn = null; + try { + if (Log.doTrace()) + Log.trace("TradeJDBCDirect:getClosedOrders - inSession(" + this.inSession + ")", userID); + + conn = getConn(); + PreparedStatement stmt = getStatement(conn, getClosedOrdersSQL); + stmt.setString(1, userID); + + ResultSet rs = stmt.executeQuery(); + + while (rs.next()) { + OrderDataBean orderData = getOrderDataFromResultSet(rs); + orderData.setOrderStatus("completed"); + updateOrderStatus(conn, orderData.getOrderID(), orderData.getOrderStatus()); + orderDataBeans.add(orderData); + + } + + stmt.close(); + commit(conn); + } catch (Exception e) { + Log.error("TradeJDBCDirect:getOrders -- error getting user orders", e); + rollBack(conn, e); + } finally { + releaseConn(conn); + } + return orderDataBeans; + } + + /** + * @see TradeServices#createQuote(String, String, BigDecimal) + */ + public QuoteDataBean createQuote(String symbol, String companyName, BigDecimal price) throws Exception { + + QuoteDataBean quoteData = null; + Connection conn = null; + try { + if (Log.doTrace()) + Log.traceEnter("TradeJDBCDirect:createQuote - inSession(" + this.inSession + ")"); + + price = price.setScale(FinancialUtils.SCALE, FinancialUtils.ROUND); + double volume = 0.0, change = 0.0; + + conn = getConn(); + PreparedStatement stmt = getStatement(conn, createQuoteSQL); + stmt.setString(1, symbol); // symbol + stmt.setString(2, companyName); // companyName + stmt.setDouble(3, volume); // volume + stmt.setBigDecimal(4, price); // price + stmt.setBigDecimal(5, price); // open + stmt.setBigDecimal(6, price); // low + stmt.setBigDecimal(7, price); // high + stmt.setDouble(8, change); // change + + stmt.executeUpdate(); + stmt.close(); + commit(conn); + + quoteData = new QuoteDataBean(symbol, companyName, volume, price, price, price, price, change); + if (Log.doTrace()) + Log.traceExit("TradeJDBCDirect:createQuote"); + } catch (Exception e) { + Log.error("TradeJDBCDirect:createQuote -- error creating quote", e); + } finally { + releaseConn(conn); + } + return quoteData; + } + + /** + * @see TradeServices#getQuote(String) + */ + + public QuoteDataBean getQuote(String symbol) throws Exception { + QuoteDataBean quoteData = null; + Connection conn = null; + + try { + if (Log.doTrace()) + Log.trace("TradeJDBCDirect:getQuote - inSession(" + this.inSession + ")", symbol); + + conn = getConn(); + quoteData = getQuote(conn, symbol); + commit(conn); + } catch (Exception e) { + Log.error("TradeJDBCDirect:getQuote -- error getting quote", e); + rollBack(conn, e); + } finally { + releaseConn(conn); + } + return quoteData; + } + + private QuoteDataBean getQuote(Connection conn, String symbol) throws Exception { + QuoteDataBean quoteData = null; + PreparedStatement stmt = getStatement(conn, getQuoteSQL); + stmt.setString(1, symbol); // symbol + + ResultSet rs = stmt.executeQuery(); + + if (!rs.next()) + Log.error("TradeJDBCDirect:getQuote -- failure no result.next() for symbol: " + symbol); + + else + quoteData = getQuoteDataFromResultSet(rs); + + stmt.close(); + + return quoteData; + } + + private QuoteDataBean getQuoteForUpdate(Connection conn, String symbol) throws Exception { + QuoteDataBean quoteData = null; + PreparedStatement stmt = getStatement(conn, getQuoteForUpdateSQL); + stmt.setString(1, symbol); // symbol + + ResultSet rs = stmt.executeQuery(); + + if (!rs.next()) + Log.error("TradeJDBCDirect:getQuote -- failure no result.next()"); + + else + quoteData = getQuoteDataFromResultSet(rs); + + stmt.close(); + + return quoteData; + } + + /** + * @see TradeServices#getAllQuotes(String) + */ + public Collection getAllQuotes() throws Exception { + Collection quotes = new ArrayList(); + QuoteDataBean quoteData = null; + + Connection conn = null; + try { + conn = getConn(); + + PreparedStatement stmt = getStatement(conn, getAllQuotesSQL); + + ResultSet rs = stmt.executeQuery(); + + while (!rs.next()) { + quoteData = getQuoteDataFromResultSet(rs); + quotes.add(quoteData); + } + + stmt.close(); + } catch (Exception e) { + Log.error("TradeJDBCDirect:getAllQuotes", e); + rollBack(conn, e); + } finally { + releaseConn(conn); + } + + return quotes; + } + + /** + * @see TradeServices#getHoldings(String) + */ + public Collection getHoldings(String userID) throws Exception { + Collection holdingDataBeans = new ArrayList(); + Connection conn = null; + try { + if (Log.doTrace()) + Log.trace("TradeJDBCDirect:getHoldings - inSession(" + this.inSession + ")", userID); + + conn = getConn(); + PreparedStatement stmt = getStatement(conn, getHoldingsForUserSQL); + stmt.setString(1, userID); + + ResultSet rs = stmt.executeQuery(); + + while (rs.next()) { + HoldingDataBean holdingData = getHoldingDataFromResultSet(rs); + holdingDataBeans.add(holdingData); + } + + stmt.close(); + commit(conn); + + } catch (Exception e) { + Log.error("TradeJDBCDirect:getHoldings -- error getting user holings", e); + rollBack(conn, e); + } finally { + releaseConn(conn); + } + return holdingDataBeans; + } + + /** + * @see TradeServices#getHolding(Integer) + */ + public HoldingDataBean getHolding(Integer holdingID) throws Exception { + HoldingDataBean holdingData = null; + Connection conn = null; + try { + if (Log.doTrace()) + Log.trace("TradeJDBCDirect:getHolding - inSession(" + this.inSession + ")", holdingID); + + conn = getConn(); + holdingData = getHoldingData(holdingID.intValue()); + + commit(conn); + + } catch (Exception e) { + Log.error("TradeJDBCDirect:getHolding -- error getting holding " + holdingID + "", e); + rollBack(conn, e); + } finally { + releaseConn(conn); + } + return holdingData; + } + + /** + * @see TradeServices#getAccountData(String) + */ + public AccountDataBean getAccountData(String userID) throws Exception { + try { + AccountDataBean accountData = null; + Connection conn = null; + try { + if (Log.doTrace()) + Log.trace("TradeJDBCDirect:getAccountData - inSession(" + this.inSession + ")", userID); + + conn = getConn(); + accountData = getAccountData(conn, userID); + commit(conn); + + } catch (Exception e) { + Log.error("TradeJDBCDirect:getAccountData -- error getting account data", e); + rollBack(conn, e); + } finally { + releaseConn(conn); + } + return accountData; + } catch (Exception e) { + throw new Exception(e.getMessage(), e); + } + } + + private AccountDataBean getAccountData(Connection conn, String userID) throws Exception { + PreparedStatement stmt = getStatement(conn, getAccountForUserSQL); + stmt.setString(1, userID); + ResultSet rs = stmt.executeQuery(); + AccountDataBean accountData = getAccountDataFromResultSet(rs); + stmt.close(); + return accountData; + } + + private AccountDataBean getAccountDataForUpdate(Connection conn, String userID) throws Exception { + PreparedStatement stmt = getStatement(conn, getAccountForUserForUpdateSQL); + stmt.setString(1, userID); + ResultSet rs = stmt.executeQuery(); + AccountDataBean accountData = getAccountDataFromResultSet(rs); + stmt.close(); + return accountData; + } + + /** + * @see TradeServices#getAccountData(String) + */ + public AccountDataBean getAccountData(int accountID) throws Exception { + AccountDataBean accountData = null; + Connection conn = null; + try { + if (Log.doTrace()) + Log.trace("TradeJDBCDirect:getAccountData - inSession(" + this.inSession + ")", new Integer(accountID)); + + conn = getConn(); + accountData = getAccountData(accountID, conn); + commit(conn); + + } catch (Exception e) { + Log.error("TradeJDBCDirect:getAccountData -- error getting account data", e); + rollBack(conn, e); + } finally { + releaseConn(conn); + } + return accountData; + } + + private AccountDataBean getAccountData(int accountID, Connection conn) throws Exception { + PreparedStatement stmt = getStatement(conn, getAccountSQL); + stmt.setInt(1, accountID); + ResultSet rs = stmt.executeQuery(); + AccountDataBean accountData = getAccountDataFromResultSet(rs); + stmt.close(); + return accountData; + } + + private AccountDataBean getAccountDataForUpdate(int accountID, Connection conn) throws Exception { + PreparedStatement stmt = getStatement(conn, getAccountForUpdateSQL); + stmt.setInt(1, accountID); + ResultSet rs = stmt.executeQuery(); + AccountDataBean accountData = getAccountDataFromResultSet(rs); + stmt.close(); + return accountData; + } + + private QuoteDataBean getQuoteData(String symbol) throws Exception { + QuoteDataBean quoteData = null; + Connection conn = null; + try { + conn = getConn(); + quoteData = getQuoteData(conn, symbol); + commit(conn); + } catch (Exception e) { + Log.error("TradeJDBCDirect:getQuoteData -- error getting data", e); + rollBack(conn, e); + } finally { + releaseConn(conn); + } + return quoteData; + } + + private QuoteDataBean getQuoteData(Connection conn, String symbol) throws Exception { + QuoteDataBean quoteData = null; + PreparedStatement stmt = getStatement(conn, getQuoteSQL); + stmt.setString(1, symbol); + ResultSet rs = stmt.executeQuery(); + if (!rs.next()) + Log.error("TradeJDBCDirect:getQuoteData -- could not find quote for symbol=" + symbol); + else + quoteData = getQuoteDataFromResultSet(rs); + stmt.close(); + return quoteData; + } + + private HoldingDataBean getHoldingData(int holdingID) throws Exception { + HoldingDataBean holdingData = null; + Connection conn = null; + try { + conn = getConn(); + holdingData = getHoldingData(conn, holdingID); + commit(conn); + } catch (Exception e) { + Log.error("TradeJDBCDirect:getHoldingData -- error getting data", e); + rollBack(conn, e); + } finally { + releaseConn(conn); + } + return holdingData; + } + + private HoldingDataBean getHoldingData(Connection conn, int holdingID) throws Exception { + HoldingDataBean holdingData = null; + PreparedStatement stmt = getStatement(conn, getHoldingSQL); + stmt.setInt(1, holdingID); + ResultSet rs = stmt.executeQuery(); + if (!rs.next()) + Log.error("TradeJDBCDirect:getHoldingData -- no results -- holdingID=" + holdingID); + else + holdingData = getHoldingDataFromResultSet(rs); + + stmt.close(); + return holdingData; + } + + private OrderDataBean getOrderData(int orderID) throws Exception { + OrderDataBean orderData = null; + Connection conn = null; + try { + conn = getConn(); + orderData = getOrderData(conn, orderID); + commit(conn); + } catch (Exception e) { + Log.error("TradeJDBCDirect:getOrderData -- error getting data", e); + rollBack(conn, e); + } finally { + releaseConn(conn); + } + return orderData; + } + + private OrderDataBean getOrderData(Connection conn, int orderID) throws Exception { + OrderDataBean orderData = null; + if (Log.doTrace()) + Log.trace("TradeJDBCDirect:getOrderData(conn, " + orderID + ")"); + PreparedStatement stmt = getStatement(conn, getOrderSQL); + stmt.setInt(1, orderID); + ResultSet rs = stmt.executeQuery(); + if (!rs.next()) + Log.error("TradeJDBCDirect:getOrderData -- no results for orderID:" + orderID); + else + orderData = getOrderDataFromResultSet(rs); + stmt.close(); + return orderData; + } + + /** + * @see TradeServices#getAccountProfileData(String) + */ + public AccountProfileDataBean getAccountProfileData(String userID) throws Exception { + AccountProfileDataBean accountProfileData = null; + Connection conn = null; + + try { + if (Log.doTrace()) + Log.trace("TradeJDBCDirect:getAccountProfileData - inSession(" + this.inSession + ")", userID); + + conn = getConn(); + accountProfileData = getAccountProfileData(conn, userID); + commit(conn); + } catch (Exception e) { + Log.error("TradeJDBCDirect:getAccountProfileData -- error getting profile data", e); + rollBack(conn, e); + } finally { + releaseConn(conn); + } + return accountProfileData; + } + + private AccountProfileDataBean getAccountProfileData(Connection conn, String userID) throws Exception { + PreparedStatement stmt = getStatement(conn, getAccountProfileSQL); + stmt.setString(1, userID); + + ResultSet rs = stmt.executeQuery(); + + AccountProfileDataBean accountProfileData = getAccountProfileDataFromResultSet(rs); + stmt.close(); + return accountProfileData; + } + + private AccountProfileDataBean getAccountProfileData(Integer accountID) throws Exception { + AccountProfileDataBean accountProfileData = null; + Connection conn = null; + + try { + if (Log.doTrace()) + Log.trace("TradeJDBCDirect:getAccountProfileData", accountID); + + conn = getConn(); + accountProfileData = getAccountProfileData(conn, accountID); + commit(conn); + } catch (Exception e) { + Log.error("TradeJDBCDirect:getAccountProfileData -- error getting profile data", e); + rollBack(conn, e); + } finally { + releaseConn(conn); + } + return accountProfileData; + } + + private AccountProfileDataBean getAccountProfileData(Connection conn, Integer accountID) throws Exception { + PreparedStatement stmt = getStatement(conn, getAccountProfileForAccountSQL); + stmt.setInt(1, accountID.intValue()); + + ResultSet rs = stmt.executeQuery(); + + AccountProfileDataBean accountProfileData = getAccountProfileDataFromResultSet(rs); + stmt.close(); + return accountProfileData; + } + + /** + * @see TradeServices#updateAccountProfile(AccountProfileDataBean) + */ + public AccountProfileDataBean updateAccountProfile(AccountProfileDataBean profileData) throws Exception { + AccountProfileDataBean accountProfileData = null; + Connection conn = null; + + try { + if (Log.doTrace()) + Log.trace("TradeJDBCDirect:updateAccountProfileData - inSession(" + this.inSession + ")", profileData + .getUserID()); + + conn = getConn(); + updateAccountProfile(conn, profileData); + + accountProfileData = getAccountProfileData(conn, profileData.getUserID()); + commit(conn); + } catch (Exception e) { + Log.error("TradeJDBCDirect:getAccountProfileData -- error getting profile data", e); + rollBack(conn, e); + } finally { + releaseConn(conn); + } + return accountProfileData; + } + + private void creditAccountBalance(Connection conn, AccountDataBean accountData, BigDecimal credit) throws Exception { + PreparedStatement stmt = getStatement(conn, creditAccountBalanceSQL); + + stmt.setBigDecimal(1, credit); + stmt.setInt(2, accountData.getAccountID().intValue()); + + int count = stmt.executeUpdate(); + stmt.close(); + + } + + // Set Timestamp to zero to denote sell is inflight + // UPDATE -- could add a "status" attribute to holding + private void updateHoldingStatus(Connection conn, Integer holdingID, String symbol) throws Exception { + Timestamp ts = new Timestamp(0); + PreparedStatement stmt = getStatement(conn, "update holdingejb set purchasedate= ? where holdingid = ?"); + + stmt.setTimestamp(1, ts); + stmt.setInt(2, holdingID.intValue()); + int count = stmt.executeUpdate(); + stmt.close(); + } + + private void updateOrderStatus(Connection conn, Integer orderID, String status) throws Exception { + PreparedStatement stmt = getStatement(conn, updateOrderStatusSQL); + + stmt.setString(1, status); + stmt.setTimestamp(2, new Timestamp(System.currentTimeMillis())); + stmt.setInt(3, orderID.intValue()); + int count = stmt.executeUpdate(); + stmt.close(); + } + + private void updateOrderHolding(Connection conn, int orderID, int holdingID) throws Exception { + PreparedStatement stmt = getStatement(conn, updateOrderHoldingSQL); + + stmt.setInt(1, holdingID); + stmt.setInt(2, orderID); + int count = stmt.executeUpdate(); + stmt.close(); + } + + private void updateAccountProfile(Connection conn, AccountProfileDataBean profileData) throws Exception { + PreparedStatement stmt = getStatement(conn, updateAccountProfileSQL); + + stmt.setString(1, profileData.getPassword()); + stmt.setString(2, profileData.getFullName()); + stmt.setString(3, profileData.getAddress()); + stmt.setString(4, profileData.getEmail()); + stmt.setString(5, profileData.getCreditCard()); + stmt.setString(6, profileData.getUserID()); + + int count = stmt.executeUpdate(); + stmt.close(); + } + + private void updateQuoteVolume(Connection conn, QuoteDataBean quoteData, double quantity) throws Exception { + PreparedStatement stmt = getStatement(conn, updateQuoteVolumeSQL); + + stmt.setDouble(1, quantity); + stmt.setString(2, quoteData.getSymbol()); + + int count = stmt.executeUpdate(); + stmt.close(); + } + + public QuoteDataBean updateQuotePriceVolume(String symbol, BigDecimal changeFactor, double sharesTraded) + throws Exception { + return updateQuotePriceVolumeInt(symbol, changeFactor, sharesTraded, TradeConfig.getPublishQuotePriceChange()); + } + + /** + * Update a quote's price and volume + * + * @param symbol + * The PK of the quote + * @param changeFactor + * the percent to change the old price by (between 50% and 150%) + * @param sharedTraded + * the ammount to add to the current volume + * @param publishQuotePriceChange + * used by the PingJDBCWrite Primitive to ensure no JMS is used, should be true for all normal calls to + * this API + */ + public QuoteDataBean updateQuotePriceVolumeInt(String symbol, BigDecimal changeFactor, double sharesTraded, + boolean publishQuotePriceChange) throws Exception { + + if (TradeConfig.getUpdateQuotePrices() == false) + return new QuoteDataBean(); + + QuoteDataBean quoteData = null; + Connection conn = null; + + try { + if (Log.doTrace()) + Log.trace("TradeJDBCDirect:updateQuotePriceVolume - inSession(" + this.inSession + ")", symbol, + changeFactor, new Double(sharesTraded)); + + conn = getConn(); + + quoteData = getQuoteForUpdate(conn, symbol); + BigDecimal oldPrice = quoteData.getPrice(); + double newVolume = quoteData.getVolume() + sharesTraded; + + if (oldPrice.equals(TradeConfig.PENNY_STOCK_PRICE)) { + changeFactor = TradeConfig.PENNY_STOCK_RECOVERY_MIRACLE_MULTIPLIER; + } else if (oldPrice.compareTo(TradeConfig.MAXIMUM_STOCK_PRICE) > 0) { + changeFactor = TradeConfig.MAXIMUM_STOCK_SPLIT_MULTIPLIER; + } + + BigDecimal newPrice = changeFactor.multiply(oldPrice).setScale(2, BigDecimal.ROUND_HALF_UP); + + updateQuotePriceVolume(conn, quoteData.getSymbol(), newPrice, newVolume); + quoteData = getQuote(conn, symbol); + + commit(conn); + + } catch (Exception e) { + Log.error("TradeJDBCDirect:updateQuotePriceVolume -- error updating quote price/volume for symbol:" + symbol); + rollBack(conn, e); + throw e; + } finally { + releaseConn(conn); + } + return quoteData; + } + + private void updateQuotePriceVolume(Connection conn, String symbol, BigDecimal newPrice, double newVolume) + throws Exception { + + PreparedStatement stmt = getStatement(conn, updateQuotePriceVolumeSQL); + + stmt.setBigDecimal(1, newPrice); + stmt.setBigDecimal(2, newPrice); + stmt.setDouble(3, newVolume); + stmt.setString(4, symbol); + + int count = stmt.executeUpdate(); + stmt.close(); + } + + private void publishQuotePriceChange(QuoteDataBean quoteData, BigDecimal oldPrice, BigDecimal changeFactor, + double sharesTraded) throws Exception { + if (!TradeConfig.getPublishQuotePriceChange()) + return; + Log.error("TradeJDBCDirect:publishQuotePriceChange - is not implemented for this runtime mode"); + throw new UnsupportedOperationException("TradeJDBCDirect:publishQuotePriceChange- is not implemented for this runtime mode"); + } + + /** + * @see TradeServices#login(String, String) + */ + + public AccountDataBean login(String userID, String password) throws Exception { + + AccountDataBean accountData = null; + Connection conn = null; + try { + if (Log.doTrace()) + Log.trace("TradeJDBCDirect:login - inSession(" + this.inSession + ")", userID, password); + + conn = getConn(); + PreparedStatement stmt = getStatement(conn, getAccountProfileSQL); + stmt.setString(1, userID); + + ResultSet rs = stmt.executeQuery(); + if (!rs.next()) { + Log.error("TradeJDBCDirect:login -- failure to find account for" + userID); + throw new RuntimeException("Cannot find account for" + userID); + } + + String pw = rs.getString("passwd"); + stmt.close(); + if ((pw == null) || (pw.equals(password) == false)) { + String error = + "TradeJDBCDirect:Login failure for user: " + userID + "\n\tIncorrect password-->" + userID + ":" + + password; + Log.error(error); + throw new Exception(error); + } + + stmt = getStatement(conn, loginSQL); + stmt.setTimestamp(1, new Timestamp(System.currentTimeMillis())); + stmt.setString(2, userID); + + int rows = stmt.executeUpdate(); + // ?assert rows==1? + stmt.close(); + + stmt = getStatement(conn, getAccountForUserSQL); + stmt.setString(1, userID); + rs = stmt.executeQuery(); + + accountData = getAccountDataFromResultSet(rs); + + stmt.close(); + + commit(conn); + } catch (Exception e) { + Log.error("TradeJDBCDirect:login -- error logging in user", e); + rollBack(conn, e); + } finally { + releaseConn(conn); + } + return accountData; + + /* + * setLastLogin( new Timestamp(System.currentTimeMillis()) ); setLoginCount( getLoginCount() + 1 ); + */ + } + + /** + * @see TradeServices#logout(String) + */ + public void logout(String userID) throws Exception { + + Connection conn = null; + + if (Log.doActionTrace()) + Log.trace("TradeAction:logout", userID); + + if (Log.doTrace()) + Log.trace("TradeJDBCDirect:logout - inSession(" + this.inSession + ")", userID); + try { + conn = getConn(); + PreparedStatement stmt = getStatement(conn, logoutSQL); + stmt.setString(1, userID); + stmt.executeUpdate(); + stmt.close(); + + commit(conn); + } catch (Exception e) { + Log.error("TradeJDBCDirect:logout -- error logging out user", e); + rollBack(conn, e); + } finally { + releaseConn(conn); + } + } + + /** + * @see TradeServices#register(String, String, String, String, String, String, BigDecimal, boolean) + */ + + public AccountDataBean register(String userID, String password, String fullname, String address, String email, + String creditCard, BigDecimal openBalance) throws Exception { + + AccountDataBean accountData = null; + Connection conn = null; + try { + if (Log.doTrace()) + Log.traceEnter("TradeJDBCDirect:register - inSession(" + this.inSession + ")"); + + conn = getConn(); + PreparedStatement stmt = getStatement(conn, createAccountSQL); + + Integer accountID = KeySequenceDirect.getNextID(conn, "account", inSession, getInGlobalTxn()); + BigDecimal balance = openBalance; + Timestamp creationDate = new Timestamp(System.currentTimeMillis()); + Timestamp lastLogin = creationDate; + int loginCount = 0; + int logoutCount = 0; + + stmt.setInt(1, accountID.intValue()); + stmt.setTimestamp(2, creationDate); + stmt.setBigDecimal(3, openBalance); + stmt.setBigDecimal(4, balance); + stmt.setTimestamp(5, lastLogin); + stmt.setInt(6, loginCount); + stmt.setInt(7, logoutCount); + stmt.setString(8, userID); + stmt.executeUpdate(); + stmt.close(); + + stmt = getStatement(conn, createAccountProfileSQL); + stmt.setString(1, userID); + stmt.setString(2, password); + stmt.setString(3, fullname); + stmt.setString(4, address); + stmt.setString(5, email); + stmt.setString(6, creditCard); + stmt.executeUpdate(); + stmt.close(); + + commit(conn); + + accountData = + new AccountDataBean(accountID, loginCount, logoutCount, lastLogin, creationDate, balance, openBalance, + userID); + if (Log.doTrace()) + Log.traceExit("TradeJDBCDirect:register"); + } catch (Exception e) { + Log.error("TradeJDBCDirect:register -- error registering new user", e); + } finally { + releaseConn(conn); + } + return accountData; + } + + private AccountDataBean getAccountDataFromResultSet(ResultSet rs) throws Exception { + AccountDataBean accountData = null; + + if (!rs.next()) + Log.error("TradeJDBCDirect:getAccountDataFromResultSet -- cannot find account data"); + + else + accountData = + new AccountDataBean(new Integer(rs.getInt("accountID")), rs.getInt("loginCount"), rs + .getInt("logoutCount"), rs.getTimestamp("lastLogin"), rs.getTimestamp("creationDate"), rs + .getBigDecimal("balance"), rs.getBigDecimal("openBalance"), rs.getString("profile_userID")); + return accountData; + } + + private AccountProfileDataBean getAccountProfileDataFromResultSet(ResultSet rs) throws Exception { + AccountProfileDataBean accountProfileData = null; + + if (!rs.next()) + Log.error("TradeJDBCDirect:getAccountProfileDataFromResultSet -- cannot find accountprofile data"); + else + accountProfileData = + new AccountProfileDataBean(rs.getString("userID"), rs.getString("passwd"), rs.getString("fullName"), rs + .getString("address"), rs.getString("email"), rs.getString("creditCard")); + + return accountProfileData; + } + + private HoldingDataBean getHoldingDataFromResultSet(ResultSet rs) throws Exception { + HoldingDataBean holdingData = null; + + holdingData = + new HoldingDataBean(new Integer(rs.getInt("holdingID")), rs.getDouble("quantity"), rs + .getBigDecimal("purchasePrice"), rs.getTimestamp("purchaseDate"), rs.getString("quote_symbol")); + return holdingData; + } + + private QuoteDataBean getQuoteDataFromResultSet(ResultSet rs) throws Exception { + QuoteDataBean quoteData = null; + + quoteData = + new QuoteDataBean(rs.getString("symbol"), rs.getString("companyName"), rs.getDouble("volume"), rs + .getBigDecimal("price"), rs.getBigDecimal("open1"), rs.getBigDecimal("low"), rs.getBigDecimal("high"), + rs.getDouble("change1")); + return quoteData; + } + + private OrderDataBean getOrderDataFromResultSet(ResultSet rs) throws Exception { + OrderDataBean orderData = null; + + orderData = + new OrderDataBean(new Integer(rs.getInt("orderID")), rs.getString("orderType"), + rs.getString("orderStatus"), rs.getTimestamp("openDate"), rs.getTimestamp("completionDate"), rs + .getDouble("quantity"), rs.getBigDecimal("price"), rs.getBigDecimal("orderFee"), rs + .getString("quote_symbol")); + return orderData; + } + + public String checkDBProductName() throws Exception { + Connection conn = null; + String dbProductName = null; + + try { + if (Log.doTrace()) + Log.traceEnter("TradeJDBCDirect:checkDBProductName"); + + conn = getConn(); + DatabaseMetaData dbmd = conn.getMetaData(); + dbProductName = dbmd.getDatabaseProductName(); + } catch (SQLException e) { + Log.error(e, "TradeJDBCDirect:checkDBProductName() -- Error checking the Daytrader Database Product Name"); + } finally { + releaseConn(conn); + } + return dbProductName; + } + + public boolean recreateDBTables(Object[] sqlBuffer, java.io.PrintWriter out) throws Exception { + // Clear MDB Statistics + MDBStats.getInstance().reset(); + + Connection conn = null; + boolean success = false; + try { + if (Log.doTrace()) + Log.traceEnter("TradeJDBCDirect:recreateDBTables"); + + conn = getConn(); + Statement stmt = conn.createStatement(); + int bufferLength = sqlBuffer.length; + for (int i = 0; i < bufferLength; i++) { + try { + stmt.executeUpdate((String) sqlBuffer[i]); + // commit(conn); + } catch (SQLException ex) { + // Ignore DROP statements as tables won't always exist. + if (((String) sqlBuffer[i]).indexOf("DROP TABLE") < 0) { + Log.error( + "TradeJDBCDirect:recreateDBTables SQL Exception thrown on executing the foll sql command: " + + sqlBuffer[i], ex); + out.println("
SQL Exception thrown on executing the foll sql command: " + sqlBuffer[i] + + " . Check log for details.
"); + } + } + } + stmt.close(); + commit(conn); + success = true; + } catch (Exception e) { + Log.error(e, "TradeJDBCDirect:recreateDBTables() -- Error dropping and recreating the database tables"); + } finally { + releaseConn(conn); + } + return success; + } + + public RunStatsDataBean resetTrade(boolean deleteAll) throws Exception { + // Clear MDB Statistics + MDBStats.getInstance().reset(); + // Reset Trade + + RunStatsDataBean runStatsData = new RunStatsDataBean(); + Connection conn = null; + try { + if (Log.doTrace()) + Log.traceEnter("TradeJDBCDirect:resetTrade deleteAll rows=" + deleteAll); + + conn = getConn(); + PreparedStatement stmt = null; + ResultSet rs = null; + + if (deleteAll) { + try { + stmt = getStatement(conn, "delete from quoteejb"); + stmt.executeUpdate(); + stmt.close(); + stmt = getStatement(conn, "delete from accountejb"); + stmt.executeUpdate(); + stmt.close(); + stmt = getStatement(conn, "delete from accountprofileejb"); + stmt.executeUpdate(); + stmt.close(); + stmt = getStatement(conn, "delete from holdingejb"); + stmt.executeUpdate(); + stmt.close(); + stmt = getStatement(conn, "delete from orderejb"); + stmt.executeUpdate(); + stmt.close(); + // FUTURE: - DuplicateKeyException - For now, don't start at + // zero as KeySequenceDirect and KeySequenceBean will still + // give out + // the cached Block and then notice this change. Better + // solution is + // to signal both classes to drop their cached blocks + // stmt = getStatement(conn, "delete from keygenejb"); + // stmt.executeUpdate(); + // stmt.close(); + commit(conn); + } catch (Exception e) { + Log + .error(e, + "TradeJDBCDirect:resetTrade(deleteAll) -- Error deleting Trade users and stock from the Trade database"); + } + return runStatsData; + } + + stmt = getStatement(conn, "delete from holdingejb where holdingejb.account_accountid is null"); + int x = stmt.executeUpdate(); + stmt.close(); + + // Count and Delete newly registered users (users w/ id that start + // "ru:%": + stmt = getStatement(conn, "delete from accountprofileejb where userid like 'ru:%'"); + int rowCount = stmt.executeUpdate(); + stmt.close(); + + stmt = + getStatement( + conn, + "delete from orderejb where account_accountid in (select accountid from accountejb a where a.profile_userid like 'ru:%')"); + rowCount = stmt.executeUpdate(); + stmt.close(); + + stmt = + getStatement( + conn, + "delete from holdingejb where account_accountid in (select accountid from accountejb a where a.profile_userid like 'ru:%')"); + rowCount = stmt.executeUpdate(); + stmt.close(); + + stmt = getStatement(conn, "delete from accountejb where profile_userid like 'ru:%'"); + int newUserCount = stmt.executeUpdate(); + runStatsData.setNewUserCount(newUserCount); + stmt.close(); + + // Count of trade users + stmt = + getStatement(conn, + "select count(accountid) as \"tradeUserCount\" from accountejb a where a.profile_userid like 'uid:%'"); + rs = stmt.executeQuery(); + rs.next(); + int tradeUserCount = rs.getInt("tradeUserCount"); + runStatsData.setTradeUserCount(tradeUserCount); + stmt.close(); + + rs.close(); + // Count of trade stocks + stmt = + getStatement(conn, + "select count(symbol) as \"tradeStockCount\" from quoteejb a where a.symbol like 's:%'"); + rs = stmt.executeQuery(); + rs.next(); + int tradeStockCount = rs.getInt("tradeStockCount"); + runStatsData.setTradeStockCount(tradeStockCount); + stmt.close(); + + // Count of trade users login, logout + stmt = + getStatement( + conn, + "select sum(loginCount) as \"sumLoginCount\", sum(logoutCount) as \"sumLogoutCount\" from accountejb a where a.profile_userID like 'uid:%'"); + rs = stmt.executeQuery(); + rs.next(); + int sumLoginCount = rs.getInt("sumLoginCount"); + int sumLogoutCount = rs.getInt("sumLogoutCount"); + runStatsData.setSumLoginCount(sumLoginCount); + runStatsData.setSumLogoutCount(sumLogoutCount); + stmt.close(); + + rs.close(); + // Update logoutcount and loginCount back to zero + + stmt = + getStatement(conn, "update accountejb set logoutCount=0,loginCount=0 where profile_userID like 'uid:%'"); + rowCount = stmt.executeUpdate(); + stmt.close(); + + // count holdings for trade users + stmt = + getStatement(conn, + "select count(holdingid) as \"holdingCount\" from holdingejb h where h.account_accountid in " + + "(select accountid from accountejb a where a.profile_userid like 'uid:%')"); + + rs = stmt.executeQuery(); + rs.next(); + int holdingCount = rs.getInt("holdingCount"); + runStatsData.setHoldingCount(holdingCount); + stmt.close(); + rs.close(); + + // count orders for trade users + stmt = + getStatement(conn, + "select count(orderid) as \"orderCount\" from orderejb o where o.account_accountid in " + + "(select accountid from accountejb a where a.profile_userid like 'uid:%')"); + + rs = stmt.executeQuery(); + rs.next(); + int orderCount = rs.getInt("orderCount"); + runStatsData.setOrderCount(orderCount); + stmt.close(); + rs.close(); + + // count orders by type for trade users + stmt = + getStatement(conn, + "select count(orderid) \"buyOrderCount\"from orderejb o where (o.account_accountid in " + + "(select accountid from accountejb a where a.profile_userid like 'uid:%')) AND " + + " (o.orderType='buy')"); + + rs = stmt.executeQuery(); + rs.next(); + int buyOrderCount = rs.getInt("buyOrderCount"); + runStatsData.setBuyOrderCount(buyOrderCount); + stmt.close(); + rs.close(); + + // count orders by type for trade users + stmt = + getStatement(conn, + "select count(orderid) \"sellOrderCount\"from orderejb o where (o.account_accountid in " + + "(select accountid from accountejb a where a.profile_userid like 'uid:%')) AND " + + " (o.orderType='sell')"); + + rs = stmt.executeQuery(); + rs.next(); + int sellOrderCount = rs.getInt("sellOrderCount"); + runStatsData.setSellOrderCount(sellOrderCount); + stmt.close(); + rs.close(); + + // Delete cancelled orders + stmt = getStatement(conn, "delete from orderejb where orderStatus='cancelled'"); + int cancelledOrderCount = stmt.executeUpdate(); + runStatsData.setCancelledOrderCount(cancelledOrderCount); + stmt.close(); + rs.close(); + + // count open orders by type for trade users + stmt = + getStatement(conn, + "select count(orderid) \"openOrderCount\"from orderejb o where (o.account_accountid in " + + "(select accountid from accountejb a where a.profile_userid like 'uid:%')) AND " + + " (o.orderStatus='open')"); + + rs = stmt.executeQuery(); + rs.next(); + int openOrderCount = rs.getInt("openOrderCount"); + runStatsData.setOpenOrderCount(openOrderCount); + + stmt.close(); + rs.close(); + // Delete orders for holding which have been purchased and sold + stmt = getStatement(conn, "delete from orderejb where holding_holdingid is null"); + int deletedOrderCount = stmt.executeUpdate(); + runStatsData.setDeletedOrderCount(deletedOrderCount); + stmt.close(); + rs.close(); + + commit(conn); + + System.out.println("TradeJDBCDirect:reset Run stats data\n\n" + runStatsData); + } catch (Exception e) { + Log.error(e, "Failed to reset Trade"); + rollBack(conn, e); + throw e; + } finally { + releaseConn(conn); + } + return runStatsData; + + } + + private void releaseConn(Connection conn) throws Exception { + try { + if (conn != null) { + conn.close(); + if (Log.doTrace()) { + synchronized (lock) { + connCount--; + } + Log.trace("TradeJDBCDirect:releaseConn -- connection closed, connCount=" + connCount); + } + } + } catch (Exception e) { + Log.error("TradeJDBCDirect:releaseConnection -- failed to close connection", e); + } + } + + /* + * Lookup the TradeData datasource + */ + private void getDataSource() throws Exception { + datasource = (DataSource) context.lookup(dsName); + } + + /* + * Allocate a new connection to the datasource + */ + private static int connCount = 0; + + private static Integer lock = new Integer(0); + + private Connection getConn() throws Exception { + + Connection conn = null; + if (datasource == null) + getDataSource(); + conn = datasource.getConnection(); + // fix PingServlet2Session2JDBC failure + if (!this.inSession) { + conn.setAutoCommit(false); + } + if (Log.doTrace()) { + synchronized (lock) { + connCount++; + } + Log.trace("TradeJDBCDirect:getConn -- new connection allocated, IsolationLevel=" + + conn.getTransactionIsolation() + " connectionCount = " + connCount); + } + + return conn; + } + + /* + * Commit the provided connection if not under Global Transaction scope - conn.commit() is not allowed in a global + * transaction. the txn manager will perform the commit + */ + private void commit(Connection conn) throws Exception { + if (!inSession) { + if ((getInGlobalTxn() == false) && (conn != null)) + conn.commit(); + } + } + + /* + * Rollback the statement for the given connection + */ + private void rollBack(Connection conn, Exception e) throws Exception { + if (!inSession) { + Log.log("TradeJDBCDirect:rollBack -- rolling back conn due to previously caught exception -- inGlobalTxn=" + + getInGlobalTxn()); + if ((getInGlobalTxn() == false) && (conn != null)) + conn.rollback(); + else + throw e; // Throw the exception + // so the Global txn manager will rollBack + } + } + + /* + * Allocate a new prepared statment for this connection + */ + private PreparedStatement getStatement(Connection conn, String sql) throws Exception { + return conn.prepareStatement(sql); + } + + private PreparedStatement getStatement(Connection conn, String sql, int type, int concurrency) throws Exception { + return conn.prepareStatement(sql, type, concurrency); + } + + private static final String createQuoteSQL = + "insert into quoteejb " + "( symbol, companyName, volume, price, open1, low, high, change1 ) " + + "VALUES ( ? , ? , ? , ? , ? , ? , ? , ? )"; + + private static final String createAccountSQL = + "insert into accountejb " + + "( accountid, creationDate, openBalance, balance, lastLogin, loginCount, logoutCount, profile_userid) " + + "VALUES ( ? , ? , ? , ? , ? , ? , ? , ? )"; + + private static final String createAccountProfileSQL = + "insert into accountprofileejb " + "( userid, passwd, fullname, address, email, creditcard ) " + + "VALUES ( ? , ? , ? , ? , ? , ? )"; + + private static final String createHoldingSQL = + "insert into holdingejb " + + "( holdingid, purchaseDate, purchasePrice, quantity, quote_symbol, account_accountid ) " + + "VALUES ( ? , ? , ? , ? , ? , ? )"; + + private static final String createOrderSQL = + "insert into orderejb " + + "( orderid, ordertype, orderstatus, opendate, quantity, price, orderfee, account_accountid, holding_holdingid, quote_symbol) " + + "VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ?)"; + + private static final String removeHoldingSQL = "delete from holdingejb where holdingid = ?"; + + private static final String removeHoldingFromOrderSQL = + "update orderejb set holding_holdingid=null where holding_holdingid = ?"; + + private final static String updateAccountProfileSQL = + "update accountprofileejb set " + "passwd = ?, fullname = ?, address = ?, email = ?, creditcard = ? " + + "where userid = (select profile_userid from accountejb a " + "where a.profile_userid=?)"; + + private final static String loginSQL = + "update accountejb set lastLogin=?, logincount=logincount+1 " + "where profile_userid=?"; + + private static final String logoutSQL = + "update accountejb set logoutcount=logoutcount+1 " + "where profile_userid=?"; + + private static final String getAccountSQL = "select * from accountejb a where a.accountid = ?"; + + private static final String getAccountForUpdateSQL = "select * from accountejb a where a.accountid = ? for update"; + + private final static String getAccountProfileSQL = + "select * from accountprofileejb ap where ap.userid = " + + "(select profile_userid from accountejb a where a.profile_userid=?)"; + + private final static String getAccountProfileForAccountSQL = + "select * from accountprofileejb ap where ap.userid = " + + "(select profile_userid from accountejb a where a.accountid=?)"; + + private static final String getAccountForUserSQL = + "select * from accountejb a where a.profile_userid = " + + "( select userid from accountprofileejb ap where ap.userid = ?)"; + + private static final String getAccountForUserForUpdateSQL = + "select * from accountejb a where a.profile_userid = " + + "( select userid from accountprofileejb ap where ap.userid = ?) for update"; + + private static final String getHoldingSQL = "select * from holdingejb h where h.holdingid = ?"; + + private static final String getHoldingsForUserSQL = + "select * from holdingejb h where h.account_accountid = " + + "(select a.accountid from accountejb a where a.profile_userid = ?)"; + + private static final String getOrderSQL = "select * from orderejb o where o.orderid = ?"; + + private static final String getOrdersByUserSQL = + "select * from orderejb o where o.account_accountid = " + + "(select a.accountid from accountejb a where a.profile_userid = ?)"; + + private static final String getClosedOrdersSQL = + "select * from orderejb o " + "where o.orderstatus = 'closed' AND o.account_accountid = " + + "(select a.accountid from accountejb a where a.profile_userid = ?)"; + + private static final String getQuoteSQL = "select * from quoteejb q where q.symbol=?"; + + private static final String getAllQuotesSQL = "select * from quoteejb q"; + + private static final String getQuoteForUpdateSQL = "select * from quoteejb q where q.symbol=? For Update"; + + private static final String getTSIAQuotesOrderByChangeSQL = + "select * from quoteejb q " + "where q.symbol like 's:1__' order by q.change1"; + + private static final String getTSIASQL = + "select SUM(price)/count(*) as TSIA from quoteejb q " + "where q.symbol like 's:1__'"; + + private static final String getOpenTSIASQL = + "select SUM(open1)/count(*) as openTSIA from quoteejb q " + "where q.symbol like 's:1__'"; + + private static final String getTSIATotalVolumeSQL = + "select SUM(volume) as totalVolume from quoteejb q " + "where q.symbol like 's:1__'"; + + private static final String creditAccountBalanceSQL = + "update accountejb set " + "balance = balance + ? " + "where accountid = ?"; + + private static final String updateOrderStatusSQL = + "update orderejb set " + "orderstatus = ?, completiondate = ? " + "where orderid = ?"; + + private static final String updateOrderHoldingSQL = + "update orderejb set " + "holding_holdingID = ? " + "where orderid = ?"; + + private static final String updateQuoteVolumeSQL = + "update quoteejb set " + "volume = volume + ? " + "where symbol = ?"; + + private static final String updateQuotePriceVolumeSQL = + "update quoteejb set " + "price = ?, change1 = ? - open1, volume = ? " + "where symbol = ?"; + + private static boolean initialized = false; + + public static synchronized void init() { + if (initialized) + return; + if (Log.doTrace()) + Log.trace("TradeJDBCDirect:init -- *** initializing"); + try { + if (Log.doTrace()) + Log.trace("TradeJDBCDirect: init"); + context = new InitialContext(); + datasource = (DataSource) context.lookup(dsName); + } catch (Exception e) { + Log.error("TradeJDBCDirect:init -- error on JNDI lookups of DataSource -- TradeJDBCDirect will not work", e); + return; + } + + TradeConfig.setPublishQuotePriceChange(false); + + if (Log.doTrace()) + Log.trace("TradeJDBCDirect:init -- +++ initialized"); + + initialized = true; + } + + public static void destroy() { + try { + if (!initialized) + return; + Log.trace("TradeJDBCDirect:destroy"); + } catch (Exception e) { + Log.error("TradeJDBCDirect:destroy", e); + } + } + + /** + * Gets the inGlobalTxn + * + * @return Returns a boolean + */ + private boolean getInGlobalTxn() { + return inGlobalTxn; + } + + /** + * Sets the inGlobalTxn + * + * @param inGlobalTxn + * The inGlobalTxn to set + */ + private void setInGlobalTxn(boolean inGlobalTxn) { + this.inGlobalTxn = inGlobalTxn; + } + + /** + * Get mode - returns the persistence mode (TradeConfig.JDBC) + * + * @return int mode + */ + public int getMode() { + return TradeConfig.JDBC; + } + +}