messageinfodao.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.MessageInfo;

public class MessageInfoDAO
{
	/*
	 * @陈磊 公告的所有方法
	 */
	private Connection conn = null;
	private DBConnection DBC = null;
	private PreparedStatement ps = null;

	public MessageInfoDAO(){
		this.DBC = new DBConnection();
	}
	//崔斌2.13添加自动生成ID号
	/*
	 * 查询所有的公告
	 */
	public List<MessageInfo> selectAllMessageInfo(){
		this.conn = this.DBC.getConnection();
		List<MessageInfo> list = new ArrayList<MessageInfo>();
		MessageInfo messageinfo = null;
		try{
			this.ps = this.conn
					.prepareStatement("select * from MessageInfo");
			ResultSet rs = this.ps.executeQuery();
			while (rs.next()){
				String MessageId = rs.getString("MessageId");
				String EmployeeName = rs.getString("EmployeeName");
				String MessageTitle = rs.getString("MessageTitle");
				String MessageTim = rs.getString("MessageTim");
				String MessageContent = rs.getString("MessageContent");
				messageinfo = new MessageInfo(MessageId, EmployeeName,
						MessageTitle, MessageTim, MessageContent);
				list.add(messageinfo);
			}
			this.DBC.closeResultSet(rs);
		} catch (SQLException e){
			e.printStackTrace();
		}
		this.DBC.closeConnection(conn);
		return list;
	}

	/*
	 * 增加公告
	 */
	public void addMessageInfo(MessageInfo messageinfo)
	{
		this.conn = this.DBC.getConnection();
		try
		{
			this.ps = this.conn
					.prepareStatement("insert into MessageInfoTable(MessageId,EmployeeId,MessageTitle,MessageTim,MessageContent)values(?,?,?,?,?)");
			this.ps.setString(1, this.getTempId());//2.13修改
			this.ps.setString(2, messageinfo.getEmployeeId());
			System.out.println(messageinfo.getEmployeeId());
			this.ps.setString(3, messageinfo.getMessageTitle());
			this.ps.setString(4, messageinfo.getMessageTim());
			this.ps.setString(5, messageinfo.getMessageContent());
			this.ps.execute();
		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		this.DBC.closeConnection(conn);
	}
	
	//刘海鹏添加
	public void insertMessageInfo(MessageInfo messageinfo)
	{
		this.conn = this.DBC.getConnection();
		try
		{
			this.ps = this.conn
					.prepareStatement("insert into MessageInfoTable(MessageId,EmployeeId,MessageTitle,MessageTim,MessageContent)values(?,?,?,?,?)");
			this.ps.setString(1, messageinfo.getMessageId());//2.13修改
			this.ps.setString(2, messageinfo.getEmployeeName());
			this.ps.setString(3, messageinfo.getMessageTitle());
			this.ps.setString(4, messageinfo.getMessageTim());
			this.ps.setString(5, messageinfo.getMessageContent());
			this.ps.execute();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		this.DBC.closeConnection(conn);
	}

	/*
	 * 删除公告根据Id编号删除公告
	 */
	public void deleteMessageInfoById(int id)
	{
		this.conn = this.DBC.getConnection();

		try
		{
			this.ps = this.conn
					.prepareStatement("delete from MessageInfoTable where MessageInfoTableId =?");
			this.ps.setInt(1, id);
			this.ps.executeUpdate();
		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		this.DBC.closeConnection(conn);
	}

	/*
	 * 修改公告
	 */
	public void updateMessageInfo(MessageInfo messageinfo)
	{
		this.conn = this.DBC.getConnection();
		try
		{
			this.ps = this.conn
					.prepareStatement("update MessageInfoTable set MessageTitle =?,MessageTim=?,MessageContent=? where MessageId=?");
			this.ps.setString(1, messageinfo.getMessageTitle());
			this.ps.setString(2, messageinfo.getMessageTim());
			this.ps.setString(3, messageinfo.getMessageContent());
			this.ps.setString(4, messageinfo.getMessageId());
			this.ps.executeUpdate();
		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		this.DBC.closeConnection(conn);
	}

	public String selectMaxMessageId(){
		this.conn = this.DBC.getConnection();
		String messageId = null;
		try {
			this.ps = this.conn.prepareStatement("select max(MessageId) from MessageInfoTable");
			ResultSet rs = this.ps.executeQuery();
			while(rs.next()){
				messageId = rs.getString(1);
			}
			this.DBC.closeResultSet(rs);
			this.DBC.closePrepStmt(ps);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				this.conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return messageId;
	}
	
	//按ID号查询(刘海鹏添加)
	public MessageInfo selectMessageInfoById(String messageId){
		this.conn = this.DBC.getConnection();
		MessageInfo messageinfo = null;
		try{
			this.ps = this.conn
					.prepareStatement("select * from MessageInfo where MessageId=?");
			this.ps.setString(1, messageId);
			ResultSet rs = this.ps.executeQuery();
			while (rs.next()){
				String EmployeeName = rs.getString("EmployeeName");
				String MessageTitle = rs.getString("MessageTitle");
				String MessageTim = rs.getString("MessageTim");
				String MessageContent = rs.getString("MessageContent");
				messageinfo = new MessageInfo(messageId, EmployeeName,
						MessageTitle, MessageTim, MessageContent);
			}
			this.DBC.closeResultSet(rs);
		} catch (SQLException e){
			e.printStackTrace();
		}
		this.DBC.closeConnection(conn);
		return messageinfo;
	}
	
	//按messageId删除(刘海鹏添加)
	public void deleteMessageInfoById(String messageId){
		this.conn = this.DBC.getConnection();
		try {
			this.ps = this.conn.prepareStatement("delete from MessageInfoTable where MessageId =?");
			this.ps.setString(1, messageId);
			this.ps.executeUpdate();
			this.DBC.closePrepStmt(ps);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			this.DBC.closeConnection(conn);
		}
	}
	
	//页数(刘海鹏添加)
	public int getpageNum(int pageSize) {
		this.conn = this.DBC.getConnection();
		int sum = 0;
		try {
			ps = conn.prepareStatement("select count(*) from MessageInfoTable");
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				sum = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				this.conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (sum % pageSize == 0) {
			return sum / pageSize;
		} else {
			return sum / pageSize + 1;
		}
	}
	
	//分页查找(刘海鹏添加)
	public List<MessageInfo> selectMessagePage(int pageSize, int pageNum){
		this.conn = this.DBC.getConnection();
		List<MessageInfo> list = new ArrayList<MessageInfo>();
		MessageInfo messageInfo = null;
		try {
			this.ps = this.conn.prepareStatement("select top " + pageSize
					+ "* from MessageInfo where messageId not in(select top "
					+ pageSize * pageNum
					+ " messageId from MessageInfo order by messageId)order by messageId");
			ResultSet rs = this.ps.executeQuery();
			while (rs.next()) {
				String messageId = rs.getString(1);
				String employeeName = rs.getString(2);
				String messageTitle = rs.getString(3);
				String messageTim = rs.getString(4);
				String messageContent = rs.getString(5);
				
				messageInfo = new MessageInfo(messageId, employeeName, messageTitle, messageTim, messageContent);
				list.add(messageInfo);
			}
			this.DBC.closeResultSet(rs);
			this.DBC.closePrepStmt(ps);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.DBC.closeConnection(conn);
		}
		return list;
	}
	
	/* 自动生成员工ID号 
	 * 格式：年份号+月份号＋流水号 0901080001

	 * */
	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(MessageInfoTableId) from " +
					"MessageInfoTable");
			if (rs.next())
			{
				int no = rs.getInt(1) + 1;
				// 生成员工编号
				temp =MessageFormat.format("{0}{1,number,0000}",year,no);;
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally
		{
			db.closeConnection(cn);
		}
		return temp;
	}
	
	// 测试
	public static void main(String[] args)
	{
//		MessageInfoDAO dao = new MessageInfoDAO();
//		
//		 List<MessageInfo> list = dao.selectMessagePage(10, 0);
//		 Iterator<MessageInfo> it = list.iterator();
//		 while(it.hasNext()){
//		 MessageInfo messageinfo = it.next();
//		 System.out.println(messageinfo.getMessageId()+messageinfo.getMessageContent());
//		 }
		// MessageInfo m = new
		// MessageInfo(1,"0901080001","EP0901","aa","2009-1-1
		// 12:08","aaaaaaaaaaaaaaaaaaaaa");
		//		DAO.updateMessageInfo(m);
//		System.out.println(dao.selectMaxMessageId());
	}
}