meetingdao.java

实现办公自动化系统

package com.oa.struts.meeting.modle;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.oa.struts.vo.MeetingInfo;
import com.oa.util.DBConn;

public class MeetingDAO {
	
	public Connection con=null;
	public  PreparedStatement pstmt=null;
	public ResultSet rs=null;
	DBConn db=null;
	
	public MeetingDAO() {
		super();
		// TODO Auto-generated constructor stub
	}
	
//返回所有行	
	public int getTotulRows() 
	{   
		int i = 0;
		try {
			db=new DBConn();
			con=db.getConnection();
			con.setAutoCommit(false);
			String sql = "select count(*) totulrows from tb_metting  where 1=1 ";
			pstmt = con.prepareStatement(sql);
			
            rs = pstmt.executeQuery();
            
			if(rs.next())
			{
				i = rs.getInt("totulrows");
			}
		    con.commit();
		} catch (SQLException e) {
			
			try {
				if(con!=null)
				{
					con.rollback();//事务回滚
				}
			} catch (SQLException e1) {
				
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		finally
		{
			try {
				if(con!=null)
				{
					con.close();
				}
				if(pstmt!=null)
				{
					pstmt.close();
				}
				if(rs!=null)
				{
					rs.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return i;
	}
	
//	返回所有的会议记录
	public List getAllMeeting()
	{
		List<MeetingInfo> list = new ArrayList<MeetingInfo>();
		int i=0;
		try {
			con=db.getConnection();
			con.setAutoCommit(false);
			pstmt = con.prepareStatement("select * from tb_metting");
			rs = pstmt.executeQuery();
			
			while(rs.next())
			{
				MeetingInfo  meeting = new MeetingInfo();
				meeting.setMetID(rs.getInt("ID"));
				meeting.setApplytime(rs.getString("APPLYTIME"));
				meeting.setTopic(rs.getString("TOPIC"));
				meeting.setMetRoomId(rs.getInt("METROOMID"));
				meeting.setUserID(rs.getInt("USERID"));
				meeting.setMetType(rs.getString("METTYPE"));
				meeting.setStartTime(rs.getString("STARTTIME"));
				meeting.setEndTime(rs.getString("ENDTIME"));
				meeting.setJoinPeople(rs.getString("JOINPEOPLE"));
				meeting.setContent(rs.getString("CONTENT"));
				meeting.setBz(rs.getString("BZ"));
				System.out.println("meeting");
				list.add(i++, meeting);

			}
		    con.commit();
		} catch (SQLException e) {
			
			try {
				if(con!=null)
				{
					con.rollback();//事务回滚
				}
			} catch (SQLException e1) {
				
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		finally
		{
			try {
				if(con!=null)
				{
					con.close();
				}
				if(pstmt!=null)
				{
					pstmt.close();
				}
				if(rs!=null)
				{
					rs.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	
	
//	查询会议室
	public List searchMeeting(String topic,String applytime)
	{
		List<MeetingInfo> list = new ArrayList<MeetingInfo>();
		try
		{
			db=new DBConn();
			con=db.getConnection();	
			String sql="select * from tb_metting where topic=? and applytime=?";
			pstmt=con.prepareStatement(sql);
			if(topic!=null&&!topic.equals(""))
			{
				pstmt.setString(1,"%"+topic+"%");
				if(applytime!=null&&!applytime.equals(""))
				{
					pstmt.setString(2,"%"+applytime+"%");	
				}
			}
			else
			{
				if(applytime!=null&&!applytime.equals(""))
				{   
					pstmt.setString(1,"%"+applytime+"%");
				}
			}
           rs=pstmt.executeQuery();
		   MeetingInfo meeting= new MeetingInfo();
				while(rs.next())
				{
					meeting.setMetID(rs.getInt("ID"));
					meeting.setApplytime(rs.getString("APPLYTIME"));
					meeting.setTopic(rs.getString("TOPIC"));
					meeting.setMetRoomId(rs.getInt("METROOMID"));
					meeting.setUserID(rs.getInt("USERID"));
					meeting.setMetType(rs.getString("METTYPE"));
					meeting.setStartTime(rs.getString("STARTTIME"));
					meeting.setEndTime(rs.getString("ENDTIME"));
					meeting.setJoinPeople(rs.getString("JOINPEOPLE"));
					meeting.setContent(rs.getString("CONTENT"));
					meeting.setBz(rs.getString("BZ"));
					list.add(meeting);
				}
			if(rs!=null) rs.close();
			if(pstmt!=null) pstmt.close();
			if(con!=null) con.close();		
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return list;
	}
	
	

//新增会议
	public boolean insertMeeting(MeetingInfo meeting)
	{
		boolean result=false;
		
		try
		{
			String sql="insert into tb_metting values(metting_seq.nextval,?,?,?,?,?,?,?,?,?,?)";
			db=new DBConn();
			con=db.getConnection();
			pstmt=con.prepareStatement(sql);
			pstmt.setString(1,meeting.getApplytime());
			pstmt.setString(2,meeting.getTopic());
			pstmt.setInt(3, meeting.getMetRoomId());
            pstmt.setInt(4,meeting.getUserID());
            pstmt.setString(5, meeting.getMetType());
            pstmt.setString(6,meeting.getStartTime());
            pstmt.setString(7,meeting.getEndTime());
            pstmt.setString(8,meeting.getJoinPeople());
            pstmt.setString(9,meeting.getContent());
            pstmt.setString(10, meeting.getBz());
            			
			int i=pstmt.executeUpdate();
			if(i>0)
			{
				result=true;
			}
				
			if(pstmt!=null) pstmt.close();
			if(con!=null) con.close();		
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return result;
	}

	
	

//根据id删除会议
	public boolean deleteMeeting(int metID)
	{
		boolean result=false;
			try {
				  DBConn db=new DBConn();
				  con = db.getConnection();
				  con.setAutoCommit(false);
				  pstmt = con.prepareStatement("delete from tb_metting where metID=?");
				  pstmt.setInt(1,metID);
			      int i = pstmt.executeUpdate();
			      con.commit();

			      if(i>0)
			     {
				  result=true;
			     }
			 	
			     if(pstmt!=null) pstmt.close();
			     if(con!=null) con.close();		
			
		        }
		        catch(Exception e)
		        {
			     e.printStackTrace();
		        }
		       return result;	
	 }

	
	
	


//根据id更新会议
	public boolean updateMeeting(MeetingInfo meeting,int metId)
	{
		boolean result=false;
		//int metID;
		//String applytime;
		//String topic;
		//int metRoomId;
		//int userID;
		//String metType;
		//String startTime;
		//String endTime;
		//String joinPeople;
		//String content;
		//String bz;
		try
		{
			String sql="update tb_metting set applytime=?,topic=?,metRoomId=?,userID=?,metType=?,startTime=?,endTime=?,joinPeople=?,content=?,bz=? where ID=?";
			db=new DBConn();
			con=db.getConnection();
			pstmt=con.prepareStatement(sql);
			pstmt.setString(1,meeting.getApplytime());
			pstmt.setString(2,meeting.getTopic());
			pstmt.setInt(3, meeting.getMetRoomId());
            pstmt.setInt(4,meeting.getUserID());
            pstmt.setString(5, meeting.getMetType());
            pstmt.setString(6,meeting.getStartTime());
            pstmt.setString(7,meeting.getEndTime());
            pstmt.setString(8,meeting.getJoinPeople());
            pstmt.setString(9,meeting.getContent());
            pstmt.setString(10, meeting.getBz());
			pstmt.setInt(11, metId);
			int i=pstmt.executeUpdate();
			if(i>0)
			{
				result=true;
			}
				
			if(pstmt!=null) pstmt.close();
			if(con!=null) con.close();		
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return result;
	}
	
	
	
}