java从数据库查询的数据导出方法有哪些

Java从数据库查询的数据导出方法有哪些

在Java开发中,将数据库查询结果导出为文件是常见的需求，本文将介绍几种常见的数据导出方法，包括使用JDBC、Apache POI、EasyExcel和Apache Commons CSV等工具来实现这一功能。

使用JDBC导出数据

JDBC（Java Database Connectivity）是Java访问数据库的标准接口，通过JDBC，可以执行SQL查询并将结果集导出为文件，以下是一个简单的示例：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.io.FileWriter;
import java.io.PrintWriter;
public class ExportDataUsingJDBC {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:mysql://localhost:3306/yourdatabase";
        String username = "yourusername";
        String password = "yourpassword";
        String query = "SELECT * FROM yourtable";
        try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password);
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(query)) {
            FileWriter fileWriter = new FileWriter("exported_data.csv");
            PrintWriter printWriter = new PrintWriter(fileWriter);
            boolean firstRow = true;
            while (resultSet.next()) {
                if (firstRow) {
                    int columnCount = resultSet.getMetaData().getColumnCount();
                    for (int i = 1; i <= columnCount; i++) {
                        printWriter.print(resultSet.getMetaData().getColumnName(i));
                        if (i < columnCount) {
                            printWriter.print(",");
                        }
                    }
                    printWriter.println();
                    firstRow = false;
                } else {
                    for (int i = 1; i <= columnCount; i++) {
                        printWriter.print(resultSet.getString(i));
                        if (i < columnCount) {
                            printWriter.print(",");
                        }
                    }
                    printWriter.println();
                }
            }
            printWriter.close();
            fileWriter.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

使用Apache POI导出数据到Excel
Apache POI是一个强大的库，用于处理Microsoft Office格式的文件，包括Excel，以下是使用Apache POI将查询结果导出到Excel的示例：

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.*;
public class ExportDataToExcelUsingPOI {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:mysql://localhost:3306/yourdatabase";
        String username = "yourusername";
        String password = "yourpassword";
        String query = "SELECT * FROM yourtable";
        try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password);
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(query);
             Workbook workbook = new XSSFWorkbook();
             FileOutputStream fileOutputStream = new FileOutputStream("exported_data.xlsx")) {
            Sheet sheet = workbook.createSheet("Data");
            Row headerRow = sheet.createRow(0);
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            for (int i = 1; i <= columnCount; i++) {
                Cell cell = headerRow.createCell(i - 1);
                cell.setCellValue(metaData.getColumnName(i));
            }
            int rowIndex = 1;
            while (resultSet.next()) {
                Row row = sheet.createRow(rowIndex++);
                for (int i = 1; i <= columnCount; i++) {
                    Cell cell = row.createCell(i - 1);
                    cell.setCellValue(resultSet.getString(i));
                }
            }
            workbook.write(fileOutputStream);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

使用EasyExcel导出数据到Excel
EasyExcel是阿里巴巴开源的一个轻量级Excel处理工具,支持快速读写Excel文件，以下是使用EasyExcel将查询结果导出到Excel的示例：
import com.alibaba.excel.EasyExcel;
import java.util.List;
import java.util.ArrayList;
import java.sql.*;
public class ExportDataToExcelUsingEasyExcel {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:mysql://localhost:3306/yourdatabase";
        String username = "yourusername";
        String password = "yourpassword";
        String query = "SELECT * FROM yourtable";
        try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password);
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(query)) {
            List<YourTable> dataList = new ArrayList<>();
            while (resultSet.next()) {
                YourTable data = new YourTable();
                // Assuming YourTable has getter and setter methods for each column in the table
                data.setColumn1(resultSet.getString("column1"));
                data.setColumn2(resultSet.getString("column2"));
                // ... other columns ...
                dataList.add(data);
            }
            EasyExcel.write("exported_data.xlsx", YourTable.class).sheet("Data").doWrite(dataList);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

使用Apache Commons CSV导出数据到CSV文件
Apache Commons CSV是一个处理CSV文件的库，适用于将查询结果导出为CSV文件，以下是使用Apache Commons CSV将查询结果导出到CSV的示例：
import org.apache.commons.csv.*;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;
import java.util.*;
public class ExportDataToCSVUsingCommonsCSV {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:mysql://localhost:3306/yourdatabase";
        String username = "yourusername";
        String password = "yourpassword";
        String query = "SELECT * FROM yourtable";
        try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password);
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(query);
             FileWriter fileWriter = new FileWriter("exported_data.csv")) {
            CSVPrinter csvPrinter = new CSVPrinter(fileWriter, CSVFormat.DEFAULT.withHeader("Column1", "Column2", "Column3")); // Add more columns as needed
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            String[] headers = new String[columnCount];
            for (int i = 1; i <= columnCount; i++) {
                headers[i - 1] = metaData.getColumnName(i);
            }
            csvPrinter.printRecord(headers);
            while (resultSet.next()) {
                String[] values = new String[columnCount];
                for (int i = 1; i <= columnCount; i++) {
                    values[i - 1] = resultSet.getString(i);
                }
                csvPrinter.printRecord(values);
            }
            csvPrinter.flush();
            csvPrinter.close();
        } catch (Exception e) {
            e.printStackTrace();
        }