goodsinfodao.java

基于j2ee的物流软件

package com.wuliu.dao;

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

import com.wuliu.DBConnection.DBConnection;
import com.wuliu.entity.GoodsInfo;

/**
 * @author 刘海鹏
 * @version 商品信息DAO
 */
//崔斌2.5添加自动生成ID号方法
public class GoodsInfoDAO {
	private Connection conn = null;
	private PreparedStatement ps = null;
	private DBConnection dBConnection = null;

	public GoodsInfoDAO() {
       this.dBConnection = new DBConnection();
	}

	// 按Id查询某条商品信息
	public GoodsInfo selectGoodsInfoById(String GoodsId) {
		GoodsInfo goodsInfo = null;
		this.conn = this.dBConnection.getConnection();
		try {
			this.ps = this.conn
					.prepareStatement("select * from GoodsInfoTable where GoodsId=?");
			this.ps.setString(1, GoodsId);
			ResultSet rs = this.ps.executeQuery();
			while (rs.next()) {
				int id = rs.getInt(1);
				String goodsId = rs.getString(2);
				String goodsName = rs.getString(3);
				String goodsTypeId = rs.getString(4);
				String goodsConsignerId = rs.getString(5);
				String goodsConsigneeId = rs.getString(6);
				String goodsStart = rs.getString(7);
				String goodsAim = rs.getString(8);
				String goodsABC = rs.getString(9);
				String goodsCity = rs.getString(10);
				String goodsStockId = rs.getString(11);
				String goodsStockRegionId = rs.getString(12);
				double goodsPrice = rs.getDouble(13);
				String goodsNumber = rs.getString(14);
				String goodsUnit = rs.getString(15);
				String goodsState = rs.getString(16);
				goodsInfo = new GoodsInfo(id,goodsId, goodsName, goodsTypeId,
						goodsConsignerId, goodsConsigneeId, goodsStart,
						goodsAim, goodsABC, goodsCity, goodsStockId,
						goodsStockRegionId, goodsPrice, goodsNumber, goodsUnit,
						goodsState);
			}
			this.dBConnection.closeResultSet(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			this.dBConnection.closePrepStmt(ps);
			try {
				this.conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return goodsInfo;
	}

	// 分页查询商品信息
	public List<GoodsInfo> selectGoodsInfoPage(int pageSize, int pageNum) {
		List<GoodsInfo> list = new ArrayList<GoodsInfo>();
		GoodsInfo goodsInfo = null;
		this.conn = this.dBConnection.getConnection();
		try {
			this.ps = this.conn
					.prepareStatement("select top " + pageSize
							+ "* from GoodsInfoTable where GoodsInfoTableId not in(select top "
							+ pageSize * pageNum
							+ " GoodsInfoTableId from GoodsInfoTable order by GoodsInfoTableId)order by GoodsInfoTableId");
			ResultSet rs = this.ps.executeQuery();
			while (rs.next()) {
				int id = rs.getInt(1);
				String goodsId = rs.getString(2);
				String goodsName = rs.getString(3);
				String goodsTypeId = rs.getString(4);
				String goodsConsignerId = rs.getString(5);
				String goodsConsigneeId = rs.getString(6);
				String goodsStart = rs.getString(7);
				String goodsAim = rs.getString(8);
				String goodsABC = rs.getString(9);
				String goodsCity = rs.getString(10);
				String goodsStockId = rs.getString(11);
				String goodsStockRegionId = rs.getString(12);
				double goodsPrice = rs.getDouble(13);
				String goodsNumber = rs.getString(14);
				String goodsUnit = rs.getString(15);
				String goodsState = rs.getString(16);
				goodsInfo = new GoodsInfo(id, goodsId, goodsName, goodsTypeId,
						goodsConsignerId, goodsConsigneeId, goodsStart,
						goodsAim, goodsABC, goodsCity, goodsStockId,
						goodsStockRegionId, goodsPrice, goodsNumber, goodsUnit,
						goodsState);
				list.add(goodsInfo);
			}
			this.dBConnection.closeResultSet(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			this.dBConnection.closePrepStmt(ps);
			try {
				this.conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	//按所在仓库编号(goodsStockId)查询商品信息表的Id
	public List<Integer> selectGoodsInfoByGoodsStockId(String goodsStockId) {
		List<Integer> list = new ArrayList<Integer>();
		int goodsInfoId;
		this.conn = this.dBConnection.getConnection();
		try {
			this.ps = this.conn
					.prepareStatement("select * from GoodsInfoTable where goodsStockId=?");
			this.ps.setString(1, goodsStockId);
			ResultSet rs = this.ps.executeQuery();
			while (rs.next()) {
				goodsInfoId = rs.getInt(1);
				list.add(goodsInfoId);
			}
			this.dBConnection.closeResultSet(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			this.dBConnection.closePrepStmt(ps);
			try {
				this.conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}
	
	// 插入一条商品信息
	public void insertGoodsInfo(GoodsInfo goodsInfo) {
		this.conn = this.dBConnection.getConnection();
		GoodsInfoDAO gidao = new GoodsInfoDAO();
		try {
			this.ps = this.conn.prepareStatement("insert into GoodsInfoTable(GoodsId, GoodsName,"
							+ "GoodsTypeId, GoodsConsignerId, GoodsConsigneeId, GoodsStart,"
							+ "GoodsAim, GoodsABC, GoodsCity, GoodsStockId, GoodsStockRegionId,"
							+ "GoodsPrice, GoodsNumber, GoodsUnit, GoodsState)"
							+ "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
			this.ps.setString(1,gidao.getTempId());//2.5修改
			this.ps.setString(2, goodsInfo.getGoodsName());
			this.ps.setString(3, goodsInfo.getGoodsTypeId());
			this.ps.setString(4, goodsInfo.getGoodsConsignerId());
			this.ps.setString(5, goodsInfo.getGoodsConsigneeId());
			this.ps.setString(6, goodsInfo.getGoodsStart());
			this.ps.setString(7, goodsInfo.getGoodsAim());
			this.ps.setString(8, goodsInfo.getGoodsABC());
			this.ps.setString(9, goodsInfo.getGoodsCity());
			this.ps.setString(10, goodsInfo.getGoodsStockId());
			this.ps.setString(11, goodsInfo.getGoodsStockRegionId());
			this.ps.setDouble(12, goodsInfo.getGoodsPrice());
			this.ps.setString(13, goodsInfo.getGoodsNumber());
			this.ps.setString(14, goodsInfo.getGoodsUnit());
			this.ps.setString(15, goodsInfo.getGoodsState());
			this.ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			this.dBConnection.closePrepStmt(ps);
			try {
				this.conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	// 通过Id删除某条商品信息
	public void deleteGoodsInfoById(int id) {
		this.conn = this.dBConnection.getConnection();
		try {
			this.ps = this.conn
					.prepareStatement("delete from GoodsInfoTable where GoodsInfoTableId=?");
			this.ps.setInt(1, id);
			this.ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			this.dBConnection.closePrepStmt(ps);
			try {
				this.conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	// 修改某条商品信息
	//2.5崔斌修改，不能修改物品ID号
	public void updateGoodsInfo(GoodsInfo goodsInfo) {
		this.conn = this.dBConnection.getConnection();
		try {
			this.ps = this.conn
					.prepareStatement("update GoodsInfoTable set GoodsName=?,"
							+ " GoodsTypeId=?, GoodsConsignerId=?, GoodsConsigneeId=?,"
							+ " GoodsStart=?, GoodsAim=?, GoodsABC=?, GoodsCity=?,"
							+ " GoodsStockId=?, GoodsStockRegionId=?, GoodsPrice=?,"
							+ " GoodsNumber=?, GoodsUnit=?, GoodsState=? where GoodsInfoTableId=?");
			this.ps.setString(1, goodsInfo.getGoodsName());
			this.ps.setString(2, goodsInfo.getGoodsTypeId());
			this.ps.setString(3, goodsInfo.getGoodsConsignerId());
			this.ps.setString(4, goodsInfo.getGoodsConsigneeId());
			this.ps.setString(5, goodsInfo.getGoodsStart());
			this.ps.setString(6, goodsInfo.getGoodsAim());
			this.ps.setString(7, goodsInfo.getGoodsABC());
			this.ps.setString(8, goodsInfo.getGoodsCity());
			this.ps.setString(9, goodsInfo.getGoodsStockId());
			this.ps.setString(10, goodsInfo.getGoodsStockRegionId());
			this.ps.setDouble(11, goodsInfo.getGoodsPrice());
			this.ps.setString(12, goodsInfo.getGoodsNumber());
			this.ps.setString(13, goodsInfo.getGoodsUnit());
			this.ps.setString(14, goodsInfo.getGoodsState());
			this.ps.setInt(15, goodsInfo.getId());
			this.ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			this.dBConnection.closePrepStmt(ps);
			try {
				this.conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	//通过页面大小获取总页数
	public int getPageNum(int pageSize) {
		int num = 0;
		this.conn = this.dBConnection.getConnection();
		try {
			this.ps = this.conn.prepareStatement("select count(*) from GoodsInfoTable");
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				num = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				this.dBConnection.closePrepStmt(ps);
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (num % pageSize == 0) {
			return num / pageSize;
		} else {
			return num / pageSize + 1;
		}
	}
	/*
	 * 查找现有商品所有ID
	 */
	public List<GoodsInfo> selectGoodsId(){
		List<GoodsInfo> list = new ArrayList<GoodsInfo>();
		this.conn=this.dBConnection.getConnection();
		String sql="select * from GoodsInfoTable";
		try {
			this.ps=this.conn.prepareStatement(sql);
			ResultSet rs = this.ps.executeQuery();
			while (rs.next()) {
				int id = rs.getInt(1);
				String goodsId = rs.getString(2);
				String goodsName = rs.getString(3);
				String goodsTypeId = rs.getString(4);
				String goodsConsignerId = rs.getString(5);
				String goodsConsigneeId = rs.getString(6);
				String goodsStart = rs.getString(7);
				String goodsAim = rs.getString(8);
				String goodsABC = rs.getString(9);
				String goodsCity = rs.getString(10);
				String goodsStockId = rs.getString(11);
				String goodsStockRegionId = rs.getString(12);
				double goodsPrice = rs.getDouble(13);
				String goodsNumber = rs.getString(14);
				String goodsUnit = rs.getString(15);
				String goodsState = rs.getString(16);
				GoodsInfo goodsInfo = new GoodsInfo(id,goodsId, goodsName, goodsTypeId,
						goodsConsignerId, goodsConsigneeId, goodsStart,
						goodsAim, goodsABC, goodsCity, goodsStockId,
						goodsStockRegionId, goodsPrice, goodsNumber, goodsUnit,
						goodsState);
				list.add(goodsInfo);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			this.dBConnection.closePrepStmt(ps);
			try {
				this.conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}
	
	/* 自动生成ID号 2.5增加
	 * 格式：GOODS+流水号 GOODS0001
	 * */
	private String getTempId()
	{
		String temp = null;
		this.conn=this.dBConnection.getConnection();
		try
		{
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery("select max(GoodsInfoTableId) from " +
					"GoodsInfoTable");
			if (rs.next())
			{
				int no = rs.getInt(1) + 1;
				// 生成编号
				temp =MessageFormat.format("GOODS{0,number,0000}",no);;
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally{
			this.dBConnection.closePrepStmt(ps);
			try {
				this.conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return temp;
	}
	/*
	 * 刘军添加获取商品名称方法2.19
	 */
	public String getGoodsName(String goodsId){
		this.conn=this.dBConnection.getConnection();
		String sql="select GoodsName from GoodsInfoTable where GoodsId = '"+goodsId+"'";
		String goodsName=null;
		try {
			this.ps=this.conn.prepareStatement(sql);
			ResultSet rs=this.ps.executeQuery();
			while(rs.next()){
			   goodsName=rs.getString(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			this.dBConnection.closePrepStmt(ps);
			this.dBConnection.closeConnection(conn);
		}
		
		return goodsName;
		
	}
	
	public static void main(String[] args){
		GoodsInfoDAO gdao = new GoodsInfoDAO();
//		GoodsInfo goods = new GoodsInfo("电脑桌","TYPE0001","CR20090108005","CE20090108001","北京","北京","A","西安","BJ001","BJ001-01",100,"10","张","入库");
//		gdao.insertGoodsInfo(goods);
        System.out.print(gdao.getGoodsName("GOODS0001"));
	}
}