tree的json的生成方法

前段时间工作中需要使用树，这一点，在前台用ext很好实现，只是这个tree的store的这个json在后台拼写的时候有些费了我的脑筋，下面总结一下，我发现很好用的一个方法：

（哦，亲们，这些代码里，数据是从不同数据源获得的，所以在代码里体现了切换数据源的代码，如果你不需要切换数据源，没有必要这个做哦，只需要看sql的组成和返回list就行了。）

sql:

Java代码

1. public List FindComboTreeDataBySubjectId(String subjectId, List<String> paramList,List constantValue) {
2. //权限表的数据及主题
3. String pmsSql = reportPermissionService.getPmsSql(constantValue);
4. Subject pmsSubject = reportPermissionService.getPmsSubject();
6. Subject subject = this.subjectDomainService.getSubject(subjectId);
7. if(subject==null||"".equals(subject)){
8. return new ArrayList();
9. }
11. String subTable = subject.getSubTable();
12. String ds = subject.getDataSource().toString();
13. // 切换到给定的数据源
14. HotSwappableTargetSource swapper = (HotSwappableTargetSource)app.getBean("swappableDataSource");
15. DataSource dataSource = (DataSource)app.getBean(ds);
16. swapper.swap(dataSource);
17. // 查询元数据
18. List result = new ArrayList();
19. String sql = "SELECT ID AS COMPANYID, ID,PID,NAME,NODETYPE,YEARMONTH FROM "+subTable+" WHERE 1=1 ";
20. if(pmsSubject !=null && pmsSubject.getDataSource().toString().equals(ds) && pmsSql !=""){
21. sql = "SELECT T.ID AS COMPANYID,T.ID,T.PID,T.NAME,T.NODETYPE,T.YEARMONTH, DECODE(PMS.ID,NULL,0,1) AS PERMIS FROM "+subTable + " T LEFT JOIN" + pmsSql + " ON T.ID = PMS.ID WHERE 1=1 ";
22. }
24. if(paramList != null){
25. Iterator paramItem = paramList.iterator();
26. while(paramItem.hasNext()){
27. String param = paramItem.next().toString();
28. if(!"".equals(param)||param!="")
29. sql +=" and "+param+" ";
30. }
31. }
32. sql = "SELECT T.*,LEVEL FROM ("+ sql + ")T start with t.pid is null connect by prior t.id = t.PID order by level,T.NAME desc";
33. SQLQuery query = getSession().createSQLQuery(sql);
34. List list = query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
35. return list;
37. }

public List FindComboTreeDataBySubjectId(String subjectId, List<String> paramList,List constantValue) {
		//权限表的数据及主题
		String pmsSql = reportPermissionService.getPmsSql(constantValue);
		Subject pmsSubject = reportPermissionService.getPmsSubject();
		
		Subject subject = this.subjectDomainService.getSubject(subjectId);
		if(subject==null||"".equals(subject)){
			return new ArrayList();
		}
		
		String subTable = subject.getSubTable();
		String ds = subject.getDataSource().toString();
		// 切换到给定的数据源
		HotSwappableTargetSource swapper = (HotSwappableTargetSource)app.getBean("swappableDataSource");
		DataSource dataSource = (DataSource)app.getBean(ds);
		swapper.swap(dataSource);
		// 查询元数据
		List result = new ArrayList();	
		String sql = "SELECT ID AS COMPANYID, ID,PID,NAME,NODETYPE,YEARMONTH FROM "+subTable+" WHERE 1=1 ";
		if(pmsSubject !=null && pmsSubject.getDataSource().toString().equals(ds) && pmsSql !=""){
			sql = "SELECT T.ID AS COMPANYID,T.ID,T.PID,T.NAME,T.NODETYPE,T.YEARMONTH, DECODE(PMS.ID,NULL,0,1) AS PERMIS FROM "+subTable + " T LEFT JOIN" + pmsSql + " ON T.ID = PMS.ID WHERE 1=1 ";
		}
		
		if(paramList != null){
			Iterator paramItem = paramList.iterator();
			while(paramItem.hasNext()){
				String param = paramItem.next().toString();
				if(!"".equals(param)||param!="")
					sql +=" and "+param+" ";
			}
		}
		sql = "SELECT T.*,LEVEL FROM ("+ sql + ")T start with t.pid is null connect by prior t.id = t.PID order by level,T.NAME desc";
		SQLQuery query = getSession().createSQLQuery(sql);
		List list = query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
		return list;

	}