多表级联查询

            DataPage dataPage = this.getDataPage(request);
            String orderBy = request.getParameter(ContextArgument.GRID);
            SH_AREAS sh_areas = this.bean(request, SH_AREAS.class, false);
            
            sh_areas = service.getPcid(sh_areas); 
            DataTable table = service.table(SH_AREASSql.class,sh_areas, orderBy,dataPage);
            FormSource source = this.source(request);
            source.add(ContextArgument.GRID, table, orderBy );
            source.add(ContextArgument.PGINFO, dataPage );
            source.put(modelMap);

　　

 

public DataTable getArea() throws Exception {
		DataTable result = null;

		SqlCommand command = new SqlCommand();
		command.setCommandText(SH_AREASSql.cascade);
		result = this.sqlProvider().executeQuery(command);
		return result;

	}

	public SH_AREAS getPcid(SH_AREAS sh_areas) {
		if (!NoneType.isNullOrEmpty(sh_areas.getTEMP1())) { // 将级联属性赋给临时字段TEMP1
			String split = ",";
			String[] areasid = StringUtil.split(sh_areas.getTEMP1(), split);
			if (areasid.length > 1) {
				if (!NoneType.isNullOrEmpty(areasid[0])) {
					sh_areas.setTEMP2(areasid[0]); // areasid[0] 省份码
					sh_areas.setCITYID(areasid[1]); //areasid[1] 城市码
				} else {
					sh_areas.setCITYID(areasid[1]);
				}

			} else {
				sh_areas.setTEMP2(areasid[0]);
			}
		}
		return sh_areas;
	}

　　

/**
	 * table方法所需的SQL语句
	 */
	public static final String table = "SELECT SH_AREAS.ID, SH_PROVINCE.PNAME, SH_CITIES.CNAME, SH_AREAS.ANAME "   
									 + "FROM SH_PROVINCE "
									 + "RIGHT JOIN SH_CITIES ON SH_CITIES.PROVINCEID = SH_PROVINCE.PROVINCEID "
									 + "RIGHT JOIN SH_AREAS ON SH_AREAS.CITYID = SH_CITIES.CITYID "
									 + "WHERE {SH_PROVINCE.PROVINCEID = SH_AREAS.TEMP2 } "
									 + "AND {SH_CITIES.CITYID  = SH_AREAS.CITYID } ";
	
	/**
	 * cascade方法级联获取省市的SQL语句
	 */
	public static final String cascade = "SELECT SH_PROVINCE.PROVINCEID  AS VAL ,  SH_PROVINCE.ALEVEL AS ALEVEL, " 
									   + "SH_PROVINCE.PARENT_ID AS MODUL, SH_PROVINCE.PNAME AS KEY "
									   + "FROM SH_PROVINCE " 
									   + "UNION SELECT SH_CITIES.CITYID , SH_CITIES.ALEVEL, SH_CITIES.PROVINCEID ,SH_CITIES.CNAME " 
									   + "FROM  SH_CITIES";
	}