warehouseinfodao.java

基于j2ee的物流软件

package com.wuliu.dao;

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

import com.wuliu.DBConnection.DBConnection;
import com.wuliu.entity.WareHouseInfo;

public class WareHouseInfoDAO {

	/**
	 * @袁子鹏 仓库信息表.DAO
	 */
	private DBConnection dao = null;
	private Connection cn = null;
	private PreparedStatement ps = null;
	private ResultSet rs = null;

	// 查看仓库信息表中的所有内容
	public List<WareHouseInfo> selectAllWareHouseInfo() {
		this.dao = new DBConnection();
		this.cn = this.dao.getConnection();
		List<WareHouseInfo> list = new ArrayList<WareHouseInfo>();
		WareHouseInfo whi = null;
		String sql = "select * from WareHouseInfoTable";
		try {
			this.ps = this.cn.prepareStatement(sql);
			rs = this.ps.executeQuery();
			while (rs.next()) {
				int WareHouseInfoTableId = rs.getInt("WareHouseInfoTableId");
				String WareHouseId = rs.getString("WareHouseId");
				String WareHouseName = rs.getString("WareHouseName");
				String ChargeId = rs.getString("ChargeId");
				String WareHouseAdd = rs.getString("WareHouseAdd");
				whi = new WareHouseInfo(WareHouseInfoTableId, WareHouseId,
						WareHouseName, ChargeId, WareHouseAdd);
				list.add(whi);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			this.dao.closeResultSet(rs);
			this.dao.closeConnection(cn);
		}
		return list;
	}
	//修改仓库信息
    public void updateWareHouseInfo(int id, String WareHouseId,String WareHouseName,String ChargeId,String WareHouseAdd){
    	this.dao = new DBConnection();
    	this.cn = this.dao.getConnection();
    	try {
			this.ps=this.cn.prepareStatement("update WareHouseInfoTable set WareHouseId=?, WareHouseName=?,ChargeId=?,WareHouseAdd=? where WareHouseInfoTableId=?");
			this.ps.setString(1, WareHouseId);
			this.ps.setString(2, WareHouseName);
			this.ps.setString(3, ChargeId);
			this.ps.setString(4, WareHouseAdd);
			this.ps.setInt(5, id);
			this.ps.executeUpdate();
    	} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			this.dao.closeConnection(cn);
		}
    }	
    //新增一个仓库
	public void insertWareHouseInfo(WareHouseInfo whi){
		this.dao = new DBConnection();
    	this.cn = this.dao.getConnection();
		try
		{
			PreparedStatement ps = cn.prepareStatement("insert into " +
					"WareHouseInfoTable(WareHouseId,WareHouseName,ChargeId,WareHouseAdd) " +
					"values(?,?,?,?)");
			ps.setString(1,whi.getWareHouseId());
			ps.setString(2,whi.getWareHouseName());
			ps.setString(3,whi.getChargeId());
			ps.setString(4,whi.getWareHouseAdd());
			ps.executeUpdate();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally{
			this.dao.closeConnection(cn);
		}
	}
 // 通过Id删除某条仓库信息
	public void deleteWareHouseInfoById(int id) {
		this.dao = new DBConnection();
    	this.cn = this.dao.getConnection();
		try {
			this.ps = this.cn
					.prepareStatement("delete from WareHouseInfoTable where WareHouseInfoTableId=?");
			this.ps.setInt(1, id);
			this.ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
		  this.dao.closeConnection(cn);
		}
	}
	
	// 通过Id删除某条仓库信息
	public void deleteWareHouseInfoByWareHouseId(String wareHouseId) {
		this.dao = new DBConnection();
    	this.cn = this.dao.getConnection();
		try {
			this.ps = this.cn
					.prepareStatement("delete from WareHouseInfoTable where wareHouseId=?");
			this.ps.setString(1, wareHouseId);
			this.ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
		  this.dao.closeConnection(cn);
		}
	}
	
	// 由仓库编号查询仓库信息
	public WareHouseInfo selectWareHouseInfoById(int id) {
		this.dao = new DBConnection();
		this.cn = this.dao.getConnection();
		WareHouseInfo whi = null;
		String sql = "select * from WareHouseInfoTable where WareHouseInfoTableId=?";
		try {
			this.ps = this.cn.prepareStatement(sql);
			this.ps.setInt(1, id);
			rs = ps.executeQuery();
			if (rs.next()) {
				String wareHouseId = rs.getString("WareHouseId");
				String wareHouseName = rs.getString("WareHouseName");
				String chargeId = rs.getString("ChargeId");
				String wareHouseAdd = rs.getString("WareHouseAdd");
				whi = new WareHouseInfo(wareHouseId, wareHouseName, chargeId,
						wareHouseAdd);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			this.dao.closeResultSet(rs);
			this.dao.closeConnection(cn);
		}

		return whi;
	}

	// 由仓库编号查询仓库信息(崔斌添)
	public WareHouseInfo selectWareHouseInfoById(String id) {
		this.dao = new DBConnection();
		this.cn = this.dao.getConnection();
		WareHouseInfo whi = null;
		String sql = "select * from WareHouseInfoTable where WareHouseId=?";
		try {
			this.ps = this.cn.prepareStatement(sql);
			this.ps.setString(1, id);
			rs = ps.executeQuery();
			if (rs.next()) {
				String wareHouseId = rs.getString("WareHouseId");
				String wareHouseName = rs.getString("WareHouseName");
				String chargeId = rs.getString("ChargeId");
				String wareHouseAdd = rs.getString("WareHouseAdd");
				whi = new WareHouseInfo(wareHouseId, wareHouseName, chargeId,
						wareHouseAdd);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			this.dao.closeResultSet(rs);
			this.dao.closeConnection(cn);
		}

		return whi;
	}
	
	// 分页大小
	public int getpageNum(int pageSize) {
		this.dao = new DBConnection();
		this.cn = this.dao.getConnection();
		int sum = 0;
		try {
			ps = cn.prepareStatement("select count(*) from WareHouseInfoTable");
			rs = ps.executeQuery();
			if (rs.next()) {
				sum = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			this.dao.closeConnection(cn);
		}
		if (sum % pageSize == 0) {
			return sum / pageSize;
		} else {
			return sum / pageSize + 1;
		}
	}

	// 分页
	public List<WareHouseInfo> selectWareHouseInfoPage(int pageSize, int pageNum) {
		this.dao = new DBConnection();
		this.cn = this.dao.getConnection();
		List<WareHouseInfo> list = new ArrayList<WareHouseInfo>();
		try {
			ps = cn
					.prepareStatement("select top "
							+ pageSize
							+ " * from WareHouseInfoTable  where WareHouseInfoTableId not in (select top "
							+ pageSize
							* pageNum
							+ " WareHouseInfoTableId from WareHouseInfoTable order by WareHouseInfoTableId)order by WareHouseInfoTableId");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		ResultSet rs = null;
		try {
			rs = ps.executeQuery();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		WareHouseInfo whi = null;
		try {
			while (rs.next()) {
				int WareHouseInfoTableId = rs.getInt("WareHouseInfoTableId");
				String WareHouseId = rs.getString("WareHouseId");
				String WareHouseName = rs.getString("WareHouseName");
				String ChargeId = rs.getString("ChargeId");
				String WareHouseAdd = rs.getString("WareHouseAdd");
				whi = new WareHouseInfo(WareHouseInfoTableId, WareHouseId,
						WareHouseName, ChargeId, WareHouseAdd);
				list.add(whi);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			this.dao.closeResultSet(rs);
			this.dao.closeConnection(cn);
		}
		return list;
	}

	//(刘海鹏添加)
	public WareHouseInfo selectWareHouseById(int id){
		this.dao = new DBConnection();
		this.cn = this.dao.getConnection();
		WareHouseInfo whi = null;
		String sql = "select * from wareHouseInfo where WareHouseInfoTableId=?";
		try {
			this.ps = this.cn.prepareStatement(sql);
			this.ps.setInt(1, id);
			this.rs = ps.executeQuery();
			if (rs.next()) {
				String wareHouseId = rs.getString("WareHouseId");
				String wareHouseName = rs.getString("WareHouseName");
				String chargeId = rs.getString("ChargeId");
				String chargeName = rs.getString("EmployeeName");
				String wareHouseAdd = rs.getString("WareHouseAdd");
				whi = new WareHouseInfo(id, wareHouseId, wareHouseName, chargeId, chargeName, wareHouseAdd);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			this.dao.closeResultSet(rs);
			this.dao.closeConnection(cn);
		}
		return whi;
	}
	
	//查询仓库信息(刘海鹏添加)
	public List<WareHouseInfo> selectWareHouseInfo(int pageSize, int pageNum) {
		this.dao = new DBConnection();
		this.cn = this.dao.getConnection();
		List<WareHouseInfo> list = new ArrayList<WareHouseInfo>();
		WareHouseInfo whi;
		
		try {
			this.ps = this.cn.prepareStatement("select top "
						+ pageSize
						+ " * from wareHouseInfo  where WareHouseInfoTableId not in (select top "
						+ pageSize * pageNum
						+ " WareHouseInfoTableId from wareHouseInfo order by WareHouseInfoTableId)order by WareHouseInfoTableId");
			this.rs = this.ps.executeQuery();
			while (rs.next()) {
				int id = rs.getInt("WareHouseInfoTableId");
				String WareHouseId = rs.getString("WareHouseId");
				String WareHouseName = rs.getString("WareHouseName");
				String ChargeId = rs.getString("ChargeId");
				String ChargeName = rs.getString("EmployeeName");
				String WareHouseAdd = rs.getString("WareHouseAdd");
				whi = new WareHouseInfo(id, WareHouseId, WareHouseName, ChargeId, ChargeName, WareHouseAdd);
				list.add(whi);
			}
			this.dao.closeResultSet(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			this.dao.closePrepStmt(ps);
			try {
				this.cn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}
	
	public static void main(String[] args) {
		// 测试含所有参数的方法selectAllWareHouseInfo
		 List<WareHouseInfo> list =new ArrayList<WareHouseInfo>();
		 WareHouseInfoDAO pd =new WareHouseInfoDAO();
		 list=pd.selectAllWareHouseInfo();
		 for(int i=0;i<list.size();i++){
		 System.out
		 .println(list.get(i).getWareHouseInfoTableId()+list.get(i).getWareHouseId()
		 +list.get(i).getWareHouseAdd()+list.get(i).getChargeId()+list.get(i).getWareHouseName());
		 }

		// 测试按WareHouseId查找的方法selectWareHouseInfoById
//		 List<WareHouseInfo> list =new ArrayList<WareHouseInfo>();
//		 WareHouseInfoDAO pd =new WareHouseInfoDAO();
//		 list=pd.selectWareHouseInfo(10, 0);
//		 for(int i=0;i<list.size();i++){
//		 System.out
//		 .println(list.get(i).getWareHouseId()+list.get(i).getWareHouseAdd()
//		 +list.get(i).getChargeId()+list.get(i).getWareHouseName()+list.get(i).getChargeName());
//		 }

		// 测试分页
//		 WareHouseInfoDAO pd =new WareHouseInfoDAO();
		// int sum=pd.getpageNum(1);
		// System.out.print(sum);
		
//		WareHouseInfoDAO pd = new WareHouseInfoDAO();
//		List<WareHouseInfo> list = new ArrayList<WareHouseInfo>();
//		list = pd.selectWareHouseInfoPage(2, 0);
//		for(int i=0;i<list.size();i++){
//			System.out.println(list.get(i).getWareHouseInfoTableId()+list.get(i).getWareHouseAdd()
//					+list.get(i).getChargeId()+list.get(i).getWareHouseId()+list.get(i).getWareHouseName());
//		}
		
//		pd.deleteWareHouseInfoById(1);
		//测试修改仓库信息表
//		pd.updateWareHouseInfo("BJ001", "海淀", "EP0902", "北京海淀");
		//测试新增一个仓库
//		WareHouseInfo whi = new WareHouseInfo("YN001", "云南", "EP0902", "云南大理");
//		pd.insertWareHouseInfo(whi);
		
	}
}