1.连接数据库
使用的是idea自带的database,但是在连接的时候要注意时区的选择,否则在存入时间的时候会出错。
网上有三种修改时区的办法:
1.MySQL
在Advanced选项下修改属性
ServerTimezone = Asia/Shanghai ;这个我没试过
2.postgrepsql
vm options 添加-Duser.timezone=PRC
3.clickhouse 试过之后是可行的
2.mybatis往数据库插入数据
1.controller层写了一个接口
package platform.task.controller;
import platform.task.service.custom.ClusterService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
/**
* @Title: ClusterController
* @Package: platform.task.controller
* @Description: 文件接口
* @Author:
* @Date: 2020-05-25 - 10:29
*/
@Api(value = "文件接口", tags = {"文件接口"})
@RestController
@RequiredArgsConstructor(onConstructor = @__(@Autowired))
@Slf4j
@RequestMapping("/task/cluster")
public class ClusterController {
private final ClusterService clusterService;
@ApiOperation(value = "上传文件", notes = "上传文件")
@PostMapping(value = "/upload")
public void upload(@ApiParam(name = "file", value = "簇文件(CSV格式)", required = true) @RequestParam("file") MultipartFile file,
@ApiParam(name = "name", value = "簇名称", required = true) @RequestParam("name") String name) {
clusterService.upload(file,name);
}
}
2.要存在数据库1中表的PO
package platform.task.po;
import com.baomidou.mybatisplus.annotation.*;
import lombok.Data;
import java.time.LocalDateTime;
/**
* @Title: ClusterPO
* @Package: platform.task.po
* @Description: 表对象
* @Author:
* @Date: 2020/05/19 - 19:00
*/
@Data
@TableName("\"task_cluster\"")
public class ClusterPO {
/**
* id
*/
@TableId(value = "id", type = IdType.ASSIGN_ID)
private Long id;
/**
* 版本时间
*/
@TableField("version_time")
private LocalDateTime versionTime;
/**
* 名称
*/
@TableField("name")
private String name;
/**
* 创建时间
*/
@TableField(value = "create_at", fill = FieldFill.INSERT)
private LocalDateTime createAt;
/**
* 更新时间
*/
@TableField(value = "update_at", fill = FieldFill.UPDATE)
private LocalDateTime updateAt;
/**
* 是否有效
*/
@TableField(value = "active", fill = FieldFill.INSERT)
@TableLogic
private Integer active;
}
3.一个基础的保存操作的服务类以及服务实现类,主要是为了保存
package task.service.base;
import platform.task.po.ClusterPO;
import com.baomidou.mybatisplus.extension.service.IService;
/**
* @Title: ClusterBaseService
* @Package: platform.task.service.base
* @Description: 表服务类
* @Author:
* @Date: 2020/05/19 - 19:00
*/
public interface ClusterBaseService extends IService<ClusterPO> {
}
package platform.task.service.base.impl;
import platform.task.dao.base.ClusterBaseDAO;
import platform.task.po.ClusterPO;
import platform.task.service.base.ClusterBaseService;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.stereotype.Service;
/**
* @Title: ClusterBaseServiceImpl
* @Package: platform.task.service.base.impl
* @Description: 表服务实现类
* @Author:
* @Date: 2020/05/19 - 19:00
*/
@Service
public class ClusterBaseServiceImpl extends ServiceImpl<ClusterBaseDAO, ClusterPO> implements ClusterBaseService {
}
4.然后是我刚写的接口的服务以及实现类
package platform.task.service.custom;
import org.springframework.web.multipart.MultipartFile;
/**
* @Title: ClusterService
* @Package: platform.task.service.custom
* @Description: 服务类
* @Author:
* @Date: 2020-05-25 - 14:54
*/
public interface ClusterService {
/**
* 上传文件
*
* @param file,name 文件,名称
* @return 结果
*/
void upload(MultipartFile file,String name);
}
package platform.task.service.custom.impl;
import platform.dataware.po.CellClusterPO;
import platform.dataware.service.CellClusterService;
import platform.task.po.ClusterPO;
import platform.task.service.base.ClusterBaseService;
import platform.task.service.custom.ClusterService;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* @Title: ClusterServiceImpl
* @Package: platform.task.service.custom.impl
* @Description: 服务实现类
* @Author:
* @Date: 2020-05-26 - 08:39
*/
@Service
@RequiredArgsConstructor(onConstructor = @__(@Autowired))
@Slf4j
public class ClusterServiceImpl implements ClusterService {
private final ClusterBaseService clusterBaseService;
private final CellClusterService cellClusterService;
/**
* 上传文件
*
* @param file,name 文件,名称
* @return 结果
*/
@Override
public void upload(MultipartFile file, String name) {
// 往pg中存入一条记录 名称和当前时间即可
ClusterPO clusterPo = new ClusterPO();
clusterPo.setName(name);
LocalDateTime today = LocalDateTime.now();
clusterPo.setVersionTime(today);
boolean result = clusterBaseService.save(clusterPo);
log.info("result: {}", result);
// 解析文件并将内容存入ck中
CellClusterPO cellclusterPo = new CellClusterPO();
Date date = new Date();
cellclusterPo.setVersionTime(date);
List<CellClusterPO> saveCluster = new ArrayList<>();
if (!file.isEmpty()) {
InputStreamReader isr = null;
BufferedReader br = null;
try {
isr = new InputStreamReader(file.getInputStream());
br = new BufferedReader(isr);
String line = null;
while ((line = br.readLine()) != null) {
String[] strs = line.split(",");
cellclusterPo.setCgi(strs[0]);
cellclusterPo.setOptimizedArea(new Integer(strs[1]));
saveCluster.add(cellclusterPo);
// log.info("line: {}", testCluster);
}
boolean insert = cellClusterService.save(saveCluster);
log.info("result: {}", insert);
} catch (IOException se) {
//Handle errors for JDBC
se.printStackTrace();
} finally {
try {
if (br != null) {
br.close();
}
if (isr != null) {
isr.close();
}
} catch (IOException e) {
}
}
}
}
}
上面保存进两个数据库里了,所以数据库2需要利用mybatis进行持久化
1.DAO接口
package platform.dataware.dao;
import platform.dataware.po.CellClusterPO;
import org.apache.ibatis.annotations.Param;
import java.util.List;
/**
* @Title: CellClusterDAO
* @Package: platform.dataware.dao
* @Description: 保存DAO接口
* @Author:
* @Date: 2020/06/01 - 17:02
*/
public interface CellClusterDAO {
/**
* 批量保存数据
*
* @param cellculsterpo 操作类
* @return 保存结果
*/
boolean save(@Param("cellculsterpo") List<CellClusterPO> cellculsterpo);
}
2.实体类PO
package platform.dataware.po;
import lombok.Data;
import java.util.Date;
/**
* @Title: CellClusterPO
* @Package: com.aspirecn.optimization.platform.dataware.po
* @Description: 表对象
* @Author:
* @Date: 2020/06/01 - 17:02
*/
@Data
public class CellClusterPO {
/**
* CGI
*/
private String cgi;
/**
* 版本时间
*/
private Date versionTime;
/**
* 是否是优化区域(0:,1:)
*/
private Integer optimizedArea;
}
3.服务以及实现类
package platform.dataware.service;
import platform.dataware.po.CellClusterPO;
import org.apache.ibatis.annotations.Param;
import java.util.List;
/**
* @Title: CellService
* @Package: platform.dataware.service
* @Description: 小区簇表服务类
* @Author:
* @Date: 2020/06/01 - 17:02
*/
public interface CellClusterService {
/**
*批量保存数据
*
* @param cellculsterpo 操作类
* @return 保存结果
*/
boolean save(@Param("cellculsterpo") List<CellClusterPO> cellculsterpo);
}
package platform.dataware.service.impl;
import platform.dataware.dao.CellClusterDAO;
import platform.dataware.po.CellClusterPO;
import platform.dataware.service.CellClusterService;
import com.baomidou.dynamic.datasource.annotation.DS;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.annotations.Param;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
/**
* @Title: BeamServiceImpl
* @Package: platform.dataware.service.impl
* @Description: 服务实现类
* @Author:
* @Date: 2020/06/01 - 17:02
*/
@DS("ck")
@Service
@RequiredArgsConstructor(onConstructor = @__(@Autowired))
@Slf4j
public class CellClusterServiceImpl implements CellClusterService {
private final CellClusterDAO cellClusterDAO;
@Override
public boolean save(@Param("cellculsterpo") List<CellClusterPO> cellculsterpo) {
cellClusterDAO.save(cellculsterpo);
return true;
}
}
4.xml文件,批量保存的
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="platform.dataware.dao.CellClusterDAO">
<!-- <resultMap type="platform.dataware.po.CellClusterPO" id="CellClusterMap">-->
<!-- <result column="cgi" property="cgi"/>-->
<!-- <result column="vtime" property="versionTime"/>-->
<!-- <result column="is_optimized_area" property="is_optimized_area"/>-->
<!-- </resultMap>-->
<insert id="save" parameterType="platform.dataware.po.CellClusterPO">
insert into "cell_cluster_test" (vtime, cgi, is_optimized_area)values
<foreach collection="cellculsterpo" item="cellculsterpo" separator="," close=";">
(#{cellculsterpo.versionTime},#{cellculsterpo.cgi},#{cellculsterpo.optimizedArea})
</foreach>
</insert>
</mapper>