.#messinfodao.java.1.4

实现办公自动化系统

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 tj = 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=tj.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 = tj.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=tj.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 boolean insertItem(MessInfo info)
	{
		boolean result=false;
		try
		{
			conn=tj.getConnection();
			conn.setAutoCommit(false);
			String sql="insert into tb_mesInfo values(?,?,?,?)";
	
		
			ps=conn.prepareStatement(sql);
			ps.setString(1,info.getTitle());
			ps.setString(2,info.getContent());
			ps.setInt(3,info.getSenderID());
			ps.setInt(4,info.getAccepterID());
			ps.setInt(2,info.getIsRead());
			
			
		
			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;
   }
	
	
//查询已发送消息
	
	public List searchSended(MessInfo info)
	{
		
		List lt=new ArrayList();
		try
		{
			String sql="select * from tb_addressBook where senderID=?";
			conn=tj.getConnection();
			conn.setAutoCommit(false);
			ps=conn.prepareStatement(sql);
			ps.setInt(1,info.getSenderID

());
		    rs=ps.executeQuery();
			
			while(rs.next())
			{
				int userID=rs.getInt(2);
				String userName=rs.getString(3);
				int phone=rs.getInt(4);
				int officeTel=rs.getInt(5);
				String QQ=rs.getString(6);
				String email=rs.getString(7);
				String address=rs.getString(8);
				lt.add(info);
			}
			
			if(rs!=null) rs.close();
			if(ps!=null) ps.close();
			if(conn!=null) conn.close();		
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}	
		return lt;
	}
	
	
	
	
	
	
	
	
	
	public boolean insertMessage(messageInfo meInfo)
	{
		boolean flag=false;

		String sql="insert into tb_mesInfo values (MESINFO_SEQ.nextval,?,?,?,?,?,?,?)";
		try
		{	
			conn=tj.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 List searchSended(MessInfo info)
	{
		
		List lt=new ArrayList();
		try
		{
			String sql="select * from tb_addressBook where userID=?,userName=?";
			db=new DBConn();
			con=db.getConnection();
			pstmt=con.prepareStatement(sql);
			pstmt.setInt(1);
			pstmt.setString(2,book.getUserName());
			rs=pstmt.executeQuery();
			
			while(rs.next())
			{
				int userID=rs.getInt(2);
				String userName=rs.getString(3);
				int phone=rs.getInt(4);
				int officeTel=rs.getInt(5);
				String QQ=rs.getString(6);
				String email=rs.getString(7);
				String address=rs.getString(8);
				lt.add(info);
			}
			
			if(rs!=null) rs.close();
			if(pstmt!=null) pstmt.close();
			if(con!=null) con.close();		
			
		}
		catch(Exception 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=tj.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=tj.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 String  getMessContent(int messId)
	{
		String content=null;
		
		try
		{
			conn=tj.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;
	}
	
	

}