accountdb.java

噶额外噶外骨骼感广泛高热感 就 啊啊

package itso.webserv.model.persistence;

import itso.webserv.exception.AccountDatabaseException;
import itso.webserv.exception.AccountDoesNotExistException;
import itso.webserv.exception.ZeroAmountException;
import itso.webserv.model.entity.Account;
import itso.webserv.model.entity.TransRecord;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;

import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

/**
 * @author Ian Brown
 *
 */
public class AccountDB {

	private static final String DATASOURCE = "jdbc/EJBBANK";

	private static AccountDB instance = new AccountDB();
	private static DataSource datasource;

	public static AccountDB getInstance() {
		return instance;
	}

	public AccountDB() {	
		try {
			// get reference to datasource
			InitialContext context = new InitialContext();
			datasource = (DataSource) context.lookup(DATASOURCE);
		} catch (NamingException namex) {
		}
	}

	private Connection getConnection() throws SQLException {
		Connection con = datasource.getConnection();
		return con;
	}

	/**
	 * Gets all account information from the database
	 * for the given account id.
	 */
	public Account getAccount(String accountId)
		throws AccountDoesNotExistException, AccountDatabaseException {

		final String GET_ACCOUNT_SQL =
			"SELECT * FROM ITSO.ACCOUNT WHERE ACCID = ? ";

		Account account = null;
		Connection con = null;
		try {
			con = getConnection();
			PreparedStatement ps = con.prepareStatement(GET_ACCOUNT_SQL);
			ps.setString(1, accountId);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				account = new Account();
				account.setAccId(accountId);
				account.setBalance(rs.getBigDecimal("BALANCE"));
				account.setAccType(rs.getString("ACCTYPE"));
			} else {
				throw new AccountDoesNotExistException();
			}
			ps.close();
			rs.close();
		} catch (SQLException sqlex) {
			throw new AccountDatabaseException();
		} finally {
			try {
				if (con != null && !con.isClosed()) {
					con.close();
				}
			} catch (SQLException sqlex) {
			}
		}
		return account;
	}

	/**
	 * Adds transaction record to database
	 */
	private int addTransRecord(TransRecord transRecord, Connection con) {
		final String ADD_TRANSRECORD =
			"INSERT INTO ITSO.TRANSRECORD (TRANSID, ACCID, TRANSTYPE, TRANSAMT) VALUES (?, ?, ?, ?)";
		int numUpdated = 0;

		try {
			PreparedStatement ps = con.prepareStatement(ADD_TRANSRECORD);
			ps.setTimestamp(1, transRecord.getTransId());
			ps.setString(2, transRecord.getAccId());
			ps.setString(3, transRecord.getTransType());
			ps.setBigDecimal(4, transRecord.getTransAmt());
			numUpdated = ps.executeUpdate();
			ps.close();
		} catch (SQLException sqlex) {
			System.out.println(sqlex.getMessage());
		}
		return numUpdated;
	}

	/**
	 * Updates the balance for the given account number.  Depending on 
	 * transaction type, either adds or subtracts.
	 */
	private void updateAccount( 
		String accId,
		BigDecimal amount,
		String transType) throws AccountDoesNotExistException {
		// assume transType is either "C" or "D"
		// by default, the bank debits your account
		String sign = "-";
		if (transType.equals("C")) {
			// credit account so add amount
			sign = "+";
		}
		String updateSql =
			"UPDATE ITSO.ACCOUNT SET BALANCE=BALANCE"
				+ sign
				+ "? WHERE ACCID= ? ";

		Connection con = null;
		try {
			con = getConnection();
			// perform both update and add transaction at same time
			con.setAutoCommit(false);

			// update account balance
			PreparedStatement ps = con.prepareStatement(updateSql);
			ps.setBigDecimal(1, amount);
			ps.setString(2, accId);
			int numUpdated = ps.executeUpdate();
			if (numUpdated <= 0) {
				throw new AccountDoesNotExistException();
			}
			ps.close();

			// create transaction record
			TransRecord transRecord = new TransRecord(accId, transType, amount);

			// add transaction to db
			addTransRecord(transRecord, con);

			
		} catch (SQLException sqlex) {
			try {
				// on error, undo changes
				con.rollback();
			} catch (SQLException e) {
			}
		} finally {
			try {
				if (con != null && !con.isClosed()) {
					// commit changes
					con.commit();
					con.close();
				}
			} catch (SQLException sqlex) {
			}
		}

	}

	/**
	 * Credits the given account by the amount specified
	 */
	public void credit(String accId, BigDecimal amount)
		throws ZeroAmountException, AccountDoesNotExistException {
		if (amount != null && amount.intValue() <= 0) {
			throw new ZeroAmountException();
		} else {
			updateAccount(accId, amount, "C");
		}
	}

	/**
	 * Debits the given account by the amount specified
	 */
	public void debit(String accId, BigDecimal amount)
		throws ZeroAmountException, AccountDoesNotExistException {
		if (amount != null && amount.intValue() <= 0) {
			throw new ZeroAmountException();
		} else {
			updateAccount(accId, amount, "D");
		}
	}

}