22.项目开发之量化交易抓取数据QuantTradeData(一)

项目创建及后端业务：定时更新“股票列表基础信息”数据

项目创建

该量化交易数据平台用于数据库的数据抓取、分析等操作。
和QuantTrade使用同一个数据库，无需重新创建

pom.xml


<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.3.12.RELEASE</spring-boot.version>
</properties>
<dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>2.2.2</version></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.33</version></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><exclusions><exclusion><groupId>org.junit.vintage</groupId><artifactId>junit-vintage-engine</artifactId></exclusion></exclusions></dependency><dependency><groupId>io.springfox</groupId><artifactId>springfox-boot-starter</artifactId><version>3.0.0</version></dependency><dependency><groupId>com.alibaba</groupId><artifactId>fastjson</artifactId><version>1.2.17</version></dependency><dependency><groupId>com.opencsv</groupId><artifactId>opencsv</artifactId><version>5.7.1</version></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-quartz</artifactId></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.quanttrade_bk.QuantTradeBkApplication</mainClass><skip>true</skip></configuration><executions><execution><id>repackage</id><goals><goal>repackage</goal></goals></execution></executions></plugin></plugins>
</build>

application.properties


# 应用服务 WEB 访问端口
server.port=8082
# 配置数据库连接信息
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3380/db_quant?serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=1234
#配置mapper.xml的路径
mybatis.mapper-locations=classpath:mapper/*/*.xml

Application

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import org.springframework.web.client.RestTemplate;
import springfox.documentation.swagger2.annotations.EnableSwagger2;@SpringBootApplication
@EnableSwagger2
public class QuantTradeDataApplication {public static void main(String[] args) {SpringApplication.run(QuantTradeDataApplication.class, args);}/*** 向spring注册RestTemplate工具* @return*/@Beanpublic RestTemplate getRestTemplate(){return new RestTemplate();}
}

RestObject

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;@Data
@NoArgsConstructor
@AllArgsConstructor
public class RestObject {private Integer code;           //前端的状态码 0：业务失败  1：业务成功private String msg;              //前端需要展示的信息private Object data;            //前端需要的复杂数据//业务成功的方法----------/*** 业务成功，自定义返回msg和返回的数据* @param msg* @param data* @return*/public static RestObject OK(String msg,Object data){return new RestObject(1,msg,data);}/*** 业务成功，自定义返回msg，无返回数据* @param msg* @return*/public static RestObject OK(String msg){return OK(msg,null);}/*** 业务成功，自定义返回的数据，无返回msg* @param data* @return*/public static RestObject OK(Object data){return OK(null,data);}//业务失败的方法----------/*** 业务失败，自定义返回msg和返回的数据* @param msg* @param data* @return*/public static RestObject ERROR(String msg,Object data){return new RestObject(0,msg,data);}/*** 业务失败，自定义返回msg，无返回数据* @param msg* @return*/public static RestObject ERROR(String msg){return ERROR(msg,null);}/*** 业务失败，自定义返回的数据，无返回msg* @param data* @return*/public static RestObject ERROR(Object data){return ERROR(null,data);}
}

搭建Const和TuShareAPI工具

Const

public interface Const {//平台地址String TUSHARE_BASE_URL     =   "http://api.tushare.pro";//令牌String TUSHARE_TOKEN        =   "自己的token";//股票基本信息String STOCK_BASIC = "stock_basic";//股票日线基本行情String STOCK_DAILY = "daily";
}


import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpEntity;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.stereotype.Component;
import org.springframework.web.client.RestTemplate;import java.util.HashMap;
import java.util.List;
import java.util.Map;@Component
public class TuShareAPI {@AutowiredRestTemplate template;/*** 用于向tushare平台发送请求，并接收到响应* @param api* @param params* @param fields* @return*/public JSONObject get(String api, Map<String, String> params, List<String> fields) {Map<String, Object> allParam = new HashMap<>();allParam.put("api_name", api);allParam.put("token", Const.TUSHARE_TOKEN);// 请求参数allParam.put("params", params);// 设置 需要响应回来的字段if (fields != null && fields.size()>0) {StringBuffer buffer = new StringBuffer();int size = fields.size();for (int i = 0; i <size; i++) {buffer.append(fields.get(i));if(i!=size-1){buffer.append(",");}}allParam.put("fields", buffer.toString());}//设置并发送请求，获取并返回响应HttpHeaders headers = new HttpHeaders();headers.setContentType(MediaType.APPLICATION_JSON);HttpEntity<Map> entity = new HttpEntity<>(allParam, headers);String str = template.postForObject(Const.TUSHARE_BASE_URL,entity, String.class);JSONObject json = JSON.parseObject(str);return json;}
}

启动时，运行： http://localhost:8082/swagger-ui/index.html

后端业务：定时更新“股票列表基础信息”数据

需求说明

为了获取前一天的最新数据，我们需要每个工作日的晚上10点定时刷新stock_basic股票列表基础信息，并将最新数据插入或更新到数据库中，将已经退市的数据删除。

前置知识：Quartz定时框架

Apache提供定时框架Quartz来帮助我们进行定时任务。

依赖

<dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-quartz</artifactId>
</dependency>

其中作业：Job （其中去编写要执行的任务）
作业详情：JobDetail （对任务属性进行调整）
触发器：Trigger（负责执行具体的某个作业）都是执行任务必不可少的组件。我们将需要执行的任务写入Job，并通过JobBuilder来构建JobDetail。
通过TriggerBuilder来构建触发器，并加入任务与执行周期&频率，让任务执行。
其中，Cron表达式无需独立学习，通过在线构建即可：https://cron.qqe2.com/

Job：

import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;
import org.springframework.scheduling.quartz.QuartzJobBean;import java.util.Date;public class TestJob1 extends QuartzJobBean {@Overrideprotected void executeInternal(JobExecutionContext jobExecutionContext) throws JobExecutionException {//定时去做什么事情System.out.println("TestJob1:"+new Date().toLocaleString());}
}

任务配置：


import org.quartz.*;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;@Configuration
public class QuartzConfig {/*** 构建任务详情* @return*/@Beanpublic JobDetail getTestJob1(){//1、通过构建，获取任务详情并返回//storeDurably()  脱离Trigger进行独立保存return JobBuilder.newJob(TestJob1.class).storeDurably().build();}@Beanpublic Trigger getTestJob1Trigger(JobDetail getTestJob1){//1、创建Cron表达式//1秒执行一次
//        CronScheduleBuilder cron = CronScheduleBuilder.cronSchedule("* * * * * ?");//从00秒开始  每10秒执行一次CronScheduleBuilder cron = CronScheduleBuilder.cronSchedule("0/10 * * * * ? ");//2、通过构建，完成触发器的创建并返回return TriggerBuilder.newTrigger().forJob(getTestJob1).withSchedule(cron).build();}
}

案例：定时点名每秒定时抽一个学生姓名

1、创建一个Job，读取txt里的数据，随机获取其中一个

import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;
import org.springframework.scheduling.quartz.QuartzJobBean;
import org.springframework.util.ResourceUtils;import java.io.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;public class RandomNameJob extends QuartzJobBean {private static List<String> names = new ArrayList<>();static {//读取txt获取数据//获取file文件File file;try {file = ResourceUtils.getFile("classpath:test.txt");} catch (FileNotFoundException e) {throw new RuntimeException(e);}//获取缓冲字符流try (BufferedReader reader = new BufferedReader(new FileReader(file));){//读取数据，保存到namesString line = null;while ((line=reader.readLine())!=null){names.add(line);}} catch (FileNotFoundException e) {throw new RuntimeException(e);} catch (IOException e) {throw new RuntimeException(e);}}private static Random r = new Random();@Overrideprotected void executeInternal(JobExecutionContext jobExecutionContext) throws JobExecutionException {System.out.println("随机点名："+names.get(r.nextInt(names.size())));}
}

2、创建JobDetail，创建Trigger 给于cron

import org.quartz.*;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;@Configuration
public class QuartzConfig {/*** 构建任务详情* @return*/@Beanpublic JobDetail getTestJob1(){//1、通过构建，获取任务详情并返回//storeDurably()  脱离Trigger进行独立保存return JobBuilder.newJob(TestJob1.class).storeDurably().build();}@Beanpublic Trigger getTestJob1Trigger(JobDetail getTestJob1){//1、创建Cron表达式//1秒执行一次
//        CronScheduleBuilder cron = CronScheduleBuilder.cronSchedule("* * * * * ?");//从00秒开始  每10秒执行一次CronScheduleBuilder cron = CronScheduleBuilder.cronSchedule("0/10 * * * * ? ");//2、通过构建，完成触发器的创建并返回return TriggerBuilder.newTrigger().forJob(getTestJob1).withSchedule(cron).build();}/*** 构建任务详情* @return*/@Beanpublic JobDetail getRandomNameJob(){//1、通过构建，获取任务详情并返回//storeDurably()  脱离Trigger进行独立保存return JobBuilder.newJob(RandomNameJob.class).storeDurably().build();}@Beanpublic Trigger getRandomNameJobTrigger(JobDetail getRandomNameJob){//1、创建Cron表达式//1秒执行一次CronScheduleBuilder cron = CronScheduleBuilder.cronSchedule("* * * * * ?");//2、通过构建，完成触发器的创建并返回return TriggerBuilder.newTrigger().forJob(getRandomNameJob).withSchedule(cron).build();}
}

股票列表基础信息表（tb_stock_basic_info）

股票列表基础信息实体（StockBasicInfo）

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;@Data
@NoArgsConstructor
@AllArgsConstructor
public class StockBasicInfo {private String tscode;          //TS代码private String stockcode;    //股票代码private String stockname;   //股票名称private String stockarea;     //地域private String industry;       //所属行业private String cnspell;         //拼音缩写private String market;         //市场类型private String list_date;       //上市日期private String is_hs;            //是否沪深港通标的，N否 H沪股通 S深股通private String op_date;       //操作日期  yyyy-MM-dd
}

三层搭建

StockBasicInfoMapper


import org.springframework.stereotype.Repository;@Repository
public interface StockBasicInfoMapper {
}

Application

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import org.springframework.web.client.RestTemplate;
import springfox.documentation.swagger2.annotations.EnableSwagger2;@SpringBootApplication
@EnableSwagger2
@MapperScan("com.quanttradedata.stock.mapper")
public class QuantTradeDataApplication {public static void main(String[] args) {SpringApplication.run(QuantTradeDataApplication.class, args);}/*** 向spring注册RestTemplate工具* @return*/@Beanpublic RestTemplate getRestTemplate(){return new RestTemplate();}
}StockBasicInfoMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.quanttradedata.stock.mapper.StockBasicInfoMapper"></mapper>

StockService


import com.quanttradedata.stock.mapper.StockBasicInfoMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;@Service
public class StockService {@Autowiredprivate StockBasicInfoMapper stockBasicInfoMapper;
}

StockController


import com.quanttradedata.stock.service.StockService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;@RestController
@RequestMapping("/stock")
@CrossOrigin //跨域在开发环境可以有，在生产环境中尽可能去除
public class StockController {@Autowiredprivate StockService stockService;
}

业务实现

StockBasicInfoJob


import com.quanttradedata.stock.service.StockService;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.quartz.QuartzJobBean;
import org.springframework.stereotype.Component;import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;@Component
public class StockBasicInfoJob extends QuartzJobBean {@Autowiredprivate StockService stockService;private SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd");@Overrideprotected void executeInternal(JobExecutionContext jobExecutionContext) throws JobExecutionException {//1、准备需要传递给tuShare 参数HashMap<String, String> param = new HashMap<>();param.put("exchange","SSE");param.put("list_status","L");//2、请求 SSE上交所的股票基本信息System.out.println("请求SSE上交所的股票基本信息......");stockService.saveStockBasicInfoFromNet(param);//3、请求 SZSE深交所的股票基本信息System.out.println("请求 SZSE深交所的股票基本信息......");param.put("exchange","SZSE");stockService.saveStockBasicInfoFromNet(param);//4、数据更新完毕，删除退市失效数据stockService.delStockBasicInfosByOpDate(sdf1.format(new Date()));}
}

QuartzConfig


import com.quanttradedata.stock.job.StockBasicInfoJob;
import org.quartz.*;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;@Configuration
public class QuartzConfig {//股票基本信息的 任务详情创建 及 触发器创建@Beanpublic JobDetail getStockBasicInfoJob(){return JobBuilder.newJob(StockBasicInfoJob.class).storeDurably().build();}@Beanpublic Trigger getStockBasicInfoJobTrigger(JobDetail getStockBasicInfoJob){//1、编写cron表达式，指定触发的时间和周期//开发执行： 每个工作日的晚上22点//CronScheduleBuilder cron = CronScheduleBuilder.cronSchedule("0 0 22 ? * MON-FRI");//测试执行： 每5秒执行一次CronScheduleBuilder cron = CronScheduleBuilder.cronSchedule("0/5 * * * * ? ");//2、构建触发器，执行任务return TriggerBuilder.newTrigger().forJob(getStockBasicInfoJob).withSchedule(cron).build();}
}

StockService


import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.quanttradedata.stock.javabean.StockBasicInfo;
import com.quanttradedata.stock.mapper.StockBasicInfoMapper;
import com.quanttradedata.utils.javabean.Const;
import com.quanttradedata.utils.javabean.TuShareAPI;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;import java.sql.SQLIntegrityConstraintViolationException;
import java.text.SimpleDateFormat;
import java.util.*;@Service
public class StockService {@Autowiredprivate StockBasicInfoMapper stockBasicInfoMapper;@Autowiredprivate TuShareAPI tuShareAPI;private SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd");/*** 根据参数，请求并更新股票列表信息* @param param*/public void saveStockBasicInfoFromNet(HashMap<String, String> param) {//1、指定TuShare平台需要给项目返回哪些字段数据List<String> fields = new ArrayList<>();Collections.addAll(fields,"ts_code","symbol","name","area","industry","cnspell","market","list_date","is_hs");//2、向TuShare平台发请求，请求json数据JSONObject jsonObject = tuShareAPI.get(Const.STOCK_BASIC, param, fields);//3、解析json数据，转为List集合List<StockBasicInfo> stockBasicInfos = new ArrayList<>();//3.1、获取所有的行数据JSONArray jsonArray = jsonObject.getJSONObject("data").getJSONArray("items");//3.2、遍历每行数据，每行转为一个StockBasicInfo对象Date today = new Date();String todayStr = sdf1.format(today);for (int i = 0; i < jsonArray.size(); i++) {JSONArray array = jsonArray.getJSONArray(i);//3.3、将StockBasicInfo对象保存到List中stockBasicInfos.add(new StockBasicInfo(array.getString(0),array.getString(1),array.getString(2),array.getString(3),array.getString(4),array.getString(5),array.getString(6),array.getString(7),array.getString(8),todayStr));}//4、进行数据插入或更新操作int insertCount=0,updateCount=0;//插入或更新if(stockBasicInfos.size()>0){//有需要插入或更新的数据，开始更新for (StockBasicInfo sbi : stockBasicInfos) {try {int rows = stockBasicInfoMapper.insertStockBasicInfo(sbi);insertCount+=rows;} catch (Exception e) {//1、判断是否为SQLIntegrityConstraintViolationException类型的异常if(e.getCause() instanceof SQLIntegrityConstraintViolationException){//2、如果是该类型，判断是否为重复主键错误String errorMsg = e.getMessage();//System.out.println(errorMsg);if(errorMsg.contains("Duplicate entry") && errorMsg.contains("PRIMARY")){//3、如果是重复主键错误，说明该数据，数据库已经存在了，没必要在Insert，进行该数据的update 即可int rows = stockBasicInfoMapper.updateStockBasicInfo(sbi);updateCount+=rows;}}}}}//5、展示结果System.out.println("插入成功了"+insertCount+"条数据");System.out.println("更新成功了"+updateCount+"条数据");}/*** 根据今天日期，删除不是今天数据的退市数据* @param todayStr*/public void delStockBasicInfosByOpDate(String todayStr){//更新完毕后，已经退市的数据要从数据库删除掉int delCount = stockBasicInfoMapper.delStockBasicInfosByOpDate(todayStr);System.out.println("删除成功了"+delCount+"条失效数据");}
}

StockBasicInfoMapper


import com.quanttradedata.stock.javabean.StockBasicInfo;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;@Repository
public interface StockBasicInfoMapper {/*** 添加一条股票基本信息* @param sbi* @return*/int insertStockBasicInfo(@Param("sbi") StockBasicInfo sbi);/*** 更新一条股票基本信息* @param sbi* @return*/int updateStockBasicInfo(@Param("sbi") StockBasicInfo sbi);/*** 删除不是今天的股票基本信息（删除退市数据）* @param todayStr* @return*/int delStockBasicInfosByOpDate(@Param("todayStr")String todayStr);
}

StockBasicInfoMapper.xml


<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.quanttradedata.stock.mapper.StockBasicInfoMapper"><insert id="insertStockBasicInfo">insert into tb_stock_basic_info values (#{sbi.tscode},#{sbi.stockcode},#{sbi.stockname},#{sbi.stockarea},#{sbi.industry},#{sbi.cnspell},#{sbi.market},#{sbi.list_date},#{sbi.is_hs},#{sbi.op_date})</insert><update id="updateStockBasicInfo">update tb_stock_basic_info setstockname=#{sbi.stockname},stockarea=#{sbi.stockarea},industry=#{sbi.industry},cnspell=#{sbi.cnspell},market=#{sbi.market},list_date=#{sbi.list_date},is_hs=#{sbi.is_hs},op_date=#{sbi.op_date}where stockcode = #{sbi.stockcode}</update><delete id="delStockBasicInfosByOpDate">delete from tb_stock_basic_info where op_date!=#{todayStr}</delete>
</mapper>