package com.sczhaoqi.metertest;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.opencsv.CSVReader;
import com.opencsv.exceptions.CsvException;
import com.sczhaoqi.metertest.bean.ColumnInfo;
import com.sczhaoqi.metertest.bean.ColumnType;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.junit.jupiter.api.AfterAll;
import org.junit.jupiter.api.BeforeAll;
import org.junit.jupiter.api.Test;
import org.springframework.core.io.ClassPathResource;
import java.io.FileReader;
import java.io.IOException;
import java.math.BigDecimal;
import java.sql.*;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.IntStream;
import static com.sczhaoqi.metertest.bean.ColumnType.INT_NUM;
import static com.sczhaoqi.metertest.bean.ColumnType.VARCHAR;
/**
* @author zhaoqi
* @date 2023-02-22 下午4:48
*/
@Slf4j
public class ExcelToDbTest {
private final String colPrefix = "col_";
@Test
public void loadExcelToMysql() throws IOException, CsvException, SQLException {
String fileName = "online_dependence1.csv";
Boolean append = false;
Integer tableId = 1;
// id INT_NUM
// parentId INT_NUM
// parentName VARCHAR
// parentPlanId INT_NUM
// parentCron VARCHAR
// parentPlanType INT_NUM
// childId INT_NUM
// childName VARCHAR
// childPlanId INT_NUM
// childCron VARCHAR
// childPlanType INT_NUM
// forceDepend INT_NUM
// depType INT_NUM
// depTime VARCHAR
List<ColumnInfo> columns = Arrays.asList(
ColumnInfo.builder().name("id").type(INT_NUM).comment("").build(),
ColumnInfo.builder().name("parentId").type(INT_NUM).comment("").build(),
ColumnInfo.builder().name("parentName").type(VARCHAR).comment("").build(),
ColumnInfo.builder().name("parentPlanId").type(INT_NUM).comment("").build(),
ColumnInfo.builder().name("parentCron").type(VARCHAR).comment("").build(),
ColumnInfo.builder().name("parentPlanType").type(INT_NUM).comment("").build(),
ColumnInfo.builder().name("childId").type(INT_NUM).comment("").build(),
ColumnInfo.builder().name("childName").type(VARCHAR).comment("").build(),
ColumnInfo.builder().name("childPlanId").type(INT_NUM).comment("").build(),
ColumnInfo.builder().name("childCron").type(VARCHAR).comment("").build(),
ColumnInfo.builder().name("childPlanType").type(INT_NUM).comment("").build(),
ColumnInfo.builder().name("forceDepend").type(INT_NUM).comment("").build(),
ColumnInfo.builder().name("depType").type(INT_NUM).comment("").build(),
ColumnInfo.builder().name("depTime").type(VARCHAR).comment("").build()
);
String tableName = getTableNameById(tableId);
if (StringUtils.isBlank(tableName)) {
tableName = createTableAndSave(tableId, columns);
}
List<Object[]> datas = new ArrayList<>();
try (CSVReader reader = new CSVReader(new FileReader(new ClassPathResource(fileName).getFile()))) {
List<String[]> list = reader.readAll();
for (String[] item : list) {
Object[] rowData = new Object[item.length];
for (int i = 0; i < columns.size(); i++) {
rowData[i] = toObject(item[i], columns.get(i).getType());
}
datas.add(rowData);
}
}
String insertSql = String.format("insert into %s (%s) values (%s)", tableName, IntStream.range(0, columns.size()).mapToObj(i -> colPrefix + i).collect(Collectors.joining(",")), IntStream.range(0, columns.size()).mapToObj(i -> "?").collect(Collectors.joining(",")));
log.info("insertSQL:{}", insertSql);
try (PreparedStatement ps = conn.prepareStatement(insertSql)) {
// 批量插入数据
for (int i = 1; i <= datas.size(); i++) {
Object[] row = datas.get(i - 1);
for (int j = 1; j <= row.length; j++) {
ps.setObject(j, row[j - 1]);
}
// 1.攒SQL
ps.addBatch();
// 攒够500条
if (i % 500 == 0 || i == 20000) {
// 2.执行batch
ps.executeBatch();
// 3.清空batch
ps.clearBatch();
}
}
}
}
public static Object toObject(String data, ColumnType columnType) {
Object cr = null;
if (StringUtils.isBlank(data)) {
return cr;
}
switch (columnType) {
case INT_NUM:
cr = Integer.valueOf(data);
break;
case DECIMAL_NUM:
cr = new BigDecimal(data);
break;
case DATE:
cr = LocalDate.parse(data, DateTimeFormatter.ofPattern("yyyy-MM-dd"));
break;
case DATETIME:
cr = LocalDateTime.parse(data, DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
break;
default:
case VARCHAR:
case LONG_VARCHAR:
cr = data;
break;
}
return cr;
}
public static final String url = "jdbc:mysql://192.168.49.2:30336/data?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
public static final String username = "root";
public static final String password = "root";
private static Connection conn;
@BeforeAll
public static void initConnection() throws SQLException {
conn = DriverManager.getConnection(url, username, password);
}
@AfterAll
public static void closeConnection() throws SQLException {
conn.close();
}
private String dataTableNameSQL = "select table_name from data_index_table where id = ?";
private String dataIndexTable = "" +
"create table if not exists data_index_table (" +
"id int(11) auto_increment primary key," +
"table_name varchar(128) not null ," +
"col_infos mediumtext not null " +
");" +
"";
private String getTableNameById(Integer id) throws SQLException {
try (PreparedStatement ps = conn.prepareStatement(dataIndexTable)) {
ps.execute();
}
String tableName = "";
try (
PreparedStatement ps = conn.prepareStatement(dataTableNameSQL);
) {
ps.setInt(1, id);
try (ResultSet rs = ps.executeQuery()) {
if (rs.next()) {
tableName = rs.getString(1);
}
}
}
log.info("{}", tableName);
return tableName;
}
private String tableRecord = "" +
"insert into data_index_table values(?,?, ?);" +
"";
private String createTableAndSave(Integer tableId, List<ColumnInfo> columnInfos) throws SQLException, JsonProcessingException {
String tableName = "data_" + DateTimeFormatter.ofPattern("yyyyMMddHHmmssS").format(LocalDateTime.now());
StringBuilder sqlBuilder = new StringBuilder("" +
"create table if not exists ");
sqlBuilder.append(tableName);
sqlBuilder.append("(");
int i = 0;
for (i = 0; i < columnInfos.size() - 1; i++) {
sqlBuilder.append(colSQL(i, columnInfos.get(i).getType(), columnInfos.get(i).getComment())).append(",");
}
sqlBuilder.append(colSQL(i, columnInfos.get(i).getType(), columnInfos.get(i).getComment())).append(")")
.append(" comment '").append(tableName).append("';");
try (PreparedStatement ps = conn.prepareStatement(sqlBuilder.toString());) {
ps.execute();
}
try (
PreparedStatement ps = conn.prepareStatement(tableRecord);
) {
ps.setInt(1, tableId);
ps.setString(2, tableName);
ps.setString(3, new ObjectMapper().writeValueAsString(columnInfos));
ps.execute();
}
return tableName;
}
private String colSQL(Integer index, ColumnType type, String comment) {
String sql = "";
switch (type) {
case INT_NUM:
sql = colPrefix + index + " int(11)";
break;
case DECIMAL_NUM:
sql = colPrefix + index + " decimal(10,4)";
break;
case DATE:
sql = colPrefix + index + " DATE";
break;
case DATETIME:
sql = colPrefix + index + " DATETIME";
break;
default:
case VARCHAR:
sql = colPrefix + index + " varchar(256)";
break;
case LONG_VARCHAR:
sql = colPrefix + index + " varchar(512)";
break;
}
if (StringUtils.isNotBlank(comment)) {
sql = String.format("%s comment '%s'", sql, comment);
}
return sql;
}
}
import java.util.Objects;
/**
*
*/
public interface BaseEnum {
Integer getCode();
String getDesc();
/**
* get byte code from current enum
*
* @return the byte code
*/
default Byte getByteCode() {
Integer code = getCode();
if (code == null) {
return null;
}
return Byte.valueOf(String.valueOf(code));
}
default boolean equalsByCode(Integer code) {
return Objects.equals(code, this.getCode());
}
default boolean equalsByCode(Byte code) {
return Objects.equals(code, this.getByteCode());
}
}
@NoArgsConstructor
@Data
@AllArgsConstructor
@Builder
public class ColumnInfo {
private Integer index;
private String name;
private ColumnType type;
private String typeExt;
private String comment;
}
public enum ColumnType implements BaseEnum {
INT_NUM(1, "整数"),
DECIMAL_NUM(2, "复数"),
VARCHAR(3, "字符串"),
LONG_VARCHAR(4, "长字符串"),
DATE(5, "日期"),
DATETIME(6, "时间"),
;
// 成员变量
private int code;
private String desc;
private ColumnType(int code, String desc) {
this.code = code;
this.desc = desc;
}
public Integer getCode() {
return code;
}
public String getDesc() {
return desc;
}
/**
* 根据值获取枚举对象
*
* @param code 或者name 枚举值
* @return 对象枚举对象
*/
public static String getValue(Integer code) {
if (code == null) {
return null;
}
for (ColumnType status : values()) {
if (status.getCode() == code) {
return status.getDesc();
}
}
return null;
}
public static ColumnType getByCode(Integer code) {
if (code == null) {
return null;
}
for (ColumnType status : values()) {
if (status.getCode().equals(code)) {
return status;
}
}
return null;
}
public static ColumnType getByName(String name) {
if (name == null) {
return null;
}
for (ColumnType status : values()) {
if (status.name().equals(name)) {
return status;
}
}
return null;
}
}