Subversion Repositories SmartDukaan

Rev

Rev 34769 | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 34769 Rev 34835
Line 1... Line 1...
1 
package com.spice.profitmandi.common.util;
 1 
package com.spice.profitmandi.common.util;
2 
 
 2 
 
3 
import org.springframework.beans.factory.annotation.Value;
 3 
import org.springframework.beans.factory.annotation.Value;
4 
 
 4 
 
5 
import java.io.File;
 5 
import java.io.File;
- 
 
 6 
import java.nio.charset.StandardCharsets;
6 
import java.nio.file.*;
 7 
import java.nio.file.*;
7 
import java.sql.*;
 8 
import java.sql.*;
- 
 
 9 
import java.util.List;
- 
 
 10 
import java.util.Map;
- 
 
 11 
import java.util.Set;
8 
 
 12 
 
9 
public class CsvUtil {
 13 
public class CsvUtil {
10 
 
 14 
 
11 
    private CsvUtil() {
 15 
    private CsvUtil() {
12 
    }
 16 
    }
13 
 
 - 
 
- 
 
 17 
    private static final String connURL = "jdbc:mysql://192.168.142.141:3306/auth";
14 
    private static final String username = "root";
 18 
    private static final String username = "root";
15 
    private static final String password = "shop2020";
 19 
    private static final String password = "shop2020";
16 
 
 20 
 
17 
    public static File generateCsvFromQuery(String jdbcUrl, String query, String headers) throws Exception {
 21 
    public static File generateCsvFromQuery(String query, String headers) throws Exception {
18 
        Connection conn = DriverManager.getConnection(jdbcUrl, username, password);
 22 
        Connection conn = DriverManager.getConnection(connURL, username, password);
19 
        PreparedStatement stmt = conn.prepareStatement(query);
 23 
        PreparedStatement stmt = conn.prepareStatement(query);
20 
        ResultSet rs = stmt.executeQuery();
 24 
        ResultSet rs = stmt.executeQuery();
21 
 
 25 
 
22 
        ResultSetMetaData metaData = rs.getMetaData();
 26 
        ResultSetMetaData metaData = rs.getMetaData();
23 
        int columnCount = metaData.getColumnCount();
 27 
        int columnCount = metaData.getColumnCount();
Line 51... Line 55...
51 
 
 55 
 
52 
        Path tempFile = Files.createTempFile("query-result-", ".csv");
 56 
        Path tempFile = Files.createTempFile("query-result-", ".csv");
53 
        Files.write(tempFile, csvBuilder.toString().getBytes());
 57 
        Files.write(tempFile, csvBuilder.toString().getBytes());
54 
        return tempFile.toFile();
 58 
        return tempFile.toFile();
55 
    }
 59 
    }
- 
 
 60 
 
- 
 
 61 
    public static File generateCsvFromListMap(List<Map<String, Object>> data, String headers) throws Exception {
- 
 
 62 
        StringBuilder csvBuilder = new StringBuilder();
- 
 
 63 
 
- 
 
 64 
        if (headers != null && !headers.trim().isEmpty()) {
- 
 
 65 
            csvBuilder.append(headers.trim()).append("\n");
- 
 
 66 
        } else if (!data.isEmpty()) {
- 
 
 67 
            Set<String> keys = data.get(0).keySet();
- 
 
 68 
            csvBuilder.append(String.join(",", keys)).append("\n");
- 
 
 69 
        }
- 
 
 70 
 
- 
 
 71 
        for (Map<String, Object> row : data) {
- 
 
 72 
            int i = 0;
- 
 
 73 
            for (Object value : row.values()) {
- 
 
 74 
                String cell = value != null ? value.toString().replaceAll(",", " ") : "";
- 
 
 75 
                csvBuilder.append(cell);
- 
 
 76 
                if (++i < row.size()) csvBuilder.append(",");
- 
 
 77 
            }
- 
 
 78 
            csvBuilder.append("\n");
- 
 
 79 
        }
- 
 
 80 
 
- 
 
 81 
        Path tempFile = Files.createTempFile("query-result-", ".csv");
- 
 
 82 
        Files.write(tempFile, csvBuilder.toString().getBytes(StandardCharsets.UTF_8));
- 
 
 83 
        return tempFile.toFile();
- 
 
 84 
    }
56 
}
 85 
}
57 
 
 86