sendeddocumentdao.java

实现办公自动化系统

package com.oa.struts.document.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 javax.servlet.http.HttpSession;

import com.oa.util.DBConn;

import com.oa.struts.vo.*;
public class SendedDocumentDAO {

	private DBConn tj = new DBConn();
	private Connection conn = null; 
	private Statement st = null;
	private PreparedStatement ps = null;
	private ResultSet rs = null;
	public int getTotulRows(String title,int accepter) //传来输入的查询条件
	{   
		int i = 0;
		try {
			conn=tj.getConnection();
			conn.setAutoCommit(false);
			String sql = "select count(*) totulrows from tb_document d where sender="+accepter+"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 delDoc(int docId)
	{
		int i = 0;
		try {
			conn = tj.getConnection();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement("delete from tb_document where id=?");
			ps.setInt(1,docId);
			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 searchSendedDoc(String title,int userID,int startRow,int endRow)
	{     
		
		
		
		
		int accepterID;
		List<DocumentInfo> lt = new ArrayList<DocumentInfo>();
		try {
			conn=tj.getConnection();
			conn.setAutoCommit(false);
			String sql = "select d.*,rownum rn from tb_document d  where sender="+userID+"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())
			{
				DocumentInfo di = new DocumentInfo();
				int  ID=rs.getInt("ID");
				String Title=rs.getString("title");
				String createTime=rs.getString("createTime");
				System.out.print("**************"+createTime);
				int sender=rs.getInt("sender");
				int accepter=rs.getInt("accepter");
				
				
				
				String content=rs.getString("content");

				int affixId=rs.getInt("affixid");
				int  examine=rs.getInt("examine");

			

				int sign=rs.getInt("sign");
				int location=rs.getInt("location");
				int isReply=rs.getInt("ISREPLY");
				di.setSender(sender);
			    di.setID(ID);
				di.setTitle(Title);
				di.setCreateTime(createTime);
				di.setSender(sender);
				di.setAccepter(accepter);
				di.setAffixId(affixId);
				di.setContent(content);
				di.setExamine(examine);
				//sdi.setIsReply(isReply);
				lt.add(di);
			}
		    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 getIsReply(int documentId,int accepterId,int senderId)
	{
		
		int  ID=0;
		String sql="select  isread from tb_mesinfo where documentid="+documentId+"and accepter="+accepterId+"and sender="+senderId;
		System.out.println("sql==="+sql);
		try
		{	 
			conn=tj.getConnection();
			conn.setAutoCommit(false);
			ps=conn.prepareStatement(sql);
			rs = ps.executeQuery();
			
			while(rs.next())
			{
			   ID=rs.getInt("isread");
			   System.out.println("*************ID=="+ID);
			}
				
		}
		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 void setIsReply(int id,int a)
	{
		
		int  i=0;
		String sql="update tb_document  set isreply="+a+"where id="+id;
		try
		{	
			conn=tj.getConnection();
			conn.setAutoCommit(false);
			ps=conn.prepareStatement(sql);
			rs = ps.executeQuery();
			
			i = ps.executeUpdate();
		    conn.commit();
			
			
				
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				if(conn!=null)
				{
					conn.close();
				}
				if(ps!=null)
				{
					ps.close();
				}
			}
			catch(SQLException e)
			{
				e.printStackTrace();
			}
		}
	
		
		
		
	}
	
	
}