springboot+jdbcTemplate+sqlite编程示例——以沪深300成分股数据处理为例

引言

我们在自己做一些小的项目或者小的数据处理分析的时候，很多时候是不需要用到mysql这样的大型数据库，并且也不需要用到maven这样很重的框架的，取而代之可以使用jdbcTemplate+sqlite这样的组合。

本文就介绍一下使用springboot+jdbcTemplate+sqlite编程的方法，本文会以处理沪深300成分股数据为例。

数据源

首先介绍一下本文进行数据处理的结果，我们最后会获得沪深300成分股列表，包括每一只股票的代码，名称，所述行业以及权重，最后处理的结果如下图所示

我们的数据源主要来自中证指数官网，有两个数据源，分别是沪深300权重列表和行业分类表

沪深300指数-中证指数有限公司 (csindex.com.cn)

行业分类查询-中证指数有限公司 (csindex.com.cn)

综合这两张表就可得到沪深300成分股的权重占比以及所属行业信息了

数据库初始化

本文使用的sqlite数据库可以说是一个嵌入式数据库，几乎没有依赖，我们只需要在项目中引入sqlite-jdbc就行了

        <dependency><groupId>org.xerial</groupId><artifactId>sqlite-jdbc</artifactId><version>3.36.0</version></dependency>

当我们引入sqlite-jdbc之后，可以在依赖包中看到数据库实例，我们不需要自己再去安装数据库

这个数据库的所有信息都会保存在一个db文件里，我们可以在项目的资源目录中创建这个db文件

然后在application.properties配置文件中指定该db文件，由于数据库是本地的，所以连用户名和密码都不用，不过在性能上好像会比mysql慢一点，不过这么方便，而且几乎没有依赖的数据库还要什么自行车呢，对于自己做一些小玩具完全是够用了。

spring.datasource.url=jdbc:sqlite:file:src/main/resources/database.db

数据处理

我们进行数据处理的时候主要是处理excel信息，这里会用到hutool工具包以及poi，需要引入如下依赖

        <dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>5.0.0</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>5.0.0</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-full</artifactId><version>5.0.0</version></dependency><dependency><groupId>cn.hutool</groupId><artifactId>hutool-all</artifactId><version>5.7.15</version></dependency>

我们主要获取以下几个维度的数据

@Data
@AllArgsConstructor
@NoArgsConstructor
public class CSI300Entity {private Integer id;private String code;private String name;private String industry;private Double weight;
}

我们把所有的数据处理都写到一个类里，包括创建表，插入数据和查询所有数据

@Slf4j
@Repository
public class SQLIteCSI300Dao implements CSI300Dao{private final String tableName = "csi_300";@Autowiredprivate JdbcTemplate jdbcTemplate;@Overridepublic void createTableIfNotExist() {Integer count = jdbcTemplate.queryForObject("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name=?", Integer.class, tableName);if (count == 0) {String sql = "create table " + tableName + "(" +"id INTEGER PRIMARY KEY AUTOINCREMENT," +"code VARCHAR(20)," +"name VARCHAR(20)," +"industry VARCHAR(20)," +"weight float)";jdbcTemplate.execute(sql);log.info(tableName + "建表成功");} else {log.info(tableName + "表已经存在，无需创建");}}@Overridepublic int insertOneItem(CSI300Entity entity) {String sql = "INSERT INTO " + tableName +"(code, name, industry, weight) VALUES " +"(?, ?, ?, ?)";Object[] params = new Object[] {entity.getCode(),entity.getName(),entity.getIndustry(),entity.getWeight()};int num = jdbcTemplate.update(sql, params);StringBuilder sb = new StringBuilder();sb.append("执行" + sql);sb.append("=>[");for (int i=0; i<params.length; i++) {sb.append(params[i].toString());if (i != params.length-1) {sb.append(", ");}}sb.append("]");if(num == 0) {log.error(sb.toString() + "失败");} else {log.info(sb.toString() + "成功");}return num;}@Overridepublic List<CSI300Entity> queryAllItems() {String sql = "SELECT * FROM " + tableName;List<CSI300Entity> csi300Entities = jdbcTemplate.query(sql, new Object[]{}, new BeanPropertyRowMapper<CSI300Entity>(CSI300Entity.class));return csi300Entities;}
}

然后是解析excel表格汇总数据

@Slf4j
@Service
public class CSI300Service {private final String FILE_PATH_WEIGHT = "E:/数据可视化工具/data_cat/数据/000300closeweight.xls";private final String FILE_PATH_INDUSTRY = "E:/数据可视化工具/data_cat/数据/行业分类.xlsx";private List<CSI300Entity> csi300EntityList;@Autowiredprivate SQLIteCSI300Dao sqlIteCSI300Dao;// 通过解析excel获取信息public void parseExcel() {ExcelReader excelReader = ExcelUtil.getReader(FILE_PATH_WEIGHT);excelReader.addHeaderAlias("成份券代码Constituent Code", "code");excelReader.addHeaderAlias("成份券名称Constituent Name", "name");excelReader.addHeaderAlias("权重(%)weight", "weight");csi300EntityList = excelReader.readAll(CSI300Entity.class);log.info("成功解析出" + csi300EntityList.size() + "条数据");log.info("开始解析所属行业");excelReader = ExcelUtil.getReader(FILE_PATH_INDUSTRY);excelReader.addHeaderAlias("证券代码", "code");excelReader.addHeaderAlias("证监会行业门类简称", "industry");List<CSI300Entity> csi300Entities = excelReader.readAll(CSI300Entity.class);for(int i=0; i<csi300Entities.size(); i++) {for(int j=0; j<csi300EntityList.size(); j++) {if (csi300EntityList.get(j).getCode().equals(csi300Entities.get(i).getCode())) {csi300EntityList.get(j).setIndustry(csi300Entities.get(i).getIndustry());}}}for(int i=0; i<csi300EntityList.size(); i++) {log.info(csi300EntityList.get(i).toString());sqlIteCSI300Dao.insertOneItem(csi300EntityList.get(i));}}}

最后看一下控制层，在编写控制层的时候加上@CrossOrigin表示这个类的所有接口都是允许跨域请求的

@Controller
@CrossOrigin
public class CSI300Controller {@Autowiredprivate SQLIteCSI300Dao sqlIteCSI300Dao;@Autowiredprivate CSI300Service csi300Service;@RequestMapping("/createTable")@ResponseBodypublic String createTable() {sqlIteCSI300Dao.createTableIfNotExist();return "success";}@RequestMapping("/parse")@ResponseBodypublic String parse() {csi300Service.parseExcel();return "success";}@RequestMapping("/queryAll")@ResponseBodypublic String queryAll() {List<CSI300Entity> csi300Entities = sqlIteCSI300Dao.queryAllItems();return JSON.toJSONString(csi300Entities);}
}

由于数据量不大，这里就将所有的数据展示出来，大家有需要可以自取

"1","000001","平安银行","金融业","0.545"
"2","000002","万科A","房地产业","0.452"
"3","000063","中兴通讯","制造业","0.474"
"4","000069","华侨城A","房地产业","0.082"
"5","000100","TCL科技","制造业","0.365"
"6","000157","中联重科","制造业","0.186"
"7","000166","申万宏源","金融业","0.236"
"8","000301","东方盛虹","制造业","0.155"
"9","000333","美的集团","制造业","1.474"
"10","000338","潍柴动力","制造业","0.469"
"11","000408","藏格矿业","制造业","0.11"
"12","000425","徐工机械","制造业","0.152"
"13","000538","云南白药","制造业","0.21"
"14","000568","泸州老窖","制造业","0.886"
"15","000596","古井贡酒","制造业","0.245"
"16","000617","中油资本","金融业","0.084"
"17","000625","长安汽车","制造业","0.588"
"18","000651","格力电器","制造业","0.862"
"19","000661","长春高新","制造业","0.302"
"20","000708","中信特钢","制造业","0.084"
"21","000723","美锦能源","制造业","0.109"
"22","000725","京东方A","制造业","0.842"
"23","000733","振华科技","制造业","0.145"
"24","000768","中航西飞","制造业","0.177"
"25","000776","广发证券","金融业","0.248"
"26","000786","北新建材","制造业","0.137"
"27","000792","盐湖股份","制造业","0.5"
"28","000800","一汽解放","制造业","0.053"
"29","000858","五 粮 液","制造业","1.68"
"30","000876","新 希 望","制造业","0.132"
"31","000877","天山股份","制造业","0.07"
"32","000895","双汇发展","制造业","0.157"
"33","000938","紫光股份","制造业","0.268"
"34","000963","华东医药","批发和零售业","0.208"
"35","000977","浪潮信息","制造业","0.211"
"36","000983","山西焦煤","采矿业","0.128"
"37","001289","龙源电力","电力、热力、燃气及水的生产和供应业","0.017"
"38","001979","招商蛇口","房地产业","0.219"
"39","002001","新和成","制造业","0.183"
"40","002007","华兰生物","制造业","0.154"
"41","002027","分众传媒","租赁和商务服务业","0.384"
"42","002049","紫光国微","制造业","0.286"
"43","002050","三花智控","制造业","0.362"
"44","002064","华峰化学","制造业","0.079"
"45","002074","国轩高科","制造业","0.137"
"46","002120","韵达股份","交通运输、仓储和邮政业","0.072"
"47","002129","TCL中环","制造业","0.313"
"48","002142","宁波银行","金融业","0.527"
"49","002179","中航光电","制造业","0.293"
"50","002180","纳思达","制造业","0.159"
"51","002202","金风科技","制造业","0.139"
"52","002230","科大讯飞","信息传输、软件和信息技术服务业","0.481"
"53","002236","大华股份","制造业","0.187"
"54","002241","歌尔股份","制造业","0.25"
"55","002252","上海莱士","制造业","0.218"
"56","002271","东方雨虹","制造业","0.252"
"57","002304","洋河股份","制造业","0.408"
"58","002311","海大集团","制造业","0.213"
"59","002352","顺丰控股","交通运输、仓储和邮政业","0.6"
"60","002371","北方华创","制造业","0.36"
"61","002410","广联达","信息传输、软件和信息技术服务业","0.151"
"62","002414","高德红外","制造业","0.077"
"63","002415","海康威视","制造业","0.945"
"64","002459","晶澳科技","制造业","0.191"
"65","002460","赣锋锂业","制造业","0.255"
"66","002466","天齐锂业","制造业","0.301"
"67","002475","立讯精密","制造业","0.922"
"68","002493","荣盛石化","制造业","0.19"
"69","002555","三七互娱","信息传输、软件和信息技术服务业","0.2"
"70","002594","比亚迪","制造业","1.048"
"71","002601","龙佰集团","制造业","0.144"
"72","002648","卫星化学","制造业","0.156"
"73","002709","天赐材料","制造业","0.162"
"74","002714","牧原股份","农、林、牧、渔业","0.62"
"75","002736","国信证券","金融业","0.159"
"76","002756","永兴材料","制造业","0.083"
"77","002812","恩捷股份","制造业","0.2"
"78","002821","凯莱英","制造业","0.2"
"79","002841","视源股份","制造业","0.111"
"80","002916","深南电路","制造业","0.087"
"81","002920","德赛西威","制造业","0.209"
"82","002938","鹏鼎控股","制造业","0.087"
"83","003816","中国广核","电力、热力、燃气及水的生产和供应业","0.138"
"84","300014","亿纬锂能","制造业","0.301"
"85","300015","爱尔眼科","卫生和社会工作业","0.462"
"86","300033","同花顺","金融业","0.175"
"87","300059","东方财富","金融业","1.078"
"88","300122","智飞生物","制造业","0.454"
"89","300124","汇川技术","制造业","0.714"
"90","300142","沃森生物","制造业","0.231"
"91","300207","欣旺达","制造业","0.132"
"92","300223","北京君正","制造业","0.095"
"93","300274","阳光电源","制造业","0.502"
"94","300316","晶盛机电","制造业","0.164"
"95","300347","泰格医药","科学研究和技术服务业","0.189"
"96","300408","三环集团","制造业","0.228"
"97","300413","芒果超媒","文化、体育和娱乐业","0.112"
"98","300433","蓝思科技","制造业","0.151"
"99","300450","先导智能","制造业","0.166"
"100","300454","深信服","信息传输、软件和信息技术服务业","0.123"
"101","300496","中科创达","信息传输、软件和信息技术服务业","0.149"
"102","300498","温氏股份","农、林、牧、渔业","0.586"
"103","300601","康泰生物","制造业","0.125"
"104","300628","亿联网络","制造业","0.094"
"105","300661","圣邦股份","制造业","0.17"
"106","300750","宁德时代","制造业","2.557"
"107","300751","迈为股份","制造业","0.087"
"108","300759","康龙化成","科学研究和技术服务业","0.173"
"109","300760","迈瑞医疗","制造业","0.819"
"110","300763","锦浪科技","制造业","0.077"
"111","300769","德方纳米","制造业","0.084"
"112","300782","卓胜微","制造业","0.3"
"113","300896","爱美客","制造业","0.191"
"114","300919","中伟股份","制造业","0.077"
"115","300957","贝泰妮","制造业","0.053"
"116","300979","华利集团","制造业","0.042"
"117","300999","金龙鱼","制造业","0.121"
"118","600000","浦发银行","金融业","0.467"
"119","600009","上海机场","交通运输、仓储和邮政业","0.259"
"120","600010","包钢股份","制造业","0.202"
"121","600011","华能国际","电力、热力、燃气及水的生产和供应业","0.197"
"122","600015","华夏银行","金融业","0.214"
"123","600016","民生银行","金融业","0.555"
"124","600018","上港集团","交通运输、仓储和邮政业","0.082"
"125","600019","宝钢股份","制造业","0.321"
"126","600025","华能水电","电力、热力、燃气及水的生产和供应业","0.087"
"127","600028","中国石化","采矿业","0.604"
"128","600029","南方航空","交通运输、仓储和邮政业","0.192"
"129","600030","中信证券","金融业","1.215"
"130","600031","三一重工","制造业","0.471"
"131","600036","招商银行","金融业","2.068"
"132","600039","四川路桥","建筑业","0.117"
"133","600048","保利发展","房地产业","0.428"
"134","600050","中国联通","信息传输、软件和信息技术服务业","0.486"
"135","600061","国投资本","金融业","0.103"
"136","600085","同仁堂","制造业","0.212"
"137","600089","特变电工","制造业","0.408"
"138","600104","上汽集团","制造业","0.404"
"139","600111","北方稀土","制造业","0.291"
"140","600115","中国东航","交通运输、仓储和邮政业","0.167"
"141","600132","重庆啤酒","制造业","0.1"
"142","600150","中国船舶","制造业","0.359"
"143","600176","中国巨石","制造业","0.154"
"144","600183","生益科技","制造业","0.116"
"145","600188","兖矿能源","采矿业","0.164"
"146","600196","复星医药","制造业","0.206"
"147","600219","南山铝业","制造业","0.117"
"148","600233","圆通速递","交通运输、仓储和邮政业","0.131"
"149","600276","恒瑞医药","制造业","1.245"
"150","600309","万华化学","制造业","0.882"
"151","600332","白云山","制造业","0.123"
"152","600346","恒力石化","制造业","0.172"
"153","600362","江西铜业","制造业","0.107"
"154","600383","金地集团","房地产业","0.094"
"155","600406","国电南瑞","信息传输、软件和信息技术服务业","0.513"
"156","600426","华鲁恒升","制造业","0.258"
"157","600436","片仔癀","制造业","0.443"
"158","600438","通威股份","制造业","0.384"
"159","600460","士兰微","制造业","0.119"
"160","600519","贵州茅台","制造业","6.531"
"161","600547","山东黄金","采矿业","0.243"
"162","600570","恒生电子","信息传输、软件和信息技术服务业","0.265"
"163","600584","长电科技","制造业","0.252"
"164","600585","海螺水泥","制造业","0.32"
"165","600588","用友网络","信息传输、软件和信息技术服务业","0.2"
"166","600600","青岛啤酒","制造业","0.153"
"167","600606","绿地控股","建筑业","0.06"
"168","600660","福耀玻璃","制造业","0.35"
"169","600674","川投能源","电力、热力、燃气及水的生产和供应业","0.185"
"170","600690","海尔智家","制造业","0.486"
"171","600732","爱旭股份","制造业","0.103"
"172","600741","华域汽车","制造业","0.159"
"173","600745","闻泰科技","制造业","0.244"
"174","600754","锦江酒店","住宿和餐饮业","0.09"
"175","600760","中航沈飞","制造业","0.269"
"176","600763","通策医疗","卫生和社会工作业","0.107"
"177","600795","国电电力","电力、热力、燃气及水的生产和供应业","0.204"
"178","600803","新奥股份","电力、热力、燃气及水的生产和供应业","0.086"
"179","600809","山西汾酒","制造业","0.677"
"180","600837","海通证券","金融业","0.545"
"181","600845","宝信软件","信息传输、软件和信息技术服务业","0.175"
"182","600875","东方电气","制造业","0.094"
"183","600884","杉杉股份","制造业","0.088"
"184","600886","国投电力","电力、热力、燃气及水的生产和供应业","0.215"
"185","600887","伊利股份","制造业","1.007"
"186","600893","航发动力","制造业","0.278"
"187","600900","长江电力","电力、热力、燃气及水的生产和供应业","1.297"
"188","600905","三峡能源","电力、热力、燃气及水的生产和供应业","0.382"
"189","600918","中泰证券","金融业","0.116"
"190","600919","江苏银行","金融业","0.553"
"191","600926","杭州银行","金融业","0.171"
"192","600941","中国移动","信息传输、软件和信息技术服务业","0.477"
"193","600958","东方证券","金融业","0.264"
"194","600989","宝丰能源","制造业","0.187"
"195","600999","招商证券","金融业","0.303"
"196","601006","大秦铁路","交通运输、仓储和邮政业","0.251"
"197","601009","南京银行","金融业","0.217"
"198","601012","隆基绿能","制造业","0.747"
"199","601021","春秋航空","交通运输、仓储和邮政业","0.121"
"200","601066","中信建投","金融业","0.194"
"201","601088","中国神华","采矿业","0.603"
"202","601100","恒立液压","制造业","0.175"
"203","601111","中国国航","交通运输、仓储和邮政业","0.164"
"204","601117","中国化学","建筑业","0.141"
"205","601138","工业富联","制造业","0.348"
"206","601155","新城控股","房地产业","0.067"
"207","601166","兴业银行","金融业","1.232"
"208","601169","北京银行","金融业","0.389"
"209","601186","中国铁建","建筑业","0.203"
"210","601211","国泰君安","金融业","0.395"
"211","601216","君正集团","制造业","0.076"
"212","601225","陕西煤业","采矿业","0.438"
"213","601229","上海银行","金融业","0.34"
"214","601236","红塔证券","金融业","0.043"
"215","601238","广汽集团","制造业","0.128"
"216","601288","农业银行","金融业","0.678"
"217","601318","中国平安","金融业","2.565"
"218","601319","中国人保","金融业","0.094"
"219","601328","交通银行","金融业","0.931"
"220","601336","新华保险","金融业","0.151"
"221","601360","三六零","信息传输、软件和信息技术服务业","0.18"
"222","601377","兴业证券","金融业","0.244"
"223","601390","中国中铁","建筑业","0.336"
"224","601398","工商银行","金融业","0.979"
"225","601600","中国铝业","制造业","0.255"
"226","601601","中国太保","金融业","0.479"
"227","601607","上海医药","批发和零售业","0.116"
"228","601615","明阳智能","制造业","0.124"
"229","601618","中国中冶","建筑业","0.128"
"230","601628","中国人寿","金融业","0.29"
"231","601633","长城汽车","制造业","0.193"
"232","601658","邮储银行","金融业","0.283"
"233","601668","中国建筑","建筑业","0.605"
"234","601669","中国电建","建筑业","0.2"
"235","601688","华泰证券","金融业","0.428"
"236","601689","拓普集团","制造业","0.189"
"237","601698","中国卫通","信息传输、软件和信息技术服务业","0.045"
"238","601699","潞安环能","采矿业","0.152"
"239","601728","中国电信","信息传输、软件和信息技术服务业","0.468"
"240","601766","中国中车","制造业","0.367"
"241","601788","光大证券","金融业","0.184"
"242","601799","星宇股份","制造业","0.12"
"243","601800","中国交建","建筑业","0.159"
"244","601808","中海油服","采矿业","0.051"
"245","601816","京沪高铁","交通运输、仓储和邮政业","0.72"
"246","601818","光大银行","金融业","0.311"
"247","601838","成都银行","金融业","0.147"
"248","601857","中国石油","采矿业","0.472"
"249","601865","福莱特","制造业","0.084"
"250","601868","中国能建","建筑业","0.16"
"251","601872","招商轮船","交通运输、仓储和邮政业","0.113"
"252","601877","正泰电器","制造业","0.137"
"253","601878","浙商证券","金融业","0.118"
"254","601881","中国银河","金融业","0.149"
"255","601888","中国中免","租赁和商务服务业","0.505"
"256","601898","中煤能源","采矿业","0.099"
"257","601899","紫金矿业","采矿业","1.165"
"258","601901","方正证券","金融业","0.207"
"259","601919","中远海控","交通运输、仓储和邮政业","0.37"
"260","601939","建设银行","金融业","0.249"
"261","601985","中国核电","电力、热力、燃气及水的生产和供应业","0.385"
"262","601988","中国银行","金融业","0.488"
"263","601989","中国重工","制造业","0.263"
"264","601995","中金公司","金融业","0.207"
"265","601998","中信银行","金融业","0.096"
"266","603019","中科曙光","制造业","0.252"
"267","603185","弘元绿能","制造业","0.055"
"268","603195","公牛集团","制造业","0.066"
"269","603259","药明康德","科学研究和技术服务业","0.984"
"270","603260","合盛硅业","制造业","0.102"
"271","603288","海天味业","制造业","0.369"
"272","603290","斯达半导","制造业","0.093"
"273","603369","今世缘","制造业","0.196"
"274","603392","万泰生物","制造业","0.108"
"275","603486","科沃斯","制造业","0.057"
"276","603501","韦尔股份","制造业","0.521"
"277","603659","璞泰来","制造业","0.114"
"278","603799","华友钴业","制造业","0.253"
"279","603806","福斯特","制造业","0.101"
"280","603833","欧派家居","制造业","0.084"
"281","603899","晨光股份","制造业","0.085"
"282","603986","兆易创新","制造业","0.364"
"283","603993","洛阳钼业","采矿业","0.214"
"284","605117","德业股份","制造业","0.063"
"285","605499","东鹏饮料","制造业","0.05"
"286","688005","容百科技","制造业","0.066"
"287","688008","澜起科技","制造业","0.272"
"288","688012","中微公司","制造业","0.427"
"289","688036","传音控股","制造业","0.207"
"290","688065","凯赛生物","制造业","0.055"
"291","688111","金山办公","信息传输、软件和信息技术服务业","0.323"
"292","688126","沪硅产业","制造业","0.14"
"293","688187","时代电气","制造业","0.058"
"294","688223","晶科能源","制造业","0.103"
"295","688303","大全能源","制造业","0.115"
"296","688363","华熙生物","制造业","0.08"
"297","688396","华润微","制造业","0.144"
"298","688561","奇安信","信息传输、软件和信息技术服务业","0.072"
"299","688599","天合光能","制造业","0.17"
"300","688981","中芯国际","制造业","0.61"

最后是用vue+element-plus编写一个简单的前端页面展示数据，页面非常简单，就是使用axios接收数据，然后渲染到表格中

<template><el-row><el-col :span="12"><el-card><el-table:data="table_data":show-header="true":max-height="635"stripe><el-table-column prop="id" label="序号"></el-table-column><el-table-column prop="code" label="股票代码"></el-table-column><el-table-column prop="name" label="公司简称"></el-table-column><el-table-column prop="industry" label="所属行业"></el-table-column><el-table-column prop="weight" label="权重占比"></el-table-column></el-table></el-card></el-col><el-col :span="12"> </el-col></el-row>
</template><script>
import axios from "axios";
export default {data() {return {table_data: [],};},mounted() {this.init();},methods: {init() {var url = "http://localhost:9001/queryAll";axios.get(url).then((response) => {this.table_data = response.data;console.log(response);}).catch(function (error) {console.log(error);});},},
};
</script><style scoped></style>

最后展示的效果就如文章开头那样。