incubator-stonehenge-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From chint...@apache.org
Subject svn commit: r958838 [4/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...
Date Tue, 29 Jun 2010 06:33:57 GMT
Modified: incubator/stonehenge/trunk/stocktrader/metro/common/src/org/apache/stonehenge/stocktrader/mssql/MSSQLCustomerDAO.java
URL: http://svn.apache.org/viewvc/incubator/stonehenge/trunk/stocktrader/metro/common/src/org/apache/stonehenge/stocktrader/mssql/MSSQLCustomerDAO.java?rev=958838&r1=958837&r2=958838&view=diff
==============================================================================
--- incubator/stonehenge/trunk/stocktrader/metro/common/src/org/apache/stonehenge/stocktrader/mssql/MSSQLCustomerDAO.java (original)
+++ incubator/stonehenge/trunk/stocktrader/metro/common/src/org/apache/stonehenge/stocktrader/mssql/MSSQLCustomerDAO.java Tue Jun 29 06:33:54 2010
@@ -1,645 +1,645 @@
-/*
- * 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.mssql;
-
-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.dal.CustomerDAO;
-import org.apache.stonehenge.stocktrader.dal.DAOException;
-import org.apache.stonehenge.stocktrader.util.StockTraderUtility;
-
-public class MSSQLCustomerDAO extends AbstractMSSQLDAO implements CustomerDAO {
-	private static final Log logger = LogFactory.getLog(MSSQLCustomerDAO.class);
-
-	private static final String SQL_DEBIT_ACCOUNT = "UPDATE dbo.ACCOUNT WITH (ROWLOCK) SET BALANCE=(BALANCE-?) WHERE ACCOUNTID=?";
-	private static final String SQL_SELECT_HOLDING_LOCK = "Set NOCOUNT ON; SELECT dbo.HOLDING.ACCOUNT_ACCOUNTID, HOLDING.HOLDINGID, HOLDING.QUANTITY, HOLDING.PURCHASEPRICE, HOLDING.PURCHASEDATE, HOLDING.QUOTE_SYMBOL FROM dbo.HOLDING WITH (ROWLOCK) INNER JOIN ORDERS ON HOLDING.HOLDINGID = ORDERS.HOLDING_HOLDINGID WHERE (ORDERS.ORDERID = ?)";
-    // FIXED: HOLDING.HOLDINGID missing 
-	private static final String SQL_SELECT_HOLDING_NOLOCK = "Set NOCOUNT ON; SELECT HOLDING.ACCOUNT_ACCOUNTID, HOLDING.HOLDINGID, HOLDING.QUANTITY, HOLDING.PURCHASEPRICE, HOLDING.PURCHASEDATE, HOLDING.QUOTE_SYMBOL FROM HOLDING WITH(NOLOCK) WHERE HOLDING.HOLDINGID=? AND HOLDING.ACCOUNT_ACCOUNTID = (SELECT ACCOUNTID FROM dbo.ACCOUNT WHERE PROFILE_USERID = ?)";
-	private static final String SQL_SELECT_CUSTOMER_PROFILE_BY_USERID = "Set NOCOUNT ON; SELECT accountprofile.USERID, accountprofile.PASSWORD, accountprofile.FULLNAME, accountprofile.ADDRESS, accountprofile.EMAIL, accountprofile.CREDITCARD FROM dbo.accountprofile WITH (NOLOCK) WHERE accountprofile.USERID = ?";
-	private static final String SQL_UPDATE_CUSTOMER_LOGIN = "UPDATE dbo.account WITH (ROWLOCK) SET LOGINCOUNT = (LOGINCOUNT + 1), LASTLOGIN = CURRENT_TIMESTAMP where PROFILE_USERID = ?";
-	private static final String SQL_SELECT_CUSTOMER_LOGIN = "SELECT account.ACCOUNTID, account.CREATIONDATE, account.OPENBALANCE, account.LOGOUTCOUNT, account.BALANCE, account.LASTLOGIN, account.LOGINCOUNT FROM dbo.account WITH (ROWLOCK) WHERE account.PROFILE_USERID = ?";
-	private static final String SQL_UPDATE_LOGOUT = "UPDATE dbo.account WITH (ROWLOCK) SET LOGOUTCOUNT = (LOGOUTCOUNT + 1) where PROFILE_USERID= ?";
-	private static final String SQL_SELECT_GET_CUSTOMER_BY_USERID = "Set NOCOUNT ON; 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 dbo.orders o where o.account_accountid = (select a.accountid from dbo.account a WITH (NOLOCK)  where a.profile_userid = ?) ORDER BY o.ORDERID DESC";
-	private static final String SQL_SELECT_CLOSED_ORDERS = "Set NOCOUNT ON; SELECT ORDERID, ORDERTYPE, ORDERSTATUS, COMPLETIONDATE, OPENDATE, QUANTITY, PRICE, ORDERFEE, QUOTE_SYMBOL FROM dbo.orders WHERE ACCOUNT_ACCOUNTID = (select accountid from dbo.account WITH(NOLOCK) where profile_userid = ?) AND ORDERSTATUS = 'closed'";
-	private static final String SQL_UPDATE_CLOSED_ORDERS = "UPDATE dbo.orders SET ORDERSTATUS = 'completed' where ORDERSTATUS = 'closed' AND ACCOUNT_ACCOUNTID = (select accountid from dbo.account WITH (NOLOCK) where profile_userid = ?)";
-	private static final String SQL_INSERT_ACCOUNT_PROFILE = "INSERT INTO dbo.accountprofile VALUES (?, ?, ?, ?, ?, ?)";
-	private static final String SQL_INSERT_ACCOUNT = "INSERT INTO dbo.account (CREATIONDATE, OPENBALANCE, LOGOUTCOUNT, BALANCE, LASTLOGIN, LOGINCOUNT, PROFILE_USERID) VALUES (GetDate(), ?, ?, ?, ?, ?, ?); SELECT ID=@@IDENTITY";
-	private static final String SQL_UPDATE_ACCOUNT_PROFILE = "UPDATE dbo.accountprofile WITH (ROWLOCK) SET ADDRESS=?, PASSWORD=?, EMAIL=?, CREDITCARD=?, FULLNAME=? WHERE USERID=?";
-	private static final String SQL_SELECT_HOLDINGS = "SELECT HOLDING.HOLDINGID, HOLDING.QUANTITY, HOLDING.PURCHASEPRICE, HOLDING.PURCHASEDATE, HOLDING.QUOTE_SYMBOL,HOLDING.ACCOUNT_ACCOUNTID  from dbo.holding WHERE HOLDING.ACCOUNT_ACCOUNTID = (SELECT ACCOUNTID FROM ACCOUNT WHERE PROFILE_USERID = ?) ORDER BY HOLDING.HOLDINGID DESC";
-
-	public MSSQLCustomerDAO(Connection sqlConnection) throws DAOException {
-		super(sqlConnection);
-	}
-
-	public CustomHoldingBean getHoldingForUpdate(int orderId)
-			throws DAOException {
-		if (logger.isDebugEnabled()) {
-			logger
-					.debug("MSSQLCustomerDAO.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);
-            // FIXED: index starts from 1 rather than 0
-			selectHoldingNoLockStat.setInt(1, holdingID);
-			selectHoldingNoLockStat.setString(2, userId);
-
-			ResultSet rs = selectHoldingNoLockStat.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) {
-			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("MSSQLCustomerDAO.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 Top " + maxTop + SQL_SELECT_ORDERS_BY_ID;
-			} else {
-				sqlQuery = "Select Top " + maxDefault + SQL_SELECT_ORDERS_BY_ID;
-			}
-			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.mssql;
+
+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.dal.CustomerDAO;
+import org.apache.stonehenge.stocktrader.dal.DAOException;
+import org.apache.stonehenge.stocktrader.util.StockTraderUtility;
+
+public class MSSQLCustomerDAO extends AbstractMSSQLDAO implements CustomerDAO {
+	private static final Log logger = LogFactory.getLog(MSSQLCustomerDAO.class);
+
+	private static final String SQL_DEBIT_ACCOUNT = "UPDATE dbo.ACCOUNT WITH (ROWLOCK) SET BALANCE=(BALANCE-?) WHERE ACCOUNTID=?";
+	private static final String SQL_SELECT_HOLDING_LOCK = "Set NOCOUNT ON; SELECT dbo.HOLDING.ACCOUNT_ACCOUNTID, HOLDING.HOLDINGID, HOLDING.QUANTITY, HOLDING.PURCHASEPRICE, HOLDING.PURCHASEDATE, HOLDING.QUOTE_SYMBOL FROM dbo.HOLDING WITH (ROWLOCK) INNER JOIN ORDERS ON HOLDING.HOLDINGID = ORDERS.HOLDING_HOLDINGID WHERE (ORDERS.ORDERID = ?)";
+    // FIXED: HOLDING.HOLDINGID missing 
+	private static final String SQL_SELECT_HOLDING_NOLOCK = "Set NOCOUNT ON; SELECT HOLDING.ACCOUNT_ACCOUNTID, HOLDING.HOLDINGID, HOLDING.QUANTITY, HOLDING.PURCHASEPRICE, HOLDING.PURCHASEDATE, HOLDING.QUOTE_SYMBOL FROM HOLDING WITH(NOLOCK) WHERE HOLDING.HOLDINGID=? AND HOLDING.ACCOUNT_ACCOUNTID = (SELECT ACCOUNTID FROM dbo.ACCOUNT WHERE PROFILE_USERID = ?)";
+	private static final String SQL_SELECT_CUSTOMER_PROFILE_BY_USERID = "Set NOCOUNT ON; SELECT accountprofile.USERID, accountprofile.PASSWORD, accountprofile.FULLNAME, accountprofile.ADDRESS, accountprofile.EMAIL, accountprofile.CREDITCARD FROM dbo.accountprofile WITH (NOLOCK) WHERE accountprofile.USERID = ?";
+	private static final String SQL_UPDATE_CUSTOMER_LOGIN = "UPDATE dbo.account WITH (ROWLOCK) SET LOGINCOUNT = (LOGINCOUNT + 1), LASTLOGIN = CURRENT_TIMESTAMP where PROFILE_USERID = ?";
+	private static final String SQL_SELECT_CUSTOMER_LOGIN = "SELECT account.ACCOUNTID, account.CREATIONDATE, account.OPENBALANCE, account.LOGOUTCOUNT, account.BALANCE, account.LASTLOGIN, account.LOGINCOUNT FROM dbo.account WITH (ROWLOCK) WHERE account.PROFILE_USERID = ?";
+	private static final String SQL_UPDATE_LOGOUT = "UPDATE dbo.account WITH (ROWLOCK) SET LOGOUTCOUNT = (LOGOUTCOUNT + 1) where PROFILE_USERID= ?";
+	private static final String SQL_SELECT_GET_CUSTOMER_BY_USERID = "Set NOCOUNT ON; 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 dbo.orders o where o.account_accountid = (select a.accountid from dbo.account a WITH (NOLOCK)  where a.profile_userid = ?) ORDER BY o.ORDERID DESC";
+	private static final String SQL_SELECT_CLOSED_ORDERS = "Set NOCOUNT ON; SELECT ORDERID, ORDERTYPE, ORDERSTATUS, COMPLETIONDATE, OPENDATE, QUANTITY, PRICE, ORDERFEE, QUOTE_SYMBOL FROM dbo.orders WHERE ACCOUNT_ACCOUNTID = (select accountid from dbo.account WITH(NOLOCK) where profile_userid = ?) AND ORDERSTATUS = 'closed'";
+	private static final String SQL_UPDATE_CLOSED_ORDERS = "UPDATE dbo.orders SET ORDERSTATUS = 'completed' where ORDERSTATUS = 'closed' AND ACCOUNT_ACCOUNTID = (select accountid from dbo.account WITH (NOLOCK) where profile_userid = ?)";
+	private static final String SQL_INSERT_ACCOUNT_PROFILE = "INSERT INTO dbo.accountprofile VALUES (?, ?, ?, ?, ?, ?)";
+	private static final String SQL_INSERT_ACCOUNT = "INSERT INTO dbo.account (CREATIONDATE, OPENBALANCE, LOGOUTCOUNT, BALANCE, LASTLOGIN, LOGINCOUNT, PROFILE_USERID) VALUES (GetDate(), ?, ?, ?, ?, ?, ?); SELECT ID=@@IDENTITY";
+	private static final String SQL_UPDATE_ACCOUNT_PROFILE = "UPDATE dbo.accountprofile WITH (ROWLOCK) SET ADDRESS=?, PASSWORD=?, EMAIL=?, CREDITCARD=?, FULLNAME=? WHERE USERID=?";
+	private static final String SQL_SELECT_HOLDINGS = "SELECT HOLDING.HOLDINGID, HOLDING.QUANTITY, HOLDING.PURCHASEPRICE, HOLDING.PURCHASEDATE, HOLDING.QUOTE_SYMBOL,HOLDING.ACCOUNT_ACCOUNTID  from dbo.holding WHERE HOLDING.ACCOUNT_ACCOUNTID = (SELECT ACCOUNTID FROM ACCOUNT WHERE PROFILE_USERID = ?) ORDER BY HOLDING.HOLDINGID DESC";
+
+	public MSSQLCustomerDAO(Connection sqlConnection) throws DAOException {
+		super(sqlConnection);
+	}
+
+	public CustomHoldingBean getHoldingForUpdate(int orderId)
+			throws DAOException {
+		if (logger.isDebugEnabled()) {
+			logger
+					.debug("MSSQLCustomerDAO.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);
+            // FIXED: index starts from 1 rather than 0
+			selectHoldingNoLockStat.setInt(1, holdingID);
+			selectHoldingNoLockStat.setString(2, userId);
+
+			ResultSet rs = selectHoldingNoLockStat.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) {
+			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("MSSQLCustomerDAO.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 Top " + maxTop + SQL_SELECT_ORDERS_BY_ID;
+			} else {
+				sqlQuery = "Select Top " + maxDefault + SQL_SELECT_ORDERS_BY_ID;
+			}
+			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/mssql/MSSQLCustomerDAO.java
------------------------------------------------------------------------------
    svn:eol-style = native

Modified: incubator/stonehenge/trunk/stocktrader/metro/common/src/org/apache/stonehenge/stocktrader/mssql/MSSQLDAOFactory.java
URL: http://svn.apache.org/viewvc/incubator/stonehenge/trunk/stocktrader/metro/common/src/org/apache/stonehenge/stocktrader/mssql/MSSQLDAOFactory.java?rev=958838&r1=958837&r2=958838&view=diff
==============================================================================
--- incubator/stonehenge/trunk/stocktrader/metro/common/src/org/apache/stonehenge/stocktrader/mssql/MSSQLDAOFactory.java (original)
+++ incubator/stonehenge/trunk/stocktrader/metro/common/src/org/apache/stonehenge/stocktrader/mssql/MSSQLDAOFactory.java Tue Jun 29 06:33:54 2010
@@ -1,124 +1,124 @@
-/*
- * 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.mssql;
-
-import org.apache.commons.logging.Log;
-import org.apache.commons.logging.LogFactory;
-import org.apache.stonehenge.stocktrader.dal.*;
-
-import java.sql.Connection;
-import java.sql.DriverManager;
-import java.sql.SQLException;
-
-public class MSSQLDAOFactory extends DAOFactory {
-    private static Log logger = LogFactory.getLog(MSSQLDAOFactory.class);
-    private static MSSQLDAOFactory self = null;
-
-    private Connection sqlConnection = null;
-
-    private String connection = null;
-
-    static {
-        try {
-            Class.forName("net.sourceforge.jtds.jdbc.Driver");
-        } catch (ClassNotFoundException e) {
-            logger.warn("Unable to load DBDrive class", e);
-        }
-    }
-
-    public static DAOFactory getInstance() {
-        if (self == null) {
-            self = new MSSQLDAOFactory();
-        }
-        return self;
-    }
-
-    private MSSQLDAOFactory() {
-    }
-
-    public OrderDAO getOrderDAO() throws DAOException {
-        logger.debug("MSSQLDAOFactory.getOrderDAO");
-        try {
-            OrderDAO orderDAO = new MSSQLOrderDAO(getConnection());
-            return orderDAO;
-        } catch (SQLException e) {
-            logger.debug("", e);
-            throw new DAOException(
-                    "Exception was thrown when instantiating MSSQLOrderDAO object",
-                    e);
-        }
-    }
-
-    public CustomerDAO getCustomerDAO() throws DAOException {
-        logger.debug("MSSQLDAOFactory.getOrderDAO");
-        try {
-            CustomerDAO customerDAO = new MSSQLCustomerDAO(getConnection());
-            return customerDAO;
-        } catch (SQLException e) {
-            logger.debug("", e);
-            throw new DAOException(
-                    "Exception was thrown when instantiating a MSSQLCustomerDAO",
-                    e);
-        }
-    }
-
-    public MarketSummaryDAO getMarketSummaryDAO() throws DAOException {
-        logger.debug("MSSQLDAOFactory.getOrderDAO");
-        try {
-            MarketSummaryDAO marketSummaryDAO = new MSSQLMarketSummaryDAO(
-                    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 == null) {
-                connection = "jdbc:jtds:sqlserver://highlander:1433/StockTraderDB;user=trade;password=trade";
-            } else {
-                StringBuffer buf = new StringBuffer();
-                buf.append("jdbc:jtds:sqlserver://");
-                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));
-                buf.append(";");
-                connection = buf.toString();
-            }
-        }
-
-        if (logger.isDebugEnabled()) {
-            logger.debug("MSSQLDAOFactory.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.mssql;
+
+import org.apache.commons.logging.Log;
+import org.apache.commons.logging.LogFactory;
+import org.apache.stonehenge.stocktrader.dal.*;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.SQLException;
+
+public class MSSQLDAOFactory extends DAOFactory {
+    private static Log logger = LogFactory.getLog(MSSQLDAOFactory.class);
+    private static MSSQLDAOFactory self = null;
+
+    private Connection sqlConnection = null;
+
+    private String connection = null;
+
+    static {
+        try {
+            Class.forName("net.sourceforge.jtds.jdbc.Driver");
+        } catch (ClassNotFoundException e) {
+            logger.warn("Unable to load DBDrive class", e);
+        }
+    }
+
+    public static DAOFactory getInstance() {
+        if (self == null) {
+            self = new MSSQLDAOFactory();
+        }
+        return self;
+    }
+
+    private MSSQLDAOFactory() {
+    }
+
+    public OrderDAO getOrderDAO() throws DAOException {
+        logger.debug("MSSQLDAOFactory.getOrderDAO");
+        try {
+            OrderDAO orderDAO = new MSSQLOrderDAO(getConnection());
+            return orderDAO;
+        } catch (SQLException e) {
+            logger.debug("", e);
+            throw new DAOException(
+                    "Exception was thrown when instantiating MSSQLOrderDAO object",
+                    e);
+        }
+    }
+
+    public CustomerDAO getCustomerDAO() throws DAOException {
+        logger.debug("MSSQLDAOFactory.getOrderDAO");
+        try {
+            CustomerDAO customerDAO = new MSSQLCustomerDAO(getConnection());
+            return customerDAO;
+        } catch (SQLException e) {
+            logger.debug("", e);
+            throw new DAOException(
+                    "Exception was thrown when instantiating a MSSQLCustomerDAO",
+                    e);
+        }
+    }
+
+    public MarketSummaryDAO getMarketSummaryDAO() throws DAOException {
+        logger.debug("MSSQLDAOFactory.getOrderDAO");
+        try {
+            MarketSummaryDAO marketSummaryDAO = new MSSQLMarketSummaryDAO(
+                    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 == null) {
+                connection = "jdbc:jtds:sqlserver://highlander:1433/StockTraderDB;user=trade;password=trade";
+            } else {
+                StringBuffer buf = new StringBuffer();
+                buf.append("jdbc:jtds:sqlserver://");
+                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));
+                buf.append(";");
+                connection = buf.toString();
+            }
+        }
+
+        if (logger.isDebugEnabled()) {
+            logger.debug("MSSQLDAOFactory.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/mssql/MSSQLDAOFactory.java
------------------------------------------------------------------------------
    svn:eol-style = native

Modified: incubator/stonehenge/trunk/stocktrader/metro/common/src/org/apache/stonehenge/stocktrader/mssql/MSSQLMarketSummaryDAO.java
URL: http://svn.apache.org/viewvc/incubator/stonehenge/trunk/stocktrader/metro/common/src/org/apache/stonehenge/stocktrader/mssql/MSSQLMarketSummaryDAO.java?rev=958838&r1=958837&r2=958838&view=diff
==============================================================================
--- incubator/stonehenge/trunk/stocktrader/metro/common/src/org/apache/stonehenge/stocktrader/mssql/MSSQLMarketSummaryDAO.java (original)
+++ incubator/stonehenge/trunk/stocktrader/metro/common/src/org/apache/stonehenge/stocktrader/mssql/MSSQLMarketSummaryDAO.java Tue Jun 29 06:33:54 2010
@@ -1,260 +1,260 @@
-/*
- * 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.mssql;
-
-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.List;
-
-import org.apache.commons.logging.Log;
-import org.apache.commons.logging.LogFactory;
-import org.apache.stonehenge.stocktrader.CustomMarketSummaryBean;
-import org.apache.stonehenge.stocktrader.CustomQuoteBean;
-import org.apache.stonehenge.stocktrader.dal.DAOException;
-import org.apache.stonehenge.stocktrader.dal.MarketSummaryDAO;
-import org.apache.stonehenge.stocktrader.util.StockTraderSQLUtil;
-import org.apache.stonehenge.stocktrader.util.StockTraderUtility;
-
-public class MSSQLMarketSummaryDAO extends AbstractMSSQLDAO implements
-		MarketSummaryDAO {
-	private static final Log logger = LogFactory
-			.getLog(MSSQLMarketSummaryDAO.class);
-
-	private static final String SQL_SELECT_QUOTE = "Set NOCOUNT ON; SELECT symbol, companyname, volume, price, open1, low, high, change1 from dbo.quote with (ROWLOCK) where symbol = ?";
-	private static final String SQL_SELECT_QUOTE_NOLOCK = "Set NOCOUNT ON; SELECT symbol, companyname, volume, price, open1, low, high, change1 from dbo.quote with (NOLOCK) where symbol = ?";
-	private static final String SQL_UPDATE_STOCKPRICEVOLUME = "UPDATE dbo.QUOTE WITH (ROWLOCK) SET PRICE=?, Low=?, High=?, Change1=?-open1, VOLUME=VOLUME+? WHERE SYMBOL=?";
-
-	private static final String SQL_SELECT_MARKETSUMMARY_GAINERS = "Set NOCOUNT ON; SELECT symbol, companyname, volume, price, open1, low, high, change1 from dbo.quote with (NOLOCK) where symbol like 's:1__' order by change1 desc";
-	private static final String SQL_SELECT_MARKETSUMMARY_LOSERS = "Set NOCOUNT ON; SELECT symbol, companyname, volume, price, open1, low, high, change1 from dbo.quote with (NOLOCK) where symbol like 's:1__' order by change1";
-	private static final String SQL_SELECT_MARKETSUMMARY_TSIA = "Set NOCOUNT ON; select SUM(price)/count(*) as TSIA from dbo.quote where symbol like 's:1__'";
-	private static final String SQL_SELECT_MARKETSUMMARY_OPENTSIA = "Set NOCOUNT ON; select SUM(open1)/count(*) as openTSIA from dbo.quote where symbol like 's:1__'";
-	private static final String SQL_SELECT_MARKETSUMMARY_VOLUME = "Set NOCOUNT ON; SELECT SUM(volume) from dbo.quote where symbol like 's:1__'";
-
-	public MSSQLMarketSummaryDAO(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.mssql;
+
+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.List;
+
+import org.apache.commons.logging.Log;
+import org.apache.commons.logging.LogFactory;
+import org.apache.stonehenge.stocktrader.CustomMarketSummaryBean;
+import org.apache.stonehenge.stocktrader.CustomQuoteBean;
+import org.apache.stonehenge.stocktrader.dal.DAOException;
+import org.apache.stonehenge.stocktrader.dal.MarketSummaryDAO;
+import org.apache.stonehenge.stocktrader.util.StockTraderSQLUtil;
+import org.apache.stonehenge.stocktrader.util.StockTraderUtility;
+
+public class MSSQLMarketSummaryDAO extends AbstractMSSQLDAO implements
+		MarketSummaryDAO {
+	private static final Log logger = LogFactory
+			.getLog(MSSQLMarketSummaryDAO.class);
+
+	private static final String SQL_SELECT_QUOTE = "Set NOCOUNT ON; SELECT symbol, companyname, volume, price, open1, low, high, change1 from dbo.quote with (ROWLOCK) where symbol = ?";
+	private static final String SQL_SELECT_QUOTE_NOLOCK = "Set NOCOUNT ON; SELECT symbol, companyname, volume, price, open1, low, high, change1 from dbo.quote with (NOLOCK) where symbol = ?";
+	private static final String SQL_UPDATE_STOCKPRICEVOLUME = "UPDATE dbo.QUOTE WITH (ROWLOCK) SET PRICE=?, Low=?, High=?, Change1=?-open1, VOLUME=VOLUME+? WHERE SYMBOL=?";
+
+	private static final String SQL_SELECT_MARKETSUMMARY_GAINERS = "Set NOCOUNT ON; SELECT symbol, companyname, volume, price, open1, low, high, change1 from dbo.quote with (NOLOCK) where symbol like 's:1__' order by change1 desc";
+	private static final String SQL_SELECT_MARKETSUMMARY_LOSERS = "Set NOCOUNT ON; SELECT symbol, companyname, volume, price, open1, low, high, change1 from dbo.quote with (NOLOCK) where symbol like 's:1__' order by change1";
+	private static final String SQL_SELECT_MARKETSUMMARY_TSIA = "Set NOCOUNT ON; select SUM(price)/count(*) as TSIA from dbo.quote where symbol like 's:1__'";
+	private static final String SQL_SELECT_MARKETSUMMARY_OPENTSIA = "Set NOCOUNT ON; select SUM(open1)/count(*) as openTSIA from dbo.quote where symbol like 's:1__'";
+	private static final String SQL_SELECT_MARKETSUMMARY_VOLUME = "Set NOCOUNT ON; SELECT SUM(volume) from dbo.quote where symbol like 's:1__'";
+
+	public MSSQLMarketSummaryDAO(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/mssql/MSSQLMarketSummaryDAO.java
------------------------------------------------------------------------------
    svn:eol-style = native



Mime
View raw message