读取excel数据批量插入数据库

package com;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.Arrays;

/**
 * @description:
 * @author:chliang
 * @date:2020/11/2 10:48
 */
public class ExcelUtil {

    public static void main(String[] args) {
        readExcel("D:/lch.xlsx");
    }

    /**
     * author:chliang
     * description://
     * return:table:表名，sql:执行sql，values:对应的值
     *
     * date: 2020/11/2 16:46
     **/
    public static JSONArray readExcel(String filePath){
        if(null == filePath || filePath.length() == 0){
            throw  new RuntimeException("传入文件为空");
        }
        if (!(filePath.endsWith(".xls") || filePath.endsWith(".xlsx"))){
            throw  new RuntimeException("请传入xls或xlsx文件");
        }
        File excel = new File(filePath);
        if (!excel.exists()){
           throw  new RuntimeException("excel路径不存在"+filePath);
        }
        JSONArray jsonArray = new JSONArray();
        Workbook hf = null;
        Sheet sheet = null;
        try {
            InputStream in = new FileInputStream(excel);
            hf = create(in);
            //获取每个sheet表
            for (int i=0;i<hf.getNumberOfSheets();i++){
                StringBuilder sb = new StringBuilder();
                sheet = hf.getSheetAt(i);
                JSONObject jsonObject = new JSONObject();
                jsonObject.put("table",sheet.getSheetName());
                Object[][] objects = new Object[sheet.getPhysicalNumberOfRows()-1][];
                sb.append("insert into ").append(sheet.getSheetName()).append("(");
                for (int j=0;j<sheet.getPhysicalNumberOfRows();j++){
                    Row row=sheet.getRow(j);
                    if (j==0){
                        for (int k=0;k<row.getPhysicalNumberOfCells();k++){
                            sb.append(row.getCell(k).toString()).append(",");
                            if (k == row.getPhysicalNumberOfCells()-1){
                                sb.deleteCharAt(sb.length()-1);
                                sb.append(") values(");
                                for (int p = 0;p<row.getPhysicalNumberOfCells();p++){
                                    sb.append("?,");
                                    if (p == row.getPhysicalNumberOfCells()-1){
                                        sb.deleteCharAt(sb.length()-1);
                                        sb.append(")");
                                    }
                                }
                            }
                        }
                        jsonObject.put("sql",sb.toString());
                    }else {
                        objects[j-1]  = new Object[row.getPhysicalNumberOfCells()];
                        for (int k=0;k<row.getPhysicalNumberOfCells();k++){
                            Cell cell = row.getCell(k);
                            if (null != cell){
                                cell.setCellType(CellType.STRING);
                                if ("".equals(cell.toString())){
                                    objects[j-1][k] = null;
                                }else {
                                    objects[j-1][k] = cell.toString();
                                }
                            }else {
                                objects[j-1][k] = null;
                            }
                        }
                    }
                }
                jsonObject.put("values",objects);
                if (objects.length > 0){
                    jsonArray.add(jsonObject);
                }
//                System.err.println(Arrays.deepToString(objects));
            }
        } catch (Exception e) {
            throw  new RuntimeException("生成sql语句出错:"+e.toString());
        }

        return jsonArray;
    }

    private static Workbook create(InputStream in) throws IOException {
        if(!in.markSupported()) {
            in = new PushbackInputStream(in, 8);
        }

        if(POIFSFileSystem.hasPOIFSHeader(in)) {
            //2003及以下
            return new HSSFWorkbook(in);
        }
        if(POIXMLDocument.hasOOXMLHeader(in)) {
            //2007及以上
            return new XSSFWorkbook(in);
        }
        throw new IllegalArgumentException("excel版本解析不了");
    }
}

package com;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbutils.QueryRunner;

import javax.sql.DataSource;
import java.beans.PropertyVetoException;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

/**
 * @description:
 * @author:chliang
 * @date:2020/11/2 13:50
 */
public class JdbcUtil {

    // 获得c3p0连接池对象
    private static final ComboPooledDataSource ds = new ComboPooledDataSource();


    static {
        String filePath = System.getProperty("user.dir") + "/c3p0.properties";
        Properties props = new Properties();
        File f = new File(filePath);
        if (!f.exists()){
            throw new RuntimeException("jar包目录下数据库配置文件c3p0.properties不存在");
        }
        try {
            props.load(new FileInputStream(f));
        } catch (IOException e) {
            throw new RuntimeException("读取数据库配置文件出错:"+e.toString());
        }
        try {
            ds.setDriverClass("com.mysql.jdbc.Driver");
        } catch (PropertyVetoException e) {
            System.err.println("加载DriverClass出错"+e);
        }
        ds.setJdbcUrl(props.getProperty("c3p0.jdbcUrl"));
        ds.setUser(props.getProperty("c3p0.user"));
        ds.setPassword(props.getProperty("c3p0.password"));
        ds.setMaxPoolSize(20);
        ds.setMinPoolSize(1);
        ds.setMaxStatements(0);
        ds.setMaxIdleTime(30);
    }

    /**
     * 获得数据库连接对象
     *
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }

    /**
     * 获得c3p0连接池对象
     * @return
     */
    public static DataSource getDataSource() {
        return ds;
    }

    public static int insertBatch(String table,String sql,Object[][] objects){
        QueryRunner qr = new QueryRunner(getDataSource());
        int sum = 0;
        try {
            int[] batch = qr.batch(sql, objects);
            for (int value : batch) {
                sum += value;
            }
        } catch (SQLException e) {
            System.err.println("执行insert表"+table+"出错:"+e);
        }
        return sum;
    }

    private static int insert(String table,String sql,Object object){
        QueryRunner qr = new QueryRunner(getDataSource());
        int sum=0;
        try {
            sum = qr.update(sql, object);
        } catch (SQLException e) {
            System.err.println("执行insert表"+table+"出错:"+e);
        }
        return sum;
    }


    public static void main(String[] args) throws SQLException {
        JSONArray jsonArray = ExcelUtil.readExcel("D:/lch.xlsx");
        for (Object jo : jsonArray){
            JSONObject jsonObject = (JSONObject) jo;
            Object[][] objects = (Object[][]) jsonObject.get("values");
            int insert = JdbcUtil.insertBatch(jsonObject.getString("table"), jsonObject.getString("sql"), objects);
            System.err.println("表"+jsonObject.getString("table")+"执行插入"+objects.length+"条数据，实际成功："+insert+"条");
        }
    }

}

 

package com;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

/**
 * @description:
 * @author:chliang
 * @date:2020/11/2 9:50
 */
public class AppStart {

    public static void main(String[] args) {
        if (args.length == 0){
            System.err.println("请在jar包后面传入一个excel");
            return;
        }
        String str = args[0];
        String filePath = System.getProperty("user.dir")+"/"+str;
        System.err.println("filePath="+filePath);
        JSONArray jsonArray = ExcelUtil.readExcel(filePath);
        for (Object jo : jsonArray){
            JSONObject jsonObject = (JSONObject) jo;
            Object[][] objects = (Object[][]) jsonObject.get("values");
            int insert = JdbcUtil.insertBatch(jsonObject.getString("table"), jsonObject.getString("sql"), objects);
            System.err.println("表"+jsonObject.getString("table")+"执行插入"+objects.length+"条数据，实际成功："+insert+"条");
        }
    }


}

<?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.test</groupId>
    <artifactId>insert-data</artifactId>
    <version>1.0-SNAPSHOT</version>


    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.18</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>4.0.0.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>4.0.0.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>com.mchange</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.5.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.16</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.15</version>
        </dependency>
        <dependency>
            <groupId>commons-dbutils</groupId>
            <artifactId>commons-dbutils</artifactId>
            <version>1.7</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.47</version>
        </dependency>
    </dependencies>

    <build>
        <sourceDirectory>src/main/java</sourceDirectory>
        <finalName>${project.artifactId}</finalName>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>8</source>
                    <target>8</target>
                    <encoding>UTF-8</encoding>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-surefire-plugin</artifactId>
                <version>2.6</version>
                <configuration>
                    <testFailureIgnore>true</testFailureIgnore>
                    <!--<testNGArtifactName>org.testng:testng</testNGArtifactName>-->
                    <forkMode>once</forkMode>
                    <skipTests>false</skipTests>
                    <properties>
                    </properties>
                </configuration>
            </plugin>

            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-shade-plugin</artifactId>
                <version>2.4.3</version>
                <executions>
                    <execution>
                        <phase>package</phase>
                        <goals>
                            <goal>shade</goal>
                        </goals>
                        <configuration>
                            <transformers>
                                <!-- 不覆盖同名文件，而是追加合并同名文件 -->
                                <transformer implementation="org.apache.maven.plugins.shade.resource.AppendingTransformer">
                                    <resource>META-INF/spring.handlers</resource>
                                </transformer>
                                <transformer implementation="org.apache.maven.plugins.shade.resource.AppendingTransformer">
                                    <resource>META-INF/spring.schemas</resource>
                                </transformer>
                                <transformer implementation="org.apache.maven.plugins.shade.resource.AppendingTransformer">
                                    <resource>META-INF/spring.tooling</resource>
                                </transformer>
                                <transformer implementation="org.apache.maven.plugins.shade.resource.ManifestResourceTransformer">
                                    <mainClass>com.AppStart</mainClass>
                                </transformer>
                            </transformers>
                        </configuration>
                    </execution>
                </executions>
            </plugin>


            <!--<plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-jar-plugin</artifactId>
                <version>3.1.0</version>
                <configuration>
                    <archive>
                        <manifest>
                            <mainClass>com.yiliao.TestRunStart</mainClass>
                        </manifest>
                    </archive>
                </configuration>
            </plugin>-->
        </plugins>
    </build>

    <repositories>
        <repository>
            <id>repo1-cache</id>
            <name>repo1-cache</name>
            <url>http://maven.iflytek.com:8081/nexus/content/groups/public</url>
        </repository>
        <repository>
            <id>repo2-cache</id>
            <name>repo2-cache</name>
            <url>http://maven.iflytek.com:8081/nexus/content/groups/public</url>
        </repository>
        <repository>
            <id>repo3-cache</id>
            <name>repo3-cache</name>
            <url>http://maven.iflytek.com:8081/nexus/content/groups/public</url>
        </repository>
    </repositories>
</project>