saledaoimpl.java

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

package mybaobao;

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

public class SaleDAOImpl implements SaleDAO {
	
	public void insert(Sale sale) throws Exception{   //插入到sale表,从库表里减去
		
		String sql=
			"insert into sale(WareID,SalePrice,SaleQty,SaleDate) values(?,?,?,?)";
		PreparedStatement pstmt = null ;
		DBConnection dbc = new DBConnection() ;
		
		try
		{
			pstmt = dbc.getConnection().prepareStatement(sql) ;
			pstmt.setInt(1,sale.getWareId()) ;
			pstmt.setInt(2,sale.getSalePrice()) ;
			pstmt.setInt(3,sale.getSaleQty()) ;
			pstmt.setString(4,sale.getSaleDate()) ;
			
			pstmt.executeUpdate();
			pstmt.close() ;
		    //以下是从库存里去掉销售的商品
			String sql2=
				"update reserve set ResQty=ResQty-'"+sale.getSaleQty()+"'where WareID='"+sale.getWareId()+"'";
			pstmt = dbc.getConnection().prepareStatement(sql2) ;
			pstmt.executeUpdate();
			//以下判断库存中是否有数量为0的商品,若有,则删除该商品记录
			String sql3="delete from reserve where ResQty=0";
			pstmt = dbc.getConnection().prepareStatement(sql3) ;
			pstmt.executeUpdate();
		}
		catch (Exception e)
		{
			
			System.out.println("销售入表时错误!!!");
		}
		finally
		{
			dbc.close() ;
		}
	}

	public List getlist() {
		List all = new ArrayList() ;
		String sql =
			"SELECT WareID,SalePrice,SaleQty,SaleDate from sale" ;
		PreparedStatement pstmt = null ;
		DBConnection dbc = new DBConnection();
		
		try
		{
			pstmt = dbc.getConnection().prepareStatement(sql) ;
			ResultSet rs = pstmt.executeQuery() ;
			while(rs.next())
			{
				Sale sale=new Sale();
				sale.setWareId(rs.getInt(1));
				sale.setSalePrice(rs.getInt(2));
				sale.setSaleQty(rs.getInt(3));
				sale.setSaleDate(rs.getString(4));
				all.add(sale) ;
			}
			rs.close() ;
			pstmt.close() ;
		}
		catch (Exception e)
		{
			System.out.println(e) ;
			
		}
		finally
		{
			dbc.close() ;
		}
		return all ;
	}

	
	

}