stockoutinfodao.java

基于j2ee的物流软件

package com.wuliu.dao;

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

import com.wuliu.DBConnection.DBConnection;
import com.wuliu.entity.StockOutInfo;

/*
 * By void
 */

public class StockOutInfoDAO {
	private Connection cn = null;
	private DBConnection db = null;
	private PreparedStatement ps = null;
	private ResultSet rs = null;

	public StockOutInfoDAO() {
		this.db = new DBConnection();
	}

	/*
	 * 查找出库表中所有元素
	 */
	public List<StockOutInfo> selectStockOutInfoTable() {
		this.cn = this.db.getConnection();
		List<StockOutInfo> list = new ArrayList<StockOutInfo>();
		String sql = "select * from StockOutInfoTable";
		try {
			this.ps = this.cn.prepareStatement(sql);
			this.rs = this.ps.executeQuery();
			while (this.rs.next()) {
				int StockOutInfoTableId = this.rs.getInt("StockOutInfoTableId");
				String StockOutId = this.rs.getString("StockOutId");
				String StockOutDate = this.rs.getString("StockOutDate");
				String CarId = this.rs.getString("CarId");
				String ChargeId = this.rs.getString("ChargeId");
				String GoodsId = this.rs.getString("GoodsId");
				int GoodsOutNumber = this.rs.getInt("GoodsOutNumber");
				String GoodsConsigneeId = this.rs.getString("GoodsConsigneeId");
				String GoodsTypeId = this.rs.getString("GoodsTypeId");
				String GoodsRemark = this.rs.getString("GoodsRemark");
				StockOutInfo skof = new StockOutInfo(StockOutInfoTableId,
						StockOutId, StockOutDate, CarId, ChargeId, GoodsId,
						GoodsOutNumber, GoodsConsigneeId, GoodsTypeId,
						GoodsRemark);
				list.add(skof);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			this.db.closeResultSet(rs);
			this.db.closePrepStmt(ps);
			this.db.closeConnection(cn);
		}
		return list;

	}

	/*
	 * 插入一条记录
	 */
	public void insertStockOutInfoTable(StockOutInfo skof) {
		this.cn = this.db.getConnection();
		StockOutInfoDAO sodao = new StockOutInfoDAO();
		String sql = "insert into StockOutInfoTable(StockOutId,StockOutDate,CarId,ChargeId,GoodsId,GoodsOutNumber,"
				+ "GoodsConsigneeId,GoodsTypeId,GoodsRemark)values(?,?,?,?,?,?,?,?,?)";
		try {
			this.ps = this.cn.prepareStatement(sql);
			//this.ps.setString(1, skof.getStockOutId());
			this.ps.setString(1, sodao.getTempId());
			this.ps.setString(2, skof.getStockOutDate());
			this.ps.setString(3, skof.getCarId());
			this.ps.setString(4, skof.getChargeId());
			this.ps.setString(5, skof.getGoodsId());
			this.ps.setInt(6, skof.getGoodsOutNumber());
			this.ps.setString(7, skof.getGoodsConsigneeId());
			this.ps.setString(8, skof.getGoodsTypeId());
			this.ps.setString(9, skof.getGoodsRemark());
			this.ps.executeUpdate();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			this.db.closePrepStmt(ps);
			this.db.closeConnection(cn);
		}
	}

	/*
	 * 修改出库表中的信息
	 */
	public void updateStockOutInfoTable(String StockOutId,
			String StockOutDate, String CarId, String ChargeId, String GoodsId,
			int GoodsOutNumber, String GoodsConsigneeId, String GoodsTypeId,
			String GoodsRemark) {
		this.cn = this.db.getConnection();
		String sql = "update StockOutInfoTable set StockOutDate=?,CarId=?, ChargeId=?, GoodsId=?,"
				+ "GoodsOutNumber=?,GoodsConsigneeId=?,GoodsTypeId=?,GoodsRemark=? where StockOutId='"
				+ StockOutId+"'";
		try {
			this.ps = this.cn.prepareStatement(sql);
			this.ps.setString(1, StockOutDate);
			this.ps.setString(2, CarId);
			this.ps.setString(3, ChargeId);
			this.ps.setString(4, GoodsId);
			this.ps.setInt(5, GoodsOutNumber);
			this.ps.setString(6, GoodsConsigneeId);
			this.ps.setString(7, GoodsTypeId);
			this.ps.setString(8, GoodsRemark);
			this.ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			this.db.closePrepStmt(ps);
			this.db.closeConnection(cn);
		}
	}

	/*
	 * 删除一条记录By Id
	 */
	public void deleteStockOutInfoById(int id) {
		this.cn = this.db.getConnection();
		String sql = "delete from StockOutInfoTable where StockOutInfoTableId=?";
		try {
			this.ps = this.cn.prepareStatement(sql);
			this.ps.setInt(1, id);
			this.ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			this.db.closePrepStmt(ps);
			this.db.closeConnection(cn);
		}

	}

	/*
	 * 得到表中自动编号的最大值，1-9测试通过 参数（表名，要得到id列名）
	 */
	public int getLastId(String table, String listID) {
		this.cn = this.db.getConnection();
		String sql = "select Max(" + listID + ")as maxid from " + table;
		int maxID = 0;
		try {
			this.ps = this.cn.prepareStatement(sql);
			this.rs = this.ps.executeQuery();
			while (rs.next()) {
				maxID = rs.getInt("maxid");
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			this.db.closeResultSet(rs);
			this.db.closePrepStmt(ps);
			this.db.closeConnection(cn);
		}
		return maxID;
	}

	/*
	 * 生成出库单编码，查找所有元素，1-9测试通过
	 */
	public String createStockOutId() {
		String sko = "SKO-";
		int maxID = this.getLastId("StockOutInfoTable", "StockOutInfoTableId") + 1;
		return sko + maxID;
	}
	/*
	 * 崔版格式化编码
	 */
	public String getTempId(){
		String temp=null;
		int maxID=this.getLastId("StockOutInfoTable", "StockOutInfoTableId") + 1;
		temp=MessageFormat.format("SKO-{0,number,0000}", maxID);
		return temp;
		
	}
	/*
	 * 分页处理入库信息，1-11测试通过
	 */
	public List<StockOutInfo> selectStockOutInfoPage(int pageSize, int pageNum){
		this.cn=this.db.getConnection();
		List<StockOutInfo> list = new ArrayList<StockOutInfo>();
		String sql="select top "+pageSize +" * from StockOutInfoTable where StockOutInfoTableId not in(select top "+ 
			pageSize*pageNum+" StockOutInfoTableId from StockOutInfoTable order by StockOutInfoTableId)order by StockOutInfoTableId";
		try {
			this.ps=this.cn.prepareStatement(sql);
			this.rs=this.ps.executeQuery();
			while (this.rs.next()) {
				int StockOutInfoTableId = this.rs.getInt("StockOutInfoTableId");
				String StockOutId = this.rs.getString("StockOutId");
				String StockOutDate = this.rs.getString("StockOutDate");
				String CarId = this.rs.getString("CarId");
				String ChargeId = this.rs.getString("ChargeId");
				String GoodsId = this.rs.getString("GoodsId");
				int GoodsOutNumber = this.rs.getInt("GoodsOutNumber");
				String GoodsConsigneeId = this.rs.getString("GoodsConsigneeId");
				String GoodsTypeId = this.rs.getString("GoodsTypeId");
				String GoodsRemark = this.rs.getString("GoodsRemark");
				StockOutInfo skof = new StockOutInfo(StockOutInfoTableId,
						StockOutId, StockOutDate, CarId, ChargeId, GoodsId,
						GoodsOutNumber, GoodsConsigneeId, GoodsTypeId,
						GoodsRemark);
				list.add(skof);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			this.db.closeResultSet(rs);
			this.db.closePrepStmt(ps);
			this.db.closeConnection(cn);
		}
		return list;
		
	}
	/*
	 * 通过页面大小获取总页数,1-11测试通过
	 */
	public int getPageNum(int pageSize) {
		this.cn=this.db.getConnection();
		int num=0;
		String sql="select count(*) from StockOutInfoTable";
		try {
			this.ps=this.cn.prepareStatement(sql);
			this.rs=this.ps.executeQuery();
			if(this.rs.next()){
				num=this.rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			this.db.closeResultSet(rs);
			this.db.closePrepStmt(ps);
			this.db.closeConnection(cn);
		}
		if (num % pageSize == 0) {
			return num / pageSize;
		} else {
			return num / pageSize + 1;
		}
		
	}
	
	/*
	 * 删除一条记录By Id(崔斌添)
	 */
	public void deleteStockOutInfoById(String stockOutId) {
		this.cn = this.db.getConnection();
		String sql = "delete from StockOutInfoTable where stockOutId=?";
		try {
			this.ps = this.cn.prepareStatement(sql);
			this.ps.setString(1, stockOutId);
			this.ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			this.db.closePrepStmt(ps);
			this.db.closeConnection(cn);
		}

	}
	
	/*
	 * 删除一条记录By CarId(崔斌添)
	 */
	public void deleteStockOutInfoByCarId(String CarId) {
		this.cn = this.db.getConnection();
		String sql = "delete from StockOutInfoTable where CarId=?";
		try {
			this.ps = this.cn.prepareStatement(sql);
			this.ps.setString(1, CarId);
			this.ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			this.db.closePrepStmt(ps);
			this.db.closeConnection(cn);
		}

	}
	
	/*
	 * 查询记录 崔斌添
	 * */
	public StockOutInfo selectStockOutInfoById(String StockOutId) {
		this.cn = this.db.getConnection();
		StockOutInfo skof = null;
		try {
			this.ps = this.cn.prepareStatement("select * from StockOutInfoTable where StockOutId=?");
			ps.setString(1,StockOutId);
			rs = ps.executeQuery();
			if(this.rs.next()) {
				int StockOutInfoTableId = this.rs.getInt("StockOutInfoTableId");
				String StockOutDate = this.rs.getString("StockOutDate");
				String CarId = this.rs.getString("CarId");
				String ChargeId = this.rs.getString("ChargeId");
				String GoodsId = this.rs.getString("GoodsId");
				int GoodsOutNumber = this.rs.getInt("GoodsOutNumber");
				String GoodsConsigneeId = this.rs.getString("GoodsConsigneeId");
				String GoodsTypeId = this.rs.getString("GoodsTypeId");
				String GoodsRemark = this.rs.getString("GoodsRemark");
				skof = new StockOutInfo(StockOutInfoTableId,
						StockOutId, StockOutDate, CarId, ChargeId, GoodsId,
						GoodsOutNumber, GoodsConsigneeId, GoodsTypeId,
						GoodsRemark);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			this.db.closeResultSet(rs);
			this.db.closePrepStmt(ps);
			this.db.closeConnection(cn);
		}
		return skof;

	}
	
	public static void main(String[] args) {
		StockOutInfoDAO sodao = new StockOutInfoDAO();
//		sodao.selectStockOutInfoById("SKO-0010").getStockOutDate();
//		sodao.deleteStockOutInfoByCarId("陕A-27027");
		sodao.updateStockOutInfoTable("SKO-0002", "1998-1-2-1-2", "陕A-3625", "EP0903", "GOODS0002", 123123, "CE20090108002"
, "TYPE0002", "123");
	}
}