Java读取execl 利用JDBC插入Postgresql简单示例

1.Pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.bet</groupId>
    <artifactId>Forcast</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>17</maven.compiler.source>
        <maven.compiler.target>17</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.2.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>
    </dependencies>
</project>

2.Main

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

public class Main {

    public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException, ParseException {
        String fileName = "C:\\Users\\mengchen.shao\\Desktop\\BET\\英超.xlsx";
        insertMatchData(fileName);
    }

    private static Connection getConn() throws SQLException, ClassNotFoundException {
        Class.forName("org.postgresql.Driver");
        Connection conn = DriverManager.getConnection("jdbc:postgresql://12.0.0.1:5488/******", "******", "******");
        return conn;
    }

    private static LocalDate getMaxDate() throws SQLException, ClassNotFoundException {
        String sql = "SELECT max(match_datetime) as maxDate FROM test_matchs";
        PreparedStatement ps = getConn().prepareStatement(sql);
        ResultSet resultSet = ps.executeQuery();
        if (resultSet.next()) {
            String maxDate = resultSet.getString("maxDate");
            return LocalDate.parse(maxDate, DateTimeFormatter.ofPattern("yyyy-MM-dd"));
        } else {
            return LocalDate.now();
        }
    }

    public static void insertMatchData(String fileName) throws IOException, SQLException, ClassNotFoundException, ParseException {
        XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(fileName));
        XSSFSheet sheet = workbook.getSheet("测");

        String sql = "INSERT INTO public.test_matchs VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        PreparedStatement ps = getConn().prepareStatement(sql);

        SimpleDateFormat formatter = new SimpleDateFormat("yyyy/MM/dd HH:mm");
        LocalDate start = getMaxDate();

        for (int i = 1; i < sheet.getLastRowNum() + 1 ; i++) {
            System.out.println(i);
            Row row = sheet.getRow(i);
            String matchDateTime =  row.getCell(0).getStringCellValue();
            LocalDate matchDate =  LocalDate.parse(matchDateTime, DateTimeFormatter.ofPattern("yyyy/MM/dd HH:mm"));
            if (matchDate.compareTo(start) > 0) {
                String halfScore = row.getCell(10).getStringCellValue();
                String fullScore = row.getCell(11).getStringCellValue();
                Date date = formatter.parse(row.getCell(0).getStringCellValue());
                ps.setDate(1, new java.sql.Date(date.getTime()));
                ps.setString(2, row.getCell(1).getStringCellValue());
                ps.setString(3, row.getCell(2).getStringCellValue());
                ps.setString(4, row.getCell(3).getStringCellValue());
                ps.setBigDecimal(5, new BigDecimal(row.getCell(4).getStringCellValue()));
                ps.setBigDecimal(6, new BigDecimal(row.getCell(5).getStringCellValue()));
                ps.setBigDecimal(7, new BigDecimal(row.getCell(6).getStringCellValue()));
                ps.setBigDecimal(8, new BigDecimal(row.getCell(7).getStringCellValue()));
                ps.setBigDecimal(9, new BigDecimal(row.getCell(8).getStringCellValue()));
                ps.setBigDecimal(10, new BigDecimal(row.getCell(9).getStringCellValue()));
                ps.setInt(11, Integer.valueOf(halfScore.split(":")[0]));
                ps.setInt(12, Integer.valueOf(halfScore.split(":")[1]));
                ps.setInt(13, Integer.valueOf(fullScore.split(":")[0]));
                ps.setInt(14, Integer.valueOf(fullScore.split(":")[1]));
                ps.setBigDecimal(15, new BigDecimal(0.000));
                ps.setBigDecimal(16, new BigDecimal(0.000));
                ps.setBigDecimal(17, new BigDecimal(0.000));
                ps.setDate(18, new java.sql.Date(date.getTime()));
                ps.addBatch();
            }
        }
        int[] cnt = ps.executeBatch();
    }
}