vendordco.java

struts+oracle+ Eclipse+tomcat海鲜超市

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

import income.vo.IncomeVo;

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 vendor.vo.VendorVo;

import util.DBConn;
import util.PageControl;


/**
 * @author yanjinkk
 * 
 * TODO 要更改此生成的类型注释的模板，请转至 窗口 － 首选项 － Java － 代码样式 － 代码模板
 */
public class VendorDCO {
	public int sumNote = 0;
	public String getVendorId() {
		String vendorId = "";
		//查询语句
		String sql = "  select vendor_seq.nextval as vendor_id from dual  ";

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

		try {
			//得到连接
			conn = DBConn.getDBConn();
			pstmt = conn.prepareStatement(sql);
			//执行查询
			rs = pstmt.executeQuery();
			//得到查询结果
			if (rs.next()) {

				vendorId = rs.getString("vendor_id");

			}

		} 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();
			}

		}
		//返回id
		return vendorId;

	}

	public int insertVendor(VendorVo vendorVo) {
		int flag = 0;

		String sql = " insert into table_vendor(vendor_id,vendor_name,vendor_address,vendor_phone,vendor_fas,vendor_contact_person) values(?,?,?,?,?,?) ";
		Connection conn = null;
		PreparedStatement pstmt = null;

		try {

			conn = DBConn.getDBConn();
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, getVendorId());
			pstmt.setString(2, vendorVo.getVendorName());
			pstmt.setString(3, vendorVo.getVendorAddress());
			pstmt.setString(4, vendorVo.getVendorPhone());
			pstmt.setString(5, vendorVo.getVendorFas());
			pstmt.setString(6, vendorVo.getContactPerson());

			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 int updateVendor(VendorVo vendorVo) {
		int flag = 0;

		String sql = " update table_vendor set vendor_name=?,vendor_address=?,vendor_phone=?,vendor_fas=?,vendor_contact_person=? where vendor_id=? ";
		Connection conn = null;
		PreparedStatement pstmt = null;

		try {

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

			pstmt.setString(6, vendorVo.getVendorId());
			pstmt.setString(1, vendorVo.getVendorName());
			pstmt.setString(2, vendorVo.getVendorAddress());
			pstmt.setString(3, vendorVo.getVendorPhone());
			pstmt.setString(4, vendorVo.getVendorFas());
			pstmt.setString(5, vendorVo.getContactPerson());

			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 selectVendorInfo(String curpage) {
		List list = new ArrayList();
		
		PageControl pageControl = new PageControl();
		//查询语句
		String sql = "  select vendor_id,vendor_name,vendor_address,vendor_phone,vendor_fas,vendor_contact_person from table_vendor order by vendor_id desc  ";

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

		try {
			//得到连接
			conn = util.DBConn.getDBConn();
			pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,//指针可移动
					ResultSet.CONCUR_READ_ONLY);
			
			//执行查询
			rs = pstmt.executeQuery();

			rs.absolute(-1);	  //指针到尾记录

			sumNote = rs.getRow();//总共多少条纪录

			pageControl.init(Integer.parseInt(curpage), sumNote);//初使话

			if (sumNote > 0) {
				if (pageControl.getStart() == 0) {
					rs.absolute(1);

				} else {
					rs.absolute(pageControl.getStart());
			
				}
			
			}
			//得到查询结果
			do{
				//用deptvo的对象封装所有数据
				VendorVo vendorVo = new VendorVo();

				VendorVo vendorvo = new VendorVo();
				vendorvo.setVendorId(rs.getString("vendor_id"));
				vendorvo.setVendorName(rs.getString("vendor_name"));
				vendorvo.setVendorAddress(rs.getString("vendor_address"));
				vendorvo.setVendorPhone(rs.getString("vendor_phone"));
				vendorvo.setVendorFas(rs.getString("vendor_fas"));
				vendorvo.setContactPerson(rs.getString("vendor_contact_person"));
	
				//将查询结果放到list中
				list.add(vendorvo);
				if(!rs.next()) {
					break;
				}
				
			}while (rs.getRow() < pageControl.getEnd() + 1);

		} 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;
	}
	
	
//不分页
	public List selectAllVendorInfo() {
		List list = new ArrayList();

		//查询语句
		String sql = "  select vendor_id,vendor_name,vendor_address,vendor_phone,vendor_fas,vendor_contact_person from table_vendor order by vendor_id desc  ";

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

		try {
			//得到连接
			conn = DBConn.getDBConn();
			pstmt = conn.prepareStatement(sql);
			//执行查询
			rs = pstmt.executeQuery();
			//得到查询结果
			while (rs.next()) {
				//用vendorvo的对象封装所有数据
				VendorVo vendorvo = new VendorVo();
				vendorvo.setVendorId(rs.getString("vendor_id"));
				vendorvo.setVendorName(rs.getString("vendor_name"));
				vendorvo.setVendorAddress(rs.getString("vendor_address"));
				vendorvo.setVendorPhone(rs.getString("vendor_phone"));
				vendorvo.setVendorFas(rs.getString("vendor_fas"));
				vendorvo.setContactPerson(rs.getString("vendor_contact_person"));

				//将查询结果放到list中
				list.add(vendorvo);
			}

		} 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;

	}

	public VendorVo selectVendorById(int vendorId) {
		VendorVo vendorvo = new VendorVo();

		//查询语句
		String sql = "  select vendor_id,vendor_name,vendor_address,vendor_phone,vendor_fas,vendor_contact_person from table_vendor where  vendor_id=?";

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

		try {
			//得到连接
			conn = DBConn.getDBConn();
			pstmt = conn.prepareStatement(sql);
			//执行查询
			pstmt.setInt(1, vendorId);
			rs = pstmt.executeQuery();
			//得到查询结果
			if (rs.next()) {
				//用vendorvo的对象封装所有数据
				vendorvo = new VendorVo();
				vendorvo.setVendorId(rs.getString("vendor_id"));
				vendorvo.setVendorName(rs.getString("vendor_name"));
				vendorvo.setVendorAddress(rs.getString("vendor_address"));
				vendorvo.setVendorPhone(rs.getString("vendor_phone"));
				vendorvo.setVendorFas(rs.getString("vendor_fas"));
				vendorvo
						.setContactPerson(rs.getString("vendor_contact_person"));

			}

		} 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 vendorvo;

	}

}