stockininfodao.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 java.text.MessageFormat;

import com.wuliu.DBConnection.DBConnection;
import com.wuliu.entity.StockInInfo;

public class StockInInfoDAO {

	/**
	 * @By void,貌似开发了大量重复的代码
	 */
	private Connection cn = null;
	private DBConnection db = null;
	private PreparedStatement ps = null;
	private ResultSet rs = null;

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

	/*
	 * 得到表中自动编号的最大值，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;
	}

	/*
	 * 查找StockInInfoTable表中的所有元素，1-9测试通过
	 */
	public List<StockInInfo> selectStockInInfoTable() {
		this.cn = this.db.getConnection();
		List<StockInInfo> list = new ArrayList<StockInInfo>();
		String sql = "select * from StockInInfoTable";
		try {
			this.ps = this.cn.prepareStatement(sql);
			this.rs = this.ps.executeQuery();
			while (this.rs.next()) {
				int StockInInfoTableId = this.rs.getInt("StockInInfoTableId");
				String StockInId = this.rs.getString("StockInId");
				String StockInDate = this.rs.getString("StockInDate");
				String ChargeId = this.rs.getString("ChargeId");
				String GoodsId = this.rs.getString("GoodsId");
				int GoodsInNumber = this.rs.getInt("GoodsInNumber");
				String GoodsTypeId = this.rs.getString("GoodsTypeId");
				String GoodsRemark = this.rs.getString("GoodsRemark");
				StockInInfo skif = new StockInInfo(StockInInfoTableId,
						StockInId, StockInDate, ChargeId, GoodsId,
						GoodsInNumber, GoodsTypeId, GoodsRemark);
				list.add(skif);
			}
		} 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-9测试通过 根据流水编码修改其他属性
	 */
	public void updateStockInInfoTable(String StockInId, String StockInDate,String ChargeId,
			String GoodsId, int GoodsInNumber, String GoodsTypeId,
			String GoodsRemark) {
		this.cn = this.db.getConnection();
		String sql = "update StockInInfoTable set StockInDate=? ,ChargeId=?,GoodsId=?,GoodsInNumber=?,"
				+ "GoodsTypeId=?,GoodsRemark=? where StockInId='"
				+ StockInId+"'";
		try {
			this.ps = this.cn.prepareStatement(sql);
			this.ps.setString(1, StockInDate);
			this.ps.setString(2, ChargeId);
			this.ps.setString(3, GoodsId);
			this.ps.setInt(4, GoodsInNumber);
			this.ps.setString(5, GoodsTypeId);
			this.ps.setString(6, GoodsRemark);
			this.ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			this.db.closePrepStmt(ps);
			this.db.closeConnection(cn);
		}

	}

	/*
	 * 给入库表中插入元素，1-9测试通过 注意其中日期编码格式
	 * 修改插入入库号2.12
	 */
	public void insertStockInInfoTable(StockInInfo skif) {
		StockInInfoDAO skid = new StockInInfoDAO();
		this.cn = this.db.getConnection();
		String sql = "insert into StockInInfoTable(StockInId,StockInDate,ChargeId,GoodsId,"
				+ "GoodsInNumber,GoodsTypeId,GoodsRemark) values(?,?,?,?,?,?,?)";
		try {
			this.ps = this.cn.prepareStatement(sql);
			this.ps.setString(1, skid.getTempId());
			this.ps.setString(2, skif.getStockInDate());
			this.ps.setString(3, skif.getChargeId());
			this.ps.setString(4, skif.getGoodsId());
			this.ps.setInt(5, skif.getGoodsInNumber());
			this.ps.setString(6, skif.getGoodsTypeId());
			this.ps.setString(7, skif.getGoodsRemark());
			this.ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			this.db.closePrepStmt(ps);
			this.db.closeConnection(cn);
		}
	}

	/*
	 * 删除入库表中指定行记录,1-9测试通过
	 * 外键约束存在（EmployeeInfoTable，GoodsInfoTable，GoodsTypeTable）
	 */
	public void deleteStockInInfoById(int id) {
		String sql = "delete from StockInInfoTable where  StockInInfoTableId=?";
		this.cn = this.db.getConnection();
		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);
		}

	}
	public void deleteStockInById(String id) {
		String sql = "delete from StockInInfoTable where  StockInId=?";
		this.cn = this.db.getConnection();
		try {
			this.ps = this.cn.prepareStatement(sql);
			this.ps.setString(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测试通过
	 * 2.5修改
	 */
	public String createStockInId() {
		String ski = "SKI-";
		int maxID = this.getLastId("StockInInfoTable", "StockInInfoTableId") + 1;
		return ski + maxID;

	}
	/*
	 * 崔版编码格式化
	 * 09.2.12测试通过
	 */
	public String getTempId(){
		String temp=null;
		int maxID = this.getLastId("StockInInfoTable", "StockInInfoTableId") + 1;
		temp=MessageFormat.format("SKI-{0,number,0000}", maxID);
		
		return temp;
		
	}
	/*
	 * 通过页面大小获取总页数,1-11测试通过
	 */
	public int getPageNum(int pageSize) {
		this.cn=this.db.getConnection();
		int num=0;
		String sql="select count(*) from StockInInfoTable";
		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;
		}
		
	}
	/*
	 * 分页处理入库信息，1-11测试通过
	 * 又是一个变态的空格
	 */
	public List<StockInInfo> selectStockInInfoPage(int pageSize, int pageNum){
		this.cn=this.db.getConnection();
		List<StockInInfo> list = new ArrayList<StockInInfo>();
		String sql="select top "+pageSize +" * from StockInInfoTable where StockInInfoTableId not in(select top "+ 
			pageSize*pageNum+" StockInInfoTableId from StockInInfoTable order by StockInInfoTableId)order by StockInInfoTableId";
		try {
			this.ps=this.cn.prepareStatement(sql);
			this.rs=this.ps.executeQuery();
			while (this.rs.next()) {
				int StockInInfoTableId = this.rs.getInt("StockInInfoTableId");
				String StockInId = this.rs.getString("StockInId");
				String StockInDate = this.rs.getString("StockInDate");
				String ChargeId = this.rs.getString("ChargeId");
				String GoodsId = this.rs.getString("GoodsId");
				int GoodsInNumber = this.rs.getInt("GoodsInNumber");
				String GoodsTypeId = this.rs.getString("GoodsTypeId");
				String GoodsRemark = this.rs.getString("GoodsRemark");
				StockInInfo skif = new StockInInfo(StockInInfoTableId,
						StockInId, StockInDate, ChargeId, GoodsId,
						GoodsInNumber, GoodsTypeId, GoodsRemark);
				list.add(skif);
			}
		} 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;
		
	}
	/*
	 * 按ID查找一条记录-1-14
	 */
	public StockInInfo selectStockInInfoById(int id){
		this.cn=this.db.getConnection();
		String sql="select * from StockInInfoTable where StockInInfoTableId="+id;
		StockInInfo skif = null;
		GoodsInfoDAO gdao = new GoodsInfoDAO();
		try {
			this.ps=this.cn.prepareStatement(sql);
			this.rs=this.ps.executeQuery();
			while(this.rs.next()){
				String StockInId = this.rs.getString("StockInId");
				String StockInDate = this.rs.getString("StockInDate");
				String ChargeId = this.rs.getString("ChargeId");
				String GoodsId = this.rs.getString("GoodsId");
				int GoodsInNumber = this.rs.getInt("GoodsInNumber");
				String GoodsTypeId = this.rs.getString("GoodsTypeId");
				String GoodsRemark = this.rs.getString("GoodsRemark");
				String goodsName=gdao.getGoodsName(GoodsId);
				 skif = new StockInInfo(id,
						StockInId, StockInDate, ChargeId, GoodsId,
						GoodsInNumber, GoodsTypeId, GoodsRemark,goodsName);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			this.db.closeResultSet(rs);
			this.db.closePrepStmt(ps);
			this.db.closeConnection(cn);
		}
		return skif;
		
	}

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		StockInInfoDAO skid = new StockInInfoDAO();
//		StockInInfo skif = new StockInInfo("SKI-0018", "2008-01-13-1-23",
//				"EP0911", "GOODS0001", 100, "TYPE0001", "商品很昂贵，运输要小心'");
		// System.out.print(skid.getLastId("StockInInfoTable",
		// "StockInInfoTableId"));
		// System.out.print(skid.createStockInId());
//		List<StockInInfo> list = new ArrayList<StockInInfo>();
		// list = skid.selectStockInInfoTable();
		// for (int i = 0; i < list.size(); i++) {
		// skif = list.get(i);
		// System.out.println(skif.getStockInInfoTableId());
		// }
		// skid.insertStockInInfoTable(skif);
//		 skid.updateStockInInfoTable("SKI-0001", "2008-09-15-2-12","EP0911", "GOODS0002", 1000,
//		 "TYPE0002", "你好123你基本原理");
		// int id=2;
		// skid.deleteStockInInfoById(id);
		//System.out.println(skid.getPageNum(4));
//		StockInInfo skif = new StockInInfo();
//		list=skid.selectStockInInfoPage(4,3);
//		for (int i = 0; i < list.size(); i++) {
//			 skif = list.get(i);
//			 System.out.println(skif.getStockInInfoTableId());
//			 }
		System.out.println(skid.getTempId());
		skid.deleteStockInById("SKI-0018");
	}

}