21xrx.com
2024-09-20 09:11:13 Friday
登录
文章检索 我的文章 写文章
Java实现Excel导入数据库操作示例
2023-06-19 09:41:49 深夜i     --     --

在Web开发中，我们通常需要从Excel文件中导入数据到数据库中，这时候需要使用Java编写程序实现Excel文件的读取和数据库的写入操作。本文将提供一个Java实现Excel导入数据库的示例。

代码案例：

下面是一个Java实现Excel导入数据库的示例代码，该示例使用了Apache POI和JDBC技术：



import java.io.File;


import java.io.FileInputStream;


import java.sql.Connection;


import java.sql.DriverManager;


import java.sql.PreparedStatement;


import java.sql.SQLException;


import java.util.Iterator;


import org.apache.poi.ss.usermodel.Cell;


import org.apache.poi.ss.usermodel.Row;


import org.apache.poi.ss.usermodel.Sheet;


import org.apache.poi.ss.usermodel.Workbook;


import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class ExcelToDatabase {


  public static void main(String[] args) {


    String jdbcUrl = "jdbc:mysql://localhost:3306/test?useSSL=false";


    String username = "root";


    String password = "root";


    String excelFilePath = "demo.xlsx";


    int batchSize = 20;


    Connection connection = null;


    PreparedStatement preparedStatement = null;


    try {


      // connect to database


      connection = DriverManager.getConnection(jdbcUrl, username, password);


      connection.setAutoCommit(false);


      // create statement


      String sql = "INSERT INTO users (name, email, country) VALUES (?, ?, ?)";


      preparedStatement = connection.prepareStatement(sql);


      // read excel file


      File file = new File(excelFilePath);


      FileInputStream inputStream = new FileInputStream(file);


      Workbook workbook = new XSSFWorkbook(inputStream);


      Sheet sheet = workbook.getSheetAt(0);


      Iterator
  rowIterator = sheet.iterator();
 


      int count = 0;


      while (rowIterator.hasNext()) {


        Row nextRow = rowIterator.next();


        Iterator
  cellIterator = nextRow.cellIterator();
 


        while (cellIterator.hasNext()) {


          Cell nextCell = cellIterator.next();


          int columnIndex = nextCell.getColumnIndex();


          switch (columnIndex) {


          case 0:


            String name = nextCell.getStringCellValue();


            preparedStatement.setString(1, name);


            break;


          case 1:


            String email = nextCell.getStringCellValue();


            preparedStatement.setString(2, email);


            break;


          case 2:


            String country = nextCell.getStringCellValue();


            preparedStatement.setString(3, country);


            break;


          }


        }


        preparedStatement.addBatch();


        if (count % batchSize == 0) {


          preparedStatement.executeBatch();


        }


        preparedStatement.executeBatch();


        connection.commit();


      }


      workbook.close();


      System.out.println("Import data successfully");


    } catch (Exception e) {


      e.printStackTrace();


      try {


        connection.rollback();


      } catch (SQLException e1) {


        e1.printStackTrace();


      }


    } finally {


      try {


        if (preparedStatement != null) {


          preparedStatement.close();


        }


        if (connection != null) {


          connection.close();


        }


      } catch (SQLException e) {


        e.printStackTrace();


      }


    }


  }


}

关键词：

1. Java

2. Excel

3. 数据库导入

  
  

评论区

{{item['qq_nickname']}}
()
回复
回复