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 }
View Code

 

注解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 }
View Code

 

  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 }
View Code

  

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 }
View Code

 

  

测试案例:

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 }
View Code

  

码值转换枚举:

 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 }
View Code

 

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     }
View Code

 

效果:

 

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 }
View Code

 

使用前注意将此自定义的PageHelper配置到mybatis的xml文件中,或者注解的方式,看项目的实际配置

使用示例:

PageHelper.startPage(1,10);

userDao.selectList(xxx);

Page<User> page = PageHelper.endPage();

List<User> userList = page.getRecords();

 

 

18.

 

后续更新

posted @ 2022-12-21 15:17  java小新人  阅读(2983)  评论(0编辑  收藏  举报