billvendordao.java

超市管理系统,jsp+javabean+sqlserver

package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import po.DepartmentPO;

import vo.billdeptVO;
import vo.billvendorVO;

public class billvendorDAO {
	Connection conn=null;
	Statement state=null;
	ResultSet rs=null;
	//查询所有
	public ArrayList findAll()
	{
		ArrayList al=new ArrayList();
		try {
			conn=Tools.getConnection();
			state=conn.createStatement();
			rs=state.executeQuery("select * from Table_bill,Table_Vendor where Table_bill.Vendor_id=Table_Vendor.Vendor_ID");
			while(rs.next())
			{
				billvendorVO bvp=new billvendorVO();
				bvp.setBill_ID(rs.getInt("Bill_ID"));
				bvp.setVendor_id(rs.getInt("Vendor_id"));
				bvp.setBill_Date(rs.getString("Bill_Date"));
				bvp.setBill_due_Date(rs.getString("Bill_due_Date"));
				bvp.setBill_paid_flag(rs.getInt("Bill_paid_flag"));
				//bvp.setBill_amount(rs.getBoolean("Bill_amount"));
				bvp.setBill_amount(rs.getInt("Bill_amount"));
				bvp.setVendor_name(rs.getString("Vendor_name"));
				bvp.setVendor_address(rs.getString("Vendor_address"));
				bvp.setVendor_phone(rs.getInt("Vendor_phone"));
				bvp.setVendor_fax(rs.getInt("Vendor_fax"));
				bvp.setVendor_contact_person(rs.getString("Vendor_contact_person"));
				al.add(bvp);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			try {
				if(rs != null)
				    rs.close();
				if(state != null)
					state.close();
				if(conn != null)
				    conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return al;
		
	}
	//由ID取名
	public billvendorVO findvenByID(int id)
	{
		billvendorVO bvp=null;
		try {
			conn=Tools.getConnection();
			state=conn.createStatement();
			rs=state.executeQuery("select * from Table_Vendor where Vendor_id ="+id);
			while(rs.next())
			{
				bvp=new billvendorVO();
				bvp.setVendor_name(rs.getString("Vendor_name "));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
				try {
					if(rs!=null)
					    rs.close();
					if(state!=null)
						rs.close();
					if(conn!=null)
						conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return bvp;
	}
	//插入一条
	public boolean Addbillvendor(billvendorVO vm)
	{
		boolean isOK=false;
		try {
			conn=Tools.getConnection();
			state=conn.createStatement();
			int i = state.executeUpdate("insert into Table_bill values("+vm.getBill_ID()+","+vm.getVendor_id()+",'"+vm.getBill_Date()+"','"+vm.getBill_due_Date()+"',"+vm.getBill_paid_flag()+","+vm.getBill_amount()+")");
			if(i>0)
			{
				isOK=true;
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
				try {
					if(state!=null)
					state.close();
					if(conn!=null)
						conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return isOK;
		
	}
	//取得ID
	public int getNextID()
	{
		billvendorVO bvvo=new billvendorVO();
		int myID=0;
		conn = Tools.getConnection();
		try {
			state=conn.createStatement();
			rs=state.executeQuery("select max(Bill_ID) myID from Table_bill");
			if(bvvo.getBill_ID()==0)
			{
				myID=100000001;
			}
			else
			{
				if(rs.next())
				{
					myID = rs.getInt("myID");
				}
				
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			
				try {
					if(rs!=null)
					rs.close();
					if(state!=null)
						state.close();
					if(conn!=null)
						conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return ++myID;
	}
    //判断是否是有效的供应商名
    public boolean isvendor(String name)
    {
    	boolean isok=false;
    	try {
			conn=Tools.getConnection();
			state=conn.createStatement();
			rs=state.executeQuery("select * from Table_Vendor where Vendor_name= '"+name+"'");
			//如果找到了
			if(rs.next())
			{
				isok=true;
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return isok;
    }
     //查找ID
	public int lookid(String Vendor_name)
	{
		Statement state = null;
		ResultSet rs = null;
		int i = 0;
		try {
			conn=Tools.getConnection();
			state=conn.createStatement();
			rs = state.executeQuery("select Vendor_ID from Table_Vendor where Vendor_name = '"+Vendor_name+"'");
			if(rs.next())
			{
				i = rs.getInt("Vendor_ID");
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			try {
				rs.close();
				state.close();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
		}
		return i;
	}
}