java常用代码整理
整理一下常用的代码,可以支持后续的直接拿过来使用,不需要慢慢再去百度搜索了, 后续不间断更新
1.List转List
将一个类型的List转为另一个类型的List
1 public static void main(String[] args) { 2 List<TbUser> userList = Lists.newArrayList(); 3 TbUser user = new TbUser(); 4 user.setId(1).setName("小王").setTel("12345"); 5 TbUser user2 = new TbUser(); 6 user2.setId(1).setName("小李").setTel("56789"); 7 userList.add(user); 8 userList.add(user2); 9 10 //1.转为name的list 11 List<String> nameList = userList.stream().map(TbUser::getName).collect(Collectors.toList()); 12 //2.转为另外一种对象的集合 13 List<TestUser> testUserList = userList.stream().map(u -> { 14 TestUser testUser = new TestUser(); 15 //使用spring中的BeanUtils 16 BeanUtils.copyProperties(u, testUser); 17 return testUser; 18 }).collect(Collectors.toList()); 19 }
2.List转Map
一般用于将数据库中的一部分数据取出来,然后转为map,方便后续的操作
1 public static void main(String[] args) { 2 List<TbUser> userList = Lists.newArrayList(); 3 TbUser user = new TbUser(); 4 user.setId(1).setName("小王").setTel("12345"); 5 TbUser user2 = new TbUser(); 6 user2.setId(1).setName("小李").setTel("56789"); 7 userList.add(user); 8 userList.add(user2); 9 10 11 //1.将userList转为Map<Integer,TbUser>, 前提是userList中key不重复 12 Map<Integer, TbUser> map = userList.stream().collect(Collectors.toMap(TbUser::getId, u -> u)); 13 //2. 将userList转为Map<Integer,String>,前提是userList中key不重复 14 Map<Integer, String> map2 = userList.stream().collect(Collectors.toMap(TbUser::getId, TbUser::getTel)); 15 //3. 将userList转为Map<Integer,TbUser>,userList中key重复的话,后一个覆盖前面一个 16 Map<Integer, TbUser> map3 = userList.stream().collect(Collectors.toMap(TbUser::getId, Function.identity(), (key1, key2) -> key2)); 17 18 }
3. List重复校验
1 public static void main(String[] args) { 2 List<TbUser> userList = Lists.newArrayList(); 3 TbUser user = new TbUser(); 4 user.setId(1).setName("小王").setTel("12345"); 5 TbUser user2 = new TbUser(); 6 user2.setId(1).setName("小李").setTel("56789"); 7 userList.add(user); 8 userList.add(user2); 9 10 11 //1.从userList找到每个名字对应数量的map 12 Map<String, Long> countMap = userList.stream().collect(Collectors.groupingBy(TbUser::getName, Collectors.counting())); 13 //2. 找到存在重复的名字,只需要遍历countMap的key,然后根据key再从countMap找到值大于1的就行了 14 List<String> repeatNameList = countMap.keySet().stream().filter(key -> countMap.get(key) > 1).collect(Collectors.toList()); 15 //3.如果要对userList中去除名字和性别同时都相同的人, 如果只是简单的List<String>去重,可以直接使用distinct() 16 List<TbUser> uniqueList = userList.stream().collect( 17 Collectors. collectingAndThen( 18 Collectors.toCollection(() -> new TreeSet<>(Comparator.comparing(o -> o.getName() + "-" + o.getSex()))), ArrayList::new) 19 ); 20 //4. 只是想看看List中是否包含一个名字叫做“小王”的人, 如果想返回user对象就用filter,如果只返回true和false,用anyMatch 21 boolean anyMatch = userList.stream().anyMatch(u -> Objects.equals(u.getName(), "小王")); 22 }
4.List中先分组,然后多次排序
通常对List处理的时候,肯定有分组的,再分组之后,对每一组数据首先对A字段排序,然后对B字段进行排序
1 public static void main(String[] args) { 2 List<TbUser> userList = Lists.newArrayList(); 3 TbUser user = new TbUser(); 4 user.setId(1).setName("小王").setTel("12345"); 5 TbUser user2 = new TbUser(); 6 user2.setId(2).setName("小李").setTel("56789"); 7 TbUser user3 = new TbUser(); 8 user3.setId(3).setName("小李").setTel("56789"); 9 userList.add(user); 10 userList.add(user2); 11 userList.add(user3); 12 13 //1. 根据集合中名字进行分组 14 Map<String, List<TbUser>> usernameGroupMap = userList.stream().collect(Collectors.groupingBy(TbUser::getName)); 15 //2. 按照名字分组之后,每一组根据电话号码进行从小到大排序, 顺序 16 HashMap<String, List<TbUser>> groupThenOrderByIdAscMap = userList.stream() 17 .collect(Collectors.groupingBy(TbUser::getName, 18 HashMap::new, 19 Collectors.collectingAndThen(Collectors.toList(), 20 list -> list.stream() 21 .sorted(Comparator.comparing(TbUser::getTel)) 22 .collect(Collectors.toList())))); 23 //3. 按照名字分组之后,每一组根据电话号码进行从大到小排序, 也就是逆序, 和上一个相比,就是多了一个reversed() 24 HashMap<String, List<TbUser>> groupThenOrderByIdDescMap = userList.stream() 25 .collect(Collectors.groupingBy(TbUser::getName, 26 HashMap::new, 27 Collectors.collectingAndThen(Collectors.toList(), 28 list -> list.stream() 29 .sorted(Comparator.comparing(TbUser::getTel).reversed()) 30 .collect(Collectors.toList())))); 31 32 //4, 按照名字分组之后,然后先根据i根据电话号码进行从小到大排序, 号码一样的再根据id从小到大排序 33 HashMap<String, List<TbUser>> ordersMap = userList.stream() 34 .collect(Collectors.groupingBy(TbUser::getName, 35 HashMap::new, 36 Collectors.collectingAndThen(Collectors.toList(), 37 list -> list.stream() 38 .sorted(Comparator.comparing(TbUser::getTel) 39 .thenComparing(TbUser::getId)) 40 .collect(Collectors.toList())))); 41 }
5. excel导出的时候,设置序列,实现的效果如下所示,还有其他的一些数据校验
1 //设置数字范围 2 public void excelRuleNumberBetween(Sheet sheet, int min, int max, int firstRow, int lastRow, int firstCol, int lastCol){ 3 DataValidationHelper helper = sheet.getDataValidationHelper(); 4 CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);//设置行列范围 5 //设置数据 6 DataValidationConstraint constraint = helper.createIntegerConstraint(DataValidationConstraint.OperatorType.BETWEEN, 7 String.valueOf(min),String.valueOf(max)); 8 DataValidation dataValidation = helper.createValidation(constraint, addressList); 9 dataValidation.createErrorBox("输入值类型或大小有误", String.format("请输入%s~%s之间的数值",min,max)); 10 //处理Excel兼容性问题 11 if(dataValidation instanceof XSSFDataValidation) { 12 dataValidation.setSuppressDropDownArrow(true); 13 dataValidation.setShowErrorBox(true); 14 }else { 15 dataValidation.setSuppressDropDownArrow(false); 16 } 17 sheet.addValidationData(dataValidation); 18 } 19 20 21 //设置校验序列 22 public void excelRuleSelect(Sheet sheet, String[] rule, int firstRow, int lastRow, int firstCol, int lastCol) { 23 DataValidationHelper helper = sheet.getDataValidationHelper(); 24 CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol); 25 DataValidationConstraint constraint = helper.createExplicitListConstraint(rule); 26 DataValidation dataValidation = helper.createValidation(constraint, addressList); 27 dataValidation.createErrorBox("输入有误", "请选择下拉参数"); 28 if (dataValidation instanceof XSSFDataValidation) { 29 dataValidation.setSuppressDropDownArrow(true); 30 dataValidation.setShowErrorBox(true); 31 } else { 32 dataValidation.setSuppressDropDownArrow(false); 33 } 34 35 sheet.addValidationData(dataValidation); 36 } 37 38 39 //列数据每个数据唯一 40 public void excelRuleUniqueue(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) { 41 Row row = sheet.getRow(0); 42 Cell cell = row.getCell(firstCol); 43 String r = ((XSSFCell) cell).getCTCell().getR(); 44 r = r.substring(0, 1); 45 DataValidationHelper helper = sheet.getDataValidationHelper(); 46 CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol); 47 //唯一 48 DataValidationConstraint constraint = helper.createCustomConstraint(MessageFormat.format("COUNTIF({0}:{0},{0}2)=1",r)); 49 DataValidation dataValidation = helper.createValidation(constraint, addressList); 50 dataValidation.createErrorBox("错误:", "赋值属性列不允许重复"); 51 dataValidation.setShowErrorBox(true); 52 dataValidation.setEmptyCellAllowed(true); 53 dataValidation.setSuppressDropDownArrow(true); 54 dataValidation.setShowPromptBox(true); 55 dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP); 56 57 sheet.addValidationData(dataValidation); 58 }
6. springboot项目文件上传的单元测试
有的时候单元测试比直接用postman等工具方便点,看实际的情况
1 //单元测试,Excel上传: 2 //@Autowired 3 TestUploadController testUploadController; 4 5 @Test 6 public void uploadStayOutTest() throws Exception { 7 8 File file = new File("C:\\Users\\c\\Downloads\\测试文件导入.xlsx"); 9 FileInputStream fileInputStream = new FileInputStream(file); 10 MockMultipartFile multipartFile = new MockMultipartFile(file.getName(), file.getName(), 11 ContentType.APPLICATION_OCTET_STREAM.toString(), fileInputStream); 12 WageOrderInfoRequestDto infoDto = new WageOrderInfoRequestDto(); 13 infoDto.setIncmType(1); 14 infoDto.setBusiYm("201906"); 15 infoDto.setWageDate("20190614"); 16 infoDto.setChangeFlag(2); 17 infoDto.setEmpName("张三"); 18 infoDto.setIdCode("1304211989707080323"); 19 infoDto.setProbDesc(""); 20 infoDto.setRemark("验证"); 21 infoDto.setWageReaSendDate(DateUtil.getDate(new Date())); 22 testUploadController.uploadStayOut(multipartFile,infoDto); 23 24 }
7. mybatis xml文件使用foreach实现批量更新
如果多笔数据的字段都要更新一样的,就没必要用下面这种方式,去掉<trim>直接写setxxx=#{xxx}就行了
<update id="updateBatch" parameterType="java.util.List"> update mydata_table <trim prefix="set" suffixOverrides=","> <trim prefix="status =case" suffix="end,"> <foreach collection="list" item="item" index="index"> <if test="item.status !=null "> when id=#{item.id} then #{item.status} </if> </foreach> </trim> </trim> where id in <foreach collection="list" index="index" item="item" separator="," open="(" close=")"> #{item.id,jdbcType=BIGINT} </foreach> </update>
8. mybatis的xml中批量新增
1 <insert id="insertList" parameterType="java.util.List"> 2 insert into t_enterprise_water_ele 3 ( 4 WATER_ELE_ID, 5 ENTERPRISE_ID, 6 ENTERPRISE_USCC, 7 ENTERPRISE_NAME, 8 YEARMONTH, 9 WATER_SIZE, 10 WATER_AMOUNT, 11 ELE_SIZE, 12 ELE_AMOUNT, 13 STATUS, 14 OPERATOR, 15 OPERATE_TIME 16 ) 17 VALUES 18 <foreach collection="list" item="item" index="index" separator=","> 19 ( 20 #{item.waterEleId,jdbcType=VARCHAR}, 21 #{item.enterpriseId,jdbcType=VARCHAR}, 22 #{item.enterpriseUscc,jdbcType=VARCHAR}, 23 #{item.enterpriseName,jdbcType=VARCHAR}, 24 #{item.yearmonth,jdbcType=VARCHAR}, 25 #{item.waterSize,jdbcType=DECIMAL}, 26 #{item.waterAmount,jdbcType=VARCHAR}, 27 #{item.eleSize,jdbcType=DOUBLE}, 28 #{item.eleAmount,jdbcType=VARCHAR}, 29 #{item.status,jdbcType=INTEGER}, 30 #{item.operator,jdbcType=VARCHAR}, 31 #{item.operateTime,jdbcType=TIMESTAMP} 32 ) 33 </foreach> 34 </insert>
9 使用mybatis-plus进行单表查询/更新
尽量使用LambdaQueryWrapper/LambdaUpdateWrapper 去做条件拼接,这样拼接条件的key使用的是类似TestUser::getAge的方式,减少硬编码,防止直接写字符串“age”拼错了,要排查好半天
@Slf4j @Service public class TestUserServiceImpl extends ServiceImpl<TestUserMapper, TestUser> implements TestUserService { @Override public String testMethod() { String userName = "王"; Integer age = 18; LambdaQueryWrapper<TestUser> queryWrapper = Wrappers.<TestUser>lambdaQuery() .eq(TestUser::getAge, age) .like(StringUtils.isNotBlank(userName),TestUser::getUserNmae, userName);//%王% List<TestUser> userList = list(queryWrapper); //做后续处理 return null; } }
10. 使用反射操作对象的List属性
1 public class Reflact { 2 public static void main(String[] args) throws Exception { 3 4 //测试方法 5 List<Room> rooms = new ArrayList<Room>(); 6 rooms.add(new Room(1,"大浪",100.0)); 7 rooms.add(new Room(2,"xiao浪",101.0)); 8 User u =new User("zhangsan", 18, rooms); 9 doWithR(u); 10 } 11 static void doWithR(Object obj) throws Exception{ 12 Field[] fields = obj.getClass().getDeclaredFields(); 13 for (Field f : fields) { 14 if(!f.isAccessible()){ 15 f.setAccessible(true);//赋权使用,否则private类型是无法操作的 16 } 17 if(List.class.isAssignableFrom(f.getType())){//判断是否为list 18 Type t = f.getGenericType(); 19 if(t instanceof ParameterizedType){ 20 //ParameterizedType pt = (ParameterizedType)t; 21 //Class clz = (Class)pt.getActualTypeArguments()[0]; 22 Class clazz = f.get(obj).getClass();//获取对象list属性的class 23 Method m = clazz.getDeclaredMethod("size");//获取list属性的size方法 24 int size = (Integer)m.invoke(f.get(obj));//调用size方法 25 for(int i = 0; i < size; i++){//根据size大小循环 26 Method getM = clazz.getDeclaredMethod("get", int.class);//获取list属性的get方法 27 //System.out.println(getM); 28 Object u = getM.invoke(f.get(obj), i);//调用get方法获取list中的对象 29 doWithR(u);//若list中还有list可以递归调用 30 //测试是否可以获取到list中对象的属性的值 31 Field[] uf = u.getClass().getDeclaredFields(); 32 for (Field fu : uf) { 33 if(!fu.isAccessible()) 34 fu.setAccessible(true); 35 System.out.println(fu.get(u)); 36 } 37 } 38 } 39 } 40 } 41 } 42 }
11. 获取ip的工具类方法
1 /** 2 * IP地址 3 */ 4 public class IPUtils { 5 6 private static Logger logger = LoggerFactory.getLogger(IPUtils.class); 7 8 /** 9 * 获取IP地址 10 * 使用Nginx等反向代理软件, 则不能通过request.getRemoteAddr()获取IP地址 11 * 如果使用了多级反向代理的话,X-Forwarded-For的值并不止一个,而是一串IP地址,X-Forwarded-For中第一个非unknown的有效IP字符串,则为真实IP地址 12 */ 13 public static String getIpAddr(HttpServletRequest request) { 14 String ip = null; 15 try { 16 ip = request.getHeader("x-forwarded-for"); 17 if (StringUtils.isEmpty(ip) || "unknown".equalsIgnoreCase(ip)) { 18 ip = request.getHeader("Proxy-Client-IP"); 19 } 20 if (StringUtils.isEmpty(ip) || ip.length() == 0 || "unknown".equalsIgnoreCase(ip)) { 21 ip = request.getHeader("WL-Proxy-Client-IP"); 22 } 23 if (StringUtils.isEmpty(ip) || "unknown".equalsIgnoreCase(ip)) { 24 ip = request.getHeader("HTTP_CLIENT_IP"); 25 } 26 if (StringUtils.isEmpty(ip) || "unknown".equalsIgnoreCase(ip)) { 27 ip = request.getHeader("HTTP_X_FORWARDED_FOR"); 28 } 29 if (StringUtils.isEmpty(ip) || "unknown".equalsIgnoreCase(ip)) { 30 ip = request.getRemoteAddr(); 31 } 32 } catch (Exception e) { 33 logger.error("IPUtils ERROR ", e); 34 } 35 // 使用代理,则获取第一个IP地址 36 if (StringUtils.isEmpty(ip) && ip.length() > 15) { 37 if (ip.indexOf(",") > 0) { 38 ip = ip.substring(0, ip.indexOf(",")); 39 } 40 } 41 return ip; 42 } 43 }
12. 获取当前方法的名字
String methodName = Thread.currentThread().getStackTrace()[1].getMethodName();
13. 使用nio进行文件的快速拷贝
public static void fileCopy( File in, File out ) throws IOException { FileChannel inChannel = new FileInputStream( in ).getChannel(); FileChannel outChannel = new FileOutputStream( out ).getChannel(); try { // inChannel.transferTo(0, inChannel.size(), outChannel); // original -- apparently has trouble copying large files on Windows // magic number for Windows, 64Mb - 32Kb) int maxCount = (64 * 1024 * 1024) - (32 * 1024); long size = inChannel.size(); long position = 0; while ( position < size ) { position += inChannel.transferTo( position, maxCount, outChannel ); } } finally { if ( inChannel != null ) { inChannel.close(); } if ( outChannel != null ) { outChannel.close(); } } }
14.创建文件的缩略图
1 private void createThumbnail(String filename, int thumbWidth, int thumbHeight, int quality, String outFilename) 2 throws InterruptedException, FileNotFoundException, IOException 3 { 4 // load image from filename 5 Image image = Toolkit.getDefaultToolkit().getImage(filename); 6 MediaTracker mediaTracker = new MediaTracker(new Container()); 7 mediaTracker.addImage(image, 0); 8 mediaTracker.waitForID(0); 9 // use this to test for errors at this point: System.out.println(mediaTracker.isErrorAny()); 10 11 // determine thumbnail size from WIDTH and HEIGHT 12 double thumbRatio = (double)thumbWidth / (double)thumbHeight; 13 int imageWidth = image.getWidth(null); 14 int imageHeight = image.getHeight(null); 15 double imageRatio = (double)imageWidth / (double)imageHeight; 16 if (thumbRatio < imageRatio) { 17 thumbHeight = (int)(thumbWidth / imageRatio); 18 } else { 19 thumbWidth = (int)(thumbHeight * imageRatio); 20 } 21 22 // draw original image to thumbnail image object and 23 // scale it to the new size on-the-fly 24 BufferedImage thumbImage = new BufferedImage(thumbWidth, thumbHeight, BufferedImage.TYPE_INT_RGB); 25 Graphics2D graphics2D = thumbImage.createGraphics(); 26 graphics2D.setRenderingHint(RenderingHints.KEY_INTERPOLATION, RenderingHints.VALUE_INTERPOLATION_BILINEAR); 27 graphics2D.drawImage(image, 0, 0, thumbWidth, thumbHeight, null); 28 29 // save thumbnail image to outFilename 30 BufferedOutputStream out = new BufferedOutputStream(new FileOutputStream(outFilename)); 31 JPEGImageEncoder encoder = JPEGCodec.createJPEGEncoder(out); 32 JPEGEncodeParam param = encoder.getDefaultJPEGEncodeParam(thumbImage); 33 quality = Math.max(0, Math.min(quality, 100)); 34 param.setQuality((float)quality / 100.0f, false); 35 encoder.setJPEGEncodeParam(param); 36 encoder.encode(thumbImage); 37 out.close(); 38 }
15. spring中bean初始化之后,获取注解标注的一些Bean, 然后获取到了这些Bean后我们可以保存到我们自己设置的Map中,后续业务层去调用
1 @Component 2 public class BeanListener implements ApplicationListener<ContextRefreshedEvent> { 3 4 @Override 5 public void onApplicationEvent(ContextRefreshedEvent event) { 6 // 根容器为Spring容器 7 if (event.getApplicationContext().getParent() == null) { 8 Map<String, Object> beans = event.getApplicationContext().getBeansWithAnnotation(TestSheet.class); 9 for (Object bean : beans.values()) { 10 System.err.println(bean == null ? "null" : bean.getClass().getName()); 11 } 12 System.err.println("=====ContextRefreshedEvent=====" + event.getSource().getClass().getName()); 13 } 14 } 15 }
16. 基于POI的excel的导出功能,包含一级标题,二级标题,表头,统计列,对某几列进行码值转换
注解ExcelSheet:
1 package com.example.demo.excel; 2 3 import java.lang.annotation.ElementType; 4 import java.lang.annotation.Retention; 5 import java.lang.annotation.RetentionPolicy; 6 import java.lang.annotation.Target; 7 8 @Target(ElementType.TYPE) 9 @Retention(RetentionPolicy.RUNTIME) 10 public @interface ExcelSheet { 11 12 /** 13 * @Description: sheet名称 14 * @Param: [] 15 * @return: java.lang.String 16 * @Date: 2023/6/29 17 */ 18 String sheetName(); 19 /** 20 * @Description: 标题名称 21 * @Param: [] 22 * @return: java.lang.String 23 * @Date: 2023/6/29 24 */ 25 String titleName(); 26 /* 27 是否显示序号列,默认显示 28 */ 29 boolean showIndexColumn() default true; 30 31 }
注解ExcelColumn:
1 package com.example.demo.excel; 2 3 import java.lang.annotation.ElementType; 4 import java.lang.annotation.Retention; 5 import java.lang.annotation.RetentionPolicy; 6 import java.lang.annotation.Target; 7 8 @Target(ElementType.FIELD) 9 @Retention(RetentionPolicy.RUNTIME) 10 public @interface ExcelColumn { 11 /** 12 * @Title: title 13 * @Description: 表头列名 必填 14 */ 15 String columnName(); 16 17 /** 18 * 列的序号,意思就是把添加这个注解的属性要放到excel的第几列,从1开始 19 * 需要注意的是:这个值必须连续,比如:各个属性的sort必须是:1,3,2,4,6,5(可以乱序) 20 * 但是不能出现:1,3,4,6,5(少了2) 21 */ 22 int sort(); 23 24 /** 25 * @Title: width 26 * @Description: 列宽 默认15 27 */ 28 short width() default 20; 29 30 enum Alignment { 31 LEFT(0x1), CENTER(0x2), RIGHT(0x3); 32 private int value; 33 34 private Alignment(int value) { 35 this.value = value; 36 } 37 38 public int getValue() { 39 return value; 40 } 41 } 42 43 ; 44 45 /** 46 * @Title: alignment 47 * @Description: 文字样式 默认居中(Alignment.CENTER) 48 */ 49 Alignment alignment() default Alignment.CENTER; 50 51 /** 52 * @Title: boder 53 * @Description: 单元格是否需要边框 环绕包围 默认true 54 */ 55 boolean boder() default true; 56 57 enum StyleColor { 58 WHITE(0x9), BLACK(0x8), BLUE(0xc), RED(0xa), YELLOW(0xd); 59 private int value; 60 61 private StyleColor(int value) { 62 this.value = value; 63 } 64 65 public int getValue() { 66 return value; 67 } 68 } 69 70 ; 71 72 /** 73 * @Title: styleColor 74 * @Description: 单元格背景色 默认白色 75 */ 76 StyleColor styleColor() default StyleColor.WHITE; 77 78 enum FontColor { 79 BLACK(0x8), BLUE(0xc), RED(0xa), YELLOW(0xd); 80 private int value; 81 82 private FontColor(int value) { 83 this.value = value; 84 } 85 86 public int getValue() { 87 return value; 88 } 89 } 90 91 ; 92 93 /** 94 * @Title: fontColor 95 * @Description: 文字颜色 默认黑色(FontColor.BLACK) 暂支持 BLACK BLUE RED YELLO 96 */ 97 FontColor fontColor() default FontColor.BLACK; 98 99 /** 100 * @Title: fontSize 101 * @Description: 字号大小 默认12 102 */ 103 short fontSize() default 12; 104 105 /** 106 * @Title: fontName 107 * @Description: 字体 默认微软雅黑 108 */ 109 String fontName() default "微软雅黑"; 110 111 /** 112 * 操作类型 113 * @return 114 */ 115 OperatorType opertorType() default OperatorType.BLANK; 116 117 enum OperatorType { 118 SUM("sum"),BLANK("blank"); 119 private String value; 120 121 private OperatorType(String value) { 122 this.value = value; 123 } 124 125 public String getValue() { 126 return value; 127 } 128 } 129 130 String operatorDescription() default "总计"; 131 132 133 /** 134 * 枚举类型的class 135 * 取值:getValue, getCode, getStatus, name 136 * 描述:getDesc 137 * 138 * @return 字典类型 139 */ 140 Class<? extends Enum<?>> convertEnumType() default Void.class; 141 enum Void {} 142 143 /* 144 枚举中转换的方法名称 145 */ 146 String convertEnumMethodName() default ""; 147 }
ExcelFieldMetadata类:
1 package com.example.demo.excel; 2 3 import java.lang.reflect.Field; 4 import java.text.SimpleDateFormat; 5 6 /** 7 * @author 8 * @title ExcelFieldType 9 * @date 2023/7/1 1:00 10 * @description 对于导出的列的元数据描述 11 */ 12 public class ExcelFieldMetadata { 13 /* 14 序号列, 后续扩展 15 */ 16 private boolean index; 17 18 /* 19 列号 从0开始 20 */ 21 private Integer sort; 22 /** 23 * 列名 24 */ 25 private String columnName; 26 27 /* 28 列宽 29 */ 30 private Short columnWidth; 31 /* 32 文本样式,左对齐,右对齐,居中 33 */ 34 private Integer align; 35 36 /* 37 是否需要边框 38 */ 39 private Boolean border; 40 41 /* 42 字体颜色 43 */ 44 private int fontColor; 45 46 /* 47 字体名字 48 */ 49 private String fontName; 50 51 /* 52 字号 53 */ 54 private Short fontSize; 55 56 /* 57 单元格背景颜色 58 */ 59 private int styleColor; 60 61 /* 62 属性值 63 */ 64 private Field field; 65 /* 66 excelColumn注解 67 */ 68 private ExcelColumn excelColumn; 69 70 /* 71 日期格式化对象 72 */ 73 private SimpleDateFormat dateFormat; 74 75 76 /* 77 转换的枚举类 78 */ 79 private Class<? extends Enum<?>> convertEnumType; 80 /* 81 用于转换的枚举类的指定方法 82 */ 83 private String convertEnumMethodName; 84 85 public ExcelFieldMetadata() { 86 } 87 88 public boolean isIndex() { 89 return index; 90 } 91 92 public ExcelFieldMetadata setIndex(boolean index) { 93 this.index = index; 94 return this; 95 } 96 97 public Integer getSort() { 98 return sort; 99 } 100 101 public ExcelFieldMetadata setSort(Integer sort) { 102 this.sort = sort; 103 return this; 104 } 105 106 public String getColumnName() { 107 return columnName; 108 } 109 110 public ExcelFieldMetadata setColumnName(String columnName) { 111 this.columnName = columnName; 112 return this; 113 } 114 115 public Short getColumnWidth() { 116 return columnWidth; 117 } 118 119 public ExcelFieldMetadata setColumnWidth(Short columnWidth) { 120 this.columnWidth = columnWidth; 121 return this; 122 } 123 124 public Integer getAlign() { 125 return align; 126 } 127 128 public ExcelFieldMetadata setAlign(Integer align) { 129 this.align = align; 130 return this; 131 } 132 133 public Boolean getBorder() { 134 return border; 135 } 136 137 public ExcelFieldMetadata setBorder(Boolean border) { 138 this.border = border; 139 return this; 140 } 141 142 public int getFontColor() { 143 return fontColor; 144 } 145 146 public ExcelFieldMetadata setFontColor(int fontColor) { 147 this.fontColor = fontColor; 148 return this; 149 } 150 151 public String getFontName() { 152 return fontName; 153 } 154 155 public ExcelFieldMetadata setFontName(String fontName) { 156 this.fontName = fontName; 157 return this; 158 } 159 160 public Short getFontSize() { 161 return fontSize; 162 } 163 164 public ExcelFieldMetadata setFontSize(Short fontSize) { 165 this.fontSize = fontSize; 166 return this; 167 } 168 169 public int getStyleColor() { 170 return styleColor; 171 } 172 173 public ExcelFieldMetadata setStyleColor(int styleColor) { 174 this.styleColor = styleColor; 175 return this; 176 } 177 178 public Field getField() { 179 return field; 180 } 181 182 public ExcelFieldMetadata setField(Field field) { 183 this.field = field; 184 return this; 185 } 186 187 public ExcelColumn getExcelColumn() { 188 return excelColumn; 189 } 190 191 public ExcelFieldMetadata setExcelColumn(ExcelColumn excelColumn) { 192 this.excelColumn = excelColumn; 193 return this; 194 } 195 196 public Class<? extends Enum<?>> getConvertEnumType() { 197 return convertEnumType; 198 } 199 200 public ExcelFieldMetadata setConvertEnumType(Class<? extends Enum<?>> convertEnumType) { 201 this.convertEnumType = convertEnumType; 202 return this; 203 } 204 205 public String getConvertEnumMethodName() { 206 return convertEnumMethodName; 207 } 208 209 public ExcelFieldMetadata setConvertEnumMethodName(String convertEnumMethodName) { 210 this.convertEnumMethodName = convertEnumMethodName; 211 return this; 212 } 213 214 public SimpleDateFormat getDateFormat() { 215 return dateFormat; 216 } 217 218 public ExcelFieldMetadata setDateFormat(SimpleDateFormat dateFormat) { 219 this.dateFormat = dateFormat; 220 return this; 221 } 222 }
MallExcelUtils工具类:
1 package com.example.demo.excel; 2 3 import com.google.common.collect.Lists; 4 import com.google.common.collect.Maps; 5 import org.apache.commons.codec.binary.Base64; 6 import org.apache.commons.collections4.MapUtils; 7 import org.apache.commons.lang3.StringUtils; 8 import org.apache.poi.hssf.usermodel.HSSFRichTextString; 9 import org.apache.poi.hssf.usermodel.HSSFWorkbook; 10 import org.apache.poi.ss.usermodel.*; 11 import org.apache.poi.ss.util.CellRangeAddress; 12 import org.apache.poi.xssf.streaming.SXSSFWorkbook; 13 import org.slf4j.Logger; 14 import org.slf4j.LoggerFactory; 15 import org.springframework.util.CollectionUtils; 16 17 import javax.servlet.ServletOutputStream; 18 import javax.servlet.http.HttpServletRequest; 19 import javax.servlet.http.HttpServletResponse; 20 import java.lang.reflect.Field; 21 import java.lang.reflect.InvocationTargetException; 22 import java.lang.reflect.Method; 23 import java.math.BigDecimal; 24 import java.net.URLEncoder; 25 import java.text.SimpleDateFormat; 26 import java.util.*; 27 28 29 /** 30 * @param <T> 31 * @Description excel导出工具类 32 * <p> 33 * 使用示例: 34 * List<User> list = getList(); 35 * new MallExcelUtils<User>() 36 * .setClazz(User.class)//实体类类型 37 * .setSecondRowLeftAndRight("操作时间:2023-06-12至2023-06-30","导出人:小王") 38 * .setSecondLevelHeader(Collections.singletonList("二级标题#3,4,1,3"))//二级标题 内容#firstRow,lastRow,firstColumn,lastColumn 39 * .setDataList(list)//实际的数据集合 40 * .setFullFileName("test.xls")//导出文件的名称 41 * .initlization()//开始初始化 42 * .exportExcel(request,response);//实际以流的方式导出 43 * @Date: 2023年6月28日 11:03:31 44 * @Author: 45 */ 46 public class MallExcelUtils<T> { 47 48 public static Logger log = LoggerFactory.getLogger(MallExcelUtils.class);//日志记录 49 50 /* 51 导出目标类的Class对象 52 */ 53 private Class<T> clazz; 54 /* 55 sheet名称 56 */ 57 private String sheetName; 58 /* 59 标题内容 60 */ 61 private String titleName; 62 /** 63 * 统计的开始和结束时间 64 */ 65 private String leftData; 66 /** 67 * 填表人 68 */ 69 private String rightData; 70 71 /* 72 实际的业务数据 73 */ 74 private List<T> dataList; 75 76 /* 77 导出的文件名称 78 */ 79 private String fullFileName; 80 /* 81 文件扩展名 82 */ 83 private String ext; 84 /* 85 要合并的表头数组 86 */ 87 private List<String> secondLevelHeader = Collections.emptyList(); 88 /* 89 导出列的元数据类型 90 */ 91 private final List<ExcelFieldMetadata> excelFieldMetadataList = Lists.newArrayList(); 92 93 /* 94 fieldName->Annotation, 当注解中opertorType不为BLANK的时候需要收集起来,后续对该列做处理,例如累加 95 */ 96 private final Map<String, ExcelColumn> fieldNameToAnnOperatorMap = Maps.newHashMap(); 97 /* 98 fieldName->finallyValue, 将对字段的特殊操作的值存放起来 99 */ 100 private final Map<String, Object> fieldNameToFinalValueMap = Maps.newHashMap();//求和 101 /** 102 * 当前的行 103 */ 104 private Integer currentRowIndex = 0; 105 106 /* 107 支持的文件扩展名 108 */ 109 public static List<String> allowExtList = Arrays.asList(".xls", ".xlsx"); 110 /* 111 是否显示序号列标志 112 */ 113 private boolean showIndexColumn = false; 114 115 public MallExcelUtils() { 116 } 117 118 public MallExcelUtils<T> setClazz(Class<T> clazz) { 119 this.clazz = clazz; 120 return this; 121 } 122 123 /* 124 设置第二行两个靠边的单元格的值,可以存放时间和操作人信息 125 */ 126 public MallExcelUtils<T> setSecondRowLeftAndRight(String leftData, String rightData) { 127 this.leftData = leftData; 128 this.rightData = rightData; 129 return this; 130 } 131 132 public MallExcelUtils<T> setSecondLevelHeader(List<String> secondLevelHeader) { 133 this.secondLevelHeader = secondLevelHeader; 134 return this; 135 } 136 137 138 public MallExcelUtils<T> setDataList(List<T> dataList) { 139 this.dataList = dataList; 140 return this; 141 } 142 143 public MallExcelUtils<T> setFullFileName(String fullFileName) { 144 if (StringUtils.isEmpty(fullFileName)) { 145 throw new IllegalArgumentException("文件名称为空"); 146 } 147 if (!fullFileName.contains(".")) { 148 throw new IllegalArgumentException("文件名称格式不对:" + fullFileName); 149 } 150 int index = fullFileName.indexOf("."); 151 ext = fullFileName.substring(index);//excel后缀名 152 if (!allowExtList.contains(ext)) { 153 throw new IllegalArgumentException("文件扩展名只能为[.xls]或[.xlsx]"); 154 } 155 this.fullFileName = fullFileName; 156 return this; 157 } 158 159 160 public MallExcelUtils<T> initlization() { 161 //初始化sheet名称和sheet中大标题 162 initSheetNameAndTitle(); 163 //初始化元数据 164 initMetadataList(); 165 return this; 166 } 167 168 169 /** 170 * @Description: 初始化sheetName名称和标题 171 * @Param: [] 172 * @return: void 173 * @Date: 2023/7/1 174 */ 175 private void initSheetNameAndTitle() { 176 if (!this.clazz.isAnnotationPresent(ExcelSheet.class)) { 177 throw new IllegalStateException("实体类中导出Excel没有配置ExcelSheet注解,实体类为:" + this.clazz.getName()); 178 } 179 ExcelSheet excelSheet = this.clazz.getAnnotation(ExcelSheet.class); 180 //sheet名称 181 String sheetName = excelSheet.sheetName(); 182 //一级标题名称 183 String titleName = excelSheet.titleName(); 184 if (StringUtils.isEmpty(sheetName)) { 185 this.sheetName = "sheet1"; 186 } else { 187 this.sheetName = sheetName; 188 } 189 if (StringUtils.isEmpty(titleName)) { 190 this.titleName = ""; 191 } else { 192 this.titleName = titleName; 193 } 194 195 //是否显示序号列标志 196 this.showIndexColumn = excelSheet.showIndexColumn(); 197 } 198 199 /** 200 * @Description: 初始化元数据 201 * @Param: [] 202 * @return: void 203 * @Date: 2023/7/1 204 */ 205 private void initMetadataList() { 206 ExcelFieldMetadata excelFieldMetadata = null; 207 //如果有序号列,第一个就是序号的Metadata 208 if (showIndexColumn){ 209 excelFieldMetadata = new ExcelFieldMetadata() 210 .setIndex(true) 211 .setSort(0) 212 .setColumnName("序号") 213 .setColumnWidth((short) 20) 214 .setAlign(ExcelColumn.Alignment.CENTER.getValue()) 215 .setBorder(true) 216 .setFontColor(ExcelColumn.FontColor.BLACK.getValue()) 217 .setFontName("微软雅黑") 218 .setFontSize((short) 12) 219 .setStyleColor(ExcelColumn.StyleColor.WHITE.getValue()); 220 excelFieldMetadataList.add(excelFieldMetadata); 221 } 222 223 //实际遍历每个属性 224 for (Field field : clazz.getDeclaredFields()) { 225 if (!field.isAnnotationPresent(ExcelColumn.class)) { 226 continue; 227 } 228 //设置field访问权限 229 field.setAccessible(true); 230 ExcelColumn excelColumn = field.getAnnotation(ExcelColumn.class); 231 excelFieldMetadata = new ExcelFieldMetadata() 232 .setField(field) 233 .setColumnName(excelColumn.columnName()) 234 .setColumnWidth(excelColumn.width()) 235 .setAlign(excelColumn.alignment().getValue()) 236 .setBorder(excelColumn.boder()) 237 .setFontColor(excelColumn.fontColor().getValue()) 238 .setFontName(excelColumn.fontName()) 239 .setFontSize(excelColumn.fontSize()) 240 .setStyleColor(excelColumn.styleColor().getValue()) 241 .setConvertEnumType(excelColumn.convertEnumType())//用于转换的枚举类 242 .setConvertEnumMethodName(excelColumn.convertEnumMethodName());//用于转换的枚举类的指定方法 243 //日期格式化 244 if (StringUtils.isNotBlank(excelColumn.dateFormat())){ 245 excelFieldMetadata.setDateFormat(new SimpleDateFormat(excelColumn.dateFormat())); 246 } 247 248 //是否展示序号列来判断其他字段的列号 249 if (showIndexColumn){ 250 excelFieldMetadata.setSort(excelColumn.sort()); 251 }else{ 252 excelFieldMetadata.setSort(excelColumn.sort()-1); 253 } 254 255 excelFieldMetadataList.add(excelFieldMetadata); 256 //这里新增对列的操作,除了Blank的都保存起来 257 ExcelColumn.OperatorType operatorType = excelColumn.opertorType(); 258 if (!ExcelColumn.OperatorType.BLANK.getValue().equals(operatorType.getValue())) { 259 fieldNameToAnnOperatorMap.put(field.getName(), excelColumn); 260 } 261 //排序 262 Collections.sort(excelFieldMetadataList, new Comparator<ExcelFieldMetadata>() { 263 @Override 264 public int compare(ExcelFieldMetadata o1, ExcelFieldMetadata o2) { 265 return o1.getSort() - o2.getSort(); 266 } 267 }); 268 } 269 } 270 271 272 /** 273 * @Description: 导出excel的方法 274 * @Param: [dataList 实际的数据, request 请求, response 响应, fullFileName 文件全名称] 275 * @return: void 276 * @Date: 2023/7/1 277 */ 278 public void exportExcel(HttpServletRequest request, HttpServletResponse response) { 279 if (StringUtils.isEmpty(fullFileName)) { 280 throw new IllegalArgumentException("未初始化导出文件名称,请首先初始化"); 281 } 282 //根据后缀名得到工作簿对象 283 Workbook workbook = getWorkBook(); 284 //创建sheet页 285 creatSheet(workbook, dataList); 286 //下载文件 287 downloadFile(workbook, request, response, fullFileName); 288 289 } 290 291 /** 292 * 创建sheet 293 */ 294 private void creatSheet(Workbook workbook, List<T> dataList) { 295 if (CollectionUtils.isEmpty(dataList)) { 296 return; 297 } 298 //1. 新建一个sheet 299 Sheet sheet = workbook.createSheet(sheetName); 300 301 // 2. 循环设置列宽 302 for (int i = 0; i < excelFieldMetadataList.size(); i++) { 303 sheet.setColumnWidth(i, excelFieldMetadataList.get(i).getColumnWidth() * 256); 304 } 305 306 // 3. 产生表格标题行,标题行是第一行 307 createTitle(workbook, sheet); 308 309 //4. 设置附加信息,有设置的话就在第二行 310 if (StringUtils.isNotBlank(leftData) || StringUtils.isNotBlank(rightData)) { 311 createAttachRow(workbook, sheet); 312 } 313 314 // 5. 合并表头行设置 315 if (!CollectionUtils.isEmpty(secondLevelHeader)) { 316 createSecondHeader(workbook, sheet); 317 } 318 319 //生6. 成表头行,用的header中的数据 320 createHeader(workbook, sheet); 321 322 // 7. 遍历集合数据,产生数据行 323 parseDataListToSheet(workbook, sheet, dataList); 324 } 325 326 327 /** 328 * @Description: 创建一行是时间和导出人 329 * @Param: [startAndEndDate, fillFormatPerson] 330 * @return: void 331 * @Date: 2023/6/29 332 */ 333 private void createAttachRow(Workbook workbook, Sheet sheet) { 334 Row row = sheet.createRow(currentRowIndex); 335 //创建一个边框的样式 336 CellStyle borderStyle = createBorderCellStyle(workbook); 337 //如果只有两列,就不要做什么合并单元格了 338 int totalColumn = excelFieldMetadataList.size(); 339 for (int i = 0; i < totalColumn; i++) { 340 Cell dateCell = row.createCell(i); 341 dateCell.setCellStyle(borderStyle); 342 } 343 344 if (totalColumn <= 4) { 345 Cell dateCell = row.getCell(0); 346 dateCell.setCellValue(leftData); 347 Cell personCell = row.getCell(totalColumn - 1); 348 personCell.setCellValue(rightData); 349 return; 350 } 351 Cell dateCell = row.getCell(0); 352 dateCell.setCellValue(leftData); 353 Cell personCell = row.getCell(totalColumn - 2); 354 personCell.setCellValue(rightData); 355 CellRangeAddress dateAddress = new CellRangeAddress(currentRowIndex, currentRowIndex, 0, 1); 356 357 sheet.addMergedRegion(dateAddress);//设置合并 358 CellRangeAddress personAddress = new CellRangeAddress(currentRowIndex, currentRowIndex, totalColumn - 2, totalColumn - 1); 359 360 sheet.addMergedRegion(personAddress);//设置合并 361 currentRowIndex++; 362 } 363 364 /* 365 创建有边框的样式 366 */ 367 private CellStyle createBorderCellStyle(Workbook workbook) { 368 // 生成一个通用样式 默认背景为白色 369 CellStyle borderStyle = workbook.createCellStyle(); 370 borderStyle.setFillForegroundColor((short) ExcelColumn.StyleColor.WHITE.getValue()); 371 372 // 单元格内容样式 373 borderStyle.setAlignment(HorizontalAlignment.CENTER); 374 borderStyle.setVerticalAlignment(VerticalAlignment.CENTER); 375 376 // 单元格是否需要边框 377 borderStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); 378 borderStyle.setBorderBottom(BorderStyle.THIN); 379 borderStyle.setBorderLeft(BorderStyle.THIN); 380 borderStyle.setBorderRight(BorderStyle.THIN); 381 borderStyle.setBorderTop(BorderStyle.THIN); 382 return borderStyle; 383 } 384 385 /** 386 * @Description: 根据表头数组信息合并行 387 * @Param: [workbook, sheet] 388 * @return: void 389 * @Date: 2023/7/1 390 */ 391 private void createSecondHeader(Workbook workbook, Sheet sheet) { 392 Row row = sheet.createRow(currentRowIndex++);//第二行是合并表头的行,行号为1 393 CellStyle borderStyle = createBorderCellStyle(workbook);//创建一个样式 394 Font font = createFont(workbook, (short) 20, true); 395 borderStyle.setFont(font);// 把字体应用到当前的样式 396 397 String[] zuoBiaoArr; 398 int colIndex = 0;//列起始行号 399 Cell cell = null; 400 //影响的行数 401 int effectRowNum = 0; 402 for (String s : secondLevelHeader) { 403 String[] valueArr = s.split("#"); 404 String cellValue = valueArr[0];//单元格内容 405 //二级标题内容#int firstRow, int lastRow, int firstCol, int lastCol 406 zuoBiaoArr = valueArr[1].split(","); 407 String firstRow = zuoBiaoArr[0]; 408 String lastRow = zuoBiaoArr[1]; 409 String firstColumn = zuoBiaoArr[2]; 410 String lastColumn = zuoBiaoArr[3]; 411 if (Integer.parseInt(firstRow) > Integer.parseInt(lastRow)) { 412 throw new IllegalArgumentException("二级标题参数有误,起始行序号大于结束行序号"); 413 } 414 if (Integer.parseInt(firstColumn) > Integer.parseInt(lastColumn)) { 415 throw new IllegalArgumentException("二级标题参数有误,起始列序号大于结束列序号"); 416 } 417 for (int i = Integer.parseInt(firstRow) - 1; i < Integer.parseInt(lastRow); i++) { 418 Row newRow = sheet.getRow(i); 419 if (newRow == null) { 420 sheet.createRow(i); 421 currentRowIndex++; 422 } 423 } 424 425 colIndex = Integer.parseInt(firstColumn) - 1; 426 cell = row.createCell(colIndex);//得到列 427 cell.setCellStyle(borderStyle);//设置样式 428 cell.setCellValue(cellValue);//设置内容 429 CellRangeAddress cellAddresses = new CellRangeAddress(Integer.parseInt(firstRow) - 1, 430 Integer.parseInt(lastRow) - 1, Integer.parseInt(firstColumn) - 1, Integer.parseInt(lastColumn) - 1); 431 //合并单元格 432 mergeColumnsStyle(sheet, cellAddresses, borderStyle); 433 sheet.addMergedRegion(cellAddresses); 434 } 435 } 436 437 /** 438 * 循环遍历,把数据设置到表格中的方法 439 * 440 * @param workbook 工作簿 441 * @param sheet sheet对象 442 * @param dataList 数据集合 443 */ 444 private void parseDataListToSheet(Workbook workbook, Sheet sheet, List<T> dataList) { 445 Row row = null; 446 Cell cell = null; 447 for (int m = 0; m < dataList.size(); m++) { 448 T t = dataList.get(m); 449 row = sheet.createRow(currentRowIndex++); 450 for (int i = 0; i < excelFieldMetadataList.size(); i++) { 451 cell = row.createCell(i); 452 ExcelFieldMetadata excelFieldMetadata = excelFieldMetadataList.get(i); 453 boolean index = excelFieldMetadata.isIndex(); 454 //序号列 455 if (index) { 456 styleToCell(workbook, cell, excelFieldMetadata, false); 457 cell.setCellValue(m+1); 458 continue; 459 } 460 Field field = excelFieldMetadata.getField();//获取属性 461 //1. 调用方法进行样式设置 462 styleToCell(workbook, cell, excelFieldMetadata, false); 463 String fieldName = field.getName();//得到属性名称 464 String fieldValue = null;//属性值 465 try { 466 if (Date.class.equals(field.getType())) { 467 //日期转换 468 fieldValue = convertDate(t, field, excelFieldMetadata); 469 }else{ 470 fieldValue = field.get(t) == null ? "" : field.get(t).toString(); 471 } 472 } catch (IllegalAccessException e) { 473 log.error("反射获取属性值失败" + fieldName); 474 e.printStackTrace(); 475 } 476 477 //2. 处理对列的求和等操作 478 try { 479 dealOpertorTypeToColunm(t, field); 480 } catch (IllegalAccessException e) { 481 log.error("对Column的操作失败:" + fieldName); 482 e.printStackTrace(); 483 } 484 485 //如果没有转换码值的列,就直接设置值返回啦 486 if (excelFieldMetadata.getConvertEnumType().getSimpleName().equalsIgnoreCase("Void")) { 487 //向cell设置值 488 cell.setCellValue(fieldValue); 489 continue; 490 } 491 //3. 存在码值转换的场景 492 try { 493 fieldValue = dealColumnConvert(t, excelFieldMetadata); 494 } catch (Exception e) { 495 log.error("对Column的转换失败:" + fieldName); 496 e.printStackTrace(); 497 } 498 cell.setCellValue(fieldValue); 499 } 500 } 501 if (MapUtils.isEmpty(fieldNameToFinalValueMap)) { 502 return; 503 } 504 //4. 处理最后统计行 505 dealStaticRow(workbook, sheet); 506 } 507 508 /** 509 * 日期转换 510 * @param t 511 * @param field 512 * @return 513 */ 514 private String convertDate(T t, Field field,ExcelFieldMetadata excelFieldMetadata) throws IllegalAccessException { 515 SimpleDateFormat dateFormat = excelFieldMetadata.getDateFormat(); 516 Object o = field.get(t); 517 if (o!=null && dateFormat!=null) { 518 return dateFormat.format(field.get(t)); 519 } 520 return ""; 521 } 522 523 /** 524 * @Description: 处理统计行 525 * @Param: [workbook, sheet] 526 * @return: void 527 * @Date: 2023/7/1 528 */ 529 private void dealStaticRow(Workbook workbook, Sheet sheet) { 530 Row row = sheet.createRow(currentRowIndex++); 531 //创建统计行的样式 532 CellStyle staticCellStyle = createStaticCellStyle(workbook, true); 533 //写入excel数据 534 for (Map.Entry<String, Object> entry : fieldNameToFinalValueMap.entrySet()) { 535 String fieldName = entry.getKey(); 536 //列号码和sort一样,sort从1开始 537 ExcelColumn excelColumn = fieldNameToAnnOperatorMap.get(fieldName); 538 int columnNum = 0; 539 if (showIndexColumn){ 540 columnNum = excelColumn.sort(); 541 }else{ 542 columnNum = excelColumn.sort() - 1; 543 } 544 //"统计行"第一个单元格的值 545 String operatorDescription = excelColumn.operatorDescription(); 546 Cell cell = null; 547 for (int i = 0; i < excelFieldMetadataList.size(); i++) { 548 Field field = excelFieldMetadataList.get(i).getField(); 549 cell = row.createCell(i); 550 cell.setCellStyle(staticCellStyle); 551 //首列单元格设置值 552 if (i == 0) { 553 cell.setCellValue(operatorDescription); 554 continue; 555 } 556 //对指定列设置统计值 557 if (i == columnNum) { 558 cell.setCellValue(fieldNameToFinalValueMap.get(field.getName()).toString()); 559 } 560 } 561 } 562 } 563 564 /** 565 * @Description: 对列进行码值转换 566 * @Param: [t, field] 567 * @return: java.lang.String 568 * @Date: 2023/7/1 569 */ 570 private String dealColumnConvert(T t, ExcelFieldMetadata excelFieldMetadata) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException { 571 Field field = excelFieldMetadata.getField(); 572 Class<? extends Enum<?>> convertEnumType = excelFieldMetadata.getConvertEnumType(); 573 String convertEnumMethodName = excelFieldMetadata.getConvertEnumMethodName(); 574 if (Integer.class.equals(field.getType())) { 575 Method method = convertEnumType.getDeclaredMethod(convertEnumMethodName, Integer.class); 576 Object[] ec = convertEnumType.getEnumConstants(); 577 Object obj = field.get(t); 578 if (obj != null) { 579 Integer fieldValueInt = (Integer) field.get(t);//属性值 580 Object description = method.invoke(ec[0], fieldValueInt); 581 return (String) description; 582 } 583 } else if (String.class.equals(field.getType())) { 584 Method method = convertEnumType.getDeclaredMethod(convertEnumMethodName, String.class); 585 Object[] ec = convertEnumType.getEnumConstants(); 586 Object obj = field.get(t); 587 if (obj != null) { 588 String fieldValueStr = field.get(t).toString();//属性值 589 Object description = method.invoke(ec[0], fieldValueStr); 590 return (String) description; 591 } 592 } 593 return ""; 594 } 595 596 /** 597 * @Description: 对特殊的列进行特殊处理,比如对某列进行求和 598 * @Param: [t, field, fieldName] 599 * @return: void 600 * @Date: 2023/7/1 601 */ 602 private void dealOpertorTypeToColunm(T t, Field field) throws IllegalAccessException { 603 String fieldName = field.getName(); 604 //对列做累加求和等操作,并把每次求和的操作保存到fieldNameToFinalValueMap中 605 if (fieldNameToAnnOperatorMap.containsKey(fieldName)) { 606 ExcelColumn excelColumn = fieldNameToAnnOperatorMap.get(fieldName); 607 //累加操作, 现在至此String, Bigdecimal,Integer 608 if (ExcelColumn.OperatorType.SUM.getValue().equals(excelColumn.opertorType().getValue())) { 609 Object obj = field.get(t); 610 if (obj == null) { 611 return; 612 } 613 //判断当前是数字还是金额 614 if (BigDecimal.class.equals(field.getType())) { 615 if (fieldNameToFinalValueMap.containsKey(fieldName)) { 616 BigDecimal bd = (BigDecimal) fieldNameToFinalValueMap.get(fieldName); 617 BigDecimal v = (BigDecimal) obj; 618 bd = bd.add(v); 619 fieldNameToFinalValueMap.put(fieldName, bd); 620 } else { 621 BigDecimal bd = (BigDecimal) obj; 622 fieldNameToFinalValueMap.put(fieldName, bd); 623 } 624 } 625 //数字类型 626 if (Integer.class.equals(field.getType())) { 627 if (fieldNameToFinalValueMap.containsKey(fieldName)) { 628 Integer bd = (Integer) fieldNameToFinalValueMap.get(fieldName); 629 bd = bd + (Integer) obj; 630 fieldNameToFinalValueMap.put(fieldName, bd); 631 } else { 632 Integer bd = (Integer) obj; 633 fieldNameToFinalValueMap.put(fieldName, bd); 634 } 635 } 636 637 //数字类型的字符串 638 //数字类型 639 if (String.class.equals(field.getType())) { 640 if (fieldNameToFinalValueMap.containsKey(fieldName)) { 641 Long bd = (Long) fieldNameToFinalValueMap.get(fieldName); 642 String s = String.valueOf(obj); 643 if (StringUtils.isEmpty(s)) { 644 return; 645 } 646 Long addInt = 0L; 647 try { 648 addInt = Long.parseLong(s); 649 } catch (Exception e) { 650 e.printStackTrace(); 651 throw new IllegalArgumentException("当前行" + field.getName() + "的数据无法转换为数字类型"); 652 653 } 654 bd = bd + addInt; 655 fieldNameToFinalValueMap.put(fieldName, bd); 656 } else { 657 try { 658 String bd = (String) obj; 659 Long bdInt = Long.parseLong(bd); 660 fieldNameToFinalValueMap.put(fieldName, bdInt); 661 } catch (Exception e) { 662 e.printStackTrace(); 663 throw new IllegalArgumentException("当前行[" + field.getName() + "]的数据无法转换为数字类型"); 664 665 } 666 667 } 668 } 669 } 670 671 672 } 673 } 674 675 /** 676 * @Description: 创建实际数据行的样式,包括文字居中,字号, 边框 677 * @Param: [] 678 * @return: org.apache.poi.ss.usermodel.CellStyle 679 * @Date: 2023/6/30 680 */ 681 private CellStyle createStaticCellStyle(Workbook workbook, boolean blod) { 682 CellStyle cellStyle = createBorderAndCenterCellStyle(workbook); 683 Font font = createFont(workbook, (short) 12, blod); 684 cellStyle.setFont(font); 685 return cellStyle; 686 } 687 688 /** 689 * @Description: 创建一个有边框,内容居中的样式 690 * @Param: [workbook] 691 * @return: org.apache.poi.ss.usermodel.CellStyle 692 * @Date: 2023/7/1 693 */ 694 private CellStyle createBorderAndCenterCellStyle(Workbook workbook) { 695 // 生成一个通用样式 默认背景为白色 696 CellStyle borderStyle = workbook.createCellStyle(); 697 borderStyle.setFillForegroundColor((short) ExcelColumn.StyleColor.WHITE.getValue()); 698 // 单元格内容样式 699 borderStyle.setAlignment(HorizontalAlignment.CENTER); 700 // 单元格是否需要边框 701 borderStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); 702 borderStyle.setBorderBottom(BorderStyle.THIN); 703 borderStyle.setBorderLeft(BorderStyle.THIN); 704 borderStyle.setBorderRight(BorderStyle.THIN); 705 borderStyle.setBorderTop(BorderStyle.THIN); 706 borderStyle.setVerticalAlignment(VerticalAlignment.CENTER); 707 borderStyle.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式 708 return borderStyle; 709 } 710 711 /** 712 * 生成表头行 713 */ 714 private void createHeader(Workbook workbook, Sheet sheet) { 715 Row row = sheet.createRow(currentRowIndex++);//内容开始行的上一行是表头行 716 Cell cell = null; 717 for (int i = 0; i < excelFieldMetadataList.size(); i++) { 718 cell = row.createCell(i); 719 ExcelFieldMetadata excelFieldMetadata = excelFieldMetadataList.get(i); 720 //调用方法设置样式 721 styleToCell(workbook, cell, excelFieldMetadata, true); 722 HSSFRichTextString text = new HSSFRichTextString(excelFieldMetadata.getColumnName()); 723 cell.setCellValue(text); 724 } 725 } 726 727 /** 728 * 创建标题行的方法 729 * 730 * @param workbook 731 * @param sheet 732 */ 733 private void createTitle(Workbook workbook, Sheet sheet) { 734 Row row = sheet.createRow(currentRowIndex++);//标题行 735 row.setHeightInPoints(50);//设置行高 736 Cell titleCell = row.createCell(0);//得到标题列 737 738 //创建一个有边框的样式 739 CellStyle cellStyle = createBorderAndCenterCellStyle(workbook); 740 // 生成一个字体 默认字体微软雅黑 741 Font font = createFont(workbook, (short) 25, true); 742 cellStyle.setFont(font); 743 titleCell.setCellStyle(cellStyle);//设置样式 744 titleCell.setCellValue(titleName);//设置内容 745 746 CellRangeAddress cellAddresses = new CellRangeAddress(row.getRowNum(), 747 row.getRowNum(), row.getRowNum(), excelFieldMetadataList.size() - 1); 748 //合并单元格 749 mergeColumnsStyle(sheet, cellAddresses, cellStyle); 750 sheet.addMergedRegion(cellAddresses); 751 } 752 753 /** 754 * @Description: 创建一个字体 755 * @Param: [workbook wb, fontSize 字体大小, blod 是否加粗] 756 * @return: org.apache.poi.ss.usermodel.Font 757 * @Date: 2023/7/1 758 */ 759 private Font createFont(Workbook workbook, short fontSize, boolean blod) { 760 Font font = workbook.createFont(); 761 // 设置字体大小 762 font.setFontHeightInPoints(fontSize); 763 // 字体加粗 764 font.setBold(blod); 765 return font; 766 } 767 768 /** 769 * @Description: 先设置样式然后再合并单元格 770 * @Param: [workbook, sheet, region, borderCellStyle] 771 * @return: void 772 * @Date: 2023/6/29 773 */ 774 private void mergeColumnsStyle(Sheet sheet, CellRangeAddress region, CellStyle borderCellStyle) { 775 int firstColumn = region.getFirstColumn(); 776 int lastColumn = region.getLastColumn(); 777 int firstRow = region.getFirstRow(); 778 int lastRow = region.getLastRow(); 779 for (int i = firstRow; i <= lastRow; i++) { 780 for (int j = firstColumn; j <= lastColumn; j++) { 781 Cell cell = sheet.getRow(i).getCell(j); 782 if (cell == null) { 783 cell = sheet.getRow(i).createCell(j); 784 } 785 cell.setCellStyle(borderCellStyle); 786 787 } 788 } 789 } 790 791 /** 792 * @param workbook 工作簿 793 * @param cell 要配置的单元格 794 * @param isBold 表头是否加粗 795 * @Title: 设置单元格样式的方法 796 * @Description: 设置单元格以及字体的整体样式 797 */ 798 private void styleToCell(Workbook workbook, Cell cell, ExcelFieldMetadata excelFieldMetadata, boolean isBold) { 799 // 生成一个通用样式 默认背景为白色 800 CellStyle style = workbook.createCellStyle(); 801 style.setFillForegroundColor((short) excelFieldMetadata.getStyleColor()); 802 803 // 单元格内容样式 804 style.setAlignment(HorizontalAlignment.CENTER); 805 806 // 单元格是否需要边框 807 if (excelFieldMetadata.getBorder()) { 808 style.setFillPattern(FillPatternType.SOLID_FOREGROUND); 809 style.setBorderBottom(BorderStyle.THIN); 810 style.setBorderLeft(BorderStyle.THIN); 811 style.setBorderRight(BorderStyle.THIN); 812 style.setBorderTop(BorderStyle.THIN); 813 } 814 815 // 生成一个字体 默认字体微软雅黑 816 Font font = workbook.createFont(); 817 font.setFontName(excelFieldMetadata.getFontName()); 818 font.setColor((short) excelFieldMetadata.getFontColor()); 819 // 设置字体大小 820 font.setFontHeightInPoints(excelFieldMetadata.getFontSize()); 821 // 字体是否加粗 822 if (isBold) { 823 font.setBold(true); 824 } 825 // 把字体应用到当前的样式 826 style.setFont(font); 827 cell.setCellStyle(style); 828 } 829 830 /** 831 * 根据后缀名得到工作簿对象 832 * 833 * @return 返回wk对象 834 */ 835 private Workbook getWorkBook() { 836 if (".xls".equals(ext)) { 837 return new HSSFWorkbook(); 838 } else if (".xlsx".equals(ext)) { 839 return new SXSSFWorkbook(); 840 } 841 return null; 842 } 843 844 /** 845 * @Description: 将excel返回给前端 846 * @Param: [workbook wb, request 请求, response 响应, filename 导出的文件全名称] 847 * @return: void 848 * @Author: java小新人 849 * @Date: 2023/7/1 850 */ 851 public static void downloadFile(Workbook workbook, HttpServletRequest request, HttpServletResponse response, String filename) { 852 try { 853 //从请求头中获取User-Agent判断当前使用的是否是火狐浏览器 854 String agent = request.getHeader("User-Agent"); 855 //根据不同浏览器进行不同的编码 856 String realFilename = ""; 857 if (agent.contains("MSIE")) { 858 // IE浏览器 859 realFilename = URLEncoder.encode(filename, "utf-8"); 860 realFilename = realFilename.replace("+", " "); 861 } else if (agent.contains("Firefox")) { 862 // 火狐浏览器,此处使用java8 863 realFilename = "=?utf-8?B?" + Base64.encodeBase64String(filename.getBytes("utf-8")) + "?="; 864 } else { 865 // 其它浏览器 866 realFilename = URLEncoder.encode(filename, "utf-8"); 867 } 868 //设置要被下载的文件名 869 response.setHeader("Content-Disposition", "attachment;filename=" + realFilename); 870 response.setContentType("application/octet-stream;charset=UTF-8"); 871 response.setCharacterEncoding("UTF-8"); // 设置文件流编码格式 不然中文会乱码 872 response.setHeader("filename", filename); 873 ServletOutputStream os = response.getOutputStream(); 874 workbook.write(os); 875 os.flush(); 876 os.close(); 877 } catch (Exception e) { 878 e.printStackTrace(); 879 } 880 } 881 }
测试案例:
User实体类:
1 package com.example.demo.entity; 2 3 import com.example.demo.enums.SexEnum; 4 import com.example.demo.enums.UserStatusEnum; 5 import com.example.demo.excel.ExcelColumn; 6 import com.example.demo.excel.ExcelSheet; 7 import lombok.Data; 8 import lombok.NoArgsConstructor; 9 import lombok.experimental.Accessors; 10 11 import java.io.Serializable; 12 import java.util.Date; 13 14 @Data 15 @NoArgsConstructor 16 @Accessors(chain = true) 17 @ExcelSheet(sheetName = "测试sheetNme", titleName = "测试标题") 18 public class User implements Serializable { 19 @ExcelColumn(columnName = "性别", sort = 4, convertEnumType = SexEnum.class, convertEnumMethodName = "getDescription") 20 private Integer sex; 21 /** 22 * 用户姓名 23 */ 24 @ExcelColumn(columnName = "姓名", sort = 2) 25 private String username; 26 27 @ExcelColumn(columnName = "密码", sort = 3) 28 private String password; 29 30 31 @ExcelColumn(columnName = "邮件", sort = 5) 32 private String email; 33 34 /** 35 * 主键id 36 */ 37 @ExcelColumn(columnName = "编号", sort = 1) 38 private Integer id; 39 40 //零花钱进行累加操作 41 @ExcelColumn(columnName = "零花钱", sort = 6, opertorType = ExcelColumn.OperatorType.SUM) 42 private String money; 43 44 45 //数据状态 46 @ExcelColumn(columnName = "状态", sort = 7, convertEnumType = UserStatusEnum.class, convertEnumMethodName = "getDescription") 47 private String status; 48 private Date createTime; 49 50 private String updateTime; 51 52 53 }
码值转换枚举:
1 package com.example.demo.enums; 2 3 import lombok.*; 4 import org.apache.commons.lang3.StringUtils; 5 6 /** 7 * @author java小新人 8 * @title SexEnum 9 * @date 2023/6/29 12:35 10 * @description TODO 11 */ 12 @AllArgsConstructor 13 @NoArgsConstructor 14 @Getter 15 public enum SexEnum { 16 men(0,"女"), 17 man(1,"男"); 18 private Integer code; 19 private String description; 20 21 /** 22 * @Description: 根据码值遍历获取对应的中文 23 * @Param: [code] 24 * @return: java.lang.String 25 * @Date: 2023/6/30 26 */ 27 public static String getDescription(Integer code){ 28 if (code==null){ 29 return ""; 30 } 31 for(SexEnum e : SexEnum.values()){ 32 if(e.code.equals(code)){ 33 return e.description; 34 } 35 } 36 return ""; 37 } 38 } 39 40 41 42 43 44 45 46 47 package com.example.demo.enums; 48 49 import lombok.AllArgsConstructor; 50 import lombok.Getter; 51 import lombok.NoArgsConstructor; 52 import org.apache.commons.lang3.StringUtils; 53 54 /** 55 * @author java小新人 56 * @title UserStatusEnum 57 * @date 2023/6/29 12:35 58 * @description TODO 59 */ 60 @AllArgsConstructor 61 @NoArgsConstructor 62 @Getter 63 public enum UserStatusEnum { 64 men("0","删除"), 65 man("1","正常"); 66 private String code; 67 private String description; 68 69 /** 70 * @Description: 根据码值遍历获取对应的中文 71 * @Param: [code] 72 * @return: java.lang.String 73 * @Date: 2023/6/30 74 */ 75 public static String getDescription(String code){ 76 if (StringUtils.isBlank(code)){ 77 return ""; 78 } 79 for(UserStatusEnum e : UserStatusEnum.values()){ 80 if(e.code.equals(code)){ 81 return e.description; 82 } 83 } 84 return ""; 85 } 86 }
controller:
1 @RequestMapping("/testExcel") 2 @ResponseBody 3 public void testExcel(HttpServletRequest request, HttpServletResponse response){ 4 List<User> list = getList(); 5 new MallExcelUtils<User>() 6 .setClazz(User.class)//实体类类型 7 .setSecondRowLeftAndRight("操作时间:2023-06-12至2023-06-30","导出人:小王") 8 .setSecondLevelHeader(Collections.singletonList("二级标题#3,4,1,3"))//二级标题 内容#firstRow,lastRow,firstColumn,lastColumn 9 .setDataList(list)//实际的数据集合 10 .setFullFileName("test.xls")//导出文件的名称 11 .initlization()//开始初始化 12 .exportExcel(request,response);//实际以流的方式导出 13 } 14 15 16 private List<User> getList(){ 17 List<User> userList = Lists.newArrayList(); 18 User user = new User(); 19 user.setUsername("wang") 20 .setPassword("111") 21 .setId(123) 22 .setSex(1) 23 .setEmail("wang@gmail.com") 24 .setMoney("100") 25 .setStatus("1"); 26 27 User user2 = new User(); 28 user2.setUsername("li"). 29 setPassword("222") 30 .setId(456) 31 .setSex(0) 32 .setEmail("li@gmail.com") 33 .setMoney("20") 34 .setStatus("0");; 35 36 userList.add(user); 37 userList.add(user2); 38 return userList; 39 }
效果:
17. mybatis分页插件(针对一些特别老的项目,不想引入依赖或者jar包, 暂时只支持mysql,可以自己扩展oracle,就改一下limit分页语句就好啦)
1 import org.apache.commons.lang3.StringUtils; 2 import org.apache.ibatis.executor.parameter.ParameterHandler; 3 import org.apache.ibatis.executor.resultset.ResultSetHandler; 4 import org.apache.ibatis.executor.statement.StatementHandler; 5 import org.apache.ibatis.mapping.BoundSql; 6 import org.apache.ibatis.mapping.MappedStatement; 7 import org.apache.ibatis.plugin.*; 8 import org.apache.ibatis.reflection.MetaObject; 9 import org.apache.ibatis.reflection.SystemMetaObject; 10 import org.apache.ibatis.scripting.defaults.DefaultParameterHandler; 11 import org.apache.log4j.Logger; 12 13 import java.sql.*; 14 import java.util.List; 15 import java.util.Objects; 16 import java.util.Properties; 17 18 /** 19 * @Description 自定义mybatis分页插件, 暂时只支持msql, 需要的可以扩展 20 * @Author 21 * @Date 2023年4月12日 17:20:34 22 */ 23 @Intercepts({ 24 @Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class}), 25 @Signature(type = ResultSetHandler.class, method = "handleResultSets", args = {Statement.class}) 26 }) 27 public class PageHelper implements Interceptor { 28 29 private static final Logger log = Logger.getLogger(PageHelper.class); 30 31 /** 32 * 数据库方言 33 */ 34 private String dbDialet; 35 36 /** 37 * 默认查询条数 38 */ 39 public static final Integer DEFAULT_PAGE_SIZE = 10; 40 41 /** 42 * 数据库方言-MySQL 43 */ 44 private static final String DB_DIALET_MYSQL = "mysql"; 45 //private static final String DB_DIALET_ORACLE = "oracle"; 46 47 /** 48 * 存Page对象的线程局部变量 49 */ 50 public static final ThreadLocal<Page> localPage = new ThreadLocal<>(); 51 52 53 @Override 54 public Object intercept(Invocation invocation) throws Throwable { 55 if (localPage.get() == null) { 56 return invocation.proceed(); 57 } 58 try { 59 if (invocation.getTarget() instanceof StatementHandler) { 60 StatementHandler statementHandler = (StatementHandler) invocation.getTarget(); 61 MetaObject metaObject = SystemMetaObject.forObject(statementHandler); 62 //对象可能被多次代理,使用下面两次循环拿到原始对象 63 while (metaObject.hasGetter("h")) { 64 Object object = metaObject.getValue("h"); 65 metaObject = SystemMetaObject.forObject(object); 66 } 67 while (metaObject.hasGetter("target")) { 68 Object object = metaObject.getValue("target"); 69 metaObject = SystemMetaObject.forObject(object); 70 } 71 72 MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement"); 73 BoundSql boundSql = (BoundSql) metaObject.getValue("delegate.boundSql"); 74 Page page = localPage.get(); 75 String sql = boundSql.getSql(); 76 77 //重写sql,添加Mysql分页参数 78 String pageSql = buildPageSql(sql, page); 79 metaObject.setValue("delegate.boundSql.sql", pageSql); 80 Connection connection = (Connection) invocation.getArgs()[0]; 81 //使用jdbc查询并设置记录总数 82 setPageParameter(sql, connection, mappedStatement, boundSql, page); 83 //将执行器交给下一个拦截器 84 return invocation.proceed(); 85 } else if (invocation.getTarget() instanceof ResultSetHandler) { 86 Object result = invocation.proceed(); 87 Page page = localPage.get(); 88 //设置实际的List数据 89 page.setRecords((List) result); 90 return result; 91 } 92 } catch (Throwable ignore) { 93 //发生异常的时候清空localPage,防止内存泄漏 94 localPage.remove(); 95 log.error("查询数据库失败", ignore); 96 } 97 return invocation.proceed(); 98 } 99 100 /** 101 * 获取记录总数和最大的页数 102 * 103 * @param sql 原始sql语句 104 * @param connection 连接 105 * @param MappedStatement mappedStatement对象 106 * @param boundSql sql的封装对象 原始sql语句 107 * @param page Page对象 108 */ 109 private void setPageParameter(String sql, Connection connection, MappedStatement mappedStatement, BoundSql boundSql, Page page) { 110 String countSql = "SELECT count(0) from (" + sql + ") as total"; 111 PreparedStatement countStmt = null; 112 ResultSet rs = null; 113 try { 114 countStmt = connection.prepareStatement(countSql); 115 BoundSql countBS = new BoundSql(mappedStatement.getConfiguration(), countSql, boundSql.getParameterMappings(), boundSql.getParameterObject()); 116 setParameter(countStmt, mappedStatement, countBS, boundSql.getParameterObject()); 117 rs = countStmt.executeQuery(); 118 int totalCount = 0; 119 if (rs.next()) { 120 totalCount = rs.getInt(1); 121 } 122 //设置最大条数和最大页数 123 page.setItemCount(totalCount); 124 int totalPage = totalCount / page.getPageSize() + ((totalCount % page.getPageSize() == 0) ? 0 : 1); 125 page.setPageCount(totalPage); 126 } catch (SQLException e) { 127 e.printStackTrace(); 128 log.error("自定义分页组件PageHelper,查询条数异常",e); 129 } finally { 130 try { 131 if (rs != null) { 132 rs.close(); 133 } 134 } catch (SQLException ignore) { 135 log.error("自定义分页插件PageHelper关闭ResultSet失败", ignore); 136 } 137 try { 138 countStmt.close(); 139 } catch (SQLException ignore) { 140 log.error("自定义分页插件PageHelper关闭PreparedStatement失败", ignore); 141 } 142 } 143 144 145 } 146 147 /** 148 * 设置参数值 149 * 150 * @param countStmt 151 * @param mappedStatement 152 * @param countBS 原始sql 153 * @param parameterObject 154 * @throws SQLException 155 */ 156 private void setParameter(PreparedStatement countStmt, MappedStatement mappedStatement, BoundSql countBS, Object parameterObject) throws SQLException { 157 ParameterHandler parameterHandler = new DefaultParameterHandler(mappedStatement, parameterObject, countBS); 158 parameterHandler.setParameters(countStmt); 159 } 160 161 /** 162 * 重写sql 163 * 164 * @param sql 原始sql 165 * @param page 分页对象 166 * @return 返回包装的sql 167 */ 168 private String buildPageSql(String sql, Page page) { 169 long startRow = (page.getPageNum() - 1) * page.getPageSize(); 170 long endRow = page.getPageNum() * page.getPageSize(); 171 //拼接sql的分页语句--MySQL 172 if (Objects.equals(dbDialet, DB_DIALET_MYSQL)) { 173 return new StringBuilder().append(sql) 174 .append(" LIMIT ") 175 .append(startRow) 176 .append(",") 177 .append(endRow - startRow) 178 .toString(); 179 } 180 return ""; 181 182 } 183 184 185 @Override 186 public Object plugin(Object target) { 187 //把自定义的插件加入到mybatis中去执行 188 //只拦截StatementHandler和ResultSetHandler这两种类型 189 if (target instanceof StatementHandler || target instanceof ResultSetHandler) { 190 return Plugin.wrap(target, this); 191 } 192 return target; 193 194 } 195 196 //启动的时候初始化时调用 197 @Override 198 public void setProperties(Properties properties) { 199 //获取配置中的参数,适配不同的数据库 200 String dbDialet = properties.getProperty("pageHelper.db.type", DB_DIALET_MYSQL); 201 if (Objects.equals(dbDialet, DB_DIALET_MYSQL)) { 202 this.dbDialet = dbDialet; 203 } else { 204 log.error("自定义的分页插件不支持当前配置的数据库类型:" + dbDialet); 205 } 206 207 } 208 209 210 /** 211 * 开始分页 212 * 213 * @param pageNum 页码 214 * @param pageSize 每一页数据量 215 */ 216 public static void startPage(int pageNum, int pageSize) throws IllegalArgumentException { 217 checkPageParam(pageNum,pageSize); 218 localPage.set(new Page(pageNum, pageSize)); 219 } 220 221 /** 222 * 开始分页 223 * 224 * @param pageNum 页码 225 * @param pageSize 每一页数据量 226 */ 227 public static void startPage(String pageNumStr, String pageSizeStr) throws IllegalArgumentException { 228 int pageNum = 1; 229 int pageSize = DEFAULT_PAGE_SIZE; 230 if (StringUtils.isNotBlank(pageNumStr)){ 231 try { 232 pageNum = Integer.parseInt(pageNumStr); 233 } catch (NumberFormatException e) { 234 pageNum = 1; 235 } 236 } 237 if (StringUtils.isNotBlank(pageSizeStr)){ 238 try { 239 pageSize= Integer.parseInt(pageSizeStr); 240 } catch (NumberFormatException e) { 241 pageSize = DEFAULT_PAGE_SIZE; 242 } 243 } 244 checkPageParam(pageNum,pageSize); 245 localPage.set(new Page(pageNum, pageSize)); 246 } 247 248 /** 249 * 校验分页参数是否合规 250 * @param pageNum 251 * @param pageSize 252 * @throws IllegalArgumentException 253 */ 254 private static void checkPageParam(int pageNum, int pageSize) throws IllegalArgumentException { 255 if (pageNum<1){ 256 throw new IllegalArgumentException("自定义分页插件PageHelper,分页参数pageNum不合规:"+pageNum); 257 } 258 if (pageSize<0){ 259 throw new IllegalArgumentException("自定义分页插件PageHelper,分页参数pageSize不合规:"+pageSize); 260 } 261 } 262 263 /** 264 * 结束分页 265 * 266 * @param <T> 267 * @return 268 */ 269 public static <T> Page<T> endPage() { 270 Page<T> page = localPage.get(); 271 localPage.remove(); 272 return page; 273 } 274 } 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 import java.io.Serializable; 294 import java.util.ArrayList; 295 import java.util.Collections; 296 import java.util.List; 297 298 /** 299 * 分页参数 300 * 301 * @param <T> 302 */ 303 public class Page<T> implements Serializable { 304 /** 305 * 当前页 306 */ 307 private Integer pageNum = 0; 308 /** 309 * 每页的条数,默认10条 310 */ 311 private Integer pageSize = PageHelper.DEFAULT_PAGE_SIZE; 312 /** 313 * 总条数 314 */ 315 private Integer itemCount = 0; 316 /** 317 * 总的页数 318 */ 319 private Integer pageCount = 0; 320 /** 321 * 封装的数据 322 */ 323 private List<T> records = Collections.emptyList(); 324 325 /** 326 * 翻页页码列表 327 */ 328 private List<Integer> pageNumList = Collections.emptyList(); 329 330 public Page(Integer pageNum, Integer pageSize) { 331 this.pageNum = pageNum; 332 this.pageSize = pageSize; 333 } 334 335 public Page() { 336 } 337 338 public Integer getPageNum() { 339 return pageNum; 340 } 341 342 public Page<T> setPageNum(Integer pageNum) { 343 this.pageNum = pageNum; 344 return this; 345 } 346 347 public Integer getPageSize() { 348 return pageSize; 349 } 350 351 public Page<T> setPageSize(Integer pageSize) { 352 this.pageSize = pageSize; 353 return this; 354 } 355 356 public Integer getItemCount() { 357 return itemCount; 358 } 359 360 public Page<T> setItemCount(Integer itemCount) { 361 this.itemCount = itemCount; 362 return this; 363 } 364 365 public Integer getPageCount() { 366 return pageCount; 367 } 368 369 public Page<T> setPageCount(Integer pageCount) { 370 this.pageCount = pageCount; 371 return this; 372 } 373 374 public List<T> getRecords() { 375 return records; 376 } 377 378 public Page<T> setRecords(List<T> records) { 379 this.records = records; 380 return this; 381 } 382 383 public List<Integer> getPageNumList() { 384 return pageNumList; 385 } 386 387 public Page<T> setPageNumList(List<Integer> pageNumList) { 388 this.pageNumList = pageNumList; 389 return this; 390 } 391 }
使用前注意将此自定义的PageHelper配置到mybatis的xml文件中,或者注解的方式,看项目的实际配置
使用示例:
PageHelper.startPage(1,10);
userDao.selectList(xxx);
Page<User> page = PageHelper.endPage();
List<User> userList = page.getRecords();
18.
后续更新