1 package net.avcompris.commons3.dao.impl;
2
3 import static com.google.common.base.Preconditions.checkArgument;
4 import static com.google.common.base.Preconditions.checkNotNull;
5 import static org.joda.time.DateTimeZone.UTC;
6
7 import java.io.IOException;
8 import java.io.InputStream;
9 import java.sql.Connection;
10 import java.sql.PreparedStatement;
11 import java.sql.ResultSet;
12 import java.sql.SQLException;
13 import java.sql.Timestamp;
14 import java.sql.Types;
15 import java.util.Calendar;
16 import java.util.TimeZone;
17
18 import javax.annotation.Nullable;
19 import javax.sql.DataSource;
20
21 import org.apache.commons.io.IOUtils;
22 import org.joda.time.DateTime;
23 import org.joda.time.DateTimeZone;
24
25 import net.avcompris.commons3.dao.EntitiesDtoQuery.SortBy;
26 import net.avcompris.commons3.utils.Clock;
27
28 public abstract class AbstractDaoInRDS extends AbstractDao {
29
30 protected static final String SCHEMA_VERSION_COLUMN_NAME = "SCHEMA_VERSION";
31
32 protected AbstractDaoInRDS(final DataSource dataSource, final String tableName, final Clock clock) {
33
34 super(clock);
35
36 this.dataSource = checkNotNull(dataSource, "dataSource");
37 this.tableName = checkNotNull(tableName, "tableName");
38 }
39
40 private final DataSource dataSource;
41 protected final String tableName;
42
43 protected final Connection getConnection() throws SQLException {
44
45 return dataSource.getConnection();
46 }
47
48 protected static final String toSQLOrderByDirective(final SortBy... sortBys) {
49
50 checkNotNull(sortBys, "sortBys");
51
52 if (sortBys.length == 0) {
53 return "";
54 }
55
56 final StringBuilder sb = new StringBuilder(" ORDER BY ");
57
58 boolean start = true;
59
60 for (final SortBy sortBy : sortBys) {
61
62 if (start) {
63 start = false;
64 } else {
65 sb.append(", ");
66 }
67
68 sb.append(sortBy.toSqlField());
69
70 if (sortBy.isDesc()) {
71 sb.append(" DESC");
72 }
73 }
74
75 return sb.toString();
76 }
77
78 protected static String toSQLLimitClause(final int start, final int limit) {
79
80 return " LIMIT " + limit + " OFFSET " + start;
81 }
82
83 @Nullable
84 protected static Integer getInteger(final ResultSet rs, final String columnName) throws SQLException {
85
86 checkNotNull(rs, "rs");
87 checkNotNull(columnName, "columnName");
88
89 final int value = rs.getInt(columnName);
90
91 return rs.wasNull() ? null : value;
92 }
93
94 @Nullable
95 protected static Integer getInteger(final ResultSet rs, final int columnIndex) throws SQLException {
96
97 checkNotNull(rs, "rs");
98
99 final int value = rs.getInt(columnIndex);
100
101 return rs.wasNull() ? null : value;
102 }
103
104 @Nullable
105 protected static Integer getInt(final ResultSet rs, final String columnName) throws SQLException {
106
107 checkNotNull(rs, "rs");
108 checkNotNull(columnName, "columnName");
109
110 return rs.getInt(columnName);
111 }
112
113 @Nullable
114 protected static Integer getInt(final ResultSet rs, final int columnIndex) throws SQLException {
115
116 checkNotNull(rs, "rs");
117
118 return rs.getInt(columnIndex);
119 }
120
121 @Nullable
122 protected static Long getLong(final ResultSet rs, final String columnName) throws SQLException {
123
124 checkNotNull(rs, "rs");
125 checkNotNull(columnName, "columnName");
126
127 final long value = rs.getLong(columnName);
128
129 return rs.wasNull() ? null : value;
130 }
131
132 @Nullable
133 protected static Long getLong(final ResultSet rs, final int columnIndex) throws SQLException {
134
135 checkNotNull(rs, "rs");
136
137 final long value = rs.getLong(columnIndex);
138
139 return rs.wasNull() ? null : value;
140 }
141
142 @Nullable
143 protected static DateTime getDateTime(final ResultSet rs, final String columnName) throws SQLException {
144
145 checkNotNull(rs, "rs");
146 checkNotNull(columnName, "columnName");
147
148 return getDateTime(rs.getTimestamp(columnName));
149 }
150
151 @Nullable
152 private static DateTime getDateTime(@Nullable final Timestamp timestamp) {
153
154 return timestamp == null ? null : new DateTime(timestamp.getTime()).withZone(UTC);
155 }
156
157 @Nullable
158 protected static DateTime getDateTime(final ResultSet rs, final int columnIndex) throws SQLException {
159
160 checkNotNull(rs, "rs");
161
162 return getDateTime(rs.getTimestamp(columnIndex));
163 }
164
165 @Nullable
166 protected static byte[] getBytes(final ResultSet rs, final String columnName) throws SQLException, IOException {
167
168 checkNotNull(rs, "rs");
169 checkNotNull(columnName, "columnName");
170
171 return getBytes(rs.getBinaryStream(columnName));
172 }
173
174 @Nullable
175 private static byte[] getBytes(@Nullable final InputStream is) throws IOException {
176
177 if (is == null) {
178
179 return null;
180 }
181
182 try {
183
184 return IOUtils.toByteArray(is);
185
186 } finally {
187
188 is.close();
189 }
190 }
191
192 @Nullable
193 protected static byte[] getBytes(final ResultSet rs, final int columnIndex) throws SQLException, IOException {
194
195 checkNotNull(rs, "rs");
196
197 return getBytes(rs.getBinaryStream(columnIndex));
198 }
199
200 @Nullable
201 protected static String getString(final ResultSet rs, final String columnName) throws SQLException {
202
203 checkNotNull(rs, "rs");
204 checkNotNull(columnName, "columnName");
205
206 return rs.getString(columnName);
207 }
208
209 @Nullable
210 protected static <T extends Enum<T>> T getEnum(final ResultSet rs, final int columnIndex, final Class<T> enumClass)
211 throws SQLException {
212
213 checkNotNull(rs, "rs");
214
215 return toEnum(rs.getString(columnIndex), enumClass);
216 }
217
218 @Nullable
219 protected static <T extends Enum<T>> T getEnum(final ResultSet rs, final String columnName,
220 final Class<T> enumClass) throws SQLException {
221
222 checkNotNull(rs, "rs");
223 checkNotNull(columnName, "columnName");
224
225 return toEnum(rs.getString(columnName), enumClass);
226 }
227
228 @Nullable
229 private static <T extends Enum<T>> T toEnum(@Nullable final String value, final Class<T> enumClass)
230 throws SQLException {
231
232 if (value == null) {
233
234 return null;
235 }
236
237 for (final T enumConstant : enumClass.getEnumConstants()) {
238
239 if (value.equals(enumConstant.name())) {
240
241 return enumConstant;
242 }
243 }
244
245 throw new IllegalArgumentException("Unknown enumConstant: " + value + ", for: " + enumClass.getName());
246 }
247
248 @Nullable
249 protected static String getString(final ResultSet rs, final int columnIndex) throws SQLException {
250
251 checkNotNull(rs, "rs");
252
253 return rs.getString(columnIndex);
254 }
255
256 @Nullable
257 protected static boolean getBoolean(final ResultSet rs, final String columnName) throws SQLException {
258
259 checkNotNull(rs, "rs");
260 checkNotNull(columnName, "columnName");
261
262 return rs.getBoolean(columnName);
263 }
264
265 @Nullable
266 protected static boolean getBoolean(final ResultSet rs, final int columnIndex) throws SQLException {
267
268 checkNotNull(rs, "rs");
269
270 return rs.getBoolean(columnIndex);
271 }
272
273 protected final void setString(final PreparedStatement pstmt, final int paramIndex, @Nullable final String s)
274 throws SQLException {
275
276 pstmt.setString(paramIndex, s);
277 }
278
279 protected final void setObject(final PreparedStatement pstmt, final int paramIndex, @Nullable final Object o)
280 throws SQLException {
281
282 pstmt.setObject(paramIndex, o);
283 }
284
285 protected final void setBinaryStream(final PreparedStatement pstmt, final int paramIndex,
286 @Nullable final InputStream is) throws SQLException {
287
288 pstmt.setBinaryStream(paramIndex, is);
289 }
290
291 protected final void setInteger(final PreparedStatement pstmt, final int paramIndex, @Nullable final Integer value)
292 throws SQLException {
293
294 if (value == null) {
295
296 pstmt.setNull(paramIndex, Types.INTEGER);
297
298 } else {
299
300 pstmt.setInt(paramIndex, value);
301 }
302 }
303
304 protected final void setInt(final PreparedStatement pstmt, final int paramIndex, final int value)
305 throws SQLException {
306
307 pstmt.setInt(paramIndex, value);
308 }
309
310 protected final void setDouble(final PreparedStatement pstmt, final int paramIndex, final double value)
311 throws SQLException {
312
313 pstmt.setDouble(paramIndex, value);
314 }
315
316 protected final void setBoolean(final PreparedStatement pstmt, final int paramIndex, final boolean value)
317 throws SQLException {
318
319 pstmt.setBoolean(paramIndex, value);
320 }
321
322 protected final void setBytes(final PreparedStatement pstmt, final int paramIndex, final byte[] bytes)
323 throws SQLException {
324
325 pstmt.setBytes(paramIndex, bytes);
326 }
327
328 public static final String PSQL_UNIQUE_VIOLATION = "23505";
329 public static final String PSQL_FOREIGN_ENTITY_VIOLATION = "23503";
330
331 public static boolean isPSQLUniqueViolation(@Nullable final SQLException e) {
332
333 return e != null && PSQL_UNIQUE_VIOLATION.contentEquals(e.getSQLState());
334 }
335
336 public static boolean isPSQLForeignEntityViolation(@Nullable final SQLException e) {
337
338 return e != null && PSQL_FOREIGN_ENTITY_VIOLATION.contentEquals(e.getSQLState());
339 }
340
341 protected final void setLong(final PreparedStatement pstmt, final int paramIndex, @Nullable final Long value)
342 throws SQLException {
343
344 if (value == null) {
345
346 pstmt.setNull(paramIndex, Types.INTEGER);
347
348 } else {
349
350 pstmt.setInt(paramIndex, value.intValue());
351 }
352 }
353
354 private static final Calendar UTC_CALENDAR = Calendar.getInstance(TimeZone.getTimeZone(DateTimeZone.UTC.getID()));
355
356 protected final void setDateTime(final PreparedStatement pstmt, final int paramIndex,
357 @Nullable final DateTime dateTime) throws SQLException {
358
359 if (dateTime == null) {
360
361 pstmt.setNull(paramIndex, Types.TIMESTAMP);
362
363 } else {
364
365 final DateTime withUTC = dateTime.withZone(UTC);
366
367 pstmt.setTimestamp(paramIndex, new Timestamp(withUTC.getMillis()), UTC_CALENDAR);
368 }
369 }
370
371 @Nullable
372 protected static String joinRefIds(@Nullable final String... refIds) {
373
374 if (refIds == null) {
375
376 return null;
377 }
378
379 final StringBuilder sb = new StringBuilder("|");
380
381 for (final String refId : refIds) {
382
383 if (refId == null) {
384 continue;
385 }
386
387 checkArgument(!refId.contains("|"),
388 "refId should not contain \"|\", but was: %s", refId);
389
390 sb.append(refId).append("|");
391 }
392
393 return sb.toString();
394 }
395 }