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