query.java

本系统是基于ASP的大型超市销售管理系统

package com.smmis;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
abstract class  Query {
	private DBSellTable dbSell = null;
	private DBStock dbStock = null;
	
	private DBConnection dbc = null;
	private Connection conn = null;
	
	public abstract List getIdQuery(String goodsId);
	public abstract List getNameQuery(String goodsName);
	public abstract List getDateQuery(String dateFrom,String dateTo);
}

class PurQuery extends Query{
	
	
	
	private DBConnection dbc = new DBConnection();
	private Connection conn = dbc.getDBConnection();
	private String sql;
	private ResultSet rst;
	
	public List getIdQuery(String goodsId) {//进药查询中的按药品编码查询
		List list = new ArrayList();
		try {			
			Statement stmt = conn.createStatement();
			sql = "select * from purchase where goodsId = '" + goodsId + "'";
			rst = stmt.executeQuery(sql);
			while(rst.next()) {
				DBPurchaseTable dbPur = new DBPurchaseTable();
				dbPur.setReceiptId(rst.getInt("receiptId"));
				dbPur.setMedicineId(rst.getString("goodsId"));
				dbPur.setPurPrice(rst.getFloat("purPrice"));
				dbPur.setPurQuantity(rst.getInt("purQuantity"));
				dbPur.setPurDate(rst.getString("purDate"));
				dbPur.setSupplier(rst.getString("supplier"));
				list.add(dbPur);
				/*
				System.out.println(dbPur.getReceiptId());
				System.out.println(dbPur.getMedicineId());
				System.out.println(dbPur.getPurPrice());
				System.out.println(dbPur.getPurQuantity());
				System.out.println(dbPur.getPurDate());
				System.out.println(dbPur.getSupplier());
				*/
			}
			//System.out.println("listsize = " + list.size());
			
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		return list;		
	}
	
	public List getNameQuery(String goodsName) {//进药查询中的按药品编码查询
		List list = new ArrayList();
		try {			
			Statement stmt = conn.createStatement();
			sql = "select * from purchase p ,stock s where p.goodsId = s.goodsId and goodsName = '" + goodsName + "'" +
					" order by receiptId , purDate asc";
			rst = stmt.executeQuery(sql);
			while(rst.next()) {
				DBPurchaseTable dbPur = new DBPurchaseTable();
				dbPur.setReceiptId(rst.getInt("receiptId"));
				dbPur.setMedicineId(rst.getString("goodsId"));
				dbPur.setPurPrice(rst.getFloat("purPrice"));
				dbPur.setPurQuantity(rst.getInt("purQuantity"));
				dbPur.setPurDate(rst.getString("purDate"));
				dbPur.setSupplier(rst.getString("supplier"));
				list.add(dbPur);
				/*
				System.out.println(dbPur.getReceiptId());
				System.out.println(dbPur.getMedicineId());
				System.out.println(dbPur.getPurPrice());
				System.out.println(dbPur.getPurQuantity());
				System.out.println(dbPur.getPurDate());
				System.out.println(dbPur.getSupplier());
				*/
			}
			//System.out.println("listsize = " + list.size());
			
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		return list;		
	}

	public List getDateQuery(String dateFrom ,String dateTo) {
		List list = new ArrayList();
		try {
			Statement stmt = conn.createStatement();
			sql = "select * from purchase where purDate >= '" + dateFrom + "' and purDate <= '" + dateTo + "'" +
					"order by purDate";
			rst = stmt.executeQuery(sql);
			while(rst.next()) {
				DBPurchaseTable dbPur = new DBPurchaseTable();
				dbPur.setReceiptId(rst.getInt("receiptId"));
				dbPur.setMedicineId(rst.getString("goodsId"));
				dbPur.setPurPrice(rst.getFloat("purPrice"));
				dbPur.setPurQuantity(rst.getInt("purQuantity"));
				dbPur.setPurDate(rst.getString("purDate"));
				dbPur.setSupplier(rst.getString("supplier"));
				list.add(dbPur);
			}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
}

class SellQuery extends Query{

	private DBConnection dbc = new DBConnection();
	private Connection conn = dbc.getDBConnection();
	private String sql;
	private ResultSet rst;
	@Override
	public List getIdQuery(String goodsId) {
		// TODO 自动生成方法存根
		List list = new ArrayList();
		try {			
			Statement stmt = conn.createStatement();
			sql = "select * from sell where goodsId = '" + goodsId + "'" +
					"order by customerId";
			rst = stmt.executeQuery(sql);
			while(rst.next()) {
				DBSellTable dbSell = new DBSellTable();
				dbSell.setCustomerId(rst.getInt("customerId"));
				dbSell.setMedicineId(rst.getString("goodsId"));				
				dbSell.setSellQuantity(rst.getInt("sellQuantity"));
				dbSell.setSellDate(rst.getString("sellDate"));
				dbSell.setSellPrice(rst.getFloat("sellPrice"));
				list.add(dbSell);
			}
			//System.out.println("listsize = " + list.size());
			
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List getNameQuery(String goodsName) {
		// TODO 自动生成方法存根
		List list = new ArrayList();
		try {			
			Statement stmt = conn.createStatement();
			sql = "select * from sell s1,stock s2 where s1.goodsId = s2.goodsId and  goodsName = '" + goodsName + "'" +
					"order by customerId";
			rst = stmt.executeQuery(sql);
			while(rst.next()) {
				DBSellTable dbSell = new DBSellTable();
				dbSell.setCustomerId(rst.getInt("customerId"));
				dbSell.setMedicineId(rst.getString("goodsId"));				
				dbSell.setSellQuantity(rst.getInt("sellQuantity"));
				dbSell.setSellDate(rst.getString("sellDate"));
				dbSell.setSellPrice(rst.getFloat("sellPrice"));
				list.add(dbSell);
			}
			//System.out.println("listsize = " + list.size());
			
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List getDateQuery(String dateFrom, String dateTo) {
		// TODO 自动生成方法存根
		List list = new ArrayList();
		try {
			Statement stmt = conn.createStatement();
			sql = "select * from sell where sellDate >= '" + dateFrom + "' and sellDate <= '" + dateTo + "'" +
					"order by sellDate";
			rst = stmt.executeQuery(sql);
			while(rst.next()) {
				DBSellTable dbSell = new DBSellTable();
				dbSell.setCustomerId(rst.getInt("customerId"));
				dbSell.setMedicineId(rst.getString("goodsId"));				
				dbSell.setSellQuantity(rst.getInt("sellQuantity"));
				dbSell.setSellDate(rst.getString("sellDate"));
				dbSell.setSellPrice(rst.getFloat("sellPrice"));
				list.add(dbSell);
			}
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}
	
}