billdco.java

struts+oracle+ Eclipse+tomcat海鲜超市

/*
 * 创建日期 2007-5-20
 *
 * TODO 要更改此生成的文件的模板，请转至
 * 窗口 － 首选项 － Java － 代码样式 － 代码模板
 */
package bill.dco;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import bill.vo.BillItemVo;
import bill.vo.BillVo;
import bill.vo.FlagVo;

import util.DBConn;

/**
 * @author Administrator
 * 
 * TODO 要更改此生成的类型注释的模板，请转至 窗口 － 首选项 － Java － 代码样式 － 代码模板
 */
public class BillDCO {

	public String getBillId(Connection conn) throws Exception {
		String billId = "";
		//查询语句
		String sql = "  select bill_seq.nextval as bill_id from dual  ";

		PreparedStatement pstmt = conn.prepareStatement(sql);
		//执行查询
		ResultSet rs = pstmt.executeQuery();
		//得到查询结果
		if (rs.next()) {

			billId = rs.getString("bill_id");

		}

		if (rs != null) {

			rs.close();
		}
		if (pstmt != null) {
			pstmt.close();
		}

		//返回id
		return billId;

	}

	public String getBillItemId(Connection conn) throws Exception {
		String billItemId = "";
		//查询语句
		String sql = "  select bill_item_seq.nextval as bill_item_id from dual  ";

		PreparedStatement pstmt = conn.prepareStatement(sql);
		//执行查询
		ResultSet rs = pstmt.executeQuery();
		//得到查询结果
		if (rs.next()) {

			billItemId = rs.getString("bill_item_id");

		}

		if (rs != null) {

			rs.close();
		}
		if (pstmt != null) {
			pstmt.close();
		}

		//返回id
		return billItemId;

	}

	public FlagVo insertBill(Connection conn, BillVo billVo) throws Exception {

		FlagVo flag = new FlagVo();

		String sql = " insert into table_bill(bill_id,vendor_id,bill_date,bill_due_date,bill_paid_flag,bill_amount)"
				+ "values(?,?,to_date(?,'yyyy/mm/dd'),to_date(?,'yyyy/mm/dd'),?,?) ";

		String billId = getBillId(conn);
		conn = DBConn.getDBConn();

		PreparedStatement pstmt = conn.prepareStatement(sql);

		pstmt.setString(1, billId);
		pstmt.setString(2, billVo.getVendorId());
		pstmt.setString(3, billVo.getBillDate());
		pstmt.setString(4, billVo.getBillDueDate());
		pstmt.setInt(5,0);
		pstmt.setString(6, billVo.getBillAmount());
		pstmt.executeUpdate();
		
		flag.setBillId(billId);

		pstmt.close();

		return flag;

	}

	public int insertBillItem(Connection conn, BillItemVo billItemVo)
			throws Exception {
		int flag = 0;

		String sql = " insert into table_bill_item(bill_id,dept_id,bill_item_id,bill_item_expense)values(?,?,?,?) ";

		PreparedStatement pstmt = conn.prepareStatement(sql);

		pstmt.setString(1, billItemVo.getBillId());
		pstmt.setString(2, billItemVo.getDeptId());
		pstmt.setString(3, getBillItemId(conn));
		pstmt.setString(4, billItemVo.getBillItemExpense());

		flag = pstmt.executeUpdate();

		pstmt.close();

		return flag;

	}

}