Java > Open Source Codes > org > apache > james > userrepository > AbstractJdbcUsersRepository


1 /***********************************************************************
2  * Copyright (c) 2000-2004 The Apache Software Foundation. *
3  * All rights reserved. *
4  * ------------------------------------------------------------------- *
5  * Licensed under the Apache License, Version 2.0 (the "License"); you *
6  * may not use this file except in compliance with the License. You *
7  * may obtain a copy of the License at: *
8  * *
9  * http://www.apache.org/licenses/LICENSE-2.0 *
10  * *
11  * Unless required by applicable law or agreed to in writing, software *
12  * distributed under the License is distributed on an "AS IS" BASIS, *
13  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or *
14  * implied. See the License for the specific language governing *
15  * permissions and limitations under the License. *
16  ***********************************************************************/
17
18 package org.apache.james.userrepository;
19
20 import org.apache.avalon.cornerstone.services.datasource.DataSourceSelector;
21 import org.apache.avalon.excalibur.datasource.DataSourceComponent;
22 import org.apache.avalon.framework.CascadingRuntimeException;
23 import org.apache.avalon.framework.activity.Initializable;
24 import org.apache.avalon.framework.component.Component;
25 import org.apache.avalon.framework.component.ComponentException;
26 import org.apache.avalon.framework.component.ComponentManager;
27 import org.apache.avalon.framework.component.Composable;
28 import org.apache.avalon.framework.configuration.Configurable;
29 import org.apache.avalon.framework.configuration.Configuration;
30 import org.apache.avalon.framework.configuration.ConfigurationException;
31 import org.apache.avalon.framework.context.Context;
32 import org.apache.avalon.framework.context.ContextException;
33 import org.apache.avalon.framework.context.Contextualizable;
34 import org.apache.james.context.AvalonContextUtilities;
35 import org.apache.james.services.User;
36 import org.apache.james.util.JDBCUtil;
37 import org.apache.james.util.SqlResources;
38
39 import java.io.File ;
40 import java.sql.*;
41 import java.util.*;
42
43 /**
44  * An abstract base class for creating UserRepository implementations
45  * which use a database for persistence.
46  *
47  * To implement a new UserRepository using by extending this class,
48  * you need to implement the 3 abstract methods defined below,
49  * and define the required SQL statements in an SQLResources
50  * file.
51  *
52  * The SQL statements used by this implementation are:
53  * <TABLE>
54  * <TH><TD><B>Required</B></TD></TH>
55  * <TR><TD>select</TD><TD>Select all users.</TD></TR>
56  * <TR><TD>insert</TD><TD>Insert a user.</TD></TR>
57  * <TR><TD>update</TD><TD>Update a user.</TD></TR>
58  * <TR><TD>delete</TD><TD>Delete a user by name.</TD></TR>
59  * <TR><TD>createTable</TD><TD>Create the users table.</TD></TR>
60  * <TH><TD><B>Optional</B></TD></TH>
61  * <TR><TD>selectByLowercaseName</TD><TD>Select a user by name (case-insensitive lowercase).</TD></TR>
62  * </TABLE>
63  *
64  */
65 public abstract class AbstractJdbcUsersRepository extends AbstractUsersRepository
66     implements Contextualizable, Composable, Configurable, Initializable
67 {
68     /**
69      * The Avalon context used by the instance
70      */
71     protected Context context;
72
73     protected Map m_sqlParameters;
74
75     private String m_sqlFileName;
76
77     private String m_datasourceName;
78
79     private DataSourceSelector m_datasources;
80
81     private DataSourceComponent m_datasource;
82
83     // Fetches all Users from the db.
84 private String m_getUsersSql;
85     
86     // This fetch a user by name, ensuring case-insensitive matching.
87 private String m_userByNameCaseInsensitiveSql;
88
89     // Insert, update and delete sql statements are not guaranteed
90 // to be case-insensitive; this is handled in code.
91 private String m_insertUserSql;
92     private String m_updateUserSql;
93     private String m_deleteUserSql;
94
95     // Creates a single table with "username" the Primary Key.
96 private String m_createUserTableSql;
97
98     // The JDBCUtil helper class
99 private JDBCUtil theJDBCUtil;
100
101     /**
102      * @see org.apache.avalon.framework.context.Contextualizable#contextualize(Context)
103      */
104     public void contextualize(final Context context)
105             throws ContextException {
106         this.context = context;
107     }
108
109     /**
110      * @see org.apache.avalon.framework.component.Composable#compose(ComponentManager)
111      */
112     public void compose( final ComponentManager componentManager )
113         throws ComponentException
114     {
115         StringBuffer logBuffer = null;
116         if (getLogger().isDebugEnabled())
117         {
118             logBuffer =
119                 new StringBuffer (64)
120                         .append(this.getClass().getName())
121                         .append(".compose()");
122             getLogger().debug( logBuffer.toString() );
123         }
124
125         m_datasources =
126             (DataSourceSelector)componentManager.lookup( DataSourceSelector.ROLE );
127     }
128
129     /**
130      * Configures the UserRepository for JDBC access.<br>
131      * <br>
132      * Requires a configuration element in the .conf.xml file of the form:<br>
133      * <br>
134      * <pre>
135      * &lt;repository name="LocalUsers"
136      * class="org.apache.james.userrepository.JamesUsersJdbcRepository"&gt;
137      * &lt;!-- Name of the datasource to use --&gt;
138      * &lt;data-source&gt;MailDb&lt;/data-source&gt;
139      * &lt;!-- File to load the SQL definitions from -->
140      * &lt;sqlFile>dist/conf/sqlResources.xml&lt;/sqlFile&gt;
141      * &lt;!-- replacement parameters for the sql file --&gt;
142      * &lt;sqlParameters table="JamesUsers"/&gt;
143      * &lt;/repository&gt;
144      * </pre>
145      */
146     public void configure(Configuration configuration) throws ConfigurationException
147     {
148         StringBuffer logBuffer = null;
149         if (getLogger().isDebugEnabled()) {
150             logBuffer =
151                 new StringBuffer (64)
152                         .append(this.getClass().getName())
153                         .append(".configure()");
154             getLogger().debug( logBuffer.toString() );
155         }
156
157         // Parse the DestinationURL for the name of the datasource,
158 // the table to use, and the (optional) repository Key.
159 String destUrl = configuration.getAttribute("destinationURL");
160         // normalise the destination, to simplify processing.
161 if ( ! destUrl.endsWith("/") ) {
162             destUrl += "/";
163         }
164         // Split on "/", starting after "db://"
165 List urlParams = new ArrayList();
166         int start = 5;
167         int end = destUrl.indexOf('/', start);
168         while ( end > -1 ) {
169             urlParams.add(destUrl.substring(start, end));
170             start = end + 1;
171             end = destUrl.indexOf('/', start);
172         }
173
174         // Build SqlParameters and get datasource name from URL parameters
175 m_sqlParameters = new HashMap();
176         switch ( urlParams.size() ) {
177         case 3:
178             m_sqlParameters.put("key", urlParams.get(2));
179         case 2:
180             m_sqlParameters.put("table", urlParams.get(1));
181         case 1:
182             m_datasourceName = (String )urlParams.get(0);
183             break;
184         default:
185             throw new ConfigurationException
186                 ("Malformed destinationURL - " +
187                  "Must be of the format \"db://<data-source>[/<table>[/<key>]]\".");
188         }
189
190         if (getLogger().isDebugEnabled()) {
191             logBuffer =
192                 new StringBuffer (128)
193                         .append("Parsed URL: table = '")
194                         .append(m_sqlParameters.get("table"))
195                         .append("', key = '")
196                         .append(m_sqlParameters.get("key"))
197                         .append("'");
198             getLogger().debug(logBuffer.toString());
199         }
200         
201         // Get the SQL file location
202 m_sqlFileName = configuration.getChild("sqlFile", true).getValue();
203         if (!m_sqlFileName.startsWith("file://")) {
204             throw new ConfigurationException
205                 ("Malformed sqlFile - Must be of the format \"file://<filename>\".");
206         }
207
208         // Get other sql parameters from the configuration object,
209 // if any.
210 Configuration sqlParamsConfig = configuration.getChild("sqlParameters");
211         String [] paramNames = sqlParamsConfig.getAttributeNames();
212         for (int i = 0; i < paramNames.length; i++ ) {
213             String paramName = paramNames[i];
214             String paramValue = sqlParamsConfig.getAttribute(paramName);
215             m_sqlParameters.put(paramName, paramValue);
216         }
217     }
218
219     /**
220      * <p>Initialises the JDBC repository.</p>
221      * <p>1) Tests the connection to the database.</p>
222      * <p>2) Loads SQL strings from the SQL definition file,
223      * choosing the appropriate SQL for this connection,
224      * and performing parameter substitution,</p>
225      * <p>3) Initialises the database with the required tables, if necessary.</p>
226      *
227      * @throws Exception if an error occurs
228      */
229     public void initialize() throws Exception
230     {
231         StringBuffer logBuffer = null;
232         if (getLogger().isDebugEnabled()) {
233             logBuffer =
234                 new StringBuffer (128)
235                         .append(this.getClass().getName())
236                         .append(".initialize()");
237             getLogger().debug( logBuffer.toString() );
238         }
239
240         theJDBCUtil =
241             new JDBCUtil() {
242                 protected void delegatedLog(String logString) {
243                     AbstractJdbcUsersRepository.this.getLogger().warn("AbstractJdbcUsersRepository: " + logString);
244                 }
245             };
246
247         // Get the data-source required.
248 m_datasource = (DataSourceComponent)m_datasources.select(m_datasourceName);
249
250         // Test the connection to the database, by getting the DatabaseMetaData.
251 Connection conn = openConnection();
252         try{
253             DatabaseMetaData dbMetaData = conn.getMetaData();
254
255             File sqlFile = null;
256
257             try {
258                 sqlFile = AvalonContextUtilities.getFile(context, m_sqlFileName);
259             } catch (Exception e) {
260                 getLogger().fatalError(e.getMessage(), e);
261                 throw e;
262             }
263
264             if (getLogger().isDebugEnabled()) {
265                 logBuffer =
266                     new StringBuffer (256)
267                             .append("Reading SQL resources from file: ")
268                             .append(sqlFile.getAbsolutePath())
269                             .append(", section ")
270                             .append(this.getClass().getName())
271                             .append(".");
272                 getLogger().debug(logBuffer.toString());
273             }
274
275             SqlResources sqlStatements = new SqlResources();
276             sqlStatements.init(sqlFile, this.getClass().getName(),
277                                conn, m_sqlParameters);
278
279             // Create the SQL Strings to use for this table.
280 // Fetches all Users from the db.
281 m_getUsersSql = sqlStatements.getSqlString("select", true);
282
283             // Get a user by lowercase name. (optional)
284 // If not provided, the entire list is iterated to find a user.
285 m_userByNameCaseInsensitiveSql =
286                 sqlStatements.getSqlString("selectByLowercaseName");
287
288             // Insert, update and delete are not guaranteed to be case-insensitive
289 // Will always be called with correct case in username..
290 m_insertUserSql = sqlStatements.getSqlString("insert", true);
291             m_updateUserSql = sqlStatements.getSqlString("update", true);
292             m_deleteUserSql = sqlStatements.getSqlString("delete", true);
293
294             // Creates a single table with "username" the Primary Key.
295 m_createUserTableSql = sqlStatements.getSqlString("createTable", true);
296
297             // Check if the required table exists. If not, create it.
298 // The table name is defined in the SqlResources.
299 String tableName = sqlStatements.getSqlString("tableName", true);
300             
301             // Need to ask in the case that identifiers are stored, ask the DatabaseMetaInfo.
302 // NB this should work, but some drivers (eg mm MySQL)
303 // don't return the right details, hence the hackery below.
304 /*
305             String tableName = m_tableName;
306             if ( dbMetaData.storesLowerCaseIdentifiers() ) {
307                 tableName = tableName.toLowerCase(Locale.US);
308             }
309             else if ( dbMetaData.storesUpperCaseIdentifiers() ) {
310                 tableName = tableName.toUpperCase(Locale.US);
311             }
312             */
313
314             // Try UPPER, lower, and MixedCase, to see if the table is there.
315 if (! theJDBCUtil.tableExists(dbMetaData, tableName))
316             {
317                 // Users table doesn't exist - create it.
318 PreparedStatement createStatement = null;
319                 try {
320                     createStatement =
321                         conn.prepareStatement(m_createUserTableSql);
322                     createStatement.execute();
323                 } finally {
324                     theJDBCUtil.closeJDBCStatement(createStatement);
325                 }
326
327                 logBuffer =
328                     new StringBuffer (128)
329                             .append(this.getClass().getName())
330                             .append(": Created table \'")
331                             .append(tableName)
332                             .append("\'.");
333                 getLogger().info(logBuffer.toString());
334             } else {
335                 if (getLogger().isDebugEnabled()) {
336                     getLogger().debug("Using table: " + tableName);
337                 }
338             }
339         
340         }
341         finally {
342             theJDBCUtil.closeJDBCConnection( conn );
343         }
344     }
345
346     /**
347      * Produces the complete list of User names, with correct case.
348      * @return a <code>List</code> of <code>String</code>s representing
349      * user names.
350      */
351     protected List listUserNames() {
352         Collection users = getAllUsers();
353         List userNames = new ArrayList(users.size());
354         for (Iterator it = users.iterator(); it.hasNext(); ) {
355             userNames.add(((User)it.next()).getUserName());
356         }
357         users.clear();
358         return userNames;
359     }
360
361     //
362 // Superclass methods - overridden from AbstractUsersRepository
363 //
364 /**
365      * Returns a list populated with all of the Users in the repository.
366      * @return an <code>Iterator</code> of <code>JamesUser</code>s.
367      */
368     protected Iterator listAllUsers() {
369         return getAllUsers().iterator();
370     }
371
372     /**
373      * Returns a list populated with all of the Users in the repository.
374      * @return a <code>Collection</code> of <code>JamesUser</code>s.
375      */
376     private Collection getAllUsers() {
377         List userList = new ArrayList(); // Build the users into this list.
378
379         Connection conn = openConnection();
380         PreparedStatement getUsersStatement = null;
381         ResultSet rsUsers = null;
382         try {
383             // Get a ResultSet containing all users.
384 getUsersStatement =
385                 conn.prepareStatement(m_getUsersSql);
386             rsUsers = getUsersStatement.executeQuery();
387
388             // Loop through and build a User for every row.
389 while ( rsUsers.next() ) {
390                 User user = readUserFromResultSet(rsUsers);
391                 userList.add(user);
392             }
393         }
394         catch ( SQLException sqlExc) {
395             throw new CascadingRuntimeException("Error accessing database", sqlExc);
396         }
397         finally {
398             theJDBCUtil.closeJDBCResultSet(rsUsers);
399             theJDBCUtil.closeJDBCStatement(getUsersStatement);
400             theJDBCUtil.closeJDBCConnection(conn);
401         }
402
403         return userList;
404     }
405
406     /**
407      * Adds a user to the underlying Repository.
408      * The user name must not clash with an existing user.
409      *
410      * @param user the user to be added
411      */
412     protected void doAddUser(User user) {
413         Connection conn = openConnection();
414         PreparedStatement addUserStatement = null;
415
416         // Insert into the database.
417 try {
418             // Get a PreparedStatement for the insert.
419 addUserStatement =
420                 conn.prepareStatement(m_insertUserSql);
421
422             setUserForInsertStatement(user, addUserStatement);
423
424             addUserStatement.execute();
425         }
426         catch ( SQLException sqlExc) {
427             throw new CascadingRuntimeException("Error accessing database", sqlExc);
428         } finally {
429             theJDBCUtil.closeJDBCStatement(addUserStatement);
430             theJDBCUtil.closeJDBCConnection(conn);
431         }
432     }
433
434     /**
435      * Removes a user from the underlying repository.
436      * If the user doesn't exist this method doesn't throw
437      * an exception.
438      *
439      * @param user the user to be removed
440      */
441     protected void doRemoveUser(User user) {
442         String username = user.getUserName();
443
444         Connection conn = openConnection();
445         PreparedStatement removeUserStatement = null;
446
447         // Delete from the database.
448 try {
449             removeUserStatement = conn.prepareStatement(m_deleteUserSql);
450             removeUserStatement.setString(1, username);
451             removeUserStatement.execute();
452         }
453         catch ( SQLException sqlExc ) {
454             throw new CascadingRuntimeException("Error accessing database", sqlExc);
455         } finally {
456             theJDBCUtil.closeJDBCStatement(removeUserStatement);
457             theJDBCUtil.closeJDBCConnection(conn);
458         }
459     }
460
461     /**
462      * Updates a user record to match the supplied User.
463      *
464      * @param user the updated user record
465      */
466     protected void doUpdateUser(User user)
467     {
468         Connection conn = openConnection();
469         PreparedStatement updateUserStatement = null;
470
471         // Update the database.
472 try {
473             updateUserStatement = conn.prepareStatement(m_updateUserSql);
474             setUserForUpdateStatement(user, updateUserStatement);
475             updateUserStatement.execute();
476         }
477         catch ( SQLException sqlExc ) {
478             throw new CascadingRuntimeException("Error accessing database", sqlExc);
479         } finally {
480             theJDBCUtil.closeJDBCStatement(updateUserStatement);
481             theJDBCUtil.closeJDBCConnection(conn);
482         }
483     }
484
485     /**
486      * Gets a user by name, ignoring case if specified.
487      * If the specified SQL statement has been defined, this method
488      * overrides the basic implementation in AbstractUsersRepository
489      * to increase performance.
490      *
491      * @param name the name of the user being retrieved
492      * @param ignoreCase whether the name is regarded as case-insensitive
493      *
494      * @return the user being retrieved, null if the user doesn't exist
495      */
496     protected User getUserByName(String name, boolean ignoreCase)
497     {
498         // See if this statement has been set, if not, use
499 // simple superclass method.
500 if ( m_userByNameCaseInsensitiveSql == null ) {
501             return super.getUserByName(name, ignoreCase);
502         }
503
504         // Always get the user via case-insensitive SQL,
505 // then check case if necessary.
506 Connection conn = openConnection();
507         PreparedStatement getUsersStatement = null;
508         ResultSet rsUsers = null;
509         try {
510             // Get a ResultSet containing all users.
511 String sql = m_userByNameCaseInsensitiveSql;
512             getUsersStatement = conn.prepareStatement(sql);
513
514             getUsersStatement.setString(1, name.toLowerCase(Locale.US));
515
516             rsUsers = getUsersStatement.executeQuery();
517
518             // For case-insensitive matching, the first matching user will be returned.
519 User user = null;
520             while ( rsUsers.next() ) {
521                 User rowUser = readUserFromResultSet(rsUsers);
522                 String actualName = rowUser.getUserName();
523                     
524                 // Check case before we assume it's the right one.
525 if ( ignoreCase || actualName.equals(name) ) {
526                     user = rowUser;
527                     break;
528                 }
529             }
530             return user;
531         }
532         catch ( SQLException sqlExc ) {
533             throw new CascadingRuntimeException("Error accessing database", sqlExc);
534         }
535         finally {
536             theJDBCUtil.closeJDBCResultSet(rsUsers);
537             theJDBCUtil.closeJDBCStatement(getUsersStatement);
538             theJDBCUtil.closeJDBCConnection(conn);
539         }
540     }
541
542
543     /**
544      * Reads properties for a User from an open ResultSet.
545      * Subclass implementations of this method must have knowledge of the fields
546      * presented by the "select" and "selectByLowercaseName" SQL statements.
547      * These implemenations may generate a subclass-specific User instance.
548      *
549      * @param rsUsers A ResultSet with a User record in the current row.
550      * @return A User instance
551      * @throws SQLException
552      * if an exception occurs reading from the ResultSet
553      */
554     protected abstract User readUserFromResultSet(ResultSet rsUsers)
555         throws SQLException;
556
557     /**
558      * Set parameters of a PreparedStatement object with
559      * property values from a User instance.
560      * Implementations of this method have knowledge of the parameter
561      * ordering of the "insert" SQL statement definition.
562      *
563      * @param user a User instance, which should be an implementation class which
564      * is handled by this Repostory implementation.
565      * @param userInsert a PreparedStatement initialised with SQL taken from the "insert" SQL definition.
566      * @throws SQLException
567      * if an exception occurs while setting parameter values.
568      */
569     protected abstract void setUserForInsertStatement(User user,
570                                                       PreparedStatement userInsert)
571         throws SQLException;
572
573     /**
574      * Set parameters of a PreparedStatement object with
575      * property values from a User instance.
576      * Implementations of this method have knowledge of the parameter
577      * ordering of the "update" SQL statement definition.
578      *
579      * @param user a User instance, which should be an implementation class which
580      * is handled by this Repostory implementation.
581      * @param userUpdate a PreparedStatement initialised with SQL taken from the "update" SQL definition.
582      * @throws SQLException
583      * if an exception occurs while setting parameter values.
584      */
585     protected abstract void setUserForUpdateStatement(User user,
586                                                       PreparedStatement userUpdate)
587         throws SQLException;
588
589     /**
590      * Opens a connection, throwing a runtime exception if a SQLException is
591      * encountered in the process.
592      *
593      * @return the new connection
594      */
595     private Connection openConnection()
596     {
597         try {
598             return m_datasource.getConnection();
599         }
600         catch (SQLException sqle) {
601             throw new CascadingRuntimeException(
602                 "An exception occurred getting a database connection.", sqle);
603         }
604     }
605 }
606
607
608

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags