KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > jahia > services > usermanager > JahiaUserDBUtils


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.services.usermanager;
14
15 import java.sql.Connection JavaDoc;
16 import java.sql.PreparedStatement JavaDoc;
17 import java.sql.ResultSet JavaDoc;
18 import java.sql.SQLException JavaDoc;
19 import java.sql.Statement JavaDoc;
20 import java.util.Enumeration JavaDoc;
21 import java.util.HashSet JavaDoc;
22 import java.util.Properties JavaDoc;
23 import java.util.Set JavaDoc;
24
25 import org.jahia.exceptions.JahiaException;
26 import org.jahia.exceptions.database.JahiaDatabaseConnectionException;
27 import org.jahia.exceptions.database.JahiaDatabaseException;
28 import org.jahia.utils.JahiaTools;
29
30
31 /**
32  * A JahiaUser represents a physical person who is defined by a username and
33  * a password for authentification purpose. Every other property of a JahiaUser
34  * is stored in it's properties list, which hold key-value string pairs.
35  * For example email, firstname, lastname, ... information should be stored in
36  * this properties list.
37  *
38  * @author Fulco Houkes
39  * @version 1.1
40  */
41 class JahiaUserDBUtils {
42     static private JahiaUserDBUtils mObject = null;
43
44     private static final String JavaDoc USERNAME_PROPERTY_NAME = "username";
45
46     private static org.apache.log4j.Logger logger =
47             org.apache.log4j.Logger.getLogger (JahiaUserDBUtils.class);
48
49     //--------------------------------------------------------------------------
50 private JahiaUserDBUtils () {
51     }
52
53
54     //--------------------------------------------------------------------------
55 /**
56      * Get the user database utilities object reference.
57      *
58      * @return The user database utilities reference.
59      */
60     static public JahiaUserDBUtils getInstance () {
61         if (mObject == null) {
62             mObject = new JahiaUserDBUtils ();
63         }
64         return mObject;
65     }
66
67     public UserProperties getUserProperties (int userID, String JavaDoc providerName, String JavaDoc userKey)
68             throws JahiaDatabaseException {
69         // Get all the user attributes
70 UserProperties properties = new UserProperties ();
71         final String JavaDoc query = "SELECT name_jahia_user_prop, value_jahia_user_prop FROM jahia_user_prop WHERE id_jahia_users=? and provider_jahia_user_prop=? and userkey_jahia_user_prop=?";
72         PreparedStatement JavaDoc statement = null;
73         try {
74
75             Connection JavaDoc dbConn = org.jahia.services.database.ConnectionDispenser.getConnection ();
76             if (dbConn == null) {
77                 return null;
78             }
79             // execute the SELECT query
80 statement = dbConn.prepareStatement (query);
81             statement.setInt (1, userID);
82             statement.setString (2, providerName);
83             statement.setString (3, userKey);
84
85             ResultSet JavaDoc rs = statement.executeQuery ();
86             if (rs != null) {
87
88                 String JavaDoc propName = null;
89                 String JavaDoc propVal = null;
90
91                 while (rs.next ()) {
92
93                     propName = rs.getString ("name_jahia_user_prop");
94                     propVal = rs.getString ("value_jahia_user_prop");
95                     if (propVal == null) {
96                         propVal = "";
97                     }
98
99                     if (propName != null) {
100                         UserProperty userProperty = new UserProperty(propName, propVal, false);
101                         properties.setUserProperty(propName, userProperty);
102                     }
103                 }
104
105             }
106         } catch (SQLException JavaDoc sqlEx) {
107             throw new JahiaDatabaseException (
108                     "", query, sqlEx, JahiaDatabaseException.ERROR_SEVERITY);
109         } finally {
110             closeStatement (statement);
111         }
112
113         return properties;
114     }
115
116     //--------------------------------------------------------------------------
117 // FH 29 Mar. 2001
118 // Initial implementation
119 //
120 /**
121      * Remove the specified property from the properties list.
122      *
123      * @param key Property's name.
124      *
125      * @return Return true on success or false on any failure.
126      */
127     public synchronized boolean removeProperty (String JavaDoc propertyKey, int userID,
128                                                 String JavaDoc providerName, String JavaDoc userKey)
129             throws JahiaDatabaseException,
130             JahiaDatabaseConnectionException,
131             JahiaException {
132         boolean result = false;
133
134         String JavaDoc query = "DELETE FROM jahia_user_prop WHERE" +
135                 " id_jahia_users=" + userID +
136                 " AND name_jahia_user_prop='" + propertyKey + "'" +
137                 " AND provider_jahia_user_prop='" + providerName + "'" +
138                 " AND userkey_jahia_user_prop='" + userKey + "'";
139
140         return makeQuery (query);
141     }
142
143
144     //---------------------------------------------------------------------------
145 // FH 29 Mar. 2001
146 // Initial implementation
147 //
148 /**
149      * Change the user's password.
150      *
151      * @param newPassword New user's password
152      *
153      * @return Return true id the old password is the same as the current one and
154      * the new password is valid. Return false on any failure.
155      */
156     public synchronized boolean setPassword (String JavaDoc password, int userID)
157             throws JahiaDatabaseException,
158             JahiaDatabaseConnectionException,
159             JahiaException {
160         boolean result = false;
161
162         // try to avoid a NullPointerException
163 if (password != null) {
164             // password should not be empty.
165 if (password.length () > 0) {
166                 // Encrypt the new password
167 String JavaDoc tmp = JahiaUserManagerService.
168                         encryptPassword (password);
169
170                 String JavaDoc query = "UPDATE jahia_users SET password_jahia_users='" +
171                         tmp + "' WHERE id_jahia_users=" + userID;
172                 result = makeQuery (query);
173             }
174         }
175         return result;
176     }
177
178
179     //--------------------------------------------------------------------------
180 // FH 29 Mar. 2001
181 // Initial implementation
182 //
183 /**
184      * Add (or update if not already in the property list) a property key-value
185      * pair in the user's properties list.
186      *
187      * @param key Property's name.
188      * @param value Property's value.
189      *
190      * @return Return true on success or false on any failure.
191      */
192     public synchronized boolean addProperty (String JavaDoc key, String JavaDoc value,
193                                              int userID, String JavaDoc providerName, String JavaDoc userKey)
194             throws JahiaDatabaseException,
195             JahiaDatabaseConnectionException,
196             JahiaException {
197         boolean result = false;
198
199         String JavaDoc query = "INSERT INTO jahia_user_prop (id_jahia_users, " +
200                 "name_jahia_user_prop, value_jahia_user_prop, provider_jahia_user_prop, userkey_jahia_user_prop)" +
201                 " VALUES (" + userID + ",'" + key + "','" + value + "', '" + providerName + "', '" + userKey + "')";
202
203         return makeQuery (query);
204     }
205
206
207     //--------------------------------------------------------------------------
208 // FH 29 Mar. 2001
209 // Initial implementation
210 //
211 /**
212      * Add (or update if not already in the property list) a property key-value
213      * pair in the user's properties list.
214      *
215      * @param key Property's name.
216      * @param value Property's value.
217      *
218      * @return Return true on success or false on any failure.
219      */
220     public synchronized boolean updateProperty (String JavaDoc key, String JavaDoc value,
221                                                 int userID, String JavaDoc providerName,
222                                                 String JavaDoc userKey)
223             throws JahiaDatabaseException,
224             JahiaDatabaseConnectionException,
225             JahiaException {
226         boolean result = false;
227
228         String JavaDoc query = "UPDATE jahia_user_prop SET " +
229                 "value_jahia_user_prop='" + value + "'" +
230                 " WHERE id_jahia_users=" + userID +
231                 " AND name_jahia_user_prop='" + key + "'" +
232                 " AND provider_jahia_user_prop='" + providerName + "'" +
233                 " AND userkey_jahia_user_prop='" + userKey + "'";
234
235         return makeQuery (query);
236     }
237
238     /**
239      * Transforms a search with "*" characters into a valid LIKE statement
240      * with "%" characters. Also escapes the string to remove all "'" and
241      * other chars that might disturb the request construct.
242      *
243      * @param input the original String
244      *
245      * @return String a resulting string that has
246      */
247     private String JavaDoc makeLIKEString (String JavaDoc input) {
248         String JavaDoc result = JahiaTools.replacePattern (input, "*", "%");
249         result = JahiaTools.replacePattern (result, "'", "\\'");
250         result = JahiaTools.replacePattern (result, "\"", "\\\"");
251         result = JahiaTools.replacePattern (result, "_", "\\_");
252         return result;
253     }
254
255     /**
256      * Find users according to a table of name=value properties. If the left
257      * side value is "*" for a property then it will be tested against all the
258      * properties. ie *=test* will match every property that starts with "test"
259      *
260      * @param siteID site identifier
261      * @param searchCriterias a Properties object that contains search criterias
262      * in the format name,value (for example "*"="*" or "username"="*test*") or
263      * null to search without criterias
264      *
265      * @return Set a set of JahiaUser elements that correspond to those
266      * search criterias
267      *
268      * @todo this code could be cleaner if username was a real user property
269      * but as it isn't we have to do a lot of custom handling.
270      */
271     public synchronized Set JavaDoc searchUsersByProperties(int siteID, Properties JavaDoc searchCriterias)
272         throws JahiaDatabaseException {
273         Set JavaDoc userKeys = new HashSet JavaDoc ();
274
275         // Get a database connection
276 Connection JavaDoc dbConn = org.jahia.services.database.ConnectionDispenser.getConnection ();
277         if (dbConn == null) {
278             return userKeys;
279         }
280
281         if (searchCriterias == null) {
282             searchCriterias = new Properties JavaDoc ();
283             searchCriterias.setProperty ("*", "*");
284         }
285
286         boolean haveWildCardProperty = false;
287         if (searchCriterias.getProperty ("*") != null) {
288             haveWildCardProperty = true;
289         }
290
291         // execute the SELECT query
292 Statement JavaDoc statement = null;
293         try {
294             statement = dbConn.createStatement ();
295             if (statement != null) {
296
297                 StringBuffer JavaDoc query;
298                 boolean onlyUserNameInSelect = false;
299                 if ((searchCriterias.getProperty (USERNAME_PROPERTY_NAME) != null) ||
300                         (haveWildCardProperty)
301                 ) {
302
303                     String JavaDoc curCriteriaValue;
304                     if (haveWildCardProperty) {
305                         curCriteriaValue = makeLIKEString (searchCriterias.getProperty ("*"));
306                     } else {
307                         curCriteriaValue =
308                                 makeLIKEString (
309                                         searchCriterias.getProperty (USERNAME_PROPERTY_NAME));
310                     }
311                     query =
312                             new StringBuffer JavaDoc (
313                                     "SELECT DISTINCT key_jahia_users AS result_key_jahia_users FROM jahia_users,jahia_sites_users WHERE ");
314                     query.append ("name_jahia_users LIKE '");
315                     query.append (curCriteriaValue);
316                     query.append ("'");
317                     query.append ("AND jahia_sites_users.siteid_sites_users=");
318                     query.append (siteID);
319                     query.append (
320                             " AND jahia_users.key_jahia_users=jahia_sites_users.userid_sites_users");
321                     // logger.debug("Executing query [" + query.toString() + "]");
322 ResultSet JavaDoc rs = statement.executeQuery (query.toString ());
323                     if (rs != null) {
324                         while (rs.next ()) {
325                             String JavaDoc name = rs.getString ("result_key_jahia_users");
326                             if (name != null) {
327                                 userKeys.add (name);
328                             }
329                         }
330                     }
331
332                     if ((!haveWildCardProperty) && (searchCriterias.size () == 1)) {
333                         onlyUserNameInSelect = true;
334                     }
335                 } else {
336                     onlyUserNameInSelect = false;
337                 }
338
339                 if (!onlyUserNameInSelect) {
340
341                     query =
342                             new StringBuffer JavaDoc (
343                                     "SELECT DISTINCT jahia_users.key_jahia_users AS result_key_jahia_users FROM jahia_users, jahia_user_prop, jahia_sites_users WHERE ");
344                     Enumeration JavaDoc criteriaNames = searchCriterias.keys ();
345                     while (criteriaNames.hasMoreElements ()) {
346                         String JavaDoc curCriteriaName = (String JavaDoc) criteriaNames.nextElement ();
347                         String JavaDoc curCriteriaValue = makeLIKEString (
348                                 searchCriterias.getProperty (curCriteriaName));
349                         if ("*".equals (curCriteriaName)) {
350                             // we must look in all columns, including special for
351 // the user.
352 query.append ("(jahia_users.name_jahia_users LIKE '");
353                             query.append (curCriteriaValue);
354                             query.append ("'");
355                             query.append (" OR jahia_user_prop.value_jahia_user_prop LIKE '");
356                             query.append (curCriteriaValue);
357                             query.append ("') ");
358                             query.append (" AND ");
359                             onlyUserNameInSelect = false;
360                         } else {
361                             if (USERNAME_PROPERTY_NAME.equals (curCriteriaName)) {
362                                 // user name filter is a special case and is not
363 // stored in the property table.
364 } else {
365                                 query.append ("(jahia_user_prop.name_jahia_user_prop='");
366                                 query.append (makeLIKEString (curCriteriaName));
367                                 query.append (
368                                         "' AND jahia_user_prop.value_jahia_user_prop LIKE '");
369                                 query.append (curCriteriaValue);
370                                 query.append ("') ");
371                                 query.append (" AND ");
372                                 onlyUserNameInSelect = false;
373                             }
374                         }
375                     }
376                     if (!(onlyUserNameInSelect)) {
377                         if (!query.toString ().endsWith (" AND ")) {
378                             query.append (" AND ");
379                         }
380                     }
381                     query.append (
382                             "jahia_users.id_jahia_users=jahia_user_prop.id_jahia_users AND ");
383                     query.append ("jahia_sites_users.siteid_sites_users=");
384                     query.append (siteID);
385                     query.append (
386                             " AND jahia_users.key_jahia_users=jahia_sites_users.userid_sites_users");
387                     // logger.debug("Executing query [" + query.toString() + "]");
388
389                     ResultSet JavaDoc rs = statement.executeQuery (query.toString ());
390                     if (rs != null) {
391                         while (rs.next ()) {
392                             String JavaDoc name = rs.getString ("result_key_jahia_users");
393                             if (name != null) {
394                                 userKeys.add (name);
395                             }
396                         }
397                     }
398                 }
399             }
400         } catch (SQLException JavaDoc ex) {
401             logger.error ("Error while searching users in site " + siteID, ex);
402         } finally {
403             closeStatement (statement);
404         }
405
406         return userKeys;
407
408     }
409
410
411     //--------------------------------------------------------------------------
412 // Executes and INSERT, UPDATE or DELETE SQL operation. This method should not
413 // be used with and SELECT operation. This method lock the object on database
414 // write access.
415 private boolean makeQuery (String JavaDoc query)
416             throws JahiaDatabaseException,
417             JahiaDatabaseConnectionException,
418             JahiaException {
419         // Get a database connection
420 Connection JavaDoc dbConn = org.jahia.services.database.ConnectionDispenser.getConnection ();
421         if (dbConn == null) {
422             return false;
423         }
424
425         boolean result = false;
426         Statement JavaDoc statement = null;
427
428         try {
429             statement = dbConn.createStatement ();
430             if (statement != null) {
431                 synchronized (this) {
432                     statement.executeUpdate (query);
433                     result = true;
434                 }
435             }
436         } catch (SQLException JavaDoc sqlEx) {
437             throw new JahiaDatabaseException (
438                     "", query, sqlEx, JahiaDatabaseException.ERROR_SEVERITY);
439         } finally {
440             closeStatement (statement);
441         }
442
443         return result;
444     }
445
446     //-------------------------------------------------------------------------
447 private void closeStatement (Statement JavaDoc statement)
448             throws JahiaDatabaseException {
449         // Close the opened statement
450 try {
451             if (statement != null) {
452                 statement.close ();
453             }
454         } catch (SQLException JavaDoc sqlEx) {
455             throw new JahiaDatabaseException (
456                     "Could not close a statement in JahiaDBUser",
457                     sqlEx, JahiaDatabaseException.ERROR_SEVERITY);
458         }
459     }
460
461 }
462
Popular Tags