1 package net.sumaris.core.extraction.dao.technical.csv;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25 import au.com.bytecode.opencsv.CSVWriter;
26 import au.com.bytecode.opencsv.ResultSetHelperService;
27 import net.sumaris.core.dao.technical.Daos;
28 import net.sumaris.core.extraction.dao.technical.ExtractionBaseDaoImpl;
29 import org.apache.commons.lang3.ArrayUtils;
30 import org.slf4j.Logger;
31 import org.slf4j.LoggerFactory;
32 import org.springframework.beans.factory.annotation.Autowired;
33 import org.springframework.context.annotation.Lazy;
34 import org.springframework.dao.DataAccessResourceFailureException;
35 import org.springframework.jdbc.core.ResultSetExtractor;
36 import org.springframework.jdbc.datasource.DataSourceUtils;
37 import org.springframework.stereotype.Repository;
38
39 import javax.sql.DataSource;
40 import java.io.*;
41 import java.math.BigDecimal;
42 import java.nio.charset.StandardCharsets;
43 import java.sql.*;
44 import java.text.DecimalFormat;
45 import java.util.*;
46
47
48
49
50
51 @Repository("extractionCsvDao")
52 @Lazy
53 public class ExtractionCsvDaoImpl extends ExtractionBaseDaoImpl implements ExtractionCsvDao {
54
55 private static final Logger log = LoggerFactory.getLogger(ExtractionCsvDaoImpl.class);
56
57 @Autowired
58 private DataSource dataSource;
59
60 @Autowired
61 public ExtractionCsvDaoImpl() {
62 super();
63 }
64
65 @Override
66 public void dumpQueryToCSV(File file, String query,
67 Map<String, String> aliasByColumnMap,
68 Map<String, String> dateFormatsByColumnMap,
69 Map<String, String> decimalFormatsByColumnMap,
70 Set<String> excludeColumnNames) throws IOException {
71
72
73 Writer fileWriter = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file), StandardCharsets.UTF_8));
74
75
76 fileWriter.write(UTF8_BOM);
77
78
79 CSVWriter csvWriter = new CSVWriter(fileWriter, configuration.getCsvSeparator().charAt(0));
80
81
82 queryAllowEmptyResultSet(
83 query,
84 new CsvResultSetExtractor(csvWriter, true, aliasByColumnMap, dateFormatsByColumnMap, decimalFormatsByColumnMap, excludeColumnNames));
85
86
87 csvWriter.flush();
88 csvWriter.close();
89
90 }
91
92
93
94 private void queryAllowEmptyResultSet(String query, CsvResultSetExtractor csvResultSetExtractor) {
95
96 Connection connection = DataSourceUtils.getConnection(dataSource);
97 PreparedStatement statement = null;
98 ResultSet rs = null;
99 try {
100 statement = Daos.prepareQuery(connection, query);
101 rs = statement.executeQuery();
102 csvResultSetExtractor.extractData(rs);
103 } catch (SQLException e) {
104 throw new DataAccessResourceFailureException(String.format("Error while executing query [%s]: %s", query, e.getMessage()), e);
105 } finally {
106 Daos.closeSilently(rs);
107 Daos.closeSilently(statement);
108 DataSourceUtils.releaseConnection(connection, dataSource);
109 }
110 }
111
112 private class CsvResultSetExtractor implements ResultSetExtractor<CSVWriter> {
113
114 private static final boolean DEFAULT_TRIM = false;
115 private static final boolean DEFAULT_CSV_APPLY_QUOTES_TO_ALL = false;
116 private final CSVWriter writer;
117 private final boolean showColumnHeaders;
118 private final CsvResultSetHelperService helperService;
119
120 CsvResultSetExtractor(CSVWriter csvWriter, boolean showColumnHeaders,
121 Map<String, String> aliasByColumnMap,
122 Map<String, String> dateFormatsByColumnMap,
123 Map<String, String> decimalFormatsByColumnMap,
124 Set<String> excludeColumnNames) {
125 writer = csvWriter;
126 helperService = new CsvResultSetHelperService(aliasByColumnMap, dateFormatsByColumnMap, decimalFormatsByColumnMap, excludeColumnNames);
127 writer.setResultService(helperService);
128 this.showColumnHeaders = showColumnHeaders;
129 }
130
131 @Override
132 public CSVWriter extractData(ResultSet rs) throws SQLException {
133 try {
134 writer.writeAll(rs, showColumnHeaders, DEFAULT_TRIM, DEFAULT_CSV_APPLY_QUOTES_TO_ALL);
135 if (log.isDebugEnabled()) {
136 log.debug(String.format("%s rows written", helperService.getNbRowsWritten()));
137 }
138 } catch (IOException e) {
139 log.error(e.getLocalizedMessage());
140 }
141 return writer;
142 }
143 }
144
145 private class CsvResultSetHelperService extends ResultSetHelperService {
146
147 private final Map<String, String> aliasByColumnMap;
148 private final Map<String, String> dateFormatsByColumnMap;
149 private final Map<String, String> decimalFormatsByColumnMap;
150 private final Map<String, DecimalFormat> decimalFormatsCache;
151 private final Set<String> excludeColumnNames;
152 private final Set<Integer> excludeColumnIndexes;
153 private int nbRowsWritten = 0;
154
155 private CsvResultSetHelperService(Map<String, String> aliasByColumnMap,
156 Map<String, String> dateFormatsByColumnMap,
157 Map<String, String> decimalFormatsByColumnMap,
158 Set<String> excludeColumnNames) {
159 this.aliasByColumnMap = aliasByColumnMap;
160 this.dateFormatsByColumnMap = dateFormatsByColumnMap;
161 this.decimalFormatsByColumnMap = decimalFormatsByColumnMap;
162 this.decimalFormatsCache = new HashMap<>();
163 this.excludeColumnNames = excludeColumnNames;
164 this.excludeColumnIndexes = new TreeSet<>(Comparator.reverseOrder());
165 }
166
167 int getNbRowsWritten() {
168 return nbRowsWritten;
169 }
170
171 @Override
172 public String[] getColumnNames(ResultSet rs) throws SQLException {
173
174 ResultSetMetaData meta = rs.getMetaData();
175 List<String> names = new ArrayList<>();
176 for (int i = 0; i < meta.getColumnCount(); i++) {
177 String columnName = meta.getColumnLabel(i + 1);
178 if (excludeColumnNames != null && excludeColumnNames.contains(columnName)) {
179 excludeColumnIndexes.add(i);
180 continue;
181 }
182 if (aliasByColumnMap != null && aliasByColumnMap.containsKey(columnName)) {
183 columnName = aliasByColumnMap.get(columnName);
184 }
185 names.add(columnName);
186 }
187 return names.toArray(new String[0]);
188 }
189
190 @Override
191 public String[] getColumnValues(ResultSet rs, boolean trim, String dateFormatString, String timeFormatString) throws SQLException, IOException {
192
193 nbRowsWritten++;
194 String[] values = super.getColumnValues(rs, trim, dateFormatString, timeFormatString);
195
196 for (Integer index : excludeColumnIndexes)
197 values = ArrayUtils.remove(values, index);
198
199 return values;
200 }
201
202 @Override
203 protected String handleBigDecimal(ResultSet rs, int columnIndex) throws SQLException {
204
205
206 BigDecimal value = rs.getBigDecimal(columnIndex);
207 if (value == null) return "";
208
209
210 value = new BigDecimal(String.valueOf(rs.getFloat(columnIndex)));
211
212
213 String columnName = rs.getMetaData().getColumnLabel(columnIndex);
214 if (decimalFormatsCache.containsKey(columnName)) {
215 return decimalFormatsCache.get(columnName).format(value);
216 }
217 if (decimalFormatsByColumnMap != null && decimalFormatsByColumnMap.containsKey(columnName)) {
218 DecimalFormat decimalFormat = new DecimalFormat(decimalFormatsByColumnMap.get(columnName));
219 decimalFormatsCache.put(columnName, decimalFormat);
220 return decimalFormat.format(value);
221 }
222
223 return value.toPlainString();
224 }
225
226 @Override
227 protected String handleDate(ResultSet rs, int columnIndex, String dateFormatString) throws SQLException {
228
229 String columnName = rs.getMetaData().getColumnLabel(columnIndex);
230 if (dateFormatsByColumnMap != null && dateFormatsByColumnMap.containsKey(columnName)) {
231 return super.handleDate(rs, columnIndex, dateFormatsByColumnMap.get(columnName));
232 }
233 return super.handleDate(rs, columnIndex, dateFormatString);
234 }
235
236 @Override
237 protected String handleTimestamp(ResultSet rs, int columnIndex, String timestampFormatString) throws SQLException {
238
239 String columnName = rs.getMetaData().getColumnLabel(columnIndex);
240 if (dateFormatsByColumnMap != null && dateFormatsByColumnMap.containsKey(columnName)) {
241 return super.handleTimestamp(rs, columnIndex, dateFormatsByColumnMap.get(columnName));
242 }
243 return super.handleTimestamp(rs, columnIndex, timestampFormatString);
244 }
245 }
246 }