stockregioninfodao.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.StockRegionInfo;

public class StockRegionInfoDAO {
	
	/**
	 * @袁子鹏
	 * 商品在库位置表.dao
	 */
	  private DBConnection dao = null;
	  private Connection cn = null;
	  private PreparedStatement ps = null;
	  private ResultSet rs = null;
	  
	   //查看商品在库位置表中的所有信息
		public List<StockRegionInfo> selectAllStockRegionInfo(){
			this.dao = new DBConnection();
			this.cn = this.dao.getConnection();
			List<StockRegionInfo> list = new ArrayList<StockRegionInfo>();
			StockRegionInfo sri = null;
			String sql = "select * from StockRegionInfoTable";
			try {
				this.ps = this.cn.prepareStatement(sql);
			    rs = this.ps.executeQuery();
				while(rs.next()){
					int StockRegionInfoTableId = rs.getInt("StockRegionInfoTableId");
					String StockRegionId = rs.getString("StockRegionId");
					String StockRegionName = rs.getString("StockRegionName");
					sri = new StockRegionInfo(StockRegionInfoTableId,StockRegionId,StockRegionName);
					list.add(sri);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				this.dao.closeResultSet(rs);
				this.dao.closeConnection(cn);
			}
			return list;
		}	
		// 由商品在库位置编号 StockRegionId 查询仓库信息
		public StockRegionInfo selectStockRegionInfoById(int stockRegionInfoTableId) {
			this.dao = new DBConnection();
			this.cn = this.dao.getConnection();
			StockRegionInfo sri = null;
			String sql = "select * from StockRegionInfoTable where stockRegionInfoTableId=?";
			try {
				this.ps = this.cn.prepareStatement(sql);
				this.ps.setInt(1, stockRegionInfoTableId);
				rs = ps.executeQuery();
				if (rs.next()) {
					String StockRegionId=rs.getString("StockRegionId");
					String StockRegionName = rs.getString("StockRegionName");
					sri = new StockRegionInfo(stockRegionInfoTableId,StockRegionId, StockRegionName);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				this.dao.closeResultSet(rs);
				this.dao.closeConnection(cn);
			}

			return sri;
		}
		
		// 由商品在库位置编号 StockRegionId 查询仓库信息(崔斌添)
		public StockRegionInfo selectStockRegionInfoById(String stockRegionId) {
			this.dao = new DBConnection();
			this.cn = this.dao.getConnection();
			StockRegionInfo sri = null;
			String sql = "select * from StockRegionInfoTable where stockRegionId=?";
			try {
				this.ps = this.cn.prepareStatement(sql);
				this.ps.setString(1, stockRegionId);
				rs = ps.executeQuery();
				if (rs.next()) {
					int stockRegionInfoTableId = rs.getInt("stockRegionInfoTableId");
					String StockRegionId=rs.getString("StockRegionId");
					String StockRegionName = rs.getString("StockRegionName");
					sri = new StockRegionInfo(stockRegionInfoTableId,StockRegionId, StockRegionName);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				this.dao.closeResultSet(rs);
				this.dao.closeConnection(cn);
			}

			return sri;
		}
		//通过 StockRegionId 修改商品在库位置表
	    public void updateStockRegionInfo(String StockRegionId,String StockRegionName){
	    	this.dao = new DBConnection();
	    	this.cn = this.dao.getConnection();
	    	try {
				this.ps=this.cn.prepareStatement("update StockRegionInfoTable set StockRegionName=? where StockRegionId=?");
				this.ps.setString(1, StockRegionName);
				this.ps.setString(2, StockRegionId);
				this.ps.executeUpdate();
	    	} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				this.dao.closeConnection(cn);
			}
	    }	  
		// 通过Id删除某条商品信息1-15void
		public boolean deleteStockRegionInfoById(String id) {
			this.dao = new DBConnection();
	    	this.cn = this.dao.getConnection();
	    	boolean flag=true;
			try {
				this.ps = this.cn
						.prepareStatement("delete from StockRegionInfoTable where StockRegionId=?");
				this.ps.setString(1, id);
				this.ps.executeUpdate();
			} catch (SQLException e) {
				e.printStackTrace();
				flag=false;
			}finally{
			  this.dao.closeConnection(cn);
			}
			return flag;
		}
		//插入一条记录2.19 void,通过测试
		public void insertStockRegion(String id,String name){
			this.dao=new DBConnection();
			this.cn=this.dao.getConnection();
			String sql="insert into StockRegionInfoTable values(?,?)";
			try {
				this.ps=this.cn.prepareStatement(sql);
				this.ps.setString(1, id);
				this.ps.setString(2, name);
				this.ps.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				this.dao.closePrepStmt(ps);
				this.dao.closeConnection(cn);
			}
		}
		// 分页大小
		public int getpageNum(int pageSize) {
			this.dao = new DBConnection();
			this.cn = this.dao.getConnection();
			int sum = 0;
			try {
				ps = cn.prepareStatement("select count(*) from StockRegionInfoTable");
				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<StockRegionInfo> selectStockRegionInfoPage(int pageSize, int pageNum) {
			this.dao = new DBConnection();
			this.cn = this.dao.getConnection();
			List<StockRegionInfo> list = new ArrayList<StockRegionInfo>();
			try {
				ps = cn
						.prepareStatement("select top "
								+ pageSize
								+ " * from StockRegionInfoTable  where StockRegionInfoTableId not in (select top "
								+ pageSize
								* pageNum
								+ " StockRegionInfoTableId from StockRegionInfoTable order by StockRegionInfoTableId)order by StockRegionInfoTableId");
			} 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();
			}
			StockRegionInfo sri = null;
			try {
				while(rs.next()){
					int StockRegionInfoTableId = rs.getInt("StockRegionInfoTableId");
					String StockRegionId = rs.getString("StockRegionId");
					String StockRegionName = rs.getString("StockRegionName");
					sri = new StockRegionInfo(StockRegionInfoTableId,StockRegionId,StockRegionName);
					list.add(sri);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				this.dao.closeResultSet(rs);
				this.dao.closeConnection(cn);
			}
			return list;
		}
		
		 public static void main(String[] args){
			    
			    //测试查找所有商品在库位置
//		    	List<StockRegionInfo> list =new ArrayList<StockRegionInfo>();
//		    	StockRegionInfoDAO pd =new StockRegionInfoDAO();
//		    	list=pd.selectAllStockRegionInfo();
//		    	for(int i=0;i<list.size();i++){
//		    		System.out .println(list.get(i).getStockRegionName()+" "+list.get(i).getStockRegionId());
//		    	}
			 
			    //测试分页查找商品在库位置
//			    StockRegionInfoDAO pd = new StockRegionInfoDAO();
//				List<StockRegionInfo> list = new ArrayList<StockRegionInfo>();
//				list = pd.selectStockRegionInfoPage(5, 0);
//				for(int i=0;i<list.size();i++){
//					System.out.println(list.get(i).getStockRegionInfoTableId()+list.get(i).getStockRegionName()+" "+list.get(i).getStockRegionId());
//				}
//			    System.out.println(pd.getpageNum(5));
//			   StockRegionInfoDAO pd = new StockRegionInfoDAO();
			   //测试修改商品在库位置表
//			   pd.updateStockRegionInfo("BJ001-01", "左一区");
//		    	pd.insertStockRegion("123", "asdasd");
			   
		 }
		
}