easyUI sql 构建树treegrid

<table id="tableTreeGrid" class="easyui-treegrid" 
           data-options="
url:'',
        method:'post',
        rownumbers: false,
animate: true,
collapsible:false,
fitColumns:true,
fit:true,
height:'auto',
autoRowHeight: false,
        idField:'ID',
        treeField:'NAME'
        ">  
        <thead>
        <tr>
        <sec:accesscontrollist hasPermission="3" domainObject="orginfoLicy_treegrid_table_NAME">
        <th data-options="field:'NAME',halign:'center'" width="220" title="项目名称">项目名称</th>
        </sec:accesscontrollist>
        </tr>
        </thead>  

</table>

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

function searchTree(){
		var param;
		var json;
		param = $('#toFeedBackNewSelect').serializeArray();
		json = convertToJson(param);
		param = JSON.stringify(json);
		//$('#tableTreeGrid').treegrid('load',{param:param});
		$('#tableTreeGrid').treegrid({    
		    url:'platform/FeedBackNewController/getTreeDataGrid?param='+param,
			//在数据加载成功后选择相应节点
			onLoadSuccess:function(row, data){
					for(var i = 0;i < data['rows'].length;i++){
						if(data['rows'][i].TASK_TYPE!=null){
							//selectNode(data['rows'][i].ID);
							break;
						}
					}
			},
			onClickRow : function(node) {
					if(node.TASK_TYPE != 0){
						$('#buttonImportForm').linkbutton('disable');
						$('#buttonAddFormRightTop').linkbutton('disable');
						$('#buttonCloseFormRightTop').linkbutton('disable');
					}
					else{
						$('#buttonImportForm').linkbutton('enable');
						$('#buttonAddFormRightTop').linkbutton('enable');
						$('#buttonCloseFormRightTop').linkbutton('enable');
					}
					taskId = node.ID;
					$('#taskId').val(node.ID);
					$('#pmTaskInfoIdMpmFeekbackLd').val(node.ID);
					$('#pmTaskInfoIdMpmProgressLd').val(node.ID);
					$('#userId').val(node.USER_ID);
					$('#pmProjectId').val(node.PM_PROJECT_ID);
					$('#pmProjectIdMpmFeekbackLd').val(node.PM_PROJECT_ID);   //给右侧MpmFeekbackLddatagrid的项目id赋值
					chaxunMpmFeekbackLd();
					chaxunMpmProgressLd();
			}
		});  
		return;

	}
	

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

ACTION层

@SuppressWarnings({ "unchecked", "unused" })
@RequestMapping("/getTreeDataGrid")
public ModelAndView getTreeDataGrid(String id, HttpServletRequest request)
throws Exception {


ModelAndView mav = new ModelAndView();
String json = ServletRequestUtils.getStringParameter(request, "param");
Map<String, Object> map = new HashMap<String, Object>();
map = JsonUtils.newInstance().Json2Bean2Form(json, Map.class);
// 获取到从页面传过来的任务状态、计划开始时间和结束时间 并放入到 map集合中
Map<String, Object> startAndEndTime = new HashMap<String, Object>();
// 获取到当前时间所在周的第一天和最后一天
Map<String, Object> weekDay = feedBackNewService
.getNowWeekFirstDayAndEndDay();
// id = map.get("id")+"";
String taskStatus = map.get("taskStatus") + "";
String planStartTime = map.get("planStartTime") + "";
String planEndTime = map.get("planEndTime") + "";
String pmProjectId = map.get("pmProjectId") + "";
String ids = map.get("id") + "";
String loginUserId = SessionHelper.getLoginSysUserId();
// 如果从页面传有任务状态的值就取前台传递过来的 否则任务状态为0
if (taskStatus != null && !"".equals(taskStatus)) {
startAndEndTime.put("taskStatus", taskStatus);
} else {
startAndEndTime.put("taskStatus", "0");
}
// 如果从页面传有计划开始时间的值就取前台传递过来的 否则计划开始时间为当前周的第一天
if (planStartTime != null && !"".equals(planStartTime)) {
startAndEndTime.put("planStartTime", planStartTime);
} else {
// 默认情况下给map对象赋值 weekDay.firstweekDay lastweekDay
startAndEndTime.put("planStartTime", weekDay.get("firstweekDay"));
}
// 如果从页面传有计划结束时间的值就取前台传递过来的 否则计划结束时间为当前周的最后一天
if (planEndTime != null && !"".equals(planEndTime)) {
startAndEndTime.put("planEndTime", planEndTime);
} else {
// 默认情况下给map对象赋值 weekDay.firstweekDay lastweekDay
startAndEndTime.put("planEndTime", weekDay.get("firstweekDay"));
}
if (pmProjectId != null && !"".equals(pmProjectId)) {
startAndEndTime.put("pmProjectId", pmProjectId);
}
if (ids != null && !"".equals(ids)) {
startAndEndTime.put("id", ids);
}
startAndEndTime.put("loginUserId", loginUserId);
startAndEndTime.put("planProperty", "P");
startAndEndTime.put("proType", "1");


// List<Map<String, Object>> lists =
// this.feedBackNewService.getTreeGridData(startAndEndTime);
List<Map<String, Object>> lists = this.feedBackNewService.getProjectTree(startAndEndTime);
for (Map<String, Object> mapList : lists) {
if (mapList.get("PARENT_ID")!=null) {
mapList.put("_parentId", mapList.get("PARENT_ID") + "");
mapList.put("state","closed");
}
//task_type
String taskType = mapList.get("task_type") + "";
//设置图标
if(taskType!=null&&taskType.equals("1")){
mapList.put("iconCls","icon-mpm-wbs");
}
else if(taskType!=null&&taskType.equals("0")){
mapList.put("iconCls","icon-mpm-task");
}
else {
mapList.put("iconCls","icon-mpm-project");
}
String name = mapList.get("NAME") + "";
name = "<span id='formatterTaskNoteTooltip' title='"+name+"' class='easyui-tooltip' data-options='showDelay:0' >"+name+"</span>";
mapList.put("NAME",name);
mapList.put("state","open");
}
mav.addObject("rows", lists);
mav.setViewName(null);
return mav;
}

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

public List<Map<String, Object>> getProjectTree(Map<String, Object> parameters){
String sql = 
"select distinct(pt.id),pt.parent_id,pt.p_name as name,pt.task_type,pt.pm_project_id,pt.user_id from (select p.id as id,p.parent_id as parent_id,p.task_name as p_name,p.task_type,p.pm_project_id as pm_project_id,p.user_id as user_id  from pm_task_info p " +
"union " + 
"select pp.id as id,'' as parent_id, pp.project_name as name,'' as task_type,pp.id as pm_project_id,pp.user_id as user_id from pm_project pp where pp.plan_property = 'P' and pp.pro_type = '1') pt " + 
"start with pt.id in( " + 
"  select id from pm_task_info t1 where " + 
"     t1.plan_s_date >= to_date(?, 'yyyy-MM-dd') " + 
"     and t1.plan_e_date <= to_date(?, 'yyyy-MM-dd') " + 
"     and t1.user_id = ? " + 
") connect by prior pt.parent_id = pt.id";
List<Map<String, Object>> list = this.jdbcTemplate.queryForList(sql, new Object[]{parameters.get("planStartTime"),parameters.get("planEndTime"),parameters.get("loginUserId")});
return list;
}