常用封装方法

常用封装方法

1.递归遍历层级关系

  *   [
     *      {
     *          id:
     *          name:
     *          children:[
     *              {
     *                  id:
     *                  name:
     *                  children:[
     *
     *                  ]
     *              }
     *          ]
     *
     *      }
     *
     *   ]
     */
  数据库使用 parent_id字段进行区分层级关系

   public static List<SysMenu> buildTree(List<SysMenu> sysMenus){

        List<SysMenu> trees = new ArrayList<>();
        for (SysMenu sysMenu : sysMenus) {
            //查找第一层
            if(sysMenu.getParentId().longValue() == 0){
                trees.add(children(sysMenu,sysMenus));
            }
        }

        return trees;
    }
    public static SysMenu children(SysMenu sysMenu,List<SysMenu> nodes){
        /**
         *      // ======  nodes ={
         *                          id: 2
         *                          title : 用户管理
         *                          parent_id : 1
         *                   }
         *    sysMenu  = {
         *        id: 1
         *        title: 系统管理
         *        parent_id: 0
         *        children:[
         *
         *        ]
         *        ....
         *
         *    }
         */
        sysMenu.setChildren(new ArrayList<>());
        for (SysMenu menu : nodes) {
            //每个固定的id和循环的父id比较，如果一致代表是子节点，添加进去
            if(menu.getParentId().longValue() == sysMenu.getId().longValue()){
                sysMenu.getChildren().add(children(menu,nodes));
            }
        }
        return sysMenu;
    }

2.递归删除

	//递归删除菜单
	@Override
	public boolean removeChildById(Long id) {
		List<Long> idList = new ArrayList<>();
		this.selectChildListById(id, idList);
		idList.add(id);
		baseMapper.deleteBatchIds(idList);
		return true;
	}

	/**
	 *	递归获取子节点
	 * @param id
	 * @param idList
	 */
	private void selectChildListById(Long id, List<Long> idList) {
		List<Permission> childList = baseMapper.selectList(new QueryWrapper<Permission>().eq("pid", id).select("id"));
		childList.stream().forEach(item -> {
			idList.add(item.getId());
			this.selectChildListById(item.getId(), idList);
		});
	}

3.minio上传文件

    public String uploadFile(MultipartFile file) {
        try {
            // 创建一个Minio的客户端对象 
            MinioClient minioClient = MinioClient.builder()
                /*配置文件读取
                  minio:
                    endpointUrl: http://127.0.0.1:9000
                    accessKey: minioadmin
                    secreKey: minioadmin
                    bucketName: spzx-bucket
                */
                    .endpoint(minioProperties.getEndpointUrl())
                    .credentials(minioProperties.getAccessKey(), minioProperties.getSecreKey())
                    .build();

            // 判断桶是否存在
            boolean found = minioClient.bucketExists(BucketExistsArgs.builder().bucket(minioProperties.getBucketName()).build());
            if (!found) {       // 如果不存在，那么此时就创建一个新的桶
                minioClient.makeBucket(MakeBucketArgs.builder().bucket(minioProperties.getBucketName()).build());
            } else {  // 如果存在打印信息
                System.out.println("Bucket 'spzx-bucket' already exists.");
            }

            // 设置存储对象名称
            String dateDir = DateUtil.format(new Date(), "yyyyMMdd");
            String uuid = UUID.randomUUID().toString().replace("-", "");
            //20230801/443e1e772bef482c95be28704bec58a901.jpg
            String fileName = dateDir+"/"+uuid+file.getOriginalFilename();

            PutObjectArgs putObjectArgs = PutObjectArgs.builder()
                    .bucket(minioProperties.getBucketName())
                    .stream(file.getInputStream(), file.getSize(), -1)
                    .object(fileName)
                    .build();
            minioClient.putObject(putObjectArgs) ;

            return minioProperties.getEndpointUrl() + "/" + minioProperties.getBucketName() + "/" + fileName ;

        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

4.Sql语句

  select DATE_FORMAT(oi.create_time ,'%Y-%m-%d') orderDate, sum(oi.total_amount)  totalAmount , count(oi.id) totalNum
  from order_info oi where DATE_FORMAT(oi.create_time ,'%Y-%m-%d') = #{lastDay}
  GROUP BY DATE_FORMAT(oi.create_time ,'%Y-%m-%d')
 #统计每日金额

	select
            p.id, p.name , p.brand_id , p.category1_id , p.category2_id , p.category3_id, p.unit_name,
            p.slider_urls , p.spec_value , p.status , p.audit_status , p.audit_message , p.create_time , p.update_time , p.is_deleted ,
            b.name brandName , c1.name category1Name , c2.name category2Name , c3.name category3Name
        from product p
                 LEFT JOIN brand b on b.id = p.brand_id
                 LEFT JOIN category c1 on c1.id = p.category1_id
                 LEFT JOIN category c2 on c2.id = p.category2_id
                 LEFT JOIN category c3 on c3.id = p.category3_id
        where
            p.id = #{id}
 #三级菜单

5.MybatisPlus自定义Sql

 //Service
 public IPage<CategoryBrand> findByPage(Integer page, Integer limit, CategoryBrandDto categoryBrandDto) {
      QueryWrapper<CategoryBrand> wapper = new QueryWrapper<>();
      if(categoryBrandDto.getCategoryId()!=null){
          wapper.eq("category_id",categoryBrandDto.getCategoryId());
      }
      if(categoryBrandDto.getBrandId()!=null){
          wapper.eq("brand_id",categoryBrandDto.getBrandId());
      }
      wapper.eq("cb.is_deleted",0);
      Page<CategoryBrand> dbPage = new Page<>(page, limit);
      return categoryBrandMapper.findByPage(dbPage,wapper);
  }
  //Mapper
   IPage<CategoryBrand> findByPage(Page<CategoryBrand> dbPage, @Param(Constants.WRAPPER) QueryWrapper<CategoryBrand> wapper);
  //Mapper.xml
     <select id="findByPage" resultMap="categoryBrandMap">
        select
        cb.id,cb.brand_id,cb.category_id,cb.create_time,cb.update_time,
        c.name as categoryName,
        b.name as brandName, b.logo
        from category_brand cb
        left join category c on c.id = cb.category_id
        left join brand b  on b.id = cb.brand_id
        ${ew.customSqlSegment}
        order by cb.id desc
    </select>

6.EasyExcel导入导出

1.导出

    @Override
    public void exportData(HttpServletResponse response) {
        // 设置响应结果类型
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        try {
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode("分类数据", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");

            // 查询数据库中的数据
            List<Category> categoryList = categoryMapper.selectList(null);
            List<CategoryExcelVo> categoryExcelVoList = new ArrayList<>(categoryList.size());

            for (Category category : categoryList) {
                CategoryExcelVo categoryExcelVo = new CategoryExcelVo();
                BeanUtils.copyProperties(category,categoryExcelVo);
                categoryExcelVoList.add(categoryExcelVo);
            }
            EasyExcel.write(response.getOutputStream(),
                    CategoryExcelVo.class).sheet("分类数据").doWrite(categoryExcelVoList);
            
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

    }

2.导入

    public void importData(MultipartFile file) {
        try {
            //创建监听器对象，传递mapper对象
            ExcelListener<CategoryExcelVo> excelListener = new ExcelListener<>(this);
            //调用read方法读取excel数据
            EasyExcel.read(file.getInputStream(),
                    CategoryExcelVo.class,
                    excelListener).sheet().doRead();
        } catch (IOException e) {
            throw new DragonException(ResultCodeEnum.DATA_ERROR);
        }
    }
    
    //============监听器
   private static final int BATCH_COUNT = 100;
    /**
     * 缓存的数据
     */
    private List<Category> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

    //获取service对象
    private CategoryService categoryService;
    public ExcelListener(CategoryService categoryService) {
        this.categoryService = categoryService;
    }

    // 每解析一行数据就会调用一次该方法
    @Override
    public void invoke(T o, AnalysisContext analysisContext) {
        CategoryExcelVo data = (CategoryExcelVo)o;
        Category category = new Category();
        BeanUtils.copyProperties(data,category);
        cachedDataList.add(category);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // excel解析完毕以后需要执行的代码
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
    }

    private void saveData() {
        categoryService.saveBatch(cachedDataList);
      //  categoryMapper.batchInsert(cachedDataList);
    }