.#meetingroomdao.java.1.7

实现办公自动化系统

/**
 * 会议室处理类
 * 包括查询会议室、修改会议室信息、删除会议室信息
 */
package com.oa.struts.meetingroom.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.MeetingInfo;
import com.oa.struts.vo.MeetingRoomInfo;
import com.oa.util.DBConn;

public class MeetingRoomDAO {

	public Connection con=null;
	public PreparedStatement pstmt=null;
	public Statement stmt=null;
	public ResultSet rs=null;
	public DBConn db=null;
	
	public MeetingRoomDAO() {
		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_metRoom  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 getAllMeetingRoom(int startRow,int endRow)
	{
		List<MeetingRoomInfo> list = new ArrayList<MeetingRoomInfo>();
		int i=0;
		try {
			con=db.getConnection();
			con.setAutoCommit(false);
			//System.out.println("1getAllMeetingRoom!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!");
			String sql="select t.*,rownum rn from tb_metRoom t where 1=1 ";
			
			sql = "select * from ("+ sql+" and rownum<="+endRow +") t where rn>="+startRow;
			stmt=con.createStatement();
			//System.out.println("2getAllMeetingRoom!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!");
			rs = stmt.executeQuery(sql);
			//System.out.println("3getAllMeetingRoom!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!");
			while(rs.next())
			{
				//System.out.println("4getAllMeetingRoom!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!");
				MeetingRoomInfo meetingroom = new MeetingRoomInfo();
				//System.out.println("MeetingRoomDAO rs getInt ID="+rs.getInt("ID"));
				meetingroom.setID(rs.getInt("ID"));
				//System.out.println("MeetingRoomDAO ID="+meetingroom.getID());
				meetingroom.setRoomName(rs.getString("roomname"));
				meetingroom.setGalleryful(rs.getInt("galleryful"));
				meetingroom.setDispose(rs.getString("dispose"));
				meetingroom.setAddress(rs.getString("address"));
				meetingroom.setState(rs.getInt("state"));
				list.add(i++, meetingroom);

			}
		    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 searchMeetingroom(int state)
	{
		boolean result=false;
		List list = new ArrayList();
		try
		{
			db=new DBConn();
			con=db.getConnection();	
			String sql="select * from tb_metRoom where state=? ";
            
			pstmt=con.prepareStatement(sql);
			pstmt.setInt(1,state);
			rs=pstmt.executeQuery();
			if(rs!=null)
			{
				MeetingRoomInfo meetRoom= new MeetingRoomInfo();
				while(rs.next())
				{
					
					meetRoom.setRoomName(rs.getString("roomName"));
					meetRoom.setGalleryful(rs.getInt("galleryful"));
					meetRoom.setDispose(rs.getString("dispose"));
					meetRoom.setAddress(rs.getString("address"));
					meetRoom.setState(rs.getInt("state"));
	                list.add(meetRoom);
				}
			}
			else
			{
				result=false;
			}
			if(rs!=null) rs.close();
			if(pstmt!=null) pstmt.close();
			if(con!=null) con.close();		
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		return list;
	}
	


	//新增会议室
	public boolean insertMeetingroom(MeetingRoomInfo meetingroom)
	{
		boolean result=false;
		try
		{
			String sql="insert into tb_metRoom values(?,?,?,?,?)";
			db=new DBConn();
			con=db.getConnection();
			pstmt=con.prepareStatement(sql);
			pstmt.setString(1,meetingroom.getRoomName());
			pstmt.setInt(2,meetingroom.getGalleryful());
			pstmt.setString(3,meetingroom.getDispose());
			
			pstmt.setString(4,meetingroom.getAddress());
			pstmt.setInt(5,meetingroom.getState());
		
		
			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 deleteMeetingroom(int ID)
	{
		boolean result=false;
		db=new DBConn();
		try {
			con = db.getConnection();
			con.setAutoCommit(false);
			String sql="delete from tb_metroom where id=?";
			pstmt=con.prepareStatement(sql);
			pstmt.setInt(1,ID);
			int i = pstmt.executeUpdate();
		    if(i>0)
		    {
			 result=true;
		    }	
		    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 result;	
	
	}
	
	
	
	//根据id更新会议室
	public boolean updateMeetingroom(MeetingRoomInfo meetingroom,int ID)
	{
		boolean result=false;
		try
		{
			String sql="update tb_metRoom set  roomName=?,galleryful=?,dispose=?,address=?,state=? where ID=?";
			db=new DBConn();
			con=db.getConnection();
			pstmt=con.prepareStatement(sql);
			pstmt.setString(1,meetingroom.getRoomName());
			pstmt.setInt(2,meetingroom.getGalleryful());
			pstmt.setString(3,meetingroom.getDispose());
            pstmt.setString(4,meetingroom.getAddress());
            pstmt.setInt(5,meetingroom.getState());
            pstmt.setInt(6,meetingroom.getID());

				
			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;
	}



	
	
	
}