/
SqlBaseDao.java
304 lines (272 loc) · 12.6 KB
/
SqlBaseDao.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
package gov.nysenate.openleg.dao.base;
import com.google.common.base.Splitter;
import com.google.common.collect.Range;
import gov.nysenate.openleg.config.Environment;
import gov.nysenate.openleg.model.base.BaseLegislativeContent;
import gov.nysenate.openleg.model.base.SessionYear;
import gov.nysenate.openleg.model.sourcefiles.LegDataFragment;
import gov.nysenate.openleg.model.updates.UpdateType;
import gov.nysenate.openleg.util.DateUtils;
import org.apache.commons.text.StringSubstitutor;
import org.postgresql.util.PGInterval;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.util.StringUtils;
import javax.annotation.PostConstruct;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.*;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeParseException;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import static gov.nysenate.openleg.util.DateUtils.toDate;
/**
* Base class for SQL data access layer classes to inherit common functionality from.
*/
public abstract class SqlBaseDao
{
private static final Logger logger = LoggerFactory.getLogger(SqlBaseDao.class);
/** JdbcTemplate reference for use by sub classes to execute SQL queries */
@Autowired protected JdbcTemplate jdbc;
/** Similar to JdbcTemplate but forces the use of named query parameter for readability. */
@Autowired protected NamedParameterJdbcTemplate jdbcNamed;
/** Reference to the environment in which the data is stored */
@Autowired protected Environment environment;
@PostConstruct
private void init() {}
/** --- Common Param Methods --- */
/**
* Returns the schema of the environment instance.
*/
protected String schema() {
if (environment == null) {
throw new IllegalStateException("The environment has not been initialized. Cannot perform SQL queries " +
"since we can't determine which database schema to operate on.");
}
return environment.getSchema();
}
/**
* Applies the 'last LegDataFragment id' column value. Useful for tracking which sobiFragment
* serves as the source data for the update.
*/
protected static void addLastFragmentParam(LegDataFragment fragment, MapSqlParameterSource params) {
params.addValue("lastFragmentId", (fragment != null) ? fragment.getFragmentId() : null);
}
/**
* Adds parameters for a date time range
*/
protected static void addDateTimeRangeParams(MapSqlParameterSource params, Range<LocalDateTime> dateTimeRange) {
params.addValue("startDateTime", DateUtils.toDate(DateUtils.startOfDateTimeRange(dateTimeRange)));
params.addValue("endDateTime", DateUtils.toDate(DateUtils.endOfDateTimeRange(dateTimeRange)));
}
/**
* Gets a new parameter map containing params for the given date time range
*/
protected static MapSqlParameterSource getDateTimeRangeParams(Range<LocalDateTime> dateTimeRange) {
MapSqlParameterSource params = new MapSqlParameterSource();
addDateTimeRangeParams(params, dateTimeRange);
return params;
}
/**
* Applies the published date / modified date column values.
*/
protected static void addModPubDateParams(LocalDateTime modifiedDate, LocalDateTime publishedDate, MapSqlParameterSource params) {
params.addValue("modifiedDateTime", toDate(modifiedDate));
params.addValue("publishedDateTime", toDate(publishedDate));
}
/**
* Convenience method for setting the modified and published date time via the default columns
* in the result set. Use this method only when the result set is guaranteed to have these
* default columns.
*/
protected static void setModPubDatesFromResultSet(BaseLegislativeContent obj, ResultSet rs) throws SQLException {
obj.setModifiedDateTime(getLocalDateTimeFromRs(rs, "modified_date_time"));
obj.setPublishedDateTime(DateUtils.getLocalDateTime(rs.getTimestamp("published_date_time")));
}
/**
* Returns a new string where the substitution key 'e.g. ${insertWhereClause}' is replaced with the
* given replacement string.
*/
protected static String queryReplace(String originalQuery, String key, String replacement) {
Map<String, String> replaceMap = new HashMap<>();
replaceMap.put(key, replacement);
return new StringSubstitutor(replaceMap).replace(originalQuery);
}
/** --- PostgreSQL Hstore handling methods --- */
/**
* Converts the output of hstore_to_array(column) to a mapping of the hstore key/val pairs.
* For example if you have an hstore value 'a=>1, b=>2', to retrieve a Map {a=1, b=2} have the
* sql query return hstore_to_array(column) and feed the result set to this method.
*/
public static Map<String, String> getHstoreMap(ResultSet rs, String column) throws SQLException {
String[] hstoreArr = (String[]) rs.getArray(column).getArray();
Map<String, String> hstoreMap = new HashMap<>();
String key = "";
for (int i = 0; i < hstoreArr.length; i++) {
if (i % 2 == 0) {
key = hstoreArr[i];
}
else {
hstoreMap.put(key, hstoreArr[i]);
}
}
return hstoreMap;
}
/**
* Converts a hstore string into a mapping of the hstore key value pairs.
* FIXME This method seems to have the potential to alter hstore values that contain commas
* FIXME e.g. "Veterans, Homeland Security and Military Affairs" becomes "Veterans,Homeland Security and Military Affairs"
* FIXME note missing space after comma
* @param hstoreString a String in the format of "print_no"=>"S100", "session_year"=>"2015".
* This string can be retrieved by calling resultSet.getString("hstore")
* on the ResultSet from "SELECT 'print_no=>S100,session_year=>2015'::hstore as hstore"
* @return A map containing all hstore key value pairs.
*/
public static Map<String, String> hstoreStringToMap(String hstoreString) {
Map<String, String> hstoreMap = new HashMap<>();
hstoreString = StringUtils.replace(hstoreString, "\"", "");
String[] hstoreEntry = hstoreString.contains(",") ? StringUtils.commaDelimitedListToStringArray(hstoreString) : new String[]{hstoreString};
String key="";
String value="";
for (int i = 0; i < hstoreEntry.length; i++) {
if (hstoreEntry[i].contains("=>")){
key = StringUtils.trimLeadingWhitespace(StringUtils.split(hstoreEntry[i], "=>")[0]);
value = StringUtils.trimLeadingWhitespace(StringUtils.split(hstoreEntry[i], "=>")[1]);
hstoreMap.put(key, value);
}
else {
hstoreMap.remove(key);
value +=","+StringUtils.trimLeadingWhitespace(hstoreEntry[i]);
hstoreMap.put(key,value);
}
}
return hstoreMap;
}
/**
* Converts the given map into the hstore string format (i.e. 'key1=>val1, key2=>val2, etc')
*/
public static String toHstoreString(Map<String, String> hstoreMap) {
return hstoreMap.entrySet().stream()
.map(kv -> kv.getKey() + "=>" +
(kv.getValue() == null ? "NULL"
: StringUtils.isEmpty(kv.getValue()) ? "\"\""
: kv.getValue().replaceAll("([,=> ])", "\\\\$1").replaceAll("'", "''")))
.collect(Collectors.joining(","));
}
/** --- Update Dao Methods --- */
protected String getDateColumnForUpdateType(UpdateType updateType) {
String dateColumn;
if (updateType.equals(UpdateType.PROCESSED_DATE)) {
dateColumn = "action_date_time";
}
else if (updateType.equals(UpdateType.PUBLISHED_DATE)) {
dateColumn = "published_date_time";
}
else {
throw new IllegalArgumentException("Cannot provide updates of type: " + updateType);
}
return dateColumn;
}
protected OrderBy getOrderByForUpdateType(UpdateType updateType, SortOrder sortOrder) {
OrderBy orderBy;
if (updateType.equals(UpdateType.PROCESSED_DATE)) {
orderBy = new OrderBy("last_processed_date_time", sortOrder);
}
else if (updateType.equals(UpdateType.PUBLISHED_DATE)) {
orderBy = new OrderBy("last_published_date_time", sortOrder, "last_processed_date_time", sortOrder);
}
else {
throw new IllegalArgumentException("Cannot provide updates of type: " + updateType);
}
return orderBy;
}
/** --- Date Methods -- */
/**
* Given a sobi fragment id, parse out the date/time. Returns null if the fragment id has a different pattern
* than usual..
*
* @param fragmentId String
* @return LocalDateTime
*/
public static LocalDateTime getLocalDateTimeFromLegDataFragmentId(String fragmentId) {
if (fragmentId != null && !fragmentId.isEmpty()) {
List<String> parts = Splitter.on(".").splitToList(fragmentId);
if (parts.size() == 4) {
try {
return LocalDateTime.parse(parts.get(1).substring(1) + parts.get(2).substring(1),
DateTimeFormatter.ofPattern("yyMMddHHmmss"));
}
catch (DateTimeParseException ex) {
logger.warn("Failed to parse date time from leg data fragment {}", fragmentId, ex);
}
}
}
return null;
}
/**
* Read the 'column' date value from the result set and cast it to a LocalDateTime.
* Return null if the column value is null.
*/
public static LocalDateTime getLocalDateTimeFromRs(ResultSet rs, String column) throws SQLException {
if (rs.getTimestamp(column) == null) return null;
return rs.getTimestamp(column).toLocalDateTime();
}
/**
* Read the 'column' date value from the result set and cast it to a LocalDate.
* Return null if the column value is null.
*/
public static LocalDate getLocalDateFromRs(ResultSet rs, String column) throws SQLException {
if (rs.getDate(column) == null) return null;
return rs.getDate(column).toLocalDate();
}
/**
* Read the 'column' time value from the result set and cast it to a LocalTime.
* Return null if the column value is null.
*/
public static LocalTime getLocalTimeFromRs(ResultSet rs, String column) throws SQLException {
if (rs.getTime(column) == null) return null;
return rs.getTime(column).toLocalTime();
}
/**
* Read the 'column' interval value from the result set and cast it to a Period.
* Return null if the column value is null.
*/
public static Period getPeriodFromRs(ResultSet rs, String column) throws SQLException {
PGInterval interval = (PGInterval) rs.getObject(column);
return interval != null ? Period.of(interval.getYears(), interval.getMonths(), interval.getDays()) : null;
}
/**
* Read the 'column' interval value from the result set and cast it to a Duration.
* Values beyond a day are ignored due to variable length of months/years
* Return null if the column value is null.
*/
public static Duration getDurationFromRs(ResultSet rs, String column) throws SQLException {
PGInterval interval = (PGInterval) rs.getObject(column);
return interval != null
? Duration.ofMillis((long) (interval.getSeconds() * 1000) +
interval.getMinutes() * 1000 * 60 +
interval.getHours() * 1000 * 60 * 60 +
interval.getDays() * 1000 * 60 * 60 * 24)
: null;
}
/**
* Read the 'column' int value from the result set and return a new SessionYear instance.
*/
public static SessionYear getSessionYearFromRs(ResultSet rs, String column) throws SQLException {
return new SessionYear(rs.getInt(column));
}
public static String toPostgresArray(Collection<?> objects) {
String commaSeparatedList = objects.stream()
.map(String::valueOf)
.collect(Collectors.joining(","));
return "{" + commaSeparatedList + "}";
}
}