本文主要是介绍2019.3.20日，记。请求参数之链式编程，数据库统计设计，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
先上原代码
@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();//返回的ModelAndViewModelAndView 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;//返回的ModelAndViewprotected 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> {//返回的ModelAndViewModelAndView 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) dNumfrom 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)) dNumfrom 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;
这篇关于2019.3.20日，记。请求参数之链式编程，数据库统计设计的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
