Subversion Repositories SmartDukaan

package com.spice.profitmandi.common.util;

import org.springframework.beans.factory.annotation.Value;

import java.io.File;

import java.nio.file.*;

import java.sql.*;

public class CsvUtil {

    private CsvUtil() {

    }

    private static final String username = "root";

    private static final String password = "shop2020";

    public static File generateCsvFromQuery(String jdbcUrl, String query, String headers) throws Exception {

        Connection conn = DriverManager.getConnection(jdbcUrl, username, password);

        PreparedStatement stmt = conn.prepareStatement(query);

        ResultSet rs = stmt.executeQuery();

        ResultSetMetaData metaData = rs.getMetaData();

        int columnCount = metaData.getColumnCount();

        StringBuilder csvBuilder = new StringBuilder();

        // Headers

        if (headers != null && !headers.trim().isEmpty()) {

            csvBuilder.append(headers.trim()).append("\n");

        } else {

            for (int i = 1; i <= columnCount; i++) {

                csvBuilder.append(metaData.getColumnLabel(i));

                if (i < columnCount) csvBuilder.append(",");

            }

            csvBuilder.append("\n");

        }

        // Rows

        while (rs.next()) {

            for (int i = 1; i <= columnCount; i++) {

                String value = rs.getString(i);

                csvBuilder.append(value != null ? value.replaceAll(",", " ") : "");

                if (i < columnCount) csvBuilder.append(",");

            }

            csvBuilder.append("\n");

        }

        rs.close();

        stmt.close();

        conn.close();

        Path tempFile = Files.createTempFile("query-result-", ".csv");

        Files.write(tempFile, csvBuilder.toString().getBytes());

        return tempFile.toFile();

    }

}