/*
* 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);
}
}
}
}
}