incubator-stonehenge-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From bende...@apache.org
Subject svn commit: r801507 [2/8] - in /incubator/stonehenge/contrib/stocktrader/metro: ./ business_service/ business_service/config/ business_service/etc/ business_service/etc/order_processor/ business_service/lib/ business_service/src/ business_service/src/o...
Date Thu, 06 Aug 2009 03:41:20 GMT
Added: incubator/stonehenge/contrib/stocktrader/metro/business_service/src/org/apache/stonehenge/stocktrader/mssql/MSSQLCustomerDAO.java
URL: http://svn.apache.org/viewvc/incubator/stonehenge/contrib/stocktrader/metro/business_service/src/org/apache/stonehenge/stocktrader/mssql/MSSQLCustomerDAO.java?rev=801507&view=auto
==============================================================================
--- incubator/stonehenge/contrib/stocktrader/metro/business_service/src/org/apache/stonehenge/stocktrader/mssql/MSSQLCustomerDAO.java (added)
+++ incubator/stonehenge/contrib/stocktrader/metro/business_service/src/org/apache/stonehenge/stocktrader/mssql/MSSQLCustomerDAO.java Thu Aug  6 03:41:14 2009
@@ -0,0 +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);
+				}
+			}
+		}
+	}
+}

Added: incubator/stonehenge/contrib/stocktrader/metro/business_service/src/org/apache/stonehenge/stocktrader/mssql/MSSQLDAOFactory.java
URL: http://svn.apache.org/viewvc/incubator/stonehenge/contrib/stocktrader/metro/business_service/src/org/apache/stonehenge/stocktrader/mssql/MSSQLDAOFactory.java?rev=801507&view=auto
==============================================================================
--- incubator/stonehenge/contrib/stocktrader/metro/business_service/src/org/apache/stonehenge/stocktrader/mssql/MSSQLDAOFactory.java (added)
+++ incubator/stonehenge/contrib/stocktrader/metro/business_service/src/org/apache/stonehenge/stocktrader/mssql/MSSQLDAOFactory.java Thu Aug  6 03:41:14 2009
@@ -0,0 +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;
+    }
+}

Added: incubator/stonehenge/contrib/stocktrader/metro/business_service/src/org/apache/stonehenge/stocktrader/mssql/MSSQLMarketSummaryDAO.java
URL: http://svn.apache.org/viewvc/incubator/stonehenge/contrib/stocktrader/metro/business_service/src/org/apache/stonehenge/stocktrader/mssql/MSSQLMarketSummaryDAO.java?rev=801507&view=auto
==============================================================================
--- incubator/stonehenge/contrib/stocktrader/metro/business_service/src/org/apache/stonehenge/stocktrader/mssql/MSSQLMarketSummaryDAO.java (added)
+++ incubator/stonehenge/contrib/stocktrader/metro/business_service/src/org/apache/stonehenge/stocktrader/mssql/MSSQLMarketSummaryDAO.java Thu Aug  6 03:41:14 2009
@@ -0,0 +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;
+	}
+}

Added: incubator/stonehenge/contrib/stocktrader/metro/business_service/src/org/apache/stonehenge/stocktrader/mssql/MSSQLOrderDAO.java
URL: http://svn.apache.org/viewvc/incubator/stonehenge/contrib/stocktrader/metro/business_service/src/org/apache/stonehenge/stocktrader/mssql/MSSQLOrderDAO.java?rev=801507&view=auto
==============================================================================
--- incubator/stonehenge/contrib/stocktrader/metro/business_service/src/org/apache/stonehenge/stocktrader/mssql/MSSQLOrderDAO.java (added)
+++ incubator/stonehenge/contrib/stocktrader/metro/business_service/src/org/apache/stonehenge/stocktrader/mssql/MSSQLOrderDAO.java Thu Aug  6 03:41:14 2009
@@ -0,0 +1,453 @@
+/*
+ * 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.sql.Types;
+import java.util.Calendar;
+
+import org.apache.commons.logging.Log;
+import org.apache.commons.logging.LogFactory;
+import org.apache.stonehenge.stocktrader.CustomHoldingBean;
+import org.apache.stonehenge.stocktrader.CustomOrderBean;
+import org.apache.stonehenge.stocktrader.CustomQuoteBean;
+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;
+import org.apache.stonehenge.stocktrader.util.StockTraderUtility;
+
+public class MSSQLOrderDAO extends AbstractMSSQLDAO implements OrderDAO {
+	private static Log logger = LogFactory.getLog(MSSQLOrderDAO.class);
+
+	private static final String SQL_GET_ACCOUNTID_ORDER = "Set NOCOUNT ON; SELECT ACCOUNT_ACCOUNTID FROM dbo.ORDERS  WITH (NOLOCK) WHERE ORDERID=?;";
+	private static final String SQL_INSERT_HOLDING = "INSERT INTO dbo.HOLDING (PURCHASEPRICE, QUANTITY, PURCHASEDATE, ACCOUNT_ACCOUNTID, QUOTE_SYMBOL) VALUES (?, ?, ?, ?, ?); SELECT ID=@@IDENTITY ;";
+	private static final String SQL_UPDATE_HOLDING = "UPDATE dbo.HOLDING WITH (ROWLOCK) SET QUANTITY=QUANTITY-? WHERE HOLDINGID=? ;";
+	private static final String SQL_DELETE_HOLDING = "DELETE FROM dbo.HOLDING WITH (ROWLOCK) WHERE HOLDINGID=?";
+	private static final String SQL_SELECT_HOLDING = "SELECT HOLDING.HOLDINGID, HOLDING.QUANTITY, HOLDING.PURCHASEPRICE, HOLDING.PURCHASEDATE, HOLDING.QUOTE_SYMBOL,HOLDING.ACCOUNT_ACCOUNTID FROM dbo.HOLDING WITH (NOLOCK) WHERE HOLDINGID= ?";
+	private static final String SQL_UPDATE_ORDER = "UPDATE dbo.ORDERS WITH (ROWLOCK) SET QUANTITY=? WHERE ORDERID=?";
+	private static final String SQL_CLOSE_ORDER = "UPDATE dbo.ORDERS WITH (ROWLOCK) SET ORDERSTATUS = ?, COMPLETIONDATE=GetDate(), HOLDING_HOLDINGID=?, PRICE=? WHERE ORDERID = ?";
+	private static final String SQL_GET_ACCOUNTID = "Set NOCOUNT ON; SELECT ACCOUNTID FROM dbo.ACCOUNT  WITH (NOLOCK) WHERE PROFILE_USERID = ?;";
+
+	// CHECKME
+	private static final String SQL_INSERT_ORDER = "INSERT INTO dbo.ORDERS (OPENDATE, ORDERFEE, PRICE, QUOTE_SYMBOL, QUANTITY, ORDERTYPE, ORDERSTATUS, ACCOUNT_ACCOUNTID, HOLDING_HOLDINGID) VALUES (GetDate(), ?, ?, ?, ?, ?, 'open', ?, ?); SELECT ID=@@IDENTITY ;";
+
+	public MSSQLOrderDAO(Connection sqlConnection) throws DAOException {
+		super(sqlConnection);
+	}
+
+	public CustomQuoteBean getQuoteForUpdate(String symbol) throws DAOException {
+		if (logger.isDebugEnabled()) {
+			logger.debug("OrderDAO.getQuoteForUpdate()\nSymbol :" + symbol);
+		}
+
+		DAOFactory fac = MSSQLDAOFactory.getInstance();
+		MarketSummaryDAO marketSummaryDAO = fac.getMarketSummaryDAO();
+		return marketSummaryDAO.getQuoteForUpdate(symbol);
+	}
+
+	public int createHolding(CustomOrderBean order) throws DAOException {
+		if (logger.isDebugEnabled()) {
+			logger.debug("OrderDAO.createHolding(OrderDataModel)\nOrderID :"
+					+ order.getOrderID() + "\nOrderType :"
+					+ order.getOrderType() + "\nSymbol :" + order.getSymbol()
+					+ "\nQuantity :" + order.getQuantity() + "\nOrder Status :"
+					+ order.getOrderStatus() + "\nOrder Open Date :"
+					+ order.getOpenDate() + "\nCompletionDate :"
+					+ order.getCompletionDate());
+		}
+
+		PreparedStatement getAccountIdStat = null;
+		int accountId = -1;
+
+		try {
+			getAccountIdStat = sqlConnection
+					.prepareStatement(SQL_GET_ACCOUNTID_ORDER);
+			getAccountIdStat.setInt(1, order.getOrderID());
+
+			ResultSet rs = getAccountIdStat.executeQuery();
+			if (rs.next()) {
+				accountId = Integer.parseInt(rs.getString(1));
+				order.setAccountId(accountId);
+			}
+
+			try {
+				rs.close();
+			} catch (Exception e) {
+				logger.debug("", e);
+			}
+		} catch (SQLException e) {
+			throw new DAOException(
+					"Exception is thrown when selecting the accountID from order entries where order ID :"
+							+ order.getOrderID(), e);
+
+		} finally {
+			if (getAccountIdStat != null) {
+				try {
+					getAccountIdStat.close();
+				} catch (Exception e) {
+					logger.debug("", e);
+				}
+			}
+		}
+
+		if (accountId != -1) {
+			int holdingId = -1;
+			PreparedStatement insertHoldingStat = null;
+
+			try {
+				insertHoldingStat = sqlConnection
+						.prepareStatement(SQL_INSERT_HOLDING);
+				insertHoldingStat.setBigDecimal(1, order.getPrice());
+				// C# - insertHolding.setFloat(1, (float) order.getQuantity());
+				insertHoldingStat.setDouble(2, order.getQuantity());
+				Calendar openDate = (order.getOpenDate() != null) ? order
+						.getOpenDate() : Calendar.getInstance();
+				insertHoldingStat.setDate(3, StockTraderUtility
+						.convertToSqlDate(openDate));
+				insertHoldingStat.setInt(4, order.getAccountId());
+				insertHoldingStat.setString(5, order.getSymbol());
+
+				ResultSet rs = insertHoldingStat.executeQuery();
+				if (rs.next()) {
+					holdingId = rs.getInt(1);
+				}
+
+				try {
+					rs.close();
+				} catch (Exception e) {
+					logger.debug("", e);
+				}
+				return holdingId;
+
+			} catch (SQLException e) {
+				throw new DAOException(
+						"An exception is thrown during an insertion of a holding entry",
+						e);
+
+			} finally {
+				if (insertHoldingStat != null) {
+					try {
+						insertHoldingStat.close();
+					} catch (Exception e) {
+						logger.debug("", e);
+					}
+				}
+			}
+		}
+		return -1;
+	}
+
+	public void updateHolding(int holdingId, double quantity)
+			throws DAOException {
+		if (logger.isDebugEnabled()) {
+			logger.debug("OrderDAO.updateHolding()\nHolding ID :" + holdingId
+					+ "\nQuantity :" + quantity);
+		}
+
+		PreparedStatement updateHoldingStat = null;
+		try {
+			updateHoldingStat = sqlConnection
+					.prepareStatement(SQL_UPDATE_HOLDING);
+			updateHoldingStat.setDouble(1, quantity);
+			updateHoldingStat.setInt(2, holdingId);
+			updateHoldingStat.executeUpdate();
+
+		} catch (SQLException e) {
+			throw new DAOException(
+					"An exception is thrown during an updation of holding entry",
+					e);
+		} finally {
+			if (updateHoldingStat != null) {
+				try {
+					updateHoldingStat.close();
+				} catch (Exception e) {
+					logger.debug("", e);
+				}
+			}
+		}
+	}
+
+	public void deleteHolding(int holdingId) throws DAOException {
+		if (logger.isDebugEnabled()) {
+			logger.debug("OrderDAO.deleteHolding()\nHolding ID :" + holdingId);
+		}
+
+		PreparedStatement deleteHoldingStat = null;
+		try {
+			deleteHoldingStat = sqlConnection
+					.prepareStatement(SQL_DELETE_HOLDING);
+			deleteHoldingStat.setInt(1, holdingId);
+			deleteHoldingStat.execute();
+
+		} catch (SQLException e) {
+			throw new DAOException(
+					"An exception is thrown during deletion of a holding entry",
+					e);
+		} finally {
+			if (deleteHoldingStat != null) {
+				try {
+					deleteHoldingStat.close();
+				} catch (SQLException e) {
+					logger.debug("", e);
+				}
+			}
+		}
+	}
+
+	public CustomHoldingBean getHoldingForUpdate(int orderId)
+			throws DAOException {
+		if (logger.isDebugEnabled()) {
+			logger.debug("HoldingDataModel.getHoldingForUpdate()\nOrder ID :"
+					+ orderId);
+		}
+		DAOFactory fac = MSSQLDAOFactory.getInstance();
+		CustomerDAO customerDAO = fac.getCustomerDAO();
+		return customerDAO.getHoldingForUpdate(orderId);
+	}
+
+	public CustomHoldingBean getHolding(int holdingId) throws DAOException {
+		CustomHoldingBean holding = null;
+		PreparedStatement selectHoldingStat = null;
+		try {
+			selectHoldingStat = sqlConnection
+					.prepareStatement(SQL_SELECT_HOLDING);
+			selectHoldingStat.setInt(1, holdingId);
+			ResultSet rs = selectHoldingStat.executeQuery();
+			if (rs.next()) {
+				try {
+					holding = new CustomHoldingBean(
+							rs.getInt(1),
+							rs.getDouble(2),
+							rs.getBigDecimal(3),
+							StockTraderUtility.convertToCalendar(rs.getDate(4)),
+							rs.getString(5),
+							rs.getInt(6));
+					return holding;
+
+				} finally {
+					try {
+						rs.close();
+					} catch (Exception e) {
+						logger.debug("", e);
+					}
+				}
+			}
+		} catch (SQLException e) {
+			throw new DAOException(
+					"An Exception is thrown during selecting a holding entry",
+					e);
+		} finally {
+			if (selectHoldingStat != null) {
+				try {
+					selectHoldingStat.close();
+				} catch (SQLException e) {
+					logger.debug("", e);
+				}
+			}
+		}
+		return holding;
+	}
+
+	public void updateAccountBalance(int accountId, BigDecimal total)
+			throws DAOException {
+		if (logger.isDebugEnabled()) {
+			logger
+					.debug("OrderDAO.updateAccountBalance(int,BigDecimal)\nAccount ID :"
+							+ accountId + "\nTotal :" + total);
+		}
+		DAOFactory fac = MSSQLDAOFactory.getInstance();
+		CustomerDAO customerDAO = fac.getCustomerDAO();
+		customerDAO.updateAccountBalance(accountId, total);
+	}
+
+	public void updateStockPriceVolume(double quantity, CustomQuoteBean quote)
+			throws DAOException {
+		if (logger.isDebugEnabled()) {
+			logger
+					.debug("OrderDAO.updateStockPriceVolume(double,QuatedataModle)\nQuantity :"
+							+ quantity + "\nQuote\nSymbol" + quote.getSymbol());
+		}
+		DAOFactory fac = MSSQLDAOFactory.getInstance();
+		MarketSummaryDAO marketSummaryDAO = fac.getMarketSummaryDAO();
+		marketSummaryDAO.updateStockPriceVolume(quantity, quote);
+	}
+
+	public void updateOrder(CustomOrderBean order) throws DAOException {
+		PreparedStatement updateHoldingStat = null;
+		try {
+			updateHoldingStat = sqlConnection
+					.prepareStatement(SQL_UPDATE_ORDER);
+			updateHoldingStat.setDouble(1, order.getQuantity());
+			updateHoldingStat.setInt(2, order.getOrderID());
+			updateHoldingStat.executeUpdate();
+
+		} catch (SQLException e) {
+			throw new DAOException(
+					"An Exception is thrown during updating a holding entry", e);
+		} finally {
+			if (updateHoldingStat != null) {
+				try {
+					updateHoldingStat.close();
+				} catch (Exception e) {
+					logger.debug("", e);
+				}
+			}
+		}
+	}
+
+	public void closeOrder(CustomOrderBean order) throws DAOException {
+		if (logger.isDebugEnabled()) {
+			logger.debug("OrderDAO.closeOrder(OrderDataModel)\nOrderID :"
+					+ order.getOrderID() + "\nOrderType :"
+					+ order.getOrderType() + "\nSymbol :" + order.getSymbol()
+					+ "\nQuantity :" + order.getQuantity() + "\nOrder Status :"
+					+ order.getOrderStatus() + "\nOrder Open Date :"
+					+ order.getOpenDate() + "\nCompletionDate :"
+					+ order.getCompletionDate());
+		}
+
+		PreparedStatement closeOrderStat = null;
+		try {
+			closeOrderStat = sqlConnection.prepareStatement(SQL_CLOSE_ORDER);
+			closeOrderStat.setString(1, StockTraderUtility.ORDER_STATUS_CLOSED);
+			if (StockTraderUtility.ORDER_TYPE_SELL.equals(order.getOrderType())) {
+				closeOrderStat.setNull(2, Types.INTEGER);
+			} else {
+				closeOrderStat.setInt(2, order.getHoldingId());
+			}
+			closeOrderStat.setBigDecimal(3, order.getPrice());
+			closeOrderStat.setInt(4, order.getOrderID());
+			closeOrderStat.executeUpdate();
+
+		} catch (SQLException e) {
+			throw new DAOException("", e);
+
+		} finally {
+			if (closeOrderStat != null) {
+				try {
+					closeOrderStat.close();
+				} catch (Exception e) {
+					logger.debug("", e);
+				}
+			}
+		}
+	}
+
+	public CustomOrderBean createOrder(String userID, String symbol,
+			String orderType, double quantity, int holdingID)
+			throws DAOException {
+		int orderID = 0;
+		Calendar minCalender = Calendar.getInstance();
+		minCalender.setTimeInMillis(0);
+		CustomOrderBean order = new CustomOrderBean(orderID, orderType,
+				StockTraderUtility.ORDER_STATUS_OPEN, Calendar.getInstance(),
+				minCalender, quantity, BigDecimal.valueOf(1),
+				StockTraderUtility.getOrderFee(orderType), symbol);
+		order.setHoldingId(holdingID);
+
+		PreparedStatement getAccountId = null;
+		try {
+			getAccountId = sqlConnection.prepareStatement(SQL_GET_ACCOUNTID);
+			getAccountId.setString(1, userID);
+			ResultSet rs = getAccountId.executeQuery();
+			if (rs.next()) {
+				order.setAccountId(rs.getInt(1));
+			}
+		} catch (SQLException e) {
+
+		} finally {
+			if (getAccountId != null) {
+				try {
+					getAccountId.close();
+				} catch (SQLException e) {
+					logger.debug("", e);
+				}
+			}
+		}
+
+		PreparedStatement insertOrder = null;
+//		PreparedStatement selectOrderID = null;
+		try {
+            // FIXED: prepare statement rather than call
+            insertOrder = sqlConnection.prepareStatement(SQL_INSERT_ORDER);
+//			insertOrder = sqlConnection.prepareCall(SQL_INSERT_ORDER);
+
+			insertOrder.setBigDecimal(1, order.getOrderFee());
+			insertOrder.setBigDecimal(2, order.getPrice());
+			insertOrder.setString(3, order.getSymbol());
+
+            // FIXED: metro used Double rather than double
+//			insertOrder.setFloat(4, (float) order.getQuantity());
+			insertOrder.setFloat(4, order.getQuantity().floatValue());
+			insertOrder.setString(5, order.getOrderType());
+			insertOrder.setInt(6, order.getAccountId());
+			insertOrder.setInt(7, order.getHoldingId());
+			ResultSet rs = insertOrder.executeQuery();
+			
+
+//			selectOrderID = sqlConnection.prepareStatement(SQL_SELECT_ORDER_ID);
+//			// ORDERFEE = ? AND PRICE = ? AND QUOTE_SYMBOL = ? AND QUANTITY = ?
+//			// ORDERTYPE = ? ORDERSTATUS = ? AND ACCOUNT_ACCOUNTID = ?
+//			// HOLDING_HOLDINGID = ?"
+//			selectOrderID.setBigDecimal(1, order.getOrderFee());
+//			selectOrderID.setBigDecimal(2, order.getPrice());
+//			selectOrderID.setString(3, order.getSymbol());
+//			selectOrderID.setDouble(4, order.getQuantity());
+//			selectOrderID.setString(5, order.getOrderType());
+//			selectOrderID.setString(6, "open");
+//			selectOrderID.setInt(7, order.getAccountId());
+//			selectOrderID.setInt(8, order.getHoldingId());
+//			ResultSet rs = selectOrderID.executeQuery();
+			if (rs.next()) {
+				try {
+					order.setOrderID(rs.getInt(1));
+				} finally {
+					try {
+						rs.close();
+					} catch (SQLException e) {
+						logger.debug("", e);
+					}
+				}
+			}
+		} catch (SQLException e) {
+			throw new DAOException("", e);
+		} finally {
+			if (insertOrder != null) {
+				try {
+					insertOrder.close();
+				} catch (SQLException e) {
+					logger.debug("", e);
+				}
+			}
+//			if (selectOrderID != null) {
+//				try {
+//					selectOrderID.close();
+//				} catch (SQLException e) {
+//					logger.debug("", e);
+//				}
+//			}
+		}
+		return order;
+	}
+}

Added: incubator/stonehenge/contrib/stocktrader/metro/business_service/src/org/apache/stonehenge/stocktrader/mysql/AbstractMySQLDAO.java
URL: http://svn.apache.org/viewvc/incubator/stonehenge/contrib/stocktrader/metro/business_service/src/org/apache/stonehenge/stocktrader/mysql/AbstractMySQLDAO.java?rev=801507&view=auto
==============================================================================
--- incubator/stonehenge/contrib/stocktrader/metro/business_service/src/org/apache/stonehenge/stocktrader/mysql/AbstractMySQLDAO.java (added)
+++ incubator/stonehenge/contrib/stocktrader/metro/business_service/src/org/apache/stonehenge/stocktrader/mysql/AbstractMySQLDAO.java Thu Aug  6 03:41:14 2009
@@ -0,0 +1,81 @@
+/*
+ * 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.dal.DAOException;
+import java.sql.Connection;
+import java.sql.SQLException;
+import org.apache.commons.logging.Log;
+import org.apache.commons.logging.LogFactory;
+
+public class AbstractMySQLDAO {
+
+	private static final Log logger = LogFactory.getLog(AbstractMySQLDAO.class);
+
+	protected Connection sqlConnection;
+
+	private int previousTransactionIsolation;
+
+	public AbstractMySQLDAO(Connection sqlConnection) throws DAOException {
+		this.sqlConnection = sqlConnection;
+	}
+
+	public void beginTransaction() throws DAOException {
+		logger.debug("AbstractMSSQLDAO.beginTransaction()");
+		try {
+			sqlConnection.setAutoCommit(false);
+			previousTransactionIsolation = sqlConnection.getTransactionIsolation();
+			sqlConnection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
+		} catch (SQLException e) {
+			e.printStackTrace();
+			logger.debug("", e);
+			throw new DAOException("Exception was thrown during the start of transaction", e);
+		}
+	}
+
+	public void commitTransaction() throws DAOException {
+		logger.debug("AbstractMSSQLDAO.commitTransaction()");
+		try {
+			sqlConnection.commit();
+			sqlConnection.setAutoCommit(true);
+			sqlConnection.setTransactionIsolation(previousTransactionIsolation);
+		} catch (SQLException e) {
+			throw new DAOException("Exception is thrown during the commit of transaction", e);
+		}
+	}
+
+	public void rollbackTransaction() throws DAOException {
+		logger.debug("AbstractMySQLDAO.rollbackTransaction()");
+		try {
+			sqlConnection.rollback();
+			sqlConnection.setAutoCommit(true);
+			sqlConnection.setTransactionIsolation(previousTransactionIsolation);
+		} catch (SQLException e) {
+			throw new DAOException("Exception is thrown during the rollback of transaction", e);
+
+		}
+	}
+
+	public void close() throws DAOException {
+		try {
+			sqlConnection.close();
+		} catch (SQLException e) {
+			throw new DAOException("", e);
+		}
+	}
+}

Added: incubator/stonehenge/contrib/stocktrader/metro/business_service/src/org/apache/stonehenge/stocktrader/mysql/MySQLCustomerDAO.java
URL: http://svn.apache.org/viewvc/incubator/stonehenge/contrib/stocktrader/metro/business_service/src/org/apache/stonehenge/stocktrader/mysql/MySQLCustomerDAO.java?rev=801507&view=auto
==============================================================================
--- incubator/stonehenge/contrib/stocktrader/metro/business_service/src/org/apache/stonehenge/stocktrader/mysql/MySQLCustomerDAO.java (added)
+++ incubator/stonehenge/contrib/stocktrader/metro/business_service/src/org/apache/stonehenge/stocktrader/mysql/MySQLCustomerDAO.java Thu Aug  6 03:41:14 2009
@@ -0,0 +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);
+                }
+            }
+        }
+    }
+}



Mime
View raw message