bankda.java

医药供应链管理系统

package com.captainli.dboperation;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.captainli.struts.form.BankForm;
import com.captainli.util.GetConnection;

/**
 * bank表数据库操作类
 * @author CaptainLi
 *
 */
public class BankDA {
	private Connection conn = GetConnection.getConn();
	private PreparedStatement pstmt = null;
	private Statement stmt = null;
	private ResultSet rs = null;
	/**
	 * 关闭数据库对象
	 *
	 */
	public void closeDB(){
		try {
			if(rs != null){
				rs.close();
			}
			if(stmt != null){
				stmt.close();
			}
			if(pstmt != null){
				pstmt.close();
			}
			if(conn != null){
				conn.close();
			}
		} catch (Exception e) {
			e.getStackTrace();
		}
	}
	/**
	 * 返回所有银行帐务集合
	 * @return
	 */
	public ArrayList showBank(){
		ArrayList arry = new ArrayList();
		BankForm form = null;
		String sql = "select * from bank order by b_id";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				form = new BankForm(rs.getInt("b_id"),rs.getString("b_name"), rs.getDouble("b_amount"), rs.getString("b_note"));
				arry.add(form);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 添加银行帐务
	 * @param form
	 */
	public void addBank(BankForm form){
		String sql = "insert into bank values (?, ?, ?)";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, form.getB_name());
			pstmt.setDouble(2, form.getB_amount());
			pstmt.setString(3, form.getB_note());
			pstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 通过ID返回银行帐务对象
	 * @param b_id
	 * @return
	 */
	public BankForm showBankByID(int b_id){
		BankForm form = null;
		String sql = "select * from bank where b_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, b_id);
			rs = pstmt.executeQuery();
			while(rs.next()){
				form = new BankForm(rs.getInt("b_id"), rs.getString("b_name"), rs.getDouble("b_amount"), rs.getString("b_note"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return form;
	}
	/**
	 * 修改银行帐务
	 * @param form
	 * @param b_id
	 */
	public void updateBank(BankForm form, int b_id){
		String sql = "update bank set b_name = ?, b_amount = ?, b_note = ? where b_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, form.getB_name());
			pstmt.setDouble(2, form.getB_amount());
			pstmt.setString(3, form.getB_note());
			pstmt.setInt(4, b_id);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 银行帐户变化
	 * @param b_id
	 * @param b_amount
	 */
	public void updateBankMoney(int b_id, double b_amount){
		String sql = "update bank set b_amount = b_amount + ? where b_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setDouble(1, b_amount);
			pstmt.setInt(2, b_id);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 通过银行id返回有多少钱
	 * @param b_id
	 * @return
	 */
	public double showMoney(int b_id){
		String sql = "select b_amount from bank where b_id = ?";
		double tmp = 0;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, b_id);
			rs = pstmt.executeQuery();
			if(rs.next()){
				tmp = rs.getDouble("b_amount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return tmp;
	}
}