meetroomdao.java

oa办公管理系统。一些小型的企业办公管理用的系统。一个月废寝忘食的结果。

package com.soft.meetroom.dao;

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.soft.pagecut.PageableResultSet;
import com.soft.pagecut.pageable;
import com.soft.util.DBConn;
import com.soft.vo.DepartmentInfo;
import com.soft.vo.MeetroomInfo;

public class MeetroomDAO {

	
	private DBConn tj = new DBConn();
	private Connection conn = null; 
	private Statement st = null;
	private PreparedStatement ps = null;
	private ResultSet rs = null;
	private pageable res=null;
	int PageCount;
	int rowsCount;
	int PageRowsCount;
	
	int PageSize;
	int CurPage;
	public int getPageCount() {
		return PageCount;
	}

	

	public int getPageRowsCount() {
		return PageRowsCount;
	}

	

	public int getPageSize() {
		return PageSize;
	}

	public void setPageSize(int pageSize) {
		PageSize = pageSize;
	}

	public int getCurPage() {
		return CurPage;
	}

	public int getRowsCount() {
		return rowsCount;
	}

	public void setCurPage(int curPage) {
		CurPage = curPage;
	}
	public List getAllRoom()
	{
		List<MeetroomInfo> lt = new ArrayList<MeetroomInfo>();
		try {
			conn=tj.getConnection();
			conn.setAutoCommit(false);
			st=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
			res=new PageableResultSet(st.executeQuery("select * from meetingroom"));//构造一个Pageable
			res.setPageSize(5);//每页5个记录
            
			System.out.println("pagetcount:"+res.getPageCount()+"rowscount:"+res.getRowsCount()+"pagesize:"+res.getPageSize());
			res.gotoPage(getCurPage());//跳转到第2页
			PageCount=res.getPageCount();
			rowsCount=res.getRowsCount();
			PageRowsCount=res.getPageRowsCount();
			System.out.println("current page:"+res.getCurPage());
			for(int i=0; i<res.getPageRowsCount(); i++)
			
			{
			    MeetroomInfo min = new MeetroomInfo();
			    String iscanapplystr="否";
				int meetingroomid=res.getInt("meetingroomid");
				int iscanapply=res.getInt("iscanapply");
				int maxpeople=res.getInt("maxpeople");
				if(iscanapply==0)
					iscanapplystr="是";
				if(iscanapply==1)
					iscanapplystr="否";
				min.setMeetingRoomId(meetingroomid);
				min.setIsCanApply(iscanapplystr);
				min.setMaxPeople(maxpeople);
				min.setIsUsing(iscanapplystr);
				lt.add(min);
				res.next();
			}
			System.out.println(lt.size());
		    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 addNewMeetRoom(int meetringroomid,String meetingroomname,int maxpeople,String meetingroomlocation)
	{
		int i = 0;
		int numberofpeople=0;
		try {
			conn = tj.getConnection();
			conn.setAutoCommit(false);
			
			ps = conn.prepareStatement("insert  into meetingroom values("+meetringroomid+",'"+meetingroomname+"','"+meetingroomlocation+"',"+1+","+maxpeople+")");
		
			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 int updateMeetRoom(int meetingroomid,String meetingroomname,int maxpeople,String roomlocation,int iscanapply )
	{
		int i = 0;
		
		try {
			conn = tj.getConnection();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement("update  meetingroom set meetingroomname=? , roomlocation=?,iscanapply=?,maxpeople=? where meetingroomid=?");
			ps.setString(1,meetingroomname);
			ps.setString(2,roomlocation);
			ps.setInt(3,iscanapply);
			ps.setInt(4,maxpeople);
			ps.setInt(5, meetingroomid);
			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 int delMeetRoom(int meetingroomid)
	{
		int i = 0;
		try {
			conn = tj.getConnection();
			conn.setAutoCommit(false);
			
			ps = conn.prepareStatement("delete from meetingroom where meetingroomid=?");
			ps.setInt(1,meetingroomid);
			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 searchMeetRoom(int meetingroomid)
	{
		List<MeetroomInfo> lt = new ArrayList<MeetroomInfo>();
		try {
			conn=tj.getConnection();
			conn.setAutoCommit(false);
			String sql = "select * from meetingroom where meetingroomid=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1,meetingroomid);
			rs = ps.executeQuery();
			while(rs.next())
			{
				MeetroomInfo mif = new MeetroomInfo();
				int meetingroomid1=rs.getInt("meetingroomid");
				String meetingroomname=rs.getString("meetingroomname");
				String roomlocation=rs.getString("roomlocation");
				int maxpeople=rs.getInt("maxpeople");
				mif.setMeetingRoomId(meetingroomid1);
				mif.setMeetingRoomName(meetingroomname);
				mif.setMeetingRoomLocation(roomlocation);
				mif.setMaxPeople(maxpeople);
				lt.add(mif);
			}
			System.out.println(lt.size());
		    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 getRoomInfo()
	{
		List<MeetroomInfo> lt = new ArrayList<MeetroomInfo>();
		try {
			conn=tj.getConnection();
			conn.setAutoCommit(false);
			st=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
			res=new PageableResultSet(st.executeQuery("select * from meetingroom where iscanapply=1"));//构造一个Pageable
			
          while(res.next())
			{
			    MeetroomInfo min = new MeetroomInfo();
			  //  String iscanapplystr="否";
				int meetingroomid=res.getInt("meetingroomid");
				String meetingroomname=res.getString("meetingroomname");
				//int iscanapply=res.getInt("iscanapply");
				int maxpeople=res.getInt("maxpeople");
				/*if(iscanapply==0)
					iscanapplystr="是";
				if(iscanapply==1)
					iscanapplystr="否";*/
				min.setMeetingRoomId(meetingroomid);
				min.setMeetingRoomName(meetingroomname);
				//min.setIsCanApply(iscanapplystr);
				min.setMaxPeople(maxpeople);
				//min.setIsUsing(iscanapplystr);
				lt.add(min);
				
			}
		
		    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;
	}
}