documentdao.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 DocumentDAO {

	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 sign,int accepter) //传来输入的查询条件
	{   
		int i = 0;
		try {
			conn=tj.getConnection();
			conn.setAutoCommit(false);
			String sql = "select count(*) totulrows from tb_document d where sign="+sign+"and accepter="+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 searchDoc(String title,int userID,int Sign,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 accepter="+userID+"and sign="+Sign+"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");
				di.setSender(sender);
			    di.setID(ID);
				di.setTitle(Title);
				di.setCreateTime(createTime);
				di.setAccepter(accepter);
				di.setAffixId(affixId);
				di.setContent(content);
				di.setExamine(examine);
				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 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");
				di.setSender(sender);
			    di.setID(ID);
				di.setTitle(Title);
				di.setCreateTime(createTime);
				di.setAccepter(accepter);
				di.setAffixId(affixId);
				di.setContent(content);
				di.setExamine(examine);
				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 boolean insertDoc(DocumentInfo docInfo)
	{
		boolean flag=false;
		try
		{	
			conn=tj.getConnection();
			conn.setAutoCommit(false);
			st=conn.createStatement();
			int id=0;
			ResultSet rs=st.executeQuery("select doc_seq.nextval from dual") ;
			while(rs.next())
			{
			id=rs.getInt(1);	
			}
			String sql="insert into tb_document values ("+id+",?,?,?,?,?,?,?,?,?,?)";
			ps=conn.prepareStatement(sql);
			System.out.println("sender="+docInfo.getSender());
			System.out.println("accepter="+docInfo.getAccepter());			
			ps.setInt(1,docInfo.getSender());
			ps.setInt(2, docInfo.getAccepter());
			ps.setString(3, docInfo.getTitle());
			ps.setString(4, docInfo.getCreateTime());
			ps.setString(5, docInfo.getContent());
			ps.setInt(6, id);
			ps.setInt(7, docInfo.getIsAffix());
			ps.setInt(8, docInfo.getExamine());
			ps.setInt(9, docInfo.getSign());
			ps.setInt(10, docInfo.getLocation());
			int i=ps.executeUpdate();
			if(i>0)
			{
				flag=true;
			}
		
		}
		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  getDocumentTitle(int docId)
	{
		String title=null;
		
		try
		{
			conn=tj.getConnection();
			conn.setAutoCommit(false);
			String sql="select  title from tb_document  where id="+docId;
			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 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();
			}
		}
	
		
		
		
	}
	
	
	public List getDocList(int documentId)
	{     
		
		List<DocumentInfo> lt = new ArrayList<DocumentInfo>();
		try {
			conn=tj.getConnection();
			conn.setAutoCommit(false);
			String sql = "select * from tb_document d where  id="+documentId;
			
			ps = conn.prepareStatement(sql);
			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");
				
				int sender=rs.getInt("sender");
				int accepter=rs.getInt("accepter");
				
				
				int isAffix=rs.getInt("ISAFFIX");
				String content=rs.getString("content");
   
				int affixId=rs.getInt("affixid");
				int  examine=rs.getInt("examine");

			    int isReply=rs.getInt("ISREPLY");
			    int sign=rs.getInt("sign");

				
				int location=rs.getInt("location");
				di.setSender(sender);
				di.setSign(sign);
			    di.setID(ID);
				di.setTitle(Title);
				di.setCreateTime(createTime);
				di.setAccepter(accepter);
				di.setAffixId(affixId);
				di.setContent(content);
				di.setExamine(examine);
				di.setAffixId(affixId);
				di.setIsAffix(isAffix);
				di.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;
	}
	
	
}