billpaymentdco.java

struts+oracle+ Eclipse+tomcat海鲜超市

package billpayment.dco;

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

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import billpayment.vo.BillPaymentVo;

import util.DBConn;



/**
 * @author Administrator
 *
 * TODO 要更改此生成的类型注释的模板，请转至
 * 窗口 － 首选项 － Java － 代码样式 － 代码模板
 */
public class BillPaymentDCO {
	
	public int updateBillPayFlag(String billId){
		
		int flag = 0;

		String sql = "  update table_bill set bill_paid_flag=1 where bill_id=?  ";
		Connection conn = null;
		PreparedStatement pstmt = null;

		try {

			conn = DBConn.getDBConn();
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(sql);

			pstmt.setString(1,billId);
			
			flag = pstmt.executeUpdate();

			conn.commit();

		} catch (SQLException e) {

			try {
				conn.rollback();
			} catch (SQLException e1) {

				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
				}
				if (conn != null) {
					conn.setAutoCommit(true);
					conn.close();
				}

			} catch (Exception e1) {

				e1.printStackTrace();
			}
		}

		return flag;
		
		
	}
	
	
	
	
	public List selectBillInfo(String vendorName) {
		List list = new ArrayList();

		//查询语句
		String sql = "  select bill_id,vendor_name,to_char(bill_date,'yyyy/mm/dd' ) as bill_date,to_char(bill_due_date,'yyyy/mm/dd' )as bill_due_date from table_bill b,table_vendor v where v.vendor_id=b.vendor_id and bill_paid_flag=0  and vendor_name=?";

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			//得到连接
			conn = DBConn.getDBConn();
			pstmt = conn.prepareStatement(sql);
			//执行查询
			pstmt.setString(1,vendorName);
			rs = pstmt.executeQuery();
			//得到查询结果
			
			while (rs.next()) {
				//用deptvo的对象封装所有数据
				BillPaymentVo billPaymentVo=new BillPaymentVo();
				billPaymentVo.setBillId(rs.getInt("bill_id"));
				billPaymentVo.setVendorName(rs.getString("vendor_name"));
				billPaymentVo.setBillDate(rs.getString("bill_date"));
				billPaymentVo.setBillDueDate(rs.getString("bill_due_date"));				
				//将查询结果放到list中
				list.add(billPaymentVo);
			}

		} catch (SQLException e) {

			e.printStackTrace();
		} finally {

			try {//关闭连接
				if (rs != null) {

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

			} catch (SQLException e1) {

				e1.printStackTrace();
			}

		}

		return list;

	}
	

}