messinfodao.java

实现办公自动化系统

package com.oa.struts.perOffice.modle;

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

import com.oa.struts.vo.DocumentInfo;
import com.oa.struts.vo.MessInfo;
import com.oa.struts.vo.messageInfo;
import com.oa.util.DBConn;

public class MessInfoDAO {
	
	private DBConn db = new DBConn();
	private Connection conn = null; 
	private Statement st = null;
	private PreparedStatement ps = null;
	private ResultSet rs = null;
	public MessInfoDAO() {
		super();
		// TODO Auto-generated constructor stub
	}
//插入新消息	
	
	public int getTotulRows(String title,int accepter,int sender,int documentId) //传来输入的查询条件
	{   
		int i = 0;
		try {
			conn=db.getConnection();
			conn.setAutoCommit(false);
			String sql = "select count(*) totulrows from tb_mesInfo  d where sender="+sender+"and accepter="+accepter+"and documentId="+documentId+"and 1=1";
			if(title!=null&&!title.equals("")) 
			{
				sql += "and d.title like ?";
				ps = conn.prepareStatement(sql);
				
				ps.setString(1,"%"+title+"%");
			}
			
			else
			{
				ps = conn.prepareStatement(sql);
			}
            rs = ps.executeQuery();
            
			if(rs.next())
			{
				i = rs.getInt("totulrows");
			}
		    conn.commit();
		} catch (SQLException e) {
			
			try {
				if(conn!=null)
				{
					conn.rollback();//事务回滚
				}
			} catch (SQLException e1) {
				
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		finally
		{
			try {
				if(conn!=null)
				{
					conn.close();
				}
				if(ps!=null)
				{
					ps.close();
				}
				if(rs!=null)
				{
					rs.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return i;
	}
	public int delMess(int  messId)
	{
		int i = 0;
		try {
			conn = db.getConnection();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement("delete from tb_mesInfo where id=?");
			ps.setInt(1, messId);
			i = ps.executeUpdate();
		    conn.commit();
		} catch (SQLException e) {
			
			try {
				if(conn!=null)
				{
					conn.rollback();//事务回滚
				}
			} catch (SQLException e1) {
				
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		finally
		{
			try {
				if(conn!=null)
				{
					conn.close();
				}
				if(ps!=null)
				{
					ps.close();
				}
				if(rs!=null)
				{
					rs.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return i;
	}

	public List searchMess(String title,int accepter,int sender,int documentId,int startRow,int endRow)
	{     
		int accepterID;
		List<messageInfo> lt = new ArrayList<messageInfo>();
		try {
			conn=db.getConnection();
			conn.setAutoCommit(false);
			String sql = "select d.*,rownum rn from tb_mesInfo d  where accepter="+accepter+"and sender="+sender+"and documentId="+documentId+"and 1=1";
			if(title!=null&&!title.equals(""))
			{
				sql += "and d.title like ?";
			}
			
			
			sql = "select * from ("+ sql+" and rownum<="+endRow +") t where rn>="+startRow;
			
			System.out.println("sql======="+sql);
			ps = conn.prepareStatement(sql);
			if(title!=null&&!title.equals(""))
			{
				ps.setString(1,"%"+title+"%");
			}
			rs = ps.executeQuery();
			while(rs.next())
			{
				messageInfo  mi = new  messageInfo( );
				int  ID=rs.getInt("ID");
				int accepterId=rs.getInt("accepter");
				int snederId=rs.getInt("sender");
				String Title=rs.getString("title");
				int documentid=rs.getInt("documentId");
				String createTime=rs.getString("createTime");
				String mesContent=rs.getString("content");
				System.out.print("**************"+createTime);
				mi.setTitle(Title);
			  mi.setId(ID);
			  mi.setAccepter(accepterId);
			  mi.setSender(snederId);
			  mi.setCreatetime(createTime);
			  mi.setContent(mesContent);
			  
			  
				
				lt.add(mi);
			}
		    conn.commit();
		} catch (SQLException e) {
			
			try {
				if(conn!=null)
				{
					conn.rollback();//事务回滚
				}
			} catch (SQLException e1) {
				
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		finally
		{
			try {
				if(conn!=null)
				{
					conn.close();
				}
				if(ps!=null)
				{
					ps.close();
				}
				if(rs!=null)
				{
					rs.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return lt;
	}
	
	
	public int getIsRead(int id)
	{
		
		int  ID=0;
		String sql="select  ISREAD from tb_mesInfo where id="+id;
		try
		{	
			conn=db.getConnection();
			conn.setAutoCommit(false);
			ps=conn.prepareStatement(sql);
			rs = ps.executeQuery();
			ID=rs.getInt("ISREAD");
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				if(conn!=null)
				{
					conn.close();
				}
				if(ps!=null)
				{
					ps.close();
				}
			}
			catch(SQLException e)
			{
				e.printStackTrace();
			}
		}
		return ID;
		
		
		
	}
	public String  getMessTitle(int messId)
	{
		String title=null;
		
		try
		{
			conn=db.getConnection();
			conn.setAutoCommit(false);
			String sql="select  title  from tb_mesinfo  where id="+messId;
			st=conn.createStatement();
			rs=st.executeQuery(sql);
			while(rs.next())
			{
				title=rs.getString("title");
			}
			if(conn!=null){conn.close();}
			if(st!=null){st.close();}
			if(rs!=null){rs.close();}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return title;
	}
	public boolean insertMessage(messageInfo meInfo)
	{
		boolean flag=false;

		String sql="insert into tb_mesInfo values (MESINFO_SEQ.nextval,?,?,?,?,?,?,?)";
		try
		{	
			conn=db.getConnection();
			conn.setAutoCommit(false);
			ps=conn.prepareStatement(sql);
			//System.out.println("sender="+docInfo.getSender());
			//System.out.println("accepter="+docInfo.getAccepter());
			System.out.println(meInfo.getCreatetime());
			int id=meInfo.getId();
			System.out.println("sssssss"+id);
			ps.setInt(1,meInfo.getSender());
			ps.setInt(2, meInfo.getAccepter());
			ps.setString(3,meInfo.getTitle());
			ps.setString(4, meInfo.getCreatetime());
			ps.setString(5, meInfo.getContent());
			ps.setInt(6, meInfo.getIsRead());
			ps.setInt(7, meInfo.getDocumentId());
			
			System.out.println("sql===========");
			int i=ps.executeUpdate();
			if(i>0)
			{
				flag=true;
			}
		    conn.commit();
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				if(conn!=null)
				{
					conn.close();
				}
				if(ps!=null)
				{
					ps.close();
				}
			}
			catch(SQLException e)
			{
				e.printStackTrace();
			}
		}
		return flag;
	}
	public String  getMessContent(int messId)
	{
		String content=null;
		db=new DBConn();
		try
		{
			conn=db.getConnection();
			conn.setAutoCommit(false);
			String sql="select  content from tb_mesinfo  where id="+messId;
			st=conn.createStatement();
			rs=st.executeQuery(sql);
			while(rs.next())
			{
				content=rs.getString("content");
			}
			if(conn!=null){conn.close();}
			if(st!=null){st.close();}
			if(rs!=null){rs.close();}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return content;
	}
	
	/*===========================================================================================================*/	
//查询所有留言
	public List<MessInfo> displayAll(int accepter)
	{
		
		StringBuffer stf=new StringBuffer();
		List<MessInfo> lt=new ArrayList<MessInfo>();
		db=new DBConn();
		try
		{
			String sql="select * from tb_mesinfo where accepter=?";
			conn=db.getConnection();
			conn.setAutoCommit(false);
			ps=conn.prepareStatement(sql);
			ps.setInt(1,accepter);
		    rs=ps.executeQuery();
			int i=0;
			while(rs.next())
			{
				MessInfo messInfo=new MessInfo();
				messInfo.setId(rs.getInt("id"));
				messInfo.setSenderID(rs.getInt("sender"));
				messInfo.setTitle(rs.getString("TITLE"));
				messInfo.setTime(rs.getString("CREATETIME"));
				messInfo.setContent(rs.getString("CONTENT"));
				messInfo.setIsRead(rs.getInt("ISREAD"));
				lt.add(i++, messInfo);
			}
			
			if(rs!=null) rs.close();
			if(ps!=null) ps.close();
			if(conn!=null) conn.close();		
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}	
		return lt;
	}
	
	
	//插入新消息
	public boolean insertMessInfo(MessInfo info)
	{
		boolean result=false;
		try
		{
			String sql="insert into tb_mesinfo values(MES_SEQ.nextval,?,?,?,?,?,?,0,?)";
			db=new DBConn();
			conn=db.getConnection();
			ps=conn.prepareStatement(sql);
			ps.setInt(1,info.getSenderID());
			ps.setInt(2,info.getAccepterID());
			ps.setString(3,info.getTitle());
			ps.setString(4,info.getTime());
			ps.setString(5,info.getContent());
		    ps.setInt(6,info.getIsRead());
		    ps.setInt(7, info.getIsSend());
			int i=ps.executeUpdate();
			//System.out.println("i="+i);
			if(i>0)
			{
				result=true;
			}
				
			if(ps!=null) ps.close();
			if(conn!=null) conn.close();		
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return result;
   }
	
	//根据ID来删除消息
	public boolean deleteMessage(int id)
	{
		boolean result=false;
		try
		{
			String sql="delete from tb_mesinfo where id=?";
			db=new DBConn();
			conn=db.getConnection();
			ps=conn.prepareStatement(sql);
			ps.setInt(1,id);
			int i=ps.executeUpdate();
			if(i>0)
			{
				result=true;
			}
				
			if(ps!=null) ps.close();
			if(conn!=null) conn.close();		
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return result;
	}
	
//	查询已发送、未发送保存在草稿箱中的消息
   public List seachSended(int sender,int isSend)
	{
		
		List<MessInfo> lt=new ArrayList<MessInfo>();
		try
		{
			String sql="select * from tb_mesinfo where sender=? and issend=?";
			db=new DBConn();
			conn=db.getConnection();
			ps=conn.prepareStatement(sql);
			ps.setInt(1, sender);
			ps.setInt(2, isSend);
			rs=ps.executeQuery();
			int i=0;
			while(rs.next())
			{
				MessInfo messInfo=new MessInfo();
				messInfo.setId(rs.getInt("id"));
				messInfo.setAccepterID(rs.getInt("ACCEPTER"));
				messInfo.setTitle(rs.getString("TITLE"));
				messInfo.setTime(rs.getString("CREATETIME"));
				messInfo.setContent(rs.getString("CONTENT"));
				messInfo.setIsRead(rs.getInt("ISREAD"));
				lt.add(i++, messInfo);
			}	
			if(rs!=null) rs.close();
			if(ps!=null) ps.close();
			if(conn!=null) conn.close();	
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}	
		return lt;	

	}
//	查询未读留言
   public String seachoNotRead()
	{
		
		StringBuffer stf=new StringBuffer();
		try
		{
			String sql="select * from tb_mesinfo where isRead=0";
			db=new DBConn();
			conn=db.getConnection();
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			
			while(rs.next())
			{
				int id=rs.getInt("id");
				String title=rs.getString("title");
				int sender1=rs.getInt("sender");
				
				 stf.append("<tr>");
			     stf.append("<td>"+title+"</td>");
			     stf.append("<td>"+sender1+"</td>");
			     stf.append("<td><a href=/F-YOA/messageControl.do?action=del&id="+id+">删除</a></td>");
				 stf.append("</tr>");	
			     System.out.print(title);
			     System.out.print(sender1);
				
			}
	        stf.append("</table>");
			
			
			System.out.println("stf="+stf);
			
			if(rs!=null) rs.close();
			if(ps!=null) ps.close();
			if(conn!=null) conn.close();	
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}	
		return stf.toString();	

	}

   public List seachRecieved(int accepter)
	{
		
		List<MessInfo> lt=new ArrayList<MessInfo>();
		try
		{
			String sql="select * from tb_mesinfo where accepter=?";
			db=new DBConn();
			conn=db.getConnection();
			ps=conn.prepareStatement(sql);
			ps.setInt(1, accepter);
			rs=ps.executeQuery();
			int i=0;
			while(rs.next())
			{
				MessInfo messInfo=new MessInfo();
				messInfo.setId(rs.getInt("id"));
				messInfo.setSenderID(rs.getInt("sender"));
				messInfo.setAccepterID(rs.getInt("ACCEPTER"));
				messInfo.setTitle(rs.getString("TITLE"));
				messInfo.setTime(rs.getString("CREATETIME"));
				messInfo.setContent(rs.getString("CONTENT"));
				messInfo.setIsRead(rs.getInt("ISREAD"));
				lt.add(i++, messInfo);
			}	
			if(rs!=null) rs.close();
			if(ps!=null) ps.close();
			if(conn!=null) conn.close();	
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}	
		return lt;	

	}

   public MessInfo getMessInfo(int id)
   {
	   MessInfo messInfo=new MessInfo();
		try
		{
			String sql="select * from tb_mesinfo where id=?";
			db=new DBConn();
			conn=db.getConnection();
			ps=conn.prepareStatement(sql);
			ps.setInt(1,id);
			rs=ps.executeQuery();
			while(rs.next())
			{
				messInfo.setId(rs.getInt("id"));
				messInfo.setSenderID(rs.getInt("sender"));
				messInfo.setAccepterID(rs.getInt("accepter"));
				messInfo.setTitle(rs.getString("title"));
				messInfo.setTime(rs.getString("CREATETIME"));
				messInfo.setContent(rs.getString("CONTENT"));
				
			}
			 conn.commit();
		} catch (SQLException e) {
			
			try {
				if(conn!=null)
				{
					conn.rollback();//事务回滚
				}
			} catch (SQLException e1) {
				
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		finally
		{
			try {
				if(conn!=null)
				{
					conn.close();
				}
				if(ps!=null)
				{
					ps.close();
				}
				if(rs!=null)
				{
					rs.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	   return messInfo;
	   
   }

}