AccountDBUtils


1   //
2   //                                   ____.
3   //                       __/\ ______|    |__/\.     _______
4   //            __   .____|    |       \   |    +----+       \
5   //    _______|  /--|    |    |    -   \  _    |    :    -   \_________
6   //    \______: :---|    :    :           |    :    |         \________>
7   //           |__\---\_____________:______:    :____|____:_____\
8   //                                      /_____|
9   //
10  //                 . . . i n   j a h i a   w e   t r u s t . . .
11  //
12  
13  package org.jahia.security.accounts;
14  
15  import java.sql.Connection  ;
16  import java.sql.ResultSet  ;
17  import java.sql.SQLException  ;
18  import java.sql.Statement  ;
19  import java.util.Date  ;
20  
21  import org.jahia.exceptions.database.JahiaDatabaseException;
22  import org.jahia.services.sites.SitesDBInterface;
23  
24  /**
25   * This class is used inside the account service and should not be used directly
26   * for any account opertion. This class implements the singelton design pattern.
27   *
28   * @author  Fulco Houkes
29   * @version 1.0
30   */
31  class AccountDBUtils implements AccountsDBInterface, SitesDBInterface
32  {
33  
34      /** reference to the unique class instance */
35      private static AccountDBUtils mInstance = null;
36  
37      /** null database date string */
38      private static final String   NULL_STRING = "-null-";
39  
40  
41      //-------------------------------------------------------------------------
42      // Foux     17 Apr. 2001
43      /** Default constructor
44       */
45      private AccountDBUtils () {
46      }
47  
48      //-------------------------------------------------------------------------
49      // Foux     17 Apr. 2001
50      /** Return the unique instance of this class.
51       *
52       * @return
53       *      Return the unique instance of this class.
54       */
55      static public AccountDBUtils getInstance () {
56          if (mInstance == null) {
57              mInstance = new AccountDBUtils();
58          }
59          return mInstance;
60      }
61  
62  
63      //-------------------------------------------------------------------------
64      // Foux     17 Apr. 2001
65      /** Delete the specified user account from the database.
66       *
67       * @param   account
68       *      Reference to the account object to be deleted.
69       *
70       * @return
71       *      Return <code>true</code> if the specified user account could be
72       *      deleted successfully from the database.
73       *      Return <code>false</code> if the speficied account is
74       *      <code>null</code> or on any other error.
75       *
76       * @exception   JahiaDatabaseException
77       *      Throws this exception on any database failure.
78       */
79      public synchronized boolean deleteAccount (Account account)
80          throws  JahiaDatabaseException
81      {
82          if (account == null) {
83              return false;
84          }
85  
86          // Get the database connection.
87          Connection   connection = org.jahia.services.database.ConnectionDispenser.getConnection();
88          if (connection == null) {
89              throw new JahiaDatabaseException (
90                      "Account deletion process could not get a connection.",
91                      JahiaDatabaseException.CRITICAL_SEVERITY);
92          }
93  
94          boolean result = false;
95          Statement   statement = null;
96          StringBuffer   query = new StringBuffer   ("");
97          try {
98              // composes the query
99              query.append ("DELETE FROM ");
100             query.append (JAHIA_ACCOUNTS);
101             query.append (" WHERE ");
102             query.append (FIELD_ACCOUNT_USER_KEY);
103             query.append ("='");
104             query.append (account.getUserKey());
105             query.append ("' AND ");
106             query.append (FIELD_ACCOUNT_SITE_KEY);
107             query.append ("='");
108             query.append (account.getSiteKey());
109             query.append ("'");
110 
111             // executes the query
112             statement = connection.createStatement();
113             if (statement != null) {
114                 statement.execute (query.toString());
115                 result = true;
116             }
117         } // catches an exception in the query
118         catch (SQLException   ex) {
119             throw new JahiaDatabaseException ("Cannot remove the account",
120                     query.toString(), ex, JahiaDatabaseException.ERROR_SEVERITY);
121         }
122         finally {
123             query = null;
124             closeStatement (statement);
125 
126         }
127 
128         return result;
129     }
130 
131 
132     //-------------------------------------------------------------------------
133     // Foux     17 Apr. 2001
134     /** Insert the specified account into the database. The new inserted account
135      *  object receives automaticaly a unique ID.
136      *
137      * @param   account
138      *      Reference to the account object to be inserted.
139      *
140      * @return
141      *      Return <code>true</code> if the specified user account could be
142      *      inserted successfully in the database. Retrun false on any error.
143      *      Return <code>false</code> if the speficied account is
144      *      <code>null</code> or on any other error.
145      *
146      * @exception   JahiaDatabaseException
147      *      Throws this exception on any database failure.
148      */
149     public synchronized boolean insertAccount (Account account)
150         throws  JahiaDatabaseException
151     {
152         if (account == null) {
153             return false;
154         }
155 
156         // Get the database connection.
157         Connection   connection = org.jahia.services.database.ConnectionDispenser.getConnection();
158         if (connection == null) {
159             throw new JahiaDatabaseException (
160                     "Account deletion process could not get a connection.",
161                     JahiaDatabaseException.CRITICAL_SEVERITY);
162         }
163 
164         // by default the result is set to false. It will become true only if
165         // the account could be inserted into the database.
166         boolean result = false;
167 
168         // create the query string
169         StringBuffer   query = new StringBuffer   ("INSERT INTO ");
170         query.append (JAHIA_ACCOUNTS);
171         query.append (" (");
172         query.append (FIELD_ACCOUNT_USER_KEY);
173         query.append (",");
174         query.append (FIELD_ACCOUNT_SITE_KEY);
175         query.append (",");
176         query.append (FIELD_CREATION_DATE_ACCOUNTS);
177         query.append (",");
178         query.append (FIELD_EXPIRATION_DATE_ACCOUNTS);
179         query.append (",");
180         query.append (FIELD_PWD_EXPIRATION_DATE_ACCOUNTS);
181         query.append (",");
182         query.append (FIELD_LAST_LOGIN_DATE_ACCOUNTS);
183         query.append (",");
184         query.append (FIELD_ACTIVATED_ACCOUNTS);
185         query.append (") VALUES ('");
186 
187         // add the account values to the query string
188         query.append (account.getUserKey());
189         query.append ("','");
190         query.append (account.getSiteKey());
191         query.append ("',");
192 
193         query.append ("'");
194         query.append (processDate (account.getCreationDate()));
195         query.append ("',");
196 
197         query.append ("'");
198         query.append (processDate (account.getExpirationDate()));
199         query.append ("',");
200 
201         query.append ("'");
202         query.append (processDate (account.getPasswordExpirationDate()));
203         query.append ("',");
204 
205         query.append ("'");
206         query.append (processDate (account.getLastLoginDate()));
207         query.append ("',");
208 
209         query.append (account.isActivated() ? "1" : "0");
210 
211         // end the query string
212         query.append (")");
213 
214         Statement   statement = null;
215         try {
216             statement = connection.createStatement();
217             if (statement != null) {
218                 statement.execute (query.toString());
219                 result = true;
220             }
221         }
222         catch (SQLException   ex) {
223             throw new JahiaDatabaseException ("Cannot insert the account",
224                     query.toString(), ex, JahiaDatabaseException.ERROR_SEVERITY);
225         }
226         finally {
227             query = null;
228             closeStatement (statement);
229 
230         }
231 
232         return result;
233     }
234 
235 
236     //-------------------------------------------------------------------------
237     // Foux     17 Apr. 2001
238     /**
239      * Load the requested user account from the database.
240      *
241      * @param   accountID
242      *      Account unique identification number.
243      *
244      * @return
245      *      Return the reference the account if the requested account ID
246      *      could be found and successfully loaded from the database.
247      *
248      * @exception   JahiaDatabaseException
249      *      Throws this exception on any database failure.
250      */
251     public Account loadAccount (String   userKey, String   siteKey)
252         throws  JahiaDatabaseException
253     {
254         // get the DB connection
255         Connection   connection = org.jahia.services.database.ConnectionDispenser.getConnection();
256         if (connection == null) {
257             throw new JahiaDatabaseException (
258                     "Account load process could not get a connection.",
259                     JahiaDatabaseException.CRITICAL_SEVERITY);
260         }
261 
262         Statement   statement = null;
263         StringBuffer   query = new StringBuffer   ("");
264         Account account = null;
265 
266         try {
267             query.append ("SELECT ");
268             query.append (FIELD_CREATION_DATE_ACCOUNTS);
269             query.append (",");
270             query.append (FIELD_EXPIRATION_DATE_ACCOUNTS);
271             query.append (",");
272             query.append (FIELD_PWD_EXPIRATION_DATE_ACCOUNTS);
273             query.append (",");
274             query.append (FIELD_LAST_LOGIN_DATE_ACCOUNTS);
275             query.append (",");
276             query.append (FIELD_ACTIVATED_ACCOUNTS);
277             query.append (" FROM ");
278             query.append (JAHIA_ACCOUNTS);
279             query.append (" WHERE ");
280             query.append (FIELD_ACCOUNT_USER_KEY);
281             query.append ("='");
282             query.append (account.getUserKey());
283             query.append ("' AND ");
284             query.append (FIELD_ACCOUNT_SITE_KEY);
285             query.append ("='");
286             query.append (account.getSiteKey());
287             query.append ("'");
288 
289             statement = connection.createStatement();
290             if (statement != null) {
291                 ResultSet   rs = statement.executeQuery( query.toString());
292                 if (rs != null) {
293                     while (rs.next()) {
294                         Date   creationDate =
295                                 extractDate (rs, FIELD_CREATION_DATE_ACCOUNTS);
296                         Date   expirationDate =
297                                 extractDate (rs, FIELD_EXPIRATION_DATE_ACCOUNTS);
298                         Date   pwdExpirationDate =
299                                 extractDate (rs, FIELD_PWD_EXPIRATION_DATE_ACCOUNTS);
300                         Date   lastLoginDate =
301                                 extractDate (rs, FIELD_LAST_LOGIN_DATE_ACCOUNTS);
302                         int activated = rs.getInt (FIELD_ACTIVATED_ACCOUNTS);
303 
304                         account = new Account (userKey, siteKey, creationDate,
305                                 expirationDate, pwdExpirationDate,
306                                 lastLoginDate, (activated==1));
307                     }
308                     rs = null;
309                 }
310             }
311         } catch (SQLException   ex) {
312             throw new JahiaDatabaseException ("Cannot load the account for user ["+
313                 userKey + "] on site [" + siteKey + "]", query.toString(), ex,
314                     JahiaDatabaseException.ERROR_SEVERITY);
315         }
316         finally {
317             query = null;
318 
319             closeStatement (statement);
320         }
321 
322         return account;
323     }
324 
325 
326     //-------------------------------------------------------------------------
327     // Foux     17 Apr. 2001
328     /** Update to the account in the database. Only the expiration date, the
329      *  last login date and the activation data will be updated, all the other
330      *  data will not be updated, because they can't change.
331      *
332      * @param   account
333      *      Reference to the account object to be updated.
334      *
335      * @return
336      *      Return <code>true</code> if the specified user account could be
337      *      updated successfully in the database. Retrun false on any error.
338      *      Return <code>false</code> if the speficied account is
339      *      <code>null</code> or on any other error.
340      *
341      * @exception   JahiaDatabaseException
342      *      Throws this exception on any database failure.
343      */
344     public synchronized boolean updateAccount (Account account)
345         throws  JahiaDatabaseException
346     {
347         if (account == null) {
348             return false;
349         }
350 
351         // Get the database connection.
352         Connection   connection = org.jahia.services.database.ConnectionDispenser.getConnection();
353         if (connection == null) {
354             throw new JahiaDatabaseException (
355                     "Account deletion process could not get a connection.",
356                     JahiaDatabaseException.CRITICAL_SEVERITY);
357         }
358 
359         // by default the result is set to false. It will become true only if
360         // the account could be inserted into the database.
361         boolean result = false;
362 
363         // create the query string
364         StringBuffer   query = new StringBuffer   ("UPDATE ");
365         query.append (JAHIA_ACCOUNTS);
366         query.append (" SET ");
367 
368         query.append (FIELD_EXPIRATION_DATE_ACCOUNTS);
369         query.append ("='");
370         query.append (processDate(account.getExpirationDate()));
371         query.append ("',");
372 
373         query.append (FIELD_PWD_EXPIRATION_DATE_ACCOUNTS);
374         query.append ("='");
375         query.append (processDate(account.getPasswordExpirationDate()));
376         query.append ("',");
377 
378         query.append (FIELD_LAST_LOGIN_DATE_ACCOUNTS);
379         query.append ("='");
380         query.append (processDate(account.getLastLoginDate()));
381         query.append ("',");
382 
383         if (account.isActivated()) {
384             query.append (FIELD_ACTIVATED_ACCOUNTS);
385             query.append ("=1");
386         } else {
387             query.append (FIELD_ACTIVATED_ACCOUNTS);
388             query.append ("=0");
389         }
390 
391         query.append (" WHERE ");
392         query.append (FIELD_ACCOUNT_USER_KEY);
393         query.append ("='");
394         query.append (account.getUserKey());
395         query.append ("' AND ");
396         query.append (FIELD_ACCOUNT_SITE_KEY);
397         query.append ("='");
398         query.append (account.getSiteKey());
399         query.append ("'");
400 
401         Statement   statement = null;
402         try {
403             statement = connection.createStatement();
404             if (statement != null) {
405                 statement.execute (query.toString());
406                 result = true;
407             }
408         }
409         catch (SQLException   ex) {
410             StringBuffer   buffer =
411                     new StringBuffer   ("Cannot update the account for user [");
412             buffer.append (account.getUserKey());
413             buffer.append ("] on site [");
414             buffer.append (account.getSiteKey());
415             buffer.append ("]");
416 
417 
418             throw new JahiaDatabaseException (buffer.toString(),
419                  query.toString(), ex, JahiaDatabaseException.ERROR_SEVERITY);
420         }
421         finally {
422             query = null;
423             closeStatement (statement);
424 
425         }
426 
427         return result;
428     }
429 
430 
431     //-------------------------------------------------------------------------
432     // Foux     17 Apr. 2001
433     /**
434      * Return all (enabled and disabled) accounts according to the site ID.
435      *
436      * @param   siteID
437      *      The unique site identification number.
438      * @param   activation
439      *      For the given site key, specified -1 to get all the accounts, 0 to
440      *      get all the disabled accounts and 1 to get all the enabled accounts.
441      *
442      * @return
443      *      Return an AccountList of accounts. This AccountList is always
444      *      non-null.
445      *
446      * @exception   JahiaDatabaseException
447      *      This exception is thrown on any database failure.
448      */
449     public AccountList getAccountsList (String   siteKey, int activation)
450         throws  JahiaDatabaseException
451     {
452         AccountList result = new AccountList ();
453 
454                 // get the DB connection
455         Connection   connection = org.jahia.services.database.ConnectionDispenser.getConnection();
456         if (connection == null) {
457             throw new JahiaDatabaseException (
458                     "Accounts list loading process could not get a connection.",
459                     JahiaDatabaseException.CRITICAL_SEVERITY);
460         }
461 
462         Statement   statement = null;
463         StringBuffer   query = new StringBuffer   ("");
464 
465         try {
466             query.append ("SELECT ");
467             query.append (FIELD_ACCOUNT_USER_KEY);
468             query.append (",");
469             query.append (FIELD_CREATION_DATE_ACCOUNTS);
470             query.append (",");
471             query.append (FIELD_EXPIRATION_DATE_ACCOUNTS);
472             query.append (",");
473             query.append (FIELD_PWD_EXPIRATION_DATE_ACCOUNTS);
474             query.append (",");
475             query.append (FIELD_LAST_LOGIN_DATE_ACCOUNTS);
476 
477             if (activation < 0) {
478                 query.append (",");
479                 query.append (FIELD_ACTIVATED_ACCOUNTS);
480             }
481 
482             query.append (" FROM ");
483             query.append (JAHIA_ACCOUNTS);
484             query.append (" WHERE ");
485             query.append (FIELD_ACCOUNT_SITE_KEY);
486             query.append ("='");
487             query.append (siteKey);
488             query.append ("'");
489 
490             if (activation >= 0) {
491                 query.append (" AND ");
492                 query.append (FIELD_ACTIVATED_ACCOUNTS);
493                 query.append ("=");
494                 query.append (activation>0?1:0);
495             }
496 
497             statement = connection.createStatement();
498             if (statement != null) {
499                 ResultSet   rs = statement.executeQuery( query.toString());
500                 if (rs != null) {
501                     while (rs.next()) {
502 
503                             String   userKey = rs.getString (FIELD_ACCOUNT_USER_KEY);
504                             if (userKey == null) {
505                                 throw new JahiaDatabaseException ("Unallowed null value for column ["+
506                                     FIELD_ACCOUNT_USER_KEY+"] in table ["+JAHIA_ACCOUNTS+"].",
507                                     JahiaDatabaseException.ERROR_SEVERITY);
508                             }
509 
510                             Date   creationDate = extractDate (rs,
511                                     FIELD_CREATION_DATE_ACCOUNTS);
512                             Date   expirationDate = extractDate (rs,
513                                     FIELD_EXPIRATION_DATE_ACCOUNTS);
514                             Date   pwdExpirationDate = extractDate (rs,
515                                     FIELD_PWD_EXPIRATION_DATE_ACCOUNTS);
516                             Date   lastLoginDate = extractDate (rs,
517                                     FIELD_LAST_LOGIN_DATE_ACCOUNTS);
518 
519                             boolean activated = false;
520                             if (activation < 0) {
521                                 activated = (rs.getInt (FIELD_ACTIVATED_ACCOUNTS) == 1);
522                             } else {
523                                 activated = activation == 1;
524                             }
525 
526                             Account account = new Account (userKey, siteKey,
527                                 creationDate, expirationDate, pwdExpirationDate,
528                                 lastLoginDate, activated);
529 
530                         result.add (account);
531                     } // while
532                     rs = null;
533                 }
534             }
535         } catch (SQLException   ex) {
536             throw new JahiaDatabaseException ("Cannot load the accounts for site ["+
537                 siteKey + "]", query.toString(), ex,
538                 JahiaDatabaseException.ERROR_SEVERITY);
539         }
540         finally {
541             query = null;
542 
543             closeStatement (statement);
544         }
545 
546         return result;
547     }
548 
549 
550     //-------------------------------------------------------------------------
551     // Foux     17 Apr. 2001
552     /**
553      * Return all (enabled and disabled) accounts for the specified user.
554      *
555      * @param   userKey
556      *      The unique user identification key.
557      *
558      * @return
559      *      Return an AccountList of accounts. This AccountList is always
560      *      non-null.
561      *
562      * @exception   JahiaDatabaseException
563      *      This exception is thrown on any database failure.
564      */
565     public AccountList getUserAccounts (String   userKey)
566         throws  JahiaDatabaseException
567     {
568                 // get the DB connection
569         Connection   connection = org.jahia.services.database.ConnectionDispenser.getConnection();
570         if (connection == null) {
571             throw new JahiaDatabaseException (
572                     "Accounts list loading process could not get a connection.",
573                     JahiaDatabaseException.CRITICAL_SEVERITY);
574         }
575 
576         AccountList result = new AccountList ();
577         Statement   statement = null;
578         StringBuffer   query = new StringBuffer   ("");
579 
580         try {
581             query.append ("SELECT ");
582             query.append (FIELD_ACCOUNT_SITE_KEY);
583             query.append (",");
584             query.append (FIELD_CREATION_DATE_ACCOUNTS);
585             query.append (",");
586             query.append (FIELD_EXPIRATION_DATE_ACCOUNTS);
587             query.append (",");
588             query.append (FIELD_PWD_EXPIRATION_DATE_ACCOUNTS);
589             query.append (",");
590             query.append (FIELD_LAST_LOGIN_DATE_ACCOUNTS);
591             query.append (",");
592             query.append (FIELD_ACTIVATED_ACCOUNTS);
593 
594             query.append (" FROM ");
595             query.append (JAHIA_ACCOUNTS);
596 
597             query.append (" WHERE ");
598             query.append (FIELD_ACCOUNT_USER_KEY);
599             query.append ("='");
600             query.append (userKey);
601             query.append ("'");
602 
603             statement = connection.createStatement();
604             if (statement != null) {
605                 ResultSet   rs = statement.executeQuery( query.toString());
606                 if (rs != null) {
607                     while (rs.next()) {
608                             String   siteKey = rs.getString (FIELD_ACCOUNT_SITE_KEY);
609 
610                             Date   creationDate = extractDate (rs,
611                                     FIELD_CREATION_DATE_ACCOUNTS);
612                             Date   expirationDate = extractDate (rs,
613                                     FIELD_EXPIRATION_DATE_ACCOUNTS);
614                             Date   pwdExpirationDate = extractDate (rs,
615                                     FIELD_PWD_EXPIRATION_DATE_ACCOUNTS);
616                             Date   lastLoginDate = extractDate (rs,
617                                     FIELD_LAST_LOGIN_DATE_ACCOUNTS);
618 
619                             boolean activated = (rs.getInt (FIELD_ACTIVATED_ACCOUNTS) == 1);
620 
621                             Account account = new Account (userKey, siteKey,
622                                 creationDate, expirationDate, pwdExpirationDate,
623                                 lastLoginDate, activated);
624 
625                         result.add (account);
626                     } // while
627                     rs = null;
628                 }
629             }
630         } catch (SQLException   ex) {
631             throw new JahiaDatabaseException ("Cannot load the accounts for user ["+
632                 userKey + "]", query.toString(), ex,
633                 JahiaDatabaseException.ERROR_SEVERITY);
634         }
635         finally {
636             query = null;
637 
638             closeStatement (statement);
639         }
640 
641         return result;
642     }
643 
644 
645     //-------------------------------------------------------------------------
646     // Foux     17 Apr. 2001
647     /**
648      * Close the specified SQL statement.
649      */
650     private void closeStatement (Statement   statement)
651     {
652         // Close the opened statement
653         try {
654             if (statement!=null) {
655                 statement.close();
656                 statement = null;
657             }
658         }
659         catch (SQLException   sqlEx) {
660             // just create an exception without raising it, just to notify it
661             // in the logs.
662             JahiaDatabaseException ex = new JahiaDatabaseException (
663                     "Cannot close a statement", sqlEx,
664                     JahiaDatabaseException.WARNING_SEVERITY);
665         }
666     }
667 
668     //-------------------------------------------------------------------------
669     // Fulco    18 Apr. 2001
670     /**
671      * Extract the creation date from the ResultSet.
672      *
673      * @param   rs
674      *      Result set from where the creation date has to be extracted.
675      * @param   columnName
676      *      Column name from where the date as to be extracted.
677      *
678      * @return
679      *      Return the creation date or <code>null</code> if the date in the
680      *      database is empty.
681      *
682      * @exception   SQLException
683      *      Throw this exception on any database failure.
684      */
685     private Date   extractDate (ResultSet   rs, String   columnName)
686         throws  SQLException  
687     {
688         Date   date = null;
689 
690         String   dateTime = rs.getString (columnName);
691         if (!NULL_STRING.equals(dateTime)) {
692             date = new Date  ();
693             date.setTime (Long.parseLong(dateTime));
694         }
695         dateTime = null;
696         return date;
697     }
698 
699     //-------------------------------------------------------------------------
700     // Fulco    18 Apr. 2001
701     /**
702      * Convert the specified date into a string format.
703      *
704      * @param   date
705      *      Date to be converted.
706      * @return
707      *      Return a string representation of the passed date.
708      */
709     private String   processDate (Date   date)
710     {
711         String   result = NULL_STRING;
712 
713         if (date != null) {
714             result = Long.toString(date.getTime());
715         }
716 
717         return result;
718     }
719 }
720
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags