consignertabledao.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.Consigner;
import com.wuliu.entity.EmployeeInfo;

public class ConsignerTableDAO
{
	/**
	 * 
	 * @陈磊
	 * 
	 * 发货人的所有方法
	 * 
	 */
	//崔斌添加模糊查询
	//2.5日添加自动生成ID
	private Connection conn = null;
	private DBConnection DBC = null;
	private PreparedStatement ps = null;

	// 数据库连接
	public ConsignerTableDAO()
	{
		this.DBC = new DBConnection();
	}

	/*
	 * 
	 * 查询所有的发货人
	 * 
	 */
	public List<Consigner> selectAllConsignerMsg()
	{
		this.conn = this.DBC.getConnection();
		List<Consigner> list = new ArrayList<Consigner>();
		Consigner consigner = null;
		try
		{
			this.ps = this.conn
					.prepareStatement("select * from ConsignerTable");
			ResultSet rs = this.ps.executeQuery();
			while (rs.next())
			{
				int id = rs.getInt("ConsignerTableId");
				String ConsignerId = rs.getString("ConsignerId");
				String ConsignerName = rs.getString("ConsignerName");
				String ConsignerCharge = rs.getString("ConsignerCharge");
				String ConsignerAddr = rs.getString("ConsignerAddr");
				String ConsignerPhone = rs.getString("ConsignerPhone");
				String ConsignerMobile = rs.getString("ConsignerMobile");
				String ConsignerFax = rs.getString("ConsignerFax");
				String ConsignerEmail = rs.getString("ConsignerEmail");
				String ConsignerIdCard = rs.getString("ConsignerIdCard");
				consigner = new Consigner(id,ConsignerId, ConsignerName,
						ConsignerCharge, ConsignerAddr, ConsignerPhone,
						ConsignerMobile, ConsignerFax, ConsignerEmail,
						ConsignerIdCard);
				list.add(consigner);
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally{
			this.DBC.closeConnection(conn);
		}
		return list;
	}
	
	/* 自动生成员工ID号 
	 * 格式：CR＋年份号＋流水号 CR20090108001
	 * */
	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(ConsignerTableId) from " +
					"ConsignerTable");
			if (rs.next())
			{
				int no = rs.getInt(1) + 1;
				// 生成员工编号
				temp =MessageFormat.format("CR{0}{1,number,000}",year,no);;
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally
		{
			db.closeConnection(cn);
		}
		return temp;
	}
	/*
	 * 增加发货人
	 */
	public void addConsigner(Consigner consigner)
	{
		this.conn = this.DBC.getConnection();
		ConsignerTableDAO ctdao = new ConsignerTableDAO();
		try
		{
			this.ps = this.conn
					.prepareStatement("insert into ConsignerTable (ConsignerId,ConsignerName,ConsignerCharge,ConsignerAddr,ConsignerPhone,ConsignerMobile,ConsignerFax,ConsignerEmail,ConsignerIdCard)"
							+ "values(?,?,?,?,?,?,?,?,?)");
			this.ps.setString(1,ctdao.getTempId());//2.5修改
			this.ps.setString(2, consigner.getConsignerName());
			this.ps.setString(3, consigner.getConsignerCharge());
			this.ps.setString(4, consigner.getConsignerAddr());
			this.ps.setString(5, consigner.getConsignerPhone());
			this.ps.setString(6, consigner.getConsignerMobile());
			this.ps.setString(7, consigner.getConsignerFax());
			this.ps.setString(8, consigner.getConsignerEmail());
			this.ps.setString(9, consigner.getConsignerIdCard());
			this.ps.execute();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally{
			this.DBC.closeConnection(conn);
		}
		
	}

	/*
	 * 
	 * 删除发货人
	 * 
	 */
	public void deleteConsigner(String ConsignerId)
	{
		this.conn = this.DBC.getConnection();
		try
		{
			this.ps = this.conn
					.prepareStatement("delete from ConsignerTable where  ConsignerId=? ");
			this.ps.setString(1, ConsignerId);
			this.ps.executeUpdate();
		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			this.DBC.closeConnection(conn);
		}
		
	}

	/*
	 * 
	 * 根据发货人的姓名查找发货人的所有信息
	 * 
	 */
	public Consigner selectConsignerMsgById(String ConsignerId)
	{
		this.conn = this.DBC.getConnection();
		Consigner consigner = null;
		try
		{
			this.ps = conn.prepareStatement("select * from ConsignerTable where ConsignerId=?");
			ps.setString(1, ConsignerId);
			ResultSet rs = this.ps.executeQuery();
			while (rs.next())
			{
				int id = rs.getInt("ConsignerTableId");
				String ConsignerName = rs.getString("ConsignerName");
				String ConsignerCharge = rs.getString("ConsignerCharge");
				String ConsignerAddr = rs.getString("ConsignerAddr");
				String ConsignerPhone = rs.getString("ConsignerPhone");
				String ConsignerMobile = rs.getString("ConsignerMobile");
				String ConsignerFax = rs.getString("ConsignerFax");
				String ConsignerEmail = rs.getString("ConsignerEmail");
				String ConsignerIdCard = rs.getString("ConsignerIdCard");
				consigner = new Consigner(id,ConsignerId, ConsignerName,
						ConsignerCharge, ConsignerAddr, ConsignerPhone,
						ConsignerMobile, ConsignerFax, ConsignerEmail,
						ConsignerIdCard);
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally{
			this.DBC.closeConnection(conn);
		}
		
		return consigner;
	}

	/*
	 * 修改
	 */
	public void updateConsigner(Consigner consigner)
	{
		this.conn = this.DBC.getConnection();
		try
		{
			this.ps = this.conn
					.prepareStatement("update ConsignerTable set ConsignerName=?,ConsignerCharge=?,ConsignerAddr=?,ConsignerPhone=?,ConsignerMobile=?,ConsignerFax=?,ConsignerEmail=?,ConsignerIdCard=? where ConsignerId=?");
			this.ps.setString(1, consigner.getConsignerName());
			this.ps.setString(2, consigner.getConsignerCharge());
			this.ps.setString(3, consigner.getConsignerAddr());
			this.ps.setString(4, consigner.getConsignerPhone());
			this.ps.setString(5, consigner.getConsignerMobile());
			this.ps.setString(6, consigner.getConsignerFax());
			this.ps.setString(7, consigner.getConsignerEmail());
			this.ps.setString(8, consigner.getConsignerIdCard());
			this.ps.setString(9, consigner.getConsignerId());
			this.ps.executeUpdate();
		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			this.DBC.closeConnection(conn);
		}
		
	}
	
	/*
	 * 
	 * 模糊查询所有的发货人
	 * 
	 */
	public List<Consigner> selectConsignerMsg(String consignerId)
	{
		this.conn = this.DBC.getConnection();
		List<Consigner> list = new ArrayList<Consigner>();
		Consigner consigner = null;
		try
		{
			this.ps = this.conn
					.prepareStatement("select * from ConsignerTable where ConsignerId like ?");
			ps.setString(1,"%"+consignerId+"%");
			ResultSet rs = this.ps.executeQuery();
			while (rs.next())
			{
				int id = rs.getInt("ConsignerTableId");
				String ConsignerId = rs.getString("ConsignerId");
				String ConsignerName = rs.getString("ConsignerName");
				String ConsignerCharge = rs.getString("ConsignerCharge");
				String ConsignerAddr = rs.getString("ConsignerAddr");
				String ConsignerPhone = rs.getString("ConsignerPhone");
				String ConsignerMobile = rs.getString("ConsignerMobile");
				String ConsignerFax = rs.getString("ConsignerFax");
				String ConsignerEmail = rs.getString("ConsignerEmail");
				String ConsignerIdCard = rs.getString("ConsignerIdCard");
				consigner = new Consigner(id,ConsignerId, ConsignerName,
						ConsignerCharge, ConsignerAddr, ConsignerPhone,
						ConsignerMobile, ConsignerFax, ConsignerEmail,
						ConsignerIdCard);
				list.add(consigner);
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally{
			this.DBC.closeConnection(conn);
		}
		return list;
	}
	
	//通过页面大小获取总页数
	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<Consigner> selectConsignerPage(int pageSize, int pageNum) {
		DBConnection db = new DBConnection();
		Connection cn = db.getConnection();
		List<Consigner> list = new ArrayList<Consigner>();
		Consigner consigner = null;
		try {
			PreparedStatement ps = cn.prepareStatement("select top " + pageSize
							+ "* from ConsignerTable where ConsignerTableId not in(select top "
							+ pageSize * pageNum
							+ " ConsignerTableId from ConsignerTable order by ConsignerTableId)order by ConsignerTableId");
			ResultSet rs =ps.executeQuery();
			while (rs.next()) {
				int id = rs.getInt("ConsignerTableId");
				String ConsignerId = rs.getString("ConsignerId");
				String ConsignerName = rs.getString("ConsignerName");
				String ConsignerCharge = rs.getString("ConsignerCharge");
				String ConsignerAddr = rs.getString("ConsignerAddr");
				String ConsignerPhone = rs.getString("ConsignerPhone");
				String ConsignerMobile = rs.getString("ConsignerMobile");
				String ConsignerFax = rs.getString("ConsignerFax");
				String ConsignerEmail = rs.getString("ConsignerEmail");
				String ConsignerIdCard = rs.getString("ConsignerIdCard");
				consigner = new Consigner(id,ConsignerId, ConsignerName,
						ConsignerCharge, ConsignerAddr, ConsignerPhone,
						ConsignerMobile, ConsignerFax, ConsignerEmail,
						ConsignerIdCard);
				list.add(consigner);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeConnection(cn);
		}
		return list;
	}

	/*
	 * 测试
	 */
	public static void main(String[] args)
	{
		ConsignerTableDAO ctdao = new ConsignerTableDAO();
		System.out.print(ctdao.getTempId());
//		ConsignerTableDAO dao = new ConsignerTableDAO();
//		 Consigner aa = dao.selectConsignerMsgById("CR20090108004");
//		 System.out.print("----"+aa.getConsignerAddr());
		// System.out.println(aa.getConsignerAddr());
//		 List<Consigner> list = dao.selectConsignerMsg("C");
//		 Iterator<Consigner> it = list.iterator();
//		 while(it.hasNext()){
//		 Consigner consigner = (Consigner) it.next();
//		 System.out.println(consigner.getConsignerId()+consigner.getConsignerAddr());
//		  }
		// Consigner c = new
		// Consigner(1,"CR20090108001","天宇科技","陈志朋","西安市商业大街13号","029-33254469","13636795426","029-312346669","[e-mail]","610404198505100051");
		// dao.updateConsigner(c);
	}
}