Java Maven项目：excel多表数据传入MySQL

1.创数据库及表

CREATE DATABASE med_info

CREATE TABLE IF NOT EXISTS national_code(
	ncr_id INT PRIMARY KEY NOT NULL auto_increment,
	ncr_name VARCHAR(30) NOT NULL,
	ncr_value VARCHAR(20) NOT null,
	ncr_value_meaning VARCHAR(30) not null,
	ncr_category varchar(30),
	ncr_note MEDIUMTEXT
)DEFAULT CHARSET=utf8 ;

CREATE table if not exists icd_diagnose(
	icdd_id int PRIMARY key not null auto_increment,
	icdd_name VARCHAR(10) not null,
	icdd_value VARCHAR(20) not null,
	icdd_value_meaning VARCHAR(30) not null,
	icdd_category VARCHAR(20),
	icdd_note MEDIUMTEXT
)DEFAULT CHARSET=utf8;

CREATE table if not exists icd_operate(
	icdo_id int PRIMARY key not null auto_increment,
	icdo_name VARCHAR(10) not null,
	icdo_value VARCHAR(20) not null,
	icdo_value_meaning VARCHAR(30) not null,
	icdo_category VARCHAR(20),
	icdo_note MEDIUMTEXT
)DEFAULT CHARSET=utf8;

CREATE table if not exists cv_health_info(
	cv_id int PRIMARY key not null auto_increment,
	cv_name VARCHAR(30) not null,
	cv_value VARCHAR(20) not null,
	cv_value_meaning VARCHAR(30) not null,
	cv_category VARCHAR(50),
	cv_note MEDIUMTEXT
)DEFAULT CHARSET=utf8;

CREATE table if not exists elec_med_record(
	emr_id int primary key not null auto_increment,
	emr_name varchar(20) not null,
	emr_value varchar(20) not null,
	emr_value_meaning varchar(30) not null,
	emr_catetory VARCHAR(30),
	emr_note MEDIUMTEXT
)DEFAULT CHARSET=utf8;

2.添加依赖

pom.xml

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.15</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.15</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>6.0.6</version>
</dependency>

3.数据库操作类

MysqlOperate.java

package com.demo.main;

import java.sql.*;
import java.util.List;

public class MysqlOperate {
    //mysql驱动包名
    private static final String DRIVER_NAME = "com.mysql.cj.jdbc.Driver";
    //数据库连接地址
    private static final String URL = "jdbc:mysql://localhost:3306/med_info?serverTimezone=GMT%2B8&useSSL=false";
    private static final String USER_NAME = "root";
    private static final String PASSWORD = "password";

    private static Connection getConnection() {
        Connection conn = null;
        try {
            Class.forName(DRIVER_NAME);
            conn = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
            return conn;
        } catch (Exception e) {
            return null;
        }
    }

    private static void insertSheet(Connection connection, List<List<List<String>>> listData, String sql, int sheetId) {
        try {
            PreparedStatement preparedStatement = connection.prepareStatement("insert into "+sql+" values(?,?,?,?,?)");
            for(int i = 0;i<listData.get(sheetId).size();i++) {
                preparedStatement.setString(1, listData.get(sheetId).get(i).get(0));
                preparedStatement.setString(2, listData.get(sheetId).get(i).get(1));
                preparedStatement.setString(3, listData.get(sheetId).get(i).get(2));
                preparedStatement.setString(4, listData.get(sheetId).get(i).get(3));
                preparedStatement.setString(5, listData.get(sheetId).get(i).get(4));
                preparedStatement.executeUpdate();
            }
            preparedStatement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            System.out.println("sheet"+sheetId+"数据插入成功!\n");
        }
    }

    public void insertAllSheet(List<List<List<String>>> listData) {
        insertSheet(getConnection(), listData,"national_code(ncr_name,ncr_value,ncr_value_meaning,ncr_category,ncr_note)",0);
        insertSheet(getConnection(), listData,"icd_diagnose(icdd_name,icdd_value,icdd_value_meaning,icdd_category,icdd_note)",1);
        insertSheet(getConnection(), listData,"icd_operate(icdo_name,icdo_value,icdo_value_meaning,icdo_category,icdo_note)",2);
        insertSheet(getConnection(), listData,"cv_health_info(cv_name,cv_value,cv_value_meaning,cv_category,cv_note)",3);
        insertSheet(getConnection(), listData,"elec_med_record(emr_name,emr_value,emr_value_meaning,emr_category,emr_note)",4);
    }
}

4.excel操作类

ExcelOperate.java

package com.demo.main;

import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * @description: excel表操作
 * @author: Rosa
 * @create: 2018-11-09 11:49
 **/
public class ExcelOperate {

    public static List<List<List<String>>> getAllData(String filePath) {
        //记录
        List<String> sheetNameList = new ArrayList<>();
        List<List<List<String>>> listData = new ArrayList<>();
        try {
            //InputStream inputStream = new FileInputStream(filePath);
            //创建对excel工作簿文件的引用
            InputStream is = new FileInputStream(filePath);
            XSSFWorkbook workbook = new XSSFWorkbook(is);
            //创建对工作表的引用
            XSSFSheet sheet = null;
            //获取每个Sheet表
            for (int i = 0; i < workbook.getNumberOfSheets() - 1; i++) {
                sheetNameList.add(workbook.getSheetName(i));
                //读取工作表
                sheet = workbook.getSheetAt(i);
                List<List<String>> sheetList = new ArrayList<>();
                if (sheet == null)
                    continue;
                //获取除第一行外每行
                for (int row = 1; row < sheet.getLastRowNum(); row++) {
                    XSSFRow xssfRow = sheet.getRow(row);
                    List<String> rowList = new ArrayList<String>();
                    //获取每个单元格
                    for (int col = xssfRow.getFirstCellNum(); col < xssfRow.getLastCellNum(); col++) {
                        rowList.add(String.valueOf(xssfRow.getCell(col)));
                    }
                    sheetList.add(rowList);
                }
                listData.add(sheetList);
            }
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return listData;
    }
}

5.主函数

Test.java

package com.demo.main;

import java.util.List;

/**
 * @description: 测试用主函数
 * @author: Rosa
 * @create: 2018-11-09 16:07
 **/
public  class Test {
    public static void main(String[] args){

        List<List<List<String>>> listData= ExcelOperate.getAllData("F://测试用数据.xlsx");
        MysqlOperate mysqlOperate = new MysqlOperate();
        mysqlOperate.insertAllSheet(listData);
    }
}

6.结果

navicat查看结果