把一张表中的数据即一个List作为查询另一张表的条件参数

来源：互联网发布：java中布尔编辑：程序博客网时间：2024/05/16 10:58

XML查询语句：

<select id="getMatterFunForeach" parameterType="Map" resultType="hashmap">  select * from(select m.mattertitle as mattertitle,<foreach collection="list" index="index" item="item" separator="," > count(decode(f.funcode, '${item.funcode}', 1)) as ${item.funcode}isnot    </foreach>from os_fun f join os_matter_fun mf    on f.funcode = mf.funcode   and f.funname = mf.funname  join os_matter m    on m.matterid = mf.matteridgroup by m.matterid,m.mattertitle)<where><if test="funcode!=null and funcode!=''">and ${funcode}ISNOT >0</if></where>  </select>

SpringMVC控制层：

@RequestMapping(value = "")public ModelAndView LoadMatteropen(@RequestParam(required = false,defaultValue="1") String pageNum,@RequestParam(required = false,defaultValue="20") String pageSize,@RequestParam(required = false,defaultValue="")String funcode,HttpServletRequest request, HttpServletResponse response) throws Exception {ModelAndView result = null;Map<String, Object> map = new HashMap<String, Object>();String userid = getLoginUserId(request);SysUserOrg suo = sysUserOrgService.findOrgByUserId2(userid);WorkOrg wo = null;if(suo!=null){wo= workOrgService.getWorkOrgByOrgcode(suo.getOrgCode());}int orgtype =getLoginUserInfo(request, response).getUser().getOrgtype();//获取user对象中的orgtypeif(orgtype==1){result = new ModelAndView("");result.addObject("funcode", funcode);List<OsFun> funList = osFunService.getOsFunList();if(funList.size()>0){result.addObject("funList", funList);}map.put("funcode", funcode);map.put("list", funList);Page<Map<String,Object>> mapList = osMatterService.getMatterFunForeach(map, new PageUtil(pageNum,pageSize)); result.addObject("mapList", mapList);setPageInfo(result,mapList,"",map);}result.addObject("orgtype", orgtype);load(result, request, response);return result;}

freemarker页面代码：

<table class="listtable" align="center" cellpadding="0" cellspacing="1" border="0"><tr class="header"><td width="30" class="center">No</td><td class="center">局委名称</td><td width="60" class="center">办事项总数</td><#list funList as fun><td width="53" class="center">${(fun.funname)!}</td></#list></tr><!-- loop begin --><#assign i=1/><#if mapList??><#assign totalcount=0/><#list mapList as map><tr <#if map_has_next>class="trow"<#else>class="header"</#if>><#if map_has_next><td <#if map_has_next>class="center tno" <#else>class="right ttext" colspan="2"</#if>>${i}</td><#else></#if><td <#if map_has_next>class="left ttext"<#else>class="right ttext" colspan="2"</#if>>${(map['ORGNAME'])!}</td> <td class="center tlink"><#if map_has_next><a href="${RootPath}x.html?orgid=${(map['ORGID'])!}"><#if (map['COUNT'])>0>${(map['COUNT'])!}个</#if></a><#assign totalcount=totalcount+map['COUNT']/><#else><a href="${RootPath}x.html"><#if totalcount>0>${totalcount}个</#if></a></#if></td> <!-- <#list map?keys as itemKey><td class="center tlink"><a href="${RootPath}x.html?orgid=${(map['ORGID'])!}">${map[itemKey]}</a></td></#list>--> <#list funList as fun><td class="center tlink"><a href="${RootPath}x.html?<#if map_has_next>orgid=${(map['ORGID'])!}&</#if>funcode=${fun.funcode?upper_case}"><#if (map['${fun.funcode?upper_case}COUNT'])>0>${(map['${fun.funcode?upper_case}COUNT'])!}个</#if></a></td></#list></tr><#assign i++/></#list></#if><!-- loop end --></table>

0 0