accountdao.java

该项为帮助银行工作人员处理银行业务

package com.bluedot.bank.framework.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.bluedot.bank.framework.sql.MySqlInfo;
import com.bluedot.bank.framework.web.actionform.AccountBean;
import com.bluedot.bank.framework.web.actionform.ExchangerecordBean;

public class AccountDao {
	public void create(AccountBean c) {
		Connection connection = null;
		connection = new MySqlInfo().getConnection();
		try {
			PreparedStatement statement = connection
					.prepareStatement("insert into account(account_id,password,customer_id,type,description,begin_balance,begin_balance_time,balance_time,balance) values(?,?,?,?,?,?,?,?,?)");
			int index = 1;
			statement.setString(index++, String.valueOf(new Date().getTime()));
			statement.setString(index++, c.getPassword());
			statement.setString(index++, c.getCustomer_id());
			statement.setString(index++, c.getType());
			statement.setString(index++, c.getDescription());
			statement.setString(index++, c.getBegin_balance());
			statement.setTimestamp(index++, new Timestamp(System
					.currentTimeMillis()));
			statement.setTimestamp(index++, new Timestamp(System
					.currentTimeMillis()));
			statement.setString(index++, c.getBegin_balance());
			statement.execute();
			connection.commit();
			statement.close();
		} catch (SQLException e) {
			new MySqlInfo().rollback(connection);
			
			throw new RuntimeException(e);
			
		} finally {
			new MySqlInfo().closeCon(connection);
		}
	}

	public List<AccountBean> select() {
		List<AccountBean> list = new ArrayList<AccountBean>();
		Connection connection = null;
		connection = new MySqlInfo().getConnection();
		try {
			PreparedStatement statement = connection
					.prepareStatement("select account_id,password,customer_id,type,description,begin_balance,balance,begin_balance_time,balance_time from account");

			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				AccountBean c = new AccountBean();
				c.setAccount_id(rs.getString("account_id"));
				c.setCustomer_id(rs.getString("customer_id"));
				c.setPassword(rs.getString("password"));
				c.setType(rs.getString("type"));
				c.setDescription(rs.getString("description"));
				c.setBegin_balance(rs.getString("begin_balance"));
				c.setBegin_balance_time(rs.getTimestamp("begin_balance_time"));
				c.setBalance_time(rs.getTimestamp("balance_time"));

				c.setBalance(rs.getString("balance"));
				list.add(c);
			}
			statement.close();
			rs.close();
			connection.commit();
		} catch (SQLException e) {
			new MySqlInfo().rollback(connection);
			throw new RuntimeException(e);
		} finally {
			new MySqlInfo().closeCon(connection);
		}
		return list;
	}

	public List<AccountBean> select(String type, String info, String cusid) {
		List<AccountBean> list = new ArrayList<AccountBean>();
		Connection connection = null;
		connection = new MySqlInfo().getConnection();

		try {
			PreparedStatement statement = connection
					.prepareStatement("select account_id,password,customer_id,type,description,begin_balance,balance,begin_balance_time,balance_time from account where customer_id=? and type=? ");
			statement.setString(1, cusid);
			statement.setString(2, type);

			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				AccountBean c = new AccountBean();
				c.setAccount_id(rs.getString("account_id"));
				c.setCustomer_id(rs.getString("customer_id"));
				c.setPassword(rs.getString("password"));
				c.setType(rs.getString("type"));
				c.setDescription(rs.getString("description"));
				c.setBegin_balance(rs.getString("begin_balance"));
				c.setBegin_balance_time(rs.getTimestamp("begin_balance_time"));
				c.setBalance_time(rs.getTimestamp("balance_time"));
				c.setBalance(rs.getString("balance"));
				list.add(c);
			}
			statement.close();
			rs.close();
			connection.commit();
		} catch (SQLException e) {
			new MySqlInfo().rollback(connection);
			throw new RuntimeException(e);
		} finally {
			new MySqlInfo().closeCon(connection);
		}
		return list;
	}

	public List<AccountBean> select(String type, String info) {
		List<AccountBean> list = new ArrayList<AccountBean>();
		Connection connection = null;
		connection = new MySqlInfo().getConnection();
		String sql = "";

		if (type.equals("account_id")) {
			sql = "account_id";
		}
		if (type.equals("customer_id")) {
			sql = "customer_id";
		}
		if (type.equals("type")) {
			sql = "type";
		}
		if (type.equals("description")) {
			sql = "description";
		}
		String b;
		b="%"+info.replaceAll("!","!!").replaceAll("%","!%")+"%";

		try {
			PreparedStatement statement = connection
					.prepareStatement("select account_id,password,customer_id,type,description,begin_balance,balance,begin_balance_time,balance_time from account where "
							+ sql + " like ? escape '!'");
			statement.setString(1, b);

			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				AccountBean c = new AccountBean();
				c.setAccount_id(rs.getString("account_id"));
				c.setCustomer_id(rs.getString("customer_id"));
				c.setPassword(rs.getString("password"));
				c.setType(rs.getString("type"));
				c.setDescription(rs.getString("description"));
				c.setBegin_balance(rs.getString("begin_balance"));
				c.setBegin_balance_time(rs.getTimestamp("begin_balance_time"));
				c.setBalance_time(rs.getTimestamp("balance_time"));
				c.setBalance(rs.getString("balance"));
				list.add(c);
			}
			statement.close();
			rs.close();
			connection.commit();
		} catch (SQLException e) {
			new MySqlInfo().rollback(connection);
			throw new RuntimeException(e);
		} finally {
			new MySqlInfo().closeCon(connection);
		}
		return list;
	}
	//存钱查询
	public List<AccountBean> selec(String type, String info) {
		List<AccountBean> list = new ArrayList<AccountBean>();
		Connection connection = null;
		connection = new MySqlInfo().getConnection();
		String sql = "";

		if (type.equals("account_id")) {
			sql = "account_id";
		}
		if (type.equals("customer_id")) {
			sql = "customer_id";
		}
		if (type.equals("type")) {
			sql = "type";
		}
		if (type.equals("description")) {
			sql = "description";
		}
		String b;
		b=info.replaceAll("!","!!").replaceAll("%","!%");

		try {
			PreparedStatement statement = connection
					.prepareStatement("select account_id,password,customer_id,type,description,begin_balance,balance,begin_balance_time,balance_time from account where "
							+ sql + "=?");
			statement.setString(1, b);

			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				AccountBean c = new AccountBean();
				c.setAccount_id(rs.getString("account_id"));
				c.setCustomer_id(rs.getString("customer_id"));
				c.setPassword(rs.getString("password"));
				c.setType(rs.getString("type"));
				c.setDescription(rs.getString("description"));
				c.setBegin_balance(rs.getString("begin_balance"));
				c.setBegin_balance_time(rs.getTimestamp("begin_balance_time"));
				c.setBalance_time(rs.getTimestamp("balance_time"));
				c.setBalance(rs.getString("balance"));
				list.add(c);
			}
			statement.close();
			rs.close();
			connection.commit();
		} catch (SQLException e) {
			new MySqlInfo().rollback(connection);
			throw new RuntimeException(e);
		} finally {
			new MySqlInfo().closeCon(connection);
		}
		return list;
	}

	public void update(String id, float money, int i) {
		Connection connection = null;
		String info = "+";
		if (i == 0) {
			info = "-";
		}
		connection = new MySqlInfo().getConnection();
		try {
			PreparedStatement statement = connection
					.prepareStatement("update account set balance=balance"
							+ info + "?,balance_time=? where account_id=?");

			statement.setFloat(1, money);
			statement
					.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
			statement.setString(3, id);
			statement.execute();
			connection.commit();
			statement.close();
		} catch (SQLException e) {
			new MySqlInfo().rollback(connection);
			throw new RuntimeException(e);
		} finally {
			new MySqlInfo().closeCon(connection);
		}
	}
	

	public void upDate(String id, String id2,float money) {
		Connection connection = null;

		connection = new MySqlInfo().getConnection();
		try {
			PreparedStatement statement = connection
					.prepareStatement("update account set balance=balance-?,balance_time=? where account_id=?");

			statement.setFloat(1, money);
			statement
					.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
			statement.setString(3, id);
			statement.execute();
			//上面是id打钱  下面打到的开 
			PreparedStatement statement2 = connection
			.prepareStatement("update account set balance=balance+?,balance_time=? where account_id=?");

			statement2.setFloat(1, money);
			statement2
				.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
			statement2.setString(3, id2);
			statement2.execute();			
			
			
			connection.commit();
			statement.close();
		} catch (SQLException e) {
			new MySqlInfo().rollback(connection);
			throw new RuntimeException(e);
		} finally {
			new MySqlInfo().closeCon(connection);
		}
	}

	public void update(String id1, String id2, float money, int type) {
		try {
			ExchangerecordBean exchangerecordBean = new ExchangerecordBean();
			if (id2.length() > 0) {
				// 转帐
				upDate(id1,id2, money);
				exchangerecordBean.setAmount(String.valueOf(money));
				exchangerecordBean.setAccount_id(id2);
				List<AccountBean> list = select("account_id", id2);
				exchangerecordBean.setBanlance(list.get(0)
						.getBalance());
				exchangerecordBean.setDescription(id2+"给你打钱了");
				new ExchangerecordDao().create(exchangerecordBean);
				
				
				exchangerecordBean.setDescription("转帐到" + id2);
			} else {
				if (type == 0) {
					// 取钱
					exchangerecordBean.setDescription("取钱");
					update(id1,money,0);
				} else {
					// 存钱
					update(id1,money,1);
					exchangerecordBean.setDescription("存钱");
				}
			}
			exchangerecordBean.setAmount(String.valueOf(money));
			exchangerecordBean.setAccount_id(id1);
			List<AccountBean> list = select("account_id", id1);
			exchangerecordBean.setBanlance(list.get(0)
					.getBalance());
			new ExchangerecordDao().create(exchangerecordBean);

		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

}