vendordao.java

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

package dao;

import java.sql.*;
import java.util.ArrayList;

import po.VendorPO;
import vo.incomeVO;

public class VendorDAO {
	
	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_Vendor");
			while(rs.next())
			{
				VendorPO vp=new VendorPO();
				vp.setVendor_ID(rs.getInt("Vendor_ID"));
				vp.setVendor_name(rs.getString("Vendor_name"));
				vp.setVendor_address(rs.getString("Vendor_address"));
				vp.setVendor_phone(rs.getInt("Vendor_phone"));
				vp.setVendor_fax(rs.getInt("Vendor_fax"));
				vp.setVendor_contact_person(rs.getString("Vendor_contact_person"));
				
				al.add(vp);
			}
		} 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;
		
	}
	
    //查询一条记录
	public VendorPO findvenByID(int id)
	{
		VendorPO vp=new VendorPO();
		try {
			conn=Tools.getConnection();
			state=conn.createStatement();
			rs=state.executeQuery("select * from Table_Vendor where Vendor_ID="+id);
			while(rs.next())
			{
				
				vp.setVendor_ID(rs.getInt("Vendor_ID"));
				vp.setVendor_name(rs.getString("Vendor_name"));
				vp.setVendor_address(rs.getString("Vendor_address"));
				vp.setVendor_phone(rs.getInt("Vendor_phone"));
				vp.setVendor_fax(rs.getInt("Vendor_fax"));
				vp.setVendor_contact_person(rs.getString("Vendor_contact_person"));
				
				
				
			}
		} 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 vp;
	}
	//更新
	public boolean vendorUpdate(VendorPO vm)
	{
		boolean isOK=false;
		
		try {
			conn=Tools.getConnection();
			state=conn.createStatement();
			
			int i=state.executeUpdate("update Table_Vendor set  Vendor_name='"+vm.getVendor_name()+"',Vendor_address='"+vm.getVendor_address()+"',Vendor_phone="+vm.getVendor_phone()+",Vendor_fax="+vm.getVendor_fax()+",Vendor_contact_person='"+vm.getVendor_contact_person()+"' where Vendor_ID="+vm.getVendor_ID());
			if(i>0)
			{
				isOK=true;
			}
		} catch (SQLException 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;
	}
	//增加一条
	public boolean Addvendor(VendorPO vm)
	{
		boolean isOK=false;
		try {
			conn=Tools.getConnection();
			state=conn.createStatement();
			int i = state.executeUpdate("insert into Table_Vendor values("+vm.getVendor_ID()+",'"+vm.getVendor_name()+"','"+vm.getVendor_address()+"',"+vm.getVendor_phone()+","+vm.getVendor_fax()+",'"+vm.getVendor_contact_person()+"')");
			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()
	{
		int myID=0;
		conn = Tools.getConnection();
		try {
			state=conn.createStatement();
			rs=state.executeQuery("select max(Vendor_ID) myID from Table_Vendor");
			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 VendorPO findincomeByID(int id)
	{
		VendorPO vepo=new VendorPO();
		try {
			conn=Tools.getConnection();
			state=conn.createStatement();
			rs=state.executeQuery("select * from Table_Vendor where Vendor_ID="+id);
			while(rs.next())
			{
				vepo.setVendor_name(rs.getString("Vendor_name"));
				vepo.setVendor_address(rs.getString("Vendor_address"));
				vepo.setVendor_phone(rs.getInt("Vendor_phone"));
				vepo.setVendor_fax(rs.getInt("Vendor_fax"));
				vepo.setVendor_contact_person(rs.getString("Vendor_contact_person"));
			}
		} 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 vepo;
	}
    //查找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;
	}
}