2019.3.20日，记。请求参数之链式编程，数据库统计设计

最新推荐文章于 2024-01-23 22:23:24 发布

原创最新推荐文章于 2024-01-23 22:23:24 发布 · 219 阅读

CC 4.0 BY-SA版权

博客分享代码优化经验，面对大量if语句和addobject，采用链式编程并通过内部类封装优化。若多个action使用，用父类泛型加反射抽出公共部分，子类继承父类，只需编写业务部分。还记录了用sum函数统计数据及group by合并最新记录的情况。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

先上原代码

@RequestMapping(value = "creditChannelList")
	@RequiresPermissions(value={"creditManager:credit_channel_list"})
	public ModelAndView creditChannelList(HttpServletRequest request, String code, String channelType, String title, String url, 
			String productCode, String startTime, String endTime, String channelStartTime, String channelEndTime, String status) {
		CreditChannelPage page = new CreditChannelPage();
		
		if (Utils.isNotBlank(status)) {
			page.setStatus(Integer.parseInt(status));
		}
		
		if (Utils.isNotBlank(code)) {
			page.setCode(code);
		}
		if (Utils.isNotBlank(channelType)) {
			page.setChannelType(Integer.parseInt(channelType));
		}
		if (Utils.isNotBlank(title)) {
			page.setTitle(title);
		}
		if (Utils.isNotBlank(url)) {
			page.setUrl(url);
		}
		if (Utils.isNotBlank(productCode)) {
			page.setProductCode(productCode);
		}
		if (Utils.isNotBlank(startTime)) {
			page.setStartTimeStr(startTime);
			//condition.append(" and DATE_FORMAT(r.created_time,'%Y/%m/%d') >= '" + startTime + "' ");
		}
		if (Utils.isNotBlank(endTime)) {
			page.setEndTimeStr(endTime);
			//condition.append("and DATE_FORMAT(r.created_time,'%Y/%m/%d') <= '" + endTime + "' ");
		}
		if (Utils.isNotBlank(channelStartTime)) {
			page.setChannelStartTimeStr(channelStartTime);
			//condition.append(" and DATE_FORMAT(r.start_time,'%Y/%m/%d') <= '" + channelStartTime + "' ");
		}
		if (Utils.isNotBlank(channelEndTime)) {
			page.setChannelEndTimeStr(channelEndTime);
			//condition.append("and DATE_FORMAT(r.end_time,'%Y/%m/%d') >= '" + channelEndTime + "' ");
		}

		// 设置默认排序方式
		SystemContext.setOrderExpression("created_time desc");

		Page<CreditChannel> creditChannels = creditChannelService.selectPageByCondition(
				CreditChannel.TABLE_NAME + ".selectPageByAdminCondition", page);
		Map<String, Object> map = creditChannelService.selectOne(
				CreditChannel.TABLE_NAME + ".selectSumAndCountByAdminCondition", page);

		ModelAndView modelAndView = new ModelAndView("/credit/creditChannelList");
		if (null != map) {
			modelAndView.addObject("totalAmt",
					map.get("totalAmt") == null ? "0.00" : map.get("totalAmt"));
			modelAndView
					.addObject(
							"totalCount",
							map.get("totalCount") == null ? "0" : map
									.get("totalCount"));
		}		
		
		modelAndView.addObject("creditChannels", creditChannels);
		modelAndView.addObject("status", status);
		modelAndView.addObject("code", code);
		modelAndView.addObject("channelType", channelType);
		modelAndView.addObject("title", title);
		modelAndView.addObject("url", url);
		modelAndView.addObject("productCode", productCode);
		modelAndView.addObject("startTime", startTime);
		modelAndView.addObject("endTime", endTime);
		modelAndView.addObject("channelStartTime", channelStartTime);
		modelAndView.addObject("channelEndTime", channelEndTime);
		return modelAndView;
	}

当看到大量的if语句跟addobject时，表示内心是崩溃的

因此想到的优化方式是链式编程,采用内部类对其进行封装

/**
     * 查询条件的封装类
     */
    class  CreditChannelPageBuild{
        //返回的分页数据
        CreditChannelPage page = new CreditChannelPage();
        //返回的ModelAndView
        ModelAndView modelAndView  = new ModelAndView();
        public CreditChannelPageBuild setTitle(String title){
            if (Utils.isNotBlank(title)) {
                page.setTitle(title);
                modelAndView.addObject("title", title);
            }
            return this;
        }
        public CreditChannelPageBuild setStartTime(String startTime){
            if (Utils.isNotBlank(startTime)) {
                page.setStartTimeStr(startTime);
                modelAndView.addObject("startTime", startTime);
            }
            return this;
        }
        public CreditChannelPageBuild setEndTime(String endTime){
            if (Utils.isNotBlank(endTime)) {
                page.setEndTimeStr(endTime);
                modelAndView.addObject("endTime", endTime);
            }
            return this;
        }
        public CreditChannelPageBuild setStatus(String status){
            if (Utils.isNotBlank(status)) {
                page.setStatus(Integer.parseInt(status));
                modelAndView.addObject("status", status);
            }
            return this;
        }
        public CreditChannelPageBuild setCode(String code){
            if (Utils.isNotBlank(code)) {
                page.setCode(code);
                modelAndView.addObject("code", code);
            }
            return this;
        }
        public CreditChannelPageBuild setChannelType(String channelType){
            if (Utils.isNotBlank(channelType)) {
                page.setChannelType(Integer.parseInt(channelType));
                modelAndView.addObject("channelType", channelType);
            }
            return this;
        }
        public CreditChannelPageBuild setUrl(String url){
            if (Utils.isNotBlank(url)) {
                page.setUrl(url);
                modelAndView.addObject("url", url);
            }
            return this;
        }
        public CreditChannelPageBuild setModelAndViewData(String key,Object value){
            modelAndView.addObject(key, value);
            return this;
        }
        public CreditChannelPageBuild setModelAndViewToViewName(String viewName){
            modelAndView.setViewName(viewName);
            return this;
        }
        public ModelAndView getModelAndView(){
            return modelAndView;
        }
        public CreditChannelPage getCreditChannelPage(){
            return page;
        }
    }

优化后

@RequestMapping(value = "creditChannelList")
	@RequiresPermissions(value={"creditManager:credit_channel_list"})
	public ModelAndView creditChannelList(String code, String channelType, String title, String url,
			String startTime, String endTime, String status) {
	    //跟据查询条件获取分页对象
        CreditChannelPageBuild creditChannelPageBuild = new CreditChannelPageBuild();
        //初始化对象,设置参数
        CreditChannelPage page = creditChannelPageBuild.setTitle(title).setStartTime(startTime)
                .setEndTime(endTime).setStatus(status).setCode(code).setChannelType(channelType)
                .setUrl(url).getCreditChannelPage();
		// 设置默认排序方式
		SystemContext.setOrderExpression("created_time desc");
		Page<CreditChannel> creditChannels = creditChannelService.selectPageByCondition(
				CreditChannel.TABLE_NAME + ".selectPageByAdminCondition", page);
		Map<String, Object> map = creditChannelService.selectOne(
				CreditChannel.TABLE_NAME + ".selectSumAndCountByAdminCondition", page);

		if (null != map) {
            creditChannelPageBuild
                    .setModelAndViewData("totalAmt",map.get("totalAmt") == null ? "0.00" : map.get("totalAmt"))
                    .setModelAndViewData("totalCount", map.get("totalCount") == null ? "0" : map.get("totalCount"));
		}
		return creditChannelPageBuild
                .setModelAndViewData("creditChannels",creditChannels)
                .setModelAndViewToViewName("/credit/creditChannelList")
                .getModelAndView();
	}

我确定看起来觉得舒服多了，然而还有个问题，那就是如果有多个action用到这个的话，每次写不是很累，而且大部是有重复的地方？

因此可以采用父类泛型加反射的方式将公共的东西再一次把公共的东西抽出来

先抽出父类，

public  class BasePageBuild<T> {
    protected T page;
    //返回的ModelAndView
    protected ModelAndView modelAndView  = new ModelAndView();
    public BasePageBuild(){
         //通过反射将对象实例化,Spring4,新添加的ResolvableType工具类等价于下面的方法
        Class clazz = ResolvableType.forType(this.getClass()).getSuperType()
                    .getGeneric(0).resolve();
        try {
            page = (T)clazz.newInstance();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
//        java自带的反射写法
//        ParameterizedType ptype = (ParameterizedType) this.getClass().getGenericSuperclass();
//        Class clazz1 = (Class<T>) ptype.getActualTypeArguments()[0];
//        try {
//            page = (T) clazz.newInstance();
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
    }
    public BasePageBuild setModelAndViewData(String key, Object value){
        modelAndView.addObject(key, value);
        return this;
    }
    public BasePageBuild setModelAndViewToViewName(String viewName){
        modelAndView.setViewName(viewName);
        return this;
    }
    public ModelAndView getModelAndView(){
        return modelAndView;
    }
    public  T getBasePage(){
        return page;
    }
}

然后子类去继承这个父类

 class  CreditChannelPageBuild extends BasePageBuild<CreditChannelPage> {
        //返回的ModelAndView
        ModelAndView modelAndView  = new ModelAndView();
        public CreditChannelPageBuild setTitle(String title){
            if (Utils.isNotBlank(title)) {
                page.setTitle(title);
                modelAndView.addObject("title", title);
            }
            return this;
        }
        public CreditChannelPageBuild setStartTime(String startTime){
            if (Utils.isNotBlank(startTime)) {
                page.setStartTimeStr(startTime);
                modelAndView.addObject("startTime", startTime);
            }
            return this;
        }
        public CreditChannelPageBuild setEndTime(String endTime){
            if (Utils.isNotBlank(endTime)) {
                page.setEndTimeStr(endTime);
                modelAndView.addObject("endTime", endTime);
            }
            return this;
        }
        public CreditChannelPageBuild setStatus(String status){
            if (Utils.isNotBlank(status)) {
                page.setStatus(Integer.parseInt(status));
                modelAndView.addObject("status", status);
            }
            return this;
        }
        public CreditChannelPageBuild setCode(String code){
            if (Utils.isNotBlank(code)) {
                page.setCode(code);
                modelAndView.addObject("code", code);
            }
            return this;
        }
        public CreditChannelPageBuild setChannelType(String channelType){
            if (Utils.isNotBlank(channelType)) {
                page.setChannelType(Integer.parseInt(channelType));
                modelAndView.addObject("channelType", channelType);
            }
            return this;
        }
        public CreditChannelPageBuild setUrl(String url){
            if (Utils.isNotBlank(url)) {
                page.setUrl(url);
                modelAndView.addObject("url", url);
            }
            return this;
        }
    }

class  CreditApplyToLoanPageBuild extends BasePageBuild<CreditApplyToLoanPage> {
        //返回的分页数据
        public CreditApplyToLoanPageBuild setMobile(String mobile){
            if (Utils.isNotBlank(mobile)) {
                page.setMobile(mobile);
                modelAndView.addObject("mobile", mobile);
            }
            return this;
        }
        public CreditApplyToLoanPageBuild setStartTime(String startTime){
            if (Utils.isNotBlank(startTime)) {
                page.setStartTimeStr(startTime);
                modelAndView.addObject("startTime", startTime);
            }
            return this;
        }
        public CreditApplyToLoanPageBuild setEndTime(String endTime){
            if (Utils.isNotBlank(endTime)) {
                page.setEndTimeStr(endTime);
                modelAndView.addObject("endTime", endTime);
            }
            return this;
        }
    }

这样就可以保证只需要编写业务部分就可以，真是不要太爽！！！

=============================================================

记一次采用sum 函数对数据进行统计

select sum(IFNULL(r.invite_count,0)) as totalAmt,
         count(1) totalCount ,IFNULL(rtb.aNum,0) aNum,
         IFNULL(rtb.bNum,0) bNum, IFNULL(rtb.cNum,0) cNum,
         IFNULL(rtb.dNum,0) dNum
        from table r left join
		( select channel_id,
			sum(if(oper_type='a',1,0)) aNum,
			sum(if(oper_type='b',1,0)) bNum,
			sum(if(oper_type='c',1,0)) cNum,
			sum(if(oper_type='d',1,0)) dNum
			from table2 ) rtb on rtb.channel_id=r.id

记一次当使用group by 时想让它合并的数据是记录里面最新的一条时

select * from (
    select * from table order by create_time desc
) as t
group by t.user_id;