mybatis写入数据库操作流程

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>

foreach标签

mybatis

批处理