Java程序猿搬砖笔记(二)
作为码农平时搜集一些小知识点个人认为是个不错的习惯,书上说
好记性不如烂笔头
我想即使是以前忽略或者新get的很简单的东西,自己动手记下来不管如何印象也会更深刻。
1、jQuery常用的选择器整理
$(":first");//匹配第一个元素
$(":last");//匹配最后一个元素
$(":eq(index)");//在匹配的集合中选择索引值为index的元素
$(":gt(index)");//在匹配的集合中选择索引值大于index的元素
$(":even");//选择索引值为偶数的元素,从0开始计数
$(":odd");//选择索引值为奇数的元素,从0开始计数
$(“parent>child”);//子选择器 :选择所有指定"parent"元素中指定的"child"的直接子元素
$(“ancestor decendant”);//后代选择器 :选择给定的祖先元素的所有后代元素,一个元素的后代可能是该元素的一个孩子,孙子,曾孙等
$(“prev +next”);//相邻兄弟选择器 :选择所有紧接在"prev"元素后的"next"元素
$(“prev ~sibings”);//一般兄弟选择器 :匹配"prev"元素之后的所有兄弟元素
2、MyBatis批量更新
sql列子:
1 2 3 4 5 6 7 8 9 10 11 12 UPDATE course SET name = CASE id WHEN 1 THEN 'name1' WHEN 2 THEN 'name2' WHEN 3 THEN 'name3' END , title = CASE id WHEN 1 THEN 'New Title 1' WHEN 2 THEN 'New Title 2' WHEN 3 THEN 'New Title 3' END WHERE id IN (1 ,2 ,3 )
在做编辑订单功能(现在想想都是一场噩梦)时,折腾了这个操作,java代码如下:
1 int updateBatch (@Param("detailList") List<ThOrderDetail> detailList) ;
注:MyBatis默认会把所有集合封装为"list",如果要自定义参数名需要用@Param注解
MyBatis代码如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 <update id ="updateBatch" parameterType ="java.util.List" > update th_order_detail <trim prefix ="set" suffixOverrides ="," > <trim prefix ="unit_price =case" suffix ="end," > <foreach collection ="detailList" item ="item" index ="index" > <if test ="item.unitPrice !=null" > when ord_detail_id=#{item.ordDetailId} then #{item.unitPrice} </if > <if test ="item.unitPrice == null" > when ord_detail_id=#{item.ordDetailId} then th_order_detail.unit_price </if > </foreach > </trim > <trim prefix ="negative_interval_percentage =case" suffix ="end," > <foreach collection ="detailList" item ="item" index ="index" > <if test ="item.negativeIntervalPercentage !=null" > when ord_detail_id=#{item.ordDetailId} then #{item.negativeIntervalPercentage} </if > <if test ="item.negativeIntervalPercentage == null" > when ord_detail_id=#{item.ordDetailId} then th_order_detail.negative_interval_percentage </if > </foreach > </trim > <trim prefix ="positive_interval_percentage =case" suffix ="end," > <foreach collection ="detailList" item ="item" index ="index" > <if test ="item.positiveIntervalPercentage !=null" > when ord_detail_id=#{item.ordDetailId} then #{item.positiveIntervalPercentage} </if > <if test ="item.positiveIntervalPercentage == null" > when ord_detail_id=#{item.ordDetailId} then th_order_detail.positive_interval_percentage </if > </foreach > </trim > <trim prefix ="negative_interval_quantity =case" suffix ="end," > <foreach collection ="detailList" item ="item" index ="index" > <if test ="item.negativeIntervalQuantity !=null" > when ord_detail_id=#{item.ordDetailId} then #{item.negativeIntervalQuantity} </if > <if test ="item.negativeIntervalQuantity == null" > when ord_detail_id=#{item.ordDetailId} then th_order_detail.negative_interval_quantity </if > </foreach > </trim > <trim prefix ="positive_interval_quantity =case" suffix ="end," > <foreach collection ="detailList" item ="item" index ="index" > <if test ="item.positiveIntervalQuantity !=null" > when ord_detail_id=#{item.ordDetailId} then #{item.positiveIntervalQuantity} </if > <if test ="item.positiveIntervalQuantity == null" > when ord_detail_id=#{item.ordDetailId} then th_order_detail.positive_interval_quantity </if > </foreach > </trim > </trim > where ord_detail_id in <foreach collection ="detailList" index ="index" item ="item" separator ="," open ="(" close =")" > #{item.ordDetailId} </foreach > </update >
执行代码后,打印出的sql如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 update th_order_detail set unit_price = case when ord_detail_id = 252 then 25.55 when ord_detail_id = 253 then 35.55 end , order_price = case when ord_detail_id = 252 then 383.25 when ord_detail_id = 253 then 639.9 end , negative_interval_percentage = case when ord_detail_id = 252 then 11.11 when ord_detail_id = 253 then 13.33 end , positive_interval_percentage = case when ord_detail_id = 252 then 12.2 when ord_detail_id = 253 then 14.44 end , negative_interval_quantity = case when ord_detail_id = 252 then 13.3335 when ord_detail_id = 253 then 15.6006 end , positive_interval_quantity = case when ord_detail_id = 252 then 16.83 when ord_detail_id = 253 then 20.5992 end where ord_detail_id in (252 , 253 ) ;
参考链接
3、POI导出Excel
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 public static void main (String[] args) throws IOException { HSSFWorkbook workbook = new HSSFWorkbook (); HSSFCellStyle style = workbook.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFSheet sheet = workbook.createSheet("sheet" ); HSSFRow row0 = sheet.createRow(0 ); HSSFCell cell_00 = row0.createCell(0 ); cell_00.setCellStyle(style); cell_00.setCellValue("日期" ); HSSFCell cell_01 = row0.createCell(1 ); cell_01.setCellStyle(style); cell_01.setCellValue("午别" ); HSSFRow row1 = sheet.createRow(1 ); HSSFCell cell_10 = row1.createCell(0 ); cell_10.setCellStyle(style); cell_10.setCellValue("20191103" ); HSSFCell cell_11 = row1.createCell(1 ); cell_11.setCellStyle(style); cell_11.setCellValue("上午" ); HSSFRow row2 = sheet.createRow(2 ); HSSFCell cell_21 = row2.createCell(1 ); cell_21.setCellStyle(style); cell_21.setCellValue("下午" ); CellRangeAddress region = new CellRangeAddress (1 , 2 , 0 , 0 ); sheet.addMergedRegion(region); File file = new File ("E:\\demo.xls" ); FileOutputStream fout = new FileOutputStream (file); workbook.write(fout); fout.close(); }
实现效果如下:
参考链接
项目中导出xcel例子:
客户给出的表格需要统计对上游付款总金额和对下游付款总金额(大于的为占用金额),然后根据这个占用金额去计算利息
有两个要求:1、第一行需要显示用款和付款 2、后面如果日期相同也要显示到同一行
这个用sql查询出来后还需要Java代码处理,总之这是今年做的最坑的需求之一(搞死开发者却不一定实用)。
后台打印出的sql如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 #分组前sql select * from ( (select p.amount as amount, ifnull(p.payment_date, p.add_time) as payment_date, p.contract_id as contract_id, c.type as type from payment_bond p inner join contract c on p.contract_id = c.contract_id and p.state != 2 and c.type in (1 , 2 ) and p.entrust_id = 116 ) union all (select s.amount as amount, ifnull(s.payment_date, s.add_time) as payment_date, s.contract_id as contract_id, c.type as type from settlement_apply s inner join contract c on s.contract_id = c.contract_id and s.status in (0 , 1 , 3 ) and c.type in (1 , 2 ) and s.entrust_id = 116 ) ) b order by b.payment_date asc
分组前数据库返回结果:
类型为1是采购合同,也就是对上游付款的信息,类型为2的是销售合同,也就是对下游收款的数据。可以看出同一类型同一天的数据还没有合并起来。所以还需要对这个查询结果再分组求和,sql如下:
1 2 3 4 5 6 7 8 #分组后sql select sum (amount) as amount, payment_date, type from (上面的sql ) bb group by bb.payment_date,bb.type ;
分组后数据库返回结果:
费了九牛二虎之力然而仅仅是开始,在java代码中还需要实现[1、第一行需要显示用款和付款 2、后面如果日期相同也要显示到同一行]这两个要求。大致思路是先定义一些全局的变量来记录上一条数据和第一条数据,找到第一条后再去看后面是不是有时间相同的,controller的核心代码如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 if (!ObjectUtils.isNullObj(paymentBondViewVos)){ NumberFormat numFormat = java.text.NumberFormat.getPercentInstance(); numFormat.setMaximumIntegerDigits(3 ); numFormat.setMaximumFractionDigits(2 ); double payAmount = 0d ; double receiptAmout = 0d ; int type=0 ; boolean alreadySet=false ; int lastType=0 ; Date lastDate=null ; double lastAmout=0.0 ; int size = paymentBondViewVos.size(); for (int i = 0 ; i < size; i++){ PaymentBondViewVo paymentBondViewVo = paymentBondViewVos.get(i); int typeTmp = paymentBondViewVo.getType(); if (i==0 ){ type = typeTmp; row = sheet.createRow(4 ); if (typeTmp==1 ){ row.createCell(0 ).setCellValue(sdf.format(paymentBondViewVo.getPaymentDate())); row.createCell(1 ).setCellValue(paymentBondViewVo.getAmount()); payAmount = DoubleUtil.preciseAdd(payAmount, paymentBondViewVo.getAmount(), 2 ); } if (typeTmp==2 ){ row.createCell(2 ).setCellValue(sdf.format(paymentBondViewVo.getPaymentDate())); row.createCell(3 ).setCellValue(paymentBondViewVo.getAmount()); receiptAmout = DoubleUtil.preciseAdd(receiptAmout, paymentBondViewVo.getAmount(), 2 ); } }else { Date paymentDate = paymentBondViewVo.getPaymentDate(); int daysOfUse = 0 ; if (type!=typeTmp&&alreadySet==false ){ alreadySet=true ; row = sheet.getRow(4 ); if (typeTmp==2 ){ daysOfUse = daysBetween(lastDate,paymentDate); } }else if (ObjectUtils.equals(sdf.format(lastDate),sdf.format(paymentDate))){ int lastRowNum = sheet.getLastRowNum(); row = sheet.getRow(lastRowNum); daysOfUse = calculateDaysOfUse(sheet,lastRowNum-1 ,sdf,paymentDate); }else { int lastRowNum = sheet.getLastRowNum(); row = sheet.createRow(lastRowNum+1 ); daysOfUse = calculateDaysOfUse(sheet,lastRowNum,sdf,paymentDate); } if (typeTmp==1 ){ row.createCell(0 ).setCellValue(sdf.format(paymentDate)); row.createCell(1 ).setCellValue(paymentBondViewVo.getAmount()); payAmount = DoubleUtil.preciseAdd(payAmount, paymentBondViewVo.getAmount(), 2 ); } if (typeTmp==2 ){ row.createCell(2 ).setCellValue(sdf.format(paymentDate)); row.createCell(3 ).setCellValue(paymentBondViewVo.getAmount()); receiptAmout = DoubleUtil.preciseAdd(receiptAmout, paymentBondViewVo.getAmount(), 2 ); } if (Double.compare(payAmount, receiptAmout)==1 ){ double advancePayment = DoubleUtil.preciseSub(payAmount,receiptAmout, 2 ); double tmp = DoubleUtil.preciseMul(DoubleUtil.preciseMul(advancePayment,Constant.ANNUALIZED),Double.valueOf(daysOfUse)); double interestAmount = DoubleUtil.preciseDev(tmp,Double.valueOf(Constant.INTERESTDAYS)); row.createCell(4 ).setCellValue(advancePayment); row.createCell(5 ).setCellValue(daysOfUse); row.createCell(7 ).setCellValue(interestAmount); } } row.createCell(6 ).setCellValue(numFormat.format(Constant.ANNUALIZED)); lastType=paymentBondViewVo.getType(); lastDate=paymentBondViewVo.getPaymentDate(); lastAmout=paymentBondViewVo.getAmount(); } }
只贴出了封装数据代码 (省略查询委托 合同 设置表头 合并单元格以及循环设置单元格样式等代码)。这样以后就导出需要的表格了,效果图如下:
然而作死把浏览器设置为英文后出现文件名乱码了,如下所示:
需要在浏览器的header中对中文重新进行编码:
1 String codedFileName = java.net.URLEncoder.encode(fileName, "UTF-8" );
这样一来,总算搞定了。。。
4、登录时返回之前的链接
在Spring中做如下配置:
1 2 3 4 5 6 <mvc:interceptor > <mvc:mapping path ="/mcmall/**" /> <mvc:mapping path ="/foodmall/**" /> <bean class ="com.itonghui.filter.RecordLoginInterceptor" /> </mvc:interceptor >
Java代码如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 public class RecordLoginInterceptor extends HandlerInterceptorAdapter { private static final Logger LOGGER = LoggerFactory.getLogger(RecordLoginInterceptor.class); @Override public boolean preHandle (HttpServletRequest request, HttpServletResponse response, Object handler) { SysUser user = (SysUser) SecurityUtils.getSubject().getPrincipal(); String url = "" ; if ((request.getMethod().equalsIgnoreCase(WebContentGenerator.METHOD_POST) || ServerInfo.isAjaxs(request))) { return true ; }else { url = request.getRequestURI(); String queryString = request.getQueryString(); if (StringUtils.isNotBlank(queryString)){ url =url+"?" +queryString; } } if (!ObjectUtils.isNullObj(user)){ return true ; } HttpSession session = request.getSession(); session.setAttribute(Constant.HISTORY_URL,url); return true ; } }