housecontrol.java

利用java编写的一个学生宿舍管理系统

/*
 *HouseControl.java
 *author:liuhaijing
 */
 
 package mydesign;
 
 import mydesign.*;
 import java.sql.*;
 import java.io.*;
 import java.util.*;
 import mydesign.*;
 public class HouseControl
 {
 	
 	ResultSet rs=null;
 	String sql=null;  //用于操作房间信息表的字符串
 	String sql1=null;  //用于操作水费信息表的字符串
 	String sql2=null;   //用于操作电费信息表的字符串
    DBConnect dbconnect=new DBConnect();   //JDBC连接Bean
 	
 	public HouseControl() //构造函数
 	{
 	}
 	
 	public void addHouse(House house)  //添加房间
 	{
 		try
 		{
 			sql="insert into house (donghao,houseid,sex,phone,kezhu,ruzhu,expense,extra) values ('"+house.getDonghao()+"','"+house.getHouseid()+"','"+house.getSex()+"','"+house.getPhone()+"','"+house.getKezhu()+"','"+house.getRuzhu()+"','"+house.getExpense()+"','"+house.getExtra()+"');";
 			sql1="insert into water (donghao,houseid,dishu,xianshu) values ('"+house.getDonghao()+"','"+house.getHouseid()+"','"+house.getWdishu()+"','"+house.getWdishu()+"');";
 			sql2="insert into electric (donghao,houseid,dishu,xianshu) values ('"+house.getDonghao()+"','"+house.getHouseid()+"','"+house.getEdishu()+"','"+house.getEdishu()+"');";
 			dbconnect.executeUpdate(sql);
 			dbconnect.executeUpdate(sql1);
 			dbconnect.executeUpdate(sql2);
 		}
 		catch(Exception ex)
 		{
 			System.out.print("HouseControl's AddAHouse error:"+ex.getMessage());
 		}
 	}
 	
 	public void addStudent(String donghao,String houseid)
 	{
 		try
 		{
 			sql="update house set ruzhu=ruzhu+1 where donghao='"+donghao+"' and houseid='"+houseid+"';";
 			dbconnect.executeUpdate(sql);
 		}
 		catch(Exception ex)
 		{
 			System.out.print("HouseControl's addStudent error:"+ex.getMessage());
 		}
 	}
 			
 	public void delStudent(String donghao,String houseid)
 	{
 		try
 		{
 			sql="update house set ruzhu=ruzhu-1 where donghao='"+donghao+"' and houseid='"+houseid+"';";
 			dbconnect.executeUpdate(sql);
 		}
 		catch(Exception ex)
 		{
 			System.out.print("HouseControl's delStudent error:"+ex.getMessage());
 		}
 	}

 	public int getHouseCount(String strsql)  //计算查询结果的行数
 	{
 		int i=0;
 		try
 		{
 			rs=dbconnect.executeQuery(strsql);
 			while(rs.next())
 			{
 				i=rs.getInt(1);
 			}
 		}
 		catch(Exception ex)
 		{
 			System.out.print("HouseControl's getHouseInfo error:"+ex.getMessage());
 		}
 		return i;
 	}
 			
 	public ArrayList getHouseInfo(String strsql)  //得到查询结果
 	{	
 		ArrayList a=new ArrayList();
 		try
 		{
 			rs=dbconnect.executeQuery(strsql);
 			while(rs.next())
 			{
 				House house=new House();
 				if(rs.getString("donghao")!=null)
 				    house.setDonghao(new String(rs.getString("donghao").getBytes("iso-8859-1")));
 				if(rs.getString("houseid")!=null)
 				    house.setHouseid(new String(rs.getString("houseid").getBytes("iso-8859-1")));
 				if(rs.getString("sex")!=null)
 				    house.setSex(new String(rs.getString("sex").getBytes("iso-8859-1")));
 				if(rs.getString("phone")!=null)
 				    house.setPhone(new String(rs.getString("phone").getBytes("iso-8859-1")));
 				if(rs.getString("kezhu")!=null)
 				    house.setKezhu(new String(rs.getString("kezhu").getBytes("iso-8859-1")));
 				if(rs.getString("ruzhu")!=null)
 				    house.setRuzhu(new String(rs.getString("ruzhu").getBytes("iso-8859-1")));
 				if(rs.getString("expense")!=null)
 				    house.setExpense(new String(rs.getString("expense").getBytes("iso-8859-1")));
 				if(rs.getString("extra")!=null)
 				    house.setExtra(new String(rs.getString("extra").getBytes("iso-8859-1")));
 				a.add(house);
 			}
 		}
 		catch(Exception ex)
 		{
 			System.out.print("HouseControl's getHouseInfo error:"+ex.getMessage());
 		}
 		return a;
 	}
	
 	public House getHouseInfo(String donghao,String houseid)  //得到查询结果
 	{	
 		House house=new House();
 		try
 		{
 			sql="select * from house where donghao='"+donghao+"' and houseid='"+houseid+"';";
 			rs=dbconnect.executeQuery(sql);
 			if(rs.next())
 			{
 				house.setDonghao(new String(rs.getString("donghao").getBytes("iso-8859-1")));
 				house.setHouseid(new String(rs.getString("houseid").getBytes("iso-8859-1")));
 				house.setSex(new String(rs.getString("sex").getBytes("iso-8859-1")));
 				house.setPhone(new String(rs.getString("phone").getBytes("iso-8859-1")));
 				house.setKezhu(new String(rs.getString("kezhu").getBytes("iso-8859-1")));
 				house.setRuzhu(new String(rs.getString("ruzhu").getBytes("iso-8859-1")));
 				house.setExpense(new String(rs.getString("expense").getBytes("iso-8859-1")));
 				house.setExtra(new String(rs.getString("extra").getBytes("iso-8859-1")));
 			}
 		}
 		catch(Exception ex)
 		{
 			System.out.print("HouseControl's getHouseInfo error:"+ex.getMessage());
 		}
 		return house;
 	}
 	
 	public boolean isExistHouse(String donghao,String houseid) throws Exception  //查询是否存在该房间
 	{
 		boolean a=false;
 		String sql="select * from house where donghao='"+donghao+"' and houseid='"+houseid+"';";
 		try
 		{
 			rs=dbconnect.executeQuery(sql);
 			if(rs.next())
 			{
 				a=true;
 			}
 		}
 		catch(Exception ex)
 		{
 			System.out.print("HouseControl's isExistHouse error:"+ex.getMessage());
 		}
 		return a;
 	}
 		
 	public void DelHouse(House house) throws Exception    //删除房间
 	{
 		try
 		{
 			sql="delete from house where donghao='"+house.getDonghao()+"' and houseid='"+house.getHouseid()+"';";
 			sql1="delete from water where donghao='"+house.getDonghao()+"' and houseid='"+house.getHouseid()+"';";
 			sql2="delete from electric where donghao='"+house.getDonghao()+"' and houseid='"+house.getHouseid()+"';";
 			dbconnect.executeUpdate(sql);
 			dbconnect.executeUpdate(sql1);
 			dbconnect.executeUpdate(sql2);
 		}
 		catch(Exception ex)
 		{
 			System.out.print("HouseControl's DelHouse Error:"+ex.getMessage());
 		}
 	} 		
	
 	public void UpdateHouse(House house) throws Exception   //修改房间信息
 	{
 		try
 		{
 			sql="update house set sex='"+house.getSex()+"',phone='"+house.getPhone()+"',kezhu='"+house.getKezhu()+"',expense='"+house.getExpense()+"',extra='"+house.getExtra()+"' where donghao='"+house.getDonghao()+"' and houseid='"+house.getHouseid()+"';";
 			dbconnect.executeUpdate(sql);
 		}
 		catch(Exception ex)
 		{
 			System.out.print("HouseControl's UpdateHouse Error:"+ex.getMessage());
 		}
 	}
 	
 	public PageBean listData(int page,SearchHouse searchhouse) throws Exception 	//获得指定页面的数据，并且封装在PageBean中返回
 	{
 		try
 		{
			int count=getHouseCount(searchhouse.strSQL1);
 			ArrayList result=getHouseInfo(searchhouse.strSQL);
			PageBean pagebean=new PageBean(count,result);
 			ArrayList v=new ArrayList();
 			int x=(pagebean.maxRowCount<page*pagebean.rowsPerPage?pagebean.maxRowCount:page*pagebean.rowsPerPage);
 			for(int i=pagebean.rowsPerPage*(page-1);i<x;i++)
 			{
 				v.add(result.get(i));
 			}
 			pagebean.curPage=page;
 			pagebean.data=v;
 			return pagebean;
 		}
 		catch(Exception ex)
 		{
 			ex.printStackTrace();
 			throw ex;
 		}
 	}
 	
 	//close
 	public void close()
 	{
 		dbconnect.close();
 	}
 }