优享资讯 | 基于spark的北京二手房离线分析（三）—

掘金后端 ( ) • 2024-06-30 16:12

基于spark的北京二手房离线分析（三）——构建后端数据服务

构建后端数据服务

主要任务：使用Spring Boot作为后端框架与MySQL数据库交互，并将数据以JSON格式传输给前端。

环境声明：jdk8；aliyun脚手架

由于使用了Spring Boot框架，和MyBatis-Plus代码生成器，省去了很多需要自行敲代码的步骤。

一、新建项目Spring

Server URL：start.aliyun.com 选择aliyun的脚手架进行创建

Group：com.example.secondhand

SDK: 1.8

Java: 8

添加配置LomBok，Spring Web， MySQL Driver，点击Finish即可完成。

本次项目结构如下：

二、项目配置pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.example.secondhand</groupId>
    <artifactId>secondhand-aliyun</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>secondhand-aliyun</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>1.8</java.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <spring-boot.version>2.6.13</spring-boot.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.2</version>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
            <version>3.3.2</version>
        </dependency>

        <dependency>
            <groupId>org.apache.velocity</groupId>
            <artifactId>velocity</artifactId>
            <version>1.7</version>
        </dependency>

    </dependencies>
    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-dependencies</artifactId>
                <version>${spring-boot.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>


        </dependencies>
    </dependencyManagement>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.8.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                    <encoding>UTF-8</encoding>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <version>${spring-boot.version}</version>
                <configuration>
                    <mainClass>com.example.secondhand.SecondhandAliyunApplication</mainClass>
                    <skip>true</skip>
                </configuration>
                <executions>
                    <execution>
                        <id>repackage</id>
                        <goals>
                            <goal>repackage</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>

</project>

三、数据库与MyBatis-Plus配置

application.yml

spring:
  datasource:
    url: <数据库连接>   #jdbc:mysql://192.168.2.99:3306/secondhand
    username: <数据库用户名>
    password: <数据库密码>
    driver-class-name: <数据库驱动>  #com.mysql.jdbc.Driver
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  mapper-locations: classpath:com/example/secondhand/mapper/xml/*.xml  #MyBatis映射文件的位置
server:
  port: 8181  #服务器配置,设定应用运行的服务器端口号为8181

四、MyBatis-Plus代码生成器脚本

自动化生成与数据库表对应的Java实体类、Mapper接口、Service层接口及实现类、Controller层控制器等。

package com.example.secondhand;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.generator.AutoGenerator;
import com.baomidou.mybatisplus.generator.config.DataSourceConfig;
import com.baomidou.mybatisplus.generator.config.GlobalConfig;
import com.baomidou.mybatisplus.generator.config.PackageConfig;
import com.baomidou.mybatisplus.generator.config.StrategyConfig;
import com.baomidou.mybatisplus.generator.config.rules.NamingStrategy;

public class GenerateTest {
    public static void main(String[] args) {
        //创建generator对象
        AutoGenerator autoGenerator = new AutoGenerator();
        //数据源
        DataSourceConfig dataSourceConfig = new DataSourceConfig();
        dataSourceConfig.setDbType(DbType.MYSQL);
        //这部分设置为你自己的数据库配置
        dataSourceConfig.setDriverName("com.mysql.cj.jdbc.Driver");
        dataSourceConfig.setUsername("root");
        dataSourceConfig.setPassword("123456");
        dataSourceConfig.setUrl("jdbc:mysql://localhost:3306/secondhand");
        autoGenerator.setDataSource(dataSourceConfig);
        //全局配置
        GlobalConfig globalConfig = new GlobalConfig();
        globalConfig.setOutputDir(System.getProperty("user.dir")+"/src/main/java");
        globalConfig.setAuthor("admin");
        globalConfig.setOpen(false);
        globalConfig.setServiceName("%sService");
        autoGenerator.setGlobalConfig(globalConfig);
        //包信息
        PackageConfig packageConfig = new PackageConfig();
        packageConfig.setParent("com.example.secondhand");
        packageConfig.setEntity("entity");
        packageConfig.setMapper("mapper");
        packageConfig.setService("service");
        packageConfig.setServiceImpl("service.impl");
        packageConfig.setController("controller");
        autoGenerator.setPackageInfo(packageConfig);
        //策略配置
        StrategyConfig strategyConfig = new StrategyConfig();
        //这边改为你自己的数据库表，可以连写，直接将表名拖入""内
        strategyConfig.setInclude("time_series_stat");
        strategyConfig.setNaming(NamingStrategy.underline_to_camel);
        strategyConfig.setColumnNaming(NamingStrategy.underline_to_camel);
        strategyConfig.setEntityLombokModel(true);
        autoGenerator.setStrategy(strategyConfig);
        //运行
        autoGenerator.execute();
    }
}

编写完成点击运行，即可得到Java实体类、Mapper接口、Service层接口及实现类、Controller层控制器。

五、数据json格式或自定义字段名处理

注：一般情况下，无需进行数据处理，直接进行controller层创建Restful接口，将数据传输至页面上即为json格式。如果需要自行处理数据，可参考以下内容。

柱状图json数据处理

VO层

新建一个vo文件夹在com.example.secondhand目录下，新建BjAvgUnitPricesVO类。自定义数据格式为List和List。

package com.example.secondhand.vo;

import lombok.Data;

import java.math.BigDecimal;
import java.util.List;


@Data
public class BjAvgUnitPricesVO {

    private List<String> districts;
    private List<BigDecimal> avgUnitPricePerSqms;
}

接口BjAvgUnitPricesService

package com.example.secondhand.service;

import com.example.secondhand.entity.BjAvgUnitPrices;
import com.baomidou.mybatisplus.extension.service.IService;
import com.example.secondhand.vo.BjAvgUnitPricesVO;

public interface BjAvgUnitPricesService extends IService<BjAvgUnitPrices> {


    public BjAvgUnitPricesVO bjAvgUnitPricesVO();
}

服务层BjAvgUnitPricesServiceImpl

将数据库读出的数据转换为自定的vo。

package com.example.secondhand.service.impl;

import com.example.secondhand.entity.BjAvgUnitPrices;
import com.example.secondhand.mapper.BjAvgUnitPricesMapper;
import com.example.secondhand.service.BjAvgUnitPricesService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.example.secondhand.vo.BjAvgUnitPricesVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;


@Service
public class BjAvgUnitPricesServiceImpl extends ServiceImpl<BjAvgUnitPricesMapper, BjAvgUnitPrices> implements BjAvgUnitPricesService {

    @Autowired
    private BjAvgUnitPricesMapper bjAvgUnitPricesMapper;


    @Override
    public BjAvgUnitPricesVO bjAvgUnitPricesVO() {
        BjAvgUnitPricesVO bjAvgUnitPricesVO = new BjAvgUnitPricesVO();
        List<String> districts = new ArrayList<>();
        List<BigDecimal> avgUnitPricePerSqms = new ArrayList<>();
        //先查出数据
        List<BjAvgUnitPrices> bjAvgUnitPrices = this.bjAvgUnitPricesMapper.selectList(null);
        for (BjAvgUnitPrices bjAvgUnitPrice : bjAvgUnitPrices){
            districts.add(bjAvgUnitPrice.getDistrict());
            avgUnitPricePerSqms.add(bjAvgUnitPrice.getAvgUnitPricePerSqm());
        }
        bjAvgUnitPricesVO.setDistricts(districts);
        bjAvgUnitPricesVO.setAvgUnitPricePerSqms(avgUnitPricePerSqms);
        //转换VO
        return bjAvgUnitPricesVO;
    }
}

六、数据分析遗漏处理

说明：数据分析应该全部在hdfs上面处理，本操作不规范。如果写到后端这一步发现有分析步骤未达到预期，想进一步分析，或者，在分析时遗漏某一步。可以选择在此操作，再次声明此操作不规范。

以折线图（时间序列图）为例

DTO层

新建一个dto文件夹（dto层，Data Transfer Object Layer，即数据传输对象层）在com.example.secondhand目录下。

定义了一个名为MonthlyStatisticsDTO的Java类。

private Long monthAttention：表示某个月的总关注度计数。
private Long monthViews：表示某个月的总浏览量计数。
private String formattedPublishTime：存储经过格式化处理的发布时间信息，通常用于展示，提高了数据的可读性。

package com.example.secondhand.dto;

import lombok.Data;

@Data
public class MonthlyStatisticsDTO {
    private Long monthAttention;
    private Long monthViews;
    private String formattedPublishTime;
}

MyBatis的接口`TimeSeriesAnalysisFormattedMapper`。

进一步分析：执行SQL查询，以收集并格式化时间序列分析中的数据，特别是按月份汇总的关注度和浏览量统计信息。

同理，饼状图（前五小区房源关注比例）也可如此操作，只选取前五（数据量过大，避免后续加载过久，选取前五个）。

package com.example.secondhand.mapper;

import com.example.secondhand.dto.MonthlyStatisticsDTO;
import com.example.secondhand.entity.TimeSeriesAnalysisFormatted;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;


@Mapper
public interface TimeSeriesAnalysisFormattedMapper extends BaseMapper<TimeSeriesAnalysisFormatted> {

    @Select("SELECT SUM(attention_count) AS month_attention, " +
            "SUM(view_count) AS month_views, " +
            "formatted_publish_time " +
            "FROM time_series_analysis_formatted " +
            "GROUP BY formatted_publish_time")
    List<MonthlyStatisticsDTO> getMonthlyStatistics();
}

业务接口`TimeSeriesAnalysisFormattedService`

定义了一组操作TimeSeriesAnalysisFormatted实体的服务方法规范，特别关注于时间序列数据分析的月度统计功能。

package com.example.secondhand.service;

import com.example.secondhand.dto.MonthlyStatisticsDTO;
import com.example.secondhand.entity.TimeSeriesAnalysisFormatted;
import com.baomidou.mybatisplus.extension.service.IService;

import java.util.List;

public interface TimeSeriesAnalysisFormattedService extends IService<TimeSeriesAnalysisFormatted> {

    List<MonthlyStatisticsDTO> getMonthlyStatistics();

}

服务实现类`TimeSeriesAnalysisFormattedServiceImpl`

实现对时间序列分析数据的操作，特别是封装了从数据库获取每月统计信息的逻辑。

package com.example.secondhand.service.impl;

import com.example.secondhand.dto.MonthlyStatisticsDTO;
import com.example.secondhand.entity.TimeSeriesAnalysisFormatted;
import com.example.secondhand.mapper.TimeSeriesAnalysisFormattedMapper;
import com.example.secondhand.service.TimeSeriesAnalysisFormattedService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;


@Service
public class TimeSeriesAnalysisFormattedServiceImpl extends ServiceImpl<TimeSeriesAnalysisFormattedMapper, TimeSeriesAnalysisFormatted> implements TimeSeriesAnalysisFormattedService {


    @Autowired
    private TimeSeriesAnalysisFormattedMapper timeSeriesAnalysisFormattedMapper;

    @Override
    public List<MonthlyStatisticsDTO> getMonthlyStatistics() {
        return timeSeriesAnalysisFormattedMapper.getMonthlyStatistics();
    }
}

七、Controller层

由于使用到的接口不多，直接写在aliyun脚手架的demos.web下的BasicController中。负责处理HTTP请求。控制器中包含的方法通过注解@GetMapping和@RequestMapping映射到特定的URL端点，用于提供JSON格式的数据响应。

/*
 * Copyright 2013-2018 the original author or authors.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      https://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package com.example.secondhand.demos.web;

import com.example.secondhand.dto.MonthlyStatisticsDTO;
import com.example.secondhand.entity.*;
import com.example.secondhand.mapper.CommunityAttentionDistributionMapper;
import com.example.secondhand.mapper.TimeSeriesAnalysisFormattedMapper;
import com.example.secondhand.service.*;
import com.example.secondhand.vo.BjAvgUnitPricesVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

/**
 * @author <a href="mailto:[email protected]">theonefx</a>
 */
@RestController
public class BasicController {

    @Autowired
    private BjAvgUnitPricesService bjAvgUnitPricesService;

    //http://localhost:8181/bjAvgUnitPricesVO
    @GetMapping("/bjAvgUnitPricesVO")
    public BjAvgUnitPricesVO bjAvgUnitPricesVO() {
        return this.bjAvgUnitPricesService.bjAvgUnitPricesVO();
    }

    @Autowired
    private BjGeographicalPriceDistributionService bjGeographicalPriceDistributionService;

    //http://localhost:8181/bjGeographicalPriceDistribution/list
    @GetMapping("/bjGeographicalPriceDistribution/list")
    public List<BjGeographicalPriceDistribution> BjGeographicalPriceDistributionList() {
        return this.bjGeographicalPriceDistributionService.list();
    }

    @Autowired
    private CommunityAttentionDistributionService communityAttentionDistributionService;

    //http://localhost:8181/communityAttentionDistribution/five
    @GetMapping("/communityAttentionDistribution/five")
    public List<CommunityAttentionDistribution> CommunityAttentionDistributionFive() {
        return this.communityAttentionDistributionMapper.getFiveCommunity();
    }

    @Autowired
    private CommunityAttentionDistributionMapper communityAttentionDistributionMapper;


    @Autowired
    private HouseTypeDistributionService houseTypeDistributionService;

    //http://localhost:8181/houseTypeDistribution/list
    @GetMapping("/houseTypeDistribution/list")
    public List<HouseTypeDistribution> HouseTypeDistributionList() {
        return this.houseTypeDistributionService.list();
    }

    @Autowired
    private TimeSeriesAnalysisFormattedService timeSeriesAnalysisFormattedService;

    //http://localhost:8181/timeSeriesAnalysisFormatted/monthly
    @GetMapping("/timeSeriesAnalysisFormatted/monthly")
    public List<MonthlyStatisticsDTO> TimeSeriesAnalysisFormattedMonthly() {
        return this.timeSeriesAnalysisFormattedService.getMonthlyStatistics();
    }



    @Autowired
    private WordFrequencyCountsService wordFrequencyCountsService;

    //http://localhost:8181/wordFrequencyCounts/list
    @GetMapping("/wordFrequencyCounts/list")
    public List<WordFrequencyCounts> WordFrequencyCountsList() {
        return this.wordFrequencyCountsService.list();
    }


    // http://127.0.0.1:8080/hello?name=lisi
    @RequestMapping("/hello")
    @ResponseBody
    public String hello(@RequestParam(name = "name", defaultValue = "unknown user") String name) {
        return "Hello " + name;
    }

    // http://127.0.0.1:8080/user
    @RequestMapping("/user")
    @ResponseBody
    public User user() {
        User user = new User();
        user.setName("theonefx");
        user.setAge(666);
        return user;
    }

    // http://127.0.0.1:8080/save_user?name=newName&age=11
    @RequestMapping("/save_user")
    @ResponseBody
    public String saveUser(User u) {
        return "user will save: name=" + u.getName() + ", age=" + u.getAge();
    }

    // http://127.0.0.1:8080/html
    @RequestMapping("/html")
    public String html() {
        return "index.html";
    }

    @ModelAttribute
    public void parseUser(@RequestParam(name = "name", defaultValue = "unknown user") String name
            , @RequestParam(name = "age", defaultValue = "12") Integer age, User user) {
        user.setName("zhangsan");
        user.setAge(18);
    }
}

启动项目后查看URL数据映射

柱状图

http://localhost:8181/bjAvgUnitPricesVO

折线图

http://localhost:8181/timeSeriesAnalysisFormatted/monthly

饼状图

http://localhost:8181/communityAttentionDistribution/five

热力图

http://localhost:8181/bjGeographicalPriceDistribution/list

词云

http://localhost:8181/wordFrequencyCounts/list

八、跨域

跨域问题会在与前端连接时发生。

配置跨源资源共享（CORS，Cross-Origin Resource Sharing）策略。CORS是一种机制，使用额外的HTTP头来告诉浏览器让运行在一个origin（域）上的Web应用被准许访问来自不同源服务器上的指定的资源。

添加以下代码即可。

package com.example.secondhand.configuration;

import org.springframework.context.annotation.Configuration;
import org.springframework.web.servlet.config.annotation.CorsRegistry;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;

@Configuration
public class CrosConfiguration implements WebMvcConfigurer {

    @Override
    public void addCorsMappings(CorsRegistry registry) {
        registry.addMapping("/**")
                .allowedOriginPatterns("*")
                .allowedMethods("GET", "HEAD", "POST", "PUT", "DELETE", "OPTIONS")
                .allowCredentials(true)
                .maxAge(3600)
                .allowedHeaders("*");
    }

}