|
1
18 package sync4j.server.admin;
19
20 import java.util.logging.Logger  ;
21 import java.util.ArrayList ;
22
23 import java.sql.Connection ;
24 import java.sql.PreparedStatement ;
25 import java.sql.ResultSet ;
26 import java.sql.SQLException ;
27
28 import javax.naming.InitialContext ;
29 import javax.naming.NameNotFoundException ;
30 import javax.naming.NamingException ;
31
32 import javax.sql.DataSource ;
33
34 import sync4j.framework.logging.Sync4jLogger;
35 import sync4j.framework.tools.DBTools;
36 import sync4j.framework.server.Sync4jUser;
37 import sync4j.framework.server.store.Clause;
38 import sync4j.framework.server.store.PreparedWhere;
39 import sync4j.framework.server.store.PersistentStoreException;
40
41
49 public class DBUserManager implements UserManager, java.io.Serializable {
50
51 public final static int SQL_SELECT_ROLES = 0;
53 public final static int SQL_SELECT_USERS = 1;
54 public final static int SQL_SELECT_USER_ROLES = 2;
55 public final static int SQL_UPDATE_USER = 3;
56 public final static int SQL_INSERT_USER = 4;
57 public final static int SQL_DELETE_USER = 5;
58 public final static int SQL_DELETE_USER_ROLES = 6;
59 public final static int SQL_INSERT_USER_ROLES = 7;
60 public final static int SQL_DELETE_PRINCIPAL = 8;
61 public final static int SQL_SELECT_USER_PRINCIPALS = 9;
62 public final static int SQL_DELETE_CLIENT_MAPPING = 10;
63 public final static int SQL_DELETE_LAST_SYNC = 11;
64 public final static int SQL_GET_USER = 12;
65
66 public final static String SQL_COUNT_USERS =
67 "select count(*) as users from sync4j_user ";
68
69 public static final String LOG_NAME = "admin";
70
71 private String [] sql = null;
73
74 public void setSql(String [] sql) {
75 this.sql = sql;
76 }
77
78 public String [] getSql() {
79 return this.sql;
80 }
81
82
85 private String jndiDataSourceName = null;
86
87 public String getJndiDataSourceName() {
88 return this.jndiDataSourceName;
89 }
90
91 public void setJndiDataSourceName(String jndiDataSourceName) throws PersistentStoreException {
92 this.jndiDataSourceName = jndiDataSourceName;
93
94 if (jndiDataSourceName == null) {
95 ds = null;
96 }
97
98 try {
103 InitialContext ctx = new InitialContext ();
104 try {
105 ds = (DataSource ) ctx.lookup(jndiDataSourceName);
106 } catch (NameNotFoundException e) {
107 if (jndiDataSourceName.startsWith("java:/")) {
108 jndiDataSourceName = (jndiDataSourceName.length()>6)
109 ? jndiDataSourceName.substring(6)
110 : jndiDataSourceName
111 ;
112 }
113
114 ds = (DataSource ) ctx.lookup("java:/comp/env/" + jndiDataSourceName);
115 }
116 } catch (NamingException e) {
117 throw new PersistentStoreException("Data source "
118 + jndiDataSourceName
119 + " not found"
120 , e
121 );
122 }
123 }
124
125 protected transient DataSource ds = null;
127
128
130
136 public String [] getRoles() throws PersistentStoreException {
137 Connection conn = null;
138 PreparedStatement stmt = null;
139 ResultSet rs = null;
140
141 ArrayList roles = new ArrayList ();
142 try {
143 conn = ds.getConnection();
144
145 stmt = conn.prepareStatement(sql[SQL_SELECT_ROLES]);
146
147 rs = stmt.executeQuery();
148 while (rs.next()) {
149 roles.add(rs.getString(1) + ' ' + rs.getString(2));
151 }
152 return (String [])roles.toArray(new String [roles.size()]);
153
154 } catch (SQLException e) {
155 throw new PersistentStoreException("Error reading roles ", e);
156 } finally {
157 DBTools.close(conn, stmt, rs);
158 }
159 }
160
161 public Sync4jUser[] getUsers(Clause clause) throws PersistentStoreException {
162 PreparedWhere pw = clause.getPreparedWhere();
163
164 Connection conn = null;
165 PreparedStatement stmt = null;
166 ResultSet rs = null;
167
168 ArrayList users = new ArrayList ();
169 try {
170 conn = ds.getConnection();
171
172 String query = sql[SQL_SELECT_USERS];
173 if (pw.sql.length() > 0) {
174 query += " where " + pw.sql;
175 }
176
177 stmt = conn.prepareStatement(query);
178
179 for(int i=0; i<pw.parameters.length; ++i) {
180 stmt.setObject(i+1, pw.parameters[i]);
181 }
182
183 rs = stmt.executeQuery();
184 while (rs.next()) {
185 users.add(
186 new Sync4jUser(
187 rs.getString(1),
188 rs.getString(2),
189 rs.getString(3),
190 rs.getString(4),
191 rs.getString(5),
192 null
193 )
194 );
195 }
196 return (Sync4jUser[])users.toArray(new Sync4jUser[users.size()]);
197
198 } catch (SQLException e) {
199 throw new PersistentStoreException("Error reading roles ", e);
200 } finally {
201 DBTools.close(conn, stmt, rs);
202 }
203 }
204
205 public void getUser(Sync4jUser user) throws PersistentStoreException {
206
207 assert (ds !=null) : "ds is null! data source not properly configured";
208
209 Connection conn = null;
210 PreparedStatement stmt = null;
211 ResultSet rs = null;
212
213 try {
214 conn = ds.getConnection();
215
216 stmt = conn.prepareStatement(sql[SQL_GET_USER]);
217 stmt.setString(1, user.getUsername());
218
219 rs = stmt.executeQuery();
220
221 while (rs.next()) {
222 user.setUsername (rs.getString(1));
223 user.setEmail (rs.getString(2));
224 user.setFirstname(rs.getString(3));
225 user.setLastname (rs.getString(4));
226 user.setPassword (rs.getString(5));
227 }
228
229 } catch (SQLException e) {
230 throw new PersistentStoreException("Error reading user ", e);
231 } finally {
232 DBTools.close(conn, stmt, rs);
233 }
234 }
235
236 public void getUserRoles(Sync4jUser user) throws PersistentStoreException {
237 Connection conn = null;
238 PreparedStatement stmt = null;
239 ResultSet rs = null;
240
241 ArrayList ret = new ArrayList ();
242 try {
243 conn = ds.getConnection();
244
245 stmt = conn.prepareStatement(sql[SQL_SELECT_USER_ROLES]);
246 stmt.setString(1, user.getUsername());
247
248 rs = stmt.executeQuery();
249
250 while (rs.next()) {
251 ret.add(rs.getString(1));
252 }
253
254 user.setRoles((String [])ret.toArray(new String [ret.size()]));
255
256 } catch (SQLException e) {
257 throw new PersistentStoreException("Error reading roles ", e);
258 } finally {
259 DBTools.close(conn, stmt, rs);
260 }
261 }
262
263 public void setUser(Sync4jUser user) throws PersistentStoreException {
264 Connection conn = null;
265 PreparedStatement stmt = null;
266
267 try {
268 conn = ds.getConnection();
269
270 stmt = conn.prepareStatement(sql[SQL_UPDATE_USER]);
271 stmt.setString(1, user.getPassword());
272 stmt.setString(2, user.getEmail());
273 stmt.setString(3, user.getFirstname());
274 stmt.setString(4, user.getLastname());
275 stmt.setString(5, user.getUsername());
276
277 stmt.executeUpdate();
278
279 stmt.close();
280
281 stmt = conn.prepareStatement(sql[SQL_DELETE_USER_ROLES]);
286
287 stmt.setString(1, user.getUsername());
288
289 stmt.executeUpdate();
290
291 stmt.close();
292
293 String [] roles = user.getRoles();
294 for (int i=0; i<roles.length; i++) {
295 stmt = conn.prepareStatement(sql[SQL_INSERT_USER_ROLES]);
296 stmt.setString(1, roles[i]);
297 stmt.setString(2, user.getUsername());
298
299 stmt.executeUpdate();
300 }
301
302
303 } catch (SQLException e) {
304 throw new PersistentStoreException("Error updating user " + user, e);
305 } finally {
306 DBTools.close(conn, stmt, null);
307 }
308 }
309
310 public void insertUser(Sync4jUser user) throws PersistentStoreException {
311 Connection conn = null;
312 PreparedStatement stmt = null;
313 int n = 0;
314
315 try {
316 conn = ds.getConnection();
317
318 stmt = conn.prepareStatement(sql[SQL_UPDATE_USER]);
319 stmt.setString(1, user.getPassword() );
320 stmt.setString(2, user.getEmail() );
321 stmt.setString(3, user.getFirstname());
322 stmt.setString(4, user.getLastname() );
323 stmt.setString(5, user.getUsername() );
324
325 n = stmt.executeUpdate();
326
327 if (n == 0) {
328
329 stmt = conn.prepareStatement(sql[SQL_INSERT_USER]);
330 stmt.setString(1, user.getUsername() );
331 stmt.setString(2, user.getPassword() );
332 stmt.setString(3, user.getEmail() );
333 stmt.setString(4, user.getFirstname());
334 stmt.setString(5, user.getLastname() );
335
336 stmt.executeUpdate();
337
338 stmt.close();
339
340 String [] roles = user.getRoles();
341 for (int i=0; i<roles.length; i++) {
342 stmt = conn.prepareStatement(sql[SQL_INSERT_USER_ROLES]);
343 stmt.setString(1, roles[i]);
344 stmt.setString(2, user.getUsername());
345
346 stmt.executeUpdate();
347 }
348 } else {
349 stmt.close();
350
351 stmt = conn.prepareStatement(sql[SQL_DELETE_USER_ROLES]);
356
357 stmt.setString(1, user.getUsername());
358
359 stmt.executeUpdate();
360
361 stmt.close();
362
363 String [] roles = user.getRoles();
364 for (int i=0; i<roles.length; i++) {
365 stmt = conn.prepareStatement(sql[SQL_INSERT_USER_ROLES]);
366 stmt.setString(1, roles[i]);
367 stmt.setString(2, user.getUsername());
368
369 stmt.executeUpdate();
370 }
371 }
372
373 } catch (SQLException e) {
374 throw new PersistentStoreException("Error inserting user " + user, e);
375 } finally {
376 DBTools.close(conn, stmt, null);
377 }
378 }
379
380 public void deleteUser(Sync4jUser user) throws PersistentStoreException {
381 Connection conn = null;
382 PreparedStatement stmt = null;
383
384 try {
385 conn = ds.getConnection();
386
387 stmt = conn.prepareStatement(sql[SQL_DELETE_USER]);
388
389 stmt.setString(1, user.getUsername());
390
391 stmt.executeUpdate();
392
393 stmt.close();
394
395 stmt = conn.prepareStatement(sql[SQL_DELETE_USER_ROLES]);
396
397 stmt.setString(1, user.getUsername());
398
399 stmt.executeUpdate();
400
401 stmt.close();
402
403 deletePrincipal(user);
405
406 } catch (SQLException e) {
407 throw new PersistentStoreException("Error deleting user " + user, e);
408 } finally {
409 DBTools.close(conn, stmt, null);
410 }
411 }
412
413 private void deletePrincipal(Sync4jUser user) throws PersistentStoreException {
414 Connection conn = null;
415 PreparedStatement stmt = null;
416 ResultSet rs = null;
417
418 try {
419
420 conn = ds.getConnection();
421
422 stmt = conn.prepareStatement(sql[SQL_SELECT_USER_PRINCIPALS]);
423
424 stmt.setString(1, user.getUsername());
425
426 rs = stmt.executeQuery();
427
428 while (rs.next()) {
429 String principal = rs.getString(1);
430
431 PreparedStatement stmtCM = conn.prepareStatement(sql[SQL_DELETE_CLIENT_MAPPING]);
432
433 stmtCM.setString(1, principal);
434
435 stmtCM.executeUpdate();
436 stmtCM.close();
437
438 PreparedStatement stmtLS = conn.prepareStatement(sql[SQL_DELETE_LAST_SYNC]);
439
440 stmtLS.setString(1, principal);
441
442 stmtLS.executeUpdate();
443 stmtLS.close();
444
445 }
446 stmt.close();
447
448 stmt = conn.prepareStatement(sql[SQL_DELETE_PRINCIPAL]);
449
450 stmt.setString(1, user.getUsername());
451
452 stmt.executeUpdate();
453
454
455 } catch (SQLException e) {
456 throw new PersistentStoreException("Error deleting principal ", e);
457 } finally {
458 DBTools.close(conn, stmt, rs);
459 }
460 }
461
462
467 public int countUsers(Clause clause) throws PersistentStoreException {
468 PreparedWhere where = clause.getPreparedWhere();
469
470 Connection conn = null;
471 PreparedStatement stmt = null;
472 ResultSet rs = null;
473 int n = 0;
474
475 try {
476 conn = ds.getConnection();
477
478 String query = SQL_COUNT_USERS;
479 if (where.sql.length() > 0) {
480 query += " where " + where.sql;
481 }
482 stmt = conn.prepareStatement(query);
483
484 for(int i=0; i<where.parameters.length; ++i) {
485 stmt.setObject(i+1, where.parameters[i]);
486 }
487
488 rs = stmt.executeQuery();
489 while (rs.next()) {
490 n = rs.getInt(1);
491 }
492 return n;
493
494 } catch (SQLException e) {
495 throw new PersistentStoreException("Error reading count users ", e);
496 } finally {
497 DBTools.close(conn, stmt, rs);
498 }
499 }
500
501 } |
Popular Tags |
Java API By Example, From Geeks To Geeks.