consigneetabledao.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.Consignee;

public class ConsigneeTableDAO
{
	/**
	 * 
	 * @陈磊 收货人的所有方法
	 * 
	 */
	//崔斌添加 模糊查询
	//2.5增加 自动生成编号的方法getTempId();
	private Connection conn = null;
	private PreparedStatement ps = null;
	private DBConnection DBC = null;

	public ConsigneeTableDAO()
	{
		this.DBC = new DBConnection();
	}

	/*
	 * 
	 * 查询所有的收货人
	 * 
	 */
	public List<Consignee> selectAllConsigneeMsg()
	{
		this.conn = this.DBC.getConnection();
		List<Consignee> list = new ArrayList<Consignee>();
		Consignee consignee = null;
		try
		{
			this.ps = this.conn
					.prepareStatement("select * from ConsigneeTable");
			ResultSet rs = this.ps.executeQuery();
			while (rs.next())
			{
				int id = rs.getInt("ConsigneeTableId");
				String ConsigneeId = rs.getString("ConsigneeId");
				String ConsigneeName = rs.getString("ConsigneeName");
				String ConsigneeCharge = rs.getString("ConsigneeCharge");
				String ConsigneeAddr = rs.getString("ConsigneeAddr");
				String ConsigneePhone = rs.getString("ConsigneePhone");
				String ConsigneeMobile = rs.getString("ConsigneeMobile");
				String ConsigneeFax = rs.getString("ConsigneeFax");
				String ConsigneeEmail = rs.getString("ConsigneeEmail");
				String ConsigneeIdCard = rs.getString("ConsigneeIdCard");
				consignee = new Consignee(id,ConsigneeId, ConsigneeName,
						ConsigneeCharge, ConsigneeAddr, ConsigneePhone,
						ConsigneeMobile, ConsigneeFax, ConsigneeEmail,
						ConsigneeIdCard);
				list.add(consignee);
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		this.DBC.closeConnection(conn);
		return list;
	}

	/*
	 * 
	 * 插入收货人
	 * 
	 */
	public void addConsignee(Consignee consignee)
	{
		this.conn = this.DBC.getConnection();
		ConsigneeTableDAO ctdao = new ConsigneeTableDAO();
		try
		{
			this.ps = this.conn
					.prepareStatement("insert into ConsigneeTable (ConsigneeId,ConsigneeName,ConsigneeCharge,ConsigneeAddr,ConsigneePhone,ConsigneeMobile,ConsigneeFax,ConsigneeEmail,ConsigneeIdCard)"
							+ "values(?,?,?,?,?,?,?,?,?)");
			this.ps.setString(1,ctdao.getTempId());//崔斌2.5修改
			this.ps.setString(2, consignee.getConsigneeName());
			this.ps.setString(3, consignee.getConsigneeCharge());
			this.ps.setString(4, consignee.getConsigneeAddr());
			this.ps.setString(5, consignee.getConsigneePhone());
			this.ps.setString(6, consignee.getConsigneeMobile());
			this.ps.setString(7, consignee.getConsigneeFax());
			this.ps.setString(8, consignee.getConsigneeEmail());
			this.ps.setString(9, consignee.getConsigneeIdCard());
			this.ps.execute();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		this.DBC.closeConnection(conn);
	}

	/*
	 * 
	 * 删除收货人(根据收货人的姓名)
	 * 
	 */
	public void deleteConsignee(String ConsigneeId)
	{
		this.conn = this.DBC.getConnection();
		try
		{
			this.ps = this.conn
					.prepareStatement("delete from ConsigneeTable where  ConsigneeId=? ");
			this.ps.setString(1, ConsigneeId);
			this.ps.executeUpdate();
		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		this.DBC.closeConnection(conn);
	}

	/*
	 * 
	 * 根据收货人的姓名查找收货人的所有信息
	 * 
	 */
	public Consignee selectConsigneeMsgById(String ConsigneeId)
	{
		this.conn = this.DBC.getConnection();
		Consignee consignee = null;
		try
		{
			this.ps = conn
					.prepareStatement("select * from ConsigneeTable where ConsigneeId=?");
			ps.setString(1, ConsigneeId);
			ResultSet rs = this.ps.executeQuery();
			while (rs.next())
			{
				int id = rs.getInt("ConsigneeTableId");
				String ConsigneeName = rs.getString("ConsigneeName");
				String ConsigneeCharge = rs.getString("ConsigneeCharge");
				String ConsigneeAddr = rs.getString("ConsigneeAddr");
				String ConsigneePhone = rs.getString("ConsigneePhone");
				String ConsigneeMobile = rs.getString("ConsigneeMobile");
				String ConsigneeFax = rs.getString("ConsigneeFax");
				String ConsigneeEmail = rs.getString("ConsigneeEmail");
				String ConsigneeIdCard = rs.getString("ConsigneeIdCard");
				consignee = new Consignee(id,ConsigneeId, ConsigneeName,
						ConsigneeCharge, ConsigneeAddr, ConsigneePhone,
						ConsigneeMobile, ConsigneeFax, ConsigneeEmail,
						ConsigneeIdCard);
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		this.DBC.closeConnection(conn);
		return consignee;
	}

	/*
	 * 修改
	 */
	public void updateConsignee(Consignee consignee)
	{
		this.conn = this.DBC.getConnection();
		try
		{
			this.ps = this.conn
					.prepareStatement("update ConsigneeTable set ConsigneeName=?,ConsigneeCharge=?,ConsigneeAddr=?,ConsigneePhone=?,ConsigneeMobile=?,ConsigneeFax=?,ConsigneeEmail=?,ConsigneeIdCard=? where ConsigneeId=?");
			this.ps.setString(1, consignee.getConsigneeName());
			this.ps.setString(2, consignee.getConsigneeCharge());
			this.ps.setString(3, consignee.getConsigneeAddr());
			this.ps.setString(4, consignee.getConsigneePhone());
			this.ps.setString(5, consignee.getConsigneeMobile());
			this.ps.setString(6, consignee.getConsigneeFax());
			this.ps.setString(7, consignee.getConsigneeEmail());
			this.ps.setString(8, consignee.getConsigneeIdCard());
			this.ps.setString(9, consignee.getConsigneeId());
			this.ps.executeUpdate();
		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		this.DBC.closeConnection(conn);
	}
	/*
	 * 
	 * 模糊查询所有的收货人
	 * 
	 */
	public List<Consignee> selectConsigneeMsg(String consigneeId)
	{
		this.conn = this.DBC.getConnection();
		List<Consignee> list = new ArrayList<Consignee>();
		Consignee consignee = null;
		try
		{
			this.ps = this.conn.prepareStatement("select * from ConsigneeTable where ConsigneeId like ?");
			ps.setString(1,"%"+consigneeId+"%");
			ResultSet rs = this.ps.executeQuery();
			
			while (rs.next())
			{
				int id = rs.getInt("ConsigneeTableId");
				String ConsigneeId = rs.getString("ConsigneeId");
				String ConsigneeName = rs.getString("ConsigneeName");
				String ConsigneeCharge = rs.getString("ConsigneeCharge");
				String ConsigneeAddr = rs.getString("ConsigneeAddr");
				String ConsigneePhone = rs.getString("ConsigneePhone");
				String ConsigneeMobile = rs.getString("ConsigneeMobile");
				String ConsigneeFax = rs.getString("ConsigneeFax");
				String ConsigneeEmail = rs.getString("ConsigneeEmail");
				String ConsigneeIdCard = rs.getString("ConsigneeIdCard");
				consignee = new Consignee(id,ConsigneeId, ConsigneeName,
						ConsigneeCharge, ConsigneeAddr, ConsigneePhone,
						ConsigneeMobile, ConsigneeFax, ConsigneeEmail,
						ConsigneeIdCard);
				list.add(consignee);
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		this.DBC.closeConnection(conn);
		return list;
	}
	
	/* 自动生成员工ID号 
	 * 格式：CR＋年份号＋流水号 CE20090108001
	 * */
	private String getTempId()
	{
		String temp = null;
		DBConnection db = new DBConnection();
		Connection cn = db.getConnection();
		// 获得年份，如20090108
		GetDate date = new GetDate();
		String year =date.getDate().substring(0,4)+date.getDate().substring(5,7)
			+date.getDate().substring(8,10);
		
		try
		{
			Statement st = cn.createStatement();
			ResultSet rs = st.executeQuery("select max(ConsigneeTableId) from " +
					"ConsigneeTable");
			if (rs.next())
			{
				int no = rs.getInt(1) + 1;
				// 生成员工编号
				temp =MessageFormat.format("CE{0}{1,number,000}",year,no);;
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally
		{
			db.closeConnection(cn);
		}
		return temp;
	}
	//通过页面大小获取总页数
	public int getPageNum(int pageSize) {
		DBConnection db = new DBConnection();
		Connection cn = db.getConnection();
		int num = 0;
		try {
			PreparedStatement ps = cn.prepareStatement("select count(*) from ConsignerTable");
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				num = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeConnection(cn);
		}
		if (num % pageSize == 0) {
			return num / pageSize;
		} else {
			return num / pageSize + 1;
		}
	}
	// 分页查询商品信息
	public List<Consignee> selectConsigneePage(int pageSize, int pageNum) {
		DBConnection db = new DBConnection();
		Connection cn = db.getConnection();
		List<Consignee> list = new ArrayList<Consignee>();
		Consignee Consignee = null;
		try {
			PreparedStatement ps = cn.prepareStatement("select top " + pageSize
							+ "* from ConsigneeTable where ConsigneeTableId not in(select top "
							+ pageSize * pageNum
							+ " ConsigneeTableId from ConsigneeTable order by ConsigneeTableId)order by ConsigneeTableId");
			ResultSet rs =ps.executeQuery();
			while (rs.next()) {
				int id = rs.getInt("ConsigneeTableId");
				String ConsigneeId = rs.getString("ConsigneeId");
				String ConsigneeName = rs.getString("ConsigneeName");
				String ConsigneeCharge = rs.getString("ConsigneeCharge");
				String ConsigneeAddr = rs.getString("ConsigneeAddr");
				String ConsigneePhone = rs.getString("ConsigneePhone");
				String ConsigneeMobile = rs.getString("ConsigneeMobile");
				String ConsigneeFax = rs.getString("ConsigneeFax");
				String ConsigneeEmail = rs.getString("ConsigneeEmail");
				String ConsigneeIdCard = rs.getString("ConsigneeIdCard");
				Consignee = new Consignee(id,ConsigneeId, ConsigneeName,
						ConsigneeCharge, ConsigneeAddr, ConsigneePhone,
						ConsigneeMobile, ConsigneeFax, ConsigneeEmail,
						ConsigneeIdCard);
				list.add(Consignee);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeConnection(cn);
		}
		return list;
	}
	
	
	public static void main(String[] args)
	{
		ConsigneeTableDAO ctdao  = new ConsigneeTableDAO();
		System.out.print(ctdao.getTempId());
//		List<Consignee> list = ctdao.selectConsigneeMsg("C");
//		 Iterator<Consignee> it = list.iterator();
//		 while(it.hasNext()){
//		 Consignee consignee = (Consignee) it.next();
//		 System.out.println(consignee.getConsigneeId()+consignee.getConsigneeAddr());
//		  }
	}
}