实战项目——智慧社区（二）之物业管理

分页

用于分页封装的实体类

@Data
public class PageVO {
    private Long totalCount;
    private Long pageSize;
    private Long totalPage;
    private Long currPage;
    private List list;
}

分页的相关配置

package com.qcby.community.configuration;

import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class PageConfiguration {

    @Bean
    public PaginationInterceptor paginationInterceptor(){
        return new PaginationInterceptor();
    }
}

增删改查的实现思路

对于传入参数，如果有，但是参数不多，只有一个，直接接收；如果参数较多，应将其传入字段封装在一个实体类中。对于get请求，用@PathVariable接收，对于非get请求，用@RequestBody接收。

对于返回参数，如果与数据单表中的字段可以进行一一映射，不需要考虑封装，直接返回；如果无法在单表中进行一一映射，则需要根据返回参数进行实体类封装。

对于单表的增删改查，mybatiaPlus提供了相应的操作，对于多表则需要手写sql语句

1、小区管理

①小区搜索和查询

传入参数

{
"page": 1,
"limit": 10,
"communityId": "",
"communityName": ""
}

返回参数

{
"msg": "操作成功",
"code": 200,
"data": {
"totalCount": 8,
"pageSize": 10,
"totalPage": 1,
"currPage": 1,
"list": [
{
"communityId": 19,
"communityName": "北清云际",
"termCount": 33,
"seq": 190,
"creater": "admin",
"createTime": "2023-07-18 00:41:20",
"lng": 116.298904,
"lat": 40.091644,
"personCnt": 1
},
{
"communityId": 17,
"communityName": "天龙苑",
"termCount": 10,
"seq": 170,
"creater": "admin",
"createTime": "2023-07-18 00:38:06",
"lng": 116.36206,
"lat": 40.088108,
"personCnt": 0
}
]
}
}

实现思路：由于返回list中在community表中没有personCnt字段，因此需要封装一个CommunityVO类用于数据的返回。同时由于mybtisPlus中并没有现成的sql用于根据community_id查询相应的居民人数，因此需要编写sql用来查询每个community对于的人数personCnt

CommunityVO类

@Data
public class CommunityVO {
    private Integer communityId;
    private String communityName;
    private Integer termCount;
    private Integer seq;
    private String creater;
    private Date createTime;
    private Float lng;
    private Float lat;
    private Integer personCnt;
}

controller

/**
     * 获取小区的所有信息，分页条件查询
     * @return
     */
    @GetMapping("/list")
    public Result getList(CommunityListForm communityListForm){
        PageVO pageVO = this.communityService.communityList(communityListForm);
        return Result.ok().put("data", pageVO);
    }

service

 @Override
    public PageVO communityList(CommunityListForm communityListForm) {
        Page<Community> page = new Page<>(communityListForm.getPage(), communityListForm.getLimit());
        QueryWrapper<Community> queryWrapper = new QueryWrapper<>();
        queryWrapper.like(StringUtils.isNotBlank(communityListForm.getCommunityName()),"community_name", communityListForm.getCommunityName());
        Page<Community> resultPage = this.communityMapper.selectPage(page, queryWrapper);
        PageVO pageVO = new PageVO();
        List<CommunityVO> list = new ArrayList<>();
        for (Community record : resultPage.getRecords()) {
            CommunityVO communityVO = new CommunityVO();
            BeanUtils.copyProperties(record, communityVO);
            communityVO.setPersonCnt(this.personMapper.getCountByCommunityId(record.getCommunityId()));
            list.add(communityVO);
        }
        pageVO.setList(list);
        pageVO.setTotalCount(resultPage.getTotal());
        pageVO.setPageSize(resultPage.getSize());
        pageVO.setCurrPage(resultPage.getCurrent());
        pageVO.setTotalPage(resultPage.getPages());
        return pageVO;
    }

根据communityId查询居民人数的mapper

@Select({
            "select count(*) from person where community_id = #{communityId} "
    })
    Integer getCountByCommunityId(Integer communityId);

界面

②添加小区

传入参数

{
"communityName": "test"
"lat": "1"
"lng": "1"
"seq": 1
"termCount": "1"
}

返回参数

{
"msg": "操作成功",
"code": 200
}

controller

    /**
     * 添加小区
     * @param community
     * @param session
     * @return
     */
    @LogAnnotation("添加小区")
    @PostMapping("/add")
    public Result add(@RequestBody Community community, HttpSession session){
        User user = (User)session.getAttribute("user");
        community.setCreater(user.getUsername());
        boolean save = this.communityService.save(community);
        if(!save) return Result.error("小区添加失败");
        return Result.ok();
    }

界面

③通过id查询小区

传入参数

1

返回参数

{
"msg": "操作成功",
"code": 200,
"data": {
"communityId": 19,
"communityName": "北清云际",
"termCount": 33,
"seq": 190,
"lng": 116.298904,
"lat": 40.091644
}
}

controller

    /**
     * 修改的回显操作
     * @param id
     * @return
     */
    @GetMapping("/info/{id}")
    public Result info(@PathVariable("id") Integer id){
        Community community = this.communityService.getById(id);
        if(community == null) return Result.error("小区不存在");
        return Result.ok().put("data", community);
    }

④修改小区

传入参数

{
"communityId": 21,
"communityName": "1",
"termCount": "12",
"lng": "12",
"lat": "12",
"seq": 210
}

返回参数

{
"msg": "操作成功",
"code": 200
}

controller

     /**
     * 修改小区信息
     * @param community
     * @return
     */
    @LogAnnotation("编辑小区")
    @PutMapping("/edit")
    public Result edit(@RequestBody Community community){
        boolean updateById = this.communityService.updateById(community);
        if(!updateById) return Result.error("编辑小区失败");
        return Result.ok();
    }

界面

⑤删除小区

传入参数

[
21,
20
]

返回参数

{
"msg": "操作成功",
"code": 200
}

controller

 /**
     * 删除小区，同时删除相关表中的信息
     * @param ids
     * @return
     */
    @LogAnnotation("删除小区")
    @DeleteMapping("/del")
    @Transactional    //事务管理
    public Result del(@RequestBody Integer[] ids){
        try{
            QueryWrapper<Camera> cameraQueryWrapper = new QueryWrapper<>();
            cameraQueryWrapper.in("community_id", ids);
            boolean remove1 = this.cameraService.remove(cameraQueryWrapper);
//            if(!remove1) return Result.error("小区摄像头删除失败");
            QueryWrapper<InOutRecord> inOutRecordQueryWrapper = new QueryWrapper<>();
            inOutRecordQueryWrapper.in("community_id", ids);
            boolean remove2 = this.inOutRecordService.remove(inOutRecordQueryWrapper);
//            if(!remove2) return Result.error("小区出入记录删除失败");
            QueryWrapper<ManualRecord> manualRecordQueryWrapper = new QueryWrapper<>();
            manualRecordQueryWrapper.in("community_id", ids);
            boolean remove3 = this.manualRecordService.remove(manualRecordQueryWrapper);
//            if(!remove3) return Result.error("小区访客记录删除失败");
            QueryWrapper<Person> personQueryWrapper = new QueryWrapper<>();
            personQueryWrapper.in("community_id", ids);
            boolean remove4 = this.personService.remove(personQueryWrapper);
//            if(!remove4) return Result.error("小区居民删除失败");
            boolean remove5 = this.communityService.removeByIds(Arrays.asList(ids));
//            if(!remove5) return Result.error("小区删除失败");
        }catch(Exception e){
            e.printStackTrace();
            return Result.error("小区删除失败");
        }
        return Result.ok();
    }

⑥摄像头管理

查询摄像头、添加摄像头、通过id查询摄像头、修改摄像头、删除摄像头，其操作和上述的小区操作相似，这里不再举例

摄像头（camera）表设计

界面

2、居民管理

查询和搜索居民、查询小区列表、添加居民、修改居民、删除居民，其操作和上述的小区操作相似，这里不再详写

①人脸采集

这里选择调用腾讯人脸识别的API接口进行人脸检测与上传，因此在人脸采集之前需要做一些准备工作。

腾讯人脸识别API接口申请

登录腾讯云，搜索访问管理，创建密钥

对应 application.yml 中的

secretId: AKID3CXqCeGxiOE1snomp9ThopkZIyXypZEl
secretKey: c7awCRbecXL0b9e4Sk2HVyfVONVk4VvF

搜索人脸识别，创建人员库

对应 application.yml 中的

groupId: 1001

文件映射的配置

package com.qcby.community.configuration;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Configuration;
import org.springframework.web.servlet.config.annotation.ResourceHandlerRegistry;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;

@Configuration
public class WebMvcConfiguration implements WebMvcConfigurer {
    @Value("${upload.face}")
    String face;
    @Value("${upload.excel}")
    String excel;

    @Override
    public void addResourceHandlers(ResourceHandlerRegistry registry) {
        registry.addResourceHandler("/community/upload/face/**").addResourceLocations("file:"+face);
        registry.addResourceHandler("/community/upload/excel/**").addResourceLocations("file:"+excel);
    }
}

传入参数

{
"personId": 98,
"extName": "png",
"fileBase64": "iVBORw0KGgoAAAANSUhEUgAAAJsAAAC4CAYAAAD0WZ4UAAAAAXNSR0IArs4c6QAAAARnQU1BAACxjwv8YQUAAAAJ8pt02jBn6wUmcy/39/xgi2nSFeQGzAAAAAElFTkSuQmCC"
}

返回参数

{
"msg": "操作成功",
"code": 200
}

实现思路

①进行程序严谨性判断：根据personId查询person信息，首先判断居民存在与否，然后判断居民是否已经录入了人脸，最后判断居民上传的图片是否为Base64编码的图片，有一个不符合都不进行人脸的录入

②调用腾讯API接口进行人脸检测，判断上传的图片是否为一张人脸

③将人脸图片上传到服务器，并且更新person表中人脸图片的地址以及识别状态

人脸识别的实现

    @Autowired
    private ApiConfiguration apiConfiguration;
    @Value("${upload.face}")
    private String face;
    @Value("${upload.urlPrefix}")
    private String urlPrefix;

    /**
     * 录入人脸信息
     * @param personFaceForm
     * @return
     */
    @LogAnnotation("人脸采集")
    @PostMapping("/addPerson")
    public Result addPerson(@RequestBody PersonFaceForm personFaceForm){
        Person person = this.personService.getById(personFaceForm.getPersonId());
        //程序严谨性判断
        if(person == null){
            return Result.error("居民不存在");
        }
        if(person.getState() == 2){
            return Result.error("人脸识别已通过，不需要重复识别");
        }
        if(personFaceForm.getFileBase64() == null || personFaceForm.getFileBase64().equals("")){
            return Result.error("请上传Base64编码的图片");
        }
        //人脸检测
        if(apiConfiguration.isUsed()){
            //调用腾讯API人脸识别
            String faceId = newPerson(personFaceForm,person.getUserName());
            if(faceId == null){
                return Result.error("人脸识别失败");
            }
            if (faceId != null) {
                //生成头像访问路径
                String filename = faceId + "." + personFaceForm.getExtName();
                String faceUrl = urlPrefix + "community/upload/face/" + filename;
                person.setFaceUrl(faceUrl);
                person.setState(2);
                //更新人脸识别状态及图片地址
                this.personService.updateById(person);
                return Result.ok();
            }

//            //模拟人脸识别
//            //生成模拟id
//            String faceId = RandomUtil.getBitRandom();
//            String faceBase = personFaceForm.getFileBase64().substring(0, 60);
//            // 如果不是头像
//            if(faceBase.equals("iVBORw0KGgoAAAANSUhEUgAAAoAAAAHgCAYAAAA10dzkAAAAAXNSR0IArs4c")) {
//                return Result.error("人脸识别失败");
//            }
//            //存储头像
//            String filename = faceId + "." + personFaceForm.getExtName();
//            String savePath = face + filename;
//            try {
//                Base64Util.decoderBase64File(personFaceForm.getFileBase64(), savePath);
//            } catch (Exception e) {
//                e.printStackTrace();
//            }
//            //生成头像访问路径
//            String faceUrl = urlPrefix + "community/upload/face/" + filename;
//            person.setFaceUrl(faceUrl);
//            person.setState(2);
//            person.setFaceBase(faceBase);
//            //更新人脸识别状态及图片地址
//            this.personService.updateById(person);
//            return Result.ok();
        }
        return Result.error("未开启人脸识别");
    }

    private String newPerson(PersonFaceForm vo,String personName) {
        String faceId = null;
        String faceBase64 = vo.getFileBase64();
        String extname = vo.getExtName();
        String personId = vo.getPersonId()+"";
        String savePath = face;
        if (faceBase64!=null && !faceBase64.equals("")) {
            FaceApi faceApi = new FaceApi();
            RootResp resp = faceApi.newperson(apiConfiguration, personId, personName, faceBase64);
            if(resp.getRet()==0) {
                JSONObject data = JSON.parseObject(resp.getData().toString());
                faceId = data.getString("FaceId");
                if(faceId!=null) {
                    String filename = faceId + "." + extname;
                    savePath += filename;
                    try {
                        Base64Util.decoderBase64File(faceBase64, savePath);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
            else {
                return faceId;
            }
        }
        return faceId;
    }

FaceApi人脸识别工具类

package com.qcby.community.util;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.qcby.community.configuration.ApiConfiguration;
import com.tencentcloudapi.common.Credential;
import com.tencentcloudapi.common.exception.TencentCloudSDKException;
import com.tencentcloudapi.common.profile.ClientProfile;
import com.tencentcloudapi.common.profile.HttpProfile;
import com.tencentcloudapi.iai.v20180301.IaiClient;
import com.tencentcloudapi.iai.v20180301.models.*;
import org.apache.log4j.Logger;

public class FaceApi {

    private Logger logger = Logger.getLogger(FaceApi.class);

    //人脸分析
    public RootResp detectFace(ApiConfiguration config, String url) {
        RootResp result = new RootResp();
        try{
            Credential cred = new Credential(config.getSecretId(), config.getSecretKey());
            HttpProfile httpProfile = new HttpProfile();
            httpProfile.setEndpoint(config.getServerIp());
            ClientProfile clientProfile = new ClientProfile();
            clientProfile.setHttpProfile(httpProfile);
            IaiClient client = new IaiClient(cred, config.getArea(), clientProfile);
            JSONObject paramObj = new JSONObject();
            paramObj.put("Url", url);
            paramObj.put("MaxFaceNum",1);
            paramObj.put("MinFaceSize",34);
            paramObj.put("NeedFaceAttributes",0);
            paramObj.put("NeedQualityDetection",1);
            DetectFaceRequest req = DetectFaceRequest.fromJsonString(paramObj.toJSONString(),DetectFaceRequest.class);
            DetectFaceResponse resp = client.DetectFace(req);
            result.setData(DetectFaceResponse.toJsonString(resp));
        } catch (TencentCloudSDKException e) {
            result.setRet(-1);
            result.setMsg(e.toString());
            logger.error(e.toString());
        }
        logger.info(result);
        return result;
    }

    //添加个体
    public RootResp newperson(ApiConfiguration config, String personId, String personName, String image) {
        RootResp result = new RootResp();
        try{
            //设置凭证和连接信息
            // 实例化一个认证对象，入参需要传入腾讯云账户 SecretId 和 SecretKey，此处还需注意密钥对的保密
            Credential cred = new Credential(config.getSecretId(), config.getSecretKey());
            HttpProfile httpProfile = new HttpProfile();
            // 实例化一个http选项，可选的，没有特殊需求可以跳过
            //httpProfile.setEndpoint("iai.tencentcloudapi.com");
            httpProfile.setEndpoint(config.getServerIp());
            // 实例化一个client选项，可选的，没有特殊需求可以跳过
            ClientProfile clientProfile = new ClientProfile();
            clientProfile.setHttpProfile(httpProfile);
            // 实例化要请求产品的client对象,clientProfile是可选的
            IaiClient client = new IaiClient(cred, config.getArea(), clientProfile);
            JSONObject paramObj = new JSONObject();
            paramObj.put("GroupId", config.getGroupId());
            paramObj.put("PersonId", config.getPersonIdPre() + personId);
            paramObj.put("PersonName", personName);
            paramObj.put("Image", image);
            //发送请求并获取响应
            CreatePersonRequest req = CreatePersonRequest.fromJsonString(paramObj.toJSONString(), CreatePersonRequest.class);
            CreatePersonResponse resp = client.CreatePerson(req);
            result.setData(CreatePersonResponse.toJsonString(resp));
        } catch (TencentCloudSDKException e) {
            result.setRet(-1);
            result.setMsg(e.toString());
            logger.error(e.toString());
        }
        logger.info(result);
        return result;
    }

    //删除个体
    public RootResp delperson(ApiConfiguration config, String personId) {
        RootResp result = new RootResp();
        try{
            Credential cred = new Credential(config.getSecretId(), config.getSecretKey());
            HttpProfile httpProfile = new HttpProfile();
            httpProfile.setEndpoint(config.getServerIp());
            ClientProfile clientProfile = new ClientProfile();
            clientProfile.setHttpProfile(httpProfile);
            IaiClient client = new IaiClient(cred, config.getArea(), clientProfile);
            JSONObject paramObj = new JSONObject();
            paramObj.put("PersonId", config.getPersonIdPre() + personId);
            DeletePersonRequest req = DeletePersonRequest.fromJsonString(paramObj.toJSONString(), DeletePersonRequest.class);
            DeletePersonResponse resp = client.DeletePerson(req);
            result.setData(DeletePersonResponse.toJsonString(resp));
        } catch (TencentCloudSDKException e) {
            result.setRet(-1);
            result.setMsg(e.toString());
            logger.error(e.toString());
        }
        logger.info(result);
        return result;
    }

    //增加人脸
    public RootResp addface(ApiConfiguration config, String personId, String image) {
        RootResp result = new RootResp();
        try{
            Credential cred = new Credential(config.getSecretId(), config.getSecretKey());
            HttpProfile httpProfile = new HttpProfile();
            httpProfile.setEndpoint(config.getServerIp());
            ClientProfile clientProfile = new ClientProfile();
            clientProfile.setHttpProfile(httpProfile);
            IaiClient client = new IaiClient(cred, config.getArea(), clientProfile);
            JSONObject paramObj = new JSONObject();
            JSONArray images = new JSONArray();
            images.add(image);
            paramObj.put("PersonId", config.getPersonIdPre() + personId);
            paramObj.put("Images", images);
            CreateFaceRequest req = CreateFaceRequest.fromJsonString(paramObj.toJSONString(), CreateFaceRequest.class);
            CreateFaceResponse resp = client.CreateFace(req);
            result.setData(CreateFaceResponse.toJsonString(resp));
        } catch (TencentCloudSDKException e) {
            result.setRet(-1);
            result.setMsg(e.toString());
            logger.error(e.toString());
        }
        logger.info(result);
        return result;
    }

    //删除人脸
    public RootResp delface(ApiConfiguration config, String personId, String faceId) {
        RootResp result = new RootResp();
        try{
            Credential cred = new Credential(config.getSecretId(), config.getSecretKey());
            HttpProfile httpProfile = new HttpProfile();
            httpProfile.setEndpoint(config.getServerIp());
            ClientProfile clientProfile = new ClientProfile();
            clientProfile.setHttpProfile(httpProfile);
            IaiClient client = new IaiClient(cred, config.getArea(), clientProfile);
            JSONObject paramObj = new JSONObject();
            JSONArray faces = new JSONArray();
            faces.add(faceId);
            paramObj.put("PersonId", config.getPersonIdPre() + personId);
            paramObj.put("FaceIds", faces);
            DeleteFaceRequest req = DeleteFaceRequest.fromJsonString(paramObj.toJSONString(), DeleteFaceRequest.class);
            DeleteFaceResponse resp = client.DeleteFace(req);
            result.setData(DeleteFaceResponse.toJsonString(resp));
        } catch (TencentCloudSDKException e) {
            result.setRet(-1);
            result.setMsg(e.toString());
            logger.error(e.toString());
        }
        logger.info(result);
        return result;
    }

    //人脸验证
    public RootResp faceVerify(ApiConfiguration config, String personId, String image) {
        RootResp result = new RootResp();
        try{
            Credential cred = new Credential(config.getSecretId(), config.getSecretKey());
            HttpProfile httpProfile = new HttpProfile();
            httpProfile.setEndpoint(config.getServerIp());
            ClientProfile clientProfile = new ClientProfile();
            clientProfile.setHttpProfile(httpProfile);
            IaiClient client = new IaiClient(cred, config.getArea(), clientProfile);
            JSONObject paramObj = new JSONObject();
            paramObj.put("PersonId", config.getPersonIdPre() + personId);
            paramObj.put("Image", image);
            VerifyFaceRequest req = VerifyFaceRequest.fromJsonString(paramObj.toJSONString(), VerifyFaceRequest.class);
            VerifyFaceResponse resp = client.VerifyFace(req);
            result.setData(VerifyFaceResponse.toJsonString(resp));
        } catch (TencentCloudSDKException e) {
            result.setRet(-1);
            result.setMsg(e.toString());
            logger.error(e.toString());
        }
        logger.info(result);
        return result;
    }

    //人员搜索按库返回
    public RootResp searchPersonsReturnsByGroup(ApiConfiguration config, String image) {
        RootResp result = new RootResp();
        try{
            Credential cred = new Credential(config.getSecretId(), config.getSecretKey());
            HttpProfile httpProfile = new HttpProfile();
            httpProfile.setEndpoint(config.getServerIp());
            ClientProfile clientProfile = new ClientProfile();
            clientProfile.setHttpProfile(httpProfile);
            IaiClient client = new IaiClient(cred, config.getArea(), clientProfile);
            JSONObject paramObj = new JSONObject();
            paramObj.put("GroupIds", new String[] {config.getGroupId()});
            paramObj.put("Image", image);
            //最多返回的最相似人员数目
            paramObj.put("MaxPersonNumPerGroup", 5);
            //返回人员具体信息
            paramObj.put("NeedPersonInfo", 1);
            //最多识别的人脸数目
            paramObj.put("MaxFaceNum", 1);
            SearchFacesReturnsByGroupRequest req = SearchFacesReturnsByGroupRequest.fromJsonString(paramObj.toJSONString(), SearchFacesReturnsByGroupRequest.class);
            SearchFacesReturnsByGroupResponse resp = client.SearchFacesReturnsByGroup(req);
            result.setData(VerifyFaceResponse.toJsonString(resp));
        } catch (TencentCloudSDKException e) {
            result.setRet(-1);
            result.setMsg(e.toString());
            logger.error(e.toString());
        }
        logger.info(result);
        return result;
    }
}

RootResp人脸识别响应类

package com.qcby.community.util;

import com.alibaba.fastjson.JSON;

public class RootResp {
    private int ret = 0;

    public int getRet() {
        return this.ret;
    }

    public void setRet(int ret) {
        this.ret = ret;
    }

    private String msg;

    public String getMsg() {
        return this.msg;
    }

    public void setMsg(String msg) {
        this.msg = msg;
    }

    private Object data;

    public Object getData() {
        return this.data;
    }

    public void setData(Object data) {
        this.data = data;
    }

    @Override
    public String toString() {
        return JSON.toJSONString(this);
    }
}

Base64Util编码转换工具类

package com.qcby.community.util;

import org.apache.commons.codec.binary.Base64;

import java.io.*;
import java.net.HttpURLConnection;
import java.net.URL;

public class Base64Util {
    /**
     * 将二进制数据编码为BASE64字符串
     *
     * @param binaryData
     * @return
     */
    public static String encode(byte[] binaryData) {
        try {
            return new String(Base64.encodeBase64(binaryData), "UTF-8");
        } catch (UnsupportedEncodingException e) {
            return null;
        }
    }

    /**
     * 将BASE64字符串恢复为二进制数据
     *
     * @param base64String
     * @return
     */
    public static byte[] decode(String base64String) {
        try {
            return Base64.decodeBase64(base64String.getBytes("UTF-8"));
        } catch (UnsupportedEncodingException e) {
            return null;
        }
    }

    /**
     * 将文件转成base64 字符串
     *
     *  path文件路径
     * @return *
     * @throws Exception
     */

    public static String encodeBase64File(String path) throws Exception {
        File file = new File(path);
        FileInputStream inputFile = new FileInputStream(file);
        byte[] buffer = new byte[(int) file.length()];
        inputFile.read(buffer);
        inputFile.close();
        return encode(buffer);
    }
    //读取网络图片
    public static String encodeBase64URLFile(String path) throws Exception {
        URL url = new URL(path);
        HttpURLConnection conn = (HttpURLConnection)url.openConnection();
        conn.setRequestMethod("GET");
        conn.setConnectTimeout(5*1000);
        InputStream is = conn.getInputStream();
        byte[] data = readInputStream(is);
        is.close();
        conn.disconnect();
        return encode(data);
    }
    public static byte[] readInputStream(InputStream inStream) throws Exception{
        ByteArrayOutputStream outStream = new ByteArrayOutputStream();
        //创建一个Buffer字符串
        byte[] buffer = new byte[1024];
        //每次读取的字符串长度，如果为-1，代表全部读取完毕
        int len = 0;
        //使用一个输入流从buffer里把数据读取出来
        while( (len=inStream.read(buffer)) != -1 ){
            //用输出流往buffer里写入数据，中间参数代表从哪个位置开始读，len代表读取的长度
            outStream.write(buffer, 0, len);
        }
        //关闭输入流
        inStream.close();
        //把outStream里的数据写入内存
        return outStream.toByteArray();
    }
    /**
     * 将base64字符解码保存文件
     *
     * @param base64Code
     * @param targetPath
     * @throws Exception
     */

    public static void decoderBase64File(String base64Code, String targetPath) throws Exception {
        byte[] buffer = decode(base64Code);
        FileOutputStream out = new FileOutputStream(targetPath);
        out.write(buffer);
        out.close();
    }

    /**
     * 将base64字符保存文本文件
     *
     * @param base64Code
     * @param targetPath
     * @throws Exception
     */

    public static void toFile(String base64Code, String targetPath) throws Exception {

        byte[] buffer = base64Code.getBytes();
        FileOutputStream out = new FileOutputStream(targetPath);
        out.write(buffer);
        out.close();
    }
}

②excel导出

传入参数

{
"page": 1,
"limit": 10,
"userName": "",
"communityId": "",
"mobile": ""
}

返回参数

{
"msg": "操作成功",
"code": 200,
"data": "20230719185659.xls"
}

实现思路

①根据传入参数，查询居民信息

②将居民信息写入excel文件，并导出。

具体而言：

准备Excel模板：
- 创建一个Excel模板文件，该文件包含所需的表头和其他固定内容。
- 在代码中指定模板文件的路径。
复制模板文件：
- 为了避免直接修改原始模板文件，先复制一份模板文件。
- 在复制的文件名中添加时间戳或其他唯一标识，确保每次导出的文件都是新的。
读取数据：
- 获取需要导出的数据，并将其存储在一个列表中。
操作Excel文件：
- 使用Apache POI库来操作Excel文件。
- 读取复制后的Excel文件，获取工作簿和工作表。
写入数据到Excel：
- 遍历数据列表，为每条数据创建一个新的行。
- 在每行中创建单元格，并将数据写入到对应的单元格中。
- 可以根据需要设置单元格的样式、格式等。
保存Excel文件：将修改后的Excel文件保存到指定的路径。
将文件名返回

引入依赖

        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-spring-boot-starter</artifactId>
            <version>4.2.0</version>
        </dependency>

controller

     /**
     * 文件导出
     * @param personListForm
     * @return
     */
    @GetMapping("/exportExcel")
    public Result exportExcel(PersonListForm personListForm){
        PageVO pageVO = this.personService.personList(personListForm);
        List list = pageVO.getList();
        String path = excel;
        path = ExcelUtil.ExpPersonInfo(list,path);
        return Result.ok().put("data", path);
    }

ExcelUtil文件导出工具类

package com.qcby.community.util;

import com.qcby.community.vo.PersonVO;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.BorderStyle;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

public class ExcelUtil {

    public static String ExpPersonInfo(List<PersonVO> info, String path){
        POIFSFileSystem fs = null;
        int headRow = 2;
        String descfile = null;
        try {
            //复制文件
            String srcfile = path + "personInfo.xls";
            Date date = new Date();
            SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
            String dateStr = format.format(date);
            descfile = dateStr + ".xls";
            try {
                FileInputStream fis = new FileInputStream(srcfile);
                FileOutputStream fos = new FileOutputStream(path+descfile);
                byte [] buffer = new byte[1024*4];
                while(fis.read(buffer) != -1){
                    fos.write(buffer);
                }
                fis.close();
                fos.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            //写数据
            fs = new POIFSFileSystem(new FileInputStream(path + descfile));
            FileOutputStream fos = new FileOutputStream(path + descfile);
            HSSFWorkbook wb1 = new HSSFWorkbook(fs);
            HSSFSheet sheet = wb1.getSheetAt(0);
            int size = info.size();
            int col = 0;
            HSSFCellStyle style = wb1.createCellStyle();
            style.setBorderLeft(BorderStyle.THIN);
            style.setBorderRight(BorderStyle.THIN);
            style.setBorderTop(BorderStyle.THIN);
            style.setBorderBottom(BorderStyle.THIN);
            for(int i = 0;i < size;i++){
                col = 0;
                PersonVO p = info.get(i);
                HSSFRow row = sheet.createRow(i+headRow);
                HSSFCell cell = null;
                cell = row.createCell(col++);
                cell.setCellStyle(style);
                cell.setCellValue(p.getPersonId());

                cell = row.createCell(col++);
                cell.setCellStyle(style);
                cell.setCellValue(p.getCommunityName());

                cell = row.createCell(col++);
                cell.setCellStyle(style);
                cell.setCellValue(p.getTermName());

                cell = row.createCell(col++);
                cell.setCellStyle(style);
                cell.setCellValue(p.getHouseNo());
                cell = row.createCell(col++);
                cell.setCellStyle(style);
                cell.setCellValue(p.getUserName());

                cell = row.createCell(col++);
                cell.setCellStyle(style);
                cell.setCellValue(p.getSex());

                cell = row.createCell(col++);
                cell.setCellStyle(style);
                cell.setCellValue(p.getMobile());

                cell = row.createCell(col++);
                cell.setCellStyle(style);
                cell.setCellValue(p.getPersonType());

                cell = row.createCell(col++);
                cell.setCellStyle(style);
                cell.setCellValue(p.getRemark());

            }
            wb1.write(fos);
            fos.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return descfile;
    }

}

③excel上传

实现思路

①指定文件上传位置

②获取上传的文件

③通过UUID生成新的文件名

④文件上传

⑤返回上传的文件名

实现的controller

    /**
     * 数据上传
     * @param file
     * @return
     * @throws Exception
     */
    @PostMapping("/excelUpload")
    public Result excelUpload(@RequestParam("uploadExcel") MultipartFile file) throws Exception {
        if(file.getOriginalFilename().equals("")){
            return Result.error("没有选中要上传的文件");
        }else {
            String picName = UUID.randomUUID().toString();
            String oriName = file.getOriginalFilename();
            String extName = oriName.substring(oriName.lastIndexOf("."));
            String newFileName = picName + extName;
            File targetFile = new File(excel, newFileName);
            // 保存文件
            file.transferTo(targetFile);
            return Result.ok().put("data",newFileName);
        }
    }

④数据导入

实现思路

文件导入的实现思路主要涉及以下几个步骤：

获取用户信息：
- 通过HttpSession对象从会话中获取当前登录的用户信息，用于存储到数据表person中creater的字段
读取文件：
- 根据传入的文件名和预设的文件路径，构建完整的文件路径。
- 使用Apache POI库的POIFSFileSystem和HSSFWorkbook类来读取Excel文件。POIFSFileSystem用于处理Office文档的底层文件系统，而HSSFWorkbook则代表一个Excel工作簿。
处理异常：
- 在读取文件的过程中，可能会遇到文件不存在、文件损坏或读取错误等异常。使用try-catch块来捕获这些异常，并打印堆栈跟踪以便于调试和错误处理。
获取工作表内容：
- 从工作簿中获取第一个工作表（或根据需求获取特定的工作表）。
- 获取工作表的总行数和总列数，以确定数据范围。
数据提取：
- 根据需要，跳过表头行。
- 遍历工作表的每一行和每一列，使用HSSFRow和HSSFCell类获取单元格数据。
- 使用DataFormatter类将单元格数据格式化为字符串，以便于后续处理。
存储数据：
- 创建一个二维对象数组，用于存储从Excel文件中提取的数据。
- 将格式化后的单元格数据按行和列的顺序存入二维数组中。
写入数据：
- 将二维对象数组中的数据逐条保存到数据库中。
返回响应：
- 返回响应给前端，前端可以根据响应中的信息进行相应的处理（如显示导入成功提示、刷新页面等）。

实现的controller

/**
     * 数据导入
     * @param fileName
     * @param session
     * @return
     */
    @LogAnnotation("导入数据")
    @PostMapping("/parsefile/{fileName}")
    public Result parsefile(@PathVariable("fileName") String fileName,HttpSession session){
        User user = (User) session.getAttribute("user");
        // POIFSFileSystem是Apache POI库中用于处理Office文档的底层文件系统API。
        // 它代表了一个OLE 2复合文档，这是一个包含多个流（stream）和存储（storage）的单一文件
        POIFSFileSystem fs = null;
        //  HSSFWorkbook类代表一个Excel工作簿，即一个.xls文件。
        // 通过这个类，你可以创建新的Excel文件，或者读取现有的Excel文件。
        // HSSFWorkbook提供了操作工作簿内容的方法，如添加、删除或修改工作表（HSSFSheet），以及设置工作簿的属性等
        HSSFWorkbook wb = null;
        try {
            String basePath = excel + fileName;
            //读取指定路径的Excel文件，放入fs输入流中
            fs = new POIFSFileSystem(new FileInputStream(basePath));
            //wb代表了这个Excel工作簿，你可以使用它来读取或修改Excel文件的内容
            wb = new HSSFWorkbook(fs);
        } catch (Exception e) {
            e.printStackTrace();
        }
        //获取第一个工作表
        HSSFSheet sheet = wb.getSheetAt(0);
        Object[][] data = null;
        //获取总行数
        int r = sheet.getLastRowNum()+1;
        //获取总列数
        int c = sheet.getRow(0).getLastCellNum();
        int headRow = 2;
        //将表格中的数据存入二维对象data中
        data = new Object[r - headRow][c];
        for (int i = headRow; i < r; i++) {
            //获取行
            HSSFRow row = sheet.getRow(i);
            for (int j = 0; j < c; j++) {
                //获取列
                HSSFCell cell = null;
                try {
                    cell = row.getCell(j);
                    try {
                        cell = row.getCell(j);
                        DataFormatter dataFormater = new DataFormatter();
                        String a = dataFormater.formatCellValue(cell);
                        data[i - headRow][j] = a;
                    } catch (Exception e) {
                        data[i-headRow][j] = "";
                        if(j==0){
                            try {
                                double d = cell.getNumericCellValue();
                                data[i - headRow][j] = (int)d + "";
                            }catch(Exception ex){
                                data[i-headRow][j] = "";
                            }
                        }
                    }
                } catch (Exception e) {
                    System.out.println("i="+i+";j="+j+":"+e.getMessage());
                }
            }
        }

        int row = data.length;
        int col = 0;
        String errinfo = "";
        headRow = 3;
        String[] stitle={"ID","小区名称","所属楼栋","房号","姓名","性别","手机号码","居住性质","状态","备注"};
        errinfo = "";
        for (int i = 0; i < row; i++) {
            Person single = new Person();
            single.setPersonId(0);
            single.setState(1);
            single.setFaceUrl("");
            try {
                col=1;
                String communityName = data[i][col++].toString();
                QueryWrapper<Community> queryWrapper = new QueryWrapper<>();
                queryWrapper.eq("community_name", communityName);
                Community community = this.communityService.getOne(queryWrapper);
                if( community == null){
                    errinfo += "Excel文件第" + (i + headRow) + "行小区名称不存在！";
                    return Result.ok().put("status", "fail").put("data", errinfo);
                }
                single.setCommunityId(community.getCommunityId());
                single.setTermName(data[i][col++].toString());
                single.setHouseNo(data[i][col++].toString());
                single.setUserName(data[i][col++].toString());
                single.setSex(data[i][col++].toString());
                single.setMobile(data[i][col++].toString());
                single.setPersonType(data[i][col++].toString());
                single.setRemark(data[i][col++].toString());
                single.setCreater(user.getUsername());
                this.personService.save(single);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return Result.ok().put("status", "success").put("data","数据导入完成！");
    }

3、小区地图

获取地图数据

前端

vue安装依赖

cnpm i --save vue-baidu-map

配置申请密钥

Vue.use(BaiduMap, {
  // ak 是在百度地图开发者平台申请的密钥
  ak: '7eTaUxl9NY8RCMxCPm3oc8m2snTBOgbt'
})

后端

传入参数

无

返回参数

{
"msg": "操作成功",
"code": 200,
"data": [
{
"communityId": 2,
"communityName": "栖海澐颂",
"termCount": 0,
"seq": 0,
"creater": "",
"createTime": "",
"lng": 116.2524,
"lat": 40.0961,
}
]
}

实现controller

    /**
     * 获取小区地图
     * @return
     */
    @GetMapping("/getCommunityMap")
    public Result getCommunityMap(){
        List<Community> data = communityService.list();
        if(data == null) return Result.error("没有小区数据");
        return Result.ok().put("data", data);
    }