accountdb.java

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

package itso.webserv.model;

import itso.bank.exception.AccountDoesNotExistException;
import itso.bank.exception.BankException;
import itso.bank.exception.ZeroAmountException;
import itso.bank.model.Account;
import itso.bank.model.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 java.util.Vector;

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

/**
 * @author Ian Brown, modified by Ueli Wahli
 *
 */
public class AccountDB {

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

	private static AccountDB instance = new AccountDB();
	private static DataSource datasource;
	// random number generator for timestamp
	private java.util.Random random = new java.util.Random();


	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) {
			namex.printStackTrace();
		}
	}

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

	/**
	 * 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");
		}
	}
	
	/**
	 * Gets all account information from the database
	 * for the given account id.
	 */
	public Account getAccount(String accountId)
		throws AccountDoesNotExistException, BankException {

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

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

	/**
	 * 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 {
		String updateSql = null;
		
		Connection con = null;
		PreparedStatement ps = null;
		try {
			con = getConnection();
			if (transType.equals("C")) 
				updateSql = "UPDATE ITSO.ACCOUNT SET BALANCE=BALANCE + ? WHERE ACCID= ?";
			else if (transType.equals("D"))
				updateSql = "UPDATE ITSO.ACCOUNT SET BALANCE=BALANCE - ? WHERE ACCID= ?";
				else throw new SQLException("Invalid transaction type");
			
			// perform both update and add transaction at same time
			con.setAutoCommit(false);

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

			// create transaction record
			TransRecord transRecord = new TransRecord();
			transRecord.setTimeStamp( new java.sql.Timestamp(System.currentTimeMillis()) );
			transRecord.setTransType(transType);
			transRecord.setTransAmt(amount);

			// add transaction to db
			int inserted = addTransRecord(transRecord, accId, con);
			if (inserted == 0) throw new SQLException("TransRecord insert failed");
			
		} catch (SQLException sqlex) {
			sqlex.printStackTrace();
			try {
				// on error, undo changes
				con.rollback();
			} catch (SQLException e) {}
		} finally {
			try {
				if (ps != null) ps.close();
				if (con != null && !con.isClosed()) {
					// commit changes
					con.commit();
					con.close();
				}
			} catch (SQLException sqlex) {}
		}
	}

	/**
	 * Adds transaction record to database
	 */
	private int addTransRecord(TransRecord transRecord, String accId, Connection con) {
		final String ADD_TRANSRECORD =
			"INSERT INTO ITSO.TRANSRECORD (TRANSID, ACCID, TRANSTYPE, TRANSAMT) VALUES (?, ?, ?, ?)";
		int numInserted = 0;
		PreparedStatement ps = null;
		Timestamp ts = (Timestamp)transRecord.getTimeStamp();
		ts.setNanos( ts.getNanos() + random.nextInt(999999) );
		try {
			ps = con.prepareStatement(ADD_TRANSRECORD);
			ps.setTimestamp(1, ts);
			ps.setString(2, accId);
			ps.setString(3, transRecord.getTransType());
			ps.setBigDecimal(4, transRecord.getTransAmt());
			numInserted = ps.executeUpdate();
		} catch (SQLException sqlex) {
			sqlex.printStackTrace();
		} finally {
			try {
				if (ps != null) ps.close();
			} catch (SQLException sqlex) {}
		}
		return numInserted;
	}

	/**
	 * Gets transaction records from the database
	 * for the given account id.
	 */
	public TransRecord[] getTransactions(String accountId)
		throws BankException {

		final String GET_TR_SQL =
			"SELECT * FROM ITSO.TRANSRECORD WHERE ACCID = ? ";

		TransRecord tr = null;
		Vector trvector = new Vector();
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			con = getConnection();
			ps = con.prepareStatement(GET_TR_SQL);
			ps.setString(1, accountId);
			rs = ps.executeQuery();
			while (rs.next()) {
				tr = new TransRecord();
				tr.setTimeStamp(rs.getTimestamp("TRANSID"));
				tr.setTransType(rs.getString("TRANSTYPE"));
				tr.setTransAmt(rs.getBigDecimal("TRANSAMT"));
				trvector.add(tr);
			}
		} catch (SQLException sqlex) {
			throw new BankException();
		} finally {
			try {
				if (ps != null) ps.close();
				if (rs != null) rs.close();
				if (con != null && !con.isClosed()) con.close();
			} catch (SQLException sqlex) {}
		}
		if (trvector.size() > 0) {
			System.out.println("Transrecords "+trvector.size());
			TransRecord[] result = new TransRecord[trvector.size()];
			for (int i=0; i < result.length; i++) 
				result[i] = (TransRecord)trvector.elementAt(i);
			return result;
		} else { return null; }
	}
}