You are viewing a plain text version of this content. The canonical link for it is here.
Posted to stonehenge-commits@incubator.apache.org by ch...@apache.org on 2010/06/29 06:33:57 UTC
svn commit: r958838 [6/42] - in /incubator/stonehenge/trunk/stocktrader:
dotnet/ dotnet/setup_utilities/Util/ metro/ metro/active_sts/
metro/active_sts/etc/
metro/active_sts/src/org/apache/stonehenge/stocktrader/sts/
metro/business_service/ metro/busin...
Modified: incubator/stonehenge/trunk/stocktrader/metro/common/src/org/apache/stonehenge/stocktrader/mysql/MySQLCustomerDAO.java
URL: http://svn.apache.org/viewvc/incubator/stonehenge/trunk/stocktrader/metro/common/src/org/apache/stonehenge/stocktrader/mysql/MySQLCustomerDAO.java?rev=958838&r1=958837&r2=958838&view=diff
==============================================================================
--- incubator/stonehenge/trunk/stocktrader/metro/common/src/org/apache/stonehenge/stocktrader/mysql/MySQLCustomerDAO.java (original)
+++ incubator/stonehenge/trunk/stocktrader/metro/common/src/org/apache/stonehenge/stocktrader/mysql/MySQLCustomerDAO.java Tue Jun 29 06:33:54 2010
@@ -1,605 +1,605 @@
-/*
- * 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.stonehenge.stocktrader.mysql;
-
-import java.math.BigDecimal;
-import java.sql.Connection;
-import java.sql.PreparedStatement;
-import java.sql.ResultSet;
-import java.sql.SQLException;
-import java.util.ArrayList;
-import java.util.Calendar;
-import java.util.List;
-import org.apache.commons.logging.Log;
-import org.apache.commons.logging.LogFactory;
-import org.apache.stonehenge.stocktrader.CustomAccountBean;
-import org.apache.stonehenge.stocktrader.CustomAccountProfileBean;
-import org.apache.stonehenge.stocktrader.CustomHoldingBean;
-import org.apache.stonehenge.stocktrader.CustomOrderBean;
-import org.apache.stonehenge.stocktrader.mysql.AbstractMySQLDAO;
-import org.apache.stonehenge.stocktrader.dal.CustomerDAO;
-import org.apache.stonehenge.stocktrader.dal.DAOException;
-import org.apache.stonehenge.stocktrader.util.StockTraderUtility;
-
-public class MySQLCustomerDAO extends AbstractMySQLDAO implements CustomerDAO {
- private static final Log logger = LogFactory.getLog(MySQLCustomerDAO.class);
-
- private static final String SQL_DEBIT_ACCOUNT = "UPDATE account SET balance= balance - ? WHERE accountid = ?";
- private static final String SQL_SELECT_HOLDING_LOCK = "SELECT h.account_accountid, h.holdingid, h.quantity, h.purchaseprice, h.purchasedate, h.quote_symbol FROM holding as h INNER JOIN orders as o on h.holdingid = o.holding_holdingid WHERE (o.orderid = ?)";
- private static final String SQL_SELECT_HOLDING_NOLOCK = "SELECT account_accountid, quantity, purchaseprice, purchasedate, quote_symbol FROM holding WHERE holdingid = ? AND account_accountid = (SELECT accountid FROM account WHERE profile_userid = ?)";
- private static final String SQL_SELECT_CUSTOMER_PROFILE_BY_USERID = "SELECT userid, password, fullname, address, email, creditcard FROM accountprofile WHERE userid = ?";
- private static final String SQL_UPDATE_CUSTOMER_LOGIN = "UPDATE account SET logincount = logincount + 1, lastlogin = now() where profile_userid = ?";
- private static final String SQL_SELECT_CUSTOMER_LOGIN = "SELECT accountid, creationdate, openbalance, logoutcount, balance, lastlogin, logincount FROM account WHERE profile_userid = ?";
- private static final String SQL_UPDATE_LOGOUT = "UPDATE account SET logoutcount = logoutcount + 1 WHERE profile_userid = ?";
- private static final String SQL_SELECT_GET_CUSTOMER_BY_USERID = "SELECT account.ACCOUNTID, account.PROFILE_USERID, account.CREATIONDATE, account.OPENBALANCE, account.LOGOUTCOUNT, account.BALANCE, account.LASTLOGIN, account.LOGINCOUNT FROM account WHERE account.PROFILE_USERID = ?";
- private static final String SQL_SELECT_ORDERS_BY_ID = " o.orderid, o.ordertype, o.orderstatus, o.opendate, o.completiondate, o.quantity, o.price, o.orderfee, o.quote_symbol FROM orders o WHERE o.account_accountid = (SELECT a.accountid FROM account a WHERE a.profile_userid = ?) ORDER BY o.orderid DESC";
- private static final String SQL_SELECT_CLOSED_ORDERS = "SELECT orderid, ordertype, orderstatus, completiondate, opendate, quantity, price, orderfee, quote_symbol FROM orders WHERE account_accountid = (SELECT accountid FROM account WHERE profile_userid = ?) AND orderstatus = 'closed'";
- private static final String SQL_UPDATE_CLOSED_ORDERS = "UPDATE orders SET orderstatus = 'completed' WHERE orderstatus = 'closed' AND account_accountid = (SELECT accountid FROM account WHERE profile_userid = ?)";
- private static final String SQL_INSERT_ACCOUNT_PROFILE = "INSERT INTO accountprofile VALUES (?, ?, ?, ?, ?, ?)";
- private static final String SQL_INSERT_ACCOUNT = "INSERT INTO account (creationdate, openbalance, logoutcount, balance, lastlogin, logincount, profile_userid, accountid) VALUES (now(), ?, ?, ?, ?, ?, ?, null); SELECT LAST_INSERT_ID();";
- private static final String SQL_UPDATE_ACCOUNT_PROFILE = "UPDATE accountprofile SET address = ?, password = ?, email = ?, creditcard = ?, fullname = ? WHERE userid = ?";
- private static final String SQL_SELECT_HOLDINGS = "SELECT holdingid, quantity, purchaseprice, purchasedate, quote_symbol, account_accountid FROM holding WHERE account_accountid = (SELECT accountid FROM account WHERE profile_userid = ?) ORDER BY holdingid DESC";
-
- public MySQLCustomerDAO(Connection sqlConnection) throws DAOException {
- super(sqlConnection);
- }
-
- public CustomHoldingBean getHoldingForUpdate(int orderId) throws DAOException {
- if (logger.isDebugEnabled()) {
- logger.debug("MySQLCustomerDAO.getHoldingForUpdate(int)\nOrder ID :" + orderId);
- }
-
- CustomHoldingBean holding = null;
- PreparedStatement selectHoldingLockStat = null;
- try {
- selectHoldingLockStat = sqlConnection.prepareStatement(SQL_SELECT_HOLDING_LOCK);
- selectHoldingLockStat.setInt(1, orderId);
- ResultSet rs = selectHoldingLockStat.executeQuery();
- if (rs.next()) {
- try {
- holding = new CustomHoldingBean(
- rs.getInt(1),
- rs.getInt(2),
- rs.getDouble(3),
- rs.getBigDecimal(4),
- StockTraderUtility.convertToCalendar(rs.getDate(5)),
- rs.getString(6));
- return holding;
- } finally {
- try {
- rs.close();
- } catch (SQLException e) {
- logger.debug("", e);
- }
- }
- }
- } catch (SQLException e) {
- throw new DAOException("Exception is thrown when selecting the holding entry for order ID :" + orderId, e);
- } finally {
- if (selectHoldingLockStat != null) {
- try {
- selectHoldingLockStat.close();
- } catch (SQLException e) {
- logger.debug("", e);
- }
- }
- }
- return holding;
- }
-
- public CustomHoldingBean getHolding(String userId, int holdingID) throws DAOException {
- if (logger.isDebugEnabled()) {
- logger.debug("MSSQLCustomerDAO.getHolding(String,int)\nUserID :" + userId + "\nOrder ID :" + holdingID);
- }
- CustomHoldingBean holding = null;
- PreparedStatement selectHoldingNoLockStat = null;
- try {
- selectHoldingNoLockStat = sqlConnection.prepareStatement(SQL_SELECT_HOLDING_NOLOCK);
- selectHoldingNoLockStat.setInt(1, holdingID);
- selectHoldingNoLockStat.setString(2, userId);
-
- ResultSet rs = selectHoldingNoLockStat.executeQuery();
- if (rs.next()) {
- try {
- holding = new CustomHoldingBean(
- rs.getInt(1),
- holdingID,
- rs.getDouble(2),
- rs.getBigDecimal(3),
- StockTraderUtility.convertToCalendar(rs.getDate(4)),
- rs.getString(5));
- return holding;
- } finally {
- try {
- rs.close();
- } catch (SQLException e) {
- logger.debug("", e);
- }
- }
- }
- } catch (SQLException e) {
- logger.debug("", e);
- throw new DAOException("Exception is thrown when selecting the holding entry for userID :" + userId + " and orderID :" + holdingID, e);
-
- } finally {
- if (selectHoldingNoLockStat != null) {
- try {
- selectHoldingNoLockStat.close();
- } catch (SQLException e) {
- logger.debug("", e);
- }
- }
- }
- return holding;
- }
-
- public void updateAccountBalance(int accountId, BigDecimal total) throws DAOException {
- if (logger.isDebugEnabled()) {
- logger.debug("MySQLCustomerDAO.updateAccoutBalance(int,BigDecimal)\n Account ID :" + accountId + "\nTotal :" + total);
- }
- PreparedStatement debitAccountStat = null;
- try {
- debitAccountStat = sqlConnection.prepareStatement(SQL_DEBIT_ACCOUNT);
- debitAccountStat.setBigDecimal(1, total);
- debitAccountStat.setInt(2, accountId);
- debitAccountStat.executeUpdate();
-
- } catch (SQLException e) {
- throw new DAOException("Excpetion is thrown when updating the account balance for accountID :" + accountId + " total :" + total, e);
- } finally {
- if (debitAccountStat != null) {
- try {
- debitAccountStat.close();
- } catch (SQLException e) {
- logger.debug("", e);
- }
- }
- }
- }
-
- public CustomAccountBean login(String userId, String password) throws DAOException {
- PreparedStatement selectCustomerProfileByUserId = null;
- PreparedStatement updateCustomerLogin = null;
- PreparedStatement selectCustomerLogin = null;
- try {
- selectCustomerProfileByUserId = sqlConnection.prepareStatement(SQL_SELECT_CUSTOMER_PROFILE_BY_USERID);
- selectCustomerProfileByUserId.setString(1, userId);
- ResultSet customerProfileRS = selectCustomerProfileByUserId.executeQuery();
- if (customerProfileRS.next()) {
- try {
- String userPassword = customerProfileRS.getString(2);
- if (userPassword.equals(password)) {
- try {
- updateCustomerLogin = sqlConnection.prepareStatement(SQL_UPDATE_CUSTOMER_LOGIN);
- updateCustomerLogin.setString(1, userId);
- updateCustomerLogin.executeUpdate();
- selectCustomerLogin = sqlConnection.prepareStatement(SQL_SELECT_CUSTOMER_LOGIN);
- selectCustomerLogin.setString(1, userId);
- ResultSet rs = selectCustomerLogin.executeQuery();
- if (rs.next()) {
- try {
- CustomAccountBean accountData = new CustomAccountBean(
- rs.getInt(1),
- userId,
- StockTraderUtility.convertToCalendar(rs.getDate(2)),
- rs.getBigDecimal(3),
- rs.getInt(4),
- rs.getBigDecimal(5),
- StockTraderUtility.convertToCalendar(rs.getDate(6)),
- rs.getInt(7) + 1);
- return accountData;
- } finally {
- try {
- rs.close();
- } catch (SQLException e) {
- logger.debug("", e);
- }
- }
- }
- } catch (SQLException e) {
- throw new DAOException("", e);
- } finally {
- if (updateCustomerLogin != null) {
- try {
- updateCustomerLogin.close();
- } catch (SQLException e) {
- logger.debug("", e);
- }
- }
- if (selectCustomerLogin != null) {
- try {
- selectCustomerLogin.close();
- } catch (SQLException e) {
- logger.debug("", e);
- }
- }
- }
- }
- } finally {
- try {
- customerProfileRS.close();
- } catch (SQLException e) {
- logger.debug("", e);
- }
- }
- }
-
- } catch (SQLException e) {
- throw new DAOException("", e);
- } finally {
- if (selectCustomerProfileByUserId != null) {
- try {
- selectCustomerProfileByUserId.close();
- } catch (SQLException e) {
- logger.debug("", e);
- }
- }
- }
- return null;
- }
-
- public void logoutUser(String userId) throws DAOException {
- PreparedStatement updateLogout = null;
- try {
- updateLogout = sqlConnection.prepareStatement(SQL_UPDATE_LOGOUT);
- updateLogout.setString(1, userId);
- updateLogout.executeUpdate();
- } catch (SQLException e) {
- throw new DAOException("", e);
- } finally {
- if (updateLogout != null) {
- try {
- updateLogout.close();
- } catch (SQLException e) {
- logger.debug("", e);
- }
- }
- }
- }
-
- public CustomAccountBean getCustomerByUserId(String userId) throws DAOException {
- PreparedStatement getCustomerByUserId = null;
-
- try {
- getCustomerByUserId = sqlConnection.prepareStatement(SQL_SELECT_GET_CUSTOMER_BY_USERID);
- getCustomerByUserId.setString(1, userId);
- ResultSet rs = getCustomerByUserId.executeQuery();
- if (rs.next()) {
- try {
- CustomAccountBean bean = new CustomAccountBean(
- rs.getInt(1),
- rs.getString(2),
- StockTraderUtility.convertToCalendar(rs.getDate(3)),
- rs.getBigDecimal(4),
- rs.getInt(5),
- rs.getBigDecimal(6),
- StockTraderUtility.convertToCalendar(rs.getDate(7)),
- rs.getInt(8));
- return bean;
- } finally {
- try {
- rs.close();
- } catch (SQLException e) {
- logger.debug("", e);
- }
- }
- }
- } catch (SQLException e) {
- throw new DAOException("", e);
- } finally {
- if (getCustomerByUserId != null) {
- try {
- getCustomerByUserId.close();
- } catch (SQLException e) {
- logger.debug("", e);
- }
- }
- }
- return null;
- }
-
- public CustomAccountProfileBean getAccountProfileData(String userId) throws DAOException {
-
- PreparedStatement customerProfileByUserId = null;
- try {
- customerProfileByUserId = sqlConnection.prepareStatement(SQL_SELECT_CUSTOMER_PROFILE_BY_USERID);
- customerProfileByUserId.setString(1, userId);
- ResultSet rs = customerProfileByUserId.executeQuery();
- if (rs.next()) {
- try {
- CustomAccountProfileBean accountProfileDataBean = new CustomAccountProfileBean(
- rs.getString(1),
- rs.getString(2),
- rs.getString(3),
- rs.getString(4),
- rs.getString(5),
- rs.getString(6));
- return accountProfileDataBean;
- } finally {
- try {
- rs.close();
- } catch (SQLException e) {
- logger.debug("", e);
- }
- }
- }
- } catch (SQLException e) {
- throw new DAOException("", e);
- } finally {
- if (customerProfileByUserId != null) {
- try {
- customerProfileByUserId.close();
- } catch (SQLException e) {
- logger.debug("", e);
- }
- }
- }
- return null;
- }
-
- public List<CustomOrderBean> getOrders(String userId, boolean top, int maxTop, int maxDefault) throws DAOException {
- PreparedStatement selectOrdersById = null;
- try {
- String sqlQuery;
- if (top) {
- sqlQuery = "SELECT " + SQL_SELECT_ORDERS_BY_ID + " LIMIT " + maxTop;
- } else {
- sqlQuery = "SELECT " + SQL_SELECT_ORDERS_BY_ID + " LIMIT " + maxDefault;
- }
- selectOrdersById = sqlConnection.prepareStatement(sqlQuery);
- selectOrdersById.setString(1, userId);
- ResultSet rs = selectOrdersById.executeQuery();
- List<CustomOrderBean> orders = new ArrayList<CustomOrderBean>();
-
- try {
- while (rs.next()) {
- int orderId = rs.getInt(1);
- Calendar openDate = StockTraderUtility.convertToCalendar(rs.getDate(4));
- Calendar completionDate = null;
- try {
- if (rs.getDate(5) != null) {
- completionDate = StockTraderUtility.convertToCalendar(rs.getDate(5));
- } else {
- completionDate = Calendar.getInstance();
- completionDate.setTimeInMillis(0);
- }
- } catch (SQLException e) {
- logger.debug("", e);
- completionDate = Calendar.getInstance();
- completionDate.setTimeInMillis(0);
- }
-
- CustomOrderBean orderBean = new CustomOrderBean(
- orderId,
- rs.getString(2),
- rs.getString(3),
- openDate,
- completionDate,
- rs.getDouble(6),
- rs.getBigDecimal(7),
- rs.getBigDecimal(8),
- rs.getString(9));
- orders.add(orderBean);
- }
-
- } finally {
- try {
- rs.close();
- } catch (SQLException e) {
- logger.debug("", e);
- }
- }
- return orders;
-
- } catch (SQLException e) {
- throw new DAOException("", e);
- } finally {
- if (selectOrdersById != null) {
- try {
- selectOrdersById.close();
- } catch (SQLException e) {
- logger.debug("", e);
- }
- }
- }
- }
-
- public List<CustomOrderBean> getClosedOrders(String userId) throws DAOException {
- PreparedStatement selectClosedOrders = null;
- PreparedStatement updateClosedOrders = null;
- try {
- selectClosedOrders = sqlConnection.prepareStatement(SQL_SELECT_CLOSED_ORDERS);
- selectClosedOrders.setString(1, userId);
- ResultSet rs = selectClosedOrders.executeQuery();
- List<CustomOrderBean> closedOrders = new ArrayList<CustomOrderBean>();
-
- try {
- while (rs.next()) {
- int orderId = rs.getInt(1);
- Calendar openDate = StockTraderUtility.convertToCalendar(rs.getDate(4));
- Calendar completionDate = null;
- try {
- completionDate = StockTraderUtility.convertToCalendar(rs.getDate(5));
- } catch (SQLException e) {
- logger.debug("", e);
- completionDate = Calendar.getInstance();
- completionDate.setTimeInMillis(0);
- }
- CustomOrderBean closedOrderBean = new CustomOrderBean(
- orderId,
- rs.getString(2),
- rs.getString(3),
- openDate,
- completionDate,
- rs.getDouble(6),
- rs.getBigDecimal(7),
- rs.getBigDecimal(8),
- rs.getString(9));
- closedOrderBean.setOrderStatus(StockTraderUtility.ORDER_STATUS_CLOSED);
- closedOrders.add(closedOrderBean);
- }
- } finally {
- try {
- rs.close();
- } catch (SQLException e) {
- logger.debug("", e);
- }
- }
-
- if (!closedOrders.isEmpty()) {
- updateClosedOrders = sqlConnection.prepareStatement(SQL_UPDATE_CLOSED_ORDERS);
- updateClosedOrders.setString(1, userId);
- updateClosedOrders.executeUpdate();
- }
-
- return closedOrders;
- } catch (SQLException e) {
- throw new DAOException("", e);
- } finally {
- if (selectClosedOrders != null) {
- try {
- selectClosedOrders.close();
- } catch (SQLException e) {
- logger.debug("", e);
- }
- }
- if (updateClosedOrders != null) {
- try {
- selectClosedOrders.close();
- } catch (SQLException e) {
- logger.debug("", e);
- }
- }
-
- }
- }
-
- public void insertAccountProfile(CustomAccountProfileBean accountProfileBean) throws DAOException {
- PreparedStatement insertAccountProfile = null;
- try {
- insertAccountProfile = sqlConnection.prepareStatement(SQL_INSERT_ACCOUNT_PROFILE);
- insertAccountProfile.setString(1, accountProfileBean.getAddress());
- insertAccountProfile.setString(2, accountProfileBean.getPassword());
- insertAccountProfile.setString(3, accountProfileBean.getUserID());
- insertAccountProfile.setString(4, accountProfileBean.getEmail());
- insertAccountProfile.setString(5, accountProfileBean.getCreditCard());
- insertAccountProfile.setString(6, accountProfileBean.getFullName());
- insertAccountProfile.executeUpdate();
- } catch (SQLException e) {
- throw new DAOException("", e);
- } finally {
- if (insertAccountProfile != null) {
- try {
- insertAccountProfile.close();
- } catch (SQLException e) {
- logger.debug("", e);
- }
- }
- }
- }
-
- public void insertAccount(CustomAccountBean accountBean) throws DAOException {
- PreparedStatement insertAccount = null;
- try {
- insertAccount = sqlConnection.prepareStatement(SQL_INSERT_ACCOUNT);
- insertAccount.setBigDecimal(1, accountBean.getOpenBalance());
- insertAccount.setInt(2, accountBean.getLogoutCount());
- insertAccount.setBigDecimal(3, accountBean.getBalance());
- insertAccount.setDate(4, StockTraderUtility.convertToSqlDate(accountBean.getLastLogin()));
- insertAccount.setInt(5, accountBean.getLoginCount());
- insertAccount.setString(6, accountBean.getUserID());
- insertAccount.executeUpdate();
-
- } catch (SQLException e) {
- throw new DAOException("", e);
-
- } finally {
- if (insertAccount != null) {
- try {
- insertAccount.close();
- } catch (SQLException e) {
- logger.debug("", e);
- }
- }
- }
- }
-
- public CustomAccountProfileBean update(CustomAccountProfileBean customerAccountProfile) throws DAOException {
- PreparedStatement updateAccountProfile = null;
- try {
- updateAccountProfile = sqlConnection.prepareStatement(SQL_UPDATE_ACCOUNT_PROFILE);
- updateAccountProfile.setString(1, customerAccountProfile.getAddress());
- updateAccountProfile.setString(2, customerAccountProfile.getPassword());
- updateAccountProfile.setString(3, customerAccountProfile.getEmail());
- updateAccountProfile.setString(4, customerAccountProfile.getCreditCard());
- updateAccountProfile.setString(5, customerAccountProfile.getFullName());
- updateAccountProfile.setString(6, customerAccountProfile.getUserID());
- updateAccountProfile.executeUpdate();
- return customerAccountProfile;
- } catch (SQLException e) {
- throw new DAOException("", e);
- } finally {
- if (updateAccountProfile != null) {
- try {
- updateAccountProfile.close();
- } catch (SQLException e) {
- logger.debug("", e);
- }
- }
- }
- }
-
- public List<CustomHoldingBean> getHoldings(String userID) throws DAOException {
- PreparedStatement selectHoldings = null;
- try {
- selectHoldings = sqlConnection.prepareStatement(SQL_SELECT_HOLDINGS);
- selectHoldings.setString(1, userID);
- ResultSet rs = selectHoldings.executeQuery();
- List<CustomHoldingBean> holdings = new ArrayList<CustomHoldingBean>();
- try {
- while (rs.next()) {
- CustomHoldingBean holding = new CustomHoldingBean(
- rs.getInt(1),
- rs.getDouble(2),
- rs.getBigDecimal(3),
- StockTraderUtility.convertToCalendar(rs.getDate(4)),
- rs.getString(5),
- rs.getInt(6));
- holdings.add(holding);
- }
- } finally {
- try {
- rs.close();
- } catch (SQLException e) {
- logger.debug("", e);
- }
- }
- return holdings;
- } catch (SQLException e) {
- throw new DAOException("", e);
- } finally {
- if (selectHoldings != null) {
- try {
- selectHoldings.close();
- } catch (SQLException e) {
- logger.debug("", e);
- }
- }
- }
- }
-}
+/*
+ * 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.stonehenge.stocktrader.mysql;
+
+import java.math.BigDecimal;
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.util.ArrayList;
+import java.util.Calendar;
+import java.util.List;
+import org.apache.commons.logging.Log;
+import org.apache.commons.logging.LogFactory;
+import org.apache.stonehenge.stocktrader.CustomAccountBean;
+import org.apache.stonehenge.stocktrader.CustomAccountProfileBean;
+import org.apache.stonehenge.stocktrader.CustomHoldingBean;
+import org.apache.stonehenge.stocktrader.CustomOrderBean;
+import org.apache.stonehenge.stocktrader.mysql.AbstractMySQLDAO;
+import org.apache.stonehenge.stocktrader.dal.CustomerDAO;
+import org.apache.stonehenge.stocktrader.dal.DAOException;
+import org.apache.stonehenge.stocktrader.util.StockTraderUtility;
+
+public class MySQLCustomerDAO extends AbstractMySQLDAO implements CustomerDAO {
+ private static final Log logger = LogFactory.getLog(MySQLCustomerDAO.class);
+
+ private static final String SQL_DEBIT_ACCOUNT = "UPDATE account SET balance= balance - ? WHERE accountid = ?";
+ private static final String SQL_SELECT_HOLDING_LOCK = "SELECT h.account_accountid, h.holdingid, h.quantity, h.purchaseprice, h.purchasedate, h.quote_symbol FROM holding as h INNER JOIN orders as o on h.holdingid = o.holding_holdingid WHERE (o.orderid = ?)";
+ private static final String SQL_SELECT_HOLDING_NOLOCK = "SELECT account_accountid, quantity, purchaseprice, purchasedate, quote_symbol FROM holding WHERE holdingid = ? AND account_accountid = (SELECT accountid FROM account WHERE profile_userid = ?)";
+ private static final String SQL_SELECT_CUSTOMER_PROFILE_BY_USERID = "SELECT userid, password, fullname, address, email, creditcard FROM accountprofile WHERE userid = ?";
+ private static final String SQL_UPDATE_CUSTOMER_LOGIN = "UPDATE account SET logincount = logincount + 1, lastlogin = now() where profile_userid = ?";
+ private static final String SQL_SELECT_CUSTOMER_LOGIN = "SELECT accountid, creationdate, openbalance, logoutcount, balance, lastlogin, logincount FROM account WHERE profile_userid = ?";
+ private static final String SQL_UPDATE_LOGOUT = "UPDATE account SET logoutcount = logoutcount + 1 WHERE profile_userid = ?";
+ private static final String SQL_SELECT_GET_CUSTOMER_BY_USERID = "SELECT account.ACCOUNTID, account.PROFILE_USERID, account.CREATIONDATE, account.OPENBALANCE, account.LOGOUTCOUNT, account.BALANCE, account.LASTLOGIN, account.LOGINCOUNT FROM account WHERE account.PROFILE_USERID = ?";
+ private static final String SQL_SELECT_ORDERS_BY_ID = " o.orderid, o.ordertype, o.orderstatus, o.opendate, o.completiondate, o.quantity, o.price, o.orderfee, o.quote_symbol FROM orders o WHERE o.account_accountid = (SELECT a.accountid FROM account a WHERE a.profile_userid = ?) ORDER BY o.orderid DESC";
+ private static final String SQL_SELECT_CLOSED_ORDERS = "SELECT orderid, ordertype, orderstatus, completiondate, opendate, quantity, price, orderfee, quote_symbol FROM orders WHERE account_accountid = (SELECT accountid FROM account WHERE profile_userid = ?) AND orderstatus = 'closed'";
+ private static final String SQL_UPDATE_CLOSED_ORDERS = "UPDATE orders SET orderstatus = 'completed' WHERE orderstatus = 'closed' AND account_accountid = (SELECT accountid FROM account WHERE profile_userid = ?)";
+ private static final String SQL_INSERT_ACCOUNT_PROFILE = "INSERT INTO accountprofile VALUES (?, ?, ?, ?, ?, ?)";
+ private static final String SQL_INSERT_ACCOUNT = "INSERT INTO account (creationdate, openbalance, logoutcount, balance, lastlogin, logincount, profile_userid, accountid) VALUES (now(), ?, ?, ?, ?, ?, ?, null); SELECT LAST_INSERT_ID();";
+ private static final String SQL_UPDATE_ACCOUNT_PROFILE = "UPDATE accountprofile SET address = ?, password = ?, email = ?, creditcard = ?, fullname = ? WHERE userid = ?";
+ private static final String SQL_SELECT_HOLDINGS = "SELECT holdingid, quantity, purchaseprice, purchasedate, quote_symbol, account_accountid FROM holding WHERE account_accountid = (SELECT accountid FROM account WHERE profile_userid = ?) ORDER BY holdingid DESC";
+
+ public MySQLCustomerDAO(Connection sqlConnection) throws DAOException {
+ super(sqlConnection);
+ }
+
+ public CustomHoldingBean getHoldingForUpdate(int orderId) throws DAOException {
+ if (logger.isDebugEnabled()) {
+ logger.debug("MySQLCustomerDAO.getHoldingForUpdate(int)\nOrder ID :" + orderId);
+ }
+
+ CustomHoldingBean holding = null;
+ PreparedStatement selectHoldingLockStat = null;
+ try {
+ selectHoldingLockStat = sqlConnection.prepareStatement(SQL_SELECT_HOLDING_LOCK);
+ selectHoldingLockStat.setInt(1, orderId);
+ ResultSet rs = selectHoldingLockStat.executeQuery();
+ if (rs.next()) {
+ try {
+ holding = new CustomHoldingBean(
+ rs.getInt(1),
+ rs.getInt(2),
+ rs.getDouble(3),
+ rs.getBigDecimal(4),
+ StockTraderUtility.convertToCalendar(rs.getDate(5)),
+ rs.getString(6));
+ return holding;
+ } finally {
+ try {
+ rs.close();
+ } catch (SQLException e) {
+ logger.debug("", e);
+ }
+ }
+ }
+ } catch (SQLException e) {
+ throw new DAOException("Exception is thrown when selecting the holding entry for order ID :" + orderId, e);
+ } finally {
+ if (selectHoldingLockStat != null) {
+ try {
+ selectHoldingLockStat.close();
+ } catch (SQLException e) {
+ logger.debug("", e);
+ }
+ }
+ }
+ return holding;
+ }
+
+ public CustomHoldingBean getHolding(String userId, int holdingID) throws DAOException {
+ if (logger.isDebugEnabled()) {
+ logger.debug("MSSQLCustomerDAO.getHolding(String,int)\nUserID :" + userId + "\nOrder ID :" + holdingID);
+ }
+ CustomHoldingBean holding = null;
+ PreparedStatement selectHoldingNoLockStat = null;
+ try {
+ selectHoldingNoLockStat = sqlConnection.prepareStatement(SQL_SELECT_HOLDING_NOLOCK);
+ selectHoldingNoLockStat.setInt(1, holdingID);
+ selectHoldingNoLockStat.setString(2, userId);
+
+ ResultSet rs = selectHoldingNoLockStat.executeQuery();
+ if (rs.next()) {
+ try {
+ holding = new CustomHoldingBean(
+ rs.getInt(1),
+ holdingID,
+ rs.getDouble(2),
+ rs.getBigDecimal(3),
+ StockTraderUtility.convertToCalendar(rs.getDate(4)),
+ rs.getString(5));
+ return holding;
+ } finally {
+ try {
+ rs.close();
+ } catch (SQLException e) {
+ logger.debug("", e);
+ }
+ }
+ }
+ } catch (SQLException e) {
+ logger.debug("", e);
+ throw new DAOException("Exception is thrown when selecting the holding entry for userID :" + userId + " and orderID :" + holdingID, e);
+
+ } finally {
+ if (selectHoldingNoLockStat != null) {
+ try {
+ selectHoldingNoLockStat.close();
+ } catch (SQLException e) {
+ logger.debug("", e);
+ }
+ }
+ }
+ return holding;
+ }
+
+ public void updateAccountBalance(int accountId, BigDecimal total) throws DAOException {
+ if (logger.isDebugEnabled()) {
+ logger.debug("MySQLCustomerDAO.updateAccoutBalance(int,BigDecimal)\n Account ID :" + accountId + "\nTotal :" + total);
+ }
+ PreparedStatement debitAccountStat = null;
+ try {
+ debitAccountStat = sqlConnection.prepareStatement(SQL_DEBIT_ACCOUNT);
+ debitAccountStat.setBigDecimal(1, total);
+ debitAccountStat.setInt(2, accountId);
+ debitAccountStat.executeUpdate();
+
+ } catch (SQLException e) {
+ throw new DAOException("Excpetion is thrown when updating the account balance for accountID :" + accountId + " total :" + total, e);
+ } finally {
+ if (debitAccountStat != null) {
+ try {
+ debitAccountStat.close();
+ } catch (SQLException e) {
+ logger.debug("", e);
+ }
+ }
+ }
+ }
+
+ public CustomAccountBean login(String userId, String password) throws DAOException {
+ PreparedStatement selectCustomerProfileByUserId = null;
+ PreparedStatement updateCustomerLogin = null;
+ PreparedStatement selectCustomerLogin = null;
+ try {
+ selectCustomerProfileByUserId = sqlConnection.prepareStatement(SQL_SELECT_CUSTOMER_PROFILE_BY_USERID);
+ selectCustomerProfileByUserId.setString(1, userId);
+ ResultSet customerProfileRS = selectCustomerProfileByUserId.executeQuery();
+ if (customerProfileRS.next()) {
+ try {
+ String userPassword = customerProfileRS.getString(2);
+ if (userPassword.equals(password)) {
+ try {
+ updateCustomerLogin = sqlConnection.prepareStatement(SQL_UPDATE_CUSTOMER_LOGIN);
+ updateCustomerLogin.setString(1, userId);
+ updateCustomerLogin.executeUpdate();
+ selectCustomerLogin = sqlConnection.prepareStatement(SQL_SELECT_CUSTOMER_LOGIN);
+ selectCustomerLogin.setString(1, userId);
+ ResultSet rs = selectCustomerLogin.executeQuery();
+ if (rs.next()) {
+ try {
+ CustomAccountBean accountData = new CustomAccountBean(
+ rs.getInt(1),
+ userId,
+ StockTraderUtility.convertToCalendar(rs.getDate(2)),
+ rs.getBigDecimal(3),
+ rs.getInt(4),
+ rs.getBigDecimal(5),
+ StockTraderUtility.convertToCalendar(rs.getDate(6)),
+ rs.getInt(7) + 1);
+ return accountData;
+ } finally {
+ try {
+ rs.close();
+ } catch (SQLException e) {
+ logger.debug("", e);
+ }
+ }
+ }
+ } catch (SQLException e) {
+ throw new DAOException("", e);
+ } finally {
+ if (updateCustomerLogin != null) {
+ try {
+ updateCustomerLogin.close();
+ } catch (SQLException e) {
+ logger.debug("", e);
+ }
+ }
+ if (selectCustomerLogin != null) {
+ try {
+ selectCustomerLogin.close();
+ } catch (SQLException e) {
+ logger.debug("", e);
+ }
+ }
+ }
+ }
+ } finally {
+ try {
+ customerProfileRS.close();
+ } catch (SQLException e) {
+ logger.debug("", e);
+ }
+ }
+ }
+
+ } catch (SQLException e) {
+ throw new DAOException("", e);
+ } finally {
+ if (selectCustomerProfileByUserId != null) {
+ try {
+ selectCustomerProfileByUserId.close();
+ } catch (SQLException e) {
+ logger.debug("", e);
+ }
+ }
+ }
+ return null;
+ }
+
+ public void logoutUser(String userId) throws DAOException {
+ PreparedStatement updateLogout = null;
+ try {
+ updateLogout = sqlConnection.prepareStatement(SQL_UPDATE_LOGOUT);
+ updateLogout.setString(1, userId);
+ updateLogout.executeUpdate();
+ } catch (SQLException e) {
+ throw new DAOException("", e);
+ } finally {
+ if (updateLogout != null) {
+ try {
+ updateLogout.close();
+ } catch (SQLException e) {
+ logger.debug("", e);
+ }
+ }
+ }
+ }
+
+ public CustomAccountBean getCustomerByUserId(String userId) throws DAOException {
+ PreparedStatement getCustomerByUserId = null;
+
+ try {
+ getCustomerByUserId = sqlConnection.prepareStatement(SQL_SELECT_GET_CUSTOMER_BY_USERID);
+ getCustomerByUserId.setString(1, userId);
+ ResultSet rs = getCustomerByUserId.executeQuery();
+ if (rs.next()) {
+ try {
+ CustomAccountBean bean = new CustomAccountBean(
+ rs.getInt(1),
+ rs.getString(2),
+ StockTraderUtility.convertToCalendar(rs.getDate(3)),
+ rs.getBigDecimal(4),
+ rs.getInt(5),
+ rs.getBigDecimal(6),
+ StockTraderUtility.convertToCalendar(rs.getDate(7)),
+ rs.getInt(8));
+ return bean;
+ } finally {
+ try {
+ rs.close();
+ } catch (SQLException e) {
+ logger.debug("", e);
+ }
+ }
+ }
+ } catch (SQLException e) {
+ throw new DAOException("", e);
+ } finally {
+ if (getCustomerByUserId != null) {
+ try {
+ getCustomerByUserId.close();
+ } catch (SQLException e) {
+ logger.debug("", e);
+ }
+ }
+ }
+ return null;
+ }
+
+ public CustomAccountProfileBean getAccountProfileData(String userId) throws DAOException {
+
+ PreparedStatement customerProfileByUserId = null;
+ try {
+ customerProfileByUserId = sqlConnection.prepareStatement(SQL_SELECT_CUSTOMER_PROFILE_BY_USERID);
+ customerProfileByUserId.setString(1, userId);
+ ResultSet rs = customerProfileByUserId.executeQuery();
+ if (rs.next()) {
+ try {
+ CustomAccountProfileBean accountProfileDataBean = new CustomAccountProfileBean(
+ rs.getString(1),
+ rs.getString(2),
+ rs.getString(3),
+ rs.getString(4),
+ rs.getString(5),
+ rs.getString(6));
+ return accountProfileDataBean;
+ } finally {
+ try {
+ rs.close();
+ } catch (SQLException e) {
+ logger.debug("", e);
+ }
+ }
+ }
+ } catch (SQLException e) {
+ throw new DAOException("", e);
+ } finally {
+ if (customerProfileByUserId != null) {
+ try {
+ customerProfileByUserId.close();
+ } catch (SQLException e) {
+ logger.debug("", e);
+ }
+ }
+ }
+ return null;
+ }
+
+ public List<CustomOrderBean> getOrders(String userId, boolean top, int maxTop, int maxDefault) throws DAOException {
+ PreparedStatement selectOrdersById = null;
+ try {
+ String sqlQuery;
+ if (top) {
+ sqlQuery = "SELECT " + SQL_SELECT_ORDERS_BY_ID + " LIMIT " + maxTop;
+ } else {
+ sqlQuery = "SELECT " + SQL_SELECT_ORDERS_BY_ID + " LIMIT " + maxDefault;
+ }
+ selectOrdersById = sqlConnection.prepareStatement(sqlQuery);
+ selectOrdersById.setString(1, userId);
+ ResultSet rs = selectOrdersById.executeQuery();
+ List<CustomOrderBean> orders = new ArrayList<CustomOrderBean>();
+
+ try {
+ while (rs.next()) {
+ int orderId = rs.getInt(1);
+ Calendar openDate = StockTraderUtility.convertToCalendar(rs.getDate(4));
+ Calendar completionDate = null;
+ try {
+ if (rs.getDate(5) != null) {
+ completionDate = StockTraderUtility.convertToCalendar(rs.getDate(5));
+ } else {
+ completionDate = Calendar.getInstance();
+ completionDate.setTimeInMillis(0);
+ }
+ } catch (SQLException e) {
+ logger.debug("", e);
+ completionDate = Calendar.getInstance();
+ completionDate.setTimeInMillis(0);
+ }
+
+ CustomOrderBean orderBean = new CustomOrderBean(
+ orderId,
+ rs.getString(2),
+ rs.getString(3),
+ openDate,
+ completionDate,
+ rs.getDouble(6),
+ rs.getBigDecimal(7),
+ rs.getBigDecimal(8),
+ rs.getString(9));
+ orders.add(orderBean);
+ }
+
+ } finally {
+ try {
+ rs.close();
+ } catch (SQLException e) {
+ logger.debug("", e);
+ }
+ }
+ return orders;
+
+ } catch (SQLException e) {
+ throw new DAOException("", e);
+ } finally {
+ if (selectOrdersById != null) {
+ try {
+ selectOrdersById.close();
+ } catch (SQLException e) {
+ logger.debug("", e);
+ }
+ }
+ }
+ }
+
+ public List<CustomOrderBean> getClosedOrders(String userId) throws DAOException {
+ PreparedStatement selectClosedOrders = null;
+ PreparedStatement updateClosedOrders = null;
+ try {
+ selectClosedOrders = sqlConnection.prepareStatement(SQL_SELECT_CLOSED_ORDERS);
+ selectClosedOrders.setString(1, userId);
+ ResultSet rs = selectClosedOrders.executeQuery();
+ List<CustomOrderBean> closedOrders = new ArrayList<CustomOrderBean>();
+
+ try {
+ while (rs.next()) {
+ int orderId = rs.getInt(1);
+ Calendar openDate = StockTraderUtility.convertToCalendar(rs.getDate(4));
+ Calendar completionDate = null;
+ try {
+ completionDate = StockTraderUtility.convertToCalendar(rs.getDate(5));
+ } catch (SQLException e) {
+ logger.debug("", e);
+ completionDate = Calendar.getInstance();
+ completionDate.setTimeInMillis(0);
+ }
+ CustomOrderBean closedOrderBean = new CustomOrderBean(
+ orderId,
+ rs.getString(2),
+ rs.getString(3),
+ openDate,
+ completionDate,
+ rs.getDouble(6),
+ rs.getBigDecimal(7),
+ rs.getBigDecimal(8),
+ rs.getString(9));
+ closedOrderBean.setOrderStatus(StockTraderUtility.ORDER_STATUS_CLOSED);
+ closedOrders.add(closedOrderBean);
+ }
+ } finally {
+ try {
+ rs.close();
+ } catch (SQLException e) {
+ logger.debug("", e);
+ }
+ }
+
+ if (!closedOrders.isEmpty()) {
+ updateClosedOrders = sqlConnection.prepareStatement(SQL_UPDATE_CLOSED_ORDERS);
+ updateClosedOrders.setString(1, userId);
+ updateClosedOrders.executeUpdate();
+ }
+
+ return closedOrders;
+ } catch (SQLException e) {
+ throw new DAOException("", e);
+ } finally {
+ if (selectClosedOrders != null) {
+ try {
+ selectClosedOrders.close();
+ } catch (SQLException e) {
+ logger.debug("", e);
+ }
+ }
+ if (updateClosedOrders != null) {
+ try {
+ selectClosedOrders.close();
+ } catch (SQLException e) {
+ logger.debug("", e);
+ }
+ }
+
+ }
+ }
+
+ public void insertAccountProfile(CustomAccountProfileBean accountProfileBean) throws DAOException {
+ PreparedStatement insertAccountProfile = null;
+ try {
+ insertAccountProfile = sqlConnection.prepareStatement(SQL_INSERT_ACCOUNT_PROFILE);
+ insertAccountProfile.setString(1, accountProfileBean.getAddress());
+ insertAccountProfile.setString(2, accountProfileBean.getPassword());
+ insertAccountProfile.setString(3, accountProfileBean.getUserID());
+ insertAccountProfile.setString(4, accountProfileBean.getEmail());
+ insertAccountProfile.setString(5, accountProfileBean.getCreditCard());
+ insertAccountProfile.setString(6, accountProfileBean.getFullName());
+ insertAccountProfile.executeUpdate();
+ } catch (SQLException e) {
+ throw new DAOException("", e);
+ } finally {
+ if (insertAccountProfile != null) {
+ try {
+ insertAccountProfile.close();
+ } catch (SQLException e) {
+ logger.debug("", e);
+ }
+ }
+ }
+ }
+
+ public void insertAccount(CustomAccountBean accountBean) throws DAOException {
+ PreparedStatement insertAccount = null;
+ try {
+ insertAccount = sqlConnection.prepareStatement(SQL_INSERT_ACCOUNT);
+ insertAccount.setBigDecimal(1, accountBean.getOpenBalance());
+ insertAccount.setInt(2, accountBean.getLogoutCount());
+ insertAccount.setBigDecimal(3, accountBean.getBalance());
+ insertAccount.setDate(4, StockTraderUtility.convertToSqlDate(accountBean.getLastLogin()));
+ insertAccount.setInt(5, accountBean.getLoginCount());
+ insertAccount.setString(6, accountBean.getUserID());
+ insertAccount.executeUpdate();
+
+ } catch (SQLException e) {
+ throw new DAOException("", e);
+
+ } finally {
+ if (insertAccount != null) {
+ try {
+ insertAccount.close();
+ } catch (SQLException e) {
+ logger.debug("", e);
+ }
+ }
+ }
+ }
+
+ public CustomAccountProfileBean update(CustomAccountProfileBean customerAccountProfile) throws DAOException {
+ PreparedStatement updateAccountProfile = null;
+ try {
+ updateAccountProfile = sqlConnection.prepareStatement(SQL_UPDATE_ACCOUNT_PROFILE);
+ updateAccountProfile.setString(1, customerAccountProfile.getAddress());
+ updateAccountProfile.setString(2, customerAccountProfile.getPassword());
+ updateAccountProfile.setString(3, customerAccountProfile.getEmail());
+ updateAccountProfile.setString(4, customerAccountProfile.getCreditCard());
+ updateAccountProfile.setString(5, customerAccountProfile.getFullName());
+ updateAccountProfile.setString(6, customerAccountProfile.getUserID());
+ updateAccountProfile.executeUpdate();
+ return customerAccountProfile;
+ } catch (SQLException e) {
+ throw new DAOException("", e);
+ } finally {
+ if (updateAccountProfile != null) {
+ try {
+ updateAccountProfile.close();
+ } catch (SQLException e) {
+ logger.debug("", e);
+ }
+ }
+ }
+ }
+
+ public List<CustomHoldingBean> getHoldings(String userID) throws DAOException {
+ PreparedStatement selectHoldings = null;
+ try {
+ selectHoldings = sqlConnection.prepareStatement(SQL_SELECT_HOLDINGS);
+ selectHoldings.setString(1, userID);
+ ResultSet rs = selectHoldings.executeQuery();
+ List<CustomHoldingBean> holdings = new ArrayList<CustomHoldingBean>();
+ try {
+ while (rs.next()) {
+ CustomHoldingBean holding = new CustomHoldingBean(
+ rs.getInt(1),
+ rs.getDouble(2),
+ rs.getBigDecimal(3),
+ StockTraderUtility.convertToCalendar(rs.getDate(4)),
+ rs.getString(5),
+ rs.getInt(6));
+ holdings.add(holding);
+ }
+ } finally {
+ try {
+ rs.close();
+ } catch (SQLException e) {
+ logger.debug("", e);
+ }
+ }
+ return holdings;
+ } catch (SQLException e) {
+ throw new DAOException("", e);
+ } finally {
+ if (selectHoldings != null) {
+ try {
+ selectHoldings.close();
+ } catch (SQLException e) {
+ logger.debug("", e);
+ }
+ }
+ }
+ }
+}
Propchange: incubator/stonehenge/trunk/stocktrader/metro/common/src/org/apache/stonehenge/stocktrader/mysql/MySQLCustomerDAO.java
------------------------------------------------------------------------------
svn:eol-style = native
Modified: incubator/stonehenge/trunk/stocktrader/metro/common/src/org/apache/stonehenge/stocktrader/mysql/MySQLDAOFactory.java
URL: http://svn.apache.org/viewvc/incubator/stonehenge/trunk/stocktrader/metro/common/src/org/apache/stonehenge/stocktrader/mysql/MySQLDAOFactory.java?rev=958838&r1=958837&r2=958838&view=diff
==============================================================================
--- incubator/stonehenge/trunk/stocktrader/metro/common/src/org/apache/stonehenge/stocktrader/mysql/MySQLDAOFactory.java (original)
+++ incubator/stonehenge/trunk/stocktrader/metro/common/src/org/apache/stonehenge/stocktrader/mysql/MySQLDAOFactory.java Tue Jun 29 06:33:54 2010
@@ -1,122 +1,122 @@
-/*
- * 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.stonehenge.stocktrader.mysql;
-
-import java.sql.Connection;
-import java.sql.DriverManager;
-import java.sql.SQLException;
-
-import org.apache.commons.logging.Log;
-import org.apache.commons.logging.LogFactory;
-import org.apache.stonehenge.stocktrader.dal.CustomerDAO;
-import org.apache.stonehenge.stocktrader.dal.DAOException;
-import org.apache.stonehenge.stocktrader.dal.DAOFactory;
-import org.apache.stonehenge.stocktrader.dal.MarketSummaryDAO;
-import org.apache.stonehenge.stocktrader.dal.OrderDAO;
-
-public class MySQLDAOFactory extends DAOFactory {
-
- private static Log logger = LogFactory.getLog(MySQLDAOFactory.class);
- private static MySQLDAOFactory self = null;
-
- private Connection sqlConnection = null;
-
- private String connection = null;
-
- static {
- try {
- Class.forName("com.mysql.jdbc.Driver");
- } catch (ClassNotFoundException e) {
- logger.warn("Unable to load DBDrive class", e);
- }
- }
-
- public static DAOFactory getInstance() {
- if (self == null) {
- self = new MySQLDAOFactory();
- }
- return self;
- }
-
- private MySQLDAOFactory() {
- }
-
- public OrderDAO getOrderDAO() throws DAOException {
- logger.debug("MySQLDAOFactory.getOrderDAO");
- try {
- OrderDAO orderDAO = new MySQLOrderDAO(getConnection());
- return orderDAO;
- } catch (SQLException e) {
- logger.debug("", e);
- throw new DAOException("Exception was thrown when instantiating MySQLOrderDAO object",e);
- }
- }
-
- public CustomerDAO getCustomerDAO() throws DAOException {
- logger.debug("MySQLDAOFactory.getCustomerDAO");
- try {
- CustomerDAO customerDAO = new MySQLCustomerDAO(getConnection());
- return customerDAO;
- } catch (SQLException e) {
- logger.debug("", e);
- throw new DAOException("Exception was thrown when instantiating a MySQLCustomerDAO",e);
- }
- }
-
- public MarketSummaryDAO getMarketSummaryDAO() throws DAOException {
- logger.debug("MySQLDAOFactory.getMarketSummaryDAO");
- try {
- MarketSummaryDAO marketSummaryDAO = new MySQLMarketSummaryDAO(getConnection());
- return marketSummaryDAO;
- } catch (SQLException e) {
- logger.debug("", e);
- throw new DAOException("Exception was thrown when instantiating a MarketSummaryDAO",e);
- }
- }
-
- private String getConnectionString() {
- if (connection == null) {
- loadProperties();
- if (prop.size() <= 0) {
-// if (prop == null) {
- connection = "jdbc:mysql://localhost:3306/stocktraderdb?user=trade&password=yyy";
- } else {
- StringBuffer buf = new StringBuffer();
- buf.append("jdbc:mysql://");
- buf.append(prop.getProperty(PROP_DB_HOST));
- buf.append(":" + prop.getProperty(PROP_DB_PORT));
- buf.append("/" + prop.getProperty(PROP_DB_NAME));
- buf.append("?user=" + prop.getProperty(PROP_DB_USER));
- buf.append("&password=" + prop.getProperty(PROP_DB_PASSWORD));
- connection = buf.toString();
- }
- }
-
- if (logger.isDebugEnabled()) {
- logger.debug("MySQLDAOFactory.getConnectionString()\nConnection :"+ connection);
- }
- return connection;
- }
-
- private Connection getConnection() throws SQLException {
- if (sqlConnection == null || sqlConnection.isClosed()) {
- sqlConnection = DriverManager.getConnection(getConnectionString());
- }
- return sqlConnection;
- }
-}
+/*
+ * 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.stonehenge.stocktrader.mysql;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.SQLException;
+
+import org.apache.commons.logging.Log;
+import org.apache.commons.logging.LogFactory;
+import org.apache.stonehenge.stocktrader.dal.CustomerDAO;
+import org.apache.stonehenge.stocktrader.dal.DAOException;
+import org.apache.stonehenge.stocktrader.dal.DAOFactory;
+import org.apache.stonehenge.stocktrader.dal.MarketSummaryDAO;
+import org.apache.stonehenge.stocktrader.dal.OrderDAO;
+
+public class MySQLDAOFactory extends DAOFactory {
+
+ private static Log logger = LogFactory.getLog(MySQLDAOFactory.class);
+ private static MySQLDAOFactory self = null;
+
+ private Connection sqlConnection = null;
+
+ private String connection = null;
+
+ static {
+ try {
+ Class.forName("com.mysql.jdbc.Driver");
+ } catch (ClassNotFoundException e) {
+ logger.warn("Unable to load DBDrive class", e);
+ }
+ }
+
+ public static DAOFactory getInstance() {
+ if (self == null) {
+ self = new MySQLDAOFactory();
+ }
+ return self;
+ }
+
+ private MySQLDAOFactory() {
+ }
+
+ public OrderDAO getOrderDAO() throws DAOException {
+ logger.debug("MySQLDAOFactory.getOrderDAO");
+ try {
+ OrderDAO orderDAO = new MySQLOrderDAO(getConnection());
+ return orderDAO;
+ } catch (SQLException e) {
+ logger.debug("", e);
+ throw new DAOException("Exception was thrown when instantiating MySQLOrderDAO object",e);
+ }
+ }
+
+ public CustomerDAO getCustomerDAO() throws DAOException {
+ logger.debug("MySQLDAOFactory.getCustomerDAO");
+ try {
+ CustomerDAO customerDAO = new MySQLCustomerDAO(getConnection());
+ return customerDAO;
+ } catch (SQLException e) {
+ logger.debug("", e);
+ throw new DAOException("Exception was thrown when instantiating a MySQLCustomerDAO",e);
+ }
+ }
+
+ public MarketSummaryDAO getMarketSummaryDAO() throws DAOException {
+ logger.debug("MySQLDAOFactory.getMarketSummaryDAO");
+ try {
+ MarketSummaryDAO marketSummaryDAO = new MySQLMarketSummaryDAO(getConnection());
+ return marketSummaryDAO;
+ } catch (SQLException e) {
+ logger.debug("", e);
+ throw new DAOException("Exception was thrown when instantiating a MarketSummaryDAO",e);
+ }
+ }
+
+ private String getConnectionString() {
+ if (connection == null) {
+ loadProperties();
+ if (prop.size() <= 0) {
+// if (prop == null) {
+ connection = "jdbc:mysql://localhost:3306/stocktraderdb?user=trade&password=yyy";
+ } else {
+ StringBuffer buf = new StringBuffer();
+ buf.append("jdbc:mysql://");
+ buf.append(prop.getProperty(PROP_DB_HOST));
+ buf.append(":" + prop.getProperty(PROP_DB_PORT));
+ buf.append("/" + prop.getProperty(PROP_DB_NAME));
+ buf.append("?user=" + prop.getProperty(PROP_DB_USER));
+ buf.append("&password=" + prop.getProperty(PROP_DB_PASSWORD));
+ connection = buf.toString();
+ }
+ }
+
+ if (logger.isDebugEnabled()) {
+ logger.debug("MySQLDAOFactory.getConnectionString()\nConnection :"+ connection);
+ }
+ return connection;
+ }
+
+ private Connection getConnection() throws SQLException {
+ if (sqlConnection == null || sqlConnection.isClosed()) {
+ sqlConnection = DriverManager.getConnection(getConnectionString());
+ }
+ return sqlConnection;
+ }
+}
Propchange: incubator/stonehenge/trunk/stocktrader/metro/common/src/org/apache/stonehenge/stocktrader/mysql/MySQLDAOFactory.java
------------------------------------------------------------------------------
svn:eol-style = native
Modified: incubator/stonehenge/trunk/stocktrader/metro/common/src/org/apache/stonehenge/stocktrader/mysql/MySQLMarketSummaryDAO.java
URL: http://svn.apache.org/viewvc/incubator/stonehenge/trunk/stocktrader/metro/common/src/org/apache/stonehenge/stocktrader/mysql/MySQLMarketSummaryDAO.java?rev=958838&r1=958837&r2=958838&view=diff
==============================================================================
--- incubator/stonehenge/trunk/stocktrader/metro/common/src/org/apache/stonehenge/stocktrader/mysql/MySQLMarketSummaryDAO.java (original)
+++ incubator/stonehenge/trunk/stocktrader/metro/common/src/org/apache/stonehenge/stocktrader/mysql/MySQLMarketSummaryDAO.java Tue Jun 29 06:33:54 2010
@@ -1,261 +1,261 @@
-/*
- * 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.stonehenge.stocktrader.mysql;
-
-import org.apache.stonehenge.stocktrader.mssql.AbstractMSSQLDAO;
-import org.apache.stonehenge.stocktrader.dal.MarketSummaryDAO;
-import org.apache.stonehenge.stocktrader.dal.DAOException;
-import org.apache.stonehenge.stocktrader.CustomQuoteBean;
-import org.apache.stonehenge.stocktrader.CustomMarketSummaryBean;
-import org.apache.stonehenge.stocktrader.util.StockTraderUtility;
-import org.apache.stonehenge.stocktrader.util.StockTraderSQLUtil;
-import java.sql.Connection;
-import java.sql.PreparedStatement;
-import java.sql.ResultSet;
-import java.sql.SQLException;
-import java.math.BigDecimal;
-import java.util.List;
-import java.util.ArrayList;
-import org.apache.commons.logging.Log;
-import org.apache.commons.logging.LogFactory;
-
-public class MySQLMarketSummaryDAO extends AbstractMSSQLDAO implements MarketSummaryDAO {
- private static final Log logger = LogFactory.getLog(MySQLMarketSummaryDAO.class);
-
- private static final String SQL_SELECT_QUOTE = "SELECT symbol, companyname, volume, price, open1, low, high, change1 FROM quote WHERE symbol = ?";
- private static final String SQL_SELECT_QUOTE_NOLOCK = "SELECT symbol, companyname, volume, price, open1, low, high, change1 FROM quote WHERE symbol = ?";
- private static final String SQL_UPDATE_STOCKPRICEVOLUME = "UPDATE quote SET price = ?, low = ?, high = ?, change1 = ? - open1, volume = volume + ? WHERE symbol = ?";
-
- private static final String SQL_SELECT_MARKETSUMMARY_GAINERS = "SELECT symbol, companyname, volume, price, open1, low, high, change1 FROM quote WHERE symbol LIKE 's:%' ORDER BY change1 DESC";
- private static final String SQL_SELECT_MARKETSUMMARY_LOSERS = "SELECT symbol, companyname, volume, price, open1, low, high, change1 FROM quote WHERE symbol LIKE 's:%' ORDER BY change1";
- private static final String SQL_SELECT_MARKETSUMMARY_TSIA = "SELECT SUM(price) / COUNT(*) as tsia FROM quote WHERE symbol LIKE 's:%'";
- private static final String SQL_SELECT_MARKETSUMMARY_OPENTSIA = "SELECT SUM(open1) / COUNT(*) as opentsia FROM quote WHERE symbol LIKE 's:%'";
- private static final String SQL_SELECT_MARKETSUMMARY_VOLUME = "SELECT SUM(volume) FROM quote WHERE symbol LIKE 's:%'";
-
- public MySQLMarketSummaryDAO(Connection sqlConnection) throws DAOException {
- super(sqlConnection);
- }
-
- public CustomQuoteBean getQuote(String symbol) throws DAOException {
- if (logger.isDebugEnabled()) {
- logger.debug("MarketSummaryDAO.getQouteForUpdate(String)\nSymbol :"+ symbol);
- }
- PreparedStatement selectQuote = null;
- try {
- selectQuote = sqlConnection.prepareStatement(SQL_SELECT_QUOTE_NOLOCK);
- selectQuote.setString(1, symbol);
- ResultSet rs = selectQuote.executeQuery();
-
- try {
- CustomQuoteBean quote = null;
- if (rs.next()) {
- quote = new CustomQuoteBean(
- rs.getString(1),
- rs.getString(2),
- rs.getDouble(3),
- rs.getBigDecimal(4),
- rs.getBigDecimal(5),
- rs.getBigDecimal(6),
- rs.getBigDecimal(7),
- rs.getDouble(8));
- }
- return quote;
- } finally {
- try {
- rs.close();
- } catch (SQLException e) {
- logger.debug("", e);
- }
- }
- } catch (SQLException e) {
- throw new DAOException("", e);
- } finally {
- try {
- if (selectQuote != null) {
- selectQuote.close();
- }
- } catch (SQLException e) {
- logger.debug("", e);
- }
- }
- }
-
- public CustomQuoteBean getQuoteForUpdate(String symbol) throws DAOException {
- if (logger.isDebugEnabled()) {
- logger.debug("MarketSummaryDAO.getQouteForUpdate(String)\nSymbol :"+ symbol);
- }
- PreparedStatement qouteForUpdateStat = null;
- try {
- qouteForUpdateStat = sqlConnection.prepareStatement(SQL_SELECT_QUOTE);
- CustomQuoteBean quote = null;
-
- qouteForUpdateStat.setString(1, symbol);
- ResultSet rs = qouteForUpdateStat.executeQuery();
-
- if (rs.next()) {
- quote = new CustomQuoteBean(
- rs.getString(1),
- rs.getString(2),
- rs.getDouble(3),
- rs.getBigDecimal(4),
- rs.getBigDecimal(5),
- rs.getBigDecimal(6),
- rs.getBigDecimal(7),
- rs.getDouble(8));
-
- try {
- rs.close();
- } catch (SQLException e) {
- logger.debug("", e);
- }
- return quote;
- } else {
- throw new DAOException("No quote entry found");
- }
- } catch (SQLException e) {
- throw new DAOException("", e);
- } finally {
- try {
- if (qouteForUpdateStat != null) {
- qouteForUpdateStat.close();
- }
- } catch (SQLException e) {
- logger.debug("", e);
- }
- }
- }
-
- public void updateStockPriceVolume(double quantity, CustomQuoteBean quote) throws DAOException {
- BigDecimal priceChangeFactor = StockTraderUtility.getRandomPriceChangeFactor(quote.getPrice());
- BigDecimal newPrice = quote.getPrice().multiply(priceChangeFactor);
-
- if (newPrice.compareTo(quote.getLow()) == -1) {
- quote.setLow(newPrice);
- }
- if (newPrice.compareTo(quote.getHigh()) == 1) {
- quote.setHigh(newPrice);
- }
-
- PreparedStatement updateStockPriceVolumeStat = null;
- try {
- updateStockPriceVolumeStat = sqlConnection.prepareStatement(SQL_UPDATE_STOCKPRICEVOLUME);
- updateStockPriceVolumeStat.setBigDecimal(1, newPrice);
- updateStockPriceVolumeStat.setBigDecimal(2, quote.getLow());
- updateStockPriceVolumeStat.setBigDecimal(3, quote.getHigh());
- updateStockPriceVolumeStat.setBigDecimal(4, newPrice);
- updateStockPriceVolumeStat.setFloat(5, (float) quantity);
- updateStockPriceVolumeStat.setString(6, quote.getSymbol());
- updateStockPriceVolumeStat.executeUpdate();
-
- } catch (SQLException e) {
- throw new DAOException("", e);
- } finally {
- try {
- if (updateStockPriceVolumeStat != null) {
- updateStockPriceVolumeStat.close();
- }
- } catch (SQLException e) {
- logger.debug("", e);
- }
- }
- }
-
- public CustomMarketSummaryBean getCustomMarketSummary() throws DAOException {
- BigDecimal tSIA = (BigDecimal) StockTraderSQLUtil.executeScalarNoParm(SQL_SELECT_MARKETSUMMARY_TSIA, sqlConnection);
- BigDecimal openTSIA = (BigDecimal) StockTraderSQLUtil.executeScalarNoParm(SQL_SELECT_MARKETSUMMARY_OPENTSIA, sqlConnection);
- double totalVolume = ((Double) StockTraderSQLUtil.executeScalarNoParm(SQL_SELECT_MARKETSUMMARY_VOLUME, sqlConnection)).doubleValue();
-
- List<CustomQuoteBean> topGainers = new ArrayList<CustomQuoteBean>();
- PreparedStatement gainers = null;
- try {
- gainers = sqlConnection.prepareStatement(SQL_SELECT_MARKETSUMMARY_GAINERS);
- ResultSet rs = gainers.executeQuery();
-
- try {
- for (int i = 0; rs.next() && i < 5; i++) {
- CustomQuoteBean quote = new CustomQuoteBean(
- rs.getString(1),
- rs.getString(2),
- rs.getDouble(3),
- rs.getBigDecimal(4),
- rs.getBigDecimal(5),
- rs.getBigDecimal(6),
- rs.getBigDecimal(7),
- rs.getDouble(8));
- topGainers.add(quote);
- }
- } finally {
- try {
- rs.close();
- } catch (SQLException e) {
- logger.debug("", e);
- }
- }
- } catch (SQLException e) {
- throw new DAOException("", e);
- } finally {
- if (gainers != null) {
- try {
- gainers.close();
- } catch (SQLException e) {
- logger.debug("", e);
- }
- }
- }
- List<CustomQuoteBean> topLosers = new ArrayList<CustomQuoteBean>();
- PreparedStatement losers = null;
- try {
- losers = sqlConnection.prepareStatement(SQL_SELECT_MARKETSUMMARY_LOSERS);
- ResultSet rs = losers.executeQuery();
-
- try {
- for (int i = 0; rs.next() && i < 5; i++) {
- CustomQuoteBean quote = new CustomQuoteBean(
- rs.getString(1),
- rs.getString(2),
- rs.getDouble(3),
- rs.getBigDecimal(4),
- rs.getBigDecimal(5),
- rs.getBigDecimal(6),
- rs.getBigDecimal(7),
- rs.getDouble(8));
- topLosers.add(quote);
- }
- } finally {
- try {
- rs.close();
- } catch (SQLException e) {
- logger.debug("", e);
- }
- }
- } catch (SQLException e) {
- throw new DAOException("", e);
- } finally {
- if (losers != null) {
- try {
- losers.close();
- } catch (SQLException e) {
- logger.debug("", e);
- }
- }
- }
- CustomMarketSummaryBean marketSummary = new CustomMarketSummaryBean(
- tSIA, openTSIA, totalVolume, topGainers, topLosers);
- return marketSummary;
- }
-}
+/*
+ * 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.stonehenge.stocktrader.mysql;
+
+import org.apache.stonehenge.stocktrader.mssql.AbstractMSSQLDAO;
+import org.apache.stonehenge.stocktrader.dal.MarketSummaryDAO;
+import org.apache.stonehenge.stocktrader.dal.DAOException;
+import org.apache.stonehenge.stocktrader.CustomQuoteBean;
+import org.apache.stonehenge.stocktrader.CustomMarketSummaryBean;
+import org.apache.stonehenge.stocktrader.util.StockTraderUtility;
+import org.apache.stonehenge.stocktrader.util.StockTraderSQLUtil;
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.math.BigDecimal;
+import java.util.List;
+import java.util.ArrayList;
+import org.apache.commons.logging.Log;
+import org.apache.commons.logging.LogFactory;
+
+public class MySQLMarketSummaryDAO extends AbstractMSSQLDAO implements MarketSummaryDAO {
+ private static final Log logger = LogFactory.getLog(MySQLMarketSummaryDAO.class);
+
+ private static final String SQL_SELECT_QUOTE = "SELECT symbol, companyname, volume, price, open1, low, high, change1 FROM quote WHERE symbol = ?";
+ private static final String SQL_SELECT_QUOTE_NOLOCK = "SELECT symbol, companyname, volume, price, open1, low, high, change1 FROM quote WHERE symbol = ?";
+ private static final String SQL_UPDATE_STOCKPRICEVOLUME = "UPDATE quote SET price = ?, low = ?, high = ?, change1 = ? - open1, volume = volume + ? WHERE symbol = ?";
+
+ private static final String SQL_SELECT_MARKETSUMMARY_GAINERS = "SELECT symbol, companyname, volume, price, open1, low, high, change1 FROM quote WHERE symbol LIKE 's:%' ORDER BY change1 DESC";
+ private static final String SQL_SELECT_MARKETSUMMARY_LOSERS = "SELECT symbol, companyname, volume, price, open1, low, high, change1 FROM quote WHERE symbol LIKE 's:%' ORDER BY change1";
+ private static final String SQL_SELECT_MARKETSUMMARY_TSIA = "SELECT SUM(price) / COUNT(*) as tsia FROM quote WHERE symbol LIKE 's:%'";
+ private static final String SQL_SELECT_MARKETSUMMARY_OPENTSIA = "SELECT SUM(open1) / COUNT(*) as opentsia FROM quote WHERE symbol LIKE 's:%'";
+ private static final String SQL_SELECT_MARKETSUMMARY_VOLUME = "SELECT SUM(volume) FROM quote WHERE symbol LIKE 's:%'";
+
+ public MySQLMarketSummaryDAO(Connection sqlConnection) throws DAOException {
+ super(sqlConnection);
+ }
+
+ public CustomQuoteBean getQuote(String symbol) throws DAOException {
+ if (logger.isDebugEnabled()) {
+ logger.debug("MarketSummaryDAO.getQouteForUpdate(String)\nSymbol :"+ symbol);
+ }
+ PreparedStatement selectQuote = null;
+ try {
+ selectQuote = sqlConnection.prepareStatement(SQL_SELECT_QUOTE_NOLOCK);
+ selectQuote.setString(1, symbol);
+ ResultSet rs = selectQuote.executeQuery();
+
+ try {
+ CustomQuoteBean quote = null;
+ if (rs.next()) {
+ quote = new CustomQuoteBean(
+ rs.getString(1),
+ rs.getString(2),
+ rs.getDouble(3),
+ rs.getBigDecimal(4),
+ rs.getBigDecimal(5),
+ rs.getBigDecimal(6),
+ rs.getBigDecimal(7),
+ rs.getDouble(8));
+ }
+ return quote;
+ } finally {
+ try {
+ rs.close();
+ } catch (SQLException e) {
+ logger.debug("", e);
+ }
+ }
+ } catch (SQLException e) {
+ throw new DAOException("", e);
+ } finally {
+ try {
+ if (selectQuote != null) {
+ selectQuote.close();
+ }
+ } catch (SQLException e) {
+ logger.debug("", e);
+ }
+ }
+ }
+
+ public CustomQuoteBean getQuoteForUpdate(String symbol) throws DAOException {
+ if (logger.isDebugEnabled()) {
+ logger.debug("MarketSummaryDAO.getQouteForUpdate(String)\nSymbol :"+ symbol);
+ }
+ PreparedStatement qouteForUpdateStat = null;
+ try {
+ qouteForUpdateStat = sqlConnection.prepareStatement(SQL_SELECT_QUOTE);
+ CustomQuoteBean quote = null;
+
+ qouteForUpdateStat.setString(1, symbol);
+ ResultSet rs = qouteForUpdateStat.executeQuery();
+
+ if (rs.next()) {
+ quote = new CustomQuoteBean(
+ rs.getString(1),
+ rs.getString(2),
+ rs.getDouble(3),
+ rs.getBigDecimal(4),
+ rs.getBigDecimal(5),
+ rs.getBigDecimal(6),
+ rs.getBigDecimal(7),
+ rs.getDouble(8));
+
+ try {
+ rs.close();
+ } catch (SQLException e) {
+ logger.debug("", e);
+ }
+ return quote;
+ } else {
+ throw new DAOException("No quote entry found");
+ }
+ } catch (SQLException e) {
+ throw new DAOException("", e);
+ } finally {
+ try {
+ if (qouteForUpdateStat != null) {
+ qouteForUpdateStat.close();
+ }
+ } catch (SQLException e) {
+ logger.debug("", e);
+ }
+ }
+ }
+
+ public void updateStockPriceVolume(double quantity, CustomQuoteBean quote) throws DAOException {
+ BigDecimal priceChangeFactor = StockTraderUtility.getRandomPriceChangeFactor(quote.getPrice());
+ BigDecimal newPrice = quote.getPrice().multiply(priceChangeFactor);
+
+ if (newPrice.compareTo(quote.getLow()) == -1) {
+ quote.setLow(newPrice);
+ }
+ if (newPrice.compareTo(quote.getHigh()) == 1) {
+ quote.setHigh(newPrice);
+ }
+
+ PreparedStatement updateStockPriceVolumeStat = null;
+ try {
+ updateStockPriceVolumeStat = sqlConnection.prepareStatement(SQL_UPDATE_STOCKPRICEVOLUME);
+ updateStockPriceVolumeStat.setBigDecimal(1, newPrice);
+ updateStockPriceVolumeStat.setBigDecimal(2, quote.getLow());
+ updateStockPriceVolumeStat.setBigDecimal(3, quote.getHigh());
+ updateStockPriceVolumeStat.setBigDecimal(4, newPrice);
+ updateStockPriceVolumeStat.setFloat(5, (float) quantity);
+ updateStockPriceVolumeStat.setString(6, quote.getSymbol());
+ updateStockPriceVolumeStat.executeUpdate();
+
+ } catch (SQLException e) {
+ throw new DAOException("", e);
+ } finally {
+ try {
+ if (updateStockPriceVolumeStat != null) {
+ updateStockPriceVolumeStat.close();
+ }
+ } catch (SQLException e) {
+ logger.debug("", e);
+ }
+ }
+ }
+
+ public CustomMarketSummaryBean getCustomMarketSummary() throws DAOException {
+ BigDecimal tSIA = (BigDecimal) StockTraderSQLUtil.executeScalarNoParm(SQL_SELECT_MARKETSUMMARY_TSIA, sqlConnection);
+ BigDecimal openTSIA = (BigDecimal) StockTraderSQLUtil.executeScalarNoParm(SQL_SELECT_MARKETSUMMARY_OPENTSIA, sqlConnection);
+ double totalVolume = ((Double) StockTraderSQLUtil.executeScalarNoParm(SQL_SELECT_MARKETSUMMARY_VOLUME, sqlConnection)).doubleValue();
+
+ List<CustomQuoteBean> topGainers = new ArrayList<CustomQuoteBean>();
+ PreparedStatement gainers = null;
+ try {
+ gainers = sqlConnection.prepareStatement(SQL_SELECT_MARKETSUMMARY_GAINERS);
+ ResultSet rs = gainers.executeQuery();
+
+ try {
+ for (int i = 0; rs.next() && i < 5; i++) {
+ CustomQuoteBean quote = new CustomQuoteBean(
+ rs.getString(1),
+ rs.getString(2),
+ rs.getDouble(3),
+ rs.getBigDecimal(4),
+ rs.getBigDecimal(5),
+ rs.getBigDecimal(6),
+ rs.getBigDecimal(7),
+ rs.getDouble(8));
+ topGainers.add(quote);
+ }
+ } finally {
+ try {
+ rs.close();
+ } catch (SQLException e) {
+ logger.debug("", e);
+ }
+ }
+ } catch (SQLException e) {
+ throw new DAOException("", e);
+ } finally {
+ if (gainers != null) {
+ try {
+ gainers.close();
+ } catch (SQLException e) {
+ logger.debug("", e);
+ }
+ }
+ }
+ List<CustomQuoteBean> topLosers = new ArrayList<CustomQuoteBean>();
+ PreparedStatement losers = null;
+ try {
+ losers = sqlConnection.prepareStatement(SQL_SELECT_MARKETSUMMARY_LOSERS);
+ ResultSet rs = losers.executeQuery();
+
+ try {
+ for (int i = 0; rs.next() && i < 5; i++) {
+ CustomQuoteBean quote = new CustomQuoteBean(
+ rs.getString(1),
+ rs.getString(2),
+ rs.getDouble(3),
+ rs.getBigDecimal(4),
+ rs.getBigDecimal(5),
+ rs.getBigDecimal(6),
+ rs.getBigDecimal(7),
+ rs.getDouble(8));
+ topLosers.add(quote);
+ }
+ } finally {
+ try {
+ rs.close();
+ } catch (SQLException e) {
+ logger.debug("", e);
+ }
+ }
+ } catch (SQLException e) {
+ throw new DAOException("", e);
+ } finally {
+ if (losers != null) {
+ try {
+ losers.close();
+ } catch (SQLException e) {
+ logger.debug("", e);
+ }
+ }
+ }
+ CustomMarketSummaryBean marketSummary = new CustomMarketSummaryBean(
+ tSIA, openTSIA, totalVolume, topGainers, topLosers);
+ return marketSummary;
+ }
+}
Propchange: incubator/stonehenge/trunk/stocktrader/metro/common/src/org/apache/stonehenge/stocktrader/mysql/MySQLMarketSummaryDAO.java
------------------------------------------------------------------------------
svn:eol-style = native