国航移动客舱项目SSH服务器端HIBERNATE的部分ORACLE查询

流程大致如下：

通过航班号和出发日期（可能有出发时间）查询flight表获取主键flight_ID，

flight_ID通过中间表user_flight查询到join_id，

通过join_id可以分别查询到User（用户表）和bag（行李表）中的相关信息

查询航班

action层：

	public void queryFlight(){
		//输入参数是fltNbr，dptDate [dptTime]
		System.out.println("fltNbr="+fltNbr+"; dptDate="+dptDate);		
		try {
			ResultInfo result = flightService.queryFlight(fltNbr, dptDate, dptTime);			
			doOutput((new JSONObject().fromObject(result)).toString());
		} catch (Exception e) {
			e.printStackTrace();
		}		
	}

	public ResultInfo queryFlight(String fltNbr, String dptDate, String dptTime) throws Exception {
		/**
		 *  主要的处理逻辑：
		 * 1、根据输入的 航班号、出发日期 和 出发时间 查询航班信息，
		 * 2、将查询结果以JSON格式保存到对应的文件
		 * 3、将JSON文件FTP到对应的文件目录//
		 * 4、最后返回操作结果, 
		 *    如果有新的文件成功生成 code=TRUE msg=FTP路径和文件名；
		 *    如果没有新文件生成、生成文件操作失败 或FTP操作失败  code=FLASE msg=具体解释
		 */
		ResultInfo rstInfo = new ResultInfo();
		List<AirFlight> resultList = flightDao.queryFlight(fltNbr, dptDate, dptTime);
		String jsonContent = "";		
		if(resultList != null && resultList.size()>0){		
			JSONArray jsonArray = new JSONArray().fromObject(resultList);
			jsonContent = jsonArray.toString();
		}else{
			jsonContent = "N/A";
		}
		
		/**处理文件 */
		if("N/A".equals(jsonContent)){
			rstInfo.setCode("FALSE");
			rstInfo.setMsg("没有查到对应的航班信息");
		}else{
			String md5Str = MD5Helper.StringToHash(fltNbr+"-"+dptDate);
			String dataFileName = doJSONFileWriteAndFTP(md5Str, "flight_data", jsonContent);
			rstInfo.setCode("TRUE");
			rstInfo.setMsg(dataFileName);
		}	
		return rstInfo;
	}

DAO层

	public List<AirFlight> queryFlight(String fltNbr, String dptDate, String dptTime) {
		String queryHQL = " from AirFlight where fltNbr = ? and dptDt = ?";
		Object[] value;
		if(dptTime != null && dptTime.length()>0){
			queryHQL = queryHQL +" and dptTm = ?";
			value = new Object[]{fltNbr,dptDate,dptTime};
		}else{
			value = new Object[]{fltNbr,dptDate};
		}
		return (List<AirFlight>)this.getHibernateTemplate().find(queryHQL,value);
	}

查询用户：

ACTION层：

	private String joinId;

	public void queryUser(){
		System.out.println("joinId="+joinId);		
		try {
			ResultInfo result = flightService.queryUser(fltId);			
			doOutput((new JSONObject().fromObject(result)).toString());
		} catch (Exception e) {
			e.printStackTrace();
		}	
	}

public ResultInfo queryUser(String fltId) throws Exception {
		ResultInfo rstInfo = new ResultInfo();
		List<AirUser> resultList = flightDao.queryUser(fltId);
		String jsonContent = "";
		
		if(resultList != null && resultList.size()>0){		
			JSONArray jsonArray = new JSONArray().fromObject(resultList);
			jsonContent = jsonArray.toString();
		}else{
			jsonContent = "N/A";
		}
		
		/**处理文件 */
		if("N/A".equals(jsonContent)){
			rstInfo.setCode("FALSE");
			rstInfo.setMsg("没有查到对应的乘客信息");
		}else{
			String md5Str = MD5Helper.StringToHash("");
			String dataFileName = doJSONFileWriteAndFTP(md5Str, "user_data", jsonContent);
			rstInfo.setCode("TRUE");
			rstInfo.setMsg(dataFileName);
		}	
		return rstInfo;
	}

	public List<AirUser> queryUser(String fltId) {
		String queryHQL = "select t3 from AirUser t3, AirUserFlight t2 where t2.joinId=t3.joinId and t2.fltId = ?";
		Object[] value = new Object[]{fltId};
		return (List<AirUser>)this.getHibernateTemplate().find(queryHQL,value);
	}

action层：

	private String fltId;
	public void queryBaggage(){
		System.out.println("fltId="+fltId+"; joinId="+joinId);		
		try {
			ResultInfo result = flightService.queryAirBaggage(joinId, fltId, fltNbr);			
			doOutput((new JSONObject().fromObject(result)).toString());
		} catch (Exception e) {
			e.printStackTrace();
		}	
	}

	public ResultInfo queryAirBaggage(String joinId, String fltId,String fltNbr) throws Exception {
		ResultInfo rstInfo = new ResultInfo();
		List<AirBaggage> resultList = flightDao.queryAirBaggage(joinId, fltId, fltNbr);
		String jsonContent = "";
		
		if(resultList != null && resultList.size()>0){		
			JSONArray jsonArray = new JSONArray().fromObject(resultList);
			jsonContent = jsonArray.toString();
		}else{
			jsonContent = "N/A";
		}
		
		/**处理文件 */
		if("N/A".equals(jsonContent)){
			rstInfo.setCode("FALSE");
			rstInfo.setMsg("没有查到对应的行李信息");
		}else{
			String md5Str = MD5Helper.StringToHash("");
			String dataFileName = doJSONFileWriteAndFTP(md5Str, "baggage_data", jsonContent);
			rstInfo.setCode("TRUE");
			rstInfo.setMsg(dataFileName);
		}	
		return rstInfo;
	}

	public List<AirBaggage> queryAirBaggage(String joinId, String fltId,String fltNbr) {
		String queryHQL = "from AirBaggage t4 where t4.fltId = ? and t4.joinId= ?";
		Object[] value = new Object[]{fltId,joinId};
		return (List<AirBaggage>)this.getHibernateTemplate().find(queryHQL,value);
	}