buydaoimpl.java

超市进销存系统,采用mvc+DAO模式编写

package mybaobao;  

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.sql.*;
public class BuyDAOImpl implements BuyDAO {
	
	public void insert(Buy buy) throws Exception{    //插入操作,实现往进货表和库表添加
		
			String sql=
				"insert into buy(SupplierID,WareID,Price,Quantity,BuyDate,BuyPerson) values(?,?,?,?,?,?)";
			
		    PreparedStatement pstmt = null ;
			DBConnection dbc = new DBConnection() ;
			try
			{
				pstmt = dbc.getConnection().prepareStatement(sql) ;
				pstmt.setInt(1,buy.getSupplierId()) ;
				pstmt.setInt(2,buy.getWareId()) ;
				pstmt.setInt(3,buy.getPrice()) ;
				pstmt.setInt(4,buy.getQuantity()) ;
				pstmt.setString(5,buy.getBuyDate()) ;
				pstmt.setString(6,buy.getBuyPerson()) ;
				pstmt.executeUpdate();
				pstmt.close() ;
			
				String sql2="select * from reserve where WareID=?";
				pstmt = dbc.getConnection().prepareStatement(sql2) ;
				pstmt.setInt(1, buy.getWareId());
				ResultSet rs=pstmt.executeQuery();
				if(rs.next()) //有库存记录
				{
					String sql3=
						"update reserve set ResQty=ResQty+'"+buy.getQuantity()+"' where WareID='"+buy.getWareId()+"'";
					pstmt = dbc.getConnection().prepareStatement(sql3) ;
					
				}
				else  //没有库存记录
				{
					String sql4="insert into reserve(WareID,ResQty) values(?,?)";
					pstmt = dbc.getConnection().prepareStatement(sql4) ;
					pstmt.setInt(1, buy.getWareId());
					pstmt.setInt(2, buy.getQuantity());
					
				}
				pstmt.executeUpdate();
				
			}
			catch (Exception e)
			{
				// System.out.println(e) ;
				throw new Exception("插入表时出现错误！！！") ;
			}
			finally
			{
				dbc.close() ;
			}
		}
		

	public List getlist() {
		List all = new ArrayList() ;
		String sql =
			"SELECT SupplierID,WareID,Price,Quantity,BuyDate,BuyPerson from buy" ;
		PreparedStatement pstmt = null ;
		DBConnection dbc = new DBConnection();
		
		try
		{
			pstmt = dbc.getConnection().prepareStatement(sql) ;
			ResultSet rs = pstmt.executeQuery() ;
			while(rs.next())
			{
				Buy buy=new Buy();
				
				buy.setSupplierId(rs.getInt(1));
				buy.setWareId(rs.getInt(2));
				buy.setPrice(rs.getInt(3));
				buy.setQuantity(rs.getInt(4));
				buy.setBuyDate(rs.getString(5));
				buy.setBuyPerson(rs.getString(6));
				all.add(buy) ;
			}
			rs.close() ;
			pstmt.close() ;
		}
		catch (Exception e)
		{
			System.out.println(e) ;
			
		}
		finally
		{
			dbc.close() ;
		}
		return all ;
	}

	

}