goodsinfodao.java

老牌java开发的物流管理系统 详细的我也没有怎么看 好像是恒基的

package com.wl.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.wl.dbconnection.DBManager;
import com.wl.entity.GoodsInfo;

/**
 * 商品信息表dao操作方法
 * 
 * @author yuan xinfeng
 * 
 */
public class GoodsInfoDAO {
	DBManager db = new DBManager();
	ResultSet rs = null;

	public GoodsInfoDAO() {

	}

	/**
	 * 查询所有的商品信息方法
	 * 
	 * @return list
	 */
	// ----------------------查询所有的商品信息方法-------------------------------
	public List<GoodsInfo> selectAllGoodsInfo() {
		List<GoodsInfo> list = new ArrayList<GoodsInfo>();
		String sql = "select * from goodsinfotable";
		db.openDB();
		try {
			db.ps = db.conn.prepareStatement(sql);
			rs = db.ps.executeQuery();
			while (rs.next()) {
				int id = rs.getInt("Id");
				String goodsId = rs.getString("GoodsId");
				String goodsName = rs.getString("GoodsName");
				int goodsCateId = rs.getInt("GoodsCateId");
				int goodsSupplyId = rs.getInt("GoodsSupplyId");
				String goodsABC = rs.getString("GoodsABC");
				String goodsProvince = rs.getString("GoodsProvince");
				String goodsCity = rs.getString("GoodsCity");
				int goodsStockId = rs.getInt("GoodsStockId");
				float goodsPrice = rs.getFloat("GoodsPrice");
				int goodsStockNumber = rs.getInt("GoodsStockNumber");
				String goodsOrderDate = rs.getString("GoodsOrderDate");
				String goodsStockInDate = rs.getString("GoodsStockInDate");
				String goodsUnit = rs.getString("GoodsUnit");
				int typeId = rs.getInt("TypeId");
				GoodsInfo goodsinfo = new GoodsInfo(id, goodsId, goodsName,
						goodsCateId, goodsSupplyId, goodsABC, goodsProvince,
						goodsCity, goodsStockId, goodsPrice, goodsStockNumber,
						goodsOrderDate, goodsStockInDate, goodsUnit,
						typeId);
				list.add(goodsinfo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("数据库连接失败!");
		} finally {
			db.closeDB();
		}
		return list;
	}
	
	/**
	 * 分页查询商品信息(2009.2.17 袁新锋修改)
	 */
	public List<GoodsInfo> selectgoodsinfoPage(int pageSize, int pageNum){
		 List<GoodsInfo> list = new  ArrayList<GoodsInfo>();
		 db.openDB();// 打开数据库
			 try{
				 db.ps = db.conn.prepareStatement("select top " + pageSize
							+ "* from goodsinfotable where Id not in(select top "
							+ pageSize * pageNum
							+ " Id from goodsinfotable order by Id)order by Id");
				 rs=db.ps.executeQuery();
				 while (rs.next()) {
						int id = rs.getInt("Id");
						String goodsId = rs.getString("GoodsId");
						String goodsName = rs.getString("GoodsName");
						int goodsCateId = rs.getInt("GoodsCateId");
						int goodsSupplyId = rs.getInt("GoodsSupplyId");
						String goodsABC = rs.getString("GoodsABC");
						String goodsProvince = rs.getString("GoodsProvince");
						String goodsCity = rs.getString("GoodsCity");
						int goodsStockId = rs.getInt("GoodsStockId");
						float goodsPrice = rs.getFloat("GoodsPrice");
						int goodsStockNumber = rs.getInt("GoodsStockNumber");
						String goodsOrderDate = rs.getString("GoodsOrderDate");
						String goodsStockInDate = rs.getString("GoodsStockInDate");
						String goodsUnit = rs.getString("GoodsUnit");
						int typeId = rs.getInt("TypeId");
						GoodsInfo goodsinfo = new GoodsInfo(id, goodsId, goodsName,
								goodsCateId, goodsSupplyId, goodsABC, goodsProvince,
								goodsCity, goodsStockId, goodsPrice, goodsStockNumber,
								goodsOrderDate, goodsStockInDate, goodsUnit,
								typeId);
						list.add(goodsinfo);
					}
			 }catch(Exception e){
				 System.out.print("数据库连接失败!！");
			 }finally {
					db.closeDB();// 关闭数据库
				}
			return list;
			 
			}
			
	/**
	 * 通过页面大小获取总页数(2009.2.17 袁新锋修改)
	 * @param pageSize
	 * @return num
	 */
	public int getPageNum(int pageSize) {
		int num = 0;
		 db.openDB();// 打开数据库
			try {
				db.ps=db.conn.prepareStatement("select count(*) from goodsinfotable");
				rs = db.ps.executeQuery();
				while(rs.next()){
					num = rs.getInt(1);
				}
				if (num % pageSize == 0) {
					return num / pageSize;
				} else {
					return num / pageSize + 1;
				}
			} catch (SQLException e) {
				System.out.println("数据库连接失败！");
				e.printStackTrace();
			}finally{
				db.closeDB();
			}
			return num;
		}
	

	/**
	 * 根据商品信息表自动编号查询商品信息方法
	 *
	 * @param id
	 * @return list
	 */
	/**修改原因
	 * tian xiaoshun 
	 * 根据自动编号查询应是一个对象
	 */
	// ----------------------根据商品信息表自动编号查询商品信息方法-------------------------------
	public GoodsInfo selectGoodsInfoById(int id) {
		GoodsInfo goodsinfo = null;
		String sql = "select * from goodsinfotable where Id=?";
		db.openDB();
		try {
			db.ps = db.conn.prepareStatement(sql);
			db.ps.setInt(1, id);
			rs = db.ps.executeQuery();
			while (rs.next()) {
				id = rs.getInt("Id");
				String goodsId = rs.getString("GoodsId");
				String goodsName = rs.getString("GoodsName");
				int goodsCateId = rs.getInt("GoodsCateId");
				int goodsSupplyId = rs.getInt("GoodsSupplyId");
				String goodsABC = rs.getString("GoodsABC");
				String goodsProvince = rs.getString("GoodsProvince");
				String goodsCity = rs.getString("GoodsCity");
				int goodsStockId = rs.getInt("GoodsStockId");
				float goodsPrice = rs.getFloat("GoodsPrice");
				int goodsStockNumber = rs.getInt("GoodsStockNumber");
				String goodsOrderDate = rs.getString("GoodsOrderDate");
				String goodsStockInDate = rs.getString("GoodsStockInDate");
				String goodsUnit = rs.getString("GoodsUnit");
				int typeId = rs.getInt("TypeId");
				
				goodsinfo = new GoodsInfo(id, goodsId, goodsName,
						goodsCateId, goodsSupplyId, goodsABC, goodsProvince,
						goodsCity, goodsStockId, goodsPrice, goodsStockNumber,
						goodsOrderDate, goodsStockInDate, goodsUnit,
						typeId);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("数据库连接失败!");
		} finally {
			db.closeDB();
		}
		return goodsinfo;
	}

	/**
	 * 根据商品Id查询商品信息方法
	 * 
	 * @param goodsId
	 * @return list
	 */
	// ----------------------根据商品Id查询商品信息方法-------------------------------
	public List<GoodsInfo> selectGoodsInfoByGoodsId(String goodsId) {
		List<GoodsInfo> list = new ArrayList<GoodsInfo>();
		String sql = "select * from goodsinfotable where GoodsId=?";
		db.openDB();
		try {
			db.ps = db.conn.prepareStatement(sql);
			db.ps.setString(1, goodsId);
			rs = db.ps.executeQuery();
			while (rs.next()) {
				int id = rs.getInt("Id");
				goodsId = rs.getString("GoodsId");
				String goodsName = rs.getString("GoodsName");
				int goodsCateId = rs.getInt("GoodsCateId");
				int goodsSupplyId = rs.getInt("GoodsSupplyId");
				String goodsABC = rs.getString("GoodsABC");
				String goodsProvince = rs.getString("GoodsProvince");
				String goodsCity = rs.getString("GoodsCity");
				int goodsStockId = rs.getInt("GoodsStockId");
				float goodsPrice = rs.getFloat("GoodsPrice");
				int goodsStockNumber = rs.getInt("GoodsStockNumber");
				String goodsOrderDate = rs.getString("GoodsOrderDate");
				String goodsStockInDate = rs.getString("GoodsStockInDate");
				String goodsUnit = rs.getString("GoodsUnit");
				int typeId = rs.getInt("TypeId");
				GoodsInfo goodsinfo = new GoodsInfo(id, goodsId, goodsName,
						goodsCateId, goodsSupplyId, goodsABC, goodsProvince,
						goodsCity, goodsStockId, goodsPrice, goodsStockNumber,
						goodsOrderDate, goodsStockInDate, goodsUnit,
						typeId);
				list.add(goodsinfo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("数据库连接失败!");
		} finally {
			db.closeDB();
		}
		return list;
	}

	/**
	 * 根据商品名称查询商品信息方法
	 * 
	 * @param goodsName
	 * @return list
	 */
	// ----------------------根据商品名称查询商品信息方法-------------------------------
	public List<GoodsInfo> selectGoodsInfoByGoodsName(String goodsName) {
		List<GoodsInfo> list = new ArrayList<GoodsInfo>();
		String sql = "select * from goodsinfotable where GoodsName like ?";
		db.openDB();
		try {
			db.ps = db.conn.prepareStatement(sql);
			db.ps.setString(1, "%"+goodsName+"%");
			rs = db.ps.executeQuery();
			while (rs.next()) {
				int id = rs.getInt("Id");
				String goodsId = rs.getString("GoodsId");
				goodsName = rs.getString("GoodsName");
				int goodsCateId = rs.getInt("GoodsCateId");
				int goodsSupplyId = rs.getInt("GoodsSupplyId");
				String goodsABC = rs.getString("GoodsABC");
				String goodsProvince = rs.getString("GoodsProvince");
				String goodsCity = rs.getString("GoodsCity");
				int goodsStockId = rs.getInt("GoodsStockId");
				float goodsPrice = rs.getFloat("GoodsPrice");
				int goodsStockNumber = rs.getInt("GoodsStockNumber");
				String goodsOrderDate = rs.getString("GoodsOrderDate");
				String goodsStockInDate = rs.getString("GoodsStockInDate");
				String goodsUnit = rs.getString("GoodsUnit");
				int typeId = rs.getInt("TypeId");
				GoodsInfo goodsinfo = new GoodsInfo(id, goodsId, goodsName,
						goodsCateId, goodsSupplyId, goodsABC, goodsProvince,
						goodsCity, goodsStockId, goodsPrice, goodsStockNumber,
						goodsOrderDate, goodsStockInDate, goodsUnit,
						typeId);
				list.add(goodsinfo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("数据库连接失败!");
		} finally {
			db.closeDB();
		}
		return list;
	}
//public static void main(String [] args){
//	GoodsInfoDAO dao =new GoodsInfoDAO();
//	System.out.println(dao.selectGoodsInfoByGoodsName("长虹"));
//}
	/**
	 * 根据商品分类Id查询商品信息方法
	 * 
	 * @param goodsCateId
	 * @return list
	 */
	// ----------------------根据商品分类Id查询商品信息方法-------------------------------
	public List<GoodsInfo> selectGoodsInfoByGoodsCateId(int goodsCateId) {
		List<GoodsInfo> list = new ArrayList<GoodsInfo>();
		String sql = "select * from goodsinfotable where GoodsCateId=?";
		db.openDB();
		try {
			db.ps = db.conn.prepareStatement(sql);
			db.ps.setInt(1, goodsCateId);
			rs = db.ps.executeQuery();
			while (rs.next()) {
				int id = rs.getInt("Id");
				String goodsId = rs.getString("GoodsId");
				String goodsName = rs.getString("GoodsName");
				goodsCateId = rs.getInt("GoodsCateId");
				int goodsSupplyId = rs.getInt("GoodsSupplyId");
				String goodsABC = rs.getString("GoodsABC");
				String goodsProvince = rs.getString("GoodsProvince");
				String goodsCity = rs.getString("GoodsCity");
				int goodsStockId = rs.getInt("GoodsStockId");
				float goodsPrice = rs.getFloat("GoodsPrice");
				int goodsStockNumber = rs.getInt("GoodsStockNumber");
				String goodsOrderDate = rs.getString("GoodsOrderDate");
				String goodsStockInDate = rs.getString("GoodsStockInDate");
				String goodsUnit = rs.getString("GoodsUnit");
				int typeId = rs.getInt("TypeId");
				GoodsInfo goodsinfo = new GoodsInfo(id, goodsId, goodsName,
						goodsCateId, goodsSupplyId, goodsABC, goodsProvince,
						goodsCity, goodsStockId, goodsPrice, goodsStockNumber,
						goodsOrderDate, goodsStockInDate, goodsUnit,
						typeId);
				list.add(goodsinfo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("数据库连接失败!");
		} finally {
			db.closeDB();
		}
		return list;
	}

	/**
	 * 根据商品供应商Id查询商品信息方法
	 * 
	 * @param goodsSupplyId
	 * @return list
	 */
	// ----------------------根据商品供应商Id查询商品信息方法-------------------------------
	public List<GoodsInfo> selectGoodsInfoByGoodsSupplyId(int goodsSupplyId) {
		List<GoodsInfo> list = new ArrayList<GoodsInfo>();
		String sql = "select * from goodsinfotable where GoodsSupplyId=?";