search.java

Java语言编写的员工信息管理系统

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.Enumeration;
import java.util.Vector;


public class Search {
	Connection connector;

	public Search() {
		System.out.println("查询事务数据库连接器");
		try {
			Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");// 连接数据库
			connector = DriverManager
					.getConnection("jdbc:odbc:Inn", " ", " ");
		} catch (Exception e) {
			e.printStackTrace();// 把该 Throwable 和它的跟踪情况打印到标准错误流。
		}
	}
//	从某个结果集获取服务信息
	public Service getServiceFromResultSet(ResultSet resultset){
		Service service = new Service();	
		try {
			service.id = resultset.getString("serviceNo").trim();
			service.name = resultset.getString("ServiceName").trim();
			service.description = resultset.getString("ServiceDescription").trim();			
			return service;
		} catch (Exception e) {
			e.printStackTrace();// 把该 Throwable 和它的跟踪情况打印到标准错误流。
			return null;
        }		
	}
//	从某个结果集获取房间信息
	public Room getRoomFromResultSet(ResultSet resultset){
		Room room = new Room();	
		try {
			room.id = resultset.getString("roomNo").trim();
			room.roomClass = resultset.getString("roomClass").trim();
			room.fee = Integer.parseInt(resultset.getString("roomFee").trim());
			room.state = resultset.getString("roomState").trim();
			room.phone = resultset.getString("roomPhone").trim();
			return room;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
        }		
	}

//	从结果集中获取所有入住客人的信息
	public EnrolGuest getEnrolGuestFromResultSet(ResultSet resultset){
		EnrolGuest guest = new EnrolGuest();	
		try {
			guest.id = Integer.parseInt(resultset.getString("enrolGuestNo").trim());
			guest.name = resultset.getString("name").trim();
			guest.sex = Integer.parseInt(resultset.getString("sex").trim());
			guest.certificateId = resultset.getString("IC").trim();
			guest.nationality = resultset.getString("nationality").trim();
			guest.entourageNum = Integer.parseInt(resultset.getString("guestCount").trim());
			guest.phone = resultset.getString("phoneNumber").trim();
			guest.address = resultset.getString("address").trim();
			guest.postalcode = resultset.getString("postalcode").trim();
			guest.ArrivalDate = resultset.getString("arrivalDate").trim();
			guest.desposit = Integer.parseInt(resultset.getString("deposit").trim());
			guest.ServiceExpenses = Integer.parseInt(resultset.getString("serviceExpenses").trim());
			guest.RoomExpenses = Integer.parseInt(resultset.getString("roomExpenses").trim());
			guest.CheckOutDate = resultset.getString("checkOutDate").trim();
			guest.StaffId = resultset.getString("staffNo").trim();	
			return guest;
		} catch (Exception e) {
			e.printStackTrace();// 把该 Throwable 和它的跟踪情况打印到标准错误流。
			return null;
        }		
	}
	public String search(int index) {		
		String result = "";
		switch (index) {
		case 1:
			System.out.println("search 1");
			result= search1();
			break;
		case 2:
			System.out.println("search 2");
			result= search2();
			break;
		case 3:
			System.out.println("search 3");
			result= search3();
			break;
		case 4:
			System.out.println("search 4");
			result= search4();
			break;
		case 5:
			System.out.println("search 5");
			result= search5();
			break;
		case 6:
			System.out.println("search 6");
			result= search6();
			break;
		case 7:
			System.out.println("search 7");
			result= search7();
			break;
		case 8:
			System.out.println("search 8");
			result= search8();
			break;
		case 9:
			System.out.println("search 9");
			result= search9();
			break;
		case 10:
			System.out.println("search 10");
			result= search10();
			break;
		case 11:
			System.out.println("search 11");
			result= search11();
			break;
		case 12:
			System.out.println("search 12");
			result= search12();
			break;
		case 13:
			System.out.println("search 13");
			result= search13();
			break;
		case 14:
			System.out.println("search 14");
			result= search14();
			break;
		case 15:
			System.out.println("search 15");
			result= search15();
			break;
		}
		return result;
	}

	public String search1() {
		String result = "";
		try{			
       		CallableStatement proc = 
       			connector.prepareCall("{ call sp_1(?) }"); 
       		proc.registerOutParameter(1,Types.INTEGER);            
        	proc.execute(); 
        	int outParam = proc.getInt(1);     // OUT parameter
			result=outParam+"";
        	proc.close();        	
        }catch (Exception e) {
            e.printStackTrace();//把该 Throwable 和它的跟踪情况打印到标准错误流。
        }        
		return result;
	}
	public String search2() {
		String result = "";
		try{			
       		CallableStatement proc = 
       			connector.prepareCall("{ call sp_2(?) }"); 
       		proc.registerOutParameter(1,Types.INTEGER);            
        	proc.execute(); 
        	int outParam = proc.getInt(1);     // OUT parameter
			result=outParam+"";
        	proc.close();        	
        }catch (Exception e) {
            e.printStackTrace();//把该 Throwable 和它的跟踪情况打印到标准错误流。
        }        
		return result;
	}
	public String search3() {
		String result = "";
		Vector roomList = new Vector();
		try {
			CallableStatement proc = connector
					.prepareCall("{ call sp_3 }");
			proc.execute();
			ResultSet resultset = proc.getResultSet();
			while (resultset.next()) {
				Room room = new Room();
				room=getRoomFromResultSet(resultset);
				roomList.addElement(room); // 加入房间列表
			}
			Room room = new Room();
			for (Enumeration ee = roomList.elements(); ee.hasMoreElements();) {
				room = (Room) ee.nextElement();
				result = result + room.id + "  " + room.roomClass + "  "
						+ room.fee + "  " + room.state + "  " + room.phone
						+ "\n";
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	
	
	public String search4() {
		String result = "";
		Vector roomList = new Vector();
		try {
			CallableStatement proc = connector
					.prepareCall("{ call sp_4 }");
			proc.execute();
			ResultSet resultset = proc.getResultSet();
			while (resultset.next()) {
				Room room = new Room();
				room=getRoomFromResultSet(resultset);
				roomList.addElement(room); // 加入房间列表
			}
			Room room = new Room();
			for (Enumeration ee = roomList.elements(); ee.hasMoreElements();) {
				room = (Room) ee.nextElement();
				result = result + room.id + "  " + room.roomClass + "  "
						+ room.fee + "  " + room.state + "  " + room.phone
						+ "\n";
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	public String search5() {
		String result = "";
		Vector roomList = new Vector();
		try {
			CallableStatement proc = connector
					.prepareCall("{ call sp_5 }");
			proc.execute();
			ResultSet resultset = proc.getResultSet();
			while (resultset.next()) {
				Room room = new Room();
				room=getRoomFromResultSet(resultset);
				roomList.addElement(room); // 加入房间列表
			}
			Room room = new Room();
			for (Enumeration ee = roomList.elements(); ee.hasMoreElements();) {
				room = (Room) ee.nextElement();
				result = result + room.id + "  " + room.roomClass + "  "
						+ room.fee + "  " + room.state + "  " + room.phone
						+ "\n";
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	public String search6() {
		String result = "";
		Vector serviceInfoList=new Vector();	
		try {
			CallableStatement proc = connector
					.prepareCall("{ call sp_6 }");
			proc.execute();
			ResultSet resultset = proc.getResultSet();			
			while (resultset.next()) {	
				Service service = new Service();	
				service = getServiceFromResultSet(resultset);
            	serviceInfoList.addElement(service);		//加入房间列表
            }            
			Service service = new Service();
			for (Enumeration ee = serviceInfoList.elements(); ee.hasMoreElements();) {
				service = (Service) ee.nextElement();
				result = result + service.id + "  " + service.name + "  "
						+ service.description + "\n";
			}				
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	public String search7() {
		String result = "";		
		try {
			CallableStatement proc = connector
					.prepareCall("{ call sp_7 }");
			proc.execute();
			ResultSet resultset = proc.getResultSet();			
			while (resultset.next()) {
				EnrolGuest enrolGuest = new EnrolGuest();	
				enrolGuest = getEnrolGuestFromResultSet(resultset);
				String roomClass = resultset.getString("roomClass").trim();            	
            	result = result + 
            	enrolGuest.id + "  " + enrolGuest.name + "  "
            	+enrolGuest.sex + "  " + enrolGuest.certificateId + "  "
            	+enrolGuest.nationality + "  " + enrolGuest.entourageNum + "  "
            	+enrolGuest.phone + "  " + enrolGuest.address + "  "
            	+enrolGuest.postalcode + "  " + enrolGuest.ArrivalDate + "  "
            	+enrolGuest.desposit + "  " + enrolGuest.ServiceExpenses + "  "
            	+enrolGuest.RoomExpenses + "  " + enrolGuest.CheckOutDate + "  "
            	+enrolGuest.StaffId + "  "
            	+ roomClass +"\n";
            }    
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	public String search8() {
		String result = "";		
		try {
			CallableStatement proc = connector
					.prepareCall("{ call sp_8(?) }");
			String roomNo="Room2";		//改变房间号查询
			proc.setString(1,roomNo);
			proc.execute();
			ResultSet resultset = proc.getResultSet();				
			while (resultset.next()) {
				EnrolGuest enrolGuest = new EnrolGuest();	
				enrolGuest = getEnrolGuestFromResultSet(resultset);
				result = result + 
            	enrolGuest.id + "  " + enrolGuest.name + "  "
            	+enrolGuest.sex + "  " + enrolGuest.certificateId + "  "
            	+enrolGuest.nationality + "  " + enrolGuest.entourageNum + "  "
            	+enrolGuest.phone + "  " + enrolGuest.address + "  "
            	+enrolGuest.postalcode + "  " + enrolGuest.ArrivalDate + "  "
            	+enrolGuest.desposit + "  " + enrolGuest.ServiceExpenses + "  "
            	+enrolGuest.RoomExpenses + "  " + enrolGuest.CheckOutDate + "  "
            	+enrolGuest.StaffId + "  "+"\n";
				
            }    
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	public String search9() {
		String result = "";		
		try {
			CallableStatement proc = connector
					.prepareCall("{ call sp_9(?) }");
			String roomClass="DX";		//改变房间类型查询
			proc.setString(1,roomClass);
			proc.execute();
			ResultSet resultset = proc.getResultSet();				
			while (resultset.next()) {
				int roomFee = resultset.getInt("roomFee");
				result = result +roomClass+"   "+ roomFee +"\n";				
            }    
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	public String search10() {
		String result = "";
		try{			
       		CallableStatement proc = 
       			connector.prepareCall("{ call sp_10(?,?) }"); 
       		String roomNo="Room4";		//改变房间编号查询
			proc.setString(1,roomNo);
			//注册OutPut参数
       		proc.registerOutParameter(2,Types.INTEGER);            
        	proc.execute(); 
        	int outParam = proc.getInt(2);     // OUT parameter
			result=roomNo+"	"+outParam+"";
        	proc.close();        	
        }catch (Exception e) {
            e.printStackTrace();//把该 Throwable 和它的跟踪情况打印到标准错误流。
        }        
		return result;
	}
	public String search11() {
		String result = "";		
		try {
			CallableStatement proc = connector
					.prepareCall("{ call sp_11(?) }");
			String date="2006-07-01";		//改变时间查询
			proc.setString(1,date);
			proc.execute();
			ResultSet resultset = proc.getResultSet();				
			while (resultset.next()) {
				String name=resultset.getString("name");
				String roomNo=resultset.getString("roomNo");
				int serviceFee = resultset.getInt("serviceExpenses");
				result = result +date+"   "
					+ name+ "   "+ roomNo+ "   "+ serviceFee+"\n";				
            }    
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	public String search12() {
		String result = "";
		try{			
       		CallableStatement proc = 
       			connector.prepareCall("{ call sp_12(?) }"); 
       		proc.registerOutParameter(1,Types.INTEGER);            
        	proc.execute(); 
        	//返回入住顾客数
        	int outParam = proc.getInt(1);     // OUT parameter
			result=outParam+"";
        	proc.close();        	
        }catch (Exception e) {
            e.printStackTrace();//把该 Throwable 和它的跟踪情况打印到标准错误流。
        }        
		return result;
	}
	public String search13() {
		String result = "";		
		try {
			CallableStatement proc = connector
					.prepareCall("{ call sp_13 }");			
			proc.execute();
			ResultSet resultset = proc.getResultSet();				
			while (resultset.next()) {
				String nationality=resultset.getString("nationality");
				int guestCount = resultset.getInt("guestCount");
				result = result +"   "
					+ nationality+ "   "+ guestCount+"\n";					
            }    
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	public String search14() {
		String result = "";		
		try {
			CallableStatement proc = connector
					.prepareCall("{ call sp_14(?) }");	
			String roomNo="Room4";		//改变房间编号查询
			proc.setString(1,roomNo);
			proc.execute();
			ResultSet resultset = proc.getResultSet();				
			while (resultset.next()) {
				String enrolGuestNo=resultset.getString("enrolGuestNo");
				String serviceNo=resultset.getString("serviceNo");
				String serviceName=resultset.getString("serviceName");
				int serviceFee = resultset.getInt("serviceFee");
				result = result +"   "
					+ enrolGuestNo+ "   "+ serviceNo
					+ "   "+ serviceName+"   "+ serviceFee
					+"\n";					
            }    
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	public String search15() {
		String result = "";
		return result;
	}
}