1 package net.avcompris.examples.users3.dao.impl;
2
3 import static com.google.common.base.Preconditions.checkNotNull;
4 import static net.avcompris.commons3.databeans.DataBeans.instantiate;
5
6 import java.io.IOException;
7 import java.sql.Connection;
8 import java.sql.PreparedStatement;
9 import java.sql.ResultSet;
10 import java.sql.SQLException;
11 import java.sql.SQLIntegrityConstraintViolationException;
12 import java.util.ConcurrentModificationException;
13
14 import javax.annotation.Nullable;
15 import javax.sql.DataSource;
16
17 import org.apache.commons.logging.Log;
18 import org.joda.time.DateTime;
19 import org.springframework.beans.factory.annotation.Autowired;
20 import org.springframework.beans.factory.annotation.Value;
21 import org.springframework.stereotype.Component;
22
23 import net.avcompris.commons.query.impl.SqlWhereClause;
24 import net.avcompris.commons3.dao.exception.DuplicateEntityException;
25 import net.avcompris.commons3.dao.impl.AbstractDaoInRDS;
26 import net.avcompris.commons3.utils.Clock;
27 import net.avcompris.commons3.utils.LogFactory;
28 import net.avcompris.examples.users3.dao.UserDto;
29 import net.avcompris.examples.users3.dao.UsersDao;
30 import net.avcompris.examples.users3.dao.UsersDto;
31 import net.avcompris.examples.users3.dao.UsersDtoQuery;
32 import net.avcompris.examples.users3.query.UserFiltering;
33
34 @Component
35 public final class UsersDaoInRDS extends AbstractDaoInRDS implements UsersDao {
36
37 private static final Log logger = LogFactory.getLog(UsersDaoInRDS.class);
38
39
40
41
42 private final String usersTableName;
43
44 private final String sessionsTableName;
45
46 @Autowired
47 public UsersDaoInRDS(
48 @Value("#{rds.dataSource}") final DataSource dataSource,
49 @Value("#{rds.tableNames.users}") final String tableName,
50 @Value("#{rds.tableNames.auth}") final String authTableName,
51 final Clock clock) {
52
53 super(dataSource, tableName, clock);
54
55 this.usersTableName = tableName;
56 this.sessionsTableName = authTableName + "_sessions";
57 }
58
59 @Override
60 public UsersDto getUsers(final UsersDtoQuery query) throws SQLException, IOException {
61
62 checkNotNull(query, "query");
63
64 final String sqlWhereClause = SqlWhereClause
65 .build(query.getFiltering(), UserFiltering.Field.class)
66 .getSQL(" WHERE");
67
68 final MutableUsersDto users = instantiate(MutableUsersDto.class)
69 .setSqlWhereClause(sqlWhereClause);
70
71 final String orderDirective = toSQLOrderByDirective(query.getSortBys());
72
73 final String limitClause = toSQLLimitClause(query.getStart(), query.getLimit());
74
75 final int total;
76
77 try (Connection cxn = getConnection()) {
78
79 try (PreparedStatement pstmt = cxn.prepareStatement("SELECT"
80 + " COUNT(1)"
81 + " FROM " + usersTableName
82 + sqlWhereClause)) {
83
84 try (ResultSet rs = pstmt.executeQuery()) {
85
86 if (rs.next()) {
87
88 total = getInt(rs, 1);
89
90 } else {
91
92 throw new IllegalStateException();
93 }
94 }
95 }
96
97 try (PreparedStatement pstmt = cxn.prepareStatement("SELECT"
98 + " username,"
99 + " rolename,"
100 + " preferred_lang,"
101 + " preferred_timezone,"
102 + " enabled,"
103 + " created_at,"
104 + " updated_at,"
105 + " last_active_at,"
106 + " revision"
107
108 + " FROM " + usersTableName
109 + sqlWhereClause
110 + orderDirective
111 + limitClause)) {
112
113 try (ResultSet rs = pstmt.executeQuery()) {
114
115 while (rs.next()) {
116
117 final UserDto user = resultSet2UserDto(rs);
118
119 users.addToResults(user);
120 }
121 }
122 }
123 }
124
125 return users.setTotal(total);
126 }
127
128 @Override
129 public void createUser(final String username,
130 final String rolename,
131 @Nullable final String preferredLang,
132 @Nullable final String preferredTimeZone,
133 final boolean enabled
134 ) throws SQLException, IOException, DuplicateEntityException {
135
136 checkNotNull(username, "username");
137 checkNotNull(rolename, "rolename");
138
139 final DateTime now = clock.now();
140
141 try (Connection cxn = getConnection()) {
142
143 try (PreparedStatement pstmt = cxn.prepareStatement("INSERT INTO " + tableName
144 + " (username,"
145 + " rolename,"
146 + " preferred_lang,"
147 + " preferred_timezone,"
148 + " enabled,"
149 + " created_at,"
150 + " updated_at,"
151 + " revision)"
152 + " VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
153 )) {
154
155 setString(pstmt, 1, username);
156 setString(pstmt, 2, rolename);
157 setString(pstmt, 3, preferredLang);
158 setString(pstmt, 4, preferredTimeZone);
159 setBoolean(pstmt, 5, enabled);
160 setDateTime(pstmt, 6, now);
161 setDateTime(pstmt, 7, now);
162 setInt(pstmt, 8, 1);
163
164 try {
165
166 pstmt.executeUpdate();
167
168 } catch (final SQLIntegrityConstraintViolationException e) {
169
170 throw new DuplicateEntityException("username: " + username, e);
171
172 } catch (final SQLException e) {
173
174 if (isPSQLUniqueViolation(e)) {
175
176 throw new DuplicateEntityException("username: " + username, e);
177 }
178
179 throw e;
180 }
181 }
182 }
183 }
184
185 @Override
186 @Nullable
187 public UserDto getUser(final String username) throws SQLException, IOException {
188
189 checkNotNull(username, "username");
190
191 try (Connection cxn = getConnection()) {
192
193 try (PreparedStatement pstmt = cxn.prepareStatement("SELECT"
194 + " username,"
195 + " rolename,"
196 + " preferred_lang,"
197 + " preferred_timezone,"
198 + " enabled,"
199 + " created_at,"
200 + " updated_at,"
201 + " revision,"
202
203 + "(SELECT MAX(updated_at)"
204 + " FROM " + sessionsTableName
205 + " WHERE username = ?"
206 + ") AS last_active_at"
207
208 + " FROM " + tableName
209 + " WHERE username = ?"
210 )) {
211
212 setString(pstmt, 1, username);
213 setString(pstmt, 2, username);
214
215 try (ResultSet rs = pstmt.executeQuery()) {
216
217 if (!rs.next()) {
218
219 return null;
220 }
221
222 return resultSet2UserDto(rs);
223 }
224 }
225 }
226 }
227
228 private static UserDto resultSet2UserDto(final ResultSet rs) throws SQLException {
229
230 return instantiate(MutableUserDto.class)
231 .setUsername(getString(rs, "username"))
232 .setRolename(getString(rs, "rolename"))
233 .setPreferredLang(getString(rs, "preferred_lang"))
234 .setPreferredTimeZone(getString(rs, "preferred_timezone"))
235 .setEnabled(getBoolean(rs, "enabled"))
236 .setCreatedAt(getDateTime(rs, "created_at"))
237 .setUpdatedAt(getDateTime(rs, "updated_at"))
238 .setLastActiveAt(getDateTime(rs, "last_active_at"))
239 .setRevision(getInt(rs, "revision"));
240 }
241
242 @Override
243 public void updateUser(final String username,
244 final String rolename,
245 @Nullable final String preferredLang,
246 @Nullable final String preferredTimeZone,
247 final boolean enabled,
248 final int fromRevision
249 ) throws SQLException, IOException {
250
251 checkNotNull(username, "username");
252 checkNotNull(rolename, "rolename");
253
254 final DateTime now = clock.now();
255
256 final int updated;
257
258 try (Connection cxn = getConnection()) {
259
260 cxn.setAutoCommit(false);
261
262 try (PreparedStatement pstmt = cxn.prepareStatement("UPDATE " + tableName
263 + " SET rolename = ?,"
264 + " enabled = ?,"
265 + " updated_at = ?,"
266 + " revision = ?"
267 + " WHERE username = ?"
268 + " AND revision = ?"
269 )) {
270
271 setString(pstmt, 1, rolename);
272 setBoolean(pstmt, 2, enabled);
273 setDateTime(pstmt, 3, now);
274 setInt(pstmt, 4, fromRevision + 1);
275 setString(pstmt, 5, username);
276 setInt(pstmt, 6, fromRevision);
277
278 updated = pstmt.executeUpdate();
279 }
280
281 if (updated != 1) {
282 throw new ConcurrentModificationException("username: " + username + ", fromRevision: " + fromRevision);
283 }
284
285 if (preferredLang != null) {
286
287 try (PreparedStatement pstmt = cxn.prepareStatement("UPDATE " + tableName
288 + " SET preferred_lang = ?"
289 + " WHERE username = ?"
290 + " AND revision = ?"
291 )) {
292
293 setString(pstmt, 1, preferredLang);
294 setString(pstmt, 2, username);
295 setInt(pstmt, 3, fromRevision + 1);
296
297 pstmt.executeUpdate();
298 }
299 }
300
301 if (preferredTimeZone != null) {
302
303 try (PreparedStatement pstmt = cxn.prepareStatement("UPDATE " + tableName
304 + " SET preferred_timezone = ?"
305 + " WHERE username = ?"
306 + " AND revision = ?"
307 )) {
308
309 setString(pstmt, 1, preferredTimeZone);
310 setString(pstmt, 2, username);
311 setInt(pstmt, 3, fromRevision + 1);
312
313 pstmt.executeUpdate();
314 }
315 }
316
317 cxn.commit();
318 }
319 }
320
321 @Override
322 public void deleteUser(final String username) throws SQLException, IOException {
323
324 checkNotNull(username, "username");
325
326 try (Connection cxn = getConnection()) {
327
328 try (PreparedStatement pstmt = cxn.prepareStatement("DELETE FROM " + tableName
329 + " WHERE username = ?"
330 )) {
331
332 setString(pstmt, 1, username);
333
334 pstmt.executeUpdate();
335 }
336 }
337 }
338
339 @Override
340 public void setLastActiveAt(final String username, final DateTime lastActiveAt) throws SQLException, IOException {
341
342 checkNotNull(username, "username");
343 checkNotNull(lastActiveAt, "lastActiveAt");
344
345 final long startMs = System.currentTimeMillis();
346
347 if (logger.isDebugEnabled()) {
348 logger.debug("setLastActiveAt(), username: " + username + "...");
349 }
350
351 try (Connection cxn = getConnection()) {
352
353 try (PreparedStatement pstmt = cxn.prepareStatement("UPDATE " + tableName
354 + " SET last_active_at = ?"
355 + " WHERE username = ?"
356 )) {
357
358 setDateTime(pstmt, 1, lastActiveAt);
359 setString(pstmt, 2, username);
360
361 pstmt.executeUpdate();
362 }
363 }
364
365 final long elapsedMs = System.currentTimeMillis() - startMs;
366
367 if (logger.isDebugEnabled()) {
368 logger.debug("setLastActiveAt(), elapsedMs: " + elapsedMs);
369 }
370 }
371 }