一对多的查询显示

1、controller层

@RequestMapping(value = { "/list" }, method = { RequestMethod.GET })
	public String list(Pageable pageable,HttpServletRequest request, ModelMap model) {
		Map<String, Object> paramter = ParameterUtils.getParametersMapStartingWith(request, "filter_");
		Map map=new HashMap();
		  for (String key : paramter.keySet()) {  
	            if(!StringUtil.isEmpty( paramter.get(key).toString())){
	            	map.put(key,  paramter.get(key));
	            }
	        }
		  pageable.setParameter(map);
		  Page<TSpecification> page = this.tSpecificationService.findByPage(pageable);
		  for (TSpecification tSpecification : page.getContent()) {//这种情况是循环查，比较耗性能比如有20个规格，你就要操作数据库21次//改进方法用左外连接+分组查询得到值拼接，
			  tSpecification.setSpecificationValues(specificationValueService.findList("specificationId",tSpecification.getId()));
		  }
		model.addAttribute("paramter", paramter);
		model.addAttribute("page", page);
		return "/admin/tspecification/list";
	}

 

2、页面中

       

<td>
					                         [#if tSpecification.specificationValues??]
					                         [#list tSpecification.specificationValues as item]
                                                 ${(item.numerical)!''};
                                              [/#list]
                                              [/#if]
                                              </td>

 上述就能完成一对多的查询，后面是优化后的sql语句

SELECT
	t.*, GROUP_CONCAT(s.numerical, ';') AS zhi
FROM
	t_specification t
LEFT JOIN t_specification_value s ON t.id = s.specification_id
GROUP BY
	s.specification_id